Skip to main content
DatabricksPySparkSnowflakeApache Airflow

Snowflake Intermediate SQL & Semi-Structured Analytics Assessment

Modern data engineering on Snowflake requires advanced SQL proficiency: orchestrating multi-step transformations with CTEs, decomposing nested JSON arrays using FLATTEN and LATERAL joins, calculating partition-level analytics with window functions, querying cloud object storage directly, and merging incremental batches using MERGE INTO.

1. Assessment Overview & Prerequisites​

This assessment evaluates your ability to write performant transformation SQL in Snowflake. Topics include CTE syntax with WITH, array unnesting via FLATTEN, dot and bracket JSON traversal, cumulative running totals and dense rankings using OVER (), querying external stage files directly via @stage, and building atomic upsert pipelines with MERGE INTO.

2. What You Will Practice & Validate​

  • Common Table Expressions (CTEs): Structure modular, readable, and optimizer-friendly queries using WITH cte_name AS (...).
  • Semi-Structured Array Flattening: Unpack JSON arrays and nested object lists into tabular rows using LATERAL FLATTEN().
  • Window Analytical Functions: Compute running totals, moving partitions, and top-N rankings with RANK(), ROW_NUMBER(), and OVER().
  • Staged File Querying & Upserts: Directly select from external stages and merge changes into destination tables atomically.

3. How to Use This Assessment & Practice Guide​

Analyze each SQL scenario for syntax correctness, execution mechanics, and optimization patterns. Pay close attention to how Snowflake handles VARIANT columns and lateral table joins.

4. Target Technical Concept Matrix​

Architectural AreaOperational PurposeProduction Significance
WITH Clause (CTE)Encapsulates reusable temporary result sets for complex multi-stage pipelines.Improves query readability; the Snowflake cost-based optimizer can inline or materialise CTEs as needed.
FLATTEN Table FunctionExplodes a JSON array or VARIANT object into relational rows containing index, key, value, and path.Enables relational querying of deeply nested arrays without pre-parsing.
LATERAL JoinAllows an inline table function (like FLATTEN) to reference columns from preceding tables in the FROM clause.Essential syntax for joining a table to its own exploded semi-structured arrays.
MERGE INTO DMLPerforms conditional INSERT, UPDATE, and DELETE operations in a single atomic SQL transaction.The foundational construct for slowly changing dimensions and CDC pipeline loading.

5. Interactive Knowledge Assessment​

1. How do you create a CTE (Common Table Expression) in Snowflake?

2. Which SQL function is used to flatten a VARIANT/JSON array in Snowflake?

3. How do you query a nested JSON field customer.name stored in VARIANT column data?

4. How do you calculate a running total using a window function?

5. How do you get the top 3 highest sales per region using window functions?

6. How do you query a Parquet file stored in a stage in Snowflake?

7. How do you extract all values from a JSON array stored in VARIANT column data.items?

8. How do you create an external stage in Snowflake?

9. How do you extract the month from a DATE column order_date?

10. How do you merge new data into an existing table in Snowflake?

6. Deep Dive & Architectural Analysis​

FLATTEN Table Function Output Structure​

The FLATTEN() function returns six standard columns for each exploded item:

  • SEQ: A unique integer sequence number associated with each input row.
  • KEY: For objects, the string key name. NULL for arrays.
  • PATH: The hierarchical path string to the element.
  • INDEX: For arrays, the 0-indexed element position. NULL for objects.
  • VALUE: The element value (stored as VARIANT).
  • THIS: The parent array or object being flattened.

Querying Staged Files Without Ingestion​

Snowflake allows direct metadata and data queries on cloud storage files prior to loading:

-- Direct query on external S3 stage with column projection
SELECT 
    $1:order_id::STRING AS order_id,
    $1:customer_name::STRING AS customer,
    $1:total_amount::NUMBER(12,2) AS total,
    METADATA$FILENAME,
    METADATA$FILE_ROW_NUMBER
FROM @raw_sales_stage/2026/03/orders.parquet
WHERE $1:total_amount > 1000;

This enables validation of incoming schemas, row counts, and data quality before triggering warehouse-intensive COPY INTO pipelines.

7. Real-World Production Scenarios & Pitfalls​

⚠️ Non-Deterministic Matches in MERGE Statements​

  • Failure Mode: Executing a MERGE when the source table contains duplicate join keys matching a single target row.
  • Impact: The MERGE query fails with error: 001007 (21000): SQL execution error: Duplicate row detected during DML operation.
  • Production Remedy: Deduplicate the source table using QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC) = 1 before passing it into USING.

⚠️ Omitting LATERAL when Flattening Arrays​

  • Failure Mode: Writing SELECT f.value FROM my_table, FLATTEN(input => my_table.items) f on older SQL engines.
  • Impact: Without LATERAL, the table function cannot reference preceding columns in the FROM clause, throwing an unknown identifier error.
  • Production Remedy: Explicitly specify LATERAL FLATTEN(input => ...) for robust portability.

8. Canonical Tutorial Reference​

Review the canonical tutorials covering these exact architectures before or after taking this assessment:

9. Next Steps in Your Learning Progression​

Advance to the Snowflake Performance, Clustering & Scaling Quiz covering micro-partitions, clustering keys, query profile, and caching.

10. Key Takeaways & Architectural Checklist​

  • FLATTEN Produces Typed Rows: Use LATERAL FLATTEN to unpack arrays while preserving parent table attributes.
  • Stage Files Are Queryable: Validate S3/GCS/Azure files in-place using $1 notation before loading into tables.
  • MERGE Powers Atomic CDC: Implement SCD2 and upsert patterns with clean WHEN MATCHED and WHEN NOT MATCHED logic.