Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 38 ยท Streams, Tasks and Pipelines

Designing an ELT Pipeline in Snowflake

Design a Snowflake ELT pipeline with raw, clean and mart layers, idempotent MERGE loads, data quality checks, error handling and a choice between tasks and dynamic tables.

Traditional ETL transforms data on a separate server before loading it. Snowflake's compute is elastic and cheap to start, so the common pattern is ELT: load raw data first, then transform it inside the warehouse with SQL. This lesson ties streams, tasks and dynamic tables into a design you can reuse, with a layered structure and the rules that keep a pipeline correct when it fails and reruns.

The Snowflake pipeline objects are shown read-only. The layers and quality checks run as portable SQL on the sample shop data.

The layered shape

flowchart LR
  S["Sources<br/>files, apps, CDC"] --> R["RAW<br/>as received"]
  R --> C["CLEAN<br/>typed, deduplicated"]
  C --> M["MART<br/>business models"]
  M --> B["BI, apps, ML"]

Each layer has one job, and every layer can be rebuilt from the one before it.

  • RAW (bronze): data exactly as it arrived, usually with the load time and source file name. Never edited. Often one VARIANT column for JSON, or the columns of the source.
  • CLEAN (silver): typed columns, trimmed text, duplicates removed, bad rows set aside, keys defined.
  • MART (gold): modeled for questions: facts and dimensions, aggregates and metrics, one place for each business definition (such as "active customer").

Typical Snowflake placement: a database per environment, a schema per layer (RAW, CLEAN, MART), and roles that can read the layer they need (see roles and privileges).

Building the layers

The next example builds all three layers from the shop tables, including a data-quality quarantine for rows that fail checks.

SQL RAW, CLEAN and MART layers (stand-in) ยท database: shop

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

Raw equals clean plus quarantine: that identity is a good automated test. Note that nothing was thrown away; the mart can always be rebuilt.

Idempotent loads

A pipeline will fail and rerun, or run twice by mistake. Design every step so running it again gives the same result:

  • MERGE on a business key rather than plain INSERT.
  • Delete-then-insert for a window: reload a day by deleting that day first, inside one transaction.
  • Deduplicate using QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1.
  • Keep a load log with source file and row counts, so re-ingesting a file is detected (the COPY INTO command already skips files it loaded in the last 64 days).
SQL MERGE-style upsert is safe to repeat ยท database: shop

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

Both numbers match, even though the load ran twice. In Snowflake you would write this as a MERGE.

Data quality checks

Treat tests as part of the pipeline, run after each layer:

Check Example query idea
Row counts clean + quarantine = raw
Uniqueness no duplicate keys in clean
Not null key and date columns filled
Referential integrity every order has a customer
Freshness newest loaded_on within the last day
Reasonableness revenue within normal bounds

Snowflake also offers data metric functions (built-in ones such as NULL_COUNT, DUPLICATE_COUNT, FRESHNESS, and your own) that you attach to a table and schedule, with results stored for alerting.

SQL Quality checks as SQL ยท database: shop

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

Every check should return 0 for a healthy table. A pipeline stage fails (or alerts) when a check returns more.

Orchestrating the pipeline

Choose the simplest tool that works:

  1. Dynamic tables for RAW to CLEAN to MART when the transformations are SQL queries and freshness is stated as a lag.
  2. Streams and tasks when you need MERGE logic per change, calls to procedures or external side effects.
  3. Task graphs for fixed batch windows with dependencies.
  4. An external orchestrator (Airflow, dbt Cloud) when the workflow spans many systems; dbt is the usual choice for managing the SQL itself.

For ingestion itself, Snowpipe and Snowpipe Streaming (see the loading chapter of this course) feed RAW continuously; the pipeline above takes over from there.

Error handling and observability

  • Quarantine bad rows instead of failing whole loads, but fail loudly on structural problems (missing columns, a file of the wrong shape).
  • Record each run: start, end, rows in, rows out, status.
  • Alert on failures (SUSPEND_TASK_AFTER_NUM_FAILURES, notification integrations).
  • Keep transformations in version control, deploy through environments (dev, test, prod) using zero-copy clones for safe testing.
  • Watch cost per pipeline by giving it its own warehouse and tag (see cost monitoring).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main difference between ETL and ELT?

    ELT uses the warehouse's own compute for transformation after loading.
  2. 2. Why keep a RAW layer that is never edited?

    An untouched copy makes reprocessing and auditing possible.
  3. 3. Which makes a load idempotent?

    MERGE (or delete-then-insert for a window) produces the same result no matter how often it runs.
  4. 4. A row fails a validation check. What is a good default?

    Quarantine keeps the evidence and lets you fix and reprocess.
  5. 5. When are streams and tasks better than dynamic tables?

    Dynamic tables are declarative; streams and tasks allow arbitrary procedural steps.

Summary

  • ELT loads first, transforms in Snowflake; use RAW, CLEAN, MART layers.
  • Make every step idempotent; quarantine bad rows.
  • Automate quality checks and monitor freshness.
  • Pick dynamic tables for declarative flows, streams and tasks for custom ones.

Next: Snowpark DataFrames