Week 15 - Final Project

Project Ideas and Inspiration

Cross-Track Collaboration

The Project Architecture

Ingestion, ACA, and CI/CD

dbt models

Publish to Postgres

Running the Pipeline Locally

Orchestrating with Airflow

Promoting to Production

Secrets & Variables

Optional: Failure Alerting

Optional: Enriching with an LLM

Optional: The Health Page

Optional: Inbound Sync

Team Timelines

Gotchas & Pitfalls

Frequently Asked Questions

Glossary: Week 15

Career relevance: Week 15

dbt models

Ingestion lands raw JSON. dbt turns those files into tested tables the backend can trust. This chapter is the final-project dbt map: which variables matter, how read_files works, which schemas you write, and how to model stg → int → marts in this repo. It is not a dbt fundamentals course - that was Week 10 and Week 13.

Run builds in Running the Pipeline Locally. Change models after you understand ingest (Ingestion, ACA, and CI/CD). Publish is a separate Python step (Publish to Postgres).

By the end of this chapter, you should be able to:

• Find the dbt folders that own staging, intermediate, marts, and singular tests
• Set LANDING_PATH and DBT_SCHEMA for a personal Path A run
• Explain how staging uses read_files and schemaHints
• Name which Databricks schemas belong to laptop, Mode 3, and Mode 4
• Sketch a stg → (optional int) → marts change when you leave the starter feed

Project structure

Everything dbt lives under data/dbt/ in your team monorepo. Starter layout (teams B/C; team A may rename models for events):

data/dbt/
├── dbt_project.yml          # project name, vars, materializations
├── profiles.yml             # Databricks target (gitignored secrets via env)
├── .sqlfluff                # SQL lint rules used in CI
├── models/
│   ├── staging/
│   │   ├── stg_postings.sql       # read_files + rename + dedupe
│   │   └── _stg_postings.yml      # tests on staging grain
│   ├── intermediate/              # optional joins / explode helpers
│   │   ├── int_posting_tags.sql
│   │   └── _int_posting_tags.yml
│   └── marts/
│       ├── fct_postings.sql
│       ├── fct_postings_enriched.py   # optional enrichment (dict or LLM)
│       ├── fct_tag_popularity.sql
│       └── _fct_*.yml                 # tests on keys the backend reads
└── tests/                             # singular SQL tests (not schema.yml)
    ├── assert_postings_not_empty.sql
    └── …

There is no models/sources/ folder for landing JSON. Staging reads the volume with read_files (next section). Profiles and connection env: Secrets & Variables.

Variables dbt cares about

These live in data/.env for Path A and Astro. Full tables: Secrets & Variables.

LANDING_PREFIX is an ingest write setting. dbt never reads it. On Path A, the last segment of the parent LANDING_PATH must still match LANDING_PREFIX so you read the files you just wrote.

<aside> 💡 Team path shapes. Teams B/C: LANDING_PATH is the parent only; staging SQL appends /postings. Team A: LANDING_PATH includes /events and staging matches that full path. Copy .env.example from your own repo.

</aside>

On the team Airflow VM, the same ideas are Airflow Variables (LANDING_PATH, LANDING_PATH_DEV, DBT_SCHEMA, …), not your laptop .env. Update them when you promote: ch12.

Schemas: who writes where

Your laptop cannot write analytics. Mode 4 is merge + the prod DAG only.

flowchart LR
    files[JSON in landing volume]
    stg[stg_*]
    int[int_* optional]
    mart[fct_* / dim_*]
    files --> stg --> int --> mart
    stg --> mart

read_files and schema hints

There is no dbt source() table for raw landing files. Staging calls Databricks read_files over a volume path. That is why lineage in dbt docs starts at staging - the API → file hop lives in Airflow and ingest, not in dbt. Architecture detail: Project Architecture.

Typical teams B/C pattern from the starter stg_postings.sql (parent landing_path + /postings in SQL):

read_files(
  '{{ var("landing_path") }}/postings'
  , format => 'json'
  , schemaHints => 'slug string, title string, …, created_at bigint'
)

https://gist.githack.com/lassebenni/0fb5a415c09322717b165944c7f91d80/raw/week_15__read_files_code_explainer.html

schemaHints must match source field names and types on disk, not your cleaned mart column names. After you swap APIs, open a landed JSON file and align hints with what ingest actually wrote. Nested fields need nested types in the hint.

Then a rename CTE maps to clean names (posting_id, posted_at, …). Fix date casts to match the bytes: Arbeitnow often uses unix seconds (timestamp_seconds); many feeds use ISO strings. Leaving the wrong cast after a source swap breaks dbt or produces nonsense dates.

Keep a dedupe (qualify row_number()…) so unique tests survive daily re-listings.

<aside> ⌨️ Hands on: Open your team's stg_*.sql and find the read_files call. Note whether it appends /postings or relies on a full LANDING_PATH. Compare schemaHints keys to one sample JSON from the starter feed.

</aside>

How to model: stg → int → marts

Use dbt layer names (Week 10): staging, optional intermediate, then marts.

Keep or rename. Prefer keeping model names stg_postings / fct_postings while you change columns, so publish defaults stay stable. Rename only when the domain name matters for the backend (for example jobs). If you rename, update publish in the same change.

When you leave the starter feed, update models in the same change set as ingest:

1. Point read_files at the right folder; refresh schemaHints and rename CTE.
2. Update staging .yml tests (unique keys, not_null on the grain).
3. Point fct_* (and any int_*) at the new staging columns. Drop starter-only enrichment you do not need; keep or replace fct_postings_enriched if publish still reads it.
4. Update mart .yml tests on the keys the backend will query (dbt Tests).
5. Run dbt build and open the mart in Catalog Explorer before you call Path A done.

Enrichment (LLM or domain rules) is optional and lives as a dbt Python model when you need it: LLM enrichment.

<aside> ❗ Thin ingestion, rich dbt. Do not move joins and cleaning into Pandas in the ingest package unless a teacher agrees. SQL in dbt is easier to test and review.

</aside>

Ready for the next chapter when

Publish to Postgres assumes you know which mart name publish will read.

• [ ] You can point at data/dbt/models/{staging,intermediate,marts} and say what each owns
• [ ] You can set LANDING_PATH and DBT_SCHEMA for a personal run
• [ ] You can explain read_files + schemaHints vs cleaned column names
• [ ] You know dev_<you>, dev_airflow, and analytics are different writers
• [ ] You can list the staging and mart files you would edit for a source swap

Extra reading

• dbt: the build command
• dbt: how we structure projects
• Week 13: dbt on Databricks

Next up: Publish to Postgres, then Running the Pipeline Locally.