Big Data, Data Engineering & Analytics

The pipeline is built, tested in CI and scheduled. This last part is about the months after go-live: knowing within minutes when a run fails, knowing within a week what the pipeline costs and which models are slow, and having a short runbook for the mornings when the dashboard is wrong. Everything here uses tools already in the stack: Airflow, dbt's own output files and Snowflake's usage views. Part 10 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 9, CI/CD for dbt with GitHub Actions. Code for this part: scripts/dbt_run_report.py , snowflake/04_monitoring.sql . Full project: tpch_analytics on GitHub . Signals Checked by Action Airflow task states failed tasks after retries dbt run_results.json test failures, slow models Source freshness RAW stopped updating Snowflake ACCOUNT_USAGE credits, slow queries, loads Alert in minutes failure callback (part 8) Weekly review cost and performance SQL Runbook find cause, fix, rerun task...

A dbt project is code, and code changes should be tested before they reach production. The goal of this part: every pull request automatically builds and tests the models it changed, in its own temporary Snowflake schema, and the result shows up as a green tick or a red cross on the pull request. Only then is it merged and picked up by the Airflow DAG from part 8. Part 9 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 8, Orchestrating dbt with Airflow. Next: Part 10, Running the pipeline in production. Code for this part: .github/workflows/dbt_ci.yml , macros/drop_ci_schemas.sql , ci/profiles.yml . Full project: tpch_analytics on GitHub . Pull request opened or updated Check out main, dbt parse --target prod → production manifest dbt build --select state:modified+ --defer --state prod-state in schema CI_PR_42_* Drop CI schemas Merge Unchanged upstream models read from production Deploy: Airflow picks up main, dbt deps + dbt parse

The pipeline works when you run it by hand. Production needs it to run every day, in the right order, with retries when Snowflake has a hiccup and an alert when something really breaks. That is Airflow's job. In this part we write a DAG that loads new files with COPY INTO and then runs the whole dbt project, with every model and its tests as separate Airflow tasks, using Astronomer's open-source Cosmos library. Part 8 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 7, Incremental models and snapshots. Next: Part 9, CI/CD for dbt with GitHub Actions. Code for this part: airflow/dags/tpch_daily.py , airflow/requirements.txt . Full project: tpch_analytics on GitHub . DAG tpch_daily · 02:00 UTC daily · 23 tasks load_raw_orders COPY INTO transform (Cosmos DbtTaskGroup) orders source freshness stg_tpch__orders .run stg_tpch__orders .test lineitem source freshness stg_…line_items .run stg_…line_items .test fct_order_items .run fct...

So far every dbt run rebuilds every table from scratch. That is simple and correct, and for small tables it is the right choice. But order lines only ever grow, and rebuilding years of history every morning to add one day of data wastes warehouse time. This part makes fct_order_items incremental , so each run processes only new rows, and adds a snapshot that records how customers change over time. Part 7 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 6, Testing and documenting with dbt. Next: Part 8, Orchestrating dbt with Airflow. Code for this part: fct_order_items.sql , snap_customers.sql , snowflake/03_next_batch.sql . Full project: tpch_analytics on GitHub . Incremental model: fct_order_items First run (or --full-refresh) build the whole table 295,635 rows New batch arrives RAW rows with a newer _loaded_at: 4,179 lines Next run select only new rows MERGE on order_item_key → 299,814 Snapshot: snap_customers (slowly changing dim...

A model that builds is not a model that is right. Keys can duplicate after a bad join, a source can stop sending data without any error, and a calculation can drift from the source system's numbers. dbt lets you write those expectations down as tests that run with every build. In this part we add 20 tests to the project, watch one of them catch a real discrepancy, check that raw data is fresh, and publish documentation with a lineage graph. Part 6 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 5, Modeling with dbt: staging to marts. Next: Part 7, Incremental models and snapshots. Code for this part: _tpch__models.yml , _core__models.yml , tests/ . Full project: tpch_analytics on GitHub . dbt source freshness is RAW recent? build model stg_tpch__orders run its tests unique, not_null, accepted_values, relationships pass build downstream models int_…, fct_…, dim_… fail skip everything downstream bad data never reaches marts dbt bui...

With dbt connected to Snowflake, the real work is deciding how to organise the transformations. The structure most dbt projects converge on has three layers: staging cleans each source table, intermediate combines them and applies business logic, and marts publish facts and dimensions people actually query. This part builds all three for the TPC-H data and ends with a star schema and a revenue table ready for a dashboard. Part 5 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 4, Your first dbt project on Snowflake. Next: Part 6, Testing and documenting with dbt. Code for this part: models/ (staging, intermediate, marts) . Full project: tpch_analytics on GitHub . Sources (RAW) orders lineitem customer nation region part Staging (views) stg_tpch__orders stg_tpch__line_items stg_tpch__customers stg_tpch__nations stg_tpch__regions stg_tpch__parts Intermediate int_order_items __enriched Marts (tables) fct_order_items fct_orders dim_cus...

dbt (data build tool) lets you build a warehouse by writing SELECT statements. You describe each table as a query, dbt works out the order to build them in, wraps each query in the right CREATE VIEW or CREATE TABLE , and runs it in Snowflake. In this part we install dbt Core, connect it to Snowflake, declare the RAW tables from part 3 as sources and build our first model. Part 4 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 3, Loading raw data into Snowflake. Next: Part 5, Modeling with dbt: staging to marts. Code for this part: dbt_project.yml , ci/profiles.yml , _tpch__sources.yml , macros/ . Full project: tpch_analytics on GitHub . profiles.yml who and where: account, role, schema dbt_project.yml what: folders, defaults models/*.sql + *.yml SELECT statements, sources, tests dbt compile Jinja → plain SQL ref() / source() → real table names Snowflake runs CREATE VIEW / CREATE TABLE AS on TRANSFORM_WH dev DBT_ARAVIND_STAGING DBT_A...

Data reaches Snowflake as files: exports from an application database, nightly drops from a partner, logs written to cloud storage. The standard way to load them is a stage (where files wait), a file format (how to read them) and COPY INTO (load them into a table). In this part we set up all three, load the TPC-H history into RAW.TPCH , and learn how to check exactly what was loaded. Part 3 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 2, Setting up Snowflake for dbt. Next: Part 4, Your first dbt project on Snowflake. Code for this part: snowflake/02_load_raw.sql , airflow/dags/sql/load_raw_orders.sql . Full project: tpch_analytics on GitHub . Source system writes files orders/batch_1998_06/ *.csv.gz Stage @RAW.TPCH.LANDING (internal or S3) COPY INTO file format CSV_GZ adds _LOADED_AT adds _SOURCE_FILE skips files already loaded RAW.TPCH ORDERS LINEITEM Load metadata which files loaded, when COPY_HISTORY rows, errors per file

Before loading a single row, a Snowflake account needs a structure that keeps costs predictable and access tidy: separate warehouses per workload, a database for raw data and one for modelled data, and roles that give each user exactly what it needs. This part sets all of that up with one SQL script, snowflake/01_setup.sql , that you can run in a Snowsight worksheet in about five minutes. Part 2 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Previous: Part 1, The stack and what we will build. Next: Part 3, Loading raw data into Snowflake. Code for this part: snowflake/01_setup.sql . Full project: tpch_analytics on GitHub . Users Roles What each role can use AIRFLOW_SVC service user, key-pair You (developer) SSO / MFA login BI tool user LOADER TRANSFORMER REPORTER LOADING_WH · write RAW.TPCH create tables, stages, file formats TRANSFORM_WH · read RAW, create schemas in ANALYTICS REPORTING_WH · read ANALYTICS marts SYSADMIN all three roles roll up to...

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. Part 1 of 10 in the series Build a modern data pipeline with Snowflake, dbt and Airflow . Next: Part 2, Setting up Snowflake for dbt. Code for this part: the full tpch_analytics project . Source system TPC-H sample data Stage gzip CSV files RAW database ORDERS, LINEITEM, CUSTOMER … COPY INTO (part 3) ANALYTICS database dbt builds: STAGING → INTERMEDIATE → MARTS tests + snapshots BI and ML marts Airflow: daily DAG runs COPY INTO, then every dbt model and its tests (part 8) GitHub Actions: every pull request builds and tests only changed models (part 9) Snowflake holds both databases; dbt...