Big Data, Data Engineering & Analytics

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...