Data Movement practice questions

Use a public network connection to stream data directly into Snowflake stages.

Why it fails: Snowball is specifically designed for environments where network throughput is insufficient for bulk data transfer. Relying on a public network defeats the purpose of utilizing a physical device for migration and does not fulfill the requirement of utilizing the secure Snowflake Snowball import workflow process.

Provide the encryption key to Snowflake support for decryption upon device arrival.

Why it fails: Snowflake does not handle decryption keys manually through support channels. The customer must use the key within the Snowflake SQL environment during the COPY command execution. Relying on manual intervention from support is not a supported or secure method for loading encrypted data into a target table.

Create a temporary internal stage to serve as an intermediary for Snowball data.

Why it fails: Snowball data is loaded into an internal stage automatically as part of the ingestion process. Creating an additional intermediary stage adds unnecessary complexity and redundant storage costs, and it does not align with the standard workflow for importing physical device data directly into Snowflake destination tables.

Unload all data into one file and use an external script to rename and move files.

Why it fails: Moving and renaming files using external scripts introduces unnecessary post-processing steps and potential latency. Snowflake can handle partitioning natively during the unload process, making external orchestration inefficient and increasing the surface area for potential errors or data corruption during the file manipulation phase of the workflow.

Execute a series of COPY INTO commands for each month using a WHERE clause.

Why it fails: Running multiple sequential commands is slow and requires managing complex job orchestration. It increases the probability of failures and maintenance overhead compared to a single, optimized COPY statement that can dynamically partition the entire dataset in one operation, making it a sub-optimal choice for large-scale data exports.

Unload the data as JSON and convert to Parquet using a separate compute cluster.

Why it fails: Converting file formats after unloading is a resource-intensive and redundant process. Snowflake supports native Parquet export, which should be used directly to minimize compute costs and reduce the time required to make the data available for downstream consumption in the desired format and partitioned structure.

XML

Why it fails: Snowflake does not provide a native 'XML' format option for the COPY INTO command. While data can be converted to XML within Snowflake using string manipulation, it is not a first-class supported file format, requiring custom serialization logic that is inefficient and prone to errors during bulk operations.

SQL

Why it fails: SQL is not a file format; it is a query language used to interact with the database. Unloading data as SQL would essentially mean exporting DDL and DML statements, which is not a supported or standard mechanism for bulk data movement out of Snowflake to cloud object storage.

SET SKIP_ROW = 1;

Why it fails: There is no 'SKIP_ROW' parameter in Snowflake file formats. Using incorrect syntax will result in an error when trying to create or alter the file format object. The engineer must use the correct, documented parameter name to achieve the desired effect of skipping the header row during ingestion.

SET HEADER_ROW = TRUE;

Why it fails: While many systems use a boolean flag for headers, Snowflake uses the 'SKIP_HEADER' integer parameter. 'HEADER_ROW' is not a valid property for a Snowflake file format, and using it will fail to prevent the header from being loaded as a data row into the table.

SET IGNORE_FIRST_LINE = TRUE;

Why it fails: 'IGNORE_FIRST_LINE' is not a valid parameter within the Snowflake COPY command or file format definition. The engine requires specific syntax to handle file metadata, and relying on incorrect parameters will result in the ingestion process treating the header as valid data, causing downstream data quality issues.

Check the 'STAGES' table to see a list of ingested files.

Why it fails: The 'STAGES' table does not track ingestion status or file processing results. It is simply a definition of where the cloud storage is located. It lacks the metadata and history required to verify whether specific files were successfully loaded into Snowflake, making it useless for monitoring pipeline success.

Use the 'SHOW PIPES' command for detailed file-level load results.

Why it fails: 'SHOW PIPES' displays metadata and status about the pipe object itself, such as the stage it uses and the last time it was modified. It does not contain granular information about individual files that were processed or skipped, and therefore cannot be used for monitoring the health of file-level ingestion.

Examine the S3 bucket logs to verify the pipe successfully read the files.

Why it fails: S3 bucket logs can only show that a read request was made by the Snowflake service principal. They do not indicate whether the ingestion into the Snowflake table succeeded or failed due to formatting issues. You must use Snowflake-native views to confirm the result of the ingestion process within the warehouse.

Directly insert data into the table using a standard SQL INSERT statement.

Why it fails: Standard SQL INSERT statements are intended for single-row or small-batch updates. They are extremely inefficient for loading data from files located on a stage. Attempting to use them for bulk file ingestion would lead to severe performance issues and is not a supported method for accessing staged cloud data files.

Create an external table that reads directly from the internal stage.

Why it fails: External tables are designed to read data from external cloud storage (e.g., S3 buckets), not internal Snowflake stages. Internal stages are designed for bulk loading via the COPY command, not for external table definitions. Attempting to mix these concepts results in an invalid configuration that Snowflake will not accept.

Use the ALTER TABLE command to import data from an internal stage.

Why it fails: 'ALTER TABLE' is for modifying table structure, such as adding or dropping columns or changing table properties. It does not support data ingestion. Using it for data movement is syntactically incorrect and fails to perform any of the necessary file parsing, data validation, or loading operations required for ingestion.

The command will fail immediately because any data type mismatch triggers an abort by default.

Why it fails: The default behavior of COPY INTO is indeed to abort on any error, but the presence of the ON_ERROR parameter overrides this behavior. Since a specific error handling threshold has been defined in the exhibit, Snowflake will follow the logic of skipping files rather than failing the entire ingestion job.

The entire batch of 100 files will be loaded, but the row with the mismatch will be replaced with NULL values.

Why it fails: Snowflake's COPY command does not automatically convert erroneous rows into NULLs unless specifically configured with transformations or specific file format options like NULL_IF. The ON_ERROR parameter handles file or row rejection but does not perform on-the-fly data correction for type mismatches during a standard bulk load.

All 100 files will be deleted from the stage regardless of whether they were successfully loaded into the table.

Why it fails: The PURGE parameter in Snowflake is designed to be safe; it only removes files from the stage that have been successfully processed and committed to the target table. Files that are skipped due to errors or those that fail to load remain in the stage for the engineer to investigate and reprocess.

External Stage

Why it fails: An External Stage defines the location of the files and the file format, but it relies on either a Storage Integration or hardcoded credentials for access. While it is used in data movement, it is not the object that specifically handles the secure, credential-less connection to the cloud provider's API.

File Format

Why it fails: A File Format object describes the structure of the data files (e.g., CSV, JSON, Parquet) so that Snowflake knows how to parse them. It has no role in managing authentication or connectivity to the external cloud storage service where the files are actually residing.

Resource Monitor

Why it fails: A Resource Monitor is used to control and track credit consumption for virtual warehouses. It has no relationship with data movement security or the authentication process required to connect Snowflake to external cloud storage platforms like AWS S3, Google Cloud Storage, or Azure Blob.

It bypasses the need for a virtual warehouse, making the data movement completely free.

Why it fails: Querying data directly from a stage or using a transformation in a COPY command still requires compute resources. A virtual warehouse must be active and running to execute the SQL logic, parse the files, and perform the data type casting shown in the exhibit.

This syntax is required because Snowflake cannot store raw JSON data in a table without pre-processing.

Why it fails: Snowflake is highly capable of storing raw JSON in a VARIANT column. Pre-processing is an architectural choice for optimization and schema enforcement, but it is not a requirement. Snowflake's schema-on-read capabilities allow users to load entire JSON documents and query them directly using functional notation.

It automatically creates a new table with the correct schema if the table does not already exist.

Why it fails: The SELECT statement against a stage does not have the capability to create tables. It is used to project data for an existing table or for ad-hoc analysis. To create a table automatically, one would typically use the INFER_SCHEMA function or the Schema Evolution features during a COPY operation.

Snowpipe

Why it fails: Snowpipe is an ingestion service used to move data from files into tables. Since the requirement specifically asks to track metadata 'without actually loading the data', Snowpipe is the wrong tool, as its primary function is the permanent movement of data into the Snowflake storage layer.

Streams on Stages

Why it fails: A Stream on a Stage tracks the 'change data' (new files) since the last time the stream was consumed. While useful for triggering ingestion, it does not provide a persistent, queryable metadata catalog of the entire stage's contents like a Directory Table does.

Materialized Views

Why it fails: Materialized Views are used to pre-compute and store the results of complex queries for performance. They require data to already exist in a Snowflake table (or an external table) and do not provide the foundational file-level metadata tracking required for external cloud storage management.

Encrypting the data using Snowflake-managed keys with the ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') parameter.

Why it fails: Server-side encryption (SSE) protects data at rest in the cloud provider, but the provider still has access to the keys. For the 'highest level' of security, especially for sensitive data, client-side encryption is preferred as it ensures the data is unreadable to anyone who does not possess the specific master key.

Setting the stage's directory table to 'REFRESH_ON_CREATE = FALSE' to hide the file list.

Why it fails: Disabling the refresh of a directory table is a management setting, not a security feature. It does not prevent unauthorized users from listing files via the cloud provider's console or API, nor does it encrypt the data. It merely stops Snowflake from automatically updating the metadata catalog for that stage.

Using a Public S3 bucket to simplify the connection and ensure no authentication errors occur.

Why it fails: Using a public bucket is a severe security risk and is the opposite of a 'high level' of security. Public buckets allow anyone on the internet to read your data. Secure data movement must always use authenticated, private connections to ensure that sensitive information is protected from unauthorized access.

The size and uptime of the virtual warehouse assigned to the pipe.

Why it fails: Snowpipe is a serverless feature and does not use a user-defined virtual warehouse. It uses Snowflake-managed compute resources, so the uptime or size of your own warehouses has no impact on Snowpipe's specific credit consumption or billing metrics.

The distance between the S3 bucket region and the Snowflake region.

Why it fails: While cross-region data transfer might incur 'Data Transfer' charges at the cloud provider level (or via Snowflake for egress), it is not a direct factor in the 'Snowpipe' credit calculation. Snowpipe billing is primarily focused on the compute and management overhead within the Snowflake service itself.

Set the pipe's AUTO_INGEST parameter to recursive.

Why it fails: The AUTO_INGEST parameter is a boolean flag used to enable event-based notifications, not for configuring recursive file path traversal. There is no recursive setting within the pipe itself, as path logic is strictly tied to the stage definition and the pattern matching used during the copy command execution.

Create a separate pipe for every subfolder in the S3 bucket.

Why it fails: Creating individual pipes for each subfolder is operationally inefficient and difficult to maintain as new subfolders are added. Snowflake allows for a single pipe to monitor a stage that encompasses multiple subfolders, provided the correct file pattern is used, which avoids significant overhead and potential latency issues.

Configure an S3 event notification to trigger the pipe for each subfolder specifically.

Why it fails: While event notifications trigger ingestion, configuring them for every subfolder is unnecessary and overly complex. S3 bucket-level notifications are sufficient to trigger the pipe for any file uploaded anywhere within the bucket, allowing Snowflake to handle the directory traversal automatically based on the stage configuration provided during setup.

Run the SnowSQL command to directly mount the Snowball device to the Snowflake warehouse.

Why it fails: Snowflake does not support mounting physical devices directly to virtual warehouses. Data from physical devices must always be uploaded to an intermediate cloud storage location like S3 or Azure Blob Storage before being accessible to Snowflake through stages, maintaining logical separation between storage and compute resources.

Configure a storage integration object to grant Snowflake access to the temporary S3 bucket.

Why it fails: While a storage integration is necessary for secure access, it is a prerequisite for the stage creation, not the final step of the loading process. The final step requires the explicit execution of the COPY INTO command to transform the staged files into structured table data within the database.

Import the Snowball data directly into a table using the IMPORT INTO command.

Why it fails: There is no IMPORT INTO command in Snowflake. The correct and only supported mechanism for loading data from stages is the COPY INTO command. Relying on non-existent commands will result in syntax errors and failure to move the data from the cloud storage into the permanent relational tables.

The PUT command supports uploading files directly to an external stage (e.g., S3).

Why it fails: The PUT command is exclusively designed for uploading local files to internal stages managed by Snowflake. It cannot interact with external stages, as external stages rely on the cloud provider's own API or SDKs for file movement. Attempting to use PUT with external stages will result in an error.

The PUT command is the recommended tool for high-volume, automated production data ingestion.

Why it fails: The PUT command is a manual utility intended for ad-hoc loads, not for high-volume automated production environments. For production pipelines, Snowflake recommends using Snowpipe or the COPY INTO command with external stages to achieve better performance, reliability, and error handling capabilities compared to manual client-side file transfers.

The PUT command can be executed within a Snowflake Worksheet.

Why it fails: The PUT command must be executed from a local client tool like SnowSQL, as it requires access to the local file system. Snowflake Worksheets run in the browser and do not have access to the user's local file system, making it impossible for the engine to locate files.

EMPTY_FIELD_AS_NULL = TRUE

Why it fails: There is no parameter named EMPTY_FIELD_AS_NULL in Snowflake's file format configuration. Using invalid parameters will cause the configuration statement to fail. The correct approach is to use the NULL_IF option, which provides the necessary flexibility to define which specific values should be converted into SQL NULLs.

SKIP_BLANK_LINES = TRUE

Why it fails: SKIP_BLANK_LINES is used to ignore entirely empty rows in a file, not to convert individual empty fields into NULL values. This setting is useful for cleaning up file artifacts but does not affect the data type conversion of fields within rows that are actually processed by the load.

TRIM_SPACE = TRUE

Why it fails: TRIM_SPACE removes leading and trailing white spaces from fields. While this helps clean up data, it does not automatically convert an empty string into a NULL value. An empty string remains an empty string unless specifically targeted by the NULL_IF configuration parameter during the data ingestion process.

Snowpipe will automatically overwrite the existing data in the table with the new file content.

Why it fails: Snowpipe does not perform an upsert or overwrite operation by default. It only performs inserts. If the same file name is uploaded, Snowpipe ignores it because it already exists in the load history metadata, meaning the new data will not be added to the target table without specific intervention.

Snowpipe will append the data to the table, resulting in duplicate records.

Why it fails: Snowpipe checks the load history before ingestion. It will not append the data because it identifies the file as having been previously processed. Unless the metadata is cleared or the file is renamed, the pipe will correctly recognize the duplicate and exclude it from the ingestion process.

The pipe will enter an error state until the administrator clears the load history.

Why it fails: The pipe does not enter an error state simply because a duplicate file is detected. It gracefully skips the file and continues monitoring for new, unique files. Error states in pipes are typically reserved for configuration issues, access errors, or data transformation failures, not for duplicate file handling.

A virtual warehouse must be running to process the pipe's ingestion tasks.

Why it fails: Snowpipe utilizes its own serverless compute resources provided by Snowflake. You do not need to manage or run a dedicated virtual warehouse for Snowpipe operations. The system handles the scaling and compute management automatically, which is a major advantage for reducing operational overhead in data movement pipelines.

The user executing the COPY INTO command must have the ACCOUNTADMIN role.

Why it fails: Snowpipe does not require the ACCOUNTADMIN role for its service operations. It operates under a specific internal service account, and the initial configuration requires standard privileges for creating pipes and integrations. Over-privileging users with ACCOUNTADMIN for routine ingestion tasks violates the principle of least privilege in security architecture.

SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_USAGE_HISTORY());

Why it fails: PIPE_USAGE_HISTORY provides metrics on the credit consumption of pipes, not the status of individual file loads. It is useful for billing and resource monitoring, but it does not contain the detailed file-level history required to verify whether specific files were successfully ingested into a target database table.

SELECT * FROM INFORMATION_SCHEMA.STAGES;

Why it fails: The STAGES view only provides configuration information about the stages themselves, such as their type, location, and owner. It does not track the activity or status of files moving through these stages. To track data movement success, one must query the system functions specifically designed for load audit logs.

SHOW PIPE_STATUS FROM TABLE(my_table);

Why it fails: There is no command called SHOW PIPE_STATUS. While one can use the SYSTEM$PIPE_STATUS function to check the health of a specific pipe, it is not the primary way to audit the history of processed files across the entire system or for specific table loads over time.

ALTER PIPE my_pipe RESET;

Why it fails: There is no RESET command for pipes. The correct terminology for this operation in Snowflake is REFRESH. Attempting to use non-existent commands will result in a syntax error, preventing the engineer from successfully re-triggering the ingestion process for the required files.

DROP PIPE my_pipe; CREATE PIPE my_pipe ...;

Why it fails: While this method would effectively clear the load history, it is disruptive and operationally inefficient. It requires re-configuring the pipe and potentially losing the current state of the ingestion pipeline. The REFRESH command is the preferred, non-destructive way to achieve the same result without needing to delete the object.

ALTER PIPE my_pipe SET LOAD_HISTORY = CLEAR;

Why it fails: The syntax provided is incorrect. There is no configuration parameter to clear the load history in this manner. The metadata associated with the pipe is managed internally by Snowflake, and the only way to influence file re-ingestion is through the REFRESH functionality provided by the ALTER command.