What a warehouse actually is

A virtual warehouse is a named, independently-billed cluster of compute resources (CPU, memory, local SSD cache). It does zero query optimization itself — that already happened in cloud services — it just executes the compiled plan against the pruned set of micro-partitions.

Warehouse sizing (T-shirt sizes)

Each size doubles the previous size's compute — and doubles cost per credit-hour. Sizing up doesn't make a single row scan faster; it adds more parallel workers for a query that can actually be split across many partitions. A query touching 3 micro-partitions gets no benefit from an XL warehouse over an XS.

Multi-cluster warehouses

A single warehouse "size" controls how much compute one cluster has. Multi-cluster controls how many copies

CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5
  SCALING_POLICY = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

• STANDARD scaling policy: favors spinning up a new cluster fast to avoid queueing.
• ECONOMY scaling policy: waits, trying to pack more queries into existing clusters before adding one — saves credits, adds latency.

Auto suspend / auto resume

Auto suspend stops billing after N seconds of inactivity (default 600s, often tuned to 60s for spiky workloads). Auto resume instantly wakes the warehouse on the next query — the first query after resume pays a small "cold cache" penalty since local SSD cache was cleared.

Concurrency scaling in practice

Resource monitors — the cost guardrail

CREATE RESOURCE MONITOR monthly_guardrail
  WITH CREDIT_QUOTA = 1000
  FREQUENCY = 'MONTHLY'
  START_TIMESTAMP = 'IMMEDIATELY'
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = monthly_guardrail;

SUSPEND lets running queries finish but blocks new ones; SUSPEND_IMMEDIATE kills everything mid-query — reserve that for a true hard stop.

Scaling strategy cheat sheet

Compared to other systems

PostgreSQL has no concept of "add a warehouse" — you scale by upgrading the one instance or adding read replicas manually. Spark clusters (EMR/Databricks) require you to configure executors, memory, and shuffle partitions by hand; Snowflake warehouses abstract all of that behind a T-shirt size.

What a micro-partition is

When data lands in a Snowflake table, it's automatically sliced into contiguous chunks of 50–500MB (uncompressed) called micro-partitions, stored compressed and columnar. Every insert, bulk load, or update creates one or more new micro-partitions — you never touch this directly.

Internal metadata per partition

Partition pruning, visualized

Imagine a table of 24 micro-partitions ordered roughly by order_date. A query filters WHERE order_date = 'March':

How inserts create partitions

A bulk COPY INTO or large INSERT writes new, naturally time-ordered micro-partitions — this is why a freshly loaded, append-only table (like an event log by timestamp) often has excellent natural clustering with zero manual effort.

How updates affect partitions

Micro-partitions are immutable. An UPDATE never edits bytes in place — Snowflake reads the affected partition(s), rewrites a new partition with the changed rows, and marks the old partition as no-longer-current (retained for Time Travel). Updating 1 row in a 500MB partition still rewrites the whole partition.

How deletes affect partitions

Same story: a DELETE rewrites the surviving rows of any affected partition into new partitions. The old partition isn't physically erased immediately — it lives on for Time Travel and Fail-safe windows (Topics 38–39) before being reclaimed.

-- Both of these trigger partition rewrites, not in-place edits:
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 88213;
DELETE FROM orders WHERE order_date < '2022-01-01';

Re-clustering impact

Over time, updates/deletes/out-of-order loads scatter values across many partitions, so a range filter that used to hit 3 partitions might now hit 30 — pruning degrades. Re-clustering physically rewrites partitions to restore sorted locality (covered fully in Topic 4).

Compared to other systems

If you've used Delta Lake or Iceberg, micro-partitions will feel familiar — Snowflake essentially pioneered this immutable-file-with-rich-metadata pattern before the open table formats existed (and Snowflake now natively supports Iceberg tables too — Module 11).

Why clustering keys exist

Pruning (Topic 3) only works well when a column's values are grouped together in a small number of partitions. If a huge table gets random inserts and updates for years, values for any given column scatter across thousands of partitions — a clustering key tells Snowflake which column(s) to keep physically sorted so pruning stays effective.

Automatic clustering

Once you define a clustering key, Snowflake runs a background service that continuously re-clusters the table as new data arrives or drifts out of order — you don't schedule or trigger it. It costs credits (billed as serverless compute), separate from your warehouses.

ALTER TABLE orders CLUSTER BY (order_date, region);

Manual clustering (legacy pattern)

Before automatic clustering matured, engineers would periodically run ALTER TABLE ... RECLUSTER or rewrite the table sorted by key via CTAS. This is rarely needed today — automatic clustering is the default recommendation — but you'll still see it in older pipelines.

Cluster depth — the health metric

Cluster depth measures, on average, how many micro-partitions overlap for a given range of clustering key values. Depth of 1 is perfect (no overlap); depth of 50 means a typical range filter has to scan 50 partitions where 1–2 would do.

SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(order_date)');

Choosing a clustering key

Compared to other systems

This maps closely to partitioning in PostgreSQL/Spark (physically splitting data by a key) but is more like continuous background sorting than a hard structural boundary — you can change a clustering key anytime without redesigning the table, unlike a partitioned table in Postgres which requires structural migration.

Three separate caches, three separate lifetimes

People say "Snowflake caching" as if it's one thing. It's actually three independent caches living in different layers, each solving a different problem.

1. Result cache — deep dive

• Lives 24 hours from last access (each hit resets the clock, up to 31 days max).
• Shared across the whole account — any user, any warehouse, even a brand-new XS warehouse gets a cached result instantly with zero compute cost.
• Invalidated the moment underlying table data changes (new rows, updates, deletes) — not by time filters like CURRENT_TIMESTAMP() in the query, which actually prevents caching since the query text changes meaning each run.

2. Metadata cache — deep dive

This is what makes SELECT COUNT(*) FROM huge_table return instantly — the row count is stored in metadata, no partitions need scanning. Same for MIN()/MAX() on a column when the whole answer is derivable from partition-level stats.

-- Answered entirely from metadata, zero warehouse compute:
SELECT COUNT(*), MIN(order_date), MAX(order_date)
FROM orders;

3. Warehouse (local SSD) cache — deep dive

Each running warehouse caches the raw micro-partition files it reads on local SSD. A second query hitting the same partitions is faster purely from disk I/O avoidance — but this cache disappears the instant the warehouse suspends, which is the real tradeoff behind aggressive auto-suspend settings (Topic 2).

Cache invalidation triggers

• Any DML on the underlying table (insert/update/delete/merge) invalidates result cache for queries against it.
• Changing session parameters that affect output (e.g. a different TIMEZONE) breaks result cache reuse.
• Using non-deterministic functions (CURRENT_TIMESTAMP, RANDOM()) disables result caching for that query entirely.

Compared to other systems

PostgreSQL has a shared buffer cache (pages in memory) but no built-in cross-session result cache — you'd bolt one on with something like pgBouncer or an app-level cache. Spark has no persistent result cache across jobs by default. Snowflake's three-tier caching is one of its most underrated cost levers: a well-cached dashboard workload can run almost entirely on metadata and result cache with minimal warehouse time.

The problem it solves

Micro-partition pruning (Module 1) works great for range filters on a well-clustered column. It works poorly for high-cardinality point lookups — WHERE customer_id = 'X' or WHERE email = 'y@z.com' — on a huge table, because clustering can only optimize one or two columns at a time, and a needle-in-a-haystack lookup on a non-clustered column has to scan far more partitions than it should.

How it works internally

Enabling search optimization builds and maintains a persistent, serverless-compute-backed index structure alongside the table — for equality lookups it's a variant of a bloom-filter/point-lookup index that lets the optimizer skip straight to the exact partitions containing a value, independent of clustering order.

ALTER TABLE customers ADD SEARCH OPTIMIZATION
  ON EQUALITY(customer_id, email);

What it accelerates

Cost model

It's billed as ongoing serverless compute to build and maintain the index (similar to automatic clustering's billing model) plus additional storage for the index structures. It's worth it only when point-lookup queries are frequent and expensive without it — always validate with a before/after query profile.

Compared to other systems

This is the closest Snowflake analog to a traditional B-tree secondary index in PostgreSQL — but it's serverless, maintained automatically, and billed on usage rather than being a fixed structure you build once and forget.

What problem it solves

A regular view re-runs its underlying query every single time it's selected from — no precomputation. A materialized view (MV) precomputes and stores the result physically, as its own set of micro-partitions, and Snowflake keeps it automatically in sync as the base table changes.

Refresh mechanics

Refreshing isn't a full recompute — Snowflake uses incremental maintenance, updating only the micro-partitions of the MV affected by changes to the base table, via a background serverless process. This is why MVs stay cheap to maintain on append-heavy tables and more expensive on tables with scattered updates/deletes.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT order_date, region, SUM(amount) AS revenue
FROM orders
GROUP BY order_date, region;

Constraints worth knowing

• One base table only — no joins across multiple tables in a single MV.
• A limited set of aggregate functions is supported (no arbitrary window functions or subqueries).
• Cannot reference another view or another materialized view.

Cost model

When it's worth it

Compared to other systems

This same automatic-incremental-refresh idea reappears later in a more general, DAG-aware form with Dynamic Tables (Module 5) — MVs are the single-table special case; dynamic tables generalize it to arbitrary multi-table SQL.

The difference from masking

Masking hides a column's value for a visible row. A row access policy hides the entire row — the querying role never sees it exists at all, as if it were filtered out by a hidden WHERE clause applied to every query automatically.

Department-wise filtering example

CREATE ROW ACCESS POLICY dept_filter AS (dept STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'GLOBAL_ADMIN'
  OR dept IN (
    SELECT allowed_dept FROM role_dept_mapping
    WHERE role_name = CURRENT_ROLE()
  );

ALTER TABLE sales_orders
  ADD ROW ACCESS POLICY dept_filter ON (department);

A sales rep in the EMEA role now sees only EMEA rows in every query, join, and aggregate against this table — automatically, with no application-layer filtering logic to maintain or forget.

Fine-grained security in practice

Combining with masking

Row access policies and masking policies stack independently — a role might see a restricted subset of rows (row policy) with some columns masked within those rows (masking policy). They're evaluated together, transparently, on every query.

Compared to other systems

This maps closely to PostgreSQL's Row-Level Security (RLS) feature — conceptually near-identical — but Snowflake's policies are first-class shareable objects that can be attached to many tables at once, rather than per-table policy definitions.

The scaling problem it solves

Attaching a masking policy column-by-column works for a handful of tables. A real enterprise account has thousands of PII columns across hundreds of tables — manually attaching policies to each doesn't scale, and auditing "where is PII masked?" becomes guesswork.

How it works

CREATE TAG pii_category ALLOWED_VALUES 'email', 'ssn', 'phone';

ALTER TABLE customers MODIFY COLUMN email
  SET TAG pii_category = 'email';

-- One policy, applied via tag, covers every column tagged 'email'
-- across the entire account:
ALTER TAG pii_category SET MASKING POLICY email_mask;

Classification & governance workflow

• Data stewards tag sensitive columns as they're created (or run auto-classification to suggest tags — covered under Horizon in Module 11).
• Security teams write masking/access policies once per tag category, not per column.
• Auditors can query ACCOUNT_USAGE views to see exactly which columns carry which sensitivity tags account-wide — a single source of truth for compliance reporting.

Compared to other systems

Most other warehouses treat classification and enforcement as separate tools (a data catalog for tags, a completely different mechanism for enforcement). Snowflake's tag-based policies unify classification and enforcement in one object model.

The problem with regular views

A standard view's definition is visible to anyone with sufficient privilege via SHOW VIEWS or the query optimizer's plan output — and worse, the optimizer is allowed to "look through" the view into the base table's structure to plan more efficient joins, which can leak information about the underlying data through query timing or error messages, even if the view itself never returns forbidden rows.

Internal mechanics

Marking a view SECURE does two things: it hides the view's DDL/definition from users who only have SELECT (not OWNERSHIP) on it, and it disables certain optimizer rewrites that would otherwise push predicates from an outer query down into the view in ways that could infer restricted data through side channels (timing, error messages).

CREATE SECURE VIEW customer_summary AS
SELECT region, COUNT(*) AS customer_count
FROM customers
WHERE deleted_at IS NULL
GROUP BY region;

Performance tradeoff

Compared to other systems

PostgreSQL has no equivalent concept — a Postgres view's plan can always be inlined into the outer query by the optimizer. Secure views are a Snowflake-specific answer to a Snowflake-specific optimizer behavior, and they're required for any view involved in secure data sharing (Topic 14).

Why UDFs need the same treatment as views

A UDF's SQL or JavaScript source code is visible via DESCRIBE FUNCTION to anyone with usage privilege by default — which is fine for a shared utility function, but a problem if the function embeds proprietary business logic (e.g. a pricing algorithm) or if you're sharing the function with an external consumer who shouldn't see the implementation.

Creating one

CREATE SECURE FUNCTION calculate_discount(tier STRING, amount NUMBER)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
  amount * CASE tier
    WHEN 'GOLD' THEN 0.85
    WHEN 'SILVER' THEN 0.92
    ELSE 1.0
  END
$$;

Same optimizer tradeoff as secure views

Just like secure views, marking a UDF secure prevents certain optimizer inlining/pushdown behaviors that could otherwise leak information about the function's internals through query performance characteristics — with the same modest performance cost in exchange.

When to use it

The core idea: share pointers, not copies

Secure Data Sharing lets one Snowflake account grant another Snowflake account live, read-only access to specific databases, tables, or secure views — without copying a single byte. The consuming account queries the provider's actual storage layer directly through their own compute.

Internal mechanics

Because storage is decoupled from compute (Module 1), and every table is already a set of immutable micro-partitions with metadata, sharing is just a metadata-layer grant — the provider's cloud services layer authorizes the consumer's cloud services layer to resolve pointers into the same underlying files. Zero data movement, zero duplication, zero storage cost for the consumer.

CREATE SHARE partner_share;
GRANT USAGE ON DATABASE analytics TO SHARE partner_share;
GRANT SELECT ON analytics.public.customer_summary TO SHARE partner_share;
ALTER SHARE partner_share ADD ACCOUNTS = partner_account_id;

Reader accounts vs. full accounts

If the consumer doesn't already have a Snowflake account, the provider can spin up a reader account on their behalf — a lightweight Snowflake account where the provider pays for the reader's compute, letting non-Snowflake customers still consume shared data (previewed briefly here; Module 11 covers reader accounts and the data marketplace in full).

Why secure views matter here

You almost never share a raw table directly — you share a secure view that pre-filters columns/rows appropriate for the external party, so the consumer never sees more than intended and the optimizer boundary (Topic 12) prevents them from inferring hidden data.

Compared to other systems

Traditional data sharing (SFTP exports, API extracts, replicated copies to a partner's warehouse) always creates data staleness and duplication cost. Delta Sharing (from Databricks) pursues a similar zero-copy philosophy but as an open protocol across engines; Snowflake's native sharing is deeply integrated but historically Snowflake-to-Snowflake (with reader accounts bridging the gap).

The last layer: who can even connect

Everything so far (masking, row policies, secure views, sharing) governs what an authenticated session can see. Network policies govern whether a connection is allowed to authenticate at all, based on IP address — the outermost perimeter.

Creating one

CREATE NETWORK POLICY corp_office_only
  ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.5')
  BLOCKED_IP_LIST = ('203.0.113.10');

ALTER USER jsmith SET NETWORK_POLICY = corp_office_only;
-- Or applied account-wide:
ALTER ACCOUNT SET NETWORK_POLICY = corp_office_only;

Scope levels

Blocked list takes precedence

If an IP appears in both the allowed and blocked list (as in the example above, where .10 falls inside the allowed /24 range), the block always wins — this is a deliberate fail-closed design so you can carve out an exception within a broader trusted range.

Compared to other systems

Comparable to PostgreSQL's pg_hba.conf IP-based rules, but managed as a live, alterable SQL object rather than a config file requiring a server restart — changes to a network policy take effect on the next authentication attempt, no downtime.

The problem it solves

A JSON column might hold an array of line items inside a single order row. SQL is fundamentally row-and-column shaped — it has no native way to turn "one row containing an array of 5 things" into "5 rows." FLATTEN does exactly that; LATERAL is what lets it reference the current row's column while doing it.

Basic syntax

SELECT o.order_id, f.value:item_name::STRING AS item_name,
       f.value:quantity::NUMBER AS quantity
FROM orders o,
     LATERAL FLATTEN(input => o.line_items) f;

Each row of orders with an array of, say, 3 line items produces 3 output rows — f.value holds one array element per row, and o.order_id repeats across all of them since it's carried through by the lateral join.

Nested objects — going deeper

-- variant column: {"customer": {"address": {"city": "Austin", "zip": "78701"}}}
SELECT raw:customer:address:city::STRING AS city,
       raw:customer:address:zip::STRING AS zip
FROM raw_events;
-- Colon-chaining walks nested object levels without FLATTEN,
-- since this is object access, not array unpacking.

Flatten vs. plain colon access

Performance impact

Flattening explodes row counts — a table of 1M orders averaging 4 line items each produces 4M output rows. This is normal and expected, but be deliberate about filtering before or after the flatten depending on whether the filter applies to the outer row or the array element, to avoid materializing more exploded rows than necessary.

Compared to other systems

This is the SQL equivalent of Spark's explode() function on an array column, or Postgres's jsonb_array_elements() combined with a lateral join. Snowflake's VARIANT type plus native FLATTEN is generally considered more ergonomic than Postgres's JSONB operators for deeply nested, irregular JSON.

The problem it solves

You already know you can't put a window function directly in a WHERE clause — WHERE is evaluated before window functions exist. The usual workaround is wrapping the query in a subquery or CTE just to filter on the window result. QUALIFY removes that boilerplate: it's a WHERE clause that runs specifically after window functions are computed.

Without QUALIFY (the old way)

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
WHERE rn = 1;

With QUALIFY (the clean way)

SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
QUALIFY rn = 1;
-- Same result, no wrapping subquery needed — this is Topic 18's
-- single most common real-world use: "get the latest row per group."

Internal execution order — where QUALIFY sits

Compared to other systems

QUALIFY is not standard ANSI SQL — it originated in Teradata and was adopted by Snowflake and DuckDB. PostgreSQL and Spark SQL don't support it; you'll still need the subquery/CTE wrapper pattern there.

What a stream actually is

A stream is not a copy of data and not a message queue — it's a pointer (an offset) against a table's change history, plus metadata columns that get computed on read. It leans directly on the same immutable micro-partition versioning that powers Time Travel (Module 8): every insert/update/delete already creates new partition versions, and a stream just remembers "which version did I last look at."

Creating and reading a stream

CREATE STREAM orders_stream ON TABLE orders;

-- Some time later, after inserts/updates/deletes happened on orders:
SELECT * FROM orders_stream;
-- Returns only the changed rows since the stream's last consumed offset,
-- plus metadata columns: METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID

Insert / update / delete capture — the metadata columns

An UPDATE shows up as a paired DELETE (before-image) + INSERT (after-image) with METADATA$ISUPDATE = TRUE on both — this is how you distinguish "this row changed" from "this row was deleted and a coincidentally similar one was inserted."

Offset tracking internals

The offset advances only when the stream is read inside a DML transaction that also consumes it — typically via INSERT INTO ... SELECT * FROM stream or a task that reads it. A plain, isolated SELECT against the stream does not advance the offset — you can peek repeatedly without losing the data. Offset only moves forward once a transaction reading the stream successfully commits.

Compared to other systems

This is Snowflake's native answer to Change Data Capture (CDC) — comparable in purpose to Debezium reading a database's write-ahead log, or Kafka Connect's CDC connectors, but requiring zero external infrastructure since it's built directly on Snowflake's own storage versioning.

What a task is

A task is a scheduled or event-triggered execution of a single SQL statement or stored procedure call — Snowflake's built-in cron, running on either a dedicated warehouse or Snowflake-managed serverless compute.

Basic scheduling

CREATE TASK refresh_summary
  WAREHOUSE = etl_wh
  SCHEDULE = 'USING CRON 0 2 * * * UTC'   -- 2 AM daily
AS
  INSERT INTO daily_summary SELECT * FROM compute_summary();

ALTER TASK refresh_summary RESUME;  -- tasks are created suspended by default

Dependency chaining (task graphs / DAGs)

CREATE TASK load_raw
  WAREHOUSE = etl_wh
  SCHEDULE = 'USING CRON 0 1 * * * UTC'
AS CALL load_raw_data();

CREATE TASK transform_data
  WAREHOUSE = etl_wh
  AFTER load_raw           -- runs only after load_raw succeeds
AS CALL transform_data();

CREATE TASK publish_marts
  WAREHOUSE = etl_wh
  AFTER transform_data
AS CALL publish_marts();

Only the root task (load_raw) needs a SCHEDULE — every downstream task is triggered purely by its predecessor's completion, forming a DAG. This is directly comparable to an Airflow DAG, but defined and executed entirely inside Snowflake.

Serverless vs. warehouse-backed tasks

Compared to other systems

Tasks are Snowflake's lightweight alternative to Airflow/Dagster/dbt Cloud scheduling for SQL-only pipelines — no external orchestrator, no separate infrastructure to maintain, though it's less feature-rich than a full orchestrator for complex branching logic, retries, and cross-system dependencies.

Why they're almost always paired

A stream on its own just sits there holding an offset — nothing reads it automatically. A task on its own just runs SQL on a schedule, blind to whether there's actually anything new to process. Together, they form Snowflake's native incremental ELT pattern: the task wakes up, checks the stream, and only does work if there's something new.

The canonical pattern

CREATE STREAM orders_stream ON TABLE orders;

CREATE TASK process_order_changes
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')   -- skip the run entirely if empty
AS
  MERGE INTO orders_summary tgt
  USING orders_stream src
    ON tgt.order_id = src.order_id
  WHEN MATCHED AND src.metadata$action = 'DELETE' THEN DELETE
  WHEN MATCHED THEN UPDATE SET tgt.amount = src.amount
  WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (src.order_id, src.amount);

ALTER TASK process_order_changes RESUME;

The WHEN SYSTEM$STREAM_HAS_DATA clause is the key efficiency piece — the task's scheduler evaluates this cheaply and skips spinning up a warehouse entirely on quiet 5-minute windows, so you pay compute only when there's real work.

Why the MERGE consumes the stream correctly

Because the MERGE statement reads the stream inside the task's transaction and that transaction commits successfully, the stream's offset advances exactly once per successful run — if the task fails partway, the transaction rolls back and the offset does not advance, so the next run safely retries the same changes. This gives you at-least-once, effectively-exactly-once processing without any manual offset bookkeeping.

This pattern vs. Dynamic Tables

This manual stream+task MERGE pattern gives you full control over merge logic and business rules — but it's more code to write and maintain than a Dynamic Table (Module 5), which achieves similar incremental freshness declaratively from a single SQL query. Reach for streams+tasks when your incremental logic is too complex or conditional for a Dynamic Table's supported SQL subset.

Compared to other systems

This pattern is Snowflake's native equivalent to a Kafka Connect CDC source feeding a Spark Structured Streaming job with a checkpoint — same conceptual shape (source change log → offset-tracked incremental processing → sink), but entirely inside the warehouse with zero external streaming infrastructure to operate.

The fundamental difference from Snowpipe

Classic Snowpipe (Topic 22) is still file-based — something has to write a file to cloud storage first, then Snowpipe notices and loads it. Snowpipe Streaming skips the file entirely: your application calls a Java/Python SDK to push rows directly into a table, with no intermediate file and no cloud storage round-trip.

Internal architecture

The SDK batches rows client-side into Snowflake's native columnar format in memory and commits them directly as new micro-partitions (or partial partitions, later compacted), completely bypassing the file-staging step. This is why latency drops from Snowpipe's ~1 minute down to single-digit seconds.

// Simplified Java SDK usage pattern
SnowflakeStreamingIngestChannel channel = client.openChannel(request);
Map<String, Object> row = new HashMap<>();
row.put("event_id", "evt_123");
row.put("payload", jsonPayload);
channel.insertRow(row, "offset_token_1");

Low-latency ingestion in practice

Offset tokens — exactly-once from the client side

Each inserted row can carry a client-supplied offset token; on reconnect after a failure, the client asks the channel for its last committed offset and resumes from there, avoiding duplicate inserts — this is the same exactly-once discipline the Kafka connector (Topic 24) also relies on.

Compared to other systems

This is Snowflake's direct answer to what Kafka Streams or Kinesis Firehose-style direct-write ingestion offers elsewhere — row-level streaming writes without a file-staging intermediary, positioning Snowflake to compete on latency with purpose-built streaming databases.

What it is

The Snowflake Kafka Connector is a Kafka Connect sink connector — it runs inside your existing Kafka Connect cluster, subscribes to one or more topics, and loads each record into a Snowflake table. Internally it can operate in two modes: the legacy file-based mode (buffers records, writes them via Snowpipe) or the newer streaming mode (uses the Snowpipe Streaming SDK from Topic 23 under the hood for lower latency).

Configuration essentials

{
  "name": "snowflake-sink",
  "connector.class": "com.snowflake.kafka.connector.SnowflakeSinkConnector",
  "topics": "orders_topic",
  "snowflake.topic2table.map": "orders_topic:raw_orders",
  "buffer.count.records": "10000",
  "buffer.flush.time": "60",
  "snowflake.ingestion.method": "SNOWPIPE_STREAMING"
}

Offset handling

The connector tracks Kafka partition offsets alongside what's been successfully committed to Snowflake. On restart, it resumes from the last committed offset per Kafka partition — the same offset-checkpoint discipline as any Kafka Connect sink, layered on top of Snowflake-side idempotency.

Exactly-once semantics

True end-to-end exactly-once is achieved by combining Kafka's own offset commit guarantees with Snowflake-side deduplication: each record's Kafka partition+offset is embedded and checked against what's already landed, so a redelivered record (Kafka's usual at-least-once guarantee) doesn't produce a duplicate row.

Retry logic

Compared to other systems

Functionally parallel to any other Kafka Connect sink (the JDBC sink, the Elasticsearch sink, the BigQuery sink) — same connector framework, same offset semantics — Snowflake's version is distinguished mainly by its two ingestion-method options trading off latency against buffering efficiency.

The idea: declare the result, not the pipeline

Recall Topic 21 — manually wiring a stream, a task, and a MERGE statement to keep a summary table incrementally fresh. A Dynamic Table collapses all of that into one declarative CREATE DYNAMIC TABLE ... AS SELECT statement: you describe the query that defines the result, specify how fresh it needs to be, and Snowflake figures out the incremental refresh plan itself — across arbitrary joins and multiple base tables, unlike a single-table Materialized View (Module 2, Topic 8).

Basic syntax

CREATE DYNAMIC TABLE order_summary
  TARGET_LAG = '5 minutes'
  WAREHOUSE = etl_wh
AS
SELECT o.customer_id, c.customer_name, SUM(o.amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id, c.customer_name;

Notice the join across two base tables — something a plain Materialized View can't do at all. TARGET_LAG is a declared freshness SLA, not a cron schedule: "keep this no more than 5 minutes stale," and Snowflake decides when and how much to refresh to hit that target.

Refresh internals

Under the hood, Snowflake automatically maintains change-tracking (conceptually similar to a stream) on each base table feeding the dynamic table, and computes the minimal incremental delta needed to bring the result back within the target lag — falling back to a full refresh only when an incremental plan isn't possible for the given query shape (e.g. certain non-deterministic functions or unsupported constructs).

DAG dependency — chaining dynamic tables

Dynamic tables can be built on top of other dynamic tables, and Snowflake automatically tracks the resulting dependency graph, propagating freshness requirements upstream — if table C (target lag 10 min) depends on table B (target lag 5 min) which depends on raw table A, Snowflake schedules refreshes so the whole chain meets C's requirement without you manually sequencing tasks.

When to choose Dynamic Tables vs. Streams+Tasks vs. Materialized View

Compared to other systems

This is close in spirit to dbt's incremental models or Materialize's continuously-maintained SQL views — declare the transformation, let the engine maintain freshness — but Dynamic Tables run natively inside Snowflake with no external orchestration tool required.

Why this exists — going past what SQL expressions can do

Plain SQL has no loops, no local variables you can mutate step-by-step, and no easy way to write genuinely procedural string/number manipulation (parsing a custom mini-format, iterative rounding logic, recursive-in-spirit calculations bounded by a counter). JavaScript UDFs give you a real procedural language for exactly that class of scalar logic, while still being callable inline in a SQL expression.

Basic example

CREATE FUNCTION normalize_phone(raw STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var digits = RAW.replace(/[^0-9]/g, '');
  if (digits.length === 10) { return '+1' + digits; }
  return digits;
$$;

Internal execution model

Each JavaScript UDF call runs inside a sandboxed V8-based JavaScript engine embedded in the query execution process on the warehouse node. For every input row, Snowflake marshals the SQL argument values into JavaScript types, invokes the function body, and marshals the JavaScript return value back into a SQL type — that marshaling happens per row, which is the source of the overhead the analogy above points at.

Performance impact

Because the function body is opaque to the SQL optimizer (unlike a SQL UDF's inlined body), it acts as an optimization boundary: no pushdown or pruning can see through it, and the per-row call/marshal cost adds up fast on large tables. A JavaScript UDF applied to a billion-row scan can be an order of magnitude slower than the equivalent logic expressed as native SQL — always check whether the logic can be rewritten in SQL first.

When to use it

Compared to other systems

Directly analogous to BigQuery's JavaScript UDFs (same V8-based sandboxing) and functionally similar in spirit to PostgreSQL's PL/pgSQL functions or Spark's Scala/Python UDFs — all of these trade optimizer transparency for procedural expressiveness, and all pay a comparable per-row invocation tax versus native, inlinable SQL.

Why this exists — the ecosystem, not just the language

The real draw of Python UDFs isn't Python's syntax over JavaScript's — it's access to Python's package ecosystem (numpy, pandas, scikit-learn, and anything else installable from Anaconda's Snowflake channel) directly inside a query, most commonly for scoring rows with a trained ML model without ever exporting data out of Snowflake.

Scalar Python UDF

CREATE FUNCTION risk_score(income FLOAT, debt FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
HANDLER = 'compute'
AS
$$
def compute(income, debt):
    return min(debt / income, 1.0) if income else 1.0
$$;

Vectorized (batch) Python UDFs — the important variant

Instead of one row per call, a vectorized UDF receives a batch of rows as a pandas DataFrame and returns a pandas Series — collapsing thousands of per-row invocations into one call operating on a whole batch with numpy/pandas' native vectorized operations.

CREATE FUNCTION risk_score_batch(income FLOAT, debt FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.10'
HANDLER = 'RiskBatch'
AS
$$
import pandas as pd
class RiskBatch:
    def end_partition(self, df):
        df['risk'] = (df['DEBT'] / df['INCOME']).clip(upper=1.0)
        return df['risk']
$$;

Internal execution

Python UDF code runs inside a sandboxed Python runtime managed by Snowflake on the warehouse's compute nodes — packages come from a curated, Snowflake-vetted Anaconda channel (or a stage-hosted zip for custom packages), and the vectorized path batches many rows into a single pandas DataFrame handoff, sharply reducing the per-row marshaling cost that plagues scalar UDFs of any language.

Performance considerations

For anything ML-scoring related, always reach for vectorized first — it's the difference between "usable at scale" and "technically works, don't run it on a billion rows."

Compared to other systems

The scalar-vs-vectorized split mirrors Spark's Python UDFs vs. pandas UDFs (Arrow-batched) distinction almost exactly — same underlying idea of amortizing serialization cost across a batch instead of paying it per row. BigQuery's remote functions and Python UDFs occupy similar territory but route through a network hop rather than an in-process sandbox.

Why this exists — calling code that can never run inside Snowflake

Sometimes the logic you need genuinely can't live inside Snowflake at all: a third-party enrichment API you don't control, a proprietary model served from SageMaker or Vertex AI, or business logic your company keeps outside the warehouse on purpose. An external function lets a SQL query call that logic mid-query, treating the outside world like just another function.

How it works internally

You create an API integration object that whitelists a proxy service (typically an AWS API Gateway + Lambda, or Azure API Management + Function, or GCP API Gateway + Cloud Function), then define a function pointing at it. At query time, Snowflake batches multiple rows' worth of arguments into a single HTTPS POST to the proxy, receives a JSON array of results, and maps them back to rows — batching per call is what keeps network round-trip overhead from dominating the whole thing.

CREATE API INTEGRATION enrichment_api
  API_PROVIDER = aws_api_gateway
  API_AWS_ROLE_ARN = 'arn:aws:iam::123456789:role/snowflake-ext-func'
  API_ALLOWED_PREFIXES = ('https://abc123.execute-api.us-east-1.amazonaws.com/prod/')
  ENABLED = TRUE;

CREATE FUNCTION enrich_address(addr STRING)
RETURNS VARIANT
API_INTEGRATION = enrichment_api
AS 'https://abc123.execute-api.us-east-1.amazonaws.com/prod/enrich';

Latency and cost model

Every call is a genuine network round trip — no result cache exists across that boundary the way it does for a plain query, so latency depends entirely on the remote endpoint's own responsiveness plus batching efficiency. Cost has two independent components: the Snowflake compute time spent waiting on the call, and whatever the external service (Lambda invocations, API Gateway requests) charges on its own side.

Security surface

The API integration object is the security boundary: it pins the function to a specific, pre-approved endpoint prefix, and the associated cloud IAM role (the API_AWS_ROLE_ARN above) controls exactly what Snowflake's side is authorized to invoke — a query author can call the function but cannot redirect it to an arbitrary URL.

Compared to other systems

The closest equivalent is BigQuery's remote functions, which follow the same "SQL calls out to a Cloud Function/Cloud Run endpoint" shape; both exist because sometimes the fastest path to production is calling logic you already trust and already run, rather than reimplementing it inside the warehouse.

The line between a UDF and a procedure

Every UDF so far, no matter the language, is restricted to being called inside a SQL expression and returning a value — it can't run DDL, can't run multiple independent DML statements, and can't branch across several distinct SQL statements based on intermediate results. A stored procedure removes all three restrictions: it's a standalone unit of imperative logic that runs its own sequence of SQL statements, with real control flow, and is called on its own rather than embedded inside a query.

Snowflake Scripting example

CREATE PROCEDURE archive_old_orders(cutoff_days INT)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  INSERT INTO orders_archive
    SELECT * FROM orders WHERE order_date < DATEADD(day, -cutoff_days, CURRENT_DATE());

  DELETE FROM orders WHERE order_date < DATEADD(day, -cutoff_days, CURRENT_DATE());

  RETURN 'Archived rows older than ' || cutoff_days || ' days';
END;
$$;

CALL archive_old_orders(90);

Notice the shape: an INSERT, then a DELETE, then a return value — a sequence a UDF simply cannot express, since a UDF must resolve to one value inside one statement.

Languages available

Beyond Snowflake Scripting (SQL with procedural extensions, shown above), procedures can also be written in JavaScript, Python, Java, or Scala — same tradeoff as UDFs: more procedural power and access to libraries, less optimizer transparency, per-call overhead instead of inlining.

Caller's rights vs. owner's rights

Owner's rights is the more common pattern for admin-style procedures: give a limited role permission to CALL a procedure that itself was created by a role with the broader privileges the task actually needs, without ever granting those broader privileges to the caller directly.

Compared to other systems

Directly parallel to PostgreSQL's PL/pgSQL procedures or SQL Server's T-SQL stored procedures — same core distinction from a function (multi-statement, side-effecting, no return-value requirement in a SELECT), and the owner's/caller's rights split mirrors Postgres's SECURITY DEFINER vs. SECURITY INVOKER almost exactly.

Default behavior: autocommit

Outside a procedure, every individual SQL statement in Snowflake commits on its own the moment it succeeds — there's no implicit multi-statement transaction wrapping a session by default. Inside a procedure's multi-statement body, though, you often need several statements to succeed or fail together, which is exactly what explicit transaction control is for.

BEGIN / COMMIT / ROLLBACK inside a procedure

CREATE PROCEDURE transfer_funds(from_acct INT, to_acct INT, amt NUMBER)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  BEGIN TRANSACTION;

  UPDATE accounts SET balance = balance - :amt WHERE account_id = :from_acct;
  UPDATE accounts SET balance = balance + :amt WHERE account_id = :to_acct;

  IF ((SELECT balance FROM accounts WHERE account_id = :from_acct) < 0) THEN
    ROLLBACK;
    RETURN 'Failed: insufficient funds';
  END IF;

  COMMIT;
  RETURN 'Transfer complete';
END;
$$;

Both updates either land together (COMMIT) or neither does (ROLLBACK) — exactly the guarantee autocommit on separate statements could never give you, since the first UPDATE would already be permanent before you could even check the balance.

Locking implications

An open transaction holds row-level locks on the rows it has modified until it commits or rolls back — combined with Snowflake's underlying MVCC model (Module 11 covers this in depth), readers of the affected rows see the pre-transaction version until commit, but a second writer targeting the same rows will queue behind the open transaction. This is exactly why the best practice below matters.

Best practice: keep transactions short

Because every table is fundamentally a set of immutable micro-partitions (Module 1) — a write doesn't edit a partition in place, it creates new partition versions — a long-running open transaction increases the chance of colliding with concurrent writers and holding locks longer than necessary. Do the minimum number of statements inside BEGIN TRANSACTION ... COMMIT, and keep any expensive read-only computation outside the transaction boundary wherever possible.

Compared to other systems

The explicit BEGIN TRANSACTION / COMMIT / ROLLBACK vocabulary is the same one PostgreSQL and most relational databases use; the main conceptual difference worth carrying forward is that Snowflake's underlying storage is immutable and versioned rather than row-mutated in place, which is what Module 11's MVCC topic unpacks fully.

OBJECT vs. VARIANT — a narrower, more specific type

OBJECT stores key-value pairs specifically — it's what a JSON object (the part inside { }) becomes when Snowflake knows for certain the value is a map of string keys to values, rather than "any type whatsoever." In practice, a column loaded from JSON is usually typed VARIANT even when every row happens to be an object, but you construct and manipulate OBJECT values explicitly when building semi-structured data inside SQL rather than loading it from a file.

Constructing and manipulating an OBJECT

SELECT OBJECT_CONSTRUCT('user', 'alice', 'action', 'click') AS event_obj;
-- {"user": "alice", "action": "click"}

SELECT OBJECT_INSERT(event_obj, 'ts', CURRENT_TIMESTAMP()) FROM events;
SELECT OBJECT_DELETE(event_obj, 'action') FROM events;
SELECT OBJECT_KEYS(event_obj) FROM events;  -- returns an ARRAY of the keys

These functions are non-destructive — each one returns a new OBJECT value rather than mutating the original, consistent with Snowflake's broader immutable-storage philosophy (Module 1's micro-partitions work the same way at the storage layer).

When you reach for OBJECT explicitly

Compared to other systems

Analogous to PostgreSQL's jsonb_build_object, jsonb_set, and related functions — same idea of constructing and editing map-shaped semi-structured values from within SQL rather than only consuming them after the fact.

What it represents

ARRAY is Snowflake's ordered, zero-indexed, mixed-type-capable collection — the equivalent of a JSON array. Like OBJECT, data loaded from files typically arrives as VARIANT even when every value happens to be an array; ARRAY becomes the working type once you extract or construct one explicitly.

Construction and access

SELECT ARRAY_CONSTRUCT('red', 'green', 'blue') AS colors;
-- ["red", "green", "blue"]

SELECT colors[0] FROM t;                 -- 'red' (zero-indexed)
SELECT ARRAY_SIZE(colors) FROM t;
SELECT ARRAY_APPEND(colors, 'yellow') FROM t;
SELECT ARRAY_CONTAINS('green'::VARIANT, colors) FROM t;

Going from array back to rows

An array's values only become individually queryable rows through FLATTEN (Topic 36) — indexing with [n] is fine for a known position, but iterating "for every element" always means flattening. This is the bridge that connects the semi-structured world back to ordinary relational querying.

Aggregating rows into an array — the reverse direction

SELECT customer_id, ARRAY_AGG(order_id) AS order_ids
FROM orders
GROUP BY customer_id;

ARRAY_AGG is the semi-structured counterpart of LISTAGG — instead of concatenating into a delimited string, it collects grouped values into a real ARRAY value that stays a native type, not text you'd need to re-parse.

Compared to other systems

Equivalent to PostgreSQL's native ARRAY type and array_agg(), and to BigQuery's ARRAY / ARRAY_AGG — the round trip of "rows → array → rows again via FLATTEN" is a common pattern across all of these engines, just with different function names.

The naive fear: "JSON must be slow"

A common assumption is that querying inside a VARIANT column means scanning and re-parsing raw text on every query, the way a naive text-based JSON store would. Snowflake avoids this because of what Topic 32 already established: VARIANT is parsed once at load time into a structured, columnar internal format — after that, querying a path is closer to querying a real column than to regex-ing through text.

Per-path statistics — pruning inside the structure

During the parse-at-load step, Snowflake collects micro-partition-level statistics (Module 1's min/max/pruning machinery) not just for whole columns, but for individual paths inside the VARIANT structure — so a predicate like WHERE payload:meta:page = 'home' can prune entire micro-partitions whose meta.page path never contains 'home', without ever touching the actual row data in those partitions.

Flattening consistency matters

Path-level pruning works best when the same path appears with a consistent type across most rows — a field that's sometimes a string and sometimes an object defeats a lot of the statistics Snowflake could otherwise build. Inconsistent schemas inside VARIANT are still fully supported, just less optimizable than clean, uniform payloads.

When to promote paths to real columns

Compared to other systems

This is a meaningfully stronger optimization story than PostgreSQL's JSONB, which supports GIN indexes on paths but requires you to create them explicitly — Snowflake builds path-level statistics automatically at load time with no index-creation step. Spark's JSON handling, by contrast, typically requires an explicit schema inference or definition pass to get comparable pushdown behavior.

What FLATTEN actually is

FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY and emits one output row per element (for an array) or per key-value pair (for an object) — it's the mechanism that turns "one row with a nested structure" into "many ordinary rows," which is exactly what Topic 17 (Lateral Flatten, Module 4) first introduced and this topic goes deeper on.

Anatomy of a FLATTEN call

SELECT e.event_id, f.value:product_id::INT AS product_id
FROM events e,
     LATERAL FLATTEN(input => e.payload:items) f;

The output columns Snowflake exposes per flattened row include SEQ (a sequence identifier), KEY (the object key, if flattening an object; null for arrays), PATH (the full path from the original root), INDEX (the array position, if flattening an array), and VALUE (the actual element) — most queries only need VALUE, but INDEX and PATH matter once you're flattening something nested or need to preserve original ordering.

Why LATERAL — execution order matters

A LATERAL join is required (not just conventional) because each row's FLATTEN call needs to reference that same row's column value as its input — an ordinary join can't do that, since ordinary joins evaluate both sides independently before matching them. LATERAL explicitly says "evaluate the right side once per row of the left side, using that row's values."

RECURSIVE flattening for arbitrary nesting depth

SELECT f.path, f.value
FROM events e,
     LATERAL FLATTEN(input => e.payload, recursive => TRUE) f;

RECURSIVE => TRUE descends into every nested array/object at every depth in one call, rather than requiring one FLATTEN per nesting level — useful for exploring an unfamiliar or deeply nested payload, though for a known, stable shape, chaining explicit single-level FLATTENs (or direct path navigation) is usually clearer and faster.

Performance impact

FLATTEN is a genuine row-multiplying operation — a table of 1M rows each containing a 50-element array becomes 50M output rows once flattened, which then flows into every downstream join, filter, and aggregate at that larger size. Filter on the un-flattened VARIANT path (Topic 35's pruning) before flattening wherever possible, so pruning happens on the smaller pre-flatten row count.

Compared to other systems

The direct equivalents are PostgreSQL's jsonb_array_elements combined with a LATERAL join, BigQuery's UNNEST, and Spark's explode() — all solve the identical "nested collection → one row per element" problem, with FLATTEN's SEQ/KEY/PATH/INDEX metadata columns being a slightly richer built-in bookkeeping set than most of those alternatives expose by default.

The idea: old versions don't disappear immediately

Because a write never edits a micro-partition in place — it writes new partition versions and updates the table's metadata to point at them (Module 1) — the old partition versions don't vanish the instant a new write commits. Snowflake simply keeps them around for a configurable retention window instead of immediately reclaiming them, and that's the entire mechanism behind Time Travel.

Querying and restoring past state

SELECT * FROM orders AT(OFFSET => -3600);              -- 1 hour ago
SELECT * FROM orders BEFORE(STATEMENT => '01af...');  -- right before a specific query ran

UNDROP TABLE orders;                                -- recover an accidentally dropped table

CREATE TABLE orders_restored AS
SELECT * FROM orders AT(TIMESTAMP => '2026-06-30 09:00:00'::TIMESTAMP);

BEFORE(STATEMENT => ...) is the most precise recovery tool of the three — pointing at an exact query ID (found via QUERY_HISTORY, Module 11) rather than an approximate timestamp, which matters when you know exactly which bad UPDATE or DELETE caused the damage.

Retention window by edition

Storage cost — this is why it's not free forever

Every day inside the retention window that old partition versions are kept alive is billed as storage — a table with heavy update/delete churn and a long retention window can carry meaningfully more storage cost than its "current" data size alone would suggest, since it's really paying to keep several days of superseded partition versions around too.

Compared to other systems

The nearest equivalents are Delta Lake's time travel (VERSION AS OF / TIMESTAMP AS OF) and Apache Iceberg's snapshot-based history — same underlying idea of immutable file versions plus a metadata pointer that can be rewound, though Snowflake's window is time-based and automatic rather than requiring an explicit VACUUM/retention job to manage.

What happens after Time Travel expires

When a table's Time Travel retention window ends, its superseded partition versions don't get deleted immediately either — for permanent tables, they move into a further 7-day period called Fail-safe, a last-resort disaster-recovery layer that exists purely for Snowflake support to recover data after catastrophic, otherwise-unrecoverable loss.

The critical distinction: not self-service

Transient and temporary tables (Module 11 covers table types in full) skip Fail-safe entirely — a deliberate tradeoff: cheaper storage in exchange for weaker recovery guarantees, appropriate for staging/scratch data you can always reproduce from source.

Cost implication

Fail-safe storage is billed the same as any other stored data, with no way to opt out for permanent tables — it's a fixed cost of choosing "permanent" as the table type, which is part of why high-churn staging tables are often deliberately created as transient instead.

Compared to other systems

There's no direct open-source-warehouse equivalent to Fail-safe specifically — it's closer in spirit to a cloud storage provider's own internal backup/replication tier (the layer behind, say, S3's durability guarantees) than to anything exposed as a queryable feature; Delta Lake and Iceberg leave true disaster recovery entirely to the underlying storage layer's own backup practices.

Retention is a dial, and table type sets its range

Topics 38 and 39 already covered what Time Travel and Fail-safe are; this topic is about deliberately choosing how much of each you actually pay for, via table type and the DATA_RETENTION_TIME_IN_DAYS parameter.

The three table types

CREATE TRANSIENT TABLE staging_load (id INT, payload VARIANT);
CREATE TEMPORARY TABLE scratch_calc AS SELECT * FROM orders LIMIT 1000;

ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

Choosing deliberately, not by default

Every extra day of retention on a high-churn table is paid storage for old partition versions (Topic 38) — the right default isn't "maximum retention everywhere," it's matching retention to how long you'd genuinely want to reach back.

Compared to other systems

Most systems don't offer this exact three-way split; the closest parallel is choosing between a durable table and an unlogged/temp table in PostgreSQL, which trades some durability guarantees for reduced overhead — Snowflake's version is more granular because it's tuning a time-based retention window rather than a binary durability switch.

Why the source file format still matters

Once data lands inside Snowflake it's always stored the same way — compressed, columnar micro-partitions (Module 1), regardless of what format the source file was in. But the format of the file you're loading from still matters enormously for load speed and for how much of the source file's own structure Snowflake can exploit during the load itself.

Format comparison

Defining a file format object

CREATE FILE FORMAT parquet_fmt
  TYPE = 'PARQUET'
  COMPRESSION = 'SNAPPY';

COPY INTO raw_events
FROM @raw_stage/events/
FILE_FORMAT = (FORMAT_NAME = parquet_fmt);

Why columnar sources load faster

A columnar format like Parquet or ORC already groups values by column and often already carries per-column statistics — Snowflake's load process can map that structure fairly directly into micro-partition columns and metadata, versus CSV/JSON where every row has to be split, typed, and reorganized column-wise from scratch during the load.

Compared to other systems

Parquet's cross-engine dominance is exactly why it's the common denominator between Snowflake, Spark, Trino, and open table formats like Iceberg and Delta Lake — choosing it for a data lake landing zone keeps the same files efficiently readable by all of them without conversion.

The unit of parallelism is the file, not the row

COPY INTO parallelizes load work across a warehouse's available threads by handing out whole files to workers — not by splitting one huge file's rows across workers. That single fact drives almost every optimization rule in this topic.

Optimal file sizing

Snowflake's own guidance is to target compressed file sizes in the 100–250 MB range. Too large (a single multi-GB file) and one thread is stuck processing it alone while others sit idle; too small (thousands of tiny files) and per-file overhead (opening, metadata, network round trips) dominates the actual data-processing time.

Practical COPY INTO tuning

COPY INTO raw_events
FROM @raw_stage/events/
FILE_FORMAT = (FORMAT_NAME = parquet_fmt)
ON_ERROR = 'SKIP_FILE'
PURGE = TRUE;

Load metadata prevents duplicate loading

Snowflake tracks which files have already been successfully loaded into a given table (via internal load metadata, retained for 64 days by default) — re-running the same COPY INTO against a stage automatically skips files it already loaded, which is what makes Snowpipe (Module 5) and repeated manual loads both safe to re-trigger without manual dedup logic.

When bulk COPY INTO isn't the right tool anymore

Batch COPY INTO is fundamentally a "process a batch of files now" operation — for continuously arriving files, Snowpipe (event-triggered) or Snowpipe Streaming (Module 5) are the better fit; reach for tuning this topic's options when you're doing scheduled or one-off bulk backfills, not steady-state ingestion.

Compared to other systems

The file-count-driven parallelism model is conceptually similar to how Spark partitions work when reading a directory of files — too few large files under-parallelizes a Spark job the same way it under-parallelizes a Snowflake load, and the 100–250MB guidance is in the same ballpark as common Spark/Hadoop file-sizing advice for exactly the same underlying reason.

The problem a broadcast join solves

In a distributed hash join, both sides of the join normally need to be redistributed ("shuffled") across compute nodes so that rows with matching keys land on the same node. When one side is genuinely small — a lookup/dimension table with a few thousand rows — shuffling it is wasted motion: it's cheaper to just copy that entire small table to every node and let each node join its local slice of the large table against the full small table locally.

When Snowflake chooses to broadcast

This decision is made automatically by the optimizer based on estimated table size — there's no explicit BROADCAST hint in Snowflake SQL the way some engines expose. A small dimension table joined against a large fact table is the canonical case where you'd expect to see this happen, and the query profile's Join node will show the small side's data being replicated rather than repartitioned.

SELECT f.order_id, f.amount, d.region_name
FROM fact_orders f
JOIN dim_region d ON f.region_id = d.region_id;
-- dim_region (small) is the natural broadcast candidate

Skew — the case broadcasting doesn't fix

Broadcasting solves an imbalance in table size, not an imbalance in key distribution. If the large side of a join has a handful of keys accounting for a disproportionate share of rows (e.g. one customer_id with millions of orders), one node ends up doing far more work regardless of whether the small side was broadcast — that's data skew, and it shows up as one uneven, long-running partition of the Join node in the profile rather than an even spread.

Practical guidance

Compared to other systems

This is the same broadcast-join concept Spark SQL exposes explicitly via broadcast() hints and spark.sql.autoBroadcastJoinThreshold — Snowflake makes the equivalent decision automatically from its own cost-based statistics rather than exposing a manual threshold or hint for you to tune.

When both sides are genuinely large

Broadcast joins (Topic 44) only help when one side is small. Joining two multi-billion-row fact tables is a different problem entirely — both sides need to be shuffled and redistributed by join key, and the total data movement across the network between compute nodes becomes the dominant cost, more than the actual comparison work.

Clustering as the primary lever

If both large tables are clustered (Module 1, Topic 4) on their join key, matching rows tend to already live in the same or nearby micro-partitions — pruning (Topic 47) can eliminate large swaths of both tables before the shuffle even starts, shrinking exactly the data volume that otherwise has to move across the network. This is the single highest-leverage optimization for large-to-large joins, more impactful than warehouse sizing alone.

ALTER TABLE fact_orders CLUSTER BY (order_date, customer_id);
ALTER TABLE fact_shipments CLUSTER BY (order_date, customer_id);
-- Joining on customer_id benefits if both tables cluster on it (or a correlated key)

Filtering before joining, aggressively

Because pushdown (Topic 46) applies filters before the join executes, adding the most selective filter you can to both sides of a large-to-large join (a date range, a status flag) shrinks the shuffle volume directly — this matters far more here than in a broadcast scenario, since there's no small side absorbing the cost.

Warehouse sizing for large joins

A shuffle-heavy join needs enough memory across the warehouse's nodes to hold its intermediate hash tables without spilling (Module 2, Topic 5) — scaling up warehouse size (Topic 48) adds memory and network bandwidth per node, which directly reduces spill-to-disk on genuinely large joins, distinct from scaling out (multi-cluster) which helps concurrency, not a single query's size.

Compared to other systems

This mirrors exactly why Spark and Trino both recommend co-partitioning/bucketing large tables on their common join key before a shuffle-heavy join — the goal in all three engines is the same: make sure matching rows are already near each other so the redistribution step has less work to do.

The idea: filter as early as physically possible

A query written with a filter after a join, subquery, or view doesn't have to actually be executed in that order. During the compilation phase (Module 1), the optimizer identifies which filters can be moved down closer to the raw scan — as early as the micro-partition read itself — without changing the query's result, and relocates them there. This is predicate pushdown.

A concrete example

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date = '2026-06-30';

Even though WHERE o.order_date = '2026-06-30' is written after the JOIN, the optimizer pushes it down to apply directly against orders before the join runs — and because order_date likely has partition-level min/max metadata (Module 1), this pushdown enables partition pruning (Topic 47) too, which is the real payoff.

What limits pushdown

Writing queries that cooperate with pushdown

You almost never need to manually reorder a query for pushdown — the optimizer generally handles it — but wrapping a filterable column in a function (WHERE UPPER(status) = 'DONE' instead of storing a normalized value) can sometimes prevent metadata-level pruning from recognizing a simple equality, since the optimizer would need to prove the function's behavior across every stored value first.

Compared to other systems

Predicate pushdown is a standard relational optimizer technique present in PostgreSQL, Spark SQL (pushing filters into a Parquet/ORC scan), and virtually every modern query engine — Snowflake's version is tightly integrated with its own metadata layer, which is what turns a pushed-down filter into partition pruning rather than just "apply the filter slightly earlier in a full scan."

The single most valuable optimization in Snowflake

Module 1, Topic 3 introduced partition pruning as a property of micro-partition metadata; this topic treats it as the tuning lever it actually is. Every micro-partition carries min/max statistics per column, and a filter that's been pushed down (Topic 46) close enough to the scan lets Snowflake skip reading — not just skip processing, skip reading from storage at all — any partition whose min/max range can't possibly contain a match.

What makes a filter prunable

Clustering exists to make pruning effective

Metadata alone doesn't guarantee good pruning — if rows for every date are scattered across every micro-partition, a date filter's min/max range covers nearly every partition, and pruning eliminates almost nothing. Clustering (Module 1, Topic 4) is the mechanism that keeps values correlated with your common filter columns physically grouped together, which is what makes their min/max ranges narrow and genuinely useful for elimination.

Verifying pruning in the query profile

The query profile (Module 2, Topic 5) reports "Partitions scanned" versus "Partitions total" on scan nodes — a query filtering to one day out of five years of daily-clustered data should show a scanned count close to what one day's worth of partitions would be, not the full table's partition count. A scanned count close to the total despite a selective-looking filter is the clearest sign that pruning isn't working as expected — usually a clustering or filter-shape problem from the tables above.

Compared to other systems

Directly analogous to partition elimination in traditional partitioned tables (PostgreSQL declarative partitioning, Hive/Spark partition pruning) and to file-level statistics skipping in Parquet/Iceberg (Module 11 covers Iceberg specifically) — the core idea of "use pre-computed ranges to skip whole chunks of data" recurs across essentially every modern analytical engine.

Two different problems, two different dials

Module 1, Topic 2 introduced warehouse sizing and multi-cluster warehouses as concepts; this topic is about correctly diagnosing which dial actually fixes the problem in front of you, since scaling the wrong one wastes money without fixing anything.

Diagnosing which dial to turn

Scaling up in practice

ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'LARGE';

Each size step doubles compute resources (XS → S → M → L → XL → ...). Since compute is billed per-second of running time, a warehouse twice as large that finishes a job in half the time often costs roughly the same overall — the real win is unlocking correctness (no more disk spill) and reduced wall-clock time, not necessarily a straightforward cost increase.

Scaling out in practice

ALTER WAREHOUSE bi_wh SET
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 4
  SCALING_POLICY = 'STANDARD';

Multi-cluster adds and removes entire additional clusters automatically as queued query volume rises and falls — it doesn't make any single query run faster, it just gives more concurrent queries somewhere to run without waiting.

Compared to other systems

The scale-up/scale-out distinction mirrors any distributed system's fundamental choice — vertical scaling (bigger nodes) versus horizontal scaling (more nodes/clusters) appears identically in Spark cluster sizing (executor size vs. executor count) and in traditional database read-replica scaling for concurrency.

The specific problem this targets

Warehouse scaling (Topic 48) helps broadly, but one particular pattern doesn't respond well to it: a handful of queries in an otherwise well-behaved workload that need to scan a disproportionately large number of partitions (a poorly-pruned ad hoc query, an unusually broad date range) — scaling the whole warehouse up just to accommodate those occasional outliers is wasteful the rest of the time.

How it works

When enabled on a warehouse, Snowflake automatically detects eligible queries — specifically those with a large, parallelizable scan portion — and offloads part of that scan work to serverless compute resources outside the warehouse's own fixed-size cluster, then merges the results back in. This happens transparently; there's no query rewrite or hint required.

ALTER WAREHOUSE analytics_wh SET
  ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

QUERY_ACCELERATION_MAX_SCALE_FACTOR caps how much serverless capacity a single query can borrow relative to the warehouse's own size — a safety limit on the cost this feature can add for any one query.

What it does and doesn't help

Cost model

Serverless acceleration compute is billed separately, per second of actual acceleration work used — you pay only when a query is actually accelerated, unlike scaling up a warehouse size which raises the cost of every query run on it, including the ones that never needed the extra headroom.

Compared to other systems

Conceptually similar to burst/serverless compute add-ons in other cloud data warehouses (e.g. BigQuery's flat-rate-plus-burst capacity models) — the shared idea is decoupling a rare capacity spike from the baseline provisioned compute, rather than provisioning permanently for the worst case.

Why a flat "grant everything to everyone useful" approach fails

Snowflake's access model is pure role-based access control: privileges are granted to roles, roles are granted to users (or to other roles), and a user's effective privileges are the union of every role in their hierarchy. Granting privileges directly to individual users works for a handful of people; at real organizational scale it becomes untrackable — nobody can answer "who can read this table" without auditing every user individually.

The standard layered pattern: functional roles vs. access roles

-- Access roles: own object-level grants, named after WHAT they access
CREATE ROLE analytics_prod_read;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_prod.public TO ROLE analytics_prod_read;

-- Functional roles: named after WHO holds them, inherit from access roles
CREATE ROLE data_analyst;
GRANT ROLE analytics_prod_read TO ROLE data_analyst;
GRANT ROLE data_analyst TO USER priya;

This two-layer split is deliberate: access roles map cleanly to "what exists" (one per schema/database/purpose), while functional roles map to "what a job needs" and can combine several access roles — a senior analyst's functional role might inherit both a read-only access role and a narrower write access role on a staging schema.

System-defined roles sit above everything

Role hierarchy as a DAG, not a strict tree

Because a role can be granted to multiple other roles, the resulting structure is a directed graph, not a single tree — this is what allows a role like data_analyst to combine grants from several independent access roles without duplicating any underlying object privilege.

Compared to other systems

Conceptually the same layered RBAC pattern recommended for PostgreSQL (group roles vs. login roles) and AWS IAM (managed policies vs. roles/groups) — the functional-vs-access role split specifically mirrors separating IAM policies (what) from IAM roles/groups (who) in cloud identity systems.

Why this needs deliberate structure at scale

A single team with one or two warehouses can watch cost informally. Once dozens of teams share an account, cost governance needs three separate things working together: hard spending guardrails, a way to attribute spend to the right team, and visibility that catches problems before the monthly bill does.

Resource monitors — the hard guardrail

Resource monitors (introduced in Module 1, Topic 2) can be scoped to one warehouse or to the whole account, with threshold-triggered actions.

CREATE RESOURCE MONITOR team_analytics_monitor
  WITH CREDIT_QUOTA = 500
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE analytics_wh SET RESOURCE_MONITOR = team_analytics_monitor;

SUSPEND lets running queries finish before stopping new ones; SUSPEND_IMMEDIATE kills everything right away — the graduated triggers (notify, then soft-stop, then hard-stop) give teams a chance to react before anything actually breaks.

Tagging for chargeback and attribution

CREATE TAG cost_center;
ALTER WAREHOUSE analytics_wh SET TAG cost_center = 'marketing';

SELECT tag_value, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history wm
JOIN snowflake.account_usage.tag_references tr ON wm.warehouse_id = tr.object_id
WHERE tr.tag_name = 'cost_center'
GROUP BY tag_value;

Tags (Module 3's tag-based security reused here for a different purpose) turn raw credit consumption into per-team, per-project cost attribution — the same tagging mechanism doing governance duty in Module 3 does budgeting duty here.

Warehouse-per-team vs. shared warehouses

Compared to other systems

The overall shape — hard budget alarms plus cost-allocation tags plus a usage-reporting layer — is the same governance pattern AWS Budgets/Cost Explorer and GCP's budget alerts/labels implement for cloud spend generally; Snowflake's version is scoped specifically to compute credits rather than a whole cloud bill.

From one share to an architecture

Module 3, Topic 14 covered the mechanics of a single CREATE SHARE. At enterprise scale, the question shifts from "how does one share work" to "how do dozens of internal teams and external partners consume curated data without turning into an unmanageable web of point-to-point shares."

The hub-and-spoke pattern

A dedicated "data sharing hub" account (or a clearly designated database within a central account) becomes the single place where curated, share-ready secure views (Module 3, Topics 12 & 14) are published — producing teams publish inward to the hub, consuming teams and external partners pull from the hub outward, and nobody sets up a direct share to anybody else.

Governance checkpoints at the hub

Reader accounts as the external on-ramp

External partners without their own Snowflake account use reader accounts (previewed in Module 3, fully unpacked in Module 11) — the hub pattern makes it straightforward to provision one reader account per external partner, each scoped narrowly to only the shares relevant to that relationship.

Compared to other systems

This hub-and-spoke governance shape mirrors how organizations structure a central data catalog or data mesh "marketplace" layer regardless of underlying platform — Delta Sharing deployments and API-based data marketplaces converge on the same pattern for the same reason: many-to-many raw sharing doesn't scale governance-wise, but many-to-one-to-many does.

Why replicate across regions at all

Two distinct reasons drive this: bringing data physically closer to users in a different geography (lower query latency for a regional team), and disaster recovery — surviving an entire cloud region becoming unavailable, which Zero Copy Cloning and Time Travel (Module 8) can't help with, since both only protect you within a single account's storage.

Setting up database replication

-- On the primary account
ALTER DATABASE analytics_prod ENABLE REPLICATION TO ACCOUNTS myorg.aws_us_east_1;

-- On the target account
CREATE DATABASE analytics_prod AS REPLICA OF myorg.aws_us_west_2.analytics_prod;
ALTER DATABASE analytics_prod REFRESH;

Refresh is not continuous streaming by default — it's a scheduled or manually triggered snapshot-style sync, meaning a replica normally lags the primary by however long since the last refresh, not zero.

Failover groups — replication plus automated failover

A plain database replica is passive — you still have to manually promote it. A failover group bundles multiple objects (databases, roles, warehouses, users) together and adds the ability to actually redirect traffic to the secondary account with a single failover command, treating the whole group as one recoverable unit rather than syncing pieces individually.

CREATE FAILOVER GROUP prod_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES
  ALLOWED_DATABASES = analytics_prod
  ALLOWED_ACCOUNTS = myorg.aws_us_east_1
  REPLICATION_SCHEDULE = '10 MINUTE';

ALTER FAILOVER GROUP prod_fg FAILOVER TO ACCOUNT myorg.aws_us_east_1;

RPO/RTO tradeoffs

Compared to other systems

Directly analogous to cross-region read replicas plus a manual/scripted promotion step in traditional databases, and to multi-region active-passive setups common in any cloud-native architecture — the failover group's bundling of multiple dependent objects together is the more Snowflake-specific piece, avoiding the classic problem of a database failing over while its roles and warehouses don't.

The same mechanism, a bigger boundary crossed

Everything from Topic 54 — database replication, failover groups — works identically whether the target account is in a different region of the same cloud provider or on an entirely different cloud provider. Snowflake's replication layer operates above the specific cloud infrastructure, which is what makes an AWS-to-Azure or GCP-to-AWS replica exactly as achievable as a same-cloud, cross-region one.

Why an organization actually needs this

Practical setup — identical syntax, different account locator

ALTER DATABASE analytics_prod ENABLE REPLICATION TO ACCOUNTS myorg.azure_east_us_2;

CREATE DATABASE analytics_prod AS REPLICA OF myorg.aws_us_east_1.analytics_prod;

The account identifier's cloud/region suffix is the only visible difference from a same-cloud replica — everything else (refresh cadence, failover groups, RPO/RTO tuning from Topic 54) behaves the same way.

What doesn't come along automatically

Objects tied to a specific cloud provider's native services — certain external stages pointing at that cloud's storage, API integrations wired to that cloud's Lambda/Functions (Module 6, Topic 29) — don't magically become cross-cloud; the replicated database's own tables and structure travel, but cloud-native integration points on the target side typically need to be reconfigured for the destination cloud.

Compared to other systems

True cross-cloud replication with unified failover semantics is a genuine differentiator versus most traditional databases, which are generally deployed and replicated within a single cloud provider's ecosystem; the closest conceptual parallel is a multi-cloud Kubernetes deployment strategy, but applied to a managed data warehouse rather than to application pods.

Tying every recovery mechanism together

By this point in the course, four distinct recovery mechanisms have each appeared for a different failure scope: Time Travel (Module 8) for "I made a mistake," Fail-safe (Module 8) for catastrophic Snowflake-side loss, and database replication/failover groups (Topic 54) for "an entire region is gone." A real disaster recovery strategy isn't picking one — it's understanding which failure each layer actually covers and designing around all of them together.

Mapping failure scope to recovery mechanism

RPO and RTO as the design inputs

Recovery Point Objective (how much data loss is acceptable, driven by replication schedule frequency) and Recovery Time Objective (how fast service must be restored, driven by how quickly a failover group can be triggered and how fast downstream systems can repoint to the new account) are the two numbers that should drive every configuration choice in Topics 54–55 — tighter RPO/RTO requirements justify more frequent replication and pre-tested failover runbooks; looser requirements justify cheaper, less frequent replication.

The runbook — the part tooling can't automate

A failover group can move data and objects, but it can't by itself update DNS/connection strings pointing applications at the old account, re-issue credentials scoped to the new account, or notify stakeholders — a written, periodically tested runbook covering exactly these steps is what turns "we technically have a DR setup" into "we can actually recover within our stated RTO" during a real incident.

Testing, not just configuring

A failover group that's never actually been triggered in a drill carries meaningful unknown risk — untested replication configuration, stale credentials on the secondary account, or an application that silently hardcoded the primary account's URL are all the kind of gaps that only surface during an actual failover attempt, ideally as a planned drill rather than during a real outage.

Compared to other systems

This layered RPO/RTO-driven approach is standard disaster recovery practice across any infrastructure, cloud-native or not — what's distinctive here is simply how many of the layers (Time Travel, Fail-safe, replication, failover groups) are native, built-in Snowflake features rather than separately bolted-on tooling you'd have to assemble yourself with a traditional database.

The primitive underneath Topics 54–55

Module 10 showed replication being used for multi-region and cross-cloud disaster recovery. This topic is the primitive itself: database replication is Snowflake asynchronously copying a database's data and (optionally) its account-level objects from a primary account to one or more secondary accounts, on a schedule you control.

Setting it up

SELECT SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER('ENABLE_ACCOUNT_DATABASE_REPLICATION', 'true');

-- On the primary
ALTER DATABASE analytics_prod ENABLE REPLICATION TO ACCOUNTS aws_us_east_1.dr_account;

-- On the secondary — creates the replica and syncs
CREATE DATABASE analytics_prod AS REPLICA OF primary_account.analytics_prod;
ALTER DATABASE analytics_prod REFRESH;

A secondary database is read-only until it's promoted (Topic 58) — you can query it, but not write to it, which is exactly what prevents the classic two-primaries-diverging problem.

What replicates vs. what doesn't

Compared to other systems

Conceptually similar to PostgreSQL logical replication or MySQL binlog replication, but operating at the level of immutable micro-partition files rather than a row-level change stream — closer in spirit to how Delta Lake/Iceberg table replication tools sync whole table versions between storage locations.

Grouping replicated objects for one-command promotion

Replicating one database at a time (Topic 57) works, but a real production account has dozens of databases plus roles, warehouses definitions, and security integrations that all need to fail over together, consistently. A failover group is a named bundle of object types that replicate and promote as a single unit.

CREATE FAILOVER GROUP prod_fg
  OBJECT_TYPES = DATABASES, ROLES, WAREHOUSES, INTEGRATIONS
  ALLOWED_DATABASES = analytics_prod, billing_prod
  ALLOWED_ACCOUNTS = aws_us_east_1.dr_account
  REPLICATION_SCHEDULE = '10 MINUTE';

-- The moment of disaster — run on the SECONDARY account
ALTER FAILOVER GROUP prod_fg PRIMARY;

That single PRIMARY statement is the promotion — the secondary instantly becomes writable and the old primary (if it's even still reachable) automatically becomes a read-only secondary, avoiding a split-brain scenario where both accounts think they're primary.

Replication schedule sets your RPO

The REPLICATION_SCHEDULE directly determines Recovery Point Objective (Topic 56, Module 10) — a 10-minute schedule means at most 10 minutes of committed data could be lost in an unplanned failover; tightening it to 1 minute lowers RPO at the cost of more replication credits burned continuously.

Compared to other systems

The same shape as AWS Route 53 + multi-region RDS failover orchestration or Kubernetes multi-cluster failover tooling — a failover group is Snowflake's native, single-command version of what would otherwise require separately wiring together DNS, replication, and promotion scripts.

The full picture, previewed in Module 3 and Module 10

Secure Data Sharing (Module 3, Topic 14) assumes the consumer already has a Snowflake account. A reader account removes that assumption — it's a lightweight, provider-managed Snowflake account created specifically so a share can reach an organization that has no Snowflake account of its own.

CREATE MANAGED ACCOUNT partner_reader
  ADMIN_NAME = 'partner_admin'
  ADMIN_PASSWORD = '...'
  TYPE = READER;

GRANT IMPORTED PRIVILEGES ON DATABASE shared_db TO SHARE partner_share;
ALTER SHARE partner_share ADD ACCOUNTS = partner_reader;

Who pays, and what the reader can't do

Compared to other systems

No close analog in traditional databases — closest comparisons are AWS's cross-account resource sharing (RAM) or a SaaS vendor provisioning a scoped, view-only tenant for a customer's customer, but Snowflake bakes the compute-billing arrangement directly into the platform.

Secure Data Sharing, made discoverable

Topics 14 and 59 covered sharing data with a known counterparty. The Data Marketplace is the same underlying share mechanism made public and searchable — providers publish a "listing" that any Snowflake customer can discover and instantly attach, with no file transfer, no pipeline, and no separate copy of the data.

Consuming a listing

-- After accepting a listing from the marketplace UI, it appears as a database
SELECT * FROM weather_data_co.public.daily_forecast
WHERE city = 'Bengaluru';

Because the underlying mechanism is the same zero-copy share used everywhere else in this module, there's no ingestion lag or storage duplication — querying marketplace data joins live against your own tables exactly like any other cross-database query.

Free vs. paid listings

Compared to other systems

Plays a similar role to AWS Data Exchange or a public API marketplace, but the consumption experience is native SQL against a database rather than an API call with its own auth and rate limits — this is the marketplace's main practical advantage for analytics teams.

The forward-reference from Module 1 pays off

Module 1 noted that micro-partitions "essentially pioneered" the pattern Apache Iceberg later formalized as an open standard: immutable data files plus a rich, versioned metadata layer describing them. Iceberg tables let Snowflake read and write data in that open format directly, stored in your own cloud storage rather than Snowflake's proprietary internal format.

CREATE ICEBERG TABLE events_iceberg (
  event_id STRING,
  event_ts TIMESTAMP_NTZ
)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_s3_volume'
  BASE_LOCATION = 'events/';

Catalog options: who's the source of truth on structure

Compared to other systems

This is Snowflake meeting Databricks/Delta Lake and the open lakehouse ecosystem on shared ground — where a regular Snowflake table is comparable to a proprietary warehouse table, an Iceberg table inside Snowflake is functionally interchangeable with an Iceberg table written by Spark or queried by Athena.

Why Snowflake needed a second storage engine at all

Every table type covered so far is columnar and micro-partition based (Module 1, Topic 3) — excellent for scanning millions of rows, structurally poor at "fetch exactly one row by primary key in single-digit milliseconds," which is the bread-and-butter access pattern of an operational application. Hybrid tables add a genuinely different, row-oriented storage engine for that use case, inside the same platform.

CREATE HYBRID TABLE user_sessions (
  session_id STRING PRIMARY KEY,
  user_id STRING NOT NULL,
  last_seen TIMESTAMP_NTZ,
  INDEX idx_user (user_id)
);

SELECT * FROM user_sessions WHERE session_id = 'sess_8821'; -- millisecond point lookup

What's genuinely new here vs. every other table type

Compared to other systems

Hybrid tables are Snowflake's answer to what HTAP databases and Postgres-alongside-a-warehouse architectures have long provided separately — the goal is to avoid standing up a whole separate operational database (and the sync pipeline between it and the warehouse) just to serve an application's low-latency lookups.

Querying data Snowflake never took ownership of

Every table discussed until Topic 61 stores its bytes inside Snowflake's own managed storage. An external table instead points at files sitting in your own cloud storage (S3, ADLS, GCS) and exposes them as a queryable table without ever copying the bytes in — Snowflake only stores a metadata layer describing the files.

CREATE EXTERNAL TABLE raw_logs (
  log_date DATE AS (TO_DATE(SPLIT_PART(metadata$filename,'/',2))),
  value VARIANT AS (VALUE:c1)
)
  PARTITION BY (log_date)
  LOCATION = @my_external_stage/logs/
  FILE_FORMAT = (TYPE = PARQUET);

The real cost: no micro-partition pruning metadata

Because Snowflake never ingested the bytes, it doesn't have the rich per-file min/max statistics that make partition pruning (Module 1, Topic 3) so effective on native tables — an external table relies on the coarser PARTITION BY expression and file-path structure instead, which is why external tables are consistently slower than loading the same data natively.

Compared to other systems

The direct equivalent of Amazon Athena or a Hive external table — SQL access to files without an ingestion step; Iceberg tables (Topic 61) with an external catalog are the more modern, better-optimized evolution of this same "don't move the bytes" idea.

The connection object referenced throughout the course

Snowpipe (Module 5, Topic 22) and external tables (Topic 63) both assumed a stage already existed. A stage is simply a named pointer to a storage location plus the credentials/integration needed to read it — an external stage points at storage you own, outside Snowflake.

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123:role/snowflake-role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/data/');

CREATE STAGE my_external_stage
  URL = 's3://my-bucket/data/'
  STORAGE_INTEGRATION = s3_int;

A storage integration (rather than a raw access key pasted into the stage) is the recommended pattern — it uses cloud-native role assumption, so no long-lived secret credential sits inside Snowflake at all.

Compared to other systems

Functionally similar to an external schema/foreign data wrapper connection in PostgreSQL, or a Spark session's configured cloud storage credentials — the concept of "a reusable, credentialed pointer to external storage" recurs in essentially every system that separates compute from storage.

The same idea, storage owned by Snowflake instead

An external stage (Topic 64) points at storage you manage. An internal stage is the mirror image — a staging area inside Snowflake's own managed storage, useful for uploading local files before loading them, with no cloud storage account of your own required.

CREATE STAGE my_named_stage;
PUT file://local_data.csv @my_named_stage;
COPY INTO my_table FROM @my_named_stage
  FILE_FORMAT = (TYPE = CSV);

Compared to other systems

Comparable to an upload staging bucket a traditional ETL tool manages itself before a bulk-load command — Snowflake's version simply removes the need to provision and secure that bucket separately.

A queryable file listing attached to a stage

Stages (Topics 64–65) hold files, but by default SQL can't easily browse "what files are actually sitting here." Enabling a directory table on a stage exposes exactly that as a queryable, always-current catalog of file names, sizes, and cloud storage URLs — most commonly used with unstructured files like images or PDFs rather than structured data ready to load.

CREATE STAGE images_stage
  URL = 's3://my-bucket/images/'
  STORAGE_INTEGRATION = s3_int
  DIRECTORY = (ENABLE = TRUE);

SELECT relative_path, size, last_modified, BUILD_SCOPED_FILE_URL(@images_stage, relative_path)
FROM DIRECTORY(@images_stage)
WHERE relative_path LIKE '%.jpg';

Compared to other systems

Similar to querying S3 object listings via s3 ls or an S3 Inventory report, but exposed as ordinary SQL joinable against other tables — useful for pairing structured metadata with a catalog of unstructured files (e.g. joining a product table to its product photos).

Where metadata$filename came from

Topic 63's external table definition quietly used metadata$filename without explaining it — these pseudo-columns are automatically available on any staged file during a COPY INTO, external table definition, or query directly against a stage, without needing to be part of the source file itself.

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, $1, $2
FROM @my_stage
WHERE METADATA$FILENAME LIKE '%2026-06%';

Compared to other systems

Analogous to Spark's input_file_name() function or Hive's INPUT__FILE__NAME virtual column — the same "which source file did this row come from" need shows up across every engine that reads from a directory of files rather than a single managed table.

Diagnosing "is this warehouse actually too small?"

Warehouse sizing (Module 1, Topic 2) is a guess until it's checked against real load. The WAREHOUSE_LOAD_HISTORY view breaks each 5-minute window into average running vs. queued query counts, which is the direct evidence for whether a warehouse is undersized, over-concurrent, or fine as-is.

SELECT start_time, warehouse_name,
       avg_running, avg_queued_load, avg_queued_provisioning
FROM snowflake.account_usage.warehouse_load_history
WHERE warehouse_name = 'ANALYTICS_WH'
ORDER BY start_time DESC;

Compared to other systems

Plays the same role as connection pool saturation metrics in a traditional database, or CPU/queue-depth dashboards for a Spark cluster — the specific insight Snowflake adds is separating "queued because busy" from "queued because cold-starting," which most systems blend into one generic wait metric.

The mechanic behind Module 8's transaction preview

Module 8's stored procedure transaction section briefly noted that an open transaction "holds row-level locks." Here's the full mechanic: when a DML statement modifies rows, Snowflake locks the specific micro-partitions containing those rows for write until the transaction commits or rolls back — readers are never blocked (Topic 71 explains why), but a second writer targeting overlapping rows queues.

-- Session A
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 501; -- lock acquired, not yet committed

-- Session B, run concurrently
UPDATE orders SET status = 'cancelled' WHERE order_id = 501; -- BLOCKS until Session A commits/rolls back

Lock granularity: micro-partition, not individual row

Because storage is organized into micro-partitions (Module 1, Topic 3) rather than individually addressable rows on disk, the practical lock granularity is closer to "the micro-partitions containing the affected rows" — two updates to logically unrelated rows that happen to live in the same micro-partition can still briefly contend, an edge case worth knowing about on very hot, narrow tables.

Compared to other systems

Row-level locking on writers mirrors PostgreSQL and MySQL/InnoDB — the notable difference (covered fully in Topic 71) is that Snowflake never uses locks to block readers, which traditional databases sometimes still do under stricter isolation settings.

Only one isolation level exists — and that's a deliberate simplification

Unlike PostgreSQL, which offers four SQL-standard isolation levels, Snowflake supports exactly one: Read Committed. There's no dial to turn — every statement sees only data committed before that statement began, full stop, which removes an entire category of tuning decisions (and an entire category of subtle bugs from picking the wrong one) other systems leave to the user.

BEGIN;
INSERT INTO orders VALUES (...);
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';
COMMIT; -- both changes become visible to other sessions atomically, together

Statement-level vs. transaction-level consistency

Compared to other systems

PostgreSQL defaults to Read Committed too, but also offers Repeatable Read and Serializable for stricter guarantees; Snowflake's decision to offer only one level is a deliberate simplicity-over-flexibility tradeoff, betting that most analytical and even most operational workloads don't actually need the stricter (and slower) alternatives.

The mechanism that makes "readers never block" true

Topics 69–70 both promised readers are never blocked by writers. Multi-Version Concurrency Control is why: because micro-partitions are immutable (Module 1, Topic 3), an UPDATE never modifies a file in place — it writes brand-new micro-partitions representing the post-update state and atomically swaps a metadata pointer, while the old micro-partitions stick around (feeding Time Travel, Module 8 Topic 38) until no longer needed.

Step by step

Compared to other systems

The same MVCC principle PostgreSQL uses internally (with its own row-versioning scheme and periodic VACUUM), but implemented at the coarser granularity of whole immutable micro-partition files rather than individual row versions — which is also precisely why Snowflake never needs a VACUUM-style maintenance process; old micro-partitions simply age out per the retention window (Module 8, Topic 40).

What actually lives in the Cloud Services Layer

Module 1's architecture overview named the Cloud Services Layer but treated it as one black box. It's really a set of independent, purpose-specific services, all free of warehouse compute cost, that together make everything else in this course possible.

Compared to other systems

Loosely analogous to a traditional database's catalog/system tables plus its query planner combined — Snowflake's distinguishing choice is running this layer as independently scaled, multi-tenant infrastructure shared across all customers, rather than a single process bolted onto each individual database instance.

Two places to look, with a real tradeoff between them

Query Profile (Module 2, Topic 5) inspects one query in detail. Query history answers "what ran across the account over time," and it's exposed through two different surfaces with different latency and retention characteristics.

SELECT query_id, user_name, warehouse_name,
       total_elapsed_time, bytes_scanned, credits_used_cloud_services
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 20;

Compared to other systems

Comparable to pg_stat_statements in PostgreSQL or Spark's history server, but split across two latency/retention tiers instead of one — a pattern that shows up again in Topic 75's Access History, which follows the identical two-surface structure.

The standard-SQL catalog, scoped per database

Topic 73 already used it in passing — INFORMATION_SCHEMA is the ANSI SQL-standard set of system views describing an individual database's own structure: tables, columns, views, grants, and (as seen) recent query activity. It exists automatically inside every database, with no setup required.

SELECT table_name, column_name, data_type
FROM analytics_prod.information_schema.columns
WHERE table_schema = 'PUBLIC'
ORDER BY table_name;

Information Schema vs. Account Usage — the same split, generalized

Compared to other systems

INFORMATION_SCHEMA is the exact same ANSI standard PostgreSQL, MySQL, and SQL Server all implement — a query written against one database's INFORMATION_SCHEMA.COLUMNS is largely portable across engines, unlike anything in the Snowflake-proprietary ACCOUNT_USAGE schema.

Query history answers "what ran." This answers "who touched what"

Query history (Topic 73) tells you a query ran and how long it took, but not which specific columns it actually read or wrote — critical for proving compliance around sensitive data governed by masking policies (Module 3, Topic 9) or row access policies (Module 3, Topic 10). ACCESS_HISTORY fills that gap with column-level read/write tracking.

SELECT query_id, user_name, query_start_time,
       base_objects_accessed, direct_objects_accessed
FROM snowflake.account_usage.access_history
WHERE query_start_time > DATEADD(day, -30, CURRENT_TIMESTAMP())
AND ARRAY_CONTAINS('SSN'::VARIANT, PARSE_JSON(base_objects_accessed):columns);

Compared to other systems

Goes further than PostgreSQL's or most databases' native audit logging, which typically stops at statement-level logging — column-level access tracking natively built into the platform (rather than requiring a separate audit-logging extension) is a meaningful governance differentiator.

The umbrella Module 3 pointed toward

Module 3's tag-based security topic mentioned auto-classification "covered under Horizon in Module 11." Horizon is Snowflake's built-in governance layer that ties together everything scattered across earlier modules — tagging, masking, row access policies, access history, and lineage — into one searchable catalog and policy surface, instead of managing each piece independently.

Compared to other systems

Occupies the same role as a standalone data catalog/governance tool (e.g. Alation, Collibra, or Purview) — Horizon's advantage is being native to the platform, so classification and policy enforcement stay in sync with the data automatically rather than through a separately synced external catalog.

Shipping logic, not just data, through the Marketplace

The Data Marketplace (Topic 60) distributes data. The Native App Framework distributes an entire application — tables, stored procedures (Module 6, Topic 30), UDFs, and a UI — that installs directly into a consumer's own account and runs on their compute against their data, without the provider ever seeing that data.

-- Provider side: package the app
CREATE APPLICATION PACKAGE churn_predictor_pkg;
CREATE APPLICATION churn_predictor FROM APPLICATION PACKAGE churn_predictor_pkg;

-- Consumer side: install and run entirely inside their own account
CALL churn_predictor.predict('my_customers_table');

Compared to other systems

Closest to how a database extension or a Salesforce AppExchange package installs and runs inside the customer's own environment — genuinely distinct from typical SaaS, where the vendor's servers process the customer's data rather than the reverse.

DataFrame code that compiles down to SQL, not a separate engine

Python UDFs (Module 6, Topic 28) let a single function run Python inside Snowflake. Snowpark goes further: it's a DataFrame API — deliberately similar to PySpark's — where the transformations you chain in Python, Scala, or Java are lazily translated into SQL and pushed down to run on a Snowflake warehouse, not shipped off to an external Spark cluster.

# Python, using the Snowpark DataFrame API
from snowflake.snowpark import Session
df = session.table("orders")
result = (df.filter(df["status"] == "shipped")
            .group_by("region")
            .agg({"amount": "sum"}))
result.show()  # only NOW does it compile to SQL and execute

Why teams migrating from Spark reach for this

Compared to other systems

Deliberately API-compatible in spirit with PySpark's DataFrame API, which is precisely why teams migrating off Spark find the learning curve shallow — the meaningful difference is that Snowpark has no separate cluster to size or manage at all; warehouse sizing (Module 1, Topic 2) is the only compute knob.

LLM calls as a SQL function, on data that never leaves the warehouse

Cortex is a family of managed AI functions — LLM completion, summarization, translation, sentiment, and vector embeddings/search — callable directly as SQL functions, so unstructured text sitting in a table can be summarized or classified without exporting it to an external AI service first.

SELECT review_id,
       SNOWFLAKE.CORTEX.SENTIMENT(review_text) AS sentiment_score,
       SNOWFLAKE.CORTEX.SUMMARIZE(review_text) AS summary
FROM customer_reviews
WHERE review_date > DATEADD(day, -1, CURRENT_DATE());

Vector search — the retrieval half of RAG

SELECT doc_id, doc_text
FROM knowledge_base
ORDER BY VECTOR_COSINE_SIMILARITY(
  embedding, SNOWFLAKE.CORTEX.EMBED_TEXT_768('e5-base-v2', 'refund policy')
) DESC LIMIT 5;

A native VECTOR data type plus similarity functions means the "find the most relevant document chunks" half of a retrieval-augmented-generation pipeline is a normal ORDER BY against a normal table — no separate vector database required for workloads already living in Snowflake.

Compared to other systems

Covers ground similarly split between OpenAI/Anthropic API calls (the generation half) and a dedicated vector database like Pinecone (the retrieval half) — Cortex's pitch is collapsing both into the warehouse, trading some model-choice flexibility for keeping sensitive data inside Snowflake's existing governance perimeter (Topic 76).

When Python/SQL UDFs (Module 6) aren't flexible enough

Python UDFs run a function. Snowpark Container Services runs an arbitrary Docker container — a full custom runtime, GPU access, any language or ML framework, even a long-running web service — directly on Snowflake-managed compute, for workloads that don't fit inside a single-function UDF model at all.

CREATE COMPUTE POOL gpu_pool
  MIN_NODES = 1 MAX_NODES = 3
  INSTANCE_FAMILY = GPU_NV_S;

CREATE SERVICE model_server
  IN COMPUTE POOL gpu_pool
  FROM SPECIFICATION $$
    spec:
      containers:
        - name: inference
          image: /my_db/my_schema/my_repo/model-server:latest
  $$;

Compared to other systems

Directly comparable to running a workload on Kubernetes or ECS, but with the compute pool provisioned and billed through Snowflake and given governed proximity to warehouse data — the target audience is ML model serving and custom workloads that Python UDFs and Snowpark (Topic 78) genuinely can't express.

Where output from your own code lands

A Python UDF (Module 6, Topic 28), a stored procedure, or a container service (Topic 80) can emit logs, traces, and metrics — but they need somewhere to land. An event table is a special, schema-fixed table that captures exactly that telemetry, and it's the foundation Topics 82–84 (alerts, observability, logging) all build on.

CREATE EVENT TABLE my_app_events;
ALTER ACCOUNT SET EVENT_TABLE = my_db.my_schema.my_app_events;

-- Inside a Python UDF/procedure:
import logging
logger = logging.getLogger("my_handler")
logger.info("processed batch", extra={"rows": 4200})

Compared to other systems

Serves the role a centralized log aggregator like Datadog or CloudWatch Logs plays for application code — the distinguishing feature is that it's just a Snowflake table, so correlating an error log with the exact business data row that triggered it is a plain SQL join, not a cross-system lookup.

Tasks (Module 4), specialized for "check and notify"

A Task (Module 4, Topic 20) runs SQL on a schedule. An Alert is the same scheduling engine with a built-in condition check and a notification action baked in — instead of writing your own "run a query, check a threshold, then call a webhook" task chain by hand.

CREATE ALERT low_inventory_alert
  WAREHOUSE = alert_wh
  SCHEDULE = '15 MINUTE'
  IF (EXISTS (SELECT 1 FROM inventory WHERE qty < reorder_threshold))
  THEN CALL SYSTEM$SEND_EMAIL(
    'ops_notification', 'ops@company.com',
    'Low inventory detected', 'Check the inventory dashboard.');

Compared to other systems

The same shape as a Prometheus alerting rule or a CloudWatch alarm — a scheduled condition check plus a notification action — scoped here to SQL conditions against warehouse data rather than infrastructure metrics.

One level up from a single log line

Where Topic 81's event table captures individual log/metric/trace records, observability is about connecting those records into the bigger picture — tracing a single request as it moves through a chain of stored procedures and UDFs, and visualizing that in a dashboard rather than reading raw rows.

# Emitting a trace span inside a Python handler
from opentelemetry import trace
tracer = trace.get_tracer("my_pipeline")
with tracer.start_as_current_span("validate_batch"):
    validate(batch)

Because Snowflake's telemetry pipeline speaks the OpenTelemetry standard, spans emitted this way can show a single logical operation's full path across multiple procedure calls — not just "procedure X logged a line," but "request 8821 took 400ms, 300 of which was inside validate_batch."

Compared to other systems

Mirrors what Jaeger/Zipkin do for distributed tracing in microservice architectures — the OpenTelemetry compatibility means existing tracing tooling and dashboards used elsewhere in an organization's stack can often plug directly into Snowflake-emitted spans.

Controlling the volume, not just the destination

Topic 81 established where logs land. This topic is about controlling how much lands there — log level thresholds work exactly like any application framework's, and they're configurable per database, schema, or even individual function, so a noisy debug session doesn't flood the event table for the whole account.

ALTER FUNCTION my_udf(STRING) SET LOG_LEVEL = 'DEBUG';
ALTER SCHEMA my_schema SET LOG_LEVEL = 'WARN'; -- quieter default for everything else

Compared to other systems

Behaves exactly like Python's logging module or Log4j's level hierarchy — familiar to anyone who's tuned application logging before, just scoped to Snowflake objects instead of an application process.

"Where did this column's value actually come from"

Access History (Topic 75) tracks who read a column. Lineage tracks how a column's value was derived — which upstream tables, views, Streams (Module 4, Topic 19), and Tasks (Module 4, Topic 20) fed into it, automatically inferred from executed SQL and surfaced through Horizon (Topic 76) as a navigable graph rather than something you build by hand.

The two directions that matter

Compared to other systems

The same category of capability a standalone catalog tool like Collibra, Alation, or OpenLineage provides — native, automatic lineage removes the common failure mode of a bolted-on catalog silently falling out of sync with the real pipelines.

The organizational practice this whole module has been building toward

A data contract isn't a single Snowflake feature — it's a discipline for making schema and semantics an explicit, enforced agreement between a data producer and its consumers, assembled from primitives already covered: enforced constraints on hybrid tables (Topic 62), tag-based classification (Module 3, Topic 11 / Horizon, Topic 76), and Tasks/Alerts (Topic 82) that validate incoming data against expectations.

-- A contract encoded as enforceable checks, not just documentation
CREATE OR REPLACE PROCEDURE validate_orders_contract()
RETURNS STRING LANGUAGE SQL AS $$
BEGIN
  LET bad_rows INTEGER := (SELECT COUNT(*) FROM staged_orders
                       WHERE order_id IS NULL OR amount < 0);
  IF (bad_rows > 0) THEN RETURN 'CONTRACT VIOLATION: ' || bad_rows || ' bad rows';
  END IF;
  RETURN 'OK';
END;
$$;

What a real contract typically pins down

Compared to other systems

The same idea as an API contract (OpenAPI spec) or a Protobuf schema in event-driven systems, applied to warehouse tables instead of service payloads — the difference is data contracts here compose from existing Snowflake governance primitives rather than requiring a wholly separate contract-testing framework.

Same destination, opposite philosophies

Streams + Tasks (Module 4, Topics 19–21) give you an offset pointer plus a scheduler you fully control — you write the MERGE, you decide the cadence, you own the DAG. Dynamic Tables (Module 5, Topic 25) flip that: you declare the target query, and Snowflake figures out the incremental refresh plan and dependency graph for you. The tradeoff is always control versus operational burden.

Decision matrix

When should I choose Streams + Tasks?

When the transformation needs real procedural logic — conditional SCD Type 2 handling, multi-table transactional writes, custom error handling per row, or calling a stored procedure with side effects. Anything a single SELECT can't express belongs here.

When should I choose Dynamic Tables?

When the transformation is expressible as a query — joins, aggregations, filters — and you'd rather declare the target state and let Snowflake own the refresh mechanics, especially across a multi-hop pipeline where manually chaining Tasks would mean hand-building a DAG that Dynamic Tables gives you for free.

Which is better for CDC? Which for complex transformations?

Real production example

A fintech ingesting raw transaction events uses a Stream + Task to run a stored procedure that applies fraud-flagging business rules with branching logic and writes an audit row on rejection — genuinely procedural. Downstream of that clean transaction table, three Dynamic Tables compute daily account balances, rolling 30-day spend aggregates, and a fraud-review dashboard feed — all pure declarative SQL chained automatically, with target lag tuned per table (1 hour for balances, 15 minutes for the fraud feed).

Three ingestion paths, three different bottlenecks

All three eventually call the same underlying loading machinery, but where the bottleneck sits is different: Snowpipe (Topic 22) waits on file staging, the Kafka Connector (Topic 24) wraps Snowpipe Streaming with Kafka-specific offset management, and raw Snowpipe Streaming (Topic 23) is the lowest-level, lowest-latency API of the three.

Deep comparison

Why choose one over another

The decision usually isn't really about Snowflake — it's about where the data already lives. If it's already landing as files in cloud storage, Snowpipe is the path of least resistance. If it's already flowing through a Kafka cluster, the Connector avoids writing custom consumer code. If you're building a new application and want the lowest possible latency with full control, calling the Snowpipe Streaming SDK directly from application code skips both the file-staging and the Kafka-broker hop entirely.

Real architectures

The question underneath the question: who owns the bytes?

Native tables (any table you've built through the whole course so far) store data in Snowflake's proprietary micro-partition format (Module 1, Topic 3) that only Snowflake's engine can read. Iceberg Tables (Topic 61) store data as open Parquet files with Iceberg metadata, readable by Spark, Trino, Athena, or any other Iceberg-aware engine — Snowflake becomes one consumer among several, not the sole owner.

Deep comparison

When should an enterprise use Iceberg?

When multiple compute engines beyond Snowflake genuinely need to read the same physical data — a common pattern where Spark handles ML feature engineering and Snowflake handles BI on identical tables without a duplicate ETL copy. Also relevant for large enterprises actively avoiding single-vendor lock-in as an explicit governance requirement, or migrating off a legacy lakehouse incrementally without a big-bang cutover.

When are native tables better?

When Snowflake is genuinely the only consumer of the data (the common case for most BI and internal analytics workloads), where the extra interoperability of Iceberg buys nothing but adds operational surface area — you'd be paying the open-format performance and management tax for a feature you never use.

Lakehouse design example

A retailer keeps raw and curated event data as Iceberg Tables in their own S3 bucket, since both a Spark-based ML training pipeline and Snowflake BI dashboards need to read it without duplicating storage. Highly-accessed, Snowflake-only aggregate tables built downstream of that (daily sales summaries, dashboard-ready marts) stay native, since no other engine ever touches them and native performance matters more there than portability.

Three optimizations solving three different query shapes

These three (Search Optimization Service, Topic 7; Clustering, Module 1 Topic 4; Materialized Views, Topic 8) get conflated because they all "make queries faster," but they attack completely different access patterns. Picking the wrong one means paying maintenance overhead for a speedup you'll never see.

Deep compare by access pattern

Cost and maintenance comparison

Decision framework

Ask: is the query filtering on a specific value (→ Search Optimization), filtering on a range of a sortable key (→ Clustering), or repeating the same aggregation/join shape over and over (→ Materialized View)? These aren't mutually exclusive — a large fact table commonly has clustering on a date column for range scans, Search Optimization on a customer ID for support lookups, and a Materialized View on top for the dashboard's daily rollup, each solving a different query shape on the same underlying data.

Migrating 100TB+ safely is a sequencing problem, not a technical one

Every individual piece of an Oracle-to-Snowflake migration uses features already covered in this course. What makes it hard isn't any single step — it's getting the order and the safety nets right so a business can keep running on the old system while the new one proves itself.

Reference architecture: On-prem Oracle → S3 → Snowflake

The seven phases

How enterprises actually migrate 100TB+

The bulk load itself is rarely the bottleneck — it's a bandwidth and warehouse-sizing problem solved by scaling up temporarily. The real work is phases 3–6: building validation that a business stakeholder trusts, and a cutover granular enough (by workload, not all-at-once) that a bad surprise in one downstream consumer doesn't block the whole program.

Scenario: the bill jumped from $8k to $40k this month

This is one of the most common architect-interview scenarios because it forces you to demonstrate you actually know where cost comes from across the whole platform, not just "warehouses cost money." Work it as a systematic elimination, not a guess.

The debugging playbook, in order

Real debugging playbook query

-- Start broad: which warehouse categories grew month over month
SELECT warehouse_name,
       DATE_TRUNC('day', start_time) AS day,
       SUM(credits_used) AS daily_credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('month', -2, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY 1, 2;

Most common root causes, ranked by frequency

One account can't isolate everything you eventually need isolated

Multi-Environment Setup (Topic 50) covered dev/QA/prod separation within reach of a single account's RBAC. At enterprise scale, that's not enough — you need blast-radius isolation between business units, hard cost-attribution boundaries, and region-specific data residency, all of which a Snowflake Organization (a container of multiple accounts) is built to provide.

Reference hierarchy

Key design decisions

Global enterprise example

A multinational retailer runs one account per major region (US, EU, APAC) to satisfy data residency law, plus a separate central governance account holding no customer data — only shared RBAC role templates, masking policy definitions, and an org-wide cost dashboard fed by ORGANIZATION_USAGE views. Each regional account maintains its own dev/QA/prod databases internally, since environment isolation there doesn't need to cross a legal boundary the way region isolation does.

Why isolate warehouses instead of running everything on one?

One shared warehouse looks simpler on a diagram, but different workload types have fundamentally incompatible resource-usage patterns. Mixing them means the worst-behaved workload degrades all the others, and — just as importantly for a real business — nobody can answer "what does BI actually cost us" when every workload's spend is blended together.

Reference warehouse strategy

Cost isolation as a byproduct, not just performance

Resource Monitors (Topic 52) attached per-warehouse turn this performance-driven design into automatic cost governance for free — a resource monitor on the data-science warehouse can hard-suspend it at a budget ceiling without any risk of taking down the production BI warehouse, because they were never sharing compute or a budget line in the first place.

Composing individual controls into one governed system

Module 3 built each security primitive individually — masking policies (Topic 9), row access policies (Topic 10), tags (Topic 11), secure views (Topic 12). Enterprise security architecture is the discipline of composing all of them under one centralized RBAC model so a hundred new tables inherit the correct governance automatically, instead of someone remembering to apply policies by hand on every new table.

Centralized RBAC role hierarchy

How the pieces chain together

Real enterprise example

A healthcare company tags every column containing patient data as PHI at creation time via a required schema-creation template. A single masking policy attached to the PHI tag handles all of them; a single row access policy chained to an analyst's department claim (via a mapping table) restricts each analyst to their assigned patient population. When a new hospital system is onboarded and new tables get created, tagging them PHI is the only manual step — masking and row access apply automatically because they're bound to the tag, not the table.

An architect is judged on the recovery plan, not just the design

Any pipeline you design in this module will eventually fail somewhere. The question interviewers actually care about isn't "can this fail" — everything can — it's "what happens next, automatically or by runbook, and how do you know it happened."

Recovery playbooks by failure type

The pattern underneath every playbook

How to use this topic

Below are architect-level scenarios spanning every category interviewers draw from. Eight are worked in full (question → how to think → best answer → tradeoffs) since that's the actual shape a strong answer takes; the rest are given as a structured bank so you can self-practice the same four-part structure using concepts from Modules 1–12.

Eight scenarios, fully worked

Additional scenario bank — practice these using the same four-part structure

What problem it solves

You need the target table to be an exact mirror of the source, and you either don't have a reliable way to detect what changed, or the source is small enough that recomputing everything is cheaper than tracking changes. Full refresh solves the "I'm not sure what changed, so I'll just reload everything" problem.

Why it exists

Not every source system exposes a change timestamp, a CDC log, or a reliable primary key. Small reference tables, vendor extracts, and daily snapshot files from legacy systems often arrive as a single complete file with no delta information. Full refresh is the fallback pattern that works no matter how bad the source data hygiene is — it makes zero assumptions beyond "the file/table I received is the complete truth as of now."

When to use it

Internal working

Two mechanics, both common in production:

Step-by-step flow

Source → Extract everything → Stage → Replace target

1. Extract full data from the source system (every row, no filter).
2. Land the extract as a file in cloud storage (S3/Azure/GCS).
3. COPY INTO a staging table in Snowflake.
4. Validate row count and key checks on the staging table.
5. Atomically swap the staging table into the target (CTAS or transaction-wrapped truncate+insert).
6. Drop or truncate the staging table for the next run.

DDL

CREATE OR REPLACE TABLE orders_target (
    order_id        NUMBER(38,0)   NOT NULL,
    customer_id     NUMBER(38,0)   NOT NULL,
    order_status    VARCHAR(20)    NOT NULL,
    order_amount    NUMBER(12,2)   NOT NULL,
    order_date      DATE           NOT NULL,
    updated_at      TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE orders_source (
    order_id        NUMBER(38,0)   NOT NULL,
    customer_id     NUMBER(38,0)   NOT NULL,
    order_status    VARCHAR(20)    NOT NULL,
    order_amount    NUMBER(12,2)   NOT NULL,
    order_date      DATE           NOT NULL,
    updated_at      TIMESTAMP_NTZ  NOT NULL
);

CREATE OR REPLACE TABLE orders_stage LIKE orders_target;

Sample INSERT data — Day 1 (100 rows, showing first 3)

INSERT INTO orders_source VALUES
  (1001, 501, 'PLACED',   129.50, '2026-06-01', '2026-06-01 09:12:00'),
  (1002, 502, 'SHIPPED',   58.00, '2026-06-01', '2026-06-01 10:03:00'),
  (1003, 503, 'DELIVERED', 210.75,'2026-06-01', '2026-06-01 11:47:00');
  -- ... 97 more rows for a total of 100

Full refresh load — Day 1

-- Step 1: bulk-load Day 1 extract into staging
COPY INTO orders_stage
  FROM @orders_ext_stage/day1/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- Step 2: atomic swap into target
CREATE OR REPLACE TABLE orders_target AS
SELECT * FROM orders_stage;

-- Result: orders_target now has exactly 100 rows

Day 2 — source data changes, full refresh happens again

By Day 2 the source still has 100 rows, but 12 of them have updated statuses and 1 was cancelled and removed entirely. A full refresh doesn't care what changed — it reloads all 100 (now 99, since one was removed) unconditionally.

-- Day 2: source now reflects 99 current rows (order 1050 was removed upstream)
TRUNCATE TABLE orders_stage;

COPY INTO orders_stage
  FROM @orders_ext_stage/day2/
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- Atomic replace — orders_target now mirrors Day 2 exactly, including the deletion
CREATE OR REPLACE TABLE orders_target AS
SELECT * FROM orders_stage;

Validation queries

-- Row count sanity check against the source file's expected count
SELECT COUNT(*) AS target_row_count FROM orders_target;

-- Duplicate primary key check (should return 0 rows)
SELECT order_id, COUNT(*) 
FROM orders_target 
GROUP BY order_id 
HAVING COUNT(*) > 1;

-- Compare today's load against yesterday's row count for anomaly detection
SELECT 
    (SELECT COUNT(*) FROM orders_target) AS today_count,
    112 AS yesterday_count,  -- pulled from batch_audit table, Topic 112
    (SELECT COUNT(*) FROM orders_target) - 112 AS delta;

Recovery strategy

If a full refresh fails mid-load, the target table was never touched — CREATE OR REPLACE only swaps in the new version after the staging load fully succeeds, so a failed COPY simply leaves yesterday's orders_target intact. Recovery is: fix the source extract, rerun the pipeline. If you used plain TRUNCATE + INSERT instead, Time Travel is your safety net:

-- Restore the previous version if a bad TRUNCATE+INSERT already committed
CREATE OR REPLACE TABLE orders_target AS
SELECT * FROM orders_target AT (OFFSET => -60*30); -- 30 minutes ago

Common interview questions

• When is full load better than incremental? When the source has no reliable change-tracking column, the table is small enough that reload cost is trivial, or you need guaranteed deletion propagation without building delete-detection logic.
• What's the risk of TRUNCATE + INSERT without a transaction? A reader can query the table while it's empty, seeing zero rows — a correctness bug for anything reading from it concurrently.
• Why is CREATE OR REPLACE preferred over TRUNCATE+INSERT in production? It's atomic — the swap either fully happens or doesn't happen at all, so there's never a partially-loaded or empty state visible to readers.
• What happens to grants and masking policies on CREATE OR REPLACE TABLE? They are dropped and must be explicitly reapplied — this is a very common production outage cause.

Pros / Cons

Practice questions

• Rewrite the Day 2 load using TRUNCATE + INSERT wrapped in an explicit transaction, and explain what changes for concurrent readers versus the CTAS approach.
• The source file for Day 3 arrives with only 40 rows instead of the expected ~99. Write a validation query that would catch this before the swap happens, and describe how you'd prevent a bad partial file from ever reaching orders_target.

What problem it solves

Full refresh (Topic 99) reprocesses every row, every run. Once a table has tens or hundreds of millions of rows, reloading everything to pick up a handful of new or changed rows is wasteful — slower, more expensive, and it puts unnecessary load on the source system. Incremental load solves this by loading only the rows that changed since the last successful run.

Why it exists

Compute and time both scale with rows processed. If only 5 out of 100,000 rows changed today, a well-designed incremental load touches 5 rows, not 100,000. This is the pattern that makes hourly or even sub-hourly refresh cycles economically viable at scale.

When to use it

Internal working

Companies track "how far we've already loaded" using one of these markers, stored durably in a watermark table — never in application memory, since the pipeline must survive restarts:

Step-by-step flow

1. Read the last successful watermark value from watermark_tracker.
2. Extract only source rows where updated_at > last_watermark.
3. Load the delta into a staging table.
4. MERGE the delta into orders_target (Topic 102).
5. On success, advance the watermark to MAX(updated_at) from the batch just loaded.
6. If the pipeline fails before the watermark advances, the next run safely reprocesses the same window — this is why the MERGE step must be idempotent (Topic 109).

DDL — watermark table

CREATE OR REPLACE TABLE watermark_tracker (
    pipeline_name     VARCHAR(100)   NOT NULL,
    last_watermark    TIMESTAMP_NTZ  NOT NULL,
    updated_at        TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (pipeline_name)
);

INSERT INTO watermark_tracker VALUES
  ('orders_incremental_load', '2026-06-01 00:00:00', CURRENT_TIMESTAMP());

Sample data — Day 1: 100 rows, Day 2: 5 new rows

-- Day 1 load already happened; watermark sits at 2026-06-01 23:59:59

-- Day 2: only 5 new/changed rows exist past that watermark
INSERT INTO orders_source VALUES
  (1101, 601, 'PLACED', 88.00,  '2026-06-02', '2026-06-02 08:15:00'),
  (1102, 602, 'PLACED', 44.25,  '2026-06-02', '2026-06-02 08:40:00'),
  (1103, 603, 'PLACED', 302.10, '2026-06-02', '2026-06-02 09:05:00'),
  (1104, 604, 'PLACED', 19.99,  '2026-06-02', '2026-06-02 09:22:00'),
  (1105, 605, 'PLACED', 156.80, '2026-06-02', '2026-06-02 10:01:00');

Full ETL — load only the 5 new rows

-- Step 1: read the watermark
SET last_wm = (SELECT last_watermark FROM watermark_tracker 
                WHERE pipeline_name = 'orders_incremental_load');

-- Step 2: extract only rows newer than the watermark
CREATE OR REPLACE TEMPORARY TABLE orders_delta AS
SELECT *
FROM orders_source
WHERE updated_at > $last_wm;
-- Returns exactly the 5 new rows, not all 105

-- Step 3: merge delta into target (full logic in Topic 102)
MERGE INTO orders_target t
USING orders_delta d
  ON t.order_id = d.order_id
WHEN MATCHED THEN UPDATE SET
    t.order_status = d.order_status,
    t.order_amount = d.order_amount,
    t.updated_at   = d.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, d.order_date, d.updated_at);

-- Step 4: advance the watermark — only after the MERGE succeeds
UPDATE watermark_tracker
SET last_watermark = (SELECT MAX(updated_at) FROM orders_delta),
    updated_at = CURRENT_TIMESTAMP()
WHERE pipeline_name = 'orders_incremental_load';

Validation queries

-- Confirm the delta matched expectations
SELECT COUNT(*) AS delta_row_count FROM orders_delta;  -- expect 5

-- Confirm target grew by exactly the expected amount
SELECT COUNT(*) FROM orders_target;  -- expect 105

-- Confirm watermark actually advanced (should not equal yesterday's value)
SELECT last_watermark FROM watermark_tracker 
WHERE pipeline_name = 'orders_incremental_load';

-- Look for rows that should have been picked up but weren't (gap detection)
SELECT * FROM orders_source
WHERE updated_at > (SELECT last_watermark FROM watermark_tracker 
                     WHERE pipeline_name = 'orders_incremental_load')
  AND order_id NOT IN (SELECT order_id FROM orders_target);

Recovery strategy

Because the watermark only advances after a successful MERGE, a mid-pipeline failure is safe by construction: rerunning the pipeline re-extracts the same window and the MERGE (being an upsert, Topic 102) simply re-applies the same changes without creating duplicates. The one failure mode to guard against is advancing the watermark before confirming the MERGE succeeded — always update the watermark last, in the same transaction as the MERGE if your orchestrator supports it.

-- If a bad watermark was already committed, reset it manually to reprocess a window
UPDATE watermark_tracker
SET last_watermark = '2026-06-01 23:59:59'
WHERE pipeline_name = 'orders_incremental_load';

Common interview questions

• What happens if the source allows updates that don't touch updated_at? Those changes are silently missed forever — this is the single biggest risk of timestamp-based incremental load, and why some teams pair it with a periodic full reconciliation load.
• Why update the watermark last, not first? If the MERGE fails after the watermark advances, those rows are never picked up again — a silent data-loss bug.
• How do you handle clock skew between source and Snowflake? Use the source system's own timestamp column (not Snowflake's load time), and add a small safety overlap window (e.g. re-pull the last 5 minutes every run) to tolerate near-boundary writes.

Practice questions

• Modify the watermark table to support multiple pipelines reading from the same source table with independent watermarks. What changes in the DDL and the extract query?
• A batch_id based incremental load is proposed instead of a timestamp. Write the DDL and MERGE logic, and explain one scenario where batch_id is safer than updated_at.

What problem it solves

Incremental load (Topic 100) tells you which rows changed, but not what kind of change happened — was a row inserted, updated, or deleted? CDC captures the actual operation type (INSERT / UPDATE / DELETE) for every change, which is required for accurate MERGE logic, especially delete propagation, which a plain updated_at filter can never see.

Why it exists

A source row that gets hard-deleted simply disappears — there's no updated_at to filter on, because the row no longer exists to be filtered. Without CDC, deletes upstream silently become "stale but present forever" rows downstream. CDC exists specifically to make deletes (and the full change history) visible to the pipeline.

How source systems generate changes

DDL — CDC change table

CREATE OR REPLACE TABLE orders_cdc (
    order_id          NUMBER(38,0)   NOT NULL,
    customer_id       NUMBER(38,0),
    order_status      VARCHAR(20),
    order_amount      NUMBER(12,2),
    operation_type    VARCHAR(1)     NOT NULL,   -- 'I', 'U', 'D'
    change_timestamp  TIMESTAMP_NTZ  NOT NULL
);

Example changes and how CDC captures them

Three real events happen on the source in sequence: a new order is placed, an existing order's status changes, and an old order is deleted.

-- Event 1: INSERT — new order placed
INSERT INTO orders_cdc VALUES
  (2001, 701, 'PLACED', 75.00, 'I', '2026-06-03 09:00:00');

-- Event 2: UPDATE — status changes from PLACED to SHIPPED
INSERT INTO orders_cdc VALUES
  (1050, 550, 'SHIPPED', 64.20, 'U', '2026-06-03 09:15:00');

-- Event 3: DELETE — order cancelled and physically removed at source
INSERT INTO orders_cdc VALUES
  (1032, 540, NULL, NULL, 'D', '2026-06-03 09:30:00');

Notice the DELETE row carries NULL for business columns — the source no longer has that data, only the fact that order_id 1032 no longer exists. This operation_type column is exactly what a plain incremental load can never produce.

Validation queries

-- Count of each operation type in the latest batch
SELECT operation_type, COUNT(*) 
FROM orders_cdc
WHERE change_timestamp > DATEADD(hour, -1, CURRENT_TIMESTAMP())
GROUP BY operation_type;

-- Sanity check: every order_id should have a sensible operation sequence
-- (a D should never appear before an I for the same order_id in raw feed order)
SELECT order_id, ARRAY_AGG(operation_type) WITHIN GROUP (ORDER BY change_timestamp) AS op_sequence
FROM orders_cdc
GROUP BY order_id
HAVING ARRAY_SIZE(op_sequence) > 1;

Recovery strategy

Because CDC captures the full change stream, recovery from a failed run is just "replay the events again from the last successfully-processed offset" — the same replay safety a Kafka consumer group relies on. Snowflake's own native building block for this is a Stream (covered in Module 4), which acts as a persistent, exactly-once-per-consumer CDC pointer over a table.

Common interview questions

• Why can't timestamp-based incremental load see deletes? A deleted row has nothing left to filter on — there's no row, so there's no updated_at to compare against a watermark.
• What's the tradeoff of trigger-based CDC? It adds write latency to every source transaction and is fragile — someone can disable a trigger without anyone downstream noticing until data goes stale.
• Why is log-based CDC preferred at scale? It reads a log the database already writes for its own durability — zero extra load on the live table, and it inherently captures every operation type including deletes.

Practice questions

• Design a trigger-based CDC table for a database you don't have log access to. Write the shadow table DDL and describe (in words) what each trigger would need to do.
• Given the three CDC events above, write the MERGE statement that would correctly apply all three to orders_target in one pass (this is fully solved in Topic 102 — attempt it first).

What problem it solves

Once you have a delta (from incremental load or CDC), you need to apply it to the target table without knowing in advance whether each row already exists there. MERGE solves "insert if new, update if it exists, and optionally delete if the source says so" in a single atomic statement — instead of a fragile sequence of separate INSERT/UPDATE/DELETE statements.

Why it exists

Before MERGE existed in SQL, upserting required either a DELETE-then-INSERT (loses row identity mid-transaction, breaks foreign key references) or a manual EXISTS check per row. MERGE lets the database engine handle matching, and it's naturally idempotent when keyed correctly — re-running the same MERGE with the same input produces the same result, which is exactly the safety property incremental pipelines need (Topic 109).

Explaining the clauses

Source rows vs. target rows

Target (orders_target) before the merge — 3 existing rows:

-- orders_target BEFORE merge
-- order_id | order_status | order_amount
-- 1001     | PLACED       | 129.50
-- 1032     | PLACED       | 45.00
-- 1050     | PLACED       | 64.20

Source delta (from CDC, Topic 101) — one insert, one update, one delete:

-- orders_cdc delta batch
-- order_id | order_status | order_amount | operation_type
-- 2001     | PLACED       | 75.00        | I
-- 1050     | SHIPPED      | 64.20        | U
-- 1032     | NULL         | NULL         | D

MERGE statement

MERGE INTO orders_target t
USING orders_cdc d
  ON t.order_id = d.order_id
WHEN MATCHED AND d.operation_type = 'D' THEN
    DELETE
WHEN MATCHED AND d.operation_type = 'U' THEN
    UPDATE SET
        t.order_status = d.order_status,
        t.order_amount = d.order_amount,
        t.updated_at   = d.change_timestamp
WHEN NOT MATCHED AND d.operation_type = 'I' THEN
    INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, CURRENT_DATE(), d.change_timestamp);

Row-by-row explanation

Validation queries

-- Confirm the delete actually happened
SELECT * FROM orders_target WHERE order_id = 1032;  -- expect 0 rows

-- Confirm the update applied
SELECT order_status FROM orders_target WHERE order_id = 1050;  -- expect 'SHIPPED'

-- Confirm the insert applied
SELECT * FROM orders_target WHERE order_id = 2001;  -- expect 1 row

-- Row count sanity: net change should be +1 (one insert, one delete, one update = net 0... 
-- but here insert - delete = +1 -1 = 0, update doesn't change count)
SELECT COUNT(*) FROM orders_target;

Recovery strategy

MERGE is naturally idempotent on the join key: re-running the exact same MERGE with the exact same delta produces the same end state — an UPDATE that sets the same values again is harmless, an INSERT that would create a duplicate key instead falls into WHEN MATCHED the second time. This is why MERGE, not sequential INSERT/UPDATE/DELETE statements, is the safe building block for retryable pipelines (Topic 109).

Common interview questions

• Why is MERGE safer to retry than separate INSERT/UPDATE/DELETE statements? Because it's a single atomic statement keyed on the join condition — a rerun re-evaluates matched/not-matched fresh each time instead of blindly re-executing a fixed sequence of writes.
• What happens if the delta has two rows with the same order_id (duplicate)? Snowflake raises an error — MERGE requires the USING source to have at most one matching row per target row per clause; you must deduplicate the source first (Topic 110).
• Can a single MERGE both update and delete in one pass? Yes — multiple WHEN MATCHED clauses with different conditions are evaluated in order, and the first one whose condition is true fires.

Practice questions

• Extend the MERGE above to also handle a hypothetical operation_type 'R' (restore — undelete a soft-deleted row) without breaking the existing three clauses.
• What happens if you swap the order of the WHEN MATCHED clauses (delete before update)? Would the result change for order 1050? Explain why or why not.

What problem it solves

Dimension attributes change — a customer moves, updates their email, or corrects a typo in their name. Slowly Changing Dimension (SCD) Type 1 solves the simplest version of this problem: overwrite the old value with the new one, and don't keep history. It answers "what is the customer's address right now," not "what was it in March."

Why it exists

Most attribute changes are corrections or don't matter historically — nobody needs to know a customer's old, misspelled last name. SCD1 exists because tracking full history for every single attribute change is unnecessary overhead when only the current value matters for reporting.

When to use it

Example: customer address changes

Before — customer 501 lives in Austin:

-- customer_dim BEFORE
-- customer_id | customer_name | city    | updated_at
-- 501         | Maria Chen    | Austin  | 2026-05-01 10:00:00

After — customer 501 moves to Denver on June 3rd:

-- customer_dim AFTER (old value gone, no trace it was ever Austin)
-- customer_id | customer_name | city    | updated_at
-- 501         | Maria Chen    | Denver  | 2026-06-03 14:22:00

DDL

CREATE OR REPLACE TABLE customer_dim (
    customer_id    NUMBER(38,0)   NOT NULL,
    customer_name  VARCHAR(200)   NOT NULL,
    city           VARCHAR(100)   NOT NULL,
    updated_at     TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (customer_id)
);

INSERT INTO customer_dim VALUES
  (501, 'Maria Chen', 'Austin', '2026-05-01 10:00:00');

MERGE logic (overwrite in place)

MERGE INTO customer_dim t
USING (SELECT 501 AS customer_id, 'Maria Chen' AS customer_name, 
              'Denver' AS city, '2026-06-03 14:22:00'::TIMESTAMP_NTZ AS updated_at) s
  ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET
    t.customer_name = s.customer_name,
    t.city           = s.city,
    t.updated_at     = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, customer_name, city, updated_at)
    VALUES (s.customer_id, s.customer_name, s.city, s.updated_at);

-- After this runs, querying customer 501's city returns only 'Denver'.
-- 'Austin' is unrecoverable from this table (only via Time Travel, if within retention).

Validation queries

-- Confirm only current value exists
SELECT city FROM customer_dim WHERE customer_id = 501;  -- 'Denver'

-- Confirm no duplicate rows were created for this customer
SELECT COUNT(*) FROM customer_dim WHERE customer_id = 501;  -- expect 1

Recovery strategy

Because SCD1 destroys history by design, the only recovery path for an accidental bad overwrite is Snowflake Time Travel — there's no application-level undo:

SELECT * FROM customer_dim AT (OFFSET => -3600) 
WHERE customer_id = 501;

Common interview questions

• What's the fundamental tradeoff of SCD1? Simplicity and small table size versus permanently losing the ability to answer "what was true in the past."
• How is SCD1 different from a plain incremental upsert? It isn't, technically — SCD1 is exactly a plain upsert applied to a dimension table; the "SCD1" label just describes the business decision to not preserve history for that attribute.

Practice questions

• A table has 5 dimension attributes; the business wants 2 tracked as SCD1 and 3 tracked as SCD2 (Topic 104). Sketch how you'd structure the table(s) to support both in the same dimension.

What problem it solves

SCD1 answers "what's true now." SCD Type 2 answers the much harder and far more common analytical question: "what was true at any given point in the past." It preserves full history of every attribute change by never overwriting a row — instead, it closes the old row and inserts a new one.

Why it exists

Business reporting frequently needs to attribute historical facts to the dimension values that were true at the time — e.g. "which sales region was this order shipped from, using the region assignment that was active on the order date, not today's reassigned region." Overwriting history (SCD1) makes this kind of point-in-time analysis impossible; SCD2 makes it exact.

How history is preserved

Example: customer moves city

Customer 501 lives in Austin starting May 1. On June 3rd they move to Denver. SCD2 doesn't overwrite the Austin row — it closes it and inserts a new one.

Before the move — one open row:

-- customer_id | city   | effective_start_date | effective_end_date | is_current
-- 501         | Austin | 2026-05-01            | NULL                | TRUE

After the move — old row closed, new row opened:

-- customer_id | city   | effective_start_date | effective_end_date | is_current
-- 501         | Austin | 2026-05-01            | 2026-06-03          | FALSE
-- 501         | Denver | 2026-06-03            | NULL                | TRUE

DDL

CREATE OR REPLACE TABLE customer_dim_scd2 (
    customer_sk           NUMBER(38,0)   AUTOINCREMENT NOT NULL,
    customer_id            NUMBER(38,0)   NOT NULL,
    customer_name          VARCHAR(200)   NOT NULL,
    city                    VARCHAR(100)   NOT NULL,
    effective_start_date   DATE           NOT NULL,
    effective_end_date     DATE,
    is_current              BOOLEAN        NOT NULL DEFAULT TRUE,
    PRIMARY KEY (customer_sk)
);

INSERT INTO customer_dim_scd2 
  (customer_id, customer_name, city, effective_start_date, effective_end_date, is_current)
VALUES
  (501, 'Maria Chen', 'Austin', '2026-05-01', NULL, TRUE);

Full MERGE logic — step by step

SCD2 needs two passes in a single MERGE: close the old row if the tracked attribute changed, and insert a new row for both new customers and changed customers. A single MERGE statement handles the close; the insert is a separate statement because MERGE cannot both update one row and insert an unrelated new row keyed on the same natural key in one pass.

-- Step 1: incoming change staged
CREATE OR REPLACE TEMPORARY TABLE customer_changes AS
SELECT 501 AS customer_id, 'Maria Chen' AS customer_name, 
       'Denver' AS city, '2026-06-03'::DATE AS change_date;

-- Step 2: close the currently-open row IF the tracked attribute actually changed
MERGE INTO customer_dim_scd2 t
USING customer_changes s
  ON t.customer_id = s.customer_id 
  AND t.is_current = TRUE
WHEN MATCHED AND t.city <> s.city THEN UPDATE SET
    t.effective_end_date = s.change_date,
    t.is_current           = FALSE;

-- Step 3: insert the new current version for changed OR brand-new customers
INSERT INTO customer_dim_scd2 
  (customer_id, customer_name, city, effective_start_date, effective_end_date, is_current)
SELECT s.customer_id, s.customer_name, s.city, s.change_date, NULL, TRUE
FROM customer_changes s
LEFT JOIN customer_dim_scd2 t
  ON t.customer_id = s.customer_id AND t.is_current = TRUE AND t.city = s.city
WHERE t.customer_id IS NULL;  -- either brand new, or the row was just closed in Step 2

Point-in-time query — the whole reason SCD2 exists

-- What city was customer 501 in on May 15th?
SELECT city 
FROM customer_dim_scd2
WHERE customer_id = 501
  AND '2026-05-15' BETWEEN effective_start_date AND COALESCE(effective_end_date, '9999-12-31');
-- Returns 'Austin' — correctly, even though today the customer lives in Denver

Validation queries

-- Exactly one current row per natural key — this must never be violated
SELECT customer_id, COUNT(*) 
FROM customer_dim_scd2
WHERE is_current = TRUE
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- No gaps or overlaps in date ranges for a given customer
SELECT customer_id, effective_start_date, effective_end_date
FROM customer_dim_scd2
ORDER BY customer_id, effective_start_date;

Recovery strategy

SCD2's append-only nature makes it self-healing for most mistakes: a bad load just adds a spurious row, which can be identified and deleted without losing any prior history, unlike SCD1 where an overwrite is destructive. Always fix backward by deleting or correcting the erroneous version row and reopening the correct predecessor (set its effective_end_date back to NULL and is_current back to TRUE) rather than patching values in place.

Common interview questions

• Why does a fact table join to the SCD2 surrogate key, not the natural key? The natural key (customer_id) has multiple rows over time; joining on it without a date range would fan out the fact table across every historical version.
• Why use two statements (MERGE then INSERT) instead of one? A single MERGE row can only be updated or inserted, not both — closing an old version and inserting a new version for the same natural key are two separate row-level operations.
• What's the danger of forgetting the is_current filter in the MERGE's ON clause? It would match against any historical version of the customer, potentially reopening or corrupting an already-closed row instead of only ever touching the current one.

Practice questions

• Extend the DDL to track two attributes (city and email) independently — should a change in either trigger a new version? Design the MERGE condition for "any tracked attribute changed."
• Write the point-in-time query to find every customer who lived in Austin at any point during May 2026, even if they've since moved.

What problem it solves

A watermark-based incremental pipeline (Topic 100) assumes data arrives roughly in order. Real systems violate this constantly — a mobile app buffers events offline and uploads them days later, or a batch export job for July 2nd runs late and lands on July 10th. Late-arriving data solves "a record whose business date is old, but which is only now becoming available," without breaking the watermark that's already moved far past that date.

Why it exists

If your incremental load only ever looks forward from the watermark, a record for July 2nd arriving on July 10th (when the watermark already sits at July 9th) would never be picked up by a naive WHERE updated_at > last_watermark filter if that filter is checked against the record's business date instead of its arrival/load timestamp. Getting this distinction right is one of the most common real-world pipeline bugs.

Example

Today is July 10th. A record for order activity on July 2nd arrives — perhaps from a store that was offline and just reconnected.

-- watermark_tracker currently sits at 2026-07-09 23:59:59 (based on ingestion time)

-- Late record arrives on July 10th, but its business date (order_date) is July 2nd
INSERT INTO orders_source VALUES
  (1207, 640, 'DELIVERED', 92.40, '2026-07-02', '2026-07-10 08:00:00');
  -- updated_at = ingestion/arrival time (July 10) — NOT the order_date (July 2)

Reprocessing strategy

Two complementary techniques, used together in production:

MERGE logic — target and any downstream aggregate both get corrected

-- Step 1: the row lands in orders_target via the normal incremental MERGE (Topic 102) —
-- no special logic needed here, since it's just a new order_id
MERGE INTO orders_target t
USING orders_source s
  ON t.order_id = s.order_id
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (s.order_id, s.customer_id, s.order_status, s.order_amount, s.order_date, s.updated_at);

-- Step 2: reprocess the daily summary partition for July 2nd since a late row landed in it
CREATE OR REPLACE TABLE daily_order_summary_tmp AS
SELECT order_date, COUNT(*) AS order_count, SUM(order_amount) AS total_amount
FROM orders_target
WHERE order_date = '2026-07-02'
GROUP BY order_date;

MERGE INTO daily_order_summary d
USING daily_order_summary_tmp s
  ON d.order_date = s.order_date
WHEN MATCHED THEN UPDATE SET
    d.order_count  = s.order_count,
    d.total_amount = s.total_amount
WHEN NOT MATCHED THEN INSERT (order_date, order_count, total_amount)
    VALUES (s.order_date, s.order_count, s.total_amount);

Partition fix — a lookback window in the extract itself

-- Instead of trusting a single watermark point, re-pull a rolling lookback
-- window every run to catch straggling late arrivals automatically:
SELECT *
FROM orders_source
WHERE updated_at > DATEADD(day, -3, (
    SELECT last_watermark FROM watermark_tracker 
    WHERE pipeline_name = 'orders_incremental_load'
));
-- 3-day lookback re-pulls slightly more data every run, but guarantees
-- late arrivals within that window are never missed

Validation queries

-- Find rows whose business date is far older than their ingestion timestamp —
-- flags exactly how "late" your late data typically is
SELECT order_id, order_date, updated_at,
       DATEDIFF(day, order_date, updated_at) AS days_late
FROM orders_target
WHERE DATEDIFF(day, order_date, updated_at) > 1
ORDER BY days_late DESC;

-- Confirm the July 2nd summary now reflects the late row
SELECT * FROM daily_order_summary WHERE order_date = '2026-07-02';

Recovery strategy

Because the lookback window re-MERGEs (not re-inserts blindly), reprocessing is idempotent — rerunning the same lookback twice doesn't double-count anything. If a late row is discovered outside even the lookback window, treat it as a targeted backfill (Topic 106) for that specific partition rather than widening the standing lookback permanently, which would increase every run's cost.

Common interview questions

• Why must a watermark filter on arrival time, not business date? Because late data has an old business date but a new arrival time — filtering on business date would cause the pipeline to reject legitimately-new rows forever.
• What's the cost tradeoff of a lookback window? A wider window catches later-arriving data but reprocesses more rows every single run, even when nothing late actually arrived — it's a tradeoff between completeness and compute cost.

Practice questions

• A source sometimes delivers data up to 14 days late, but a 14-day lookback on every run is too expensive. Design a two-tier strategy: a cheap 1-day lookback on every run, plus a cheap way to detect when a wider reprocess is actually needed.

What problem it solves

Sometimes the pipeline ran correctly, but the source data itself was wrong — a bug in the upstream application miscalculated order_amount for three weeks, or a customer's data needs correcting after a support ticket. Backfills solve "we already loaded this, and it was wrong — how do we safely fix a bounded slice of history without touching everything else or reloading the whole table."

Why it exists

A full reload (Topic 99) fixes everything but is often too slow or risky for a targeted correction. Backfills exist to let you scope a fix precisely — a date range, a partition, or a single customer — and apply it safely, with a clear rollback path if the fix itself turns out to be wrong.

How companies fix targeted slices of history

Approach 1 — DELETE + INSERT for a date range

-- Bug: order_amount was miscalculated for all orders between June 1-15
-- Corrected extract has already been re-pulled from source into orders_source_corrected

BEGIN;

DELETE FROM orders_target
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-15';

INSERT INTO orders_target
SELECT * FROM orders_source_corrected
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-15';

COMMIT;

Approach 2 — MERGE (safer, no delete-then-empty window)

MERGE INTO orders_target t
USING orders_source_corrected s
  ON t.order_id = s.order_id
WHEN MATCHED AND s.order_date BETWEEN '2026-06-01' AND '2026-06-15' THEN UPDATE SET
    t.order_amount = s.order_amount,
    t.updated_at    = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (s.order_id, s.customer_id, s.order_status, s.order_amount, s.order_date, CURRENT_TIMESTAMP());

Approach 3 — CTAS replacement for a specific partition

-- Rebuild only June's partition from clean source data, atomically
CREATE OR REPLACE TABLE orders_target_june_fix AS
SELECT * FROM orders_target WHERE order_date NOT BETWEEN '2026-06-01' AND '2026-06-30'
UNION ALL
SELECT * FROM orders_source_corrected WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30';

ALTER TABLE orders_target SWAP WITH orders_target_june_fix;
DROP TABLE orders_target_june_fix;

Approach 4 — targeted single-customer fix

-- A support ticket reveals customer 501's historical orders were tagged to the
-- wrong region due to a bad join upstream. Fix only that customer's rows.
UPDATE orders_target
SET region = 'US-WEST'
WHERE customer_id = 501
  AND order_date < '2026-06-01';  -- bug was fixed upstream on June 1, so only backfill before that

Validation queries

-- Confirm row count in the fixed range matches the corrected source exactly
SELECT COUNT(*) FROM orders_target 
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-15';

SELECT COUNT(*) FROM orders_source_corrected 
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-15';
-- These two counts must match

-- Confirm no rows outside the fix window were touched (checksum comparison)
SELECT order_date, SUM(order_amount) 
FROM orders_target
WHERE order_date NOT BETWEEN '2026-06-01' AND '2026-06-15'
GROUP BY order_date
ORDER BY order_date;
-- Compare this against a pre-fix snapshot to confirm untouched rows are unchanged

Recovery strategy

Before running any backfill, take an explicit safety snapshot using zero-copy cloning — cheap, instant, and gives you an exact rollback point independent of Time Travel retention windows:

CREATE TABLE orders_target_pre_backfill_snapshot CLONE orders_target;

-- If the backfill turns out wrong:
ALTER TABLE orders_target SWAP WITH orders_target_pre_backfill_snapshot;
DROP TABLE orders_target_pre_backfill_snapshot;

Common interview questions

• Why prefer MERGE over DELETE+INSERT for a backfill? MERGE avoids the empty-window problem — a DELETE followed by a slow INSERT leaves a period where concurrent readers see missing rows in the affected range.
• Why take a zero-copy clone before a backfill instead of relying on Time Travel? A clone is an explicit, named, permanent rollback point you control — Time Travel retention can be as short as 1 day on standard edition and might expire before you realize the backfill was wrong.
• How do you scope a backfill safely? Always filter both the DELETE/UPDATE and the re-INSERT by the exact same bounded condition (date range, customer_id, partition) so the blast radius is provably limited to only the intended rows.

Practice questions

• A backfill needs to correct 6 months of history across 40 million rows, but the business can't tolerate any downtime on orders_target. Design the CTAS-swap approach for this, including how you'd validate before the swap.
• Write a query that would have caught the June 1–15 amount bug automatically, before a human noticed it via a support ticket.

What problem it solves

Physically removing a row loses everything about it instantly and permanently (outside Time Travel). Soft deletes solve "mark this row as no longer active, but keep it queryable for history, audit, and recovery purposes."

Why it exists

Analysts frequently need to answer questions like "how many orders were cancelled last month" — a question that's impossible to answer if cancelled orders are hard-deleted. Soft deletes exist because "deleted" is usually a business status, not a request to erase data.

DDL

CREATE OR REPLACE TABLE orders_target (
    order_id        NUMBER(38,0)   NOT NULL,
    customer_id     NUMBER(38,0)   NOT NULL,
    order_status    VARCHAR(20)    NOT NULL,
    order_amount    NUMBER(12,2)   NOT NULL,
    order_date      DATE           NOT NULL,
    is_deleted      BOOLEAN        NOT NULL DEFAULT FALSE,
    deleted_at      TIMESTAMP_NTZ,
    updated_at      TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (order_id)
);

MERGE logic — soft delete instead of physical delete

MERGE INTO orders_target t
USING orders_cdc d
  ON t.order_id = d.order_id
WHEN MATCHED AND d.operation_type = 'D' THEN UPDATE SET
    t.is_deleted  = TRUE,
    t.deleted_at   = d.change_timestamp,
    t.updated_at   = d.change_timestamp
WHEN MATCHED AND d.operation_type = 'U' THEN UPDATE SET
    t.order_status = d.order_status,
    t.order_amount = d.order_amount,
    t.updated_at    = d.change_timestamp
WHEN NOT MATCHED AND d.operation_type = 'I' THEN INSERT
    (order_id, customer_id, order_status, order_amount, order_date, is_deleted, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, CURRENT_DATE(), FALSE, d.change_timestamp);

Querying around soft deletes

-- Normal reporting view excludes soft-deleted rows by default
CREATE OR REPLACE VIEW orders_active AS
SELECT * FROM orders_target WHERE is_deleted = FALSE;

-- Audit query: how many orders were cancelled this month?
SELECT COUNT(*) FROM orders_target 
WHERE is_deleted = TRUE 
  AND deleted_at >= DATE_TRUNC('month', CURRENT_DATE());

Validation queries

-- Confirm the row still exists but is flagged
SELECT order_id, is_deleted, deleted_at FROM orders_target WHERE order_id = 1032;

-- Confirm the active view correctly excludes it
SELECT * FROM orders_active WHERE order_id = 1032;  -- expect 0 rows

Recovery strategy

Undoing a soft delete is trivial and requires no Time Travel — just flip the flag back:

UPDATE orders_target 
SET is_deleted = FALSE, deleted_at = NULL 
WHERE order_id = 1032;

Common interview questions

• Why do most production systems prefer soft deletes over hard deletes? They preserve auditability and give instant, trivial recovery from accidental or incorrect deletes, at the cost of every query needing an is_deleted = FALSE filter.
• What's the operational cost of soft deletes? Every downstream query must remember to filter them out (usually solved with a view), and the table grows unbounded unless a separate archival/purge process eventually hard-deletes very old soft-deleted rows.

Practice questions

• Design a scheduled Task that hard-deletes rows which have been soft-deleted for more than 2 years, satisfying a data-retention policy while keeping recent deletes recoverable.

What problem it solves

Sometimes data must actually be removed — GDPR/CCPA "right to be forgotten" requests, or genuine test/junk data that should never have existed. Hard deletes physically remove the row so it cannot be queried, reconstructed, or recovered outside a short Time Travel window.

How to track in CDC

The CDC feed (Topic 101) still records the delete event — the difference from a soft delete is only in what the target pipeline does with that event.

INSERT INTO orders_cdc VALUES
  (1032, 540, NULL, NULL, 'D', '2026-06-03 09:30:00');

How to merge a hard delete

MERGE INTO orders_target t
USING orders_cdc d
  ON t.order_id = d.order_id
WHEN MATCHED AND d.operation_type = 'D' THEN
    DELETE
WHEN MATCHED AND d.operation_type = 'U' THEN UPDATE SET
    t.order_status = d.order_status,
    t.order_amount = d.order_amount
WHEN NOT MATCHED AND d.operation_type = 'I' THEN INSERT
    (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, CURRENT_DATE(), d.change_timestamp);

-- Row is gone from orders_target entirely after this executes

Validation queries

-- Confirm the row is physically gone
SELECT * FROM orders_target WHERE order_id = 1032;  -- expect 0 rows

-- For compliance, log that the deletion happened, in a SEPARATE audit table
-- that itself contains no PII — just the fact of deletion
SELECT * FROM deletion_audit_log WHERE order_id = 1032;

Recovery strategy

Only Time Travel can recover a hard-deleted row, and only within the table's retention window:

SELECT * FROM orders_target AT (OFFSET => -3600) WHERE order_id = 1032;

-- Re-insert if genuinely needed and recovery is legitimate
INSERT INTO orders_target
SELECT * FROM orders_target AT (OFFSET => -3600) WHERE order_id = 1032;

Common interview questions

• Why is hard delete harder to make idempotent than an upsert? Deleting a row that's already gone is a no-op (safe), but if the pipeline reprocesses an old batch after a hard delete, a since-deleted row could get accidentally re-inserted by a stale INSERT event — ordering discipline matters more here than for MERGE upserts.
• How does GDPR interact with Time Travel and Fail-safe? A hard delete isn't truly unrecoverable until both the Time Travel retention window and Fail-safe period have elapsed — compliance teams need to account for this when promising "data is deleted."

Practice questions

• Design a GDPR deletion pipeline that hard-deletes a customer's rows from orders_target, logs the deletion to an audit table, and sets that table's Time Travel retention to the minimum (0-1 days) to accelerate true erasure.

What problem it solves

Pipelines fail and get rerun — an orchestrator retry, a manual re-trigger after fixing a bug, or a network blip mid-load. An idempotent pipeline solves "running the same batch twice produces the exact same end state as running it once," so retries are always safe by default rather than something you have to reason about case by case.

Why it exists

Without idempotency, every failure requires careful manual cleanup before rerunning — did the last run partially complete? Which rows already made it in? Idempotency removes that entire category of operational stress: "just rerun it" becomes a universally safe answer.

What breaks idempotency (the anti-patterns)

How to build it: MERGE keyed correctly + batch IDs

CREATE OR REPLACE TABLE batch_audit (
    batch_id           VARCHAR(50)    NOT NULL,
    pipeline_name       VARCHAR(100)   NOT NULL,
    status               VARCHAR(20)    NOT NULL,   -- 'RUNNING','SUCCESS','FAILED'
    started_at           TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (batch_id)
);

-- Step 1: check if this exact batch already succeeded — skip if so (idempotent guard)
SET already_done = (
    SELECT COUNT(*) FROM batch_audit 
    WHERE batch_id = 'orders_20260603_0800' AND status = 'SUCCESS'
);

-- Step 2: only proceed if not already successfully processed
-- (orchestrator branches on $already_done = 0 before running the MERGE below)

INSERT INTO batch_audit VALUES ('orders_20260603_0800', 'orders_incremental_load', 'RUNNING', CURRENT_TIMESTAMP());

MERGE INTO orders_target t
USING orders_delta d
  ON t.order_id = d.order_id
WHEN MATCHED THEN UPDATE SET t.order_status = d.order_status, t.order_amount = d.order_amount
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, d.order_date, d.updated_at);
-- Safe to rerun: matched rows just get the same values reapplied, no duplicates possible

UPDATE batch_audit SET status = 'SUCCESS' WHERE batch_id = 'orders_20260603_0800';

Validation queries

-- Confirm no duplicate order_ids exist after a rerun
SELECT order_id, COUNT(*) FROM orders_target GROUP BY order_id HAVING COUNT(*) > 1;

-- Confirm the batch was only ever marked SUCCESS once
SELECT batch_id, COUNT(*) FROM batch_audit WHERE status = 'SUCCESS' GROUP BY batch_id HAVING COUNT(*) > 1;

Recovery strategy

Because the MERGE is keyed on the natural key and the batch_audit guard prevents reprocessing an already-successful batch, recovery from any failure is simply: fix the underlying issue, rerun the same orchestrator job. No manual row-counting or cleanup step is ever required — that's the entire point of designing for idempotency up front.

Common interview questions

• What makes MERGE naturally more idempotent than INSERT? MERGE re-evaluates matched/not-matched on every run against the current target state, so reapplying the same delta twice converges to the same result instead of accumulating duplicates.
• Why check batch status before reprocessing instead of relying purely on MERGE? Purely relying on MERGE is usually sufficient for the target table itself, but a batch guard also protects non-idempotent side effects the pipeline might trigger elsewhere (sending a notification, calling an external API) that MERGE's safety doesn't cover.

Practice questions

• A pipeline step sends a Slack notification after loading each batch. Notifications aren't naturally idempotent like MERGE. Redesign the pipeline so a rerun after a partial failure doesn't send a duplicate notification.

What problem it solves

A delta batch or CDC feed can contain multiple events for the same key in one load window (e.g. an order was updated three times in the last hour, all three landed in the same CDC batch). Feeding that straight into a MERGE fails outright — MERGE requires at most one matching source row per target row. Deduplication solves "collapse multiple events for the same key down to the one that actually matters" before the MERGE runs.

Why it exists

Batch windows are a real-world compromise between latency and efficiency — you don't MERGE after every single row change, you batch a window of changes together. That batching is exactly what creates duplicate keys within a single load, so dedup is a required step, not an optional cleanup.

Example duplicate orders

-- Same order_id appears 3 times in one CDC batch — status changed twice within the hour
-- order_id | order_status | operation_type | change_timestamp
-- 1050     | PLACED       | U               | 2026-06-03 09:00:00
-- 1050     | PACKED        | U               | 2026-06-03 09:20:00
-- 1050     | SHIPPED       | U               | 2026-06-03 09:45:00

Feeding all three rows into a MERGE keyed on order_id raises an error — Snowflake can't determine which of the three should apply. You must keep only the latest (SHIPPED) before merging.

ROW_NUMBER() — keep the latest row per key

CREATE OR REPLACE TEMPORARY TABLE orders_cdc_deduped AS
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY order_id 
               ORDER BY change_timestamp DESC
           ) AS rn
    FROM orders_cdc
    WHERE change_timestamp > '2026-06-03 08:00:00'
)
WHERE rn = 1;
-- Result: only the SHIPPED row (09:45:00) survives for order_id 1050

QUALIFY — same result, cleaner syntax

SELECT *
FROM orders_cdc
WHERE change_timestamp > '2026-06-03 08:00:00'
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY order_id 
    ORDER BY change_timestamp DESC
) = 1;
-- QUALIFY filters on the window function directly — no subquery needed

Now the MERGE succeeds

MERGE INTO orders_target t
USING orders_cdc_deduped d
  ON t.order_id = d.order_id
WHEN MATCHED THEN UPDATE SET
    t.order_status = d.order_status,
    t.updated_at    = d.change_timestamp
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, order_amount, order_date, updated_at)
    VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, CURRENT_DATE(), d.change_timestamp);
-- Only one row per order_id in the USING clause now — MERGE succeeds cleanly

Validation queries

-- Confirm dedup actually collapsed the duplicates
SELECT order_id, COUNT(*) 
FROM orders_cdc_deduped 
GROUP BY order_id 
HAVING COUNT(*) > 1;
-- Should return 0 rows — this is exactly the check MERGE itself would fail on otherwise

-- Confirm the surviving row is genuinely the latest
SELECT order_id, order_status, change_timestamp FROM orders_cdc_deduped WHERE order_id = 1050;
-- Should show 'SHIPPED' at 09:45:00, not an earlier status

Recovery strategy

If a MERGE fails with a "multiple matches" error in production, the fix is always to add or fix a dedup step immediately before the MERGE — never to loosen the MERGE's join condition, which risks silently applying the wrong version of a row instead of failing loudly.

Common interview questions

• Why does MERGE fail on duplicate keys in the USING clause instead of just picking one? Silently picking an arbitrary row would be a correctness bug that's invisible until someone notices wrong data — Snowflake fails loudly instead, forcing an explicit dedup decision.
• What's the difference between ROW_NUMBER() with a subquery and QUALIFY? They produce identical results; QUALIFY is purely a syntax convenience that filters directly on a window function without wrapping the query in a subquery.
• What if two duplicate rows have the exact same change_timestamp? ROW_NUMBER()'s ordering becomes non-deterministic on ties — add a tiebreaker column (e.g. a monotonically increasing ingestion sequence ID) to the ORDER BY to make the choice deterministic.

Practice questions

• Two CDC events for the same order_id have identical change_timestamp values. Modify the dedup query to break the tie using a secondary ingestion_seq column.
• Write a dedup query that keeps the earliest row per key instead of the latest — describe one real scenario where that would be the correct choice.

What problem it solves

Topic 100 introduced the watermark as a single concept. This topic goes deeper into designing the watermark storage itself correctly — the difference between a high and low watermark, and how to make watermark state survive a mid-pipeline crash without losing or double-processing data.

High watermark vs low watermark

DDL — a production-grade watermark table

CREATE OR REPLACE TABLE watermark_tracker (
    pipeline_name        VARCHAR(100)   NOT NULL,
    low_watermark          TIMESTAMP_NTZ  NOT NULL,
    high_watermark          TIMESTAMP_NTZ  NOT NULL,
    last_run_status          VARCHAR(20)    NOT NULL,   -- 'SUCCESS','FAILED','RUNNING'
    last_run_batch_id        VARCHAR(50),
    updated_at                TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (pipeline_name)
);

INSERT INTO watermark_tracker VALUES
  ('orders_incremental_load', '2026-06-01 00:00:00', '2026-06-01 00:00:00', 'SUCCESS', NULL, CURRENT_TIMESTAMP());

Recovery after failure — the state machine

1. Before extracting, set last_run_status = 'RUNNING' and record the batch_id — this makes an in-flight run visible to anyone investigating a stuck pipeline.
2. Extract rows between low_watermark (with safety overlap) and the current time.
3. MERGE the delta into the target.
4. Only after the MERGE commits successfully: advance high_watermark to the max timestamp in the batch, set low_watermark = high_watermark, and set last_run_status = 'SUCCESS'.
5. If the pipeline crashes anywhere before step 4, last_run_status stays 'RUNNING' or gets set to 'FAILED' by the orchestrator's error handler — the watermark itself never advances, so the next run safely reprocesses the exact same window.

-- Step 1: mark running
UPDATE watermark_tracker 
SET last_run_status = 'RUNNING', last_run_batch_id = 'orders_20260604_0600'
WHERE pipeline_name = 'orders_incremental_load';

-- Step 2-3: extract + MERGE happen here (omitted, same as Topic 100/102)

-- Step 4: only on success
UPDATE watermark_tracker
SET high_watermark = (SELECT MAX(updated_at) FROM orders_delta),
    low_watermark   = (SELECT MAX(updated_at) FROM orders_delta),
    last_run_status  = 'SUCCESS',
    updated_at        = CURRENT_TIMESTAMP()
WHERE pipeline_name = 'orders_incremental_load';

Validation queries

-- Alert on any pipeline stuck in RUNNING for too long (likely crashed silently)
SELECT pipeline_name, last_run_batch_id, updated_at
FROM watermark_tracker
WHERE last_run_status = 'RUNNING'
  AND updated_at < DATEADD(hour, -2, CURRENT_TIMESTAMP());

-- Confirm the watermark actually advanced after a successful run
SELECT pipeline_name, high_watermark, last_run_status FROM watermark_tracker;

Recovery strategy

If a run is found stuck in 'RUNNING', the correct recovery is to confirm no partial MERGE was left uncommitted (Snowflake transactions are all-or-nothing, so this is usually already safe), then simply reset status to 'FAILED' and let the orchestrator retry — the watermark not having advanced guarantees the retry reprocesses exactly the right window, no more and no less.

Common interview questions

• Why track last_run_status in the watermark table itself? It turns "is this pipeline stuck" into a simple query instead of requiring log spelunking, and it prevents two concurrent runs of the same pipeline from racing each other.
• Why is low_watermark a separate column from high_watermark? It lets you deliberately reprocess a small overlap window (for late data tolerance) without losing track of the true high-water point the pipeline has confirmed as fully processed.

Practice questions

• Design a check that prevents two orchestrator triggers of the same pipeline from running concurrently, using only the watermark_tracker table (no external locking service).

What problem it solves

When something goes wrong in production at 3am, the first question is always "which batch, when did it start, how far did it get, and how many rows failed." A batch audit table solves this by giving every pipeline run a permanent, queryable record — independent of whatever logging system the orchestrator uses, which may rotate or expire.

Why it exists

Orchestrator logs (Airflow, dbt Cloud, etc.) are often ephemeral, hard to query with SQL, or scattered across systems. A batch audit table lives in Snowflake itself, queryable with the same SQL as everything else, and becomes the single source of truth for "did today's load actually succeed, and how much data moved."

DDL

CREATE OR REPLACE TABLE batch_audit (
    batch_id             VARCHAR(50)    NOT NULL,
    pipeline_name          VARCHAR(100)   NOT NULL,
    start_time              TIMESTAMP_NTZ  NOT NULL,
    end_time                TIMESTAMP_NTZ,
    status                   VARCHAR(20)    NOT NULL,   -- 'RUNNING','SUCCESS','FAILED'
    records_processed        NUMBER(38,0)   DEFAULT 0,
    records_inserted          NUMBER(38,0)   DEFAULT 0,
    records_updated           NUMBER(38,0)   DEFAULT 0,
    records_deleted           NUMBER(38,0)   DEFAULT 0,
    records_failed             NUMBER(38,0)   DEFAULT 0,
    error_message               VARCHAR(2000),
    PRIMARY KEY (batch_id)
);

Insert examples — a full run lifecycle

-- Batch starts
INSERT INTO batch_audit (batch_id, pipeline_name, start_time, status)
VALUES ('orders_20260604_0600', 'orders_incremental_load', CURRENT_TIMESTAMP(), 'RUNNING');

-- ... MERGE runs here, and Snowflake reports rows affected via RESULT_SCAN or the MERGE result ...

-- Batch finishes successfully, with counts populated
UPDATE batch_audit
SET end_time            = CURRENT_TIMESTAMP(),
    status               = 'SUCCESS',
    records_processed    = 5,
    records_inserted      = 1,
    records_updated        = 3,
    records_deleted         = 1
WHERE batch_id = 'orders_20260604_0600';

-- A failed run instead records the error
UPDATE batch_audit
SET end_time      = CURRENT_TIMESTAMP(),
    status         = 'FAILED',
    error_message   = 'MERGE failed: duplicate key in USING clause for order_id 1050'
WHERE batch_id = 'orders_20260604_0601';

Capturing MERGE row counts automatically

MERGE INTO orders_target t
USING orders_cdc_deduped d ON t.order_id = d.order_id
WHEN MATCHED AND d.operation_type = 'D' THEN DELETE
WHEN MATCHED AND d.operation_type = 'U' THEN UPDATE SET t.order_status = d.order_status
WHEN NOT MATCHED AND d.operation_type = 'I' THEN INSERT (order_id, order_status) VALUES (d.order_id, d.order_status);

-- Snowflake exposes rows affected via the query result message —
-- orchestrators typically parse "number of rows inserted" / "number of rows updated"
-- from this and write it straight into batch_audit

Validation queries

-- Daily health check: any failed batches in the last 24 hours?
SELECT * FROM batch_audit 
WHERE status = 'FAILED' 
  AND start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP());

-- Trend: is records_processed dropping unexpectedly (silent upstream data loss)?
SELECT DATE(start_time) AS run_date, records_processed
FROM batch_audit
WHERE pipeline_name = 'orders_incremental_load'
ORDER BY run_date DESC
LIMIT 30;

-- Runs that started but never finished (orchestrator crash, not a clean FAILED)
SELECT * FROM batch_audit WHERE status = 'RUNNING' AND end_time IS NULL
  AND start_time < DATEADD(hour, -1, CURRENT_TIMESTAMP());

Recovery strategy

The batch_audit table is the first place to look during an incident — it tells you exactly which batch_id to investigate, whether it's safe to rerun (idempotent MERGE, Topic 109, means yes), and gives you the error_message without needing orchestrator log access at all.

Common interview questions

• Why keep audit data in Snowflake instead of just relying on orchestrator logs? It's queryable with SQL alongside the data itself, survives independently of log retention policies, and lets you build dashboards/alerts directly on top of it.
• What's the value of tracking records_inserted/updated/deleted separately, not just a total? A sudden spike in records_deleted with no corresponding business reason is often the first sign of a CDC bug misclassifying updates as deletes.

Practice questions

• Design an alerting query that flags a pipeline whose records_processed today is more than 3x its 7-day rolling average — a signal of a possible duplicate-source or replay bug.

The full architecture

Everything in this module comes together in one real production shape: Oracle → CDC connector → S3 → Snowflake external stage → RAW table → Stream → Task → MERGE → final table. Every table below plays one distinct role — understanding why each layer exists is more important than memorizing the SQL.

Every table, every DDL

-- 1. External stage pointing at the CDC connector's S3 output
CREATE OR REPLACE STAGE orders_cdc_stage
  URL = 's3://company-cdc-landing/oracle/orders/'
  CREDENTIALS = (AWS_ROLE = 'arn:aws:iam::123456789012:role/snowflake-cdc-reader')
  FILE_FORMAT = (TYPE = JSON);

-- 2. Raw landing table — append-only, one row per CDC event, ever
CREATE OR REPLACE TABLE orders_cdc_raw (
    order_id          NUMBER(38,0),
    customer_id        NUMBER(38,0),
    order_status        VARCHAR(20),
    order_amount         NUMBER(12,2),
    order_date            DATE,
    operation_type          VARCHAR(1),
    change_timestamp        TIMESTAMP_NTZ,
    file_name                  VARCHAR(500),
    loaded_at                    TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

-- 3. Pipe for continuous ingestion from the stage (Snowpipe)
CREATE OR REPLACE PIPE orders_cdc_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO orders_cdc_raw (order_id, customer_id, order_status, order_amount, order_date, 
                              operation_type, change_timestamp, file_name)
  FROM (
    SELECT $1:order_id, $1:customer_id, $1:order_status, $1:order_amount, $1:order_date,
           $1:operation_type, $1:change_timestamp, METADATA$FILENAME
    FROM @orders_cdc_stage
  )
  FILE_FORMAT = (TYPE = JSON);

-- 4. Stream on the raw table — tracks unconsumed CDC rows automatically
CREATE OR REPLACE STREAM orders_cdc_stream ON TABLE orders_cdc_raw;

-- 5. Final target table
CREATE OR REPLACE TABLE orders_target (
    order_id        NUMBER(38,0)   NOT NULL,
    customer_id     NUMBER(38,0)   NOT NULL,
    order_status    VARCHAR(20)    NOT NULL,
    order_amount    NUMBER(12,2)   NOT NULL,
    order_date      DATE           NOT NULL,
    updated_at      TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (order_id)
);

-- 6. Batch audit table (Topic 112) — reused here for this pipeline's runs
CREATE OR REPLACE TABLE batch_audit (
    batch_id VARCHAR(50), pipeline_name VARCHAR(100), start_time TIMESTAMP_NTZ,
    end_time TIMESTAMP_NTZ, status VARCHAR(20), records_processed NUMBER(38,0)
);

The Task — scheduled MERGE consuming the Stream

CREATE OR REPLACE TASK merge_orders_task
  WAREHOUSE = etl_wh
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_cdc_stream')
AS
BEGIN
  -- Dedup the Stream's new rows before merging (Topic 110)
  CREATE OR REPLACE TEMPORARY TABLE orders_delta_deduped AS
  SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY change_timestamp DESC) AS rn
      FROM orders_cdc_stream
  ) WHERE rn = 1;

  MERGE INTO orders_target t
  USING orders_delta_deduped d
    ON t.order_id = d.order_id
  WHEN MATCHED AND d.operation_type = 'D' THEN
      DELETE
  WHEN MATCHED AND d.operation_type = 'U' THEN UPDATE SET
      t.order_status = d.order_status,
      t.order_amount = d.order_amount,
      t.updated_at    = d.change_timestamp
  WHEN NOT MATCHED AND d.operation_type = 'I' THEN INSERT
      (order_id, customer_id, order_status, order_amount, order_date, updated_at)
      VALUES (d.order_id, d.customer_id, d.order_status, d.order_amount, d.order_date, d.change_timestamp);
  -- Consuming from the Stream in this transaction automatically advances its offset
  -- on commit — no manual watermark bookkeeping needed for this layer
END;

ALTER TASK merge_orders_task RESUME;

Why the Stream replaces manual watermark tracking here

A Stream is Snowflake's native, exactly-once-per-consumer CDC pointer — reading from it inside a DML transaction and committing that transaction atomically advances the Stream's offset. This eliminates the entire "update the watermark table last" discipline from Topic 100/111 for this specific layer, because Snowflake guarantees the offset only advances if the consuming transaction actually commits.

Validation queries

-- Confirm the pipe is actively ingesting
SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_USAGE_HISTORY(
    DATE_RANGE_START => DATEADD('hour',-1,CURRENT_TIMESTAMP()),
    PIPE_NAME => 'ORDERS_CDC_PIPE'));

-- Confirm the Stream has unconsumed data before the Task fires
SELECT SYSTEM$STREAM_HAS_DATA('orders_cdc_stream');

-- Confirm the Task is actually running on schedule
SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'MERGE_ORDERS_TASK')) 
ORDER BY scheduled_time DESC LIMIT 10;

-- End-to-end reconciliation: raw event count vs. final table row-change count
SELECT COUNT(*) FROM orders_cdc_raw WHERE loaded_at > DATEADD(hour,-24,CURRENT_TIMESTAMP());

Recovery strategy

Every layer has an independent recovery path: if Snowpipe misses files, COPY INTO can be run manually against the stage to backfill; if the Task fails mid-MERGE, Snowflake's transactional guarantee means the Stream offset simply doesn't advance and the next scheduled run reprocesses the same rows safely (idempotent MERGE, Topic 109); if the final table itself needs correction, it's a standard backfill (Topic 106) using orders_cdc_raw as the append-only source of truth to rebuild from.

Common interview questions

• Why land raw CDC events in an append-only RAW table instead of merging directly from the stage? The RAW table is a permanent, replayable source of truth — if a bug is found in the MERGE logic weeks later, you can rebuild orders_target from scratch by replaying RAW, which wouldn't be possible if events were merged and discarded immediately.
• What does the Stream actually track internally? A Stream is metadata-only — it tracks which table versions (via Snowflake's Time Travel/change-tracking metadata) have already been consumed, not a physical copy of the data.
• Why 5-minute Task schedule instead of triggering immediately on new data? Batching a small window amortizes warehouse spin-up cost across many events rather than paying for a warehouse resume on every single row — a direct cost/latency tradeoff (Module 12, Topic 87 framework).

Practice questions

• Redraw this architecture replacing the Stream+Task layer with a Dynamic Table instead. What do you gain, and what do you lose, per the decision framework from Module 12 Topic 88?
• The CDC connector occasionally delivers duplicate files (same S3 key uploaded twice). Where in this pipeline would you add protection against double-processing the same file, and how?

How to use this bank

Each problem below is self-contained: minimal DDL, a small seed dataset, a scenario, the expected outcome, and your task. Try writing the SQL yourself before checking against the patterns taught earlier in this module — the topic reference next to each problem tells you exactly which technique it's testing.

1. What is it

Duplicate handling means making sure the same order row doesn't end up twice in orders_target after an incremental load. In plain words: your incremental job reads "everything changed since the last watermark," and if that job runs more than once over the same window, or the same row shows up twice in the source, you get two copies of one order sitting in your target table.

2. Why it happens

3. Real-world example

An orchestrator retries a failed 9am load of orders_raw because the warehouse resumed slowly and the step timed out. The timeout happened after the INSERT into orders_stage committed but before the job marked itself complete. The retry reprocesses the same file. Now order 5001 exists twice in orders_stage, and if you blindly INSERT into orders_target, it exists twice there too.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    order_id       NUMBER(38,0)   NOT NULL,
    customer_id    NUMBER(38,0)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    updated_at     TIMESTAMP_NTZ  NOT NULL,
    batch_id       VARCHAR(40)    NOT NULL,
    load_ts        TIMESTAMP_NTZ  DEFAULT CURRENT_TIMESTAMP()
);

CREATE OR REPLACE TABLE orders_target (
    order_id       NUMBER(38,0)   NOT NULL,
    customer_id    NUMBER(38,0)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    updated_at     TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (order_id)
);

5. Insert data (simulating the rerun)

-- First run of the 9am job, batch_id = 'b_0900_v1'
INSERT INTO orders_raw VALUES
  (5001, 701, 'SHIPPED', 88.00, '2026-06-15 09:01:00', 'b_0900_v1', CURRENT_TIMESTAMP()),
  (5002, 702, 'PLACED',  42.50, '2026-06-15 09:02:00', 'b_0900_v1', CURRENT_TIMESTAMP());

-- Timeout happens after commit. Orchestrator retries with a NEW batch_id but the SAME rows.
INSERT INTO orders_raw VALUES
  (5001, 701, 'SHIPPED', 88.00, '2026-06-15 09:01:00', 'b_0900_v2', CURRENT_TIMESTAMP()),
  (5002, 702, 'PLACED',  42.50, '2026-06-15 09:02:00', 'b_0900_v2', CURRENT_TIMESTAMP());

6. Broken scenario

-- BAD: naive incremental load, no dedup
INSERT INTO orders_target
SELECT order_id, customer_id, order_status, order_amount, updated_at
FROM orders_raw;

-- orders_target now has order_id 5001 and 5002 TWICE each.
SELECT order_id, COUNT(*) FROM orders_target GROUP BY order_id HAVING COUNT(*) > 1;
-- returns 5001 -> 2, 5002 -> 2

7. The fix

Keep only the latest row per order_id before it ever reaches the target, using QUALIFY with ROW_NUMBER(), then MERGE instead of blind INSERT so reruns are safe even if they slip past the dedup.

-- Step 1: dedupe the raw batch down to one row per order_id, keep the latest
CREATE OR REPLACE TEMPORARY TABLE orders_stage AS
SELECT order_id, customer_id, order_status, order_amount, updated_at
FROM orders_raw
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, load_ts DESC) = 1;

-- Step 2: MERGE the deduped stage into target — safe even on a full rerun
MERGE INTO orders_target t
USING orders_stage s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET t.order_status = s.order_status, t.order_amount = s.order_amount, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, order_status, order_amount, updated_at)
  VALUES (s.order_id, s.customer_id, s.order_status, s.order_amount, s.updated_at);

8. Prevention

• Give every load a unique batch_id and record it in orders_audit before writing any target rows, so a retry can check "did batch X already complete?" before reprocessing.
• Never use plain INSERT against a target table that's fed incrementally — always MERGE on the natural key.
• Compute a hash key (HASH(order_id, customer_id, order_status, order_amount)) and skip writing rows whose hash hasn't changed, to keep MERGE cheap and idempotent.

9. Recovery

-- If duplicates already made it into orders_target, clean up with the same ROW_NUMBER pattern
CREATE OR REPLACE TABLE orders_target AS
SELECT order_id, customer_id, order_status, order_amount, updated_at
FROM orders_target
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

10. Interview questions

• Why does QUALIFY alone not fully solve duplicate loads? QUALIFY only dedupes within the current batch/query — it does nothing to protect you if two separate pipeline runs each insert one copy. That's what MERGE-on-key is for.
• What's the difference between a batch_id and a hash key for dedup? batch_id tells you which load produced a row (useful for audit/rollback); a hash key tells you whether a row's content actually changed (useful for skipping no-op updates).

11. Practice questions

• Rewrite the fix so that ties in updated_at (two rows with the exact same timestamp) are broken deterministically using a secondary column.
• Write a query against orders_audit that would let a retrying job check "has batch_id b_0900_v1 already fully loaded?" before doing any work.

1. What is it

CDC (Change Data Capture) streams one event per row change from the source database. Duplicate handling in CDC means making sure that if the exact same change event is delivered more than once, you don't apply it twice.

2. Why it happens

3. Real-world example

Order 6001 is marked SHIPPED. The CDC connector emits that UPDATE event, but a broker rebalance happens right after, before the consumer's offset commits. On rebalance, the same partition is reprocessed from the last committed offset — the SHIPPED event fires a second time, one minute apart, with an identical payload but a new Kafka offset.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    event_id       VARCHAR(60)    NOT NULL,   -- unique ID assigned by the source CDC log
    order_id       NUMBER(38,0)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    sequence_id    NUMBER(38,0)   NOT NULL,   -- monotonically increasing per order_id (LSN/SCN equivalent)
    event_ts       TIMESTAMP_NTZ  NOT NULL
);

CREATE OR REPLACE TABLE orders_target (
    order_id       NUMBER(38,0)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    last_sequence_id NUMBER(38,0) NOT NULL,
    PRIMARY KEY (order_id)
);

5. Insert data (simulating the replay)

-- Original event
INSERT INTO orders_raw VALUES
  ('evt-9001', 6001, 'SHIPPED', 88.00, 501, '2026-06-15 10:00:00');

-- Rebalance causes the SAME logical change to replay with a new event_id but the SAME sequence_id
INSERT INTO orders_raw VALUES
  ('evt-9002', 6001, 'SHIPPED', 88.00, 501, '2026-06-15 10:00:05');

6. Broken scenario

-- BAD: dedup on event_id, which is different for the replayed event — duplicate slips through
MERGE INTO orders_target t
USING (SELECT DISTINCT order_id, order_status, order_amount, sequence_id FROM orders_raw) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.order_status = s.order_status, t.last_sequence_id = s.sequence_id
WHEN NOT MATCHED THEN INSERT (order_id, order_status, order_amount, last_sequence_id)
  VALUES (s.order_id, s.order_status, s.order_amount, s.sequence_id);
-- Applies the same logical update twice — harmless here since values match,
-- but if this had been a quantity DECREMENT event, applying it twice would double-decrement.

7. The fix

Never dedupe CDC on event_id — dedupe on the source's ordering key (sequence_id, which stands in for a database LSN/SCN) and only apply an event if its sequence_id is strictly greater than what's already applied.

MERGE INTO orders_target t
USING (
  SELECT order_id, order_status, order_amount, sequence_id
  FROM orders_raw
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence_id DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.sequence_id > t.last_sequence_id THEN
  UPDATE SET t.order_status = s.order_status, t.order_amount = s.order_amount, t.last_sequence_id = s.sequence_id
WHEN NOT MATCHED THEN
  INSERT (order_id, order_status, order_amount, last_sequence_id)
  VALUES (s.order_id, s.order_status, s.order_amount, s.sequence_id);

8. Prevention

• Require every CDC source to emit a monotonic sequence_id (or expose native LSN/SCN) — refuse to onboard a CDC feed that can't guarantee this.
• Make the consumer's MERGE condition always sequence_id > last_sequence_id, never a blind overwrite, so replays are automatically no-ops.
• Commit Kafka consumer offsets only after the MERGE commits successfully, not before, so a crash between read and write causes reprocessing rather than data loss.

9. Recovery

-- Detect orders where target sequence doesn't match the true max sequence in raw (missed or double-applied)
SELECT r.order_id, MAX(r.sequence_id) AS true_max_seq, t.last_sequence_id
FROM orders_raw r
JOIN orders_target t ON t.order_id = r.order_id
GROUP BY r.order_id, t.last_sequence_id
HAVING MAX(r.sequence_id) <> t.last_sequence_id;

-- Rebuild the affected rows straight from raw, keyed on the true max sequence_id
MERGE INTO orders_target t
USING (
  SELECT order_id, order_status, order_amount, sequence_id
  FROM orders_raw
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence_id DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.order_status = s.order_status, t.order_amount = s.order_amount, t.last_sequence_id = s.sequence_id;

10. Interview questions

• Why can't you dedupe CDC events by event_id alone? event_id identifies a delivery attempt, not a logical change — a replayed event gets a new event_id but represents the same underlying change, so event_id-based dedup lets replays through.
• What plays the role of LSN/SCN when the source doesn't natively expose one? A connector-assigned monotonic sequence per key, or a composite of (source commit timestamp, source transaction id) if nothing else is available.

11. Practice questions

• Extend the MERGE to also handle DELETE events, where the CDC payload includes an op = 'D' flag.
• Write a query that finds any order_id where two different sequence_ids arrived with the exact same event_ts, which would indicate a source clock or sequencing bug.

1. What is it

A watermark is a saved checkpoint (usually a timestamp or ID) that tells the next incremental run "everything before this point is already loaded." Watermark failure recovery is what you do when the watermark and the actual loaded data disagree — because the job crashed at exactly the wrong moment.

2. Why it happens

There are two dangerous orderings, and both cause real damage:

3. Real-world example

A job advances watermark_tracker to 11:00 immediately after reading the source query, to "reserve" the window before the long load starts. The warehouse suspends mid-load due to a resource limit. The load never finishes, but the watermark already says 11:00 is done. Tomorrow's run starts from 11:00 and orders between 10:45–11:00 are gone forever unless someone notices.

4. DDL

CREATE OR REPLACE TABLE watermark_tracker (
    pipeline_name   VARCHAR(60)    NOT NULL,
    high_watermark  TIMESTAMP_NTZ  NOT NULL,
    status          VARCHAR(20)    NOT NULL,  -- 'IN_PROGRESS' or 'COMMITTED'
    updated_at      TIMESTAMP_NTZ  NOT NULL,
    PRIMARY KEY (pipeline_name)
);

CREATE OR REPLACE TABLE orders_audit (
    batch_id      VARCHAR(40)     NOT NULL,
    pipeline_name VARCHAR(60)     NOT NULL,
    window_start  TIMESTAMP_NTZ   NOT NULL,
    window_end    TIMESTAMP_NTZ   NOT NULL,
    row_count     NUMBER(38,0),
    status        VARCHAR(20)     NOT NULL,  -- 'STARTED', 'VALIDATED', 'COMMITTED', 'FAILED'
    started_at    TIMESTAMP_NTZ   NOT NULL,
    ended_at      TIMESTAMP_NTZ
);

5. Insert data

INSERT INTO watermark_tracker VALUES ('orders_incremental', '2026-06-15 10:45:00', 'COMMITTED', '2026-06-15 10:46:00');

6. Broken scenario (wrong order)

-- BAD: watermark advanced BEFORE the load runs
UPDATE watermark_tracker SET high_watermark = '2026-06-15 11:00:00', status = 'COMMITTED'
WHERE pipeline_name = 'orders_incremental';

-- ... warehouse suspends here, load never runs ...
INSERT INTO orders_target
SELECT * FROM orders_raw WHERE updated_at > '2026-06-15 10:45:00' AND updated_at <= '2026-06-15 11:00:00';
-- never executes. Tomorrow's job reads high_watermark = 11:00 and skips this window forever.

7. The fix

Enforce the correct order: load → validate → commit → watermark update. The watermark only moves after the data is proven to be in the target.

-- Step 1: record the attempt as IN_PROGRESS, do NOT touch high_watermark yet
INSERT INTO orders_audit VALUES ('b_1100', 'orders_incremental', '2026-06-15 10:45:00', '2026-06-15 11:00:00', NULL, 'STARTED', CURRENT_TIMESTAMP(), NULL);

-- Step 2: load into target
INSERT INTO orders_target
SELECT order_id, customer_id, order_status, order_amount, updated_at
FROM orders_raw
WHERE updated_at > '2026-06-15 10:45:00' AND updated_at <= '2026-06-15 11:00:00';

-- Step 3: validate row count matches expectation
UPDATE orders_audit SET row_count = (SELECT COUNT(*) FROM orders_raw WHERE updated_at > '2026-06-15 10:45:00' AND updated_at <= '2026-06-15 11:00:00'),
  status = 'VALIDATED', ended_at = CURRENT_TIMESTAMP()
WHERE batch_id = 'b_1100';

-- Step 4: ONLY NOW advance the watermark, and mark the audit row COMMITTED
UPDATE watermark_tracker SET high_watermark = '2026-06-15 11:00:00', status = 'COMMITTED', updated_at = CURRENT_TIMESTAMP()
WHERE pipeline_name = 'orders_incremental';

UPDATE orders_audit SET status = 'COMMITTED' WHERE batch_id = 'b_1100';

8. Prevention

• Never write the watermark update in the same statement or transaction step that "reserves" a window before work starts.
• Always pair a watermark row with an orders_audit row in STARTED status, so a crash leaves visible evidence instead of just a stale watermark.
• Add a scheduled reconciliation job that alerts if any orders_audit row sits in STARTED or VALIDATED for longer than the expected job duration.

9. Recovery

-- Find batches that started but never committed — these windows may have been silently skipped
SELECT * FROM orders_audit WHERE status IN ('STARTED', 'VALIDATED') AND started_at < DATEADD(hour, -2, CURRENT_TIMESTAMP());

-- Re-run the load for that exact window manually, then re-run Steps 3-4 above
INSERT INTO orders_target
SELECT order_id, customer_id, order_status, order_amount, updated_at
FROM orders_raw
WHERE updated_at > (SELECT window_start FROM orders_audit WHERE batch_id = 'b_1100')
  AND updated_at <= (SELECT window_end FROM orders_audit WHERE batch_id = 'b_1100');

10. Interview questions

• Where exactly should the watermark update happen in the pipeline? After the load is validated and durably committed to the target — it is the last step, never the first.
• How do you detect a "silent skip" caused by a premature watermark update? Reconcile source row counts for the watermark's window against what actually landed in the target; a stuck orders_audit row in STARTED status is the earliest signal.

11. Practice questions

• Design the DDL and logic so that watermark_tracker can never be updated outside of a stored procedure that enforces the load → validate → commit order.
• Write the reconciliation query that runs nightly and flags any pipeline whose watermark hasn't advanced in over 24 hours.

1. What is it

Out-of-order data means events don't arrive in the same order they actually happened. Event at 10:02 might physically arrive in Snowflake after the event at 10:08. If your pipeline naively trusts "last row wins by arrival," you'll overwrite a newer state with an older one.

2. Why it happens

Network latency varies per request, retries reorder delivery, multiple producers write concurrently with independent clocks, and message queues don't always guarantee strict ordering across partitions. A row generated at 10:05 can easily land in Snowflake before a row generated at 10:02, and one generated at 10:08 can land before both.

3. Real-world example

Three status updates for order 7001 are generated at 10:02 (PLACED), 10:05 (PACKED), and 10:08 (SHIPPED), but because of retry delays they physically arrive at Snowflake in the order 10:05, 10:02, 10:08. If the pipeline applies them by arrival order, the final state would incorrectly bounce PACKED → PLACED → SHIPPED-correctly-last, which happens to work here by luck, but any pipeline relying on "last inserted row" instead of "highest event_time" is one delay away from ending up on PACKED forever.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    order_id       NUMBER(38,0)  NOT NULL,
    order_status   VARCHAR(20)   NOT NULL,
    event_time     TIMESTAMP_NTZ NOT NULL,   -- when the change actually happened at the source
    commit_time    TIMESTAMP_NTZ NOT NULL,   -- when it was written into Snowflake
    sequence_number NUMBER(38,0) NOT NULL    -- source-assigned strictly increasing number per order_id
);

CREATE OR REPLACE TABLE orders_target (
    order_id        NUMBER(38,0)  NOT NULL,
    order_status    VARCHAR(20)   NOT NULL,
    last_event_time TIMESTAMP_NTZ NOT NULL,
    last_sequence_number NUMBER(38,0) NOT NULL,
    PRIMARY KEY (order_id)
);

5. Insert data (arriving out of order)

-- Arrival order: PACKED first, then PLACED, then SHIPPED (commit_time shows true arrival order)
INSERT INTO orders_raw VALUES
  (7001, 'PACKED',  '2026-06-15 10:05:00', '2026-06-15 10:05:02', 2),
  (7001, 'PLACED',  '2026-06-15 10:02:00', '2026-06-15 10:05:04', 1),
  (7001, 'SHIPPED', '2026-06-15 10:08:00', '2026-06-15 10:05:06', 3);

6. Broken scenario

-- BAD: apply rows by arrival order (insertion order / commit_time), not event order
MERGE INTO orders_target t
USING (SELECT order_id, order_status, event_time, sequence_number FROM orders_raw ORDER BY commit_time) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.order_status = s.order_status, t.last_event_time = s.event_time
WHEN NOT MATCHED THEN INSERT (order_id, order_status, last_event_time, last_sequence_number) VALUES (s.order_id, s.order_status, s.event_time, s.sequence_number);
-- Depending on execution order this can leave order_status as PLACED even though SHIPPED already happened —
-- MERGE has no guaranteed row-processing order unless you explicitly pick the winner first.

7. The fix

Always resolve to a single winning row per key using the true ordering field (sequence_number, or event_time if no sequence exists) before merging — never rely on arrival/commit order.

MERGE INTO orders_target t
USING (
  SELECT order_id, order_status, event_time, sequence_number
  FROM orders_raw
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence_number DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.sequence_number > t.last_sequence_number THEN
  UPDATE SET t.order_status = s.order_status, t.last_event_time = s.event_time, t.last_sequence_number = s.sequence_number
WHEN NOT MATCHED THEN
  INSERT (order_id, order_status, last_event_time, last_sequence_number)
  VALUES (s.order_id, s.order_status, s.event_time, s.sequence_number);
-- Result: order_status = 'SHIPPED', because sequence_number 3 wins regardless of arrival order.

8. Prevention

• Insist every event source assigns a strictly increasing sequence_number per key — event_time alone is risky since clocks can skew across producers.
• Never write pipeline logic that assumes "the last row inserted is the newest fact" — always compare the ordering field explicitly.
• If sequence_number isn't available, fall back to event_time but add monitoring for clock skew between producers.

9. Recovery

-- Re-derive correct current state for any order where target status might be stale due to past out-of-order application
MERGE INTO orders_target t
USING (
  SELECT order_id, order_status, event_time, sequence_number
  FROM orders_raw
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence_number DESC) = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.order_status = s.order_status, t.last_event_time = s.event_time, t.last_sequence_number = s.sequence_number;

10. Interview questions

• Why is arrival order unsafe for determining current state? Network and retry delays mean arrival order doesn't reflect true event order — you need an explicit ordering field, not insertion order.
• event_time vs sequence_number — which is more reliable and why? sequence_number is more reliable because it's assigned by a single authority in strictly increasing order; event_time depends on possibly-skewed clocks across distributed producers.

11. Practice questions

• Write a query that flags any order_id where event_time order and sequence_number order disagree, which would indicate a clock-skew problem worth investigating.
• Extend the MERGE to only accept an event if its event_time is within a 24-hour tolerance window of the current target's last_event_time, rejecting wildly out-of-range events to a dead-letter table instead.

1. What is it

A late-arriving update is a record whose business date is old, but which physically shows up in your pipeline much later than the date it belongs to — for example, sales for July 2 arriving in the feed on July 10. This is different from out-of-order data (Topic 118): here the delay isn't seconds, it's days, and it usually means an already-closed reporting window needs to reopen.

2. Why it happens

A regional store's POS system was offline and queued transactions locally, an upstream batch job failed silently for over a week, or a correction/refund is booked against an old sale after an audit. The record is completely valid — it's just very late.

3. Real-world example

A daily sales summary for July 2 was already computed and reported to finance. On July 10, three sales transactions dated July 2 arrive from a store whose POS terminal lost connectivity for over a week. Finance's July 2 number is now technically wrong until the pipeline reprocesses that historical window.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    order_id      NUMBER(38,0)  NOT NULL,
    order_date    DATE          NOT NULL,   -- the business date the sale belongs to
    order_amount  NUMBER(12,2)  NOT NULL,
    received_at   TIMESTAMP_NTZ NOT NULL    -- when the pipeline actually saw this row
);

CREATE OR REPLACE TABLE orders_target (
    order_id      NUMBER(38,0)  NOT NULL,
    order_date    DATE          NOT NULL,
    order_amount  NUMBER(12,2)  NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE daily_sales_summary (
    order_date    DATE          NOT NULL,
    total_amount  NUMBER(15,2)  NOT NULL,
    PRIMARY KEY (order_date)
);

5. Insert data

-- Original July 2 data, processed on time
INSERT INTO orders_raw VALUES (8001, '2026-07-02', 150.00, '2026-07-02 23:00:00');
INSERT INTO orders_target VALUES (8001, '2026-07-02', 150.00);
INSERT INTO daily_sales_summary VALUES ('2026-07-02', 150.00);

-- Late-arriving sales for July 2, received July 10
INSERT INTO orders_raw VALUES
  (8050, '2026-07-02', 40.00, '2026-07-10 09:00:00'),
  (8051, '2026-07-02', 22.50, '2026-07-10 09:00:00');

6. Broken scenario

-- BAD: pipeline only ever appends today's received rows into today's summary bucket
INSERT INTO daily_sales_summary VALUES ('2026-07-10', 62.50);
-- Now July 2 sales are permanently misfiled under July 10 in the summary,
-- and daily_sales_summary for July 2 still understates the true total.

7. The fix

Route late rows by their order_date, not by received_at, and MERGE into the historical partition/window they actually belong to, then rebuild any downstream aggregate for that window.

-- Step 1: load late rows into orders_target keyed by their true order_date
MERGE INTO orders_target t
USING orders_raw s
ON t.order_id = s.order_id
WHEN NOT MATCHED THEN
  INSERT (order_id, order_date, order_amount) VALUES (s.order_id, s.order_date, s.order_amount);

-- Step 2: identify which historical dates were touched by late arrivals
CREATE OR REPLACE TEMPORARY TABLE affected_dates AS
SELECT DISTINCT order_date FROM orders_raw WHERE received_at::DATE > order_date + 1;

-- Step 3: rebuild the summary ONLY for the affected historical windows
MERGE INTO daily_sales_summary d
USING (
  SELECT order_date, SUM(order_amount) AS total_amount
  FROM orders_target
  WHERE order_date IN (SELECT order_date FROM affected_dates)
  GROUP BY order_date
) s
ON d.order_date = s.order_date
WHEN MATCHED THEN UPDATE SET d.total_amount = s.total_amount
WHEN NOT MATCHED THEN INSERT (order_date, total_amount) VALUES (s.order_date, s.total_amount);
-- daily_sales_summary for 2026-07-02 is now correctly 212.50

8. Prevention

• Never assume a reporting window is "closed" permanently — build every downstream aggregate as a re-runnable MERGE keyed on the business date, not an append-only INSERT.
• Track a per-date "last touched" timestamp so late arrivals automatically flag which historical aggregates need rebuilding.
• Set a reasonable lateness tolerance (e.g. 30 days) after which extremely late data goes to orders_dead_letter for manual review instead of silently reopening ancient windows.

9. Recovery

-- Full recompute of a specific historical window if you're not sure what's already been rebuilt
MERGE INTO daily_sales_summary d
USING (SELECT order_date, SUM(order_amount) AS total_amount FROM orders_target WHERE order_date = '2026-07-02' GROUP BY order_date) s
ON d.order_date = s.order_date
WHEN MATCHED THEN UPDATE SET d.total_amount = s.total_amount;

10. Interview questions

• Why is received_at the wrong column to bucket data by? It reflects when the pipeline saw the row, not what business period the row actually belongs to — using it corrupts historical reporting.
• How do you avoid recomputing every historical aggregate every single run? Track which business dates were actually touched by late arrivals (via a diff or a "dirty dates" table) and only rebuild those, not the entire history.

11. Practice questions

• Design a late_arrival_log table that records every time a row arrives more than 24 hours after its order_date, for monitoring how often this happens per source.
• Write the query that would alert the team if more than 5% of a day's finalized total changes due to late arrivals — a signal the source feed itself may be unreliable.

1. What is it

A poison record is a single row so malformed that it breaks (or would break) normal processing — bad JSON, a value that doesn't match its column's datatype, a missing required key, or an otherwise corrupt row. Poison record handling means catching that one bad row without letting it take down the whole batch.

2. Why it happens

An upstream application bug emits a malformed payload, a manual data entry error produces a string where a number was expected, a partial write leaves a JSON document truncated mid-field, or a schema change upstream silently drops a required key that your pipeline still expects.

3. Real-world example

A batch of 500 order events lands in orders_raw as VARIANT JSON. One row has "order_amount": "N/A" instead of a number, because a upstream bug fired for a cancelled order. A naive CAST in the load query throws a type-conversion error and fails the entire 500-row batch, delaying 499 perfectly good orders because of one bad one.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    raw_payload   VARIANT        NOT NULL,
    received_at   TIMESTAMP_NTZ  DEFAULT CURRENT_TIMESTAMP()
);

CREATE OR REPLACE TABLE orders_target (
    order_id      NUMBER(38,0)  NOT NULL,
    order_amount  NUMBER(12,2)  NOT NULL,
    order_status  VARCHAR(20)   NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE orders_dead_letter (
    raw_payload    VARIANT        NOT NULL,
    error_reason   VARCHAR(500)   NOT NULL,
    failed_at      TIMESTAMP_NTZ  DEFAULT CURRENT_TIMESTAMP(),
    retry_count    NUMBER(3,0)    DEFAULT 0,
    resolved       BOOLEAN        DEFAULT FALSE
);

5. Insert data

INSERT INTO orders_raw (raw_payload) SELECT PARSE_JSON('{"order_id": 9001, "order_amount": 55.00, "order_status": "PLACED"}');
INSERT INTO orders_raw (raw_payload) SELECT PARSE_JSON('{"order_id": 9002, "order_amount": "N/A", "order_status": "CANCELLED"}');  -- poison: bad datatype
INSERT INTO orders_raw (raw_payload) SELECT PARSE_JSON('{"order_amount": 18.00, "order_status": "PLACED"}');  -- poison: missing order_id

6. Broken scenario

-- BAD: direct CAST fails the whole statement the moment it hits the poison row
INSERT INTO orders_target
SELECT
  raw_payload:order_id::NUMBER,
  raw_payload:order_amount::NUMBER,
  raw_payload:order_status::VARCHAR
FROM orders_raw;
-- Error: Numeric value 'N/A' is not recognized — ZERO rows are inserted, including the 1 good row.

7. The fix

Validate each row before casting, route anything that fails validation to orders_dead_letter instead of letting one bad row abort the batch, and only cast rows that already passed validation.

-- Step 1: classify every row as valid or poison using TRY_CAST and key-existence checks
CREATE OR REPLACE TEMPORARY TABLE orders_classified AS
SELECT
  raw_payload,
  raw_payload:order_id::VARCHAR         AS order_id_raw,
  TRY_CAST(raw_payload:order_amount::VARCHAR AS NUMBER(12,2)) AS order_amount_cast,
  raw_payload:order_status::VARCHAR     AS order_status_raw,
  CASE
    WHEN raw_payload:order_id IS NULL THEN 'missing order_id'
    WHEN TRY_CAST(raw_payload:order_amount::VARCHAR AS NUMBER(12,2)) IS NULL THEN 'invalid order_amount'
    WHEN raw_payload:order_status IS NULL THEN 'missing order_status'
    ELSE NULL
  END AS error_reason
FROM orders_raw;

-- Step 2: good rows go to the target
INSERT INTO orders_target
SELECT order_id_raw::NUMBER, order_amount_cast, order_status_raw
FROM orders_classified WHERE error_reason IS NULL;

-- Step 3: poison rows go to the dead letter table, batch keeps moving
INSERT INTO orders_dead_letter (raw_payload, error_reason)
SELECT raw_payload, error_reason FROM orders_classified WHERE error_reason IS NOT NULL;

8. Prevention

• Land everything as VARIANT first and validate/cast explicitly downstream, rather than casting during the initial COPY INTO, so schema drift never blocks the raw landing step.
• Add a lightweight JSON schema check (required keys present) as the very first classification rule, before any type casting is attempted.
• Alert when the dead-letter rate for a batch exceeds a threshold (e.g. 1%) — a sudden spike usually means an upstream contract break, not random noise.

9. Recovery (retry)

-- Once upstream fixes the bug and resends a corrected payload for order 9002,
-- manually re-attempt dead-lettered rows after a fix is confirmed
UPDATE orders_dead_letter SET retry_count = retry_count + 1 WHERE resolved = FALSE;

INSERT INTO orders_target
SELECT
  raw_payload:order_id::NUMBER,
  TRY_CAST(raw_payload:order_amount::VARCHAR AS NUMBER(12,2)),
  raw_payload:order_status::VARCHAR
FROM orders_dead_letter
WHERE resolved = FALSE AND TRY_CAST(raw_payload:order_amount::VARCHAR AS NUMBER(12,2)) IS NOT NULL;

UPDATE orders_dead_letter SET resolved = TRUE
WHERE resolved = FALSE AND TRY_CAST(raw_payload:order_amount::VARCHAR AS NUMBER(12,2)) IS NOT NULL;

10. Interview questions

• Why is TRY_CAST preferred over CAST in a validation layer? CAST throws and aborts the whole statement on the first bad value; TRY_CAST returns NULL, letting you isolate and route individual bad rows without blocking valid ones.
• What belongs in a dead-letter table besides the raw payload? The error reason, when it failed, a retry count, and a resolved flag — enough to both alert on patterns and support safe reprocessing later.

11. Practice questions

• Add a rule that also dead-letters any row where order_amount is negative, even though it's numerically valid.
• Write a query that summarizes orders_dead_letter by error_reason and count, to identify the single most common failure mode this week.

1. What is it

Schema evolution is what happens when the shape of your source data changes over time — a new column shows up, an old one disappears, a datatype changes, or a column that was always filled in starts arriving empty. Schema evolution handling means your pipeline keeps loading correctly when that happens, instead of silently dropping data or hard-crashing at 2am.

2. Why it happens

3. Real-world example

The order-service team ships a promotions feature. Starting today, the JSON payload landing in orders_raw includes a new field discount_code that never existed before. Your COPY INTO uses a fixed column list built six months ago. Nothing breaks immediately — Snowflake's VARIANT ingestion just doesn't have a column to put it in — but the discount data is silently lost forever unless you evolve the target schema to capture it.

4. DDL

CREATE OR REPLACE TABLE orders_raw (
    raw_payload   VARIANT        NOT NULL,
    received_at   TIMESTAMP_NTZ  DEFAULT CURRENT_TIMESTAMP()
);

CREATE OR REPLACE TABLE orders_target (
    order_id       NUMBER(38,0)   NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE schema_change_log (
    detected_at    TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    column_name    VARCHAR(100),
    change_type    VARCHAR(30),
    sample_value   VARCHAR(500)
);

5. Insert data

INSERT INTO orders_raw (raw_payload) SELECT PARSE_JSON('{"order_id": 4001, "order_amount": 60.00, "order_status": "PLACED"}');
INSERT INTO orders_raw (raw_payload) SELECT PARSE_JSON('{"order_id": 4002, "order_amount": 45.00, "order_status": "PLACED", "discount_code": "SUMMER10"}');

6. Broken scenario

-- BAD: fixed column list, new field silently discarded, no one is ever told
INSERT INTO orders_target
SELECT raw_payload:order_id::NUMBER, raw_payload:order_amount::NUMBER, raw_payload:order_status::VARCHAR
FROM orders_raw;
-- order 4002 loads successfully but discount_code = SUMMER10 is gone forever, no error, no log

7. The fix

Detect new keys automatically by diffing the VARIANT's object keys against a known-columns list, log any drift to schema_change_log, and use ALTER TABLE ADD COLUMN to evolve the target deliberately instead of dropping data silently.

-- Step 1: detect keys in raw payload that the target doesn't know about yet
INSERT INTO schema_change_log (column_name, change_type, sample_value)
SELECT DISTINCT f.key, 'NEW_COLUMN', f.value::VARCHAR
FROM orders_raw, LATERAL FLATTEN(input => raw_payload) f
WHERE f.key NOT IN ('order_id','order_amount','order_status');

-- Step 2: evolve the target once the new column is confirmed and named
ALTER TABLE orders_target ADD COLUMN discount_code VARCHAR(20);

-- Step 3: backfill the new column for rows that already have the data in raw
UPDATE orders_target t
SET discount_code = r.raw_payload:discount_code::VARCHAR
FROM orders_raw r
WHERE t.order_id = r.raw_payload:order_id::NUMBER
  AND r.raw_payload:discount_code IS NOT NULL;

8. Prevention

• Land raw data as VARIANT always, never with a rigid column list at the ingestion layer, so new fields never get silently dropped before they're even visible.
• Run a daily key-diff job comparing today's payload keys against yesterday's, and alert on any difference instead of waiting for someone to notice missing data.
• Treat datatype narrowing as a schema change too — TRY_CAST it and log a warning rather than truncating silently.

9. Recovery

-- Once discount_code is added, backfill historical rows where raw payload had it
-- but the target column didn't exist yet at load time
UPDATE orders_target t
SET discount_code = r.raw_payload:discount_code::VARCHAR
FROM orders_raw r
WHERE t.order_id = r.raw_payload:order_id::NUMBER
  AND t.discount_code IS NULL
  AND r.raw_payload:discount_code IS NOT NULL;

10. Interview questions

• Why land data as VARIANT instead of casting columns at ingestion time? VARIANT preserves every field regardless of schema drift — casting at ingestion locks you to a column list that breaks the moment the source adds or removes a field.
• What's the risk of auto-adding every new JSON key as a column? A single buggy upstream release with a typo'd field name would permanently pollute the schema with junk columns — new keys should be logged and confirmed, not blindly materialized.

11. Practice questions

• Write a query that flags any column present in orders_target's known list that has stopped appearing in the last 7 days of raw payloads (a removed-column signal).
• Extend the key-diff job to also detect a datatype change (e.g. a field that was always a number now sometimes arrives as a string).

1. What is it

Replay architecture means you can always rebuild your target table from scratch by re-running your transformation logic over raw data, because you kept the raw data around. It's your ultimate insurance policy: if the target ever gets corrupted by a bug, you don't patch broken rows one at a time, you truncate and replay.

2. Why it happens (why you need it)

Every fix you've learned so far in this module (dedup, MERGE, watermark recovery) assumes the raw data is still there to fix from. If a transformation bug corrupted orders_target for three weeks before anyone noticed, patching individual rows is error-prone and slow. Replay lets you nuke the target and regenerate it correctly from immutable raw history in one shot.

3. Real-world example

A bug in the SCD2 MERGE logic silently corrupted customer_dim_scd2's effective-date ranges for every customer updated in the last 3 weeks. Patching each broken row individually would take days and risks missing edge cases. Because orders_raw retained every change event for 3 weeks, the fix is: truncate the dimension, fix the SCD2 logic, and replay all 3 weeks of raw events through the corrected logic in one pass.

4. DDL

CREATE OR REPLACE TABLE orders_cdc_raw (
    event_id       VARCHAR(60)    NOT NULL,
    order_id       NUMBER(38,0)   NOT NULL,
    operation_type VARCHAR(10)    NOT NULL,
    order_status   VARCHAR(20),
    order_amount   NUMBER(12,2),
    sequence_id    NUMBER(38,0)   NOT NULL,
    event_ts       TIMESTAMP_NTZ  NOT NULL
)
DATA_RETENTION_TIME_IN_DAYS = 90;

CREATE OR REPLACE TABLE orders_target (
    order_id       NUMBER(38,0)   NOT NULL,
    order_status   VARCHAR(20)    NOT NULL,
    order_amount   NUMBER(12,2)   NOT NULL,
    last_sequence_id NUMBER(38,0) NOT NULL,
    PRIMARY KEY (order_id)
);

5. Insert data

INSERT INTO orders_cdc_raw VALUES
  ('evt-1', 7001, 'I', 'PLACED', 60.00, 201, '2026-05-01 09:00:00'),
  ('evt-2', 7001, 'U', 'SHIPPED', 60.00, 202, '2026-05-03 14:00:00'),
  ('evt-3', 7002, 'I', 'PLACED', 30.00, 301, '2026-05-02 10:00:00');
-- imagine 3 weeks of similar events feeding a buggy SCD2 MERGE

6. Broken scenario

-- BAD: hand-patching corrupted rows one at a time after a 3-week-old logic bug
UPDATE orders_target SET order_status = 'SHIPPED' WHERE order_id = 7001;
-- This fixes one symptom you happened to notice, but doesn't guarantee every
-- affected row across 3 weeks is correct, since you'd need to know every symptom in advance

7. The fix

Truncate the target, fix the transformation logic, and replay every raw event back through the corrected logic in sequence order. This guarantees a fully consistent rebuild instead of a partial patch.

-- Step 1: snapshot for safety before wiping the target
CREATE OR REPLACE TABLE orders_target_backup CLONE orders_target;

-- Step 2: truncate and rebuild from raw using the CORRECTED logic
TRUNCATE TABLE orders_target;

INSERT INTO orders_target (order_id, order_status, order_amount, last_sequence_id)
SELECT order_id, order_status, order_amount, sequence_id
FROM orders_cdc_raw
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY sequence_id DESC) = 1;

8. Prevention

• Set generous DATA_RETENTION_TIME_IN_DAYS (or a permanent append-only raw table) on every raw/CDC landing table, since you can't replay what you didn't keep.
• Keep transformation logic in version control with tests, so a corrected version can be applied consistently across the full replay window, not guessed at row by row.
• Always clone the target before a replay, so a bug in the replay logic itself is a 10-second rollback, not a second incident.

9. Recovery

-- If the replay itself was wrong, swap back to the pre-replay clone instantly
ALTER TABLE orders_target SWAP WITH orders_target_backup;

10. Interview questions

• Why is replay safer than patching individual corrupted rows? Patching relies on knowing every symptom in advance; replay regenerates every row consistently from source truth, catching issues you didn't even know to look for.
• What's the single prerequisite that makes replay possible at all? Raw/CDC data retention — if raw events were deleted or overwritten, there's nothing to replay from and you're stuck patching.

11. Practice questions

• Design a partial replay that only rebuilds orders touched in the last 3 weeks, instead of the full table, to save compute on a huge target.
• Write the validation query you'd run immediately after a replay to confirm the rebuilt target matches expected row counts from raw.

1. What is it

Reconciliation is automatically proving that your target table actually matches its source — same row counts, same totals, same values — instead of just assuming the pipeline worked because it didn't throw an error. A pipeline can finish with SUCCESS status and still silently produce wrong data; reconciliation is how you catch that.

2. Why it happens (why you need it)

Bugs in MERGE conditions, dropped rows from a bad JOIN, silently truncated values, or a partial watermark advance can all leave orders_target subtly wrong while every job in the orchestrator shows green. Without automated reconciliation, these drift bugs are usually discovered by an angry analyst weeks later, not by the pipeline itself.

3. Real-world example

A MERGE's join condition has a subtle bug that silently excludes any order with a NULL customer_id. Every run "succeeds." Three weeks later, finance notices July revenue is short by $40K. Reconciliation — comparing SUM(order_amount) in source vs target daily — would have caught the exact day the drift started, instead of a vague three-week-old discrepancy.

4. DDL

CREATE OR REPLACE TABLE orders_source (
    order_id      NUMBER(38,0) NOT NULL,
    customer_id   NUMBER(38,0),
    order_amount  NUMBER(12,2) NOT NULL,
    order_status  VARCHAR(20)  NOT NULL
);

CREATE OR REPLACE TABLE orders_target (
    order_id      NUMBER(38,0) NOT NULL,
    customer_id   NUMBER(38,0),
    order_amount  NUMBER(12,2) NOT NULL,
    order_status  VARCHAR(20)  NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE reconciliation_log (
    run_date       DATE,
    check_name     VARCHAR(60),
    source_value   VARCHAR(100),
    target_value   VARCHAR(100),
    status         VARCHAR(10)
);

5. Insert data

INSERT INTO orders_source VALUES
  (8001, 501, 100.00, 'PLACED'),
  (8002, NULL, 40.00, 'PLACED');   -- NULL customer_id, the row the buggy MERGE will drop

-- Buggy MERGE only copied the row WHERE customer_id IS NOT NULL
INSERT INTO orders_target VALUES (8001, 501, 100.00, 'PLACED');
-- 8002 is missing from target: the bug that drops NULL customer_id rows

6. Broken scenario

-- BAD: no automated check at all, pipeline shows SUCCESS every day
-- The org only finds out something is wrong when finance manually audits revenue weeks later
SELECT 'pipeline finished' AS status;

7. The fix

Run three levels of reconciliation after every load: row-count match, aggregate match (SUM/AVG on key numeric columns), and a row-level MINUS/EXCEPT to pinpoint exactly which rows are missing, extra, or wrong.

-- Level 1: row count check
INSERT INTO reconciliation_log
SELECT CURRENT_DATE(), 'ROW_COUNT',
       (SELECT COUNT(*) FROM orders_source)::VARCHAR,
       (SELECT COUNT(*) FROM orders_target)::VARCHAR,
       CASE WHEN (SELECT COUNT(*) FROM orders_source) = (SELECT COUNT(*) FROM orders_target)
            THEN 'PASS' ELSE 'FAIL' END;

-- Level 2: aggregate check on the business-critical column
INSERT INTO reconciliation_log
SELECT CURRENT_DATE(), 'SUM_ORDER_AMOUNT',
       (SELECT SUM(order_amount) FROM orders_source)::VARCHAR,
       (SELECT SUM(order_amount) FROM orders_target)::VARCHAR,
       CASE WHEN (SELECT SUM(order_amount) FROM orders_source) = (SELECT SUM(order_amount) FROM orders_target)
            THEN 'PASS' ELSE 'FAIL' END;

-- Level 3: row-level diff to find exactly which orders are missing
SELECT order_id, customer_id, order_amount, order_status FROM orders_source
MINUS
SELECT order_id, customer_id, order_amount, order_status FROM orders_target;
-- returns order_id 8002 -> pinpoints the exact row the buggy MERGE dropped

8. Prevention

• Run reconciliation as a mandatory step in the pipeline itself, not a separate manual audit — a FAIL should block or alert, not just get logged and ignored.
• For very large tables, use a HASH-based check (e.g. HASH_AGG over all columns) instead of a full MINUS, which is cheaper than row-by-row comparison at scale.
• Track reconciliation results over time in reconciliation_log so a slow, creeping drift is visible on a trend chart, not just a single day's pass/fail.

9. Recovery

-- Once the MINUS identifies the missing rows, insert exactly those rows (idempotent, safe to rerun)
MERGE INTO orders_target t
USING orders_source s
ON t.order_id = s.order_id
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, order_amount, order_status)
  VALUES (s.order_id, s.customer_id, s.order_amount, s.order_status);

10. Interview questions

• Why can row counts match even when the data is wrong? A count only tells you volume, not content — one row dropped and a different row duplicated leaves the count identical while the data is materially wrong.
• When would you use a HASH-based reconciliation instead of MINUS? On very large tables, hashing each row and aggregating is far cheaper than a full row-by-row MINUS comparison, though it only tells you pass/fail, not which specific rows differ.

11. Practice questions

• Write a reconciliation check that also flags "extra" rows in target that don't exist in source (the reverse MINUS direction).
• Design a reconciliation_log alert that fires only when a check has FAILed for 2 consecutive days, to avoid noise from one-off timing races.

1. What is it

A pipeline is idempotent when running it twice with the same input produces exactly the same result as running it once — no duplicate rows, no double-counted totals, no corrupted state. This topic goes deeper than Module 13's introduction: it's the single property that makes every failure-recovery technique in this module actually safe to use, because every fix in this module assumes you can rerun something.

2. Why it happens (why it matters here)

Every recovery pattern you've learned — reprocessing a dead-lettered batch, replaying raw CDC, retrying a crashed watermark job — involves running something again. If that something isn't idempotent, the "fix" becomes a second incident: duplicated revenue, doubled inventory counts, or a dimension table with conflicting history.

3. Real-world example

An Airflow DAG retries a task automatically after a transient network timeout. The first attempt actually succeeded and inserted 500 rows — the timeout happened while waiting for the success acknowledgment, not during the insert. The automatic retry reruns the same INSERT, and now there are 1,000 rows for a batch that should have 500, because the pipeline had no way to know "this batch already ran."

4. DDL

CREATE OR REPLACE TABLE orders_source (
    batch_id      VARCHAR(40)    NOT NULL,
    order_id      NUMBER(38,0)   NOT NULL,
    order_amount  NUMBER(12,2)   NOT NULL,
    order_status  VARCHAR(20)    NOT NULL
);

CREATE OR REPLACE TABLE orders_target (
    order_id      NUMBER(38,0)   NOT NULL,
    order_amount  NUMBER(12,2)   NOT NULL,
    order_status  VARCHAR(20)    NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE TABLE batch_tracker (
    batch_id      VARCHAR(40)    NOT NULL,
    status        VARCHAR(20)    NOT NULL,   -- STARTED / COMMITTED / FAILED
    row_count     NUMBER(38,0),
    started_at    TIMESTAMP_NTZ  DEFAULT CURRENT_TIMESTAMP(),
    committed_at  TIMESTAMP_NTZ,
    PRIMARY KEY (batch_id)
);

5. Insert data

INSERT INTO orders_source VALUES
  ('batch-2026-06-01', 5001, 80.00, 'PLACED'),
  ('batch-2026-06-01', 5002, 45.00, 'PLACED');
-- Simulate: this batch already ran once and committed, then Airflow retried it after a network timeout

6. Broken scenario

-- BAD: plain INSERT with no batch tracking, no dedupe key
INSERT INTO orders_target (order_id, order_amount, order_status)
SELECT order_id, order_amount, order_status FROM orders_source WHERE batch_id = 'batch-2026-06-01';
-- Airflow retries after a timeout -> the exact same statement runs again
INSERT INTO orders_target (order_id, order_amount, order_status)
SELECT order_id, order_amount, order_status FROM orders_source WHERE batch_id = 'batch-2026-06-01';
-- orders_target now has orders 5001 and 5002 TWICE -> revenue reported 2x too high

7. The fix

Combine two independent layers of idempotency: a batch_tracker that records whether a batch already committed (so the whole batch can be skipped on retry), and a MERGE keyed on the natural key (so even a partial or out-of-order rerun can't create duplicates).

-- Step 1: check the tracker before doing any work — this alone stops the second run cold
-- (application/orchestrator logic: SELECT status FROM batch_tracker WHERE batch_id = 'batch-2026-06-01')
-- if status = 'COMMITTED', skip the batch entirely and exit successfully

-- Step 2: even if that check is bypassed, MERGE on the natural key makes a rerun a no-op
MERGE INTO orders_target t
USING (SELECT order_id, order_amount, order_status FROM orders_source WHERE batch_id = 'batch-2026-06-01') s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET order_amount = s.order_amount, order_status = s.order_status
WHEN NOT MATCHED THEN
  INSERT (order_id, order_amount, order_status)
  VALUES (s.order_id, s.order_amount, s.order_status);

-- Step 3: mark the batch committed only after the MERGE succeeds, inside the same transaction boundary
BEGIN;
  MERGE INTO orders_target t USING (SELECT order_id, order_amount, order_status FROM orders_source WHERE batch_id = 'batch-2026-06-01') s
  ON t.order_id = s.order_id
  WHEN MATCHED THEN UPDATE SET order_amount = s.order_amount, order_status = s.order_status
  WHEN NOT MATCHED THEN INSERT (order_id, order_amount, order_status) VALUES (s.order_id, s.order_amount, s.order_status);

  MERGE INTO batch_tracker bt USING (SELECT 'batch-2026-06-01' AS batch_id) b
  ON bt.batch_id = b.batch_id
  WHEN MATCHED THEN UPDATE SET status = 'COMMITTED', committed_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED THEN INSERT (batch_id, status, committed_at) VALUES (b.batch_id, 'COMMITTED', CURRENT_TIMESTAMP());
COMMIT;

8. Prevention

• Never use plain INSERT for anything that an orchestrator might retry — default to MERGE keyed on a natural or business key everywhere reruns are possible.
• Wrap the data write and the batch-status update in the same transaction, so you can never end up with data committed but the tracker still saying STARTED (or vice versa).
• Design every batch_id to be deterministic (e.g. derived from the source watermark window, not a random UUID per run), so a retry naturally produces the identical batch_id and is recognized as "the same batch."

9. Recovery

-- If a batch is stuck at STARTED (crashed mid-run) with no COMMITTED status,
-- it's safe to simply rerun it — the MERGE ensures no duplicates regardless of what partially landed
SELECT * FROM batch_tracker WHERE status = 'STARTED' AND started_at < DATEADD(hour, -2, CURRENT_TIMESTAMP());
-- for each stuck batch: rerun the MERGE + tracker update above using its batch_id

10. Interview questions

• Why is MERGE-on-natural-key considered a stronger guarantee than a batch-tracking table alone? The tracker only prevents whole-batch reruns; MERGE prevents duplication at the row level regardless of how the rerun happens, including partial or out-of-order retries.
• Why must the data write and the tracker update share a transaction? Without a shared transaction boundary, a crash between the two steps leaves data committed but the tracker unaware, causing an unnecessary (though harmless, thanks to MERGE) rerun — or worse, data marked committed that never actually landed.

11. Practice questions

• Design a batch_id scheme for a CDC pipeline where the source doesn't naturally provide clean batch boundaries.
• Write a query that finds any batch_tracker row stuck at STARTED for more than 2 hours, as a stuck-job alert.

1. What is it

Join performance tuning is making sure a query that combines two or more tables does the least possible work to get the right answer — filtering early, joining on the right keys, and avoiding accidental row explosion. A slow join is the single most common cause of "why is this query burning credits" tickets.

2. Why it happens

3. Real-world example

An analyst joins orders (50M rows) to order_events (200M rows, several events per order) to get the latest status, then filters to last 7 days after the join. Snowflake ends up joining the full 50M x 200M history before the filter ever gets applied, scanning and shuffling data that the WHERE clause was going to throw away anyway.

4. DDL

CREATE OR REPLACE TABLE orders (
    order_id      NUMBER(38,0)   NOT NULL,
    customer_id   NUMBER(38,0),
    order_date    DATE           NOT NULL,
    order_amount  NUMBER(12,2)   NOT NULL
);

CREATE OR REPLACE TABLE order_events (
    order_id      NUMBER(38,0)   NOT NULL,
    event_status  VARCHAR(20)    NOT NULL,
    event_ts      TIMESTAMP_NTZ  NOT NULL
);

CREATE OR REPLACE TABLE customer_dim_scd2 (
    customer_id    NUMBER(38,0)   NOT NULL,
    customer_tier  VARCHAR(20)    NOT NULL,
    effective_from DATE           NOT NULL,
    effective_to   DATE,
    is_current     BOOLEAN        NOT NULL
);

5. Insert data

INSERT INTO orders VALUES
  (6001, 701, '2026-06-25', 90.00),
  (6002, 701, '2026-01-10', 40.00);   -- old order, outside the 7-day window

INSERT INTO order_events VALUES
  (6001, 'PLACED', '2026-06-25 09:00:00'),
  (6001, 'SHIPPED', '2026-06-26 10:00:00'),
  (6001, 'DELIVERED', '2026-06-28 11:00:00'),
  (6002, 'PLACED', '2026-01-10 09:00:00');

INSERT INTO customer_dim_scd2 VALUES
  (701, 'SILVER', '2025-01-01', '2026-03-31', FALSE),
  (701, 'GOLD',   '2026-04-01', NULL, TRUE);

6. Broken scenario

-- BAD #1: filter applied AFTER the join, so Snowflake joins full history first
SELECT o.order_id, e.event_status, o.order_amount
FROM orders o
JOIN order_events e ON o.order_id = e.order_id
WHERE o.order_date >= DATEADD(day, -7, CURRENT_DATE());
-- joins all 4 event rows to their orders BEFORE filtering out order 6002's old row

-- BAD #2: SCD2 join with no date bounds -> matches BOTH historical rows for customer 701
SELECT o.order_id, c.customer_tier
FROM orders o
JOIN customer_dim_scd2 c ON o.customer_id = c.customer_id;
-- order 6001 (June 2026) incorrectly gets matched to BOTH SILVER (expired) and GOLD (current) -> duplicate rows

7. The fix

Push filters into a CTE before the join (pre-filtering), aggregate to the needed grain before joining when possible (pre-aggregation), and always bound SCD2 joins with the effective-date range so exactly one historical row matches.

-- FIX #1: filter orders down to the 7-day window BEFORE joining to events
WITH recent_orders AS (
  SELECT order_id, order_amount
  FROM orders
  WHERE order_date >= DATEADD(day, -7, CURRENT_DATE())
)
SELECT r.order_id, e.event_status, r.order_amount
FROM recent_orders r
JOIN order_events e ON r.order_id = e.order_id;
-- Snowflake now only ever joins the 1 relevant order's events, not all 4 rows across both orders

-- FIX #2: pre-aggregate to latest status per order BEFORE joining, avoiding a 1-to-many blowout
WITH latest_status AS (
  SELECT order_id, event_status
  FROM order_events
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY event_ts DESC) = 1
)
SELECT o.order_id, l.event_status, o.order_amount
FROM orders o
JOIN latest_status l ON o.order_id = l.order_id;

-- FIX #3: SCD2 join correctly bounded by the effective-date range -> exactly one match per order
SELECT o.order_id, c.customer_tier
FROM orders o
JOIN customer_dim_scd2 c
  ON o.customer_id = c.customer_id
  AND o.order_date >= c.effective_from
  AND (o.order_date < c.effective_to OR c.effective_to IS NULL);
-- order 6001 (June 2026) now correctly matches only GOLD, the tier active on that date

8. Prevention

• Always filter and aggregate to the smallest necessary grain in a CTE before joining, rather than joining wide and filtering late.
• Never join to an SCD2 table without an explicit effective-date range condition — treat it as a mandatory part of the ON clause, not optional.
• Watch for skewed join keys (like a NULL or "SYSTEM" customer_id with millions of rows) and filter or handle them separately before the main join.

9. Recovery

-- If a report already produced duplicated rows from an unbounded SCD2 join,
-- dedupe post-hoc by picking the row matching the correct effective window
SELECT order_id, customer_tier FROM (
  SELECT o.order_id, c.customer_tier,
         ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY c.effective_from DESC) AS rn
  FROM orders o
  JOIN customer_dim_scd2 c ON o.customer_id = c.customer_id
                            AND o.order_date >= c.effective_from
) WHERE rn = 1;

10. Interview questions

• Why does filtering before a join usually outperform filtering after? It reduces the row count on one side of the join before the expensive join operation happens, instead of doing the full join and throwing rows away afterward.
• Why must every SCD2 join include an effective-date range? Without it, the join matches every historical version of a dimension row instead of just the one valid on the fact row's date, causing silent row duplication.

11. Practice questions

• Rewrite a query that joins three tables (orders, order_events, customer_dim_scd2) applying pre-filtering, pre-aggregation, and correct SCD2 bounding all together.
• Given a join key with heavy skew (one value has 40% of all rows), design a query pattern that handles the skewed value separately from the rest.

1. What is it

Incremental load performance tuning is making sure your "load only what changed" pipeline actually scans only what changed — not the whole table — by lining up your watermark filter with how Snowflake physically organizes and prunes data.

2. Why it happens

An incremental load is supposed to be cheap because it touches a small slice of data. It becomes expensive when the watermark filter can't be pruned efficiently (e.g. filtering on a column that isn't well-clustered), when the batch window is too large (loading a whole week at once instead of hourly), or when a single-threaded loader ingests files one at a time instead of in parallel.

3. Real-world example