Hard Difficulty Questions

It lists only the immediate view name that the user referenced in their SELECT statement.

Why it fails: The column that captures the immediate object referenced in the query is 'objects_accessed', not 'base_objects_accessed'. Focusing only on the immediate object would fail to reveal the underlying sensitive tables if the user accessed them through an abstraction layer like a non-secure view or a stored procedure.

It displays the metadata of the warehouse used to execute the query for the table.

Why it fails: Warehouse metadata is not stored within the 'base_objects_accessed' field. That field is dedicated strictly to object-level lineage and data access patterns. Information about the compute resources used for the query is found in different columns of the ACCESS_HISTORY view or the QUERY_HISTORY view.

It identifies the specific columns and rows that were filtered out by the query optimizer.

Why it fails: ACCESS_HISTORY records which objects were accessed, but it does not provide a detailed log of which specific rows were returned or which were filtered out by the optimizer. Its purpose is to track the flow of sensitive data at the object and column level for governance purposes.

Create a standard view with CASE statements for masking.

Why it fails: Standard views do not hide the underlying metadata, potentially exposing table structures to the third party. Furthermore, manual CASE statements are harder to maintain than centralized masking policies. This approach is less secure and less scalable than using native Snowflake governance features designed for data protection.

Grant direct access to the base table.

Why it fails: Granting direct access to a base table is a major security risk. It exposes the entire table schema and all data, which is contrary to the principle of least privilege. Data sharing should always be facilitated through restricted, governed layers like views and policies to protect internal assets.

Physically clone the table and remove sensitive rows.

Why it fails: Cloning creates a duplicate copy of the data, leading to storage costs and potential synchronization issues. It also increases the attack surface as there is now a new copy of the data to manage, secure, and potentially delete. This approach is inefficient compared to using policies and views.

XLSX

Why it fails: Snowflake does not natively support exporting data directly to the XLSX (Excel) format. To export data in this format, you would need to unload to a supported format like CSV and then use an external tool or script to convert that file into the final Excel document structure.

SQL

Why it fails: Snowflake does not support unloading data into a raw .sql file format. While you can export data, it must be in a standard data exchange format like CSV, JSON, or Parquet. SQL is a command language and not a data storage format, so it is not a valid output format.

PDF

Why it fails: PDF is a document formatting standard and is not a supported data format for unloading from Snowflake tables. Data movement is intended for structured or semi-structured data, and PDF does not fulfill the requirements for machine-readable data exchange that Snowflake's engine is designed to handle during export operations.

MD5 with a randomly generated initialization vector

Why it fails: MD5 is a hash function, but it does not use an initialization vector; that concept applies to encryption modes. Introducing randomness would break determinism, as the same input would produce different outputs. Additionally, MD5 is considered cryptographically broken for collision resistance, making it unsuitable for pseudonymization that requires security and consistency.

ENCRYPT with a symmetric key and the 'AES-256' algorithm

Why it fails: ENCRYPT with AES produces reversible ciphertext, meaning anyone with the key can decrypt and recover the original value. The requirement states the pseudonym must not be reversible without a secret, but encryption inherently allows reversal with the key. While encryption is useful for confidentiality, it does not meet the non-reversibility aspect of pseudonymization here.

RANDOM with a fixed seed value for reproducibility

Why it fails: RANDOM generates pseudo-random numbers and does not accept a seed for reproducibility in Snowflake. Even if it did, it would not be deterministic for the same input string because it does not take the input as a parameter. This function is designed for generating random values, not for deriving deterministic pseudonyms from existing data, so it fails the consistency requirement.

CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)) AS event_hour

Why it fails: The three-argument version of CONVERT_TIMEZONE treats the second argument as the source timezone and the third as the target. Here, it would interpret event_time as being in UTC and convert to New York, which is actually the same as the two-argument version if event_time is UTC. However, the two-argument version is the documented shorthand for UTC to target, and using three arguments with 'UTC' as source is redundant but not incorrect. The subtle issue is that the three-argument form expects a TIMESTAMP_NTZ and returns TIMESTAMP_NTZ, but the two-argument form is more concise and equally valid. In this scenario, both would work, but the two-argument form is the standard pattern. However, the question asks for the correct expression; the three-argument form is not wrong, but it is not the best answer because it adds unnecessary complexity. The correct answer is the two-argument form.

CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)::DATE AS event_date, EXTRACT(HOUR FROM CONVERT_TIMEZONE('America/New_York', 'UTC', event_time)) AS event_hour

Why it fails: This three-argument call treats the first argument as the source timezone and the second as the target. It would interpret event_time as being in New York time and convert to UTC, which is the opposite of what is needed. The resulting date and hour would be in UTC, not New York, leading to incorrect day boundaries and hour values for the business requirement.

DATE_TRUNC('DAY', event_time)::DATE AS event_date, EXTRACT(HOUR FROM event_time) AS event_hour

Why it fails: DATE_TRUNC and EXTRACT without timezone conversion operate on the stored timestamp as-is, which is in TIMESTAMP_NTZ (no timezone). This ignores the requirement to convert to America/New_York. The resulting date and hour would reflect the original timezone-less value, not the desired local time, causing late-arriving data to be bucketed incorrectly.

Create a temporary table with the base data, then run a separate UPDATE statement to compute the moving average and store it in a new column.

Why it fails: This approach requires multiple steps: creating a temporary table, then updating it. The UPDATE statement would need to scan the temporary table again, and the temporary table itself adds storage overhead. For billions of rows, this is less efficient than a single-pass transformation, and it increases the risk of errors and longer execution time.

Use a Snowpark Python stored procedure that reads the data into a DataFrame, computes the moving average using pandas, and writes the result back to Snowflake.

Why it fails: Pulling billions of rows into a Snowpark DataFrame and then into pandas for computation would cause massive data movement and likely exceed memory limits. While Snowpark can scale, using pandas for window functions on that scale is inefficient and slow. A pure SQL window function leverages Snowflake's distributed compute without data export.

Create a materialized view that includes the moving average calculation, then query the materialized view to populate the target table.

Why it fails: Materialized views in Snowflake are automatically maintained and can improve performance for repeated queries, but they have limitations: they cannot contain window functions in their definition. Therefore, this approach is not feasible for computing a moving average. Even if it were, maintaining a materialized view on a huge table would add overhead.

Use a JavaScript UDF within a SELECT statement on a virtual warehouse.

Why it fails: While JavaScript UDFs can be used in SELECT statements, they run row-by-row and can be slow for large datasets. They also incur warehouse compute costs. For terabyte-scale data, this approach may lead to long runtimes and high costs due to the overhead of calling the UDF for each row, especially if the UDF is complex.

Use a Java UDF with vectorized execution on a standard warehouse.

Why it fails: Java UDFs can be efficient, but vectorized execution is not a native feature of Java UDFs in Snowflake. Vectorization is typically associated with built-in functions or certain UDF types. Without vectorization, Java UDFs process rows individually, which can be slow for large datasets. Standard warehouses may not be optimized for memory-heavy UDFs.

Use a Snowpark Python user-defined function (UDF) executed on a Snowpark-optimized warehouse.

Why it fails: Snowpark Python UDFs are powerful, but they still execute row-by-row and may not be the most efficient for simple transformations. However, for complex logic, they can be optimized. But without vectorization, they may not fully leverage Snowflake's performance. Snowpark-optimized warehouses are memory-intensive but not necessarily the best for all UDF workloads.

Create a stream on the target table and use a task with a WHEN clause to process only new data.

Why it fails: This approach is more suited for change data capture (CDC) on existing tables, not for transforming incoming streaming data from Snowpipe. Streams capture changes to tables, not the ingestion stream itself. Using a stream on the target table would require the data to be already loaded, adding an extra step and latency.

Use a task that runs every minute to transform the data using a stored procedure.

Why it fails: A task running every minute introduces at least a one-minute latency, which may not meet low-latency requirements. Additionally, stored procedures can be less efficient for set-based transformations compared to native SQL. This approach also incurs costs for frequent task executions, and the latency might be unacceptable for streaming scenarios.

Create a materialized view that joins the stream with the dimension table and pre-aggregates the results.

Why it fails: Materialized views in Snowflake are not designed for streaming data and cannot reference streams. They are refreshed periodically and are best for static or slowly changing data. Using a materialized view here would introduce latency and would not capture real-time changes from Snowpipe, defeating the purpose of low-latency processing.

IFNULL(column, AVG(column) OVER (PARTITION BY category))

Why it fails: IFNULL is similar to COALESCE but only accepts two arguments. While it would work syntactically, the question specifies using a function that can handle the transformation. IFNULL is a valid alternative, but COALESCE is more standard and flexible. However, both would achieve the same result. Since the question asks for the function to use, COALESCE is the more general and recommended choice, but IFNULL is not incorrect. However, in Snowflake, IFNULL and COALESCE behave identically for two arguments. The key is that both are valid, but COALESCE is the ANSI standard. To avoid ambiguity, the correct answer is COALESCE because it is the most portable and widely used.

NVL(column, AVG(column) OVER (PARTITION BY category))

Why it fails: NVL is an Oracle-style function that Snowflake supports, but it is not the preferred function in Snowflake. It behaves like COALESCE for two arguments. However, using NVL might be considered less idiomatic in Snowflake. The question does not specify Oracle compatibility, so COALESCE is the better choice. NVL would technically work, but it is not the best answer because it is not the standard Snowflake function for this purpose.

NULLIF(column, AVG(column) OVER (PARTITION BY category))

Why it fails: NULLIF returns NULL if the two arguments are equal, otherwise it returns the first argument. This is the opposite of what is needed. It would replace values that equal the average with NULL, rather than replacing NULLs with the average. Therefore, it does not solve the problem and is incorrect.

Add a clustering key on the group-by columns to improve data pruning and reduce the number of rows processed.

Why it fails: Clustering on group-by columns can improve pruning for filtered queries, but it does not directly reduce the memory footprint of an aggregation. The aggregation still needs to process all rows that pass the filter. While pruning can reduce input size, it may not eliminate spilling if the aggregation itself requires more memory than available. Clustering is more effective for selective filters, not for reducing aggregation memory.

Enable the USE_CACHED_RESULT parameter to reuse previous aggregation results.

Why it fails: USE_CACHED_RESULT can speed up repeated identical queries by reusing results, but it does not help the first execution of a query that is spilling. Caching only applies when the exact same query has been run before and the underlying data has not changed. In this scenario, the query is likely new or data has changed, so caching would not reduce spilling during execution.

Increase the warehouse size to Large to provide more memory for the aggregation.

Why it fails: Increasing warehouse size would indeed provide more memory and likely reduce spilling, but the engineer explicitly wants to avoid increasing warehouse size. Therefore, this action does not meet the requirement. While it is a valid optimization, it is not the correct choice given the constraint. The goal is to optimize within the current warehouse size.

The PIVOT operator can only be used with a single aggregate function per pivot.

Why it fails: Snowflake's PIVOT operator supports multiple aggregate functions in a single pivot operation. You can specify multiple aggregates, such as SUM(revenue) AND AVG(revenue), and they will produce separate columns for each combination of pivot value and aggregate function. This flexibility allows more complex transformations without multiple PIVOT clauses.

The PIVOT operator preserves the original column names for the pivoted columns without any modification.

Why it fails: When pivoting, Snowflake generates new column names based on the pivot values and the aggregate function. For example, pivoting on 'month' with SUM(revenue) might produce columns like 'SUM_REVENUE_JAN' or similar, depending on the naming convention. The original column names are not preserved; new names are constructed to reflect the aggregation and pivot value.

The PIVOT operator can only pivot on a numeric column.

Why it fails: The pivot column can be of any data type that can be used in a grouping context, such as string, date, or numeric. In this scenario, 'month' is likely a string or date, not numeric. The aggregate column must be numeric for SUM, but the pivot column itself is not restricted to numeric types. This statement is false because it imposes an unnecessary limitation.

A stream can be consumed by multiple concurrent transactions without advancing the offset until each transaction commits.

Why it fails: A stream's offset advances only when a transaction that reads from it commits. Multiple concurrent transactions reading the same stream can cause conflicts, and the offset is not independently maintained per transaction. This statement misrepresents how stream offsets work and is incorrect.

A stream automatically purges change data after a fixed retention period regardless of consumption.

Why it fails: Stream change data is retained according to the underlying table's data retention period, but it is not purged independently of consumption. The offset ensures that changes are available until consumed and the retention period expires. This statement mischaracterizes stream retention and is incorrect.

A stream can be created on a view that joins multiple tables, and it will capture changes from all underlying tables.

Why it fails: Streams on views have restrictions; they cannot be created on views that join multiple tables in all cases, and the change tracking behavior is limited. The scenario requires capturing changes from a single table, so this statement is not generally true and is a common misconception about stream capabilities.

Rewrite the query to filter each table before the join to reduce the number of rows processed.

Why it fails: Filtering before the join can reduce the volume of data, which may help, but it is not always possible or sufficient. If the join is inherently large due to data skew or many-to-many relationships, filtering may not eliminate spilling. Remote spilling is often caused by uneven data distribution, not just total data volume. Therefore, this action may not be the most effective for the described issue.

Add a clustering key on the join columns of both tables.

Why it fails: Clustering on join columns can improve pruning and reduce the amount of data read, but it does not directly address the memory pressure during the join operation. Remote spilling occurs when the join operation exceeds memory and writes intermediate results to remote storage. While clustering may reduce input size, it may not eliminate spilling if the join itself is memory-intensive. Other techniques like reducing data skew are more directly effective.

Use a larger warehouse size to increase memory for the join.

Why it fails: Increasing warehouse size would provide more memory and likely reduce spilling, but the engineer explicitly wants to avoid increasing warehouse size. Therefore, this action does not meet the requirement. While it is a valid optimization, it is not the correct choice given the constraint. The goal is to optimize within the current warehouse size.

Disable the query acceleration service for the warehouse.

Why it fails: The query acceleration service offloads portions of a query to shared compute resources and is designed to improve performance for scans and certain predicates. It does not cause local spilling, and disabling it would remove a potential performance benefit. Spilling is a memory pressure symptom within the warehouse, not an effect of query acceleration, so this action would not resolve the spill.

Rewrite the query to use a LEFT JOIN instead of an INNER JOIN.

Why it fails: Changing the join type does not reduce the build-side size or the amount of data processed in a HashJoin. A LEFT JOIN may actually process the same or more rows depending on the data, and it does not change the fundamental memory requirement for the hash table. The spilling is caused by the build side not fitting in memory, which is independent of join semantics.

Change the warehouse to a Snowpark-optimized warehouse.

Why it fails: Snowpark-optimized warehouses provide more memory per node for memory-intensive workloads such as Python UDFs and Snowpark transformations. They are not the standard remedy for a SQL HashJoin spill, and they cost more credits per second. While they may reduce spilling by adding memory, they do not address the root cause of a large build side, and the credit cost may be unjustified for a batch SQL join.

Increase the warehouse size to improve concurrency.

Why it fails: Increasing warehouse size provides more compute resources per node, which can improve performance for individual queries, but it does not reduce the amount of data scanned. Concurrency is better addressed by using multi-cluster warehouses, which allow multiple clusters to handle concurrent queries. Scaling up a single warehouse does not inherently improve concurrency if the bottleneck is data scanning; it may help but is not as targeted as reducing scanned data through clustering.

Create a materialized view that pre-aggregates the data by the high-cardinality column.

Why it fails: A materialized view pre-aggregates data and can improve performance for specific queries, but it does not reduce the amount of data scanned for queries that filter on the high-cardinality column without using the pre-aggregated results. Materialized views are beneficial for repetitive aggregations, but they add storage and maintenance overhead. For ad-hoc filtering on a high-cardinality column, a clustering key is more effective because it organizes the data for pruning, directly reducing scanned bytes.

Enable the search optimization service on the high-cardinality column.

Why it fails: The search optimization service is designed to improve performance of point lookups and selective queries, but it is not intended for high-cardinality columns where queries scan large portions of the table. It maintains a search access path for equality and IN predicates, which can help with highly selective queries, but it does not reduce scanning for range filters or broad queries. For high-cardinality columns with frequent filtering, clustering is more appropriate.

Rewrite the query so the small dimension is the build side and rely on the optimizer to choose a more efficient join distribution.

Why it fails: The optimizer already chose a broadcast for this small dimension, and the profile shows the broadcast itself is expensive due to the size of the probe side. Simply reordering the tables does not change the fundamental data movement of broadcasting to every node. Without reducing the probe side or improving pruning, the network transfer and runtime remain high, so this does not address the observed bottleneck.

Convert the dimension table into a materialized view and join against the view instead.

Why it fails: Materializing the small dimension does not change its size, so the broadcast payload is essentially unchanged. The expensive part is the fact table's rows flowing through the join, not the dimension's computation. A materialized view also adds maintenance overhead for a table that is already tiny and updated daily, so this yields little benefit for the described bottleneck.

Increase the warehouse size to add more nodes so the broadcast join finishes faster.

Why it fails: A larger warehouse adds compute nodes, but a broadcast join sends the small table to every node, and the dominant cost is the large probe side being processed and transferred. More nodes can help somewhat, but the fundamental data movement remains proportional to fact rows. It is a costly approach that does not remove the underlying inefficiency, so it is not the best fix.

Add a clustering key on the join column of the larger table to improve join pruning and reduce data movement.

Why it fails: Clustering on the join column can improve pruning when the join predicate is highly selective, but it does not guarantee reduced build-side size or remote spilling. The build side is determined by the table chosen by the optimizer, and clustering does not change that choice. It may help with filter predicates, but here the issue is join memory, not scan pruning.

Rewrite the query to use a UNION ALL instead of a join to avoid the build side entirely.

Why it fails: UNION ALL concatenates result sets and does not perform matching between tables, so it cannot replace a join that correlates rows. Using it would produce incorrect results and would not address the memory pressure of a join build side. It also does not reduce remote spilling because the operation changes semantics rather than optimizing the join.

Disable the join spill-to-disk feature by setting the ENABLE_JOIN_SPILL parameter to FALSE.

Why it fails: Snowflake does not expose an ENABLE_JOIN_SPILL parameter. Spilling is an automatic behavior of the execution engine when operators exceed memory. Disabling it is not possible and would likely cause query failures if memory were exceeded. The correct approach is to reduce memory demand, not to disable a safety mechanism.

Add a clustering key on the 'sales' table on the join key 'product_id'.

Why it fails: Clustering the large fact table on the join key can improve pruning for filters on that key, but the query filters on 'products.category', not on 'sales.product_id'. Clustering may not help the join itself because the join is already a broadcast join, which is efficient for small dimension tables. The bottleneck is likely the aggregation or the filter on the dimension, not the join method.

Add a search optimization service to the 'sales' table to speed up point lookups on 'product_id'.

Why it fails: Search optimization service is designed for selective point lookups, not for large-scale joins and aggregations. It adds overhead and does not change the join strategy. For this analytical query, it would not improve performance and could even degrade it due to maintenance costs. The join is already using an efficient broadcast method.

Rewrite the query to use a LEFT JOIN instead of an INNER JOIN to reduce the amount of data processed.

Why it fails: Changing to a LEFT JOIN would not reduce data processed; it would include all rows from the left table, potentially increasing the result set. The original query likely uses an INNER JOIN, which is more restrictive. The join type does not influence the broadcast join decision; the optimizer chooses based on table sizes and statistics.

Disable the join by rewriting the query to use a correlated subquery instead.

Why it fails: Converting the join to a correlated subquery changes the execution plan but does not reduce the underlying data movement. Snowflake's optimizer may still internally perform a join-like operation, and correlated subqueries over large tables can be slower. This does not address the root cause of remote spilling, which is data distribution relative to the join key.

Increase the warehouse size to add more compute nodes and memory.

Why it fails: Scaling up adds memory and compute, which can reduce local spilling, but the profile indicates remote spilling driven by a high-cardinality join and poor partitioning alignment. Without addressing the join key distribution or clustering, a larger warehouse may only shift the bottleneck and increase cost rather than eliminate remote spilling for this specific join.

Set the JOIN_MEMORY_LIMIT parameter to allow more memory for the join operator.

Why it fails: Snowflake does not expose a JOIN_MEMORY_LIMIT parameter for tuning individual join operators. Memory allocation is managed by the warehouse and query optimizer. Attempting to set a non-existent parameter will fail, and it would not resolve the remote spilling caused by high-cardinality data distribution and lack of clustering alignment.

Reduce the warehouse size to decrease the number of nodes competing for memory.

Why it fails: Reducing warehouse size decreases memory per node and total memory, which increases the likelihood of spilling. It would not reduce remote spilling; it would make memory pressure worse. The correct direction is to scale up, not down, when the goal is to eliminate spilling for memory-intensive queries that cannot be rewritten.

Disable the query result cache to free memory for spilling operations.

Why it fails: The query result cache stores completed results and does not consume the memory used by active query operators. Disabling it would not free memory for spilling and would actually remove a performance benefit for repeated queries. It does not address the root cause of remote spilling, which is insufficient memory or poor data distribution during execution.

Set the ENABLE_REMOTE_SPILLING parameter to FALSE to force local spilling only.

Why it fails: Snowflake does not provide an ENABLE_REMOTE_SPILLING parameter to disable remote spilling. Spilling behavior is managed automatically based on available local and remote storage. Attempting to set a non-existent parameter would fail, and forcing local-only spilling would not be possible or beneficial if local disk is already insufficient.