dbt Packages: dbt_utils, dbt_expectations, dbt-codegen, dbt-osmosis — When Each Wins

dbt packages are the analytics-engineering force multiplier that separate a senior dbt team from a mid team writing the same surrogate_key, pivot, and date_spine macros for the tenth time. Every team eventually reaches the same wall — the internal macros/ folder swells to 40 hand-rolled helpers, half of them subtly wrong on Snowflake vs BigQuery, none of them tested, and any refactor risks a Monday-morning outage nobody wants to sign off on. The dbt_utils package (owned by dbt Labs, maintained continuously since 2016) ships those exact macros — tested, adapter-dispatched, version-pinned — for free. The engineering trade-off is not whether to reach for dbt community packages; it is which four packages you standardise on, how you pin them, and what you monitor when you upgrade.

This guide is the senior analytics-engineer walkthrough for the four packages that carry the bulk of a modern dbt project — dbt_utils for macros and cross-adapter helpers, dbt_expectations for Great-Expectations-style column and table tests, dbt-codegen for auto-generating sources.yml, model YAML, and base SQL from the warehouse, and dbt-osmosis for propagating column descriptions and tests through the DAG. It walks through the four "must-answer" interview axes (coverage, versioning, adapter support, dependency hygiene), the canonical packages.yml with Hub, Git, and local installs, the dbt macros package dispatch model that makes cross-warehouse code portable, and the upgrade blast-radius audit that catches breaking macro renames before they land in production. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why packages are the analytics-engineering force multiplier
• dbt_utils — the utility belt
• dbt_expectations — Great-Expectations-style asserts
• dbt-codegen + dbt-osmosis — reduce boilerplate
• Package management + versioning discipline
• Cheat sheet — dbt package recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why packages are the analytics-engineering force multiplier

Every dbt team writes the same 40 macros — packages let you stop paying that tax

The one-sentence invariant: every dbt team ships variations of the same surrogate_key, date_spine, pivot, deduplicate, expect_column_values_to_be_between, and generate_source helpers, and the "senior" answer to that pattern is not to write them better — it is to install dbt_utils, dbt_expectations, dbt-codegen, and dbt-osmosis, pin the versions, and spend the recovered engineering time on the domain model that actually makes the business money. The dbt package management layer is a Jinja + SQL analogue of npm or pip: packages.yml declares dependencies, dbt deps resolves them into dbt_packages/, and every downstream model can {{ dbt_utils.generate_surrogate_key(...) }} as if the macros were local. The engineering trade-off is which packages you standardise on and how disciplined you are about versioning — nothing else.

The four axes interviewers actually probe.

• Coverage. Which of the "40 recurring macros" does the package own? dbt_utils covers ~60 macros; dbt_expectations covers ~60 test macros; dbt-codegen covers ~10 code-generation macros; dbt-osmosis covers the doc-propagation and yaml-refactor loop. The senior signal is naming which macro lives in which package without a Google search.
• Versioning. Is your packages.yml pinned to a version range ([">=1.0.0", "<2.0.0"]) that admits patch fixes and blocks breaking-major upgrades? Loose pins (">=1.0.0") are how a Monday-morning dbt deps pulls a fresh major version and every generate_surrogate_key call across 400 models breaks silently.
• Adapter support. Does the package's macros work on Snowflake, BigQuery, Redshift, Postgres, and Databricks? dbt_utils and dbt_expectations use adapter.dispatch to route each macro to a per-adapter implementation. Senior teams verify adapter coverage before adopting a package; junior teams discover the gap in a broken CI run on Friday afternoon.
• Dependency hygiene. Does the package depend on other packages? dbt_expectations transitively depends on dbt_utils (which is why the two are almost always installed together). Every added package widens the dependency graph and multiplies the version-conflict surface. The senior answer is to prune to the four packages above and only add a fifth when the ROI is unambiguous.

Where dbt packages live in 2026.

• dbt Hub. The public registry at hub.getdbt.com. Packages published here are addressable as dbt-labs/dbt_utils with a version range; dbt deps pulls the exact matching git tag under the hood. Preferred install path for community packages — versions are canonical, upgrades are trackable, changelogs are linked.
• Git direct. Any git URL ([e-mail]:calogica/dbt-expectations.git) with a revision (branch, tag, or sha) can be installed. Used for packages not on Hub, for internal packages, or when you need to pin to an unreleased commit for a specific fix.
• Local. A relative path (packages: - local: ../shared-macros) installs an in-repo package. Used in monorepos to share macros across multiple dbt projects without publishing to Hub.
• Private registries. Enterprise teams sometimes proxy Hub through an internal registry; the syntax is identical, only the resolver endpoint changes.

What interviewers listen for.

• Do you say "install dbt_utils instead of rolling your own generate_surrogate_key" without prompting? — senior signal.
• Do you mention adapter.dispatch when asked how a package works on both Snowflake and BigQuery? — senior signal.
• Do you push back on "just write it inline" with the versioning, adapter-dispatch, and community-testing argument? — required answer.
• Do you describe package upgrades as a blast-radius decision, not a routine dbt deps? — required answer.

Why the four-package standard is the right default in 2026.

• Coverage economics. dbt_utils covers macros; dbt_expectations covers tests; dbt-codegen covers YAML/SQL scaffolding; dbt-osmosis covers doc propagation. The four together cover ~80% of the "recurring analytics-engineering tax" without overlap.
• dbt Labs sponsorship. dbt_utils and dbt-codegen are owned by dbt Labs, which means they track every dbt-core release within days and every adapter change within weeks. That is the highest reliability signal a community package can carry.
• Ecosystem depth. dbt_expectations (calogica) and dbt-osmosis (z3z1ma) are community-maintained but have hundreds of production users and public changelogs; their bus factor is well above the median community package.
• Adapter coverage. All four support Snowflake, BigQuery, Redshift, Postgres, and Databricks. Some macros are adapter-specific (BigQuery UDF wrappers, Snowflake time-travel helpers) but the core surface is universal.
• Pinning discipline. Each ships with a stable semver contract; the pin [">=1.0.0", "<2.0.0"] (or the equivalent for that package's current major) is a well-understood pattern.

Worked example — the "just write it inline" anti-pattern

Detailed explanation. A textbook anti-pattern that shows up in every code review. A junior analytics engineer needs a surrogate key. Instead of installing dbt_utils, they write the following inline in the model:

{{ md5(coalesce(cast(order_id as string), '') || '-' || coalesce(cast(user_id as string), '')) }}

This works on Postgres. It silently returns wrong hashes on BigQuery (which uses TO_HEX(MD5(...)) instead of just MD5), throws a syntax error on Redshift (which needs FUNC_SHA1 or a UDF), and produces different hashes on Snowflake vs Databricks because the string concatenation and null-coalescing semantics differ across adapters. The macro is silently wrong on 3 of 5 warehouses. Walk an interviewer through the failure mode and the correct approach.

• The symptom. The model runs green in dev (Postgres) and green-but-wrong in prod (Snowflake). Every JOIN on the surrogate key returns fewer rows than expected because the hash values silently differ.
• The reason. Adapter-specific SQL dialects. What md5(...) returns depends on the warehouse; the null-coalescing semantics of || differ; the string-cast of a timestamp is not identical across adapters.
• The correct fix. {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }} — a single macro that dispatches to a per-adapter implementation and produces the same hash across all supported warehouses.
• The wrong fix. Ship five copies of the inline macro, one guarded by {% if target.type == 'snowflake' %} for each adapter, hand-maintained forever.

Question. A team of 8 analytics engineers has 400 dbt models. 63 of those models compute surrogate keys inline (grepped md5(coalesce(...)) ... returns 63 hits with 7 subtly different implementations). The team is migrating from Redshift to Snowflake and CI is red on 41 models. Quantify the fix cost with and without dbt_utils.

Input.

Approach	Files to edit	Test surface	Adapter guarantee
Fix inline macros by hand	63 files, 7 variants	manual per-file	none — silent-wrong risk stays
Replace with dbt_utils.generate_surrogate_key	63 files, 1 pattern	package's CI	dbt_utils supports 5+ adapters

Code.

# packages.yml — the two-line fix
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

dbt deps
# Installing dbt-labs/dbt_utils
#   Installed from version 1.1.1
#   Up to date!

-- Before — hand-rolled, adapter-specific, silently wrong on 3/5 warehouses
select
    md5(coalesce(cast(order_id as string), '') || '-' || coalesce(cast(user_id as string), '')) as order_key,
    order_id,
    user_id,
    amount_usd
from {{ ref('stg_orders') }};

-- After — one macro, adapter-dispatched
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }} as order_key,
    order_id,
    user_id,
    amount_usd
from {{ ref('stg_orders') }};

Step-by-step explanation.

1. Install dbt_utils via packages.yml and run dbt deps. The package materialises under dbt_packages/dbt_utils/ and its ~60 macros become available under the dbt_utils namespace.
2. Replace every hand-rolled surrogate-key expression with {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }}. The macro's internal implementation uses adapter.dispatch — Snowflake gets md5(concat_ws('||', ...)), BigQuery gets to_hex(md5(concat(...))), Postgres gets md5(...). All return the same 32-character hex string for the same input tuple.
3. Because the macro is versioned, upgrading dbt-core or the warehouse adapter cannot silently change the hash function without a major-version bump on dbt_utils. Regression risk drops from "any team member's PR" to "explicit packages.yml version bump."
4. The test surface concentrates. Instead of 63 hand-rolled expressions with 7 variants, there is one package-level implementation with its own CI suite covering every supported adapter. The team's code review reduces from "is this variant correct?" to "is the package version pinned?"
5. The migration to Snowflake becomes a config change (target adapter switch) rather than a 63-file audit. Every model that used the macro produces the correct hash on the new warehouse the moment dbt deps re-resolves.

Output.

Metric	Hand-rolled	With dbt_utils
Model files touched during warehouse migration	63	0 (transparent)
Distinct implementations to audit	7	1
Adapter coverage	1 (accidentally)	5 (explicitly)
Time to fix red CI	3 senior-days	30 minutes
Silent-wrong risk after migration	high	low

Rule of thumb. Every hand-rolled md5(...) inside a dbt model is a latent adapter-portability bug. Install dbt_utils on day one of the project. The 30-second install saves a 3-day migration audit two years later.

Worked example — the "one package per macro" fragmentation trap

Detailed explanation. Another anti-pattern: a team hears about a niche macro package (e.g. dbt_project_evaluator, dbt_meta_testing, dbt_dataflow) and installs it for a single macro. Six months later packages.yml has 14 packages, three of them depend on incompatible dbt_utils version ranges, dbt deps fails to resolve, and no one remembers which macro came from which package. The fragmentation trap.

• The symptom. dbt deps errors with "Version conflict: dbt_utils required by A as >=0.9.0,<1.0.0 and by B as >=1.0.0,<2.0.0." No safe resolution short of removing one package.
• The root cause. Each package brings its own transitive dependency graph. Every added package widens the graph and multiplies the constraint surface.
• The fix. Prune to the four standard packages. Anything else must clear a "ROI ≥ cost of dependency management" bar; most niche packages fail this test.

Question. Design the package-selection policy that keeps packages.yml under 5 entries while giving analytics engineers the macro coverage they need.

Input.

Package	Purpose	Verdict
dbt-labs/dbt_utils	macros	keep — standard
calogica/dbt_expectations	tests	keep — standard
dbt-labs/codegen	scaffold	keep — standard
z3z1ma/dbt-osmosis	doc propagation	keep — standard
dbt-labs/dbt_project_evaluator	project-linting	keep only if CI-integrated
Anything else	niche	inline the ~10 lines of macro instead

Code.

# packages.yml — the disciplined four-package baseline
packages:
  # Macros: surrogate keys, date_spine, pivot, deduplicate, star, get_column_values
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

  # Tests: expect_column_values_to_be_between, expect_row_values_to_have_recent_data
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

  # Scaffolding: generate_source, generate_model_yaml, generate_base_model
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]

  # Doc propagation: dbt-osmosis is a CLI, not a hub package, but often referenced together
  # (installed via pip: pip install dbt-osmosis)

Step-by-step explanation.

1. The packages.yml above is the disciplined baseline: four entries, all with narrow version ranges, all covering distinct macro surfaces (macros / tests / scaffolding / docs). No overlap; no waste.
2. Anything not in these four packages must clear the ROI test: does it save more analytics-engineer hours than it costs in dependency management? Most niche packages fail — a 10-line macro inlined in macros/ beats a full package dependency for coverage of a single use case.
3. dbt_project_evaluator (or dbt_meta_testing) is worth adding only if you actively wire it into CI. Installing a linting package "just in case" fragments the dependency graph without a paid return.
4. The version-range strategy is [">=X.Y.0", "<X.(Y+1).0"] — patch upgrades are allowed, minor upgrades are blocked until reviewed. This is stricter than "just pin the major" and avoids the "silent minor break" that mid teams get burned by.
5. Every package upgrade is a PR with a diff of the package's changelog attached. The PR author's job is to explain what changed and what could break. No "silent dbt deps --upgrade" against production.

Output.

Metric	Fragmented (14 packages)	Disciplined (4 packages)
packages.yml entries	14	4
Transitive version conflicts	frequent	rare
Ownership per package	unclear	clear
Upgrade blast radius	opaque	scoped
Team fluency with each package	low	high

Rule of thumb. Four packages is the sweet spot for most teams. The 40 recurring macros (surrogate key, spine, pivot, dedup, star, expect_*, generate_source, generate_model_yaml, osmosis propagate) are covered exactly. Every additional package must clear the ROI bar.

Worked example — the packages.yml semantics and dbt_packages/ layout

Detailed explanation. Interviewers often ask "walk me through what actually happens when I run dbt deps." The senior answer names the resolver, the pin semantics, the dbt_packages/ directory layout, and the macro namespacing rules. The mid answer is "it installs packages." Walk through the mechanics.

• The resolver. dbt deps reads packages.yml, resolves each entry to a concrete git ref (a tag for Hub packages, an explicit revision for git-direct, the local path for local), clones the ref into dbt_packages/{name}/, and recursively resolves each installed package's own packages.yml.
• The namespace. Every macro in an installed package is addressable as {{ {package_name}.{macro_name}(args) }}. The package name comes from the installed package's own dbt_project.yml, not from the entry in your packages.yml.
• The precedence. If the same macro name exists in two packages, or in a package and locally, precedence is: local macros > macros in your project's dispatch config > macros in the leftmost package. Ambiguity is a code smell.

Question. A team runs dbt deps with the three-package packages.yml from the previous example. Trace what appears on disk and what's callable in Jinja after the resolve completes.

Input.

packages.yml entry	Resolved ref	Installed at
dbt-labs/dbt_utils >=1.1.0,<2.0.0	tag 1.1.1	dbt_packages/dbt_utils/
calogica/dbt_expectations >=0.10.0,<0.11.0	tag 0.10.4	dbt_packages/dbt_expectations/
dbt-labs/codegen >=0.12.0,<0.13.0	tag 0.12.1	dbt_packages/codegen/

Code.

# On-disk after `dbt deps`
my_project/
├── dbt_project.yml
├── packages.yml
├── models/
├── macros/
└── dbt_packages/
    ├── dbt_utils/
    │   ├── dbt_project.yml        # declares name: dbt_utils
    │   ├── macros/
    │   │   ├── sql/generate_surrogate_key.sql
    │   │   ├── sql/date_spine.sql
    │   │   ├── sql/pivot.sql
    │   │   ├── sql/deduplicate.sql
    │   │   └── sql/star.sql
    │   └── tests/
    ├── dbt_expectations/
    │   ├── dbt_project.yml        # declares name: dbt_expectations
    │   ├── macros/
    │   │   └── schema_tests/
    │   │       ├── column_values_basic/expect_column_values_to_be_between.sql
    │   │       └── table_shape/expect_table_row_count_to_be_between.sql
    │   └── packages.yml            # transitively depends on dbt_utils
    └── codegen/
        ├── dbt_project.yml         # declares name: codegen
        └── macros/
            ├── generate_source.sql
            ├── generate_model_yaml.sql
            └── generate_base_model.sql

-- Callable in any model or analysis
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }} as order_key,
    order_id,
    amount_usd
from {{ ref('stg_orders') }}

-- Callable in tests/*.yml
- name: fct_orders
  columns:
    - name: amount_usd
      tests:
        - dbt_expectations.expect_column_values_to_be_between:
            min_value: 0
            max_value: 100000

Step-by-step explanation.

1. dbt deps walks packages.yml top-to-bottom. For each entry it consults the resolver: Hub entries hit hub.getdbt.com to look up the version-to-tag mapping; git entries clone the URL at the specified revision; local entries symlink or copy the local path.
2. Each package is materialised under dbt_packages/{name}/. The {name} is the name declared in the package's own dbt_project.yml, not the string in your packages.yml. That is why dbt-labs/codegen installs as dbt_packages/codegen/ (the package declares name: codegen in its own dbt_project.yml).
3. If a package has its own packages.yml, dbt recursively resolves the transitive dependencies. dbt_expectations depends on dbt_utils; since dbt_utils is already at the top level, the resolver checks the version compatibility and reuses the top-level install if the ranges intersect.
4. Every macro in dbt_packages/{name}/macros/**/*.sql becomes callable in Jinja as {{ {name}.{macro_name}(args) }}. The macro namespace is flat within a package — folders inside macros/ are for organisation only, not addressing.
5. Precedence rules kick in when a macro name exists in multiple places. Your project's own macros/generate_surrogate_key.sql shadows dbt_utils.generate_surrogate_key (though the fully-qualified {{ dbt_utils.generate_surrogate_key(...) }} still resolves). This is how teams override a package's default implementation without forking.

Output.

Layer	Populated by	Addressable as
macros/ in your project	you	{{ macro_name(...) }}
dbt_packages/dbt_utils/macros/	dbt deps	{{ dbt_utils.macro_name(...) }}
dbt_packages/dbt_expectations/macros/	dbt deps	{{ dbt_expectations.macro_name(...) }}
Recursive transitive install	dbt deps	already resolved by parent range

Rule of thumb. Never edit files under dbt_packages/ — they're regenerated on every dbt deps. To override a package macro, add a same-named macro in your project's macros/ folder or configure the dispatch mechanism in dbt_project.yml.

Senior interview question on why dbt teams standardise on packages

A senior interviewer often opens with: "You inherit a dbt project with 40 hand-rolled macros in the local macros/ folder and zero packages installed. Walk me through your first-week package-adoption plan, which of the 40 macros disappear immediately, and how you'd measure the win."

Solution Using the four-package baseline + a macro-audit spreadsheet

# packages.yml — the four-package baseline
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]

# macro-audit.tsv — one row per hand-rolled macro
name                    lines   used_in   replaces_with                              action
generate_hash_key       12      63        dbt_utils.generate_surrogate_key           delete
build_calendar          40      12        dbt_utils.date_spine                       delete
pivot_wide              38      8         dbt_utils.pivot                            delete
dedup_by_pk             22      41        dbt_utils.deduplicate                      delete
select_star_except      18      6         dbt_utils.star                             delete
column_min_max_check    24      15        dbt_expectations.expect_column_values_to_be_between   delete
recent_data_check       19      9         dbt_expectations.expect_row_values_to_have_recent_data   delete
row_count_bounds        16      11        dbt_expectations.expect_table_row_count_to_be_between   delete
gen_source_yaml         31      1         codegen.generate_source                    delete
gen_model_yaml          28      1         codegen.generate_model_yaml                delete
domain_ranking_score    54      6         (business-specific — keep)                 keep
customer_ltv_bands      67      4         (business-specific — keep)                 keep

Step-by-step trace.

Step	Before	After
Hand-rolled macros in macros/	40	8 (business-specific only)
Lines of macro code	~1200	~300
Adapter-portability risk (silent-wrong)	high	low (package-tested)
Package upgrades	0 (nothing installed)	3 packages, all pinned
CI time on macro compile	12 s	8 s (fewer local macros to render)
Onboarding time for new engineers	days (learn 40 quirks)	hours (docs.getdbt.com)

After the audit, 32 of the 40 hand-rolled macros are replaced by package equivalents; the 8 that survive are genuinely business-specific and worth keeping local. The team ships one PR per surviving batch (surrogate keys, spines, pivots, dedups, tests) with a global find-and-replace, verifies CI green, deletes the old files. The whole rollout is one senior-engineer week.

Output:

Metric	Before	After
Local macros	40	8
Package versions pinned	0	3
Cross-adapter test coverage	0%	inherited from packages
Team fluency on each macro	low	high (Hub docs)
Time to onboard	3 days	4 hours

Why this works — concept by concept:

• Four-package baseline — the architectural lever is stop writing what dbt Labs and the community already ship. Four packages cover the recurring macro tax; anything else is business logic that belongs in models/ or genuinely-niche macros that stay local.
• Macro audit spreadsheet — every hand-rolled macro is either replaced or kept, and each row carries an explicit justification. No macro survives the audit by inertia.
• Version pinning — [">=1.1.0", "<2.0.0"] admits patch fixes and blocks breaking major upgrades. The dbt deps resolver produces the same install every time until the pin changes.
• Adapter dispatch inherited — every replaced macro now works on Snowflake, BigQuery, Redshift, Postgres, and Databricks by construction. The team's next warehouse migration is transparent for the macro layer.
• Cost — one senior-engineer week for the audit + rollout; the amortised cost is O(1) per quarterly package upgrade. The saved cost — no more per-adapter macro debugging — dominates every alternative.

SQL
Topic — sql
SQL macro-portability and package-adoption problems

Practice →

ETL Topic — etl ETL problems on dbt project hardening

Practice →

---

2. dbt_utils — the utility belt

Six macros do 80% of the work — generate_surrogate_key, date_spine, pivot, deduplicate, star, get_column_values

The mental model in one line: dbt_utils is the ~60-macro utility belt every dbt project reaches for; six of those macros — generate_surrogate_key, date_spine, pivot, deduplicate, star, and get_column_values — carry 80% of the day-to-day analytics-engineering load. The macros are adapter-dispatched so the same call compiles to the right SQL on Snowflake, BigQuery, Redshift, Postgres, and Databricks; the package is owned by dbt Labs so it tracks every dbt-core release within days; and the version range [">=1.1.0", "<2.0.0"] is the canonical pin for the current major.

The six macros you'll use every week.

• generate_surrogate_key. Deterministic hash of an ordered column list; adapter-dispatched to the correct MD5 dialect per warehouse. Replaces every hand-rolled md5(concat(...)). The package renamed the older surrogate_key macro to generate_surrogate_key in v0.8.0 — the most common breaking-change trap when upgrading from ancient versions.
• date_spine. Generates a gapless date series between start_date and end_date at any granularity (day, hour, week, month). Powers every "customers active per day" or "orders per hour" metric where the source data has gaps. Adapter-dispatched using recursive CTEs (Postgres), generate_series (Snowflake), or GENERATE_DATE_ARRAY (BigQuery).
• pivot. Turns a long table into a wide one — one row per grouping key, one column per pivot value. The pivot values must be known at compile time (typically via get_column_values). Replaces every hand-written CASE WHEN pivot.
• deduplicate. Keeps the row per (partition_by, order_by) group. The idiomatic replacement for ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1 filters. Compiles to QUALIFY on adapters that support it, subquery-filter on those that don't.
• star. SELECT * EXCEPT (col1, col2, ...) — expands to the full column list of a ref/source minus the excluded columns. Essential for staging models that mirror an upstream with 40 columns minus 2 PII fields.
• get_column_values. Runs a warehouse query at compile time to fetch distinct values from a column, then returns them as a Jinja list. Used as input to pivot (get the distinct pivot categories) or for driving for loops that emit templated SQL.

Test macros — the sleeper wins.

• unique_combination_of_columns. Tests that (col_a, col_b, col_c) is unique — the multi-column uniqueness test dbt-core doesn't ship natively. Replaces every hand-written composite-key uniqueness singular test.
• expression_is_true. Tests that any SQL expression returns TRUE for every row. Escape hatch for row-level invariants (amount_usd >= 0 or is_refund = true).
• equal_rowcount / fewer_rows_than / equality. Cross-model row-count and equality tests. Used for regression tests during migrations ("does the new model produce the same rows as the old model?").
• recency. Tests that the latest row is not older than N units. The "is my source table stale?" test.

Adapter dispatch under the hood.

• The pattern. dbt_utils.generate_surrogate_key calls adapter.dispatch('default__generate_surrogate_key', 'dbt_utils'). dbt inspects the current target's adapter (Snowflake, BigQuery, ...) and picks the matching implementation: snowflake__generate_surrogate_key, bigquery__generate_surrogate_key, postgres__generate_surrogate_key, or the default__ implementation as the fallback.
• The precedence. Adapter-specific > default. If your target is Snowflake and the package ships a snowflake__generate_surrogate_key, that wins over the default__ implementation.
• The override. You can shadow any dispatched macro by defining {name}__generate_surrogate_key in your own project and registering it in the dispatch config in dbt_project.yml. Rarely needed; the package's implementations are almost always correct.

Common interview probes on dbt_utils.

• "What macro replaces a hand-written MD5 for surrogate keys?" — generate_surrogate_key.
• "How do you generate a gapless date series?" — date_spine.
• "How do you pivot a long table to wide in dbt?" — pivot + get_column_values to seed the categories.
• "What test catches duplicate rows on a composite key?" — dbt_utils.unique_combination_of_columns.
• "How does the same macro produce different SQL on Snowflake vs BigQuery?" — adapter.dispatch.

Worked example — rebuilding a date spine with dbt_utils.date_spine

Detailed explanation. A textbook use case: the orders source has gaps — no rows on days with zero orders — but the "orders per day" chart needs to show zero for those gap days rather than skipping them. The senior fix is to left-join a date_spine model against orders grouped by day. The date_spine macro produces one row per day between the earliest order date and the latest; the LEFT JOIN + COALESCE fills gaps with zero.

• The problem. SELECT date_trunc('day', ordered_at), count(*) FROM orders GROUP BY 1 — skips days with zero orders.
• The fix. Build a dim_date model with dbt_utils.date_spine; LEFT JOIN orders grouped by day against it; COALESCE the count.

Question. Build a dim_date model with a daily spine from 2020-01-01 through the current date + 1 year, then a fct_orders_daily fact model that shows zero on gap days.

Input.

Model	Grain	Source
dim_date	one row per day	dbt_utils.date_spine
fct_orders_daily	date × count(orders)	LEFT JOIN dim_date × stg_orders

Code.

-- models/marts/dim_date.sql
{{ config(materialized='table') }}

with spine as (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast(dateadd('year', 1, current_date) as date)"
    ) }}
)

select
    date_day::date                                    as date_day,
    date_trunc('week',  date_day)::date               as week_start,
    date_trunc('month', date_day)::date               as month_start,
    date_trunc('quarter', date_day)::date             as quarter_start,
    date_trunc('year',  date_day)::date               as year_start,
    extract(dayofweek from date_day)                  as day_of_week,
    extract(day from date_day)                        as day_of_month,
    extract(week from date_day)                       as week_of_year,
    case when extract(dayofweek from date_day) in (0, 6) then true else false end as is_weekend
from spine

-- models/marts/fct_orders_daily.sql
{{ config(materialized='incremental', unique_key='date_day') }}

with orders_by_day as (
    select
        date_trunc('day', ordered_at)::date as date_day,
        count(*)                            as order_count,
        sum(amount_usd)                     as revenue_usd
    from {{ ref('stg_orders') }}
    group by 1
),

spine as (
    select date_day from {{ ref('dim_date') }}
    where date_day <= current_date
)

select
    spine.date_day,
    coalesce(o.order_count, 0)  as order_count,
    coalesce(o.revenue_usd, 0)  as revenue_usd
from spine
left join orders_by_day o using (date_day)

Step-by-step explanation.

1. dbt_utils.date_spine(datepart="day", start_date=..., end_date=...) compiles to a warehouse-native recursive CTE (Postgres), GENERATE_DATE_ARRAY (BigQuery), or generator function (Snowflake) that yields one row per day. The macro handles the adapter-specific SQL; the caller writes one line.
2. dim_date wraps the spine with denormalised calendar columns (week, month, quarter, year, day-of-week, is_weekend). Every downstream model that needs a calendar attribute joins dim_date on date_day — a Kimball-style shared dimension.
3. fct_orders_daily LEFT JOINs the spine against the grouped orders. Days with zero orders survive because the spine drives the join; the COALESCE fills the missing order_count and revenue_usd with zero.
4. The incremental materialisation on unique_key='date_day' means the model only recomputes new dates (or reprocesses if the merge key matches). The spine model itself is materialised as a full table because it's cheap and used by many downstream models.
5. Every warehouse rebuild produces the same calendar. No hand-rolled recursive CTE lives in the project; no per-adapter special-casing; a warehouse migration is transparent for the calendar layer.

Output.

Approach	date_day	order_count	Correct?
Group by date_trunc alone	2026-06-19	12	yes
Group by date_trunc alone	2026-06-21	4	yes (2026-06-20 missing!)
LEFT JOIN spine	2026-06-19	12	yes
LEFT JOIN spine	2026-06-20	0	yes (gap filled!)
LEFT JOIN spine	2026-06-21	4	yes

Rule of thumb. Any "per day" (or "per hour") chart driven by a group-by on an event table skips gap periods. Always join against a spine; always use dbt_utils.date_spine to build it. The one-line macro replaces 30 lines of adapter-specific recursive CTE.

Worked example — surrogate keys with dbt_utils.generate_surrogate_key

Detailed explanation. The migration from an ancient project that uses dbt_utils.surrogate_key (deprecated, renamed to generate_surrogate_key in v0.8.0) to modern dbt_utils. The senior signal is knowing that the rename happened, why (v1.0.0 changed null-handling semantics), and how to migrate safely without silently changing hash values across the fleet.

• The rename. dbt_utils.surrogate_key(...) → dbt_utils.generate_surrogate_key(...). Deprecated in 0.8, removed in 1.0.
• The semantic change. generate_surrogate_key coalesces nulls to _dbt_utils_surrogate_key_null_ by default, producing different hashes than the old surrogate_key for rows with nulls. Silent-break risk during upgrade.
• The migration. Introduce the new macro; run both in parallel for one build to compare hashes; only then delete the old.

Question. Design a migration from dbt_utils 0.7.x (surrogate_key) to dbt_utils 1.1.x (generate_surrogate_key) across a project with 63 usages, without silently changing production hash values.

Input.

Metric	Before	After
dbt_utils version	0.7.6	1.1.1
Macro name	surrogate_key	generate_surrogate_key
Null-handling	passes nulls through	coalesces to sentinel
Usages in project	63	63

Code.

# packages.yml — pin the new range explicitly
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

-- Migration helper macro — macros/generate_surrogate_key_legacy.sql
{% macro generate_surrogate_key_legacy(field_list) %}
    -- Replicates dbt_utils 0.7 null-passthrough semantics for regression testing.
    {%- set fields = [] -%}
    {%- for field in field_list -%}
        {%- set _ = fields.append("cast(" ~ field ~ " as " ~ dbt.type_string() ~ ")") -%}
    {%- endfor -%}
    md5(concat({{ fields|join(', ') }}))
{% endmacro %}

-- Regression test — analyses/check_surrogate_key_stability.sql
select
    {{ generate_surrogate_key_legacy(['order_id', 'user_id']) }}   as legacy_key,
    {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }} as new_key,
    order_id,
    user_id
from {{ ref('stg_orders') }}
where {{ generate_surrogate_key_legacy(['order_id', 'user_id']) }}
   != {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }}
limit 100

-- Model change — models/marts/fct_orders.sql
{{ config(materialized='table') }}
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id']) }} as order_key,
    order_id,
    user_id,
    amount_usd,
    ordered_at
from {{ ref('stg_orders') }}

Step-by-step explanation.

1. Pin dbt_utils to [">=1.1.0", "<2.0.0"] and run dbt deps in a feature branch. Do not merge to main yet — the goal is to audit the semantic difference before shipping.
2. Add a generate_surrogate_key_legacy helper that replicates the old 0.7 null-passthrough behaviour. This macro is a temporary regression-test aid, not production code.
3. Run the analysis query in analyses/check_surrogate_key_stability.sql against every key-generating model. Rows where legacy_key != new_key are the rows where the null-handling semantic changed the hash. If the diff is zero, migration is safe; if it's nonzero, decide whether the new hash is acceptable (usually yes — the new semantic is safer, but downstream joins on the old hash break).
4. If any downstream table stores the hash (fact tables joined on order_key), you cannot silently change the hash without breaking those joins. The migration then either (a) drops and rebuilds those downstream tables with new keys, or (b) keeps a compatibility layer that hashes the legacy way for those columns.
5. After the audit is complete and the diffs are understood, replace every dbt_utils.surrogate_key with dbt_utils.generate_surrogate_key, delete the legacy helper, ship one PR. CI green, prod deploy in the next release window.

Output.

Row	order_id	user_id	legacy_key	new_key	delta
all-non-null	100	5	a1b2...	a1b2...	0 (match)
null user_id	101	NULL	(null propagates)	c3d4...	changed
both null	NULL	NULL	(null)	e5f6...	changed

Rule of thumb. Every dbt_utils major-version upgrade needs a regression audit of any renamed or semantically-changed macro. Ship the compatibility helper, run the diff, understand every non-zero row, then rip the helper out. Never blind-deploy a major package upgrade.

Worked example — pivoting with dbt_utils.pivot + get_column_values

Detailed explanation. A long table (event_counts with columns event_name, date, count) needs to be pivoted to wide (date, login_count, signup_count, purchase_count). Without dbt_utils, the model hard-codes the event names in a CASE WHEN pivot — the moment a new event name appears, the model silently drops it. The senior fix is dbt_utils.pivot driven by dbt_utils.get_column_values: at compile time, fetch the distinct event names from the warehouse, then emit a CASE WHEN pivot for each one automatically.

• The problem. Hand-rolled CASE WHEN pivot goes stale the moment a new event name appears.
• The fix. get_column_values runs at compile time to fetch the pivot categories; pivot emits the CASE WHENs for each.

Question. Build a daily_event_counts_wide model that pivots daily_event_counts_long (columns: date_day, event_name, event_count) into a wide format with one column per event name, resilient to new events being added.

Input.

Long-table row	date_day	event_name	event_count
...	2026-06-22	login	1200
...	2026-06-22	signup	45
...	2026-06-22	purchase	88

Code.

-- models/marts/daily_event_counts_wide.sql
{{ config(materialized='table') }}

-- Fetch distinct event names at compile time
{%- set event_names = dbt_utils.get_column_values(
    table=ref('daily_event_counts_long'),
    column='event_name',
    order_by='event_name'
) -%}

select
    date_day,
    {{ dbt_utils.pivot(
        column='event_name',
        values=event_names,
        agg='sum',
        then_value='event_count',
        else_value='0',
        prefix='',
        suffix='_count'
    ) }}
from {{ ref('daily_event_counts_long') }}
group by date_day

-- What the compiled SQL looks like (Snowflake target)
select
    date_day,
    sum(case when event_name = 'login'    then event_count else 0 end) as login_count,
    sum(case when event_name = 'signup'   then event_count else 0 end) as signup_count,
    sum(case when event_name = 'purchase' then event_count else 0 end) as purchase_count,
    sum(case when event_name = 'refund'   then event_count else 0 end) as refund_count
from analytics.dbt.daily_event_counts_long
group by date_day

Step-by-step explanation.

1. get_column_values(table=ref(...), column='event_name') runs a SELECT DISTINCT event_name FROM ... ORDER BY event_name at compile time — not at run time. The result is a Jinja list of strings.
2. pivot(column='event_name', values=event_names, agg='sum', then_value='event_count') emits one SUM(CASE WHEN event_name = 'X' THEN event_count ELSE 0 END) AS X_count per event name in the list.
3. The generated SQL is regular pivot SQL — no runtime overhead vs a hand-written pivot. The only cost is the compile-time distinct query, which runs once per dbt compile / dbt run.
4. When a new event name appears in the source (e.g. refund), the next dbt run picks it up because the distinct query returns it. No code change to the model.
5. Caveat: schema-evolving pivots break downstream models that hard-code the column list. If a downstream mart hard-codes login_count, signup_count, purchase_count, adding refund_count upstream is fine — but removing login_count (event stopped firing) will break the mart. Guard the pivot by an allowlist of expected events in a seed file if you need column-level stability.

Output.

Row	date_day	login_count	signup_count	purchase_count	refund_count
...	2026-06-22	1200	45	88	0
...	2026-06-21	1150	40	92	3

Rule of thumb. For pivots where the category list evolves, always use dbt_utils.pivot + get_column_values. For pivots where you need column-level stability against schema evolution, drive the values list from a seed file instead of the compile-time distinct query.

Senior interview question on choosing dbt_utils macros

A senior interviewer might ask: "You need to build a staging model that (a) generates a surrogate key from a composite of columns, (b) selects all upstream columns except two PII fields, (c) deduplicates by primary key keeping the latest row, and (d) exposes a gapless date column for downstream joins. Write the model using dbt_utils and explain each macro choice."

Solution Using generate_surrogate_key + star + deduplicate + date_spine

-- models/staging/stg_orders_clean.sql
{{ config(materialized='table') }}

with source as (
    select * from {{ source('raw', 'orders') }}
),

dedup as (
    {{ dbt_utils.deduplicate(
        relation='source',
        partition_by='order_id',
        order_by='updated_at desc'
    ) }}
),

spine as (
    {{ dbt_utils.date_spine(
        datepart='day',
        start_date="cast('2023-01-01' as date)",
        end_date="cast(dateadd('day', 1, current_date) as date)"
    ) }}
)

select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'user_id', 'ordered_at']) }} as order_sk,
    {{ dbt_utils.star(
        from=ref('source'),
        except=['pii_email', 'pii_phone']
    ) }},
    spine.date_day as ordered_day
from dedup
left join spine on cast(dedup.ordered_at as date) = spine.date_day

Step-by-step trace.

Step	Macro	Behaviour
1	deduplicate	Emits QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1 on Snowflake; subquery-filter on adapters without QUALIFY
2	generate_surrogate_key	Adapter-dispatched MD5 hash; deterministic across warehouses
3	star(except=[...])	Expands to full column list of source minus the two PII fields
4	date_spine	Gapless daily spine 2023-01-01 → today; LEFT JOIN gives every order a spine-matched day

After the model compiles, the emitted SQL is regular warehouse SQL — no runtime dbt overhead. Each macro replaces boilerplate that would otherwise be 10–40 lines of hand-written code, all of which would need per-adapter maintenance. The four macros together compress ~120 lines of hand-rolled logic into a single model that reads cleanly.

Output:

Macro used	Replaces	Adapter dispatched
deduplicate	ROW_NUMBER() OVER (...) = 1 subquery	yes
generate_surrogate_key	md5(concat(...)) inline	yes
star	Manual column list minus PII	no (static Jinja)
date_spine	Hand-written recursive CTE	yes

Why this works — concept by concept:

• One macro per concern — each macro solves exactly one repeatable problem (dedup, hash, projection, spine). Composing four macros into one model is the analytics-engineering equivalent of the Unix philosophy.
• Adapter dispatch inherited — three of the four macros dispatch to per-adapter SQL. The model portability across Snowflake, BigQuery, Redshift, Postgres, and Databricks is free.
• star for exclusion — the PII exclusion is expressed once, at the projection layer, not with an explicit column list. Adding a new upstream column doesn't require a model edit; adding a new PII field to except=[...] is the only edit needed.
• Compile-time vs run-time — star and date_spine bounds are resolved at compile time; deduplicate and generate_surrogate_key emit warehouse-native SQL that runs at query time. Compile-time evaluation is free; run-time SQL is warehouse-priced. The macro choice respects that boundary.
• Cost — one model, four package macros, ~30 lines. The equivalent hand-rolled version is ~150 lines with per-adapter branches. The saved code review, testing, and cross-warehouse debugging cost pays for the package on the first model that uses it.

SQL
Topic — sql
SQL date_spine, pivot, and deduplication problems

Practice →

ETL Topic — etl ETL problems on dbt staging model design

Practice →

---

3. dbt_expectations — Great-Expectations-style asserts

Bring Great Expectations vocabulary into dbt tests without leaving the DAG

The mental model in one line: dbt_expectations ports Great Expectations' expect_column_* and expect_table_* semantics into dbt's schema-test framework, giving you 60+ ready-to-wire column and table assertions that run as part of every dbt test invocation — no separate Great Expectations service, no external orchestration, no Python runtime. The tests live in your models/schema.yml, run against the warehouse, and produce a red build the moment a data-quality invariant breaks.

Column-level assertions.

• expect_column_values_to_be_between. Numeric or date column falls in a range. min_value: 0, max_value: 100000. Replaces every check amount_usd >= 0 singular test.
• expect_column_values_to_match_regex. Column matches a regex. Common for validating email, phone, ISO country codes, IANA timezone strings.
• expect_column_values_to_be_in_set. Column values are within an enum-like set. The typed alternative to accepted_values (dbt core's version), with better null-handling.
• expect_column_distinct_count_to_equal. The number of distinct values in a column equals a known count. Used to catch category-drift ("we should have exactly 12 country codes; if the count changes, something upstream broke").
• expect_column_pair_values_A_to_be_greater_than_B. Cross-column invariant — e.g. end_ts > start_ts on every row.

Aggregate-level assertions.

• expect_table_row_count_to_be_between. Total row count falls in a window. Catches "the upstream job silently produced 0 rows" and "the join blew up to 10x the expected size."
• expect_column_sum_to_be_between. SUM(column) is in a range. The volume sanity check for finance tables.
• expect_column_mean_to_be_between / expect_column_median_to_be_between. Distribution-shape asserts. Catches feature drift on ML input tables.
• expect_column_stdev_to_be_between. Variance sanity check. Fires when a source starts producing constant values (broken sensor, stuck integration).
• expect_row_values_to_have_recent_data. Latest row's timestamp is not older than N minutes/hours/days. The "is my source table fresh?" test — the most-used dbt_expectations test in production.

Distribution assertions (the sleeper wins).

• expect_column_kl_divergence_to_be_less_than. KL-divergence between the observed distribution and a reference distribution. Detects data drift on ML feature stores.
• expect_column_pair_cramers_v_to_be_less_than. Association strength between two categorical columns. Detects data-leak scenarios where a feature accidentally encodes the label.

How dbt_expectations differs from Great Expectations proper.

• Runtime. dbt_expectations runs inside dbt's dbt test command, using the warehouse to evaluate the assertion. Great Expectations runs in Python, pulls the data (or batches of it) to the driver, and evaluates in pandas / SQL / Spark. dbt_expectations is warehouse-native; GE is bring-your-own-runtime.
• Result storage. dbt_expectations returns fail rows via dbt's store_failures: true config; GE writes to a "data docs" HTML site. Different UX; same information.
• Coverage. dbt_expectations mirrors most of GE's expect_column_* and expect_table_* semantics, but a few are Python-only (anything requiring model inference or scipy).
• Composability. dbt_expectations tests live alongside your models and inherit dbt's DAG scheduling. GE checkpoints need external orchestration (Airflow, Dagster).

Common interview probes on dbt_expectations.

• "When would you pick dbt_expectations over dbt core's built-in tests?" — when you need the GE vocabulary (regex, ranges, recency, distribution) without a separate GE deployment.
• "How does expect_row_values_to_have_recent_data compare to dbt source freshness?" — source freshness runs before the DAG; recency tests run inside the DAG on already-built models. Different scopes; both useful.
• "What breaks if a dbt_expectations test fails?" — same as any dbt test: the build errors, downstream models are not built (unless --warn-error is off), CI turns red.
• "How do you store the failing rows?" — store_failures: true in dbt_project.yml writes the failed rows into a dbt_test__audit schema.

Worked example — six real-world tests on a fct_orders table

Detailed explanation. A finance-critical fct_orders table needs a battery of data-quality tests: amounts non-negative, currency codes in a fixed enum, ordered_at not in the future, source table refreshed within the last 60 minutes, row count within historical band, order_key genuinely unique. Wire these six tests via dbt_expectations in the model's schema.yml.

• The invariants. Six independent invariants covering value ranges, enum membership, temporal validity, freshness, volume, and uniqueness.
• The rollout. One PR that adds the tests; run dbt test --select fct_orders locally; fix any red rows; ship.

Question. Add the six invariants to models/marts/fct_orders.yml. Use dbt_expectations for anything the dbt core tests can't express.

Input.

Invariant	Test	Package
amount_usd >= 0	expect_column_values_to_be_between (min=0)	dbt_expectations
currency_code in enum	expect_column_values_to_be_in_set	dbt_expectations
ordered_at ≤ now()	expect_column_values_to_be_between (max=CURRENT_TIMESTAMP)	dbt_expectations
source fresh (< 60min)	expect_row_values_to_have_recent_data	dbt_expectations
row count 10k–200k daily	expect_table_row_count_to_be_between	dbt_expectations
order_key unique	unique	dbt core

Code.

# models/marts/fct_orders.yml
version: 2

models:
  - name: fct_orders
    description: One row per completed order.
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 10000
          max_value: 200000
          row_condition: "cast(ordered_at as date) = current_date"
    columns:
      - name: order_key
        description: Surrogate key for the order.
        tests:
          - unique
          - not_null

      - name: amount_usd
        description: Order total in USD.
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000

      - name: currency_code
        description: ISO 4217 currency code.
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_in_set:
              value_set: ['USD', 'EUR', 'GBP', 'JPY', 'CAD', 'AUD']

      - name: ordered_at
        description: Timestamp the order was placed.
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: "'2020-01-01'::timestamp"
              max_value: "current_timestamp"
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: minute
              interval: 60
              row_condition: "ordered_at is not null"

Step-by-step explanation.

1. The table-level test expect_table_row_count_to_be_between runs a single SELECT COUNT(*) against fct_orders scoped to today's rows, then compares to the min_value / max_value bounds. Red if outside; green otherwise. Catches volume anomalies.
2. expect_column_values_to_be_between on amount_usd catches any negative or absurdly-large value. The lower bound of 0 mirrors the business rule "orders can't be negative"; the upper bound of 1M catches unit-conversion bugs (values in cents that got interpreted as dollars).
3. expect_column_values_to_be_in_set on currency_code enforces the closed enum. Any row with currency_code = 'XYZ' fails the test, exposing an upstream data-entry bug or a new currency the enum forgot to include.
4. expect_row_values_to_have_recent_data on ordered_at with datepart=minute, interval=60 fires when the newest ordered_at is older than 60 minutes. The "source table went stale" test. row_condition="ordered_at is not null" skips test-tombstone rows.
5. The final unique + not_null on order_key uses dbt core's built-in tests (no need to reach for the package for these). The package is for the tests dbt core doesn't ship.

Output.

Test	Type	Passes when
expect_table_row_count_to_be_between	volume	10k ≤ today's rows ≤ 200k
expect_column_values_to_be_between (amount_usd)	range	0 ≤ amount_usd ≤ 1M
expect_column_values_to_be_in_set (currency_code)	enum	value ∈ {USD, EUR, GBP, JPY, CAD, AUD}
expect_column_values_to_be_between (ordered_at)	temporal	2020-01-01 ≤ ordered_at ≤ now
expect_row_values_to_have_recent_data	freshness	max(ordered_at) ≥ now - 60m
unique / not_null (order_key)	uniqueness	dbt core

Rule of thumb. Every fact table needs at least: volume, freshness, uniqueness, and one value-range test per numeric column. dbt_expectations supplies the volume, freshness, and range tests in one line each; dbt core supplies uniqueness. Ship all four on every fact table.

Worked example — expect_column_values_to_match_regex for email validation

Detailed explanation. A user-signup event stream lands in stg_user_events with an email column. Downstream marts join on email (usually a bad idea, but sometimes unavoidable in early-stage pipelines). The invariant: every non-null email matches the RFC-5322-ish regex. Add the test; catch upstream bugs before they poison downstream marts.

• The invariant. email matches ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$.
• The exceptions. Nulls are allowed (test with a row_condition); test-account emails ending in @example.com are excluded via the row condition.

Question. Add a dbt_expectations.expect_column_values_to_match_regex test on stg_user_events.email that validates format, skips nulls, and excludes internal test accounts.

Input.

Row	email	Passes?
1	[e-mail]	yes
2	NULL	(skipped)
3	[e-mail]	(skipped — test account)
4	not-an-email	no (fails)

Code.

# models/staging/stg_user_events.yml
version: 2

models:
  - name: stg_user_events
    columns:
      - name: email
        description: User email; validated for RFC-5322-ish format.
        tests:
          - dbt_expectations.expect_column_values_to_match_regex:
              regex: "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$"
              row_condition: "email is not null and email not like '[e-mail]'"

-- Compiled SQL (Snowflake)
select *
from analytics.dbt.stg_user_events
where email is not null
  and email not like '[e-mail]'
  and not regexp_like(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')

Step-by-step explanation.

1. The regex string is JSON-escaped in the YAML (\\. → \. after YAML parse → \. after Jinja render). Two backslashes in the file become one in the compiled SQL.
2. The row_condition clause is critical for regex tests. Without email is not null, every null row is treated as a failure (regex against null is null, not TRUE). Without the not like '[e-mail]' clause, every QA test account fails the format check.
3. The compiled SQL is a SELECT * against the model filtered by the row condition and the negation of the regex match. Any returned row is a failing row. dbt reports the count and (with store_failures: true) writes the offending rows to a dbt_test__audit schema.
4. Regex tests are moderately expensive — the regex engine runs per row. For a table with 100M rows, the test may take 10–30 seconds on Snowflake. Adjust the severity: warn config if you want the test to log but not fail the build for tables where regex cost is high.
5. The regex itself is deliberately not full RFC 5322 (which is thousands of characters). The pragmatic pattern catches 99% of malformed emails without the compile-time nightmare of a strict RFC parser. Adjust for your risk tolerance.

Output.

Row	email	Test outcome
1	[e-mail]	pass
2	NULL	skipped (row_condition)
3	[e-mail]	skipped (row_condition)
4	not-an-email	fail (returned by SELECT)
5	[e-mail]	pass

Rule of thumb. Regex tests need explicit row_condition guards for null-handling and known-excluded patterns. Without them, the test is either too strict (fails on nulls) or too noisy (fails on QA accounts).

Worked example — freshness with expect_row_values_to_have_recent_data

Detailed explanation. The "is my source table stale?" test is arguably the most-used dbt_expectations assertion in production. It fires when the newest row's timestamp is older than a threshold. Use it on every source-facing staging model to catch upstream pipeline stalls before the downstream mart shows stale numbers.

• The invariant. max(updated_at) >= now() - INTERVAL '60 minutes' (or whatever the SLA is).
• The alternative. dbt core's source freshness block. Same intent; different execution — source freshness runs before the DAG builds; expect_row_values_to_have_recent_data runs after the model is built. Belt-and-braces to use both.

Question. Add expect_row_values_to_have_recent_data on stg_stripe_charges with a 30-minute SLA and demonstrate the difference from source freshness.

Input.

Layer	Freshness SLA	Enforced by
Source (raw.stripe_charges)	15 min	dbt source freshness
Staging (stg_stripe_charges)	30 min	dbt_expectations
Mart (fct_revenue_daily)	4 hours	dbt_expectations

Code.

# models/sources.yml — source-level freshness (belt)
version: 2

sources:
  - name: raw
    tables:
      - name: stripe_charges
        loaded_at_field: _synced_at
        freshness:
          warn_after:  {count: 15, period: minute}
          error_after: {count: 60, period: minute}

# models/staging/stg_stripe_charges.yml — staging-level recency (braces)
version: 2

models:
  - name: stg_stripe_charges
    tests:
      - dbt_expectations.expect_row_values_to_have_recent_data:
          column_name: created_at
          datepart: minute
          interval: 30
          row_condition: "created_at is not null"

# models/marts/fct_revenue_daily.yml — mart-level recency
version: 2

models:
  - name: fct_revenue_daily
    tests:
      - dbt_expectations.expect_row_values_to_have_recent_data:
          column_name: date_day
          datepart: hour
          interval: 4

Step-by-step explanation.

1. source freshness runs before the DAG. It checks max(loaded_at_field) on the raw source table and errors if too stale. Detects upstream ingestion stalls before dbt bothers to rebuild anything downstream.
2. expect_row_values_to_have_recent_data on the staging model runs after the staging model is built. It checks max(created_at) — the source-of-record timestamp — against the current time. Detects the case where ingestion is running fine (source_freshness passes) but the source's own upstream (Stripe API) is delayed.
3. The mart-level recency check on fct_revenue_daily uses date_day (a truncated date). The threshold is 4 hours because the mart is typically refreshed hourly and a 4-hour gap indicates a scheduling problem.
4. The three checks form a "defence in depth" against staleness. Each one catches a different failure mode: ingestion lag (source freshness), upstream-source lag (staging recency), mart-refresh lag (mart recency).
5. Without the staging-level recency check, a Stripe API outage looks fine to source freshness (data is landing in raw) but the actual charges are hours old. Mart numbers would be silently stale until someone noticed. The staging-level check is the missing safety net.

Output.

Layer	Detects	SLA
source freshness	Ingestion lag (no new raw rows)	15 min
staging recency	Upstream API lag (rows land but content is old)	30 min
mart recency	Refresh-schedule lag (mart didn't rebuild)	4 hours

Rule of thumb. Layer three levels of freshness checks: source (source freshness), staging (expect_row_values_to_have_recent_data on the source-of-record timestamp), and mart (recency on the mart's own grain column). One layer catches one failure mode; three layers catch every realistic pipeline stall.

Senior interview question on layering dbt_expectations tests

A senior interviewer might ask: "Design the dbt_expectations test coverage for a fct_orders fact table that feeds a downstream revenue dashboard. What tests do you add at column level, table level, and cross-table level, and how do you decide which are warn-only vs error?"

Solution Using a four-tier test taxonomy — column, table, cross-table, warn-vs-error

# models/marts/fct_orders.yml — full test coverage
version: 2

models:
  - name: fct_orders
    description: Grain — one row per completed order.

    # Table-level: volume, freshness, uniqueness of composite
    tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 10000
          max_value: 500000
          row_condition: "cast(ordered_at as date) = current_date"
          severity: error

      - dbt_expectations.expect_compound_columns_to_be_unique:
          column_list: [order_id, user_id]
          severity: error

      - dbt_expectations.expect_row_values_to_have_recent_data:
          column_name: ordered_at
          datepart: minute
          interval: 60
          severity: warn      # warn-only during known API-blip windows

    columns:
      - name: order_key
        tests:
          - unique
          - not_null

      - name: amount_usd
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000
              severity: error
          - dbt_expectations.expect_column_mean_to_be_between:
              min_value: 20
              max_value: 500
              severity: warn    # distribution drift → warn, not error

      - name: currency_code
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_in_set:
              value_set: ['USD', 'EUR', 'GBP', 'JPY', 'CAD', 'AUD']

      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_users')
              field: user_id

      - name: ordered_at
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: "'2020-01-01'::timestamp"
              max_value: "current_timestamp"

Step-by-step trace.

Tier	Test	Severity	Catches
Table (volume)	expect_table_row_count_to_be_between	error	ETL blew up or produced 0 rows
Table (uniqueness)	expect_compound_columns_to_be_unique	error	Join accidentally exploded the grain
Table (freshness)	expect_row_values_to_have_recent_data	warn	Upstream API lag; alert but don't fail deploy
Column (range)	expect_column_values_to_be_between (amount_usd)	error	Negative / absurd values → hard fail
Column (drift)	expect_column_mean_to_be_between (amount_usd)	warn	Distribution shift → investigate but continue
Column (enum)	expect_column_values_to_be_in_set (currency_code)	error	Unknown currency → hard fail
Cross-table	relationships (user_id → dim_users)	error	Orphan user_id → hard fail

After the tests are added, every dbt test run against fct_orders runs these 8 tests in parallel (dbt schedules test compilation and execution). Total runtime on Snowflake for a 10M-row table is 5–15 seconds. The CI pipeline can distinguish error (fail the build) from warn (log but succeed), so freshness drift generates a Slack alert without blocking the deploy — the correct tradeoff for signals that need a human eye but not a build stop.

Output:

Metric	Coverage
Table-level tests	3 (volume, uniqueness, freshness)
Column-level range/set tests	4 (amount, currency, timestamp, order_key)
Distribution tests	1 (mean drift on amount_usd)
Cross-table relationships	1 (user_id → dim_users)
Total tests	9
Runtime on 10M rows	~10 s

Why this works — concept by concept:

• Four-tier taxonomy — every fact table gets (a) volume test, (b) uniqueness of composite, (c) freshness, (d) per-column range or enum. The taxonomy is prescriptive; teams stop debating "what tests do we add?" and start shipping.
• Severity split — error vs warn — hard invariants (negative amount, unknown currency, orphan user_id) are severity: error; soft signals (distribution drift, temporary API lag) are severity: warn. The build stops for hard invariants; humans get paged for soft signals.
• row_condition guards — row_condition: "cast(ordered_at as date) = current_date" scopes the volume test to today's rows only. Historical rows are ignored (which is what you want — you're testing today's ingestion, not the whole table).
• Cross-table relationships — dbt core's relationships test catches orphan foreign keys. Complements dbt_expectations by covering the referential-integrity axis that dbt_expectations doesn't specialise in.
• Cost — 9 tests, ~50 lines of YAML, ~10 seconds of Snowflake runtime per build. The catch — one silently-corrupt fact table costs a week of downstream re-work; the tests pay for themselves the first time they fire.

SQL
Topic — sql
SQL data-quality assertion problems

Practice →

ETL Topic — etl ETL problems on freshness and volume monitoring

Practice →

---

4. dbt-codegen + dbt-osmosis — reduce boilerplate

codegen writes the YAML and SQL you'd otherwise type; osmosis propagates docs and tests through the DAG

The mental model in one line: dbt-codegen inspects the warehouse and emits the sources.yml, model YAML, and base staging SQL you would otherwise type by hand — a one-shot productivity multiplier when onboarding a new table with 40 columns; dbt-osmosis walks your DAG at rest and propagates column descriptions, meta tags, and tests upstream and downstream so the docs you wrote once appear everywhere the column touches. Together they close the two biggest boilerplate loops in a mature dbt project: (a) the "we just added a 40-column source, write out all the YAML" pain, and (b) the "column email is documented on stg_users but not on the four downstream models that select it" doc-rot problem.

codegen — the three macros that matter.

• generate_source. Inspects the warehouse schema, then emits a sources.yml block listing every table with its columns and inferred types. Used at project bootstrap to onboard a new schema.
• generate_model_yaml. Given a materialised model, emits the YAML block with columns, types, and empty descriptions. Used after building a new staging model to seed its docs.
• generate_base_model. Given a source table, emits a first-cut staging SQL that selects every column with basic aliasing. Used to bootstrap stg_{table}.sql files.

dbt-osmosis — the three commands that matter.

• dbt-osmosis yaml refactor. Walks the DAG and rewrites every schema.yml to reflect the current SQL — adds new columns, removes columns no longer in the SELECT, aligns column ordering to the SQL. Fixes drift between YAML docs and the models they describe.
• dbt-osmosis yaml document. Propagates column descriptions: if stg_orders.order_id has a description and fct_orders.order_id doesn't, osmosis copies it downstream. The "documented once, appears everywhere" pattern.
• dbt-osmosis yaml organize. Reorders columns in schema.yml to match the SQL SELECT order. Cosmetic but stops PR-noise from schema.yml drifting.

When to use codegen vs osmosis.

• codegen — one-shot bootstrap. Run codegen when onboarding a new source or a new staging model. Copy the emitted YAML into your project once. Delete the codegen invocation.
• osmosis — recurring maintenance. Run osmosis in CI or as a pre-commit hook. It's the "keep the docs and YAML aligned with the SQL as the project evolves" loop, not a one-shot.
• codegen is a package. Install via packages.yml.
• osmosis is a Python CLI. Install via pip install dbt-osmosis. Not a dbt package in the packages.yml sense — but referenced together with the other three because it fills the same "reduce boilerplate" niche.

The doc-rot problem osmosis fixes.

• The pattern. stg_users.email is documented (description: user email, RFC-5322 format). int_users_enriched.email selects it. dim_users.email selects that. mart_customer_ltv.email selects that. The description appears on stg_users and nowhere else — three downstream models silently lose the doc.
• Without osmosis. Analytics engineers manually copy the description into each downstream schema.yml. Nobody does this consistently. The docs decay.
• With osmosis. dbt-osmosis yaml document walks the DAG and copies the description onto every downstream schema.yml entry for the same column name. One command; consistent docs everywhere.

Common interview probes on codegen and osmosis.

• "How do you onboard a new source with 40 columns without typing every schema.yml entry?" — codegen.generate_source + codegen.generate_model_yaml.
• "How do you keep column descriptions consistent across the DAG?" — dbt-osmosis yaml document.
• "How do you catch schema drift between YAML docs and the actual model SQL?" — dbt-osmosis yaml refactor (drift), dbt test (test coverage).
• "Do you run osmosis in CI or locally?" — both. Pre-commit hook locally for fast feedback; CI check to catch anyone who bypassed the hook.

Worked example — onboarding a 20-column table with generate_source

Detailed explanation. A new SaaS integration lands 15 tables into raw.hubspot. Onboarding each table into dbt normally means typing out a sources.yml block with every column, type, description, and freshness config. For 15 tables × 30 columns average = 450 YAML lines. codegen.generate_source cuts that to a 3-minute compile step.

• The manual pain. 15 tables × 30 columns = 450 YAML entries; each entry needs a description; missing an entry means downstream source(...) calls silently reference an undeclared column.
• The codegen shortcut. Run one macro; get back the fully-populated sources.yml; paste into your project; fill in descriptions.

Question. Bootstrap a sources.yml for the entire raw.hubspot schema using codegen.generate_source. Show the invocation, the emitted YAML, and the follow-up steps to make it production-ready.

Input.

Schema	Tables	Columns per table (avg)
raw.hubspot	15	30
Manual YAML lines needed	—	~450

Code.

# packages.yml
packages:
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]

dbt deps

-- Run this as a dbt operation
-- dbt run-operation generate_source --args '{schema_name: hubspot, database_name: raw, generate_columns: true, include_descriptions: true}'

# Emitted YAML (paste into models/sources/hubspot.yml)
version: 2

sources:
  - name: hubspot
    database: raw
    schema: hubspot
    tables:
      - name: contacts
        description: ""
        columns:
          - name: contact_id
            description: ""
            data_type: number
          - name: email
            description: ""
            data_type: varchar
          - name: created_at
            description: ""
            data_type: timestamp_ntz
          - name: updated_at
            description: ""
            data_type: timestamp_ntz
          # ... 26 more columns
      - name: deals
        description: ""
        columns:
          - name: deal_id
            description: ""
            data_type: number
          # ... 29 more columns
      # ... 13 more tables

Step-by-step explanation.

1. generate_source accepts schema_name and database_name (or defaults to the target's database). It runs an information_schema.columns query against the warehouse and returns the fully-populated YAML as a dbt run-operation output.
2. Paste the emitted YAML into models/sources/hubspot.yml. The file has every table, every column, and correct types — but empty descriptions.
3. Fill in descriptions as a follow-up PR. Split the work across the team: 15 tables / 3 engineers = 5 tables each; each engineer documents their assigned tables from the Hubspot API docs. Multiple parallel PRs; no single-engineer bottleneck.
4. Add per-table loaded_at_field and freshness blocks as another follow-up PR. codegen doesn't infer freshness (it doesn't know your ingestion cadence); this is the "fill in the SLA" step.
5. Total onboarding time: 3 minutes to run codegen; 2 hours to describe the 15 tables; 30 minutes to add freshness. Compared to hand-typing all 450 lines (typically 4–6 senior-hours), the win is 3–5× and — more importantly — the emitted YAML has zero typos.

Output.

Step	Manual	With codegen
Type YAML skeleton	4–6 hours	3 minutes
Add descriptions	2 hours	2 hours
Add freshness	30 minutes	30 minutes
Typo risk	high (450 lines)	none (inspected by SQL)
Total	6–8 hours	2.5 hours

Rule of thumb. Never type a sources.yml block by hand for more than 5 columns. Run codegen.generate_source at bootstrap; paste; document; ship. The 3-minute macro invocation replaces 4+ hours of typo-prone typing.

Worked example — bootstrapping a staging model with generate_base_model

Detailed explanation. After a source is declared, the next step is to build the staging model — typically a 1:1 select against the source with light renaming (snake_case, _at on timestamps) and cast typing. Hand-writing this for a 30-column source is 30 lines of grunt work. codegen.generate_base_model emits the first-cut SQL you edit and check in.

• The pattern. Every staging model starts as SELECT column_a AS renamed_a, column_b AS renamed_b, ... FROM {{ source(...) }}.
• The codegen shortcut. generate_base_model reads the source, emits the SELECT with sensible aliasing, and you tweak.

Question. Generate the staging model for source('hubspot', 'contacts') (30 columns) using codegen.generate_base_model, then walk through the edits a senior engineer typically applies before checking it in.

Input.

Source	Columns	Casing convention
source('hubspot', 'contacts')	30	snake_case, timestamps end in _at

Code.

-- Run this as a dbt operation
-- dbt run-operation generate_base_model --args '{source_name: hubspot, table_name: contacts, leading_commas: false, materialized: view}'

-- Emitted SQL (paste into models/staging/hubspot/stg_hubspot__contacts.sql)
with source as (
    select * from {{ source('hubspot', 'contacts') }}
),

renamed as (
    select
        contact_id,
        email,
        first_name,
        last_name,
        phone,
        company,
        created_at,
        updated_at,
        hs_lead_status,
        hs_lifecyclestage,
        -- ... 20 more columns
    from source
)

select * from renamed

-- Senior-engineer edits (final model)
with source as (
    select * from {{ source('hubspot', 'contacts') }}
),

renamed as (
    select
        contact_id                          as contact_id,
        lower(trim(email))                  as email,
        first_name                          as first_name,
        last_name                           as last_name,
        phone                               as phone_e164,
        company                             as company_name,
        created_at                          as created_at,
        updated_at                          as updated_at,
        hs_lead_status                      as lead_status,
        hs_lifecyclestage                   as lifecycle_stage,
        -- Drop the 20 columns we don't need in staging
        _synced_at                          as _synced_at
    from source
    where deleted = false
)

select
    {{ dbt_utils.generate_surrogate_key(['contact_id']) }} as contact_sk,
    *
from renamed

Step-by-step explanation.

1. generate_base_model reads the source columns and emits a SELECT column_name FROM {{ source(...) }} skeleton. Every column shows up in the select; no columns are dropped or renamed by default.
2. The senior engineer applies four categories of edits: (a) rename hs_-prefixed Hubspot-native columns to business-neutral names, (b) apply cleanup (lower(trim(email))), (c) drop columns you don't need downstream, (d) add a where deleted = false filter for soft-deletes.
3. The final touch is prepending a surrogate key via dbt_utils.generate_surrogate_key(['contact_id']). The codegen output doesn't know about your project conventions; the staging model does.
4. The emitted file goes through code review like any other model. The reviewer's checklist: are the aliases business-neutral? Are unused columns dropped? Is the soft-delete filter applied? Is the surrogate key present?
5. Time saved: 5 minutes to run codegen and paste; 15 minutes to apply the four edit categories. Compared to hand-writing the 40-line staging model, the win is 3–5× and — again — no typos in the column list.

Output.

Task	Manual	With codegen
Type the column list	10 minutes	3 minutes (macro output)
Apply renames and casts	10 minutes	10 minutes
Add filter and surrogate key	5 minutes	5 minutes
Total	25 minutes	18 minutes
Typo risk	moderate	none

Rule of thumb. Use generate_base_model for the first-cut SQL of every staging model. It handles the mechanical part; you handle the editorial part.

Worked example — propagating column descriptions with dbt-osmosis yaml document

Detailed explanation. The doc-rot problem in one command. A project has stg_orders.order_id documented; int_orders_enriched.order_id, fct_orders.order_id, and mart_finance_daily.order_id are undocumented. dbt-osmosis yaml document walks the DAG, finds each undocumented column whose upstream neighbour has a description, and copies the description downstream.

• The rule. If a column with the same name has a description upstream and no description downstream, osmosis copies it.
• The safety. Osmosis is idempotent — running it twice produces the same result. It never overwrites an existing description.

Question. Show a before/after snapshot of a 4-model DAG where stg_orders.order_id is documented but nothing downstream is, then run dbt-osmosis yaml document and compare.

Input.

Model	Column	Before	After
stg_orders	order_id	"Primary key of the raw order table."	(unchanged)
int_orders_enriched	order_id	(blank)	"Primary key of the raw order table."
fct_orders	order_id	(blank)	"Primary key of the raw order table."
mart_finance_daily	order_id	(blank)	"Primary key of the raw order table."

Code.

pip install dbt-osmosis

# Configure osmosis to inherit descriptions
# dbt_project.yml
models:
  my_project:
    +dbt-osmosis: "_schema.yml"

# Run the docs propagation
dbt-osmosis yaml document --project-dir . --profiles-dir ~/.dbt/

# Before — models/staging/stg_orders.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        description: "Primary key of the raw order table."

# Before — models/marts/fct_orders.yml
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        # (no description)

# After — models/marts/fct_orders.yml (rewritten by osmosis)
version: 2
models:
  - name: fct_orders
    columns:
      - name: order_id
        description: "Primary key of the raw order table."

Step-by-step explanation.

1. Osmosis parses the project's manifest (built from dbt compile) to construct the DAG and the column-lineage graph. Every column in every model gets its lineage traced back to the closest documented ancestor.
2. For each undocumented column in a downstream model, osmosis finds the most-immediate documented upstream column with the same name and copies the description into the schema.yml.
3. Idempotency: if you run osmosis twice, the second run is a no-op — every description already exists. This makes it safe to run in a pre-commit hook or CI.
4. The propagation follows column names by default. If your staging renames id to order_id, downstream models that select order_id inherit stg_orders.order_id's description — but only after the rename. Osmosis doesn't chase renamed columns unless you provide an explicit alias map.
5. Meta tags (meta: {pii: true, contains_email: true}) propagate the same way. If stg_users.email has meta: {pii: true}, every downstream model with an email column gets the same meta after osmosis runs — enabling one-line PII audits across the DAG.

Output.

Model	order_id description (before)	order_id description (after)
stg_orders	"Primary key of the raw order table."	(unchanged)
int_orders_enriched	(blank)	inherited
fct_orders	(blank)	inherited
mart_finance_daily	(blank)	inherited

Rule of thumb. Run dbt-osmosis yaml document in a pre-commit hook and in CI. The upfront cost is 10 seconds per run; the payoff is a project where every column's docs, meta tags, and (optionally) tests appear consistently everywhere the column touches.

Senior interview question on codegen + osmosis rollout

A senior interviewer might ask: "You're onboarding a new 15-table Salesforce integration into an existing dbt project with 300 models. Walk me through your rollout plan using codegen and osmosis, what you generate vs write by hand, and how you keep the docs consistent as the project evolves."

Solution Using a 5-step bootstrap + a pre-commit hook

# Step 1 — install packages
cat >> packages.yml <<'EOF'
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]
EOF

pip install dbt-osmosis pre-commit
dbt deps

# Step 2 — generate sources.yml
dbt run-operation generate_source \
  --args '{schema_name: salesforce, database_name: raw, generate_columns: true, include_descriptions: true}' \
  > models/sources/salesforce.yml

# Step 3 — generate staging models (loop over the 15 tables)
for table in accounts contacts opportunities leads campaigns cases activities \
             tasks notes attachments users products pricebook opportunity_line_items quotes
do
  dbt run-operation generate_base_model \
    --args "{source_name: salesforce, table_name: $table, materialized: view}" \
    > models/staging/salesforce/stg_salesforce__$table.sql
done

# Step 4 — generate model YAML for each staging model
for table in accounts contacts opportunities leads campaigns cases activities \
             tasks notes attachments users products pricebook opportunity_line_items quotes
do
  dbt run-operation generate_model_yaml \
    --args "{model_names: [stg_salesforce__$table]}" \
    >> models/staging/salesforce/_stg_salesforce__models.yml
done

# Step 5 — pre-commit hook keeps the docs propagated
# .pre-commit-config.yaml
repos:
  - repo: local
    hooks:
      - id: dbt-osmosis-yaml-refactor
        name: dbt-osmosis yaml refactor
        entry: dbt-osmosis yaml refactor --project-dir . --profiles-dir ~/.dbt/
        language: system
        files: '^(models/|dbt_project.yml)'
        pass_filenames: false

      - id: dbt-osmosis-yaml-document
        name: dbt-osmosis yaml document
        entry: dbt-osmosis yaml document --project-dir . --profiles-dir ~/.dbt/
        language: system
        files: '^(models/|dbt_project.yml)'
        pass_filenames: false

Step-by-step trace.

Step	Command	Output
1	packages.yml + pip install	codegen + osmosis available
2	generate_source	Fully populated sources.yml (15 tables)
3	generate_base_model × 15	15 stg_salesforce__*.sql skeletons
4	generate_model_yaml × 15	Consolidated models.yml with columns per staging
5	pre-commit hook	Every commit runs osmosis refactor + document

After the bootstrap, the team edits the emitted files: (a) rename business-neutral columns in staging SQL, (b) apply soft-delete filters, (c) add surrogate keys via dbt_utils.generate_surrogate_key, (d) document the first-mention columns in staging YAML. The pre-commit hook then propagates docs downstream as the DAG evolves. Total bootstrap time: 4 senior-engineer hours; without codegen + osmosis, the same rollout is 2–3 senior-engineer days.

Output:

Metric	Manual	With codegen + osmosis
Time to onboard 15 tables	2–3 days	4 hours
Typo risk in schema.yml	high	none
Doc propagation to downstream	manual	automatic
Ongoing maintenance	manual	pre-commit hook
Doc-rot risk	high	low

Why this works — concept by concept:

• One-shot bootstrap — codegen replaces the mechanical typing at project boot. Run it once per new source; delete the invocation from the runbook.
• Recurring propagation — osmosis replaces the ongoing "keep the docs aligned" toil. Wire it into pre-commit + CI so every PR ships with consistent docs.
• Split responsibility — codegen writes the structure; humans write the meaning. The macro can list columns; only a human knows what the columns represent.
• Idempotence__ — osmosis is safe to run repeatedly; the pre-commit hook doesn't fight with itself. Every PR converges to the same docs state regardless of ordering.
• Cost — one PR to install; a pre-commit hook running in ~10 seconds; every quarterly source onboarding takes hours instead of days. The compounding productivity gain scales with project size.

SQL
Topic — sql
SQL scaffolding and source-onboarding problems

Practice →

ETL Topic — etl ETL problems on dbt DAG maintenance

Practice →

---

5. Package management + versioning discipline

packages.yml is a contract — pin the version range, audit before upgrading, choose Hub vs Git deliberately

The mental model in one line: packages.yml is a dependency contract; the senior signal is (a) pinning every package to a version range that admits patch fixes and blocks breaking-major upgrades, (b) running an explicit upgrade blast-radius audit before every version bump, and (c) choosing Hub vs Git vs local per-package based on where the package is published and how much control you need over the exact ref. Loose pins (">=1.0.0") are how a Monday-morning dbt deps pulls a fresh major and silently changes hash values across the fleet.

Where packages live — Hub, Git, local, private.

• Hub (hub.getdbt.com). The canonical registry. Entries use package: {org}/{name} and version: {semver-range}. dbt looks up the tag matching the range on Hub, clones from the linked git URL, checks out the tag. Best for public community packages.
• Git direct. Any git URL (git://github.com/foo/bar.git) with revision: {tag|branch|sha}. Used for packages not on Hub, unreleased-fix commits, or internal packages in a private git remote.
• Local. A relative path (local: ../shared-macros). Copies (or symlinks) the local directory into dbt_packages/. Used in monorepos to share macros across sibling dbt projects.
• Private registries. Enterprise HTTP registries that proxy Hub with custom auth. Config-only difference; same version-range semantics.

Version pinning — three levels of strictness.

• Loose. version: ">=1.0.0". Admits every new major. Guaranteed to break eventually.
• Range (recommended). version: [">=1.1.0", "<2.0.0"]. Admits patches and minors in the current major; blocks the next major. This is the canonical dbt pin.
• Exact. version: "1.1.1". Locks to one release. Safest against upgrade drift, but you must manually bump for every patch fix.

The upgrade blast-radius audit.

• Step 1. Read the package's changelog for the range you're bumping into. Note every "Breaking change" or "Deprecation" entry.
• Step 2. For each breaking change, grep your project for usages of the affected macro / test / config. Zero hits → safe. Non-zero hits → migrate.
• Step 3. Bump the pin in a feature branch. Run dbt deps. Run dbt compile — compile-time errors surface renamed macros immediately.
• Step 4. Run dbt build --select changed (or the full DAG) in a dev environment. Any test-red or SQL-diff points at semantic changes not caught by compile.
• Step 5. Merge the pin bump with the migration diff in one atomic PR. Never bump the pin without the migration in the same commit.

Monorepo package sharing.

• The pattern. A monorepo hosts multiple dbt projects (analytics/, finance/, marketing/) that share a set of internal macros. The macros live in a fourth project (shared_macros/) referenced by the other three via packages: - local: ../shared_macros.
• The alternative. Each project has its own copy of the macros — three copies, drift risk.
• The winner. The local: package. Every project sees the same version of the shared macros; changes propagate on the next dbt deps in each project.

Mesh + dbt Cloud package sharing.

• In 2026. dbt Mesh introduces cross-project references ({{ ref('other_project', 'model_name') }}) that partially obsolete the "shared macros package" pattern for model sharing. Macros still ride via packages.yml — Mesh is model-level, not macro-level.
• The senior signal. Knowing where the boundary is: Mesh for models; packages for macros and tests.

Common interview probes on package management.

• "How do you pin package versions?" — range [">=X.Y.0", "<X.(Y+1).0"]; audit changelog before every bump.
• "What's the upgrade blast-radius audit?" — changelog read + project-wide grep + compile + build + test.
• "Hub vs Git vs local?" — Hub for public packages; Git for unreleased fixes or private; local for monorepo shared macros.
• "How do you share macros across sibling dbt projects?" — local package, same-repo path.

Worked example — a canonical packages.yml with pin discipline

Detailed explanation. The senior baseline packages.yml — four packages, each pinned to a range that admits patches and minors but blocks the next major. Anything looser is a Monday-morning break waiting to happen; anything stricter creates unnecessary friction for patch upgrades.

• The four packages. dbt_utils, dbt_expectations, codegen, dbt_project_evaluator (optional).
• The pin pattern. [">=X.Y.0", "<X.(Y+1).0"] for the current major.
• The changelog discipline. Every pin bump is a PR with the diff of the package changelog attached.

Question. Write a production-grade packages.yml for a mid-sized dbt project, comment each pin's reasoning, and outline the review checklist for a pin bump PR.

Input.

Package	Current version	Pin
dbt-labs/dbt_utils	1.1.1	[">=1.1.0", "<2.0.0"]
calogica/dbt_expectations	0.10.4	[">=0.10.0", "<0.11.0"]
dbt-labs/codegen	0.12.1	[">=0.12.0", "<0.13.0"]
dbt-labs/dbt_project_evaluator	0.9.0	[">=0.9.0", "<0.10.0"]

Code.

# packages.yml
packages:
  # Utility macros — surrogate keys, spines, pivots, dedups, tests
  # Pin admits 1.1.x patches; blocks 2.0.0 (breaking).
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

  # Data-quality tests (Great Expectations vocabulary)
  # Pre-1.0; pin to the current minor to avoid pre-release drift.
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]

  # Code-generation macros — sources.yml, model YAML, base SQL
  # Pin admits 0.12.x patches; blocks 0.13.0.
  - package: dbt-labs/codegen
    version: [">=0.12.0", "<0.13.0"]

  # Optional — project-linting for style, structure, DAG shape
  # Add only if wired into CI; skip if unused.
  - package: dbt-labs/dbt_project_evaluator
    version: [">=0.9.0", "<0.10.0"]

# .github/PULL_REQUEST_TEMPLATE/package-bump.md
## Package upgrade blast-radius review

- [ ] Package: `<name>` from `<old>` to `<new>`
- [ ] Read changelog: <link to release notes>
- [ ] Listed all breaking changes in this PR description
- [ ] Grep project for usages of every deprecated macro/test/config
- [ ] Ran `dbt compile` — no compile-time errors
- [ ] Ran `dbt build --select changed` in dev — no test-red
- [ ] Ran output-diff query on affected surrogate keys — no unexpected changes
- [ ] Attached diff to PR description

Step-by-step explanation.

1. Each pin uses the range [">=X.Y.0", "<X.(Y+1).0"] for its current major. This admits patch (X.Y.1, X.Y.2, ...) upgrades on the next dbt deps — critical bug fixes get pulled automatically — and blocks the next minor or major until a human reviews and bumps the pin.
2. dbt_expectations is pre-1.0, so the pin uses the current minor as the boundary. Pre-1.0 packages treat every minor as breaking; the range [">=0.10.0", "<0.11.0"] reflects that.
3. The commented reasoning in packages.yml matters — it tells the next engineer why the pin is what it is. Without the comment, "who put <2.0.0 there?" becomes a git-blame archaeology exercise.
4. The PR template for package bumps codifies the audit. Every pin-change PR must tick every box; unchecked boxes block review. The checklist is the friction that prevents blind dbt deps --upgrade.
5. The optional dbt_project_evaluator package earns its slot only if the CI pipeline actively invokes it. Installing a linting package "for future use" fragments the dependency graph without a paid return — see the anti-pattern in section 1.

Output.

Package	Pin	Admits	Blocks
dbt_utils	[">=1.1.0", "<2.0.0"]	1.1.x, 1.2.x, 1.3.x, ..., 1.9.x	2.0.0
dbt_expectations	[">=0.10.0", "<0.11.0"]	0.10.x	0.11.0
codegen	[">=0.12.0", "<0.13.0"]	0.12.x	0.13.0
dbt_project_evaluator	[">=0.9.0", "<0.10.0"]	0.9.x	0.10.0

Rule of thumb. Every packages.yml entry gets a range pin and a comment explaining the range. Every pin bump gets a PR ticking every audit box. Anything looser is a production incident waiting for the Monday morning it decides to fire.

Worked example — the upgrade blast-radius audit end-to-end

Detailed explanation. A senior engineer bumps dbt_utils from 1.1.1 to 1.2.0. The changelog lists three entries: (a) new macro dbt_utils.escape_single_quotes (additive, safe), (b) deprecation of dbt_utils.type_string in favour of dbt.type_string (dbt-core alignment), (c) subtle change to deduplicate on Redshift for edge-case null tiebreaking. Walk through the full audit.

• The three changelog entries. One additive, one deprecation, one semantic.
• The audit for each. Grep for usages; assess risk; migrate before merging the pin bump.

Question. Perform the blast-radius audit for the dbt_utils 1.1.1 → 1.2.0 upgrade on a project with 300 models. Show every command; show the migration diff.

Input.

Change	Category	Risk
New escape_single_quotes	additive	none
Deprecate dbt_utils.type_string	deprecation	low (still works, warns)
deduplicate null-tiebreak on Redshift	semantic	medium — audit needed

Code.

# Step 1 — grep for the deprecated macro
git grep -n "dbt_utils.type_string" -- '*.sql' '*.yml'
# models/marts/mart_finance.sql:12: cast(x as {{ dbt_utils.type_string() }})
# models/staging/stg_events.sql:8:  {{ dbt_utils.type_string() }}
# → 2 usages; migrate to dbt.type_string()

# Step 2 — grep for the affected macro (deduplicate)
git grep -n "dbt_utils.deduplicate" -- '*.sql'
# models/intermediate/int_orders_dedup.sql:5: {{ dbt_utils.deduplicate(...) }}
# models/intermediate/int_users_dedup.sql:5: {{ dbt_utils.deduplicate(...) }}
# → 2 usages; only Redshift is affected; project target is Snowflake → safe

# Step 3 — bump the pin in a feature branch
git checkout -b bump/dbt_utils-1.2.0

# packages.yml — diff
-  - package: dbt-labs/dbt_utils
-    version: [">=1.1.0", "<2.0.0"]
+  - package: dbt-labs/dbt_utils
+    version: [">=1.2.0", "<2.0.0"]

-- Step 4 — migrate the deprecated calls
-- models/marts/mart_finance.sql — before
select cast(x as {{ dbt_utils.type_string() }}) from ...

-- models/marts/mart_finance.sql — after
select cast(x as {{ dbt.type_string() }}) from ...

# Step 5 — run the audit
dbt deps
dbt compile              # zero errors → deprecation cleanup complete
dbt build --select fct_orders int_orders_dedup int_users_dedup   # zero test-red

-- Step 6 — regression check on any surrogate-key output
select
    order_key,
    order_id,
    user_id
from {{ ref('fct_orders') }}
except
select
    order_key,
    order_id,
    user_id
from {{ ref('fct_orders_prev_version') }}
limit 100
-- (0 rows) → surrogate keys unchanged; safe to merge

Step-by-step explanation.

1. Step 1: grep for the deprecated macro across all .sql and .yml files. Two hits — migrate them in the same PR. This is the "explicit deprecation" case; the changelog told us; we deal with it.
2. Step 2: grep for the semantically-changed macro. Two usages of deduplicate — but the change only affects Redshift, and our target is Snowflake. Zero risk to us; noted for the future if we ever add Redshift.
3. Step 3: bump the pin in a feature branch. Don't touch main until the audit is complete.
4. Step 4: migrate the deprecated calls to their new equivalents. dbt_utils.type_string() → dbt.type_string(). The change is mechanical; the risk is low; the diff is small.
5. Step 5: run dbt compile (catches compile-time issues) and dbt build --select on the affected models (catches semantic issues via tests). Both green → the audit is clean.
6. Step 6 (belt-and-braces): run a regression query against the surrogate-key output of any downstream fact model. Zero diff → the semantic changes upstream didn't perturb the downstream keys. Now merge.

Output.

Audit step	Command	Result
1. Grep deprecated	git grep dbt_utils.type_string	2 usages → migrate
2. Grep semantic change	git grep dbt_utils.deduplicate	2 usages, but our target=Snowflake → safe
3. Bump pin	packages.yml diff	change 1.1.0 → 1.2.0
4. Migrate	mechanical edits	2 lines changed
5. Compile + build	dbt compile && dbt build --select changed	green
6. Regression check	EXCEPT query on surrogate keys	0 rows

Rule of thumb. Every dbt deps --upgrade on production is preceded by the six-step audit above. If any step is red, do not merge — go back and understand the semantic change. Blind upgrades are how a Monday morning becomes a Monday incident.

Worked example — sharing macros across a monorepo with a local package

Detailed explanation. A monorepo hosts three dbt projects (analytics/, finance/, marketing/) that share a set of company-specific macros — the internal mask_pii(col), snowflake_warehouse_switch(name), and slack_alert_on_failure() helpers. Copying the macros into each project creates drift risk. The senior fix is a fourth "package" project (shared_macros/) referenced by the other three via local:.

• The layout. repo-root/{analytics,finance,marketing,shared_macros}/.
• The reference. packages: - local: ../shared_macros in each project's packages.yml.
• The result. One source of truth for the shared macros; changes propagate on the next dbt deps in each project.

Question. Set up the monorepo shared-macros pattern. Show the directory layout, the packages.yml in each project, and the dbt_project.yml for the shared package.

Input.

Project	Location	Shared macros needed
analytics	/analytics	mask_pii, slack_alert_on_failure
finance	/finance	mask_pii, snowflake_warehouse_switch
marketing	/marketing	mask_pii
shared_macros	/shared_macros	(owns all three)

Code.

# Directory layout
repo-root/
├── analytics/
│   ├── dbt_project.yml
│   ├── packages.yml         # local: ../shared_macros
│   └── models/
├── finance/
│   ├── dbt_project.yml
│   ├── packages.yml         # local: ../shared_macros
│   └── models/
├── marketing/
│   ├── dbt_project.yml
│   ├── packages.yml         # local: ../shared_macros
│   └── models/
└── shared_macros/
    ├── dbt_project.yml      # name: shared_macros
    └── macros/
        ├── mask_pii.sql
        ├── snowflake_warehouse_switch.sql
        └── slack_alert_on_failure.sql

# shared_macros/dbt_project.yml
name: 'shared_macros'
version: '1.0.0'
config-version: 2
require-dbt-version: [">=1.6.0", "<2.0.0"]

# No models — this is a macro-only package

# analytics/packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]
  - package: calogica/dbt_expectations
    version: [">=0.10.0", "<0.11.0"]
  - local: ../shared_macros

-- macros in each project are addressable as shared_macros.<macro_name>
select
    {{ shared_macros.mask_pii('email') }} as email_masked,
    order_id,
    amount_usd
from {{ ref('stg_orders') }}

Step-by-step explanation.

1. shared_macros/ is a fully-formed dbt package: it has a dbt_project.yml declaring name: shared_macros, a macros/ directory with the shared logic, and no models. It's a macro-only package.
2. Each sibling project's packages.yml references it via local: ../shared_macros. The dbt deps step copies (or symlinks, depending on version) the shared_macros/ directory into each project's dbt_packages/.
3. Every macro in shared_macros/macros/*.sql is callable in any of the three projects as {{ shared_macros.macro_name(args) }}. The addressing is the same across all sibling projects.
4. When the macros need to change (bug fix, new masking rule, new alert channel), the edit lives in shared_macros/macros/ — one place. On the next dbt deps in each sibling project, the new version propagates.
5. The alternative — copying the macros into each project — creates a drift graveyard. Two months in, analytics.mask_pii and finance.mask_pii have subtly different implementations because someone patched one and not the other. The local-package pattern makes this impossible by construction.

Output.

Layer	Owner	Reference
shared_macros/macros/mask_pii.sql	source of truth	one file
analytics/dbt_packages/shared_macros/macros/mask_pii.sql	dbt deps copy	regenerated
finance/dbt_packages/shared_macros/macros/mask_pii.sql	dbt deps copy	regenerated
marketing/dbt_packages/shared_macros/macros/mask_pii.sql	dbt deps copy	regenerated

Rule of thumb. In a monorepo with multiple dbt projects, always factor shared macros into a local: package. Never copy-paste macros between sibling projects — the drift is guaranteed within months.

Senior interview question on package management discipline

A senior interviewer might ask: "You inherit a dbt project with a packages.yml that has 12 packages, 8 with loose >=X.0.0 pins, no changelog audit in the git history, and a monthly dbt deps that occasionally breaks CI. Design the first-week hardening plan."

Solution Using a five-step hardening plan

Package-management hardening plan
==================================

Day 1 — Inventory + risk categorisation
  - git grep every package's usage across models and macros
  - Categorise each package: keep (essential), evaluate (unclear ROI), remove (unused)
  - Delete packages with zero usages
  - Target: 4–6 packages remaining

Day 2 — Pin discipline
  - Rewrite every pin to [">=X.Y.0", "<X.(Y+1).0"] for the current major
  - Add a comment above each entry explaining the pin
  - Run `dbt deps` to confirm the range resolves; commit

Day 3 — Upgrade audit template
  - Add .github/PULL_REQUEST_TEMPLATE/package-bump.md
  - Checklist: read changelog, grep breaking-change macros, compile, build --select, regression diff
  - Wire required-review-approvals for any PR that touches packages.yml

Day 4 — CI gate on pin drift
  - Add a CI job that runs `dbt deps --lock` (if available) or `dbt deps --dry-run`
  - Fail the build if `dbt_packages/` differs from the checked-in state
  - Optional: cache dbt_packages/ per pin to speed CI

Day 5 — Monorepo audit (if applicable)
  - Identify macros duplicated across sibling projects
  - Create shared_macros/ local package
  - Refactor sibling projects to reference it
  - Delete duplicates

Step-by-step trace.

Day	Activity	Output
1	Inventory	12 → 5 packages after removing unused
2	Pin discipline	Every entry ranged and commented
3	Audit template	Package-bump PR checklist live
4	CI gate	Dependency drift caught on every PR
5	Monorepo	Shared macros unified into local package

After the five-day hardening, the packages.yml is a small, well-pinned, well-audited contract. The monthly dbt deps no longer surprises anyone; upgrades follow the audit checklist; the shared macros live in one place. The team's operational tax on packages drops from "a headache every month" to "a review every quarter."

Output:

Metric	Before	After
Packages installed	12	5
Loose pins	8	0
Package bumps with audit	0	100%
CI catches pin drift	no	yes
Duplicate macros across sibling projects	~15	0

Why this works — concept by concept:

• Inventory + prune — every installed package pays a dependency-graph tax. Removing unused packages shrinks the surface area before pin discipline is applied. Do the pruning first.
• Range pins with comments — the range admits patches (auto-pulled bug fixes) and blocks majors (surprise breakage). The comment tells the next engineer why.
• Audit template — the checklist is the friction that prevents blind upgrades. Making the audit a PR-template mechanic means every upgrade goes through the same steps; no shortcuts.
• CI gate — the CI job that fails on pin drift is the safety net for humans who forget the checklist. Belt-and-braces.
• Cost — five senior-engineer days for the rollout; the avoided cost of one "monthly dbt deps broke prod" incident pays for the entire week. O(1) per quarter for pin bumps after that.

SQL
Topic — sql
SQL dependency-management and versioning problems

Practice →

Optimization
Topic — optimization
Optimization problems on dbt project hygiene

Practice →

---

Cheat sheet — dbt package recipes

• The canonical packages.yml — six lines. - package: dbt-labs/dbt_utils + version: [">=1.1.0", "<2.0.0"]; - package: calogica/dbt_expectations + version: [">=0.10.0", "<0.11.0"]; - package: dbt-labs/codegen + version: [">=0.12.0", "<0.13.0"]. Add dbt-osmosis via pip install dbt-osmosis (CLI, not a Hub package). Four names cover the recurring analytics-engineering tax.
• dbt_utils — the six macros you'll use every week. generate_surrogate_key(['col1', 'col2']) for hash keys; date_spine(datepart='day', start_date=..., end_date=...) for gapless calendars; pivot(column=..., values=..., agg='sum', then_value=...) for long→wide; deduplicate(relation=..., partition_by=..., order_by=...) for row-number-filter dedupes; star(from=ref(...), except=[...]) for SELECT-*-EXCEPT projection; get_column_values(table=ref(...), column=...) for compile-time distincts. Every one is adapter-dispatched.
• dbt_expectations — the six tests every fact table needs. expect_table_row_count_to_be_between (volume); expect_row_values_to_have_recent_data (freshness); expect_column_values_to_be_between (numeric range); expect_column_values_to_be_in_set (enum); expect_column_values_to_match_regex (format); expect_compound_columns_to_be_unique (composite key). Layer with dbt core's unique, not_null, relationships.
• dbt-codegen — the three macros that bootstrap a source. generate_source emits the full sources.yml from information_schema.columns; generate_base_model emits a first-cut stg_*.sql selecting every column; generate_model_yaml emits the model YAML with columns and empty descriptions. Run once per new source; delete the invocations from the runbook.
• dbt-osmosis — the three commands that maintain the docs. dbt-osmosis yaml refactor rewrites schema.yml to match current SQL; dbt-osmosis yaml document propagates column descriptions downstream through the DAG; dbt-osmosis yaml organize reorders columns in YAML to match SQL. Wire into pre-commit + CI for idempotent runs.
• Adapter dispatch — the pattern. dbt_utils.generate_surrogate_key calls adapter.dispatch('default__generate_surrogate_key', 'dbt_utils'); dbt picks the snowflake__, bigquery__, postgres__, or default__ implementation based on the target. Override by defining {name}__generate_surrogate_key locally and registering in the dispatch config.
• Version pinning — the range pattern. [">=X.Y.0", "<X.(Y+1).0"] admits patch and minor upgrades in the current major; blocks the next major. For pre-1.0 packages, use [">=0.X.0", "<0.(X+1).0"] (minor is the boundary). Never use loose ">=X.0.0" pins in production.
• Upgrade blast-radius audit — the six steps. Read the changelog for every breaking change; grep the project for usages; bump the pin in a feature branch; run dbt compile (compile-time errors); run dbt build --select changed (semantic errors); regression-diff any hash outputs. All green → merge.
• Hub vs Git vs local — decision tree. Hub for public community packages (canonical); Git direct for unreleased-fix commits or private-repo packages; local for monorepo shared macros. Never mix modes for the same package.
• Monorepo shared macros — the pattern. Factor shared macros into a shared_macros/ project with its own dbt_project.yml. Reference from sibling projects via local: ../shared_macros. Address as {{ shared_macros.macro_name(...) }}.
• dbt_packages/ layout — the rules. Every installed package lives at dbt_packages/{name}/ where {name} is the package's own declared name (not the packages.yml string). Never edit files under dbt_packages/ — regenerated on every dbt deps. Override macros by defining same-named macros in your project.
• Precedence — three levels. Local macros in your project's macros/ folder > macros registered via dispatch config > macros in the leftmost package in packages.yml. Ambiguity is a code smell.
• CI gate — pin drift. Add a CI step that fails if dbt_packages/ differs from the checked-in lockfile state (or from a hash of packages.yml). Prevents anyone from shipping a divergent install.
• dbt-osmosis pre-commit hook. Two hooks: dbt-osmosis yaml refactor (structure) and dbt-osmosis yaml document (docs). Both idempotent; both run in under 30 seconds; both stop schema.yml from drifting away from the SQL.
• The four-package sanity check. dbt_utils (macros) + dbt_expectations (tests) + codegen (scaffold) + osmosis (docs). Anything more must clear the ROI bar: does it save more analytics-engineer hours than it costs in dependency management?

Frequently asked questions

What are dbt packages and why do I need them?

dbt packages are Jinja + SQL libraries you install via packages.yml that expose macros, tests, and materialisation logic to your dbt project. Every mature dbt team ships variations of the same ~40 macros (surrogate keys, date spines, pivots, dedups, format validators); packages let you install those macros — adapter-dispatched, version-pinned, community-tested — for free instead of writing and maintaining them locally. The dbt_utils package (owned by dbt Labs, ~60 macros) covers the utility layer; dbt_expectations (calogica, ~60 test macros) covers the data-quality layer; dbt-codegen (dbt Labs) covers the code-generation layer; dbt-osmosis (z3z1ma) covers doc propagation. The senior signal in 2026 is standardising on those four, pinning them to narrow version ranges ([">=1.1.0", "<2.0.0"] for dbt_utils), and running an upgrade blast-radius audit before every version bump — never a blind dbt deps --upgrade against production.

dbt_utils vs dbt_expectations — when do I pick each?

dbt_utils is for macros — reusable SQL generators like generate_surrogate_key, date_spine, pivot, deduplicate, and star. Use it any time you'd otherwise write hand-rolled Jinja to emit SQL. dbt_expectations is for tests — column-level and table-level assertions in the Great Expectations vocabulary (expect_column_values_to_be_between, expect_row_values_to_have_recent_data, expect_table_row_count_to_be_between). Use it any time dbt core's built-in tests (unique, not_null, relationships, accepted_values) don't cover the invariant you need. The two are complementary, not competing: almost every production dbt project installs both. dbt_expectations transitively depends on dbt_utils (it uses dbt_utils macros internally), so installing dbt_expectations pulls in dbt_utils automatically — pin them both explicitly anyway to lock the versions.

Should I pin dbt package versions, and how strictly?

Yes — and pin to a range, not to an exact version and not to a loose >= open-ended pin. The canonical pattern is version: [">=X.Y.0", "<X.(Y+1).0"] for the current major. This admits patch and minor upgrades in the current major (dbt_utils 1.1.0 → 1.1.1 → 1.2.0) — critical bug fixes get pulled automatically — but blocks the next major (1.x.x → 2.0.0) until a human reviews the changelog and bumps the pin. For pre-1.0 packages like dbt_expectations, use [">=0.X.0", "<0.(X+1).0"] because pre-1.0 semver treats every minor as potentially breaking. Never ship a loose pin like ">=1.0.0" to production — a Monday-morning dbt deps will eventually pull a new major that silently changes generate_surrogate_key's null-handling and breaks every downstream JOIN. Every pin bump is a PR with the changelog diff, a project-wide grep for breaking-change usages, dbt compile + dbt build --select changed runs, and a regression EXCEPT query on any surrogate-key outputs.

Should I install packages from dbt Hub or from Git?

Prefer dbt Hub (hub.getdbt.com) for any package published there. Hub entries use package: {org}/{name} and version: {range}; dbt resolves the range to a git tag, clones from the linked git URL, and installs. Hub versions are canonical, changelogs are linked, upgrades are trackable. Fall back to Git direct (git: [e-mail]:foo/bar.git + revision: <tag|sha>) for packages not published to Hub, unreleased-fix commits you need immediately, or internal packages in a private git remote. Use local (local: ../shared_macros) for monorepo shared macros — a fourth sibling "package" project referenced by the other three. Enterprise teams sometimes run a private HTTP registry that proxies Hub; syntax stays the same. Never mix modes for the same package across environments — Hub in staging + Git in prod is a divergent install waiting to bite you at 3 AM.

Does dbt-osmosis overwrite my existing documentation?

No — dbt-osmosis yaml document is strictly additive and idempotent. It walks the DAG's column-lineage graph, and for each undocumented column downstream, it copies the description from the closest documented upstream column with the same name. If a downstream column already has a description, osmosis leaves it alone. Running osmosis twice produces the same result as running it once. The safe-by-default behaviour is why you can wire it into a pre-commit hook and CI without worrying about clobbering hand-written docs. dbt-osmosis yaml refactor (the sibling command) is more invasive — it rewrites schema.yml to match the current SQL, adding new columns and removing columns no longer selected — but it also preserves existing descriptions on retained columns. If you want to change existing docs, edit the schema.yml directly and re-run osmosis to propagate; the manual edit at the source of truth is the entry point, not the osmosis command.

Can I write my own dbt package?

Yes — a dbt package is just a dbt project with a dbt_project.yml declaring name: your_package_name and a macros/ (and optionally models/, tests/, seeds/) directory. To share internally, push to git and reference via packages: - git: {url} - revision: {tag} in the consuming project's packages.yml. To publish publicly, tag a semver release, submit a PR to the dbt-labs/hub.getdbt.com metadata repo to list your package on Hub, and consumers can then - package: {your-org}/{your-package}. Best practices: pick a clear name (avoid collisions with dbt_utils macro names), declare require-dbt-version in your dbt_project.yml, ship a README.md with a macro-by-macro API doc, write tests for your macros against every adapter you claim to support, and keep a CHANGELOG.md that tracks every breaking change per semver. The senior signal is treating your internal package with the same versioning discipline as the community packages — pin ranges, audit upgrades, document breaking changes.

Practice on PipeCode

• Drill the SQL practice library → for the surrogate-key, date-spine, pivot, dedup, and data-quality problems senior dbt interviewers love.
• Rehearse on the ETL practice library → for the staging-model, source-onboarding, and DAG-hygiene problems that motivate the four-package baseline.
• Sharpen the tuning axis with the optimization practice library → for the package-versioning, upgrade-audit, and monorepo-sharing problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the dbt_utils + dbt_expectations + codegen + osmosis decision axis against real graded inputs.

Lock in dbt-package muscle memory

Package READMEs explain macros. PipeCode drills explain the decision — when `dbt_utils.generate_surrogate_key` replaces hand-rolled MD5, when `dbt_expectations` beats `source freshness`, when `dbt-osmosis` closes the doc-rot loop, and when a version-pin bump needs the full six-step blast-radius audit. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior analytics engineers actually face.

Practice SQL problems →
Practice optimization problems →