Performance Optimization, Querying, and Transformation

A data engineer needs to build a continuously refreshing transformation that joins raw order and customer tables and materializes the aggregated result. The business requires the transformed data to never be more than 5 minutes stale, and the team wants to avoid writing and scheduling custom tasks or streams to manage incremental refresh logic. Which Snowflake object best meets these requirements?

A data engineer needs to maintain a pre-computed table that joins three large source tables and applies several aggregations. The result must be automatically refreshed as the source data changes, without writing custom scheduling or task logic, and must support multi-table joins. Which Snowflake object best fits these requirements?

A data engineer has a table EVENTS with a VARIANT column PAYLOAD. Each row's PAYLOAD contains a JSON object with a top-level array field named 'items', where each element has 'sku' and 'qty' attributes. The engineer needs a result set with one row per item, exposing the SKU and quantity as separate columns alongside the event's ID. Which query construct correctly produces this output?

A data engineer is transforming a raw table named ORDERS that has a VARIANT column ITEMS containing a JSON array of line items. Some order rows have an empty ITEMS array or a NULL ITEMS value. The engineer writes a query using LATERAL FLATTEN to explode the array into one row per line item, but notices that orders with empty or NULL arrays disappear entirely from the result set. The requirement is to keep exactly one row for every order, even when it has no line items (returning NULL for the line-item columns in that case). Which change should the engineer make to the FLATTEN call?

A data engineer loads deeply nested JSON documents into a VARIANT column. Each document contains an 'employees' array, and each employee element itself contains a nested 'projects' array of objects. The engineer needs a single query that returns one row per project, with columns for the employee name and the project name. Which approach correctly produces this result?

A data engineer needs to create a scalar UDF that parses a URL string, extracts the query parameters into a set of key-value pairs, and returns a formatted VARIANT. The logic requires procedural string manipulation with loops and conditional branching that is awkward to express in pure SQL. Performance on large row counts is a concern, but the function must remain a scalar UDF callable in SELECT statements. Which approach best fits these requirements?

A data engineer creates a materialized view on a very large fact table to speed up a frequently run dashboard aggregation. After deployment, the base table receives heavy, continuous DML (thousands of inserts and updates per hour). The team notices unexpected serverless credit consumption even during periods when no one is querying the dashboard. What is the MOST likely cause of this credit consumption?

A data engineer maintains a large fact table with a materialized view that pre-aggregates daily sales by region. Queries against the materialized view have grown slow because they filter on a specific region, and the engineer wants to improve partition pruning on the materialized view itself. Which action is supported in Snowflake to address this?

An analytics team runs the same expensive aggregation query dozens of times per hour against a very large, slowly-changing fact table. The query scans billions of rows and computes SUM and COUNT grouped by a low-cardinality region column. They want to dramatically reduce compute cost and latency for these repeated aggregations, and they accept a small amount of maintenance cost when the base table changes. Which Snowflake feature best fits this requirement?

A data analyst runs 'SELECT COUNT(*) FROM sales_transactions;' against a very large table with no filters. The query returns almost instantly, and the Query Profile shows a single 'METADATA-BASED RESULT' step with no compute processing. The warehouse was suspended at the time and did not resume. Which Snowflake caching or optimization mechanism is responsible for this behavior?

A data engineer loads raw event data into a table where the payload column is defined as VARCHAR and contains valid JSON text strings. Analysts complain that they cannot use dot-notation or colon-path access (e.g., payload:user.id) to extract fields directly from this column. What is the most appropriate transformation to enable semi-structured path access on this data?

A data analyst runs an ad hoc analytics workload on a MEDIUM warehouse. Most queries complete quickly, but a few unpredictable, scan-heavy queries take far longer because they process large amounts of data. The team does not want to permanently resize the warehouse, since that would raise costs for the many small queries. The Query Profile for the slow queries shows most time spent in TableScan operations with no significant spillage. Which Snowflake feature best addresses these occasional large scan-heavy outlier queries without increasing the base warehouse size?

A data engineer notices that the first execution of a heavy analytical query each morning takes 40 seconds and scans nearly all micro-partitions from remote storage. Subsequent runs of the same query during the day complete in about 6 seconds. The warehouse has AUTO_SUSPEND set to 60 seconds and is idle overnight. The query itself is not eligible for the result cache because it references CURRENT_TIMESTAMP(). Which explanation BEST accounts for the slow first-of-the-day run compared to later runs?

A data engineer reviews the Query Profile for a slow-running aggregation query on a Medium warehouse. The profile shows a large value for 'Bytes spilled to local storage' but zero for 'Bytes spilled to remote storage'. The query completes, but the engineer wants to understand what this indicates and how to improve performance. Which statement BEST describes the situation and the most appropriate remediation?

A data analyst runs a dashboard query and inspects its Query Profile. The profile shows a single node labeled 'QUERY RESULT REUSE' and reports that the query executed in under a second with no bytes scanned. The analyst is surprised because the underlying table contains billions of rows. Which conclusion is correct about what happened?

An analyst reports that a reporting query joining a 10-million-row ORDERS table with a 500-row REGIONS table is running far slower than expected and consuming excessive memory. In the Query Profile, you notice the Join node shows an output row count of roughly 5 billion rows, and a filter is applied only after the join. What is the MOST likely cause and correct remediation?

A data engineer notices that a large aggregation query on a Medium warehouse is running much slower than expected. Reviewing the Query Profile, they see significant values under both 'Bytes spilled to local storage' and 'Bytes spilled to remote storage'. What does the remote spillage specifically indicate, and what is the most appropriate first remediation?

A data engineer notices a query joining an orders table (10 million rows) with a shipments table (12 million rows) is running far slower than expected. In the Query Profile, the engineer sees that a single Join operator outputs roughly 480 million rows, and a large amount of bytes are spilled to local and remote storage. The join condition uses only a partial key. What is the MOST likely cause of the performance problem revealed by the Query Profile?

A data engineer is troubleshooting a slow-running analytical query. She opens the Query Profile in Snowsight and notices that one operator node accounts for 78% of the total execution time. She wants to systematically identify and address the single biggest performance bottleneck in the query. Which element of the Query Profile is the MOST direct indicator she should use to locate that bottleneck?

A data engineer is investigating a slow-running dashboard query against a 2 TB table. In the Query Profile, they examine the TableScan operator and see: 'Partitions scanned: 48,120' and 'Partitions total: 48,200'. The WHERE clause filters on an event_date column. What does this observation most strongly indicate?