Optional: Enriching with an LLM
Every final-project team asks roughly the same questions in the first two weeks, usually in the same order. This page collects them with short answers, so you get unblocked in a minute instead of waiting for the next mentor session.
The answers here are summaries. Where a chapter already explains the reasoning in full, the answer links to it rather than repeating it.
Not first. Your application has almost no real users during the project, so a popularity score built on it is a score over your own team's clicking. That is a lot of moving parts for a number nobody can trust.
Ask the backend for the app_events table in week one anyway, because it only records what happens after it exists. You do not have to model it until outbound publish works in prod. See Optional: Inbound Sync.
A ranking based on your external data, for example "events near you this weekend, cheapest first", needs no usage data at all and is worth more in a demo.
One source, landed raw, cleaned in dbt, published to the analytics schema, triggered by one Airflow DAG. Nothing else.
Build the chain before you build the features. A DAG whose four tasks all turn green on dummy data is more useful in week two than a beautiful dbt model that nobody has scheduled, because the chain is where the surprises are.
<aside> 💡 A useful test for any extra feature: if your source disappeared tomorrow, would this feature still be worth having? If the answer is no, it belongs after the pipeline works, not before.
</aside>
Usually yes, and that is a real answer rather than a way of avoiding work.
Your pipeline is a daily batch. It cannot deliver anything in under a second, so a small, fast API that needs no cleaning gains nothing by passing through it. If the backend calls that API when it renders a page, the user gets fresher data than you could ever hand them.
Route it through your pipeline when at least one of these is true: the source needs real cleaning, you need to join it to your models, or you need its history rather than its current value. Yesterday's weather at an event location is a data problem. Today's weather in the sidebar is not.
One per source. Keep them separate through raw and staging, then join them into the mart.
Combining at ingestion means writing schema logic in Python, before you know what the data looks like, and you lose the ability to see what any single source actually sent you. Combining in dbt means the join is SQL, tested, and easy to change when the fourth source arrives.
Add a source column to the mart so the application can filter by it. Fields that only one source provides are simply null for the others: say so in the mart contract, so the backend is not surprised by a column that is empty two thirds of the time.
Start with one wide mart (jobs; the starter uses fct_postings). Add a second published table only when a different grain or access pattern needs it, not because you built extra tables in dbt.
| Backend need | What to publish |
|---|---|
| Job list, detail, filters on job fields | jobs only |
| Skill search or overlap ranking | job_skills at job × skill grain |
| Homepage KPIs or filter counts | stats_by_skill / jobs_by_city or backend scheduled cache on jobs |
Think of each published mart as a read-only product API for the backend: documented grain, columns, and refresh. They should answer most screens with one SELECT from jobs, not by joining five tables.
<aside>
⌨️ Hands on: Take the screen your frontend teammates sketched and write the single query that fills it. If that query needs joins across companies, skills, and jobs, fold those attributes into the wide mart instead.
</aside>
Full reasoning and diagrams: Cross-Track Collaboration: Mart shape.
dim_*, fct_*, bridge_*) in dbt?No. Week 10 already builds OBT marts without dim_* models. This project has an API, not a reporting tool, so you do not need to store a star. Use staging and int_* for grain, keys, and reused SQL. Put only published tables in marts, named after the entity (jobs, not fct_jobs).
dbt and Airflow keep build order correct. They do not write the backend's join logic per endpoint. dbt tests stop bad rows in your pipeline; they do not stop a list endpoint from joining job_skills and returning the same job eight times.
For job skills, pick based on how the backend reads them:
| Pattern | When to use |
|---|---|
skills text[] on jobs |
Detail pages show skills on the job card; skill search is not core |
job_skills |
Skill search, filters, or overlap ranking are core product features |
| JSON column | Small nested blobs always read with the parent row |
| View | Convenient slice over tables you already published; not a substitute for a mart contract |
A view does not replace a mart: it has no tests, no atomic swap, and no documented refresh of its own.
They can, but we do not recommend it for the capstone. Copying jobs into normalized app tables means the backend owns duplicate data, a second refresh schedule, and join logic you already solved in dbt, with no meaningful win at project scale.
Prefer querying analytics directly, adding indexes, or caching slow aggregates on a schedule.
Not every KPI needs its own mart. Default: the backend aggregates from jobs (group by city or role) and caches on a schedule if the UI hits it every page load.
Publish a summary mart when the number is a product promise you want tested in dbt, or when the SQL is awkward on jobs (averages per skill). One grain per table (jobs_by_city, stats_by_skill). Do not name skill-level stats job_skills (that is the job × skill search table). Do not mix city, skill, and role in one job_statistics table.
You may copy a city or role rollup onto each jobs row. Do not put "the" skill average on a listing that has several skills.
Either way, do not run full-table aggregates on every HTTP request. See Cross-Track Collaboration: Mart shape.
Adding, yes. Renaming, removing or changing a type, no.
Extra columns are harmless, because nothing in the backend selects a column it does not know about. Everything else breaks their code, and a type change breaks it quietly, which is worse. The full table of what is safe to do alone is in Cross-Track Collaboration.
If you find an interesting field while cleaning, publish it. The worst case is that nobody uses it.
In dbt, as a rule. That keeps the raw layer a faithful record of what the source sent, which is what lets you prove a bug is theirs and not yours.
Ingestion still has a job here: if the source gives you a stable id and lets you ask for records changed since yesterday, use it. Not fetching a record is cheaper than fetching it and throwing it away every night for five weeks. That is avoiding duplicates, which is different from deduplicating them.
You do not, because it is not a duplicate.
A duplicate is the same row arriving twice from the same source. It has the same id, and a qualify row_number() over (partition by id order by loaded_at desc) removes it. That is a solved problem.
Two rows describing the same real-world thing, coming from two different systems, is a different problem with a different name: entity resolution, also called record linkage. There is no shared id. You are comparing a title, a date, a venue and a price, deciding how similar is similar enough, and choosing which version wins. It is fuzzy, it is judgement, and it is a project on its own.
For the final project, the answer is ownership rather than technique. You publish external records, the backend owns what its admins create, and matching the two happens in the application, where somebody can look at a screen and decide. Give the backend the fields that make matching possible (title, start time, venue, and a stable source id) and let them decide what to show.
<aside> ⚠️ Do not quietly build fuzzy matching into your mart because it sounds impressive. A matcher that is 90% right merges two different concerts one time in ten, and nobody notices until a user does.
</aside>
None of this is new, and the people who first had the problem were not working with databases.
<aside> 🤓 Curious Geek: matching records is older than databases
</aside>
You do. You own one schema in their database, you are the only one who writes to it, and if the backend says a column is missing or a type is wrong, fixing it is your job.
You do not own anything else in that database. Their users, sessions and application tables are theirs, and you should not have permission to touch them. The role split that enforces this is in Cross-Track Collaboration.
No. If it is not data you ingested or modelled, it is not yours. Authentication, permissions and anything the application writes belong to the backend.
This sounds obvious written down, and teams still drift into it in week three, usually because one data trainee is faster than the backend and starts helping. Helping is fine. Owning their tables is not, because two teams changing the same schema is how a migration silently drops something your sync recreates every night.
Once a day, in the same DAG run, until you have a reason to go faster.
Real-time sync means a job every minute, or change detection on their tables, and both are real work for a product with no users yet. Nobody is refreshing your app at 3am waiting for a number to update.
Start manual, then daily, then hourly if a feature genuinely needs it. Every step up in frequency costs more than the last.
Together through prod on one source, then split by tool, not by layer. The canonical plan is in Team Timelines.
Weeks 1 to 3 (all DEs together):