Packages and dbt_utils

A dbt Package Is a Shareable dbt Project

Every dbt project you have built so far in this track has been self-contained: models, macros, tests, and seeds all living inside one repository, written by your own team. A dbt package breaks that assumption. A package is itself a complete dbt project — with its own macros, models, and tests — that is packaged up so it can be installed into a different dbt project and used there, the same way a Python library gets installed with pip or an npm library gets installed with npm install. Someone else wrote it, tested it, and published it. You pull it in and use its macros and models as if you had written them yourself.

This is a different distribution unit than anything covered in the earlier modules in this track. A macro (covered in the previous module) is something you write once inside your own project and call repeatedly within that project. A package is something someone else wrote, in an entirely separate project, that you install as a dependency of your project. The macros inside an installed package become callable from your own models exactly like your own project's macros — dbt does not meaningfully distinguish "a macro I wrote" from "a macro a package I installed provides," once it has been installed and compiled into your project's macro namespace.

Why does this exist at all? Because an enormous amount of what teams write in their macros/ directory is not actually specific to their business — it is general-purpose SQL-generation logic that thousands of other dbt users also need. Generating a surrogate key by hashing several columns together. Building a complete calendar date dimension from a start date to an end date. Pivoting a long, narrow table into a wide one. These are not FreshCart-specific or Compass-specific problems — they are generic SQL engineering problems that come up on nearly every analytics engineering team, and writing the correct, edge-case-hardened version of that macro from scratch on every team, at every company, is a waste of engineering time that a shared package eliminates.

Declaring Dependencies in packages.yml

A dbt project declares which packages it depends on in a file named packages.yml, sitting at the root of the project alongside dbt_project.yml. This file is a list of package declarations. dbt supports two main ways of declaring where a package comes from: by referencing its name on dbt Hub, the public registry of dbt packages, or by pointing directly at a git repository URL.

Installing from dbt Hub — the common case

Most well-known community packages, including dbt_utils itself, are published to dbt Hub. Hub installs are the simplest form — you give the package name and a version constraint, and dbt resolves and downloads the matching release.

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

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

The version field is a range, not a single pin — but notice the range is still tight: it allows any patch or minor release that satisfies "at least 1.1.0, but strictly less than 2.0.0." This is the recommended pattern from dbt Labs itself, and it is doing real work: it lets you pick up bug fixes and small improvements automatically within the 1.x line, while refusing to silently jump to 2.x, where a major version bump could change macro behavior or remove something you depend on. Part 03 goes deeper into exactly why this range discipline matters.

Installing directly from a git repository

Not every package worth using is on dbt Hub — a package a coworker wrote and pushed to your company's internal GitHub, or a fork of a public package with a small patch applied, is installed by pointing directly at the git URL instead.

packages:
  - git: "https://github.com/dbt-labs/dbt-audit-helper.git"
    revision: "0.12.0"

  - git: "https://github.com/your-company/internal-dbt-macros.git"
    revision: "main"
    warn-unpinned: false

The revision field for a git install is doing the same conceptual job as version does for a Hub install — it pins exactly which commit, tag, or branch gets downloaded. Pinning revision to a tag like "0.12.0" behaves like a versioned release. Pinning it to a branch name like "main" means every dbt deps run downloads whatever the latest commit on that branch happens to be at that moment — which is exactly the loosely-pinned pattern Part 03 explains is a real production risk.

dbt deps and the Real Risk of Loose Version Pins

Declaring a package in packages.yml does not install anything by itself — it is only a manifest of intent. The command that actually does the work is dbt deps. Running it reads packages.yml, resolves each declared package against the version constraints given, downloads the matching release (or git revision), and writes the result into a folder named dbt_packages/ at the root of your project.

$ dbt deps

Installing dbt-labs/dbt_utils
Installed from version 1.1.1
  Updated version available: 1.3.0
Installing dbt-labs/codegen
Installed from version 0.12.1
  Up to date!

Installed 2 packages in 3.41s

After this runs, your project has a new top-level directory: dbt_packages/dbt_utils/ and dbt_packages/codegen/, each containing that package's full source — its macros/, models/, and any other files it ships. dbt treats these as part of the project's compiled context. A macro defined inside dbt_packages/dbt_utils/macros/ becomes callable from your own model files as dbt_utils.some_macro(...), and it is worth noting explicitly: dbt_packages/ is a generated directory, not something you hand-edit or commit meaningful custom logic into — most teams add it to .gitignore, exactly like node_modules in a JavaScript project, and rely on packages.yml plus a lockfile to reproduce it.

The real risk: an unpinned or loosely-pinned package changing under you

This is the single most important operational fact in this module. dbt deps is not a one-time action — it is re-run constantly: on every developer's laptop when they clone the project fresh, in CI on every pull request, and in the production job that runs your scheduled dbt build. If a package's version constraint is loose — a floating range with no upper bound, or a git revision pinned to a branch name instead of a tag or commit SHA — then two different runs of dbt deps, days or weeks apart, can silently pull down two different versions of that package's code, with no change to your own project's files at all.

# packages.yml with a genuinely dangerous, unbounded pin:
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0"]      # no upper bound at all

# Monday: dbt deps resolves this to dbt_utils 1.1.1 — everything works.
# A production model calls dbt_utils.surrogate_key(['customer_id', 'order_id']).

# Three weeks later, dbt_utils releases 2.0.0 with a breaking change:
# surrogate_key's underlying hashing macro is renamed and restructured
# as part of the 2.x major-version cleanup.

# Thursday: a completely unrelated PR touches a different model.
# CI runs "dbt deps" as part of its normal setup step.
# dbt deps silently resolves dbt_utils to the new 2.0.0 release,
# because ">=1.0.0" is satisfied by 2.0.0 too.

# The unrelated PR's CI run now fails on a macro compilation error
# in a model nobody touched — because the dependency moved underneath it.
# This is exactly the kind of failure that looks like "CI is flaky"
# until someone actually reads the compile error and finds the real cause.

Notice that nothing in the team's own codebase changed between the two dbt deps runs. The break was entirely caused by the dependency itself moving, on a routine, automatic dbt deps invocation that nobody thought of as a risky operation. This is why dbt Labs' own documentation, and every production dbt project worth trusting, pins package versions to a bounded range — typically allowing patch and minor upgrades within a major version, but never crossing a major version boundary automatically.

The practical rule that follows from all of this: every package declaration in a production packages.yml should have an upper bound, and every git-based package declaration should point at an immutable reference — a tag or a commit SHA, never a branch name. Upgrading a package's version should always be a deliberate, reviewed change to packages.yml, committed and tested like any other code change — never something that happens as an unnoticed side effect of a routine dbt deps.

dbt_utils — the Package Nearly Every dbt Project Installs

dbt-labs/dbt_utils is maintained by dbt Labs itself and is, by a wide margin, the most widely installed community package in the entire dbt ecosystem. It is a grab-bag of genuinely useful, battle-tested macros covering SQL generation problems that come up across nearly every warehouse and nearly every project: generating surrogate keys, building date spines, pivoting data, testing composite-key uniqueness, and dozens of smaller cross-database compatibility helpers. The next four Parts walk through the specific macros worth knowing cold.

Part of why dbt_utils earns the "install it in nearly every project" recommendation, where most other packages do not, is cross-warehouse portability. Several of its macros exist specifically to paper over syntax differences between Snowflake, BigQuery, Redshift, Postgres, and Databricks — writing a date-manipulation expression or a hashing function that behaves identically no matter which warehouse compiles it. Writing that portability layer yourself, correctly, across every warehouse dialect you might ever run on, is a much bigger undertaking than it looks from the outside.

• ✓dbt_utils.surrogate_key() — hashes multiple columns into one deterministic composite key.
• ✓dbt_utils.date_spine() — generates a complete, gapless calendar/date dimension between two dates.
• ✓dbt_utils.pivot() — turns long, narrow data into a wide table, generating the CASE WHEN logic for you.
• ✓dbt_utils.unique_combination_of_columns — a generic test asserting a set of columns together forms a unique key.
• ✓Dozens of smaller helpers — date_trunc-style cross-warehouse date functions, star() for selecting columns dynamically, and type-casting helpers.

dbt_utils.surrogate_key() — Hashing a Composite Key

A surrogate key is a synthetic, warehouse-generated identifier for a row, as opposed to a natural key made of business columns that already exist in the source data. Many source systems don't provide a single clean primary key column for every table you need one for — an events table might only be uniquely identified by the combination of user_id, event_type, and event_timestamp together, with no single column serving as a key on its own. dbt_utils.surrogate_key() solves this by hashing a list of columns together into one deterministic string value.

-- models/staging/stg_events.sql

select
    {{ dbt_utils.generate_surrogate_key(['user_id', 'event_type', 'event_timestamp']) }}
        as event_pk,
    user_id,
    event_type,
    event_timestamp,
    event_payload
from {{ source('app', 'raw_events') }}

Note the macro name is generate_surrogate_key in current dbt_utils versions (surrogate_key was the name in older, now-deprecated releases — another concrete reason the version-pinning discipline from Part 03 matters, since a major-version bump can rename the exact macro your models depend on). What it compiles to, conceptually, is a hash function applied to the concatenation of the given columns, with each column first cast to a string and null-coalesced to a consistent placeholder so that a null column doesn't silently break the hash or make two genuinely different rows hash identically.

-- Simplified compiled SQL (actual output uses a warehouse-specific hash function,
-- e.g. MD5 on most warehouses):

md5(
    coalesce(cast(user_id as varchar), '_dbt_utils_surrogate_key_null_') || '-' ||
    coalesce(cast(event_type as varchar), '_dbt_utils_surrogate_key_null_') || '-' ||
    coalesce(cast(event_timestamp as varchar), '_dbt_utils_surrogate_key_null_')
) as event_pk

The resulting hash is deterministic — the same three input values always produce the same event_pk, on every run, on every warehouse. This determinism is exactly what makes it usable as a real primary key: it can be tested with a unique generic test, joined on, and referenced from downstream models, all without ever storing the three source columns' combined value as anything other than this one hashed column.

dbt_utils.date_spine() — Generating a Complete Calendar Dimension

A date spine is a table with exactly one row per calendar day (or week, or month) across some range, with no gaps — even for days that have zero activity in your actual source data. This is foundational for a huge class of reporting problems: "orders per day, including days with zero orders," "active users per day," or any chart where a missing day should show up as a visible zero rather than simply not appearing on the x-axis at all. Building this by hand means writing a recursive CTE or a numbers-table cross join — dbt_utils.date_spine() generates that for you.

-- models/marts/dim_date_spine.sql

with spine as (

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

)

select
    date_day,
    extract(year from date_day)   as year,
    extract(month from date_day)  as month,
    extract(dow from date_day)    as day_of_week
from spine

The generated spine gives you exactly one row per day from 2023-01-01 through today, with a column named date_day, and nothing else — you then join your actual fact data onto this spine using a left join from the spine, which is what guarantees every date appears in the output regardless of whether any real activity happened on it.

-- models/marts/fct_daily_orders.sql

with spine as (
    select date_day from {{ ref('dim_date_spine') }}
),

orders as (
    select
        cast(order_placed_at as date) as order_date,
        count(*) as order_count
    from {{ ref('fct_orders') }}
    group by 1
)

select
    spine.date_day,
    coalesce(orders.order_count, 0) as order_count
from spine
left join orders
    on spine.date_day = orders.order_date
order by spine.date_day

date_day    | order_count
2026-03-01  | 412
2026-03-02  | 389
2026-03-03  | 0      <- a real zero-order day, visible instead of missing entirely
2026-03-04  | 501

Without the spine, 2026-03-03 would simply not appear in the query result at all — a group by on the orders table alone only produces rows for dates that actually had orders. Whether that missing row reads as "zero orders" or "a bug in the dashboard" to whoever is looking at the chart is exactly the difference date_spine is built to eliminate.

pivot() for Wide Data, and a Composite-Key Uniqueness Test

dbt_utils.pivot() — turning long data wide

Long, narrow data — one row per entity per attribute — is usually the right shape for storage and transformation, but reporting tools and stakeholders frequently want the opposite shape: one row per entity, with each distinct attribute value as its own column. Writing that transformation by hand means a CASE WHEN expression per distinct value, which gets tedious and error-prone once there are more than a handful of values. dbt_utils.pivot() generates that CASE WHEN block for you from a list of values.

-- Input shape (long): one row per order_date per status
-- order_date  | status      | order_count
-- 2026-03-01  | placed      | 320
-- 2026-03-01  | cancelled   | 18
-- 2026-03-01  | refunded    | 6

select
    order_date,
    {{ dbt_utils.pivot(
        column='status',
        values=['placed', 'cancelled', 'refunded'],
        agg='sum',
        then_value='order_count',
        else_value='0'
    ) }}
from {{ ref('stg_daily_order_status_counts') }}
group by order_date

-- Compiled SQL (simplified):
select
    order_date,
    sum(case when status = 'placed'    then order_count else 0 end) as placed,
    sum(case when status = 'cancelled' then order_count else 0 end) as cancelled,
    sum(case when status = 'refunded'  then order_count else 0 end) as refunded
from stg_daily_order_status_counts
group by order_date

-- Output shape (wide): one row per order_date
-- order_date  | placed | cancelled | refunded
-- 2026-03-01  | 320    | 18        | 6

dbt_utils.unique_combination_of_columns — testing a composite key

Module 09's testing content covered the built-in unique test, which only checks a single column. A great many real primary keys are composite — no single column is unique on its own, but the combination of several columns together is. dbt_utils ships a generic test, unique_combination_of_columns, specifically for this case, applied through the same YAML tests: block used for every other generic test in this track.

# models/staging/_staging.yml

models:
  - name: stg_order_line_items
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_item_number

This test compiles to a SQL query that groups by both columns together and asserts the count of rows per (order_id, line_item_number) pair never exceeds one — the composite-key equivalent of the plain unique test's single-column GROUP BY ... HAVING COUNT(*) > 1 pattern. Reaching for this generic test from dbt_utils is a much better default than writing an equivalent singular test by hand every time a new model needs a composite-key check, precisely because it is exactly the kind of small, genuinely reusable logic Part 08 argues is worth pulling in as a dependency rather than reimplementing.

codegen and audit_helper — Two More Packages Worth Knowing Exist

dbt-labs/codegen — generating source YAML boilerplate

Writing the sources: block in a schema.yml file by hand — listing every table in a source system and every column in each table — is exactly the kind of repetitive, mechanical task a macro should do for you instead. codegen provides operations you run from the command line (via dbt run-operation) that introspect an actual database schema and print out ready-to-paste YAML for you, rather than a macro you call inside a model.

$ dbt run-operation generate_source \
    --args '{"schema_name": "raw_freshcart", "database_name": "analytics"}'

# Prints a complete, ready-to-paste sources: YAML block to the console,
# with every table and column codegen found in raw_freshcart already listed —
# turning what would be an hour of manual typing into a copy-paste-and-review step.

dbt-labs/audit_helper — comparing two tables row by row

audit_helper solves a specific, high-value problem: you are refactoring an existing model — maybe rewriting a gnarly legacy SQL query into cleaner incremental logic — and you need to prove the new version produces identical output to the old one before you cut over. Manually eyeballing two large tables for differences does not scale. audit_helper's compare_relations macro generates a query that reports exactly which rows and columns differ between two relations.

-- analysis/compare_fct_orders_rewrite.sql

{% set old_etl_relation = source('legacy', 'fct_orders_old') %}
{% set new_dbt_relation = ref('fct_orders') %}

{{ audit_helper.compare_relations(
    a_relation=old_etl_relation,
    b_relation=new_dbt_relation,
    primary_key="order_id"
) }}

column_name    | perc_matching  | perc_diff
order_id       | 100.0%         | 0.0%
customer_id    | 100.0%         | 0.0%
order_total    | 99.94%         | 0.06%    <- rounding difference to investigate
order_status   | 100.0%         | 0.0%

A 99.94% match on order_total, rather than a flat 100%, is exactly the kind of signal that would be nearly impossible to catch by spot-checking a handful of rows manually, but that audit_helper surfaces automatically across the entire table — telling you precisely which column, and roughly how much of it, still needs investigation before the rewritten model can safely replace the old one in production.

When a Package Is Worth the Dependency, and When It Isn't

Every package you install is a dependency risk, exactly as Part 03 laid out — it is code you did not write, maintained on someone else's schedule, that can change underneath your project on a routine dbt deps if pinning discipline slips. That risk is not a reason to avoid packages altogether; it is a reason to be deliberate about when pulling one in is actually worth it versus when writing three lines of your own macro is the better call.

A useful rule of thumb: reach for dbt_utils, or another well-known package, when the thing you need is a solved problem that thousands of other teams have already solved and battle-tested — a hashed composite key, a date dimension, a pivot. Write your own macro when the thing you need is small, specific to your own project's business logic, and would not meaningfully benefit from someone else's testing, because nobody else's project has the same rule to test against in the first place.

Worked Example: Installing dbt_utils and Using surrogate_key in a Staging Model

Putting the whole workflow together end to end: declaring the dependency, installing it, and using one of its macros in a real staging model, exactly the sequence you would follow on a real project the first time you need a composite key.

# packages.yml

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

$ dbt deps

Installing dbt-labs/dbt_utils
Installed from version 1.3.0
  Up to date!

Installed 1 package in 1.87s

At this point, dbt_packages/dbt_utils/ exists locally with the package's full source, and every macro it defines — including generate_surrogate_key — is now callable from any model in the project, namespaced as dbt_utils.macro_name(...).

-- models/staging/stg_thumbtack_reviews.sql

with source as (
    select * from {{ source('thumbtack', 'raw_reviews') }}
),

renamed as (
    select
        {{ dbt_utils.generate_surrogate_key(['pro_id', 'customer_id', 'review_submitted_at']) }}
            as review_pk,
        pro_id,
        customer_id,
        review_submitted_at,
        star_rating,
        review_text
    from source
)

select * from renamed

Thumbtack's raw review events have no single natural primary key — the same pro_id and customer_id pair could legitimately submit more than one review over time, so review_submitted_at has to join the key to disambiguate them. Rather than concatenating and hashing those three columns by hand with a warehouse-specific MD5 expression, generate_surrogate_key handles the null-coalescing, casting, and hashing consistently, and the result — review_pk — can now be tested with a plain unique generic test in schema.yml, joined on safely from downstream models, and trusted as a real primary key.

# models/staging/_staging.yml

models:
  - name: stg_thumbtack_reviews
    columns:
      - name: review_pk
        tests:
          - unique
          - not_null

This is the complete loop: a dependency declared with a safe version range, installed with dbt deps, used inside a model through its namespaced macro call, and validated with the same testing patterns covered in Module 09 — no different, from the model author's point of view, than if the hashing logic had been written by hand inside the project's own macros/ directory.

Five Misconceptions About dbt Packages

Three Ways Real Teams Actually Use dbt Packages

5 Interview Questions — With Complete Answers

Five Mistakes Engineers Make Working With Packages

Package Errors — And Exactly Why They Happen