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

Optional: Enriching with an LLM

This chapter is optional. Nothing here is required for Week 15's deliverables or the assignment. Build it only when your required pipeline runs green in production and your team wants to go further.

<aside> 📝 LiteLLM (this cohort): The class uses a shared LiteLLM gateway (not OpenRouter). Each team has its own budgeted virtual key. Iterate on prompts in a local notebook, then enable the dbt Python model on Databricks. Deep setup lives in your repo under data/optional/llm_classify/ and data/optional/python_model/.

</aside>

Your starter repo already classifies job titles into disciplines. fct_postings_enriched.py does it with a dictionary of keywords: "data engineer" means data, "kubernetes" means devops. That model is required and it works.

This chapter swaps the rules for a language model. The interesting part is what stays the same. Both production paths are dbt Python models on serverless compute, so the difference is the rules, not the plumbing. What changes for development is a laptop notebook, so you are not paying serverless minutes while you argue about labels.

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

• Say why the classifier is a dbt model rather than another container
• Explain the three design decisions that make an LLM step affordable
• Iterate locally in the notebook, then port the prompt into the dbt model
• Pick a LiteLLM mode (cheap / medium / strong) and check spend against the daily budget
• Judge whether your team should build this at all

Why a dbt model, not a container

You have built containers all track, so a container is the obvious reflex. It is the wrong one here, and the reason is dependency order.

dbt already knows this table depends on fct_postings. That single fact buys you three things:

• dbt build runs it in the right order, so it cannot read yesterday's mart
• ref() resolves it like any other model
• You can put tests on its output, in the same .yml as everything else

A separate container would need its own trigger, its own wait, and its own answer to "did the thing I read finish building?". You would be rebuilding dbt's dependency graph in Airflow, by hand, for one model.

What you give up is speed while developing. A serverless run takes one to two minutes (often two to three with LLM batches), against a few seconds for a local notebook cell. That is fine daily and annoying while iterating. It is exactly why prompt work starts in optional/llm_classify/, not in dbt build.

<aside> 🤓 Curious Geek: Python models are newer than you think

dbt was SQL-only for its first six years. Python models arrived in 2022, once warehouses could run Python next to the data instead of shipping the data to Python. That ordering is why dbt.ref() in a Python model returns a DataFrame that never left the warehouse.

</aside>

Two runtimes, one prompt

You cannot run the dbt Python model as local Python. Databricks always executes it on serverless. So the repo ships two places that share a prompt contract, not an import:

Serverless does not ship your optional/ package, so do not try to import the notebook helpers from models/. Copy the PROMPT string (and categories in that prompt) by hand when labels look good. Full file map and VS Code kernel steps: data/optional/llm_classify/README.md.

The three decisions that make it affordable

Read these before you enable the dbt model, because getting any of them wrong turns a rounding error into a bill worth explaining.

Drop any of the three and you are calling an API once per posting per day. On the sample source that is cents. On a real one, it is the difference between nothing and a number your teacher asks about.

• 🎵 Supermode - Tell Me Why: why does the code force an unknown label to other instead of storing what the model said?

What is deterministic and what is not

A dictionary gives the same answer forever. A language model does not. That has a real consequence: accepted_values can fail on a day when your data did not change at all.

Two things in the shipped code keep that in check:

• temperature: 0 set on every request, so two runs over the same titles agree
• A fixed list of disciplines, with anything outside it forced to other

<aside> ⚠️ Check that list against the dictionary model before you switch. The two must name the same disciplines, or the ones only the dictionary knows about quietly become other: accepted_values allows other, so nothing fails and the category simply stops appearing in your app.

</aside>

Pin the model alias too: cheap, medium, or strong. Those names stay stable; the Azure deployment behind them is the teacher's problem.

The LiteLLM gateway (shared)

LiteLLM is an OpenAI-compatible proxy in front of Azure OpenAI. All final-project teams share one gateway. Both the notebook and the dbt model post JSON to /v1/chat/completions. What differs per team is only the virtual key (and therefore the daily spend budget).

Three modes

Pick one with +llm_model: in dbt_project.yml (and the same id in the notebook). Start on cheap. Use strong only on tiny samples when judging label quality: it burns the daily budget faster. medium is the same cost tier as cheap in this pilot; treat it like cheap today. The Azure deployment behind each alias is the teacher's problem.

Daily budget and rate limits

Each team's virtual key is capped separately. Crossing the budget returns an error until the reset time (UTC midnight for this pilot).

That is why the three design decisions above still matter. Batching and incremental runs keep you under rpm/tpm. Choosing cheap keeps spend under $5.

Two habits keep you out of trouble:

Run the first Databricks backfill by hand, once. The scheduled task retries, and a failed incremental run writes nothing. So every batch is paid for again on the retry. Run it yourself and watch it finish. Let the schedule pick up from there, when there are only a few new titles a day.

Read the error before changing anything. A budget or rate-limit refusal names the cause. If it says the budget is spent, wait for reset or ask your teacher. Do not edit the code to "try harder".

Costs for the team

Check remaining budget with the team key (/key/info). Ask your teacher for the key if it is not already in your shell: it is the same material as Key Vault litellm-key-team-<letter> / Databricks litellm-api-key. Full request logs and the Admin UI need the teacher master key (litellm-master-key-team-d; Optional matrix).

curl -sS \
  -H "Authorization: Bearer $LITELLM_TEAM_KEY" \
  "https://app-litellm-team-d.blacksky-9263d113.westeurope.azurecontainerapps.io/key/info" \
  | python3 -m json.tool

Read info.spend, info.max_budget, and info.budget_reset_at.

<aside> 💡 First call after idle can take about a minute (sometimes longer): the Container App scales to zero, then boots Postgres + LiteLLM. Default HTTP timeout in the repo helpers is generous (~300s). Retry once before debugging your prompt.

</aside>

Walkthrough: notebook first, then dbt

Mechanical detail (VS Code kernel, uv sync --extra notebook, cold starts) lives in data/optional/llm_classify/README.md. Enabling and joining the dbt model: data/optional/python_model/README.md. This section is the ordered path.

Step 1: Confirm the dictionary path still works

Do not touch the LLM path until fct_postings_enriched builds green. The LLM table is optional enrichment on top of a working mart.

Step 2: Install extras and open the notebook

Install everything this walkthrough needs in one go. uv sync --extra notebook alone drops pytest and dbt from the venv, so the later steps would fail.

cd data
uv sync --extra notebook --extra dbt --extra dev
az login   # HYF tenant, so Key Vault can supply the LiteLLM key

Open data/optional/llm_classify/llm_classify_dev.ipynb in VS Code. Select the data/.venv kernel (see the llm_classify README if it is missing).

You do not paste the LiteLLM key into .env for this path. The notebook loads Key Vault litellm-key-team-<letter> via Azure CLI (or LITELLM_API_KEY if a teacher gave you that override). The letter comes from DATABRICKS_CATALOG in .env (team_c → litellm-key-team-c). Run az login against the HYF tenant (default profile ~/.azure). If Key Vault still fails after that, ask your teacher, or set LITELLM_API_KEY for this session.

Databricks settings in data/.env are still needed so the notebook can run sample SQL (DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_TOKEN, DATABRICKS_CATALOG, DBT_SCHEMA).

Step 3: Iterate on a small sample

In the notebook:

1. Run the SQL cell (sample titles; keep SAMPLE_N small at first). The shipped query reads team_<letter>.analytics.stg_postings; change the catalog if needed.
2. Optional: list_models() to see cheap / medium / strong. The first LiteLLM call after idle can take about a minute while the gateway wakes up.
3. Edit CATEGORIES, PROMPT (keep {numbered_items}), MODEL, BATCH_SIZE.
4. Run classify(...) and read the table out loud with a teammate.

That judgement is the point of this step. If you would not defend the labels, do not enable dbt yet.

Step 4: Copy the prompt into the dbt model

Paste the notebook PROMPT into the PROMPT constant in dbt/models/marts/fct_title_discipline.py. Keep {numbered_items} and the same category list. Do not copy llm_local.py into models/: the dbt file has its own thin chat helper for serverless.

Step 5: Run unit tests (no network, no spend)

cd data
uv run pytest tests/dbt/test_fct_title_discipline.py tests/optional/test_llm_local.py

These cover batching and parsing. They do not call LiteLLM. Needs the dev extra from Step 2 (pytest is not in the notebook-only sync).