Data Transformation practice questions

The stream is automatically dropped after its content is read by a SELECT statement.

Why it fails: Reading from a stream using a simple SELECT statement does not consume the data or advance the offset. Streams are persistent objects that remain in the schema until explicitly dropped by a user. They are designed to be consumed by DML operations like INSERT, MERGE, or UPDATE to drive incremental data loading workflows.

A stream can only be created on permanent tables, not on transient or temporary tables.

Why it fails: Snowflake supports creating streams on permanent, transient, and even temporary tables to track changes. While the lifecycle of the stream is tied to the existence of the source table, the table type does not strictly limit the stream's creation. This flexibility allows engineers to use streams for intermediate processing steps using transient storage.

The stream will store a physical copy of the source data until it is consumed.

Why it fails: Streams do not store actual data records but rather act as a pointer or bookmark to the versioning history of the source table. They leverage Snowflake's Time Travel metadata to present the delta between two points in time. This design is highly efficient as it avoids duplicating storage costs while providing change data.

Use a single FLATTEN on the 'orders' path and access 'items' using bracket notation.

Why it fails: A single flatten operation only expands the first level of the hierarchy, leaving the 'items' field as an array within the result set. While bracket notation can access specific elements, it cannot transform the nested array into multiple rows. This would result in one row per order rather than one row per product item.

Apply the GET_PATH function recursively within a GROUP BY clause to aggregate products.

Why it fails: GET_PATH is used to extract specific values from a variant column but does not perform row expansion. Combining it with GROUP BY would attempt to aggregate data that has not yet been flattened, leading to incorrect results. Transformations involving array unnesting must use FLATTEN to generate the necessary rows before any aggregation.

Utilize the STRIP_NULL_VALUE function before a single FLATTEN on the root object.

Why it fails: STRIP_NULL_VALUE is a utility for cleaning semi-structured data by removing null JSON elements but has no impact on the flattening process itself. A single flatten on the root object would not address the nested nature of the 'items' array. This approach fails to produce the required granularity for the product-level report.

Dynamic Tables support the use of non-deterministic functions like CURRENT_TIMESTAMP().

Why it fails: Incremental refreshes in Dynamic Tables generally do not support non-deterministic functions because they would produce inconsistent results during delta processing. Functions that return different values for the same input at different times break the mathematical model used for incremental updates. This is a common limitation when designing real-time transformation logic for dynamic objects.

Dynamic Tables are physically stored as a view and do not consume storage.

Why it fails: Unlike standard views, Dynamic Tables are materialized objects that store the results of a query physically on disk. They consume storage space in the same way that permanent tables do. This materialization is what allows for high-performance reads, as the transformation logic has already been executed and the results are persisted.

A PIVOT function combined with a window function

Why it fails: The PIVOT function is used to transform rows into columns for reporting purposes, typically involving aggregates. It does not have the capability to traverse recursive relationships or calculate the depth of a tree structure. Using PIVOT for hierarchy management would require a known, fixed depth, which is rarely the case in organizational data.

A LATERAL JOIN with a FLATTEN function

Why it fails: LATERAL JOINs and FLATTEN are intended for semi-structured data transformation, such as expanding JSON arrays into relational rows. They are not designed for traversing recursive self-referencing relationships in a relational table. While they can expand data, they lack the logic to walk up or down a tree structure based on column values.

A GROUP BY CUBE clause

Why it fails: The CUBE clause is an extension of GROUP BY that generates subtotals for all possible combinations of dimensions. It is used for multi-dimensional analysis and data cubing, not for hierarchical traversal. It cannot track the relationship between specific employee records or calculate their distance from a specific node like the CEO.

The record will be updated with the value from the staging table.

Why it fails: The update clause is the second 'WHEN MATCHED' condition in the statement. Because the DELETE condition appears first and evaluates to true for the 'DELETED' status, the MERGE operation will execute the delete and stop evaluating further match clauses for that specific row. This prevents the update from ever occurring.

The MERGE statement will fail with a 'Multiple matches' error.

Why it fails: A 'multiple matches' error occurs if the source (staging) table has more than one row matching a single row in the target table. The exhibit does not suggest multiple source rows for the same ID. Matching multiple clauses for a single row is handled by order of precedence, not by throwing an error.

The record will be updated and then immediately deleted.

Why it fails: Snowflake does not perform multiple actions (like both an UPDATE and a DELETE) on the same row within a single MERGE statement. Once a row satisfies a MATCHED or NOT MATCHED condition and an action is taken, processing for that row is complete. This ensures predictable behavior and prevents conflicting data modifications.

Materialized Views support multi-table joins which are not available in Dynamic Tables.

Why it fails: This statement is technically incorrect. In Snowflake, Materialized Views are restricted to a single base table and do not support joins. Dynamic Tables, conversely, were specifically designed to support complex multi-table joins and aggregations, making them the superior choice for complex pipeline architectures that involve merging data from multiple sources.

Materialized Views are always cheaper to maintain than Dynamic Tables.

Why it fails: Maintenance costs depend entirely on the churn rate of the underlying data. Materialized Views use serverless compute which can become expensive if the base table is updated very frequently. Dynamic Tables allow for more control over costs by adjusting the TARGET_LAG, potentially reducing the frequency of compute-intensive updates compared to a Materialized View.

Materialized Views support all SQL functions including non-deterministic ones.

Why it fails: Materialized Views have significant limitations on the SQL they can contain. They do not support non-deterministic functions, UDFs, or window functions. They are much more restrictive than Dynamic Tables in this regard. Their primary purpose is to provide a pre-computed result of a simple query to speed up data access.

A Storage Integration object

Why it fails: Storage Integrations are used specifically for connecting Snowflake to external cloud storage locations like S3 or GCS for data loading and unloading. They are not involved in the execution of External Functions. Using the wrong integration type would result in a failure to authenticate with the API Gateway or Lambda service.

A Snowflake Managed Stage

Why it fails: Internal or external stages are used for storing files and are not part of the communication path for External Functions. While data might be loaded from a stage before being passed to an external function, the stage itself is not a required component for the function's execution or connectivity framework.

Select columns with high cardinality (e.g., UUIDs) as clustering keys.

Why it fails: High cardinality columns are poor candidates for clustering because they do not allow Snowflake to effectively group similar data into the same micro-partitions. This leads to poor pruning. Ideal clustering keys have low to medium cardinality, such as dates or geographic regions, which allow for meaningful separation of data across partitions.

Manually run the RECLUSTER command daily during off-peak hours.

Why it fails: Snowflake handles reclustering as an automatic background service once a clustering key is defined. There is no manual 'RECLUSTER' command for users to run. The Automatic Clustering service monitors the table and performs maintenance only when it is cost-effective to do so, based on the degree of fragmentation in the partitions.

Apply clustering keys to all tables regardless of their size to ensure consistency.

Why it fails: Clustering keys are only recommended for very large tables (typically over several hundred gigabytes). On smaller tables, the overhead of the Automatic Clustering service often outweighs the performance gains. For most small to medium datasets, Snowflake's natural ingestion order provides sufficient performance without the added complexity and cost of clustering.

A recursive CTE (Common Table Expression)

Why it fails: While recursive CTEs can theoretically solve this by iterating through rows, they are extremely inefficient in a distributed cloud data warehouse like Snowflake for this purpose. They execute sequentially, which negates the benefits of parallel processing. A window-function-based approach is much faster and more idiomatic for time-series transformations.

The GROUP BY ROLLUP clause

Why it fails: ROLLUP is used to generate hierarchical sub-totals for multiple dimensions in a single result set (e.g., Year > Month > Day). It has no mechanism for detecting gaps between individual rows or resetting calculations based on temporal conditions. It is an aggregation tool, not a sequence-aware transformation tool.

The RATIO_TO_REPORT window function

Why it fails: RATIO_TO_REPORT calculates the relative contribution of a row's value to the total sum of a partition. It is useful for percentage-of-total calculations but cannot be used to track time gaps, define sessions, or calculate cumulative sums that reset based on logic. It lacks the conditional logic required for this scenario.

The view must be a Secure View.

Why it fails: Security settings (Secure vs. non-secure) do not affect the ability to create a stream on a view. While secure views are often used in production for privacy, the streaming mechanism is independent of the security metadata and focuses instead on the underlying data tracking and the query structure of the view.

The view must only contain inner joins and no aggregations.

Why it fails: Snowflake supports streams on views that contain various types of joins and even certain transformations. While there are some limitations on the complexity of the SQL (like certain non-deterministic functions), the restriction is not as narrow as 'inner joins only'. The primary requirement is that the change data can be logically derived.

The stream must be created using the 'SHOW_INITIAL_ROWS' clause.

Why it fails: The 'SHOW_INITIAL_ROWS' clause is an optional parameter used when creating a stream to capture all existing rows in the source as 'inserts'. It is not a requirement for the stream's functionality on a view. A stream can be created without this clause to only capture changes that occur after the stream's creation.

The query must include a non-deterministic function like CURRENT_TIMESTAMP() in the SELECT clause.

Why it fails: Non-deterministic functions prevent the refresh process from accurately calculating incremental updates. Because the results of such functions change independently of the underlying data, Snowflake cannot reliably determine which rows have been modified, rendering incremental refresh impossible for the dynamic table pipeline.

The query definition must not contain any JOIN operations between tables.

Why it fails: Dynamic tables fully support JOIN operations between multiple source tables. The engine maintains the incremental state of the join, allowing for complex transformations that combine data from different sources while still benefiting from the automated scheduling and refresh capabilities provided by the dynamic table.

The warehouse assigned to the dynamic table must be running continuously to trigger refreshes.

Why it fails: Snowflake handles the warehouse management for dynamic tables. When a refresh is needed based on the lag target, Snowflake automatically starts the required compute resources, performs the work, and suspends them, meaning you do not need to keep a warehouse running manually.

Always set the stream on a view that contains non-deterministic functions to capture dynamic data.

Why it fails: Streams cannot track changes on views containing non-deterministic functions because there is no reliable way to identify row-level modifications. Attempting to create a stream on such a view will cause an error, as the system cannot guarantee the integrity of the captured delta.

Use the STREAM_GET_STALE_OFFSET function to force the stream to reset its position to the beginning of time.

Why it fails: Streams track offsets automatically based on the data retention period. Forcing offsets or manually managing them is not a standard optimization practice and often leads to data loss or duplicate processing if not handled correctly by the underlying system.

It can be applied to the SUM() aggregate function to treat NULLs as zero.

Why it fails: The IGNORE NULLS clause is specifically designed for window functions like LAG, LEAD, FIRST_VALUE, and LAST_VALUE. It does not apply to aggregate functions like SUM, which handle nulls by ignoring them by default during the summation process.

It is valid for use in all standard SQL aggregate functions.

Why it fails: IGNORE NULLS is only supported by specific window functions, not aggregate functions. Attempting to use it with standard aggregates like COUNT or AVG will result in a syntax error, as those functions have their own built-in logic for handling null values.

It converts all NULL values to a default constant value specified by the user.

Why it fails: IGNORE NULLS does not perform conversion. Conversion to a constant is achieved through the COALESCE or NVL functions. IGNORE NULLS strictly changes the lookup behavior of window functions to find the next valid record rather than modifying the data values themselves.

Keep all data in a single VARIANT column and use lateral flatten in every query.

Why it fails: Constant use of lateral flatten operations forces the engine to re-parse the JSON structure every time the query runs. This significantly increases compute costs and degrades performance for large datasets where only a subset of fields is actually needed for reporting purposes by end users.

Convert JSON to a CSV file and load it into a standard table using COPY INTO.

Why it fails: Converting JSON to CSV is an unnecessary ETL step that adds latency and complexity to the pipeline. Snowflake natively supports semi-structured data, and moving through flat-file formats introduces risks of data loss or schema mismatch errors during the serialization/deserialization process compared to native loading.

Store the JSON in a temporary table and use a stored procedure to cast it to STRING.

Why it fails: Casting complex nested JSON into a single STRING type renders the data virtually useless for analytical querying. This transformation destroys the structural integrity of the nested objects and forces users to perform complex regex or substring manipulations, which are computationally expensive and prone to failure.

The source tables must be updated using only the INSERT command.

Why it fails: Dynamic Tables support DML operations like UPDATE and DELETE on source tables. The change tracking mechanism utilizes Change Data Capture (CDC) streams internally, which allows the dynamic table to capture row-level modifications regardless of the specific DML operation used, as long as streams can track changes.

The user must manually trigger a refresh every hour for consistency.

Why it fails: Dynamic tables are designed for automated, scheduled refreshes based on a specified target lag. Manual intervention is unnecessary and contradicts the goal of declarative data pipelines. The system manages the scheduling automatically based on the workload and the defined lag parameters provided by the user.

The source data must be stored in a flat file format like Parquet.

Why it fails: Dynamic tables work seamlessly with source objects like tables or other dynamic tables regardless of the underlying file format used to ingest the data. Once data is loaded into a Snowflake table, the transformation logic operates on the relational state of the data, not files.

Streams can track changes on views if the view is a secure view.

Why it fails: Snowflake streams are strictly supported on tables and materialized views. They cannot be created on standard views or secure views, as those objects do not have persistent storage or change tracking metadata associated with them to track row-level modifications accurately across DML operations.

Streams automatically purge data once a task consumes it.

Why it fails: Streams do not automatically purge data. Data remains in the stream until it is consumed by a DML statement or a task. If the stream is not consumed, it can grow in size, and the underlying historical data in the source table may be retained longer than expected.

Streams are global objects and can be shared across accounts.

Why it fails: Streams are local to the database and schema where they are created. They cannot be shared via Snowflake Data Sharing to other accounts. The change tracking mechanism is tightly coupled to the table's micro-partition state, which is unique to the specific account where the data resides.

Client-side execution on the local machine.

Why it fails: While code is developed locally, the actual data transformation execution happens on the Snowflake compute engine. Snowpark does not pull data to the local client for processing, as that would be highly inefficient and insecure, failing to leverage Snowflake's distributed processing capabilities.

Native support for training machine learning models on external GPUs.

Why it fails: Snowpark does not natively offload transformations to external GPU clusters. All processing is contained within the Snowflake environment, utilizing internal compute resources. While Snowpark is used for data prep for ML, the actual GPU-based training would need to happen in a separate infrastructure.

Set RECURSIVE => FALSE

Why it fails: The `RECURSIVE` parameter controls whether the function traverses nested objects beyond the immediate children. It does not filter out empty objects. Setting this to false would simply limit the depth of the flattening process, not improve the quality of the resulting data by removing empty nodes.

Set MODE => 'OBJECT'

Why it fails: The `MODE` parameter is not a standard option for the `FLATTEN` table function in this context. `FLATTEN` uses specific arguments to control traversal behavior. Specifying an invalid mode will lead to a syntax error, as the function expects specific keywords for its parameter configuration.

Set PATH => 'NULL'

Why it fails: The `PATH` parameter specifies the path to the sub-element to flatten. It does not accept 'NULL' as a value to filter out empty objects. Misconfiguring the path parameter would likely result in an empty result set or an error rather than selectively filtering empty objects.

SQL UDFs

Why it fails: SQL UDFs are excellent for simple transformations, but they are essentially macros and cannot handle complex procedural logic or imported libraries. For compute-intensive operations requiring advanced mathematical modeling or stateful processing, SQL UDFs lack the necessary capability and performance compared to compiled or high-performance interpreted languages.

Python UDFs

Why it fails: Python is excellent for library support but can be slower for raw compute-intensive loops compared to Java due to the Global Interpreter Lock (GIL) and overhead of the interpreter. While efficient for many tasks, it is not always the highest-performing choice for very low-level compute-heavy logic.

JavaScript UDFs

Why it fails: JavaScript UDFs are primarily intended for parsing JSON or handling string manipulations rather than heavy computational tasks. They are not designed for large-scale mathematical or logical processing and would likely underperform significantly compared to Java or Python in a data-intensive engineering pipeline.

JSON_EXTRACT_PATH_TEXT(raw_data, 'user_id')

Why it fails: While this function extracts text from a JSON path, it is generally less performant than the direct colon notation. In many contexts, it may return a string 'null' for missing elements depending on the specific implementation, which can lead to data quality issues if not explicitly handled by the data engineer.

GET(raw_data, 'user_id')

Why it fails: The GET function retrieves a value from a VARIANT but returns it as a VARIANT type itself. To use this as a standard SQL NULL, you would still need an additional cast. Without the cast, the output remains semi-structured, which complicates standard relational filtering and aggregation logic in data warehouses.

FLATTEN(raw_data:user_id)

Why it fails: FLATTEN is designed to explode arrays or objects into multiple rows, not for simple attribute extraction. Using it on a single key that might be missing would result in zero rows for that record if not configured correctly, effectively filtering out data that should have been preserved as a NULL.