Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท Modeling and Warehousing

Warehouse SQL Patterns: ELT, Dedup, Incremental

Master the SQL patterns data engineers use daily: ELT with staging tables, latest-record deduplication, MERGE upserts, incremental loads and rebuildable marts.

Warehouses such as BigQuery, Snowflake, Redshift and ClickHouse have changed the default order of work. Instead of transforming data on a separate server before loading (ETL), teams load raw data first and transform it with SQL inside the warehouse (ELT). The warehouse's scalable compute does the heavy lifting, and transformations become version-controlled SQL files.

This lesson shows the handful of SQL patterns that cover most of that work. Examples run on SQLite from Python, using only features that exist in every major warehouse (some names differ, which the text points out).

ELT in layers

flowchart LR
  R["raw_orders<br/>as loaded"] --> S["stg_orders<br/>typed and cleaned"]
  S --> C["orders<br/>deduplicated"]
  C --> M["mart_daily_revenue"]
  • raw_* tables are append-only copies of what arrived, with load metadata.
  • stg_* (staging) models rename columns, cast types and drop obviously bad rows.
  • Core and mart models join, deduplicate and aggregate.

Each layer is a SQL SELECT, materialized as a view or a table. Tools like dbt manage the dependency order, but the SQL itself is plain.

Pattern 1: staging with casts

Python Raw to staging

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note CAST('abc' AS REAL) gives 0.0 in SQLite; in BigQuery or Snowflake use SAFE_CAST or TRY_CAST to get NULL instead of an error, then filter or flag those rows.

Pattern 2: keep the latest version of each record

Sources resend rows or send updates as new events. Deduplicate with a window function: number the versions per key, newest first, and keep number 1.

Python Latest row per key with ROW_NUMBER

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Snowflake and BigQuery offer QUALIFY ROW_NUMBER() OVER (...) = 1 to skip the subquery. Always add a tiebreaker to ORDER BY (for example _loaded_at DESC, then a unique id) so reruns are deterministic.

Pattern 3: MERGE (upsert)

MERGE updates matching rows and inserts new ones in one statement. Most warehouses support it; SQLite uses INSERT ... ON CONFLICT DO UPDATE, which does the same job for this case.

Python Upsert a batch into a target table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In standard warehouse SQL the same logic reads:

SQL
MERGE INTO orders t
USING batch s ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET customer = s.customer, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer, amount, updated_at)
  VALUES (s.order_id, s.customer, s.amount, s.updated_at);

The updated_at guard makes the merge safe against out-of-order batches. Remember the source batch must be unique per key, or the MERGE fails or applies a random duplicate.

Pattern 4: incremental models

Rebuilding a 5 TB table every hour wastes money. An incremental model processes only new data, using a watermark: the highest updated_at or _loaded_at already in the target.

Python Incremental load with a watermark

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Watermark pitfalls: late-arriving data with an older timestamp is missed (fix with a lookback window, such as reprocess the last 3 days, which MERGE makes safe), and clocks that differ between systems. Always keep a way to do a full refresh that rebuilds from raw.

Pattern 5: rebuildable marts

Aggregated marts should be pure functions of the layers below. If you can drop and recreate them from raw data at any time, bugs are fixable by changing SQL and re-running, with no manual patching.

Python A daily revenue mart

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Style habits that scale

  • Name columns consistently (order_id, created_at, is_active).
  • Use CTEs (WITH ...) to name steps instead of deep subqueries.
  • Select explicit columns in models; SELECT * hides schema changes.
  • Keep one grain per model and document it.
  • Test uniqueness of keys and not-null columns (see the quality lessons).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main idea of ELT compared with ETL?

    ELT uses the warehouse's own compute for transformations, keeping raw data available.
  2. 2. Which technique keeps only the latest record per key?

    A window function numbers versions per key so you can keep the newest.
  3. 3. Why add a guard like updated_at > target.updated_at to a MERGE?

    Out-of-order batches would otherwise reintroduce stale values.
  4. 4. What is a risk of watermark-based incremental loads?

    Rows older than the watermark are skipped unless you use a lookback window or occasional full refresh.

Summary

  • ELT loads raw data first, then builds staging, core and mart layers in SQL.
  • Dedup with ROW_NUMBER, upsert with MERGE (guard by timestamp), load incrementally with a watermark and a lookback.
  • Keep marts rebuildable and models at one clear grain.

Next: Orchestration and DAGs