Most tutorials on Snowflake, dbt or Airflow show one tool in isolation. Real data platforms need all three working together: Snowflake to store and compute, dbt to turn raw tables into tested, documented models, and Airflow to run everything on schedule and recover when something breaks. This 10-part series builds that complete pipeline step by step, with code you can run and diagrams for every stage.
Code for this part: the full tpch_analytics project.
What each tool does
| Tool | Job in this pipeline | Covered in |
|---|---|---|
| Snowflake | Stores raw and modelled data; runs all SQL on elastic, per-second-billed warehouses | Parts 2, 3, 10 |
| dbt | Turns raw tables into staging, intermediate and mart models in plain SQL, with tests, documentation and lineage | Parts 4 to 7 |
| Apache Airflow | Runs the daily load and the dbt build in the right order, retries failures and alerts | Part 8 |
| GitHub Actions | Builds and tests every pull request in an isolated schema before it reaches production | Part 9 |
If you are new to the ideas behind this stack, start with ETL vs ELT (we use ELT: load first, transform inside Snowflake), Snowflake architecture explained and Apache Airflow for data engineers.
The data: TPC-H retail orders
Every Snowflake account includes the SNOWFLAKE_SAMPLE_DATA database with the TPC-H benchmark data: a wholesale supplier with customers, orders, order lines, parts and suppliers. Using it means you need no files to start, and the data is realistic enough to model properly. We use the TPCH_SF1 schema (1.5 million orders, about 6 million order lines).
| Table | Grain | Joins to |
|---|---|---|
| ORDERS | One row per order | CUSTOMER |
| LINEITEM | One row per order line (up to 7 per order) | ORDERS, PART, SUPPLIER |
| CUSTOMER | One row per customer, with market segment | NATION |
| NATION, REGION | 25 nations in 5 regions | REGION |
| PART, SUPPLIER | Products and who supplies them | NATION |
What we will build
By part 10 the pipeline will produce these tables in Snowflake, rebuilt and tested every morning:
- fct_order_items: one row per order line with gross, discount, net, tax and total amounts. Loaded incrementally.
- fct_orders: one row per order with totals rolled up from its lines.
- dim_customers: customers with nation, region and order history.
- dim_parts: the product catalogue.
- agg_monthly_revenue: revenue by month, region and market segment, ready for a dashboard.
- snap_customers: the full history of customer changes (slowly changing dimension type 2).
Behind them: 7 staging models, 1 intermediate model and 20 data tests.
The project layout
All the code lives in one repository. Here is the finished structure; each part of the series fills in a piece of it.
texttpch_analytics/
├── dbt_project.yml # dbt project config (part 4)
├── packages.yml # dbt_utils
├── requirements.txt # pinned dbt versions
├── ci/profiles.yml # Snowflake connections: dev, ci, prod (parts 4, 9)
├── snowflake/
│ ├── 01_setup.sql # warehouses, databases, roles, users (part 2)
│ ├── 02_load_raw.sql # stage, file format, raw tables (part 3)
│ ├── 03_next_batch.sql # simulate a new day of data (part 7)
│ └── 04_monitoring.sql # cost and performance queries (part 10)
├── models/
│ ├── staging/tpch/ # 1:1 cleaned copies of raw tables (part 5)
│ ├── intermediate/ # joins and business logic (part 5)
│ └── marts/ # facts, dimensions, aggregates (parts 5, 7)
├── tests/ # custom data tests (part 6)
├── snapshots/ # customer history (part 7)
├── macros/ # schema naming, CI clean-up (parts 4, 9)
├── airflow/dags/ # the daily DAG and its SQL (part 8)
├── .github/workflows/ # pull-request CI (part 9)
└── scripts/ # run report for monitoring (part 10)What you need
- A Snowflake account. A free 30-day trial is enough for the whole series. All warehouses here are X-Small (1 credit per hour while running) and suspend after 60 seconds idle, so following along costs very little.
- Python 3.10 or newer for dbt, and git.
- Airflow for part 8: a local install, Docker, or a managed service.
- A GitHub repository for part 9.
The code in this series was tested with these versions:
| Package | Version |
|---|---|
| dbt-core | 1.12.5 |
| dbt-snowflake | 1.12.1 |
| Apache Airflow | 3.1.8 |
| astronomer-cosmos | 1.15.1 |
| apache-airflow-providers-snowflake | 6.18.0 |
No Snowflake account yet? The project also runs on DuckDB on your laptop: the TPC-H tables have the same columns, so every dbt model, test and snapshot in this series works unchanged. That is how each snippet in the series was verified; the Snowflake-only statements (warehouses, roles, stages, COPY INTO, usage views) are marked as such.
How the series is organised
Each part is self-contained: it starts from where the previous one ended, adds one capability, and finishes with something you can run and check. Parts 2 and 3 set up Snowflake. Parts 4 to 7 build the dbt project. Part 8 schedules it with Airflow, part 9 adds CI/CD, and part 10 covers running it day to day: cost, monitoring and what to do when a run fails.
The series
- The stack and what we will build (this post)
- Setting up Snowflake for dbt
- Loading raw data into Snowflake
- Your first dbt project on Snowflake
- Modeling with dbt: staging to marts
- Testing and documenting with dbt
- Incremental models and snapshots
- Orchestrating dbt with Airflow
- CI/CD for dbt with GitHub Actions
- Running the pipeline in production
All parts: Data Pipeline Series. Every file shown in this series is in the companion project tpch_analytics on GitHub; clone it to follow along, or run it locally on DuckDB without a Snowflake account.
Comments
Post a Comment