dbt Setup for Azure PostgreSQL
Career relevance: Week 10 in the NL data job market
History of Analytics Engineering
Going Further: Optional Deep Dives
In dbt Tests you attached tests to your models so that the producer of the data (you) can trust what leaves the pipeline. This chapter is about the other side of that contract: the consumers: analysts, dashboard builders, the next person on the team: who need to find your mart, understand what it means, and decide whether they can trust it.
Tests answer "are the numbers right?" Documentation answers "what does this column mean?" and "where did it come from?" Without either, a dashboard that says "revenue" is a guess. With both, it's a claim that can be audited.
This chapter reuses the _stg_trips.yml, _stg_zones.yml, and _fct_trips.yml files you wrote in dbt Tests: you already have the skeleton; you are adding descriptions to it. Then you generate and host a documentation site with lineage. After that, the chapter covers the package ecosystem (you already used dbt_utils in the previous chapter; this adds context).
By the end of this chapter, you should be able to:
description: fields to models and columns in your schema YAML files.dbt docs generate and dbt docs serve to host a documentation site and navigate its lineage graph.packages.yml + dbt deps.<aside>
📦 Reference repo: The end-state of this chapter (schema YAMLs with descriptions and a generated docs site) lives at tag ch6-docs-extras-solution in the reference repo. The optional snapshots deep-dive on the Going Further page has its own going-further branch, so this chapter's end-state stays snapshot-free. As with the other chapters: type it yourself first, consult the tag when something breaks.
</aside>
In the four built-in generic tests every column in your _stg_trips.yml already has a description: field: you wrote them when you added tests. What you have not done yet is generate the HTML site that turns those descriptions into something a non-dbt user can browse. The site is free: dbt builds it from the same YAML files you already maintain.
Two lessons from the previous chapter already set up the description workflow:
description: field on every model and column in the schema YAML is the primary documentation surface. Keep descriptions tight: what the column means, what its grain is, what its units are.Open _fct_trips.yml from the dbt Tests practice. Right now it probably has a one-line stub. Flesh it out:
# models/marts/_fct_trips.yml
version: 2
models:
- name: fct_trips
description: |
One row per completed NYC green taxi trip in January 2024, with
pickup/dropoff zone attributes folded in (OBT-style mart). Queried
directly by dashboards and ad-hoc analysis.
**Grain:** one row per trip.
**Source:** `nyc_taxi.raw_trips` joined to `nyc_taxi.raw_zones` on
`pickup_location_id` and `dropoff_location_id`.
**Not included:** trips where `pickup_location_id` is NULL (dropped
in `stg_trips`); duplicate rows from the TLC source are kept as-is
and surfaced by `dbt_utils.unique_combination_of_columns`.
columns:
- name: pickup_datetime
description: Wall-clock time the trip began (America/New_York, no timezone attached).
tests: [not_null]
- name: fare_amount
description: Metered fare in USD, not including tip, tolls, or surcharges.
- name: tip_pct
description: |
`tip_amount / fare_amount`, rounded to 4 decimals. NULL when
`fare_amount` is 0 (voided trips, no-charge rides).
- name: pickup_borough
description: |
NYC borough of the pickup zone, joined from `stg_zones.borough`.
Values: Manhattan, Brooklyn, Queens, Bronx, Staten Island, EWR,
Unknown, NaN, or NULL when `pickup_location_id` did not resolve.
The | (YAML literal block) lets a description span multiple lines and keeps newlines as written: useful for anything longer than a sentence.
<aside>
💡 What's mandatory vs. convention. The only parts dbt actually parses are the YAML keys: version, models, name, description, columns, tests. Everything inside a description: value is free-form Markdown: dbt dumps it straight into the docs site without interpreting it. The **Grain:**, **Source:**, **Not included:** labels above are a house convention for making mart docs scannable, not dbt syntax. Use them, replace them with your own headings, or skip them entirely; dbt does not care. What matters is that every mart column has some description that helps the next reader.
</aside>
Rule of thumb: document every column in every mart. Staging and intermediate models can be sparser: they are an implementation detail: but a mart is a contract, and each column is a public API.
<aside>
💡 Using AI to help: Writing good descriptions for 30 columns by hand is tedious. A practical use of an LLM here is: paste your model's select list and the underlying source's column semantics, ask the model to draft one-sentence descriptions, then edit for accuracy. ⚠️ Ensure no PII or sensitive company data is included: for this week's public NYC TLC dataset that is not a concern, but on a real project always scrub customer identifiers, business metrics, or internal URLs before sending columns to a model.
</aside>
When a description grows past a couple of sentences, or when the same text needs to live next to five columns, move it into a doc block: a named chunk of Markdown in a .md file under models/.
<!-- models/marts/fct_trips_docs.md -->
{% docs trip_grain %}
One row per completed taxi trip. "Completed" means the TLC submitted the
trip record to the public dataset; cancellations and trips in progress
are not included. Duplicates exist in the source data (roughly 4 rows
in January 2024) and are kept as-is; see `dbt_utils.unique_combination_of_columns`
test results.
{% enddocs %}
Then reference it from YAML with the {{ doc(...) }} function:
- name: fct_trips
description: '{{ doc("trip_grain") }}'
Rule of thumb: reach for doc blocks when the description runs 3+ lines, contains lists or formatting, or would otherwise be duplicated across models.
dbt docs generate and dbt docs serveTwo commands turn the YAML into a browsable site:
dbt docs generate
This reads every schema YAML, introspects your warehouse for column types and row counts (via a SELECT ... FROM information_schema query per model), and writes three files into target/:
target/manifest.json: the parsed project structure.target/catalog.json: the warehouse introspection results.target/index.html: the static HTML site that reads the two JSON files.<aside>
🤓 Curious Geek: why information_schema works on every warehouse
The reason dbt docs generate can introspect Postgres, BigQuery, Snowflake, and Redshift at all is that information_schema is part of the SQL standard (SQL-92). Every compliant database exposes comparable read-only views (information_schema.columns, information_schema.tables, ...) describing its own structure. Each dbt adapter still ships its own introspection macro (Postgres also reads pg_catalog, BigQuery scopes it per dataset), but they all lean on that shared standard, which is why one dbt docs generate command pulls column types and row counts from whatever warehouse you point it at.
</aside>
Then:
dbt docs serve --port 8001
Starts a local web server at http://localhost:8001. The page you land on lists every model, source, and test in the project. Click a model to see its description, columns, tests, and compiled SQL. In the bottom-right corner there is a blue "view lineage graph" button: click it.
dbt docs: fct_trips model page showing description, details, and column table
The fct_trips page above is what your _fct_trips.yml description and column descriptions render into: Markdown in the description, the column types pulled from Postgres, the attached tests surfaced inline. Every bit of prose a stakeholder reads here came from a YAML field you maintain.
<aside>
💡 If port 8001 is already busy (a previous dbt docs serve you forgot to stop), pick any other port (--port 8002). The default port 8080 collides with many other tools, which is why the dbt docs recommend choosing one explicitly.
</aside>
The lineage graph shows every model as a node and every ref() / source() call as an edge. For the nyc_taxi project you will see four nodes and four edges: the two raw sources feeding two staging models, both feeding the mart:
dbt docs lineage graph showing raw_trips and raw_zones feeding stg_trips and stg_zones, which both feed fct_trips
Source nodes are green, staging models are cyan, marts are purple. Click fct_trips in the graph and its upstream dependencies stay visible while everything else fades; do the same on a stg_* model and you see which marts depend on it. On a real project with 50+ models, this is the single most useful navigation tool dbt ships: before you change a column in stg_trips, the lineage graph tells you which marts will need review.
<aside>
💡 The docs site as a team communication tool. In a real team the docs site is hosted somewhere anyone can reach it (often as static HTML in Azure Blob Storage / Azure Static Web Apps, Cloudflare Pages, or GitHub Pages, regenerated on every merge to main). When a stakeholder asks "where does revenue come from?", you send them a link to the mart's page in the docs site instead of explaining it in Slack. The docs site is how dbt turns an internal engineering tool into a cross-functional one. Setting up hosted docs is outside this course's scope; see the dbt docs' Hosting and serving guide when you get to it on your own project.
</aside>
You already used a package in Putting it all together: dbt_utils.unique_combination_of_columns shipped the multi-column uniqueness test your project would have had to implement by hand. Packages are how the dbt community collects reusable macros, tests, and materializations without every project writing the same code.
The packages.yml file you wrote in the previous chapter already has the pattern:
# packages.yml
packages:
- package: dbt-labs/dbt_utils
version: [">=1.1.0", "<2.0.0"]
dbt deps reads the file, resolves versions, and installs each package into dbt_packages/. The macros and tests inside become available under the package's namespace: dbt_utils.generate_surrogate_key(...), dbt_utils.unique_combination_of_columns, and so on.
Beyond dbt_utils, the dbt packages hub is the discovery surface for anything else. When you meet a dbt pattern that feels like it should already exist (pivot tables, JSON parsing, better assertion libraries, calendar-table macros), check the hub before writing it yourself. The Going Further page lists the two community packages most projects reach for after dbt_utils.
<aside>
⚠️ Every package you add is code you did not write running against your warehouse. Pin versions (as above), read the package's README for what permissions it needs, and keep an eye on release notes for breaking changes. dbt deps does not audit what it installs.
</aside>
Four steps that turn the schema YAML you already have into a browsable docs site.
_fct_trips.ymlOpen models/marts/_fct_trips.yml. The Chapter 5 practice left it as a two-line stub with just a not_null test on pickup_datetime. Replace it with the fuller example from the Column and model descriptions section above: a multi-paragraph model description: using the | literal block, and a description: on every column in the fct_trips select (pickup_datetime, dropoff_datetime, fare_amount, tip_pct, fare_per_mile, payment_type_label, pickup_borough, pickup_zone, dropoff_borough, dropoff_zone).
Keep each column description to one or two sentences. The goal is to give an analyst enough to decide whether the column is what they want, not to re-derive the SQL.
dbt docs generate
Found 3 models, 10 data tests, 2 sources, 581 macros, 1 unit test
Concurrency: 4 threads (target='dev')
Building catalog
Catalog written to target/catalog.json
The Building catalog step is dbt introspecting the warehouse: one information_schema query per model: to capture the column types and row counts the docs site displays. If you see a warning about "relations not found in the catalog," run dbt run --select +fct_trips first: dbt cannot introspect columns in tables that do not exist yet.
dbt docs serve --port 8001
Open http://localhost:8001. In the left sidebar, expand nyc_taxi > models > marts > fct_trips. The center panel shows the description you just wrote, the column list with their types inferred from Postgres, the tests attached to each column, and the compiled SQL.
Click the blue view lineage graph button in the bottom-right. Press ctrl/cmd + C to stop the server when you are done.
cat target/catalog.json | python3 -m json.tool | head -40
Notice that dbt has recorded every column's Postgres type and every model's row count. This is the introspection step that makes the docs site richer than just your YAML: it pulls live metadata from the warehouse.
<aside>
⌨️ Stretch goal: add a doc block at models/marts/fct_trips_docs.md with a longer explanation of the mart's grain (using the {% docs trip_grain %} / {% enddocs %} syntax from the Doc blocks section) and reference it from _fct_trips.yml with {{ doc("trip_grain") }}. Regenerate the docs site and confirm the block renders as formatted Markdown in the fct_trips page.
</aside>
Once the docs site builds cleanly and the lineage graph renders, you have completed the chapter's core workflow. The practice chapter is where you repeat it until it feels routine.
<aside> 📝 Practice: The exercises in this week's practice extend the documentation work from this chapter onto a slightly different slice of the data. Finish the hands-on above before attempting them.
</aside>
<aside> 🚀 Try it in the widget: Interactive Quiz: Docs & Extras
</aside>
https://lasse.be/simple-hyf-teach-widget/mcq.html?bank=week_10_ch6_docs_extras_quiz&embed=1
fct_trips directly in the YAML file. A teammate complains that the YAML is getting unreadable. What is the dbt feature designed for this case, and how do you use it?dbt docs generate fails with "relation not found." Your model SQL is correct. What command do you run first, and why does the docs build need it?{{ doc("trip_grain") }} in a schema YAML. Where does dbt look for that text, and how does it differ from writing the description inline?Chapter 7 is the week's practice exercise: you will combine everything from Chapters 2-6 on a slightly larger slice of the same dataset. Before you start, confirm the basics are in hand:
dbt docs generate produced target/catalog.json, target/manifest.json, and target/index.html without errors.dbt docs serve rendered the site and the lineage graph showed raw_trips and raw_zones feeding stg_trips / stg_zones, both feeding fct_trips.view vs table for a model.packages.yml is in your project and dbt deps has installed dbt_utils into dbt_packages/: you do not need any new packages for the practice, but the file should already exist from Chapter 5.description: field to a column in _fct_trips.yml that goes beyond restating the column name.dbt docs generate, and the lineage graph.<aside> 🎬 Struggling with this concept? Watch this beginner-friendly video:
</aside>
https://www.youtube.com/watch?v=UqoWyMjcqrA
If the chapter leaves you curious about adjacent tooling, the Going Further page is where the optional material lives.
<aside>
📚 For deeper dives: the optional snapshots deep-dive (SCD Type 2 row history), the dbt Power User VS Code extension, and incremental-model references: see the Going Further page.
</aside>
And when something in your own project does not match what the chapter describes, the reference repo has the end-state to diff against:
<aside>
📦 Reference repo: Type this chapter yourself, then practice from a clean base with git switch ch6-docs-and-extras in your clone (it starts from Chapter 5 finished). Stuck? Diff your project against the ch6-docs-extras-solution tag.
</aside>
Next up: Practice, where you apply the full Week 10 workflow (sources, staging models, macros, a mart, tests, and docs) in a hands-on exercise until the pipeline feels routine.