Three dbt Features I Didn't Appreciate Until They Drove the Hot Path

My poller started polling from a list that looked identical to the one I used to type by hand. The list was right. The fact that I hadn’t written it was awesome.

My fleet poller reads a CSV (data/projectors.csv) to know what to poll. For a while, that CSV was something I edited by hand. Add a projector, type a row. Move a device, edit a row. The poller picked up the changes on its next reload, no questions asked.

There were three different CSVs in the picture by the time the dbt-driven version went live. The per-site seed CSVs in seeds/ are the human-curated IP Manifest: sixteen of them, one per site, edited by people in spreadsheets. The mart is what dbt builds from those seeds plus live PJLink data. The exported CSV is what scripts/export_polling_list.py writes after dbt runs, in the format the poller expects. Different files at different ends of the chain.

The change wasn’t visible. The list looked the same. The mechanism behind it had changed.

dbt isn’t just describing the pipeline. It’s driving the poller’s next cycle.

This is what my dbt project looked like three months ago:

• A few staging models, a couple of marts.

• Tests on the obvious places (unique on the inventory PK, not_null on IP).

• Ran on a cron. Grafana refreshed. Data looked clean.

• I thought I knew dbt.

What I knew was the surface. ref, source, run, test. What I'd missed: dbt isn't just a query tool plus a scheduler. It's a contract layer. A way of saying "this depends on that, and here's what good looks like." Tables are how the contract gets honored. Tests are how it gets checked.

Three features clicked for me once I understood that. Each one looks small in isolation. Together they’re the difference between dbt produces my reports and dbt drives the poller’s targeting.

My data pipeline project runs dbt on Postgres rather than a warehouse like Snowflake or BigQuery. The patterns apply.

The popular mental model is that views are cheap, tables are durable, and incremental is fancy. Pick view unless something’s slow.

It’s the dbt default. The dbt docs spell it out: if you don’t specify a materialization, you get a view. The dbt Labs project structure guide reinforces it for the next layer up: staging models as views, marts as tables. There’s a reason that recommendation is everywhere. For most projects, it’s the right starting point.

For those that haven’t met all of them, dbt has four built-in materializations:

• view: no data persisted; the SQL runs every time the view is queried.

• table: a table dropped and rebuilt on each dbt run.

• incremental: a table that only appends or updates rows matching a where clause since the last run, useful for big append-only sources.

• ephemeral: compiled into downstream models as a CTE, never persisted.

There are more via packages and custom code, but those four are the working set most projects live in.

Here’s an insight that hit me. A materialization is a contract about who pays the cost of recompute.

View: reader pays. Every query re-runs the SQL.

Table: build pays. Full rebuild every dbt run.

Incremental: logic pays. You write the “since last run” predicate, and the cost moves into the SQL you have to maintain.

This lens changes how you see your own project.

It clicked when I noticed my marts split into two operational profiles. mart_polling_candidates and mart_pjlink_work_orders are read by scripts once per refresh — queried after make refresh_all, then idle until the next cycle. mart_projector_status and mart_site_summary are read by Grafana on every panel refresh.

Both are tables, for different reasons. The operational marts need a stable artifact while scripts run against them; the presentational marts are queried hard enough by Grafana that view-recompute would be expensive at that frequency.

Staging models stay view. They’re a thin reshaping layer between source and mart, doing nothing more than renaming columns, casting types, and basic cleaning. Persisting them as tables would duplicate data without adding much, and the dbt graph still tracks the dependency chain either way. Lineage is independent of materialization choice.

Here’s the operational mart, opened with an explicit materialization config:

And here's a staging model where view is doing its job:

Each model’s materialization became a deliberate read on who pays the cost of recompute. The lineage was always there. The materialization is now its operational counterpart. Who reads this, how often, what do they need? Made into a config line.

Pick the materialization that fits the read pattern. The default is a starting point, not a verdict.

Commonly, source for raw inputs, ref for everything dbt builds. Both compile to table references. The choice feels mechanical.

Reframed, source declares what dbt doesn't own. ref declares what it does. The discipline isn't just syntactic. It's a contract about ownership and a generator of operational lineage.

The discipline showed up while I was building mart_polling_candidates — the mart in the middle of the seed → mart → exported-CSV chain.

dbt's job is to bridge. When a device gets added to a seed CSV, make refresh_all walks the lineage seed CSV → stg_ip_manifest → mart_polling_candidates → exported CSV → next poll cycle. That lineage walk only exists because every model in the chain refs the model upstream of it, and the seed/source declarations sit at the boundary of what dbt owns versus what it reads.

Source declaration first. The raw tables the poller writes to are declared once in YAML, with descriptions and column-level tests:

Then everything downstream uses source() to read from these, and ref() to read from each other:

The compiled SQL would still work if you typed the table names directly. What you’d lose is the graph. The thing that lets dbt run --select +mart_polling_candidates+ know which models have to rebuild, and in what order.

I moved away from writing more glue scripts. The “glue” is the dbt graph itself. dbt list --resource-type model --select +mart_polling_candidates becomes my deploy plan, not just a debugging command.

Let the graph be your glue.

Leave a comment

The starter set: unique, not_null, accepted_values, relationships. Drop them in a YAML file. Done.

Generic tests cover something like 70% of what you need. The other 30% is assertions with shape. Invariants that hold across rows, joins, or conditional logic. That’s where singular tests live, and that’s where most of the bugs that hurt also live.

Invariants are properties that should always hold true. Generic tests handle column-level type properties (this column is unique, this one is not null, these are the only allowed values). Singular tests handle structural properties (this row’s existence depends on conditions across other tables, this aggregate matches that one, this set of rows partitions without overlap).

A realization hit me when I needed to assert that the IP addresses landing in mart_polling_candidates were always bare IPs, never CIDR notation like 10.20.91.31/32. Some of the source rows had CIDR, some didn’t, depending on the site that maintained them. The mart’s job included normalizing them with split_part(). The assertion I wanted: no row in the final mart should ever contain a slash. Not expressible as unique, not_null, or any combo of the standard generics.

A singular test is a SELECT that returns rows when the assertion fails. Three lines of SQL; assertion lives in the build:

The generic-test side of the same model handles the simpler invariants:

That YAML catches if a seventh status value snuck in. It doesn’t catch the CIDR case, because CIDR isn’t a finite set of accepted values. It’s a shape that the column should never have. That’s the singular test’s job.

Assertions that used to be “I’ll check it manually after a refresh” became “the build fails if this drifts.” Bug-shaped concerns moved out of code review and into CI.

Singular tests are as efficient as the SQL you write. No more, no less. A generic unique test compiles to dbt-optimized SQL that’s been battle-tested across thousands of projects. A singular test runs the SQL you typed, as written. If you write an unfiltered SELECT against a billion-row table, that’s a billion-row scan every build. They also don’t compose; you can’t reuse a singular test across models the way you can stamp unique: true in a YAML and have it apply to fifteen columns.

So they’re a cool tool for a specific job (assertions with shape). Write them like production SQL: predicate-narrowed, sometimes sampled, always reviewed for scan cost.

What dbt is, for me, after these three lessons: a contract layer that makes data movement legible. Not just a query tool. Not just a scheduler. A way of saying "this depends on that, and here's what good looks like."

Once I understood that, every dbt feature took on weight. Materializations are contract terms about cost. ref is contract enforcement. Tests are contract violations made visible.

If you’ve read my piece on layering my pipeline with the medallion architecture, the operational-marts twist I wrote about there makes more sense once you see dbt as the contract layer underneath. Medallion gives you the layering vocabulary. dbt gives you the way to enforce that the layers mean what they say.

dbt did more than enforce my expectations of the data. It reshaped them. After those expectations became writable, I started defining invariants I would have ignored before.

What’s the tool you thought you knew well until the system you built with it asked something new of it? Mine took me three months to fully see.