Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 41 ยท Packages and Deployment

Packages and dbt_utils

Install dbt packages with packages.yml and dbt deps, pin versions safely, and use dbt_utils macros and tests such as surrogate keys and unique combinations.

Other teams have already solved many problems you will meet: generating surrogate keys, comparing two models, pivoting columns, building a date spine. dbt packages let you reuse that work. A package is simply another dbt project (models, macros, tests) that you install into yours. Most of the ecosystem's value sits in a handful of them, dbt_utils first.

Packages download from the dbt package hub or Git, which is not possible on this site. The dbt commands and Jinja are shown read-only; the SQL each macro produces runs in the editors.

Installing packages

List them in packages.yml at the project root (next to dbt_project.yml):

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

  - package: calogica/dbt_date
    version: 0.10.1

  - git: "https://github.com/example-org/internal-macros.git"
    revision: v1.4.0

  - local: ../shared-dbt-package

Then run:

Terminal
dbt deps

dbt downloads each package into the dbt_packages/ folder (add it to .gitignore). A package can depend on other packages, and dbt deps resolves these. In recent versions it also writes a package-lock.yml that pins exact versions, which you should commit so every environment installs the same code.

Pinning matters. Use a range that stops before the next major version (<2.0.0), or an exact version for anything production-critical. Upgrade deliberately, read the changelog, and run your tests.

Using package macros

Macros are called with the package name as a namespace: {{ dbt_utils.macro_name(...) }}. Some of the ones you will use most:

generate_surrogate_key

Builds a stable hash key from one or more columns, handling NULLs and types consistently:

SQL
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }} as order_line_key,
    *
from {{ ref('stg_order_lines') }}

Compiled, it becomes an md5 hash of the concatenated, NULL-safe values, for example md5(cast(coalesce(cast(order_id as varchar), '_dbt_utils_surrogate_key_null_') || '-' || ...)). You can approximate the idea in SQLite, which has no md5, by concatenating with a separator:

SQL What a surrogate key must do ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

star

Select all columns except some, without typing them:

SQL
select {{ dbt_utils.star(from=ref('stg_customers'), except=['email']) }}
from {{ ref('stg_customers') }}

date_spine

Generates one row per day between two dates, the base for filling gaps in reports:

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

The idea is a recursive sequence of dates, which you can run here:

SQL A date spine in plain SQL

Press Run (or Ctrl + Enter) to see the result here.

Join a spine to daily metrics with a left join and coalesce(metric, 0) to show zero-activity days instead of missing ones.

Package tests

dbt_utils also adds generic tests:

YAML
models:
  - name: fct_order_lines
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          arguments:
            combination_of_columns: [order_id, line_number]
    columns:
      - name: quantity
        data_tests:
          - dbt_utils.accepted_range:
              arguments:
                min_value: 1
                inclusive: true

Other popular tests: expression_is_true, equal_rowcount, recency, not_null_proportion.

Other packages worth knowing

Package Purpose
dbt_utils Macros and tests for everyday SQL
dbt_expectations Great Expectations-style tests (value ranges, distributions)
dbt_date Date and calendar helpers
audit_helper Compare two tables or models when refactoring
codegen Generate YAML and staging SQL from the warehouse
dbt_project_evaluator Lint your DAG for best-practice violations
elementary Data observability and anomaly tests
Source packages (Fivetran etc.) Ready-made staging and marts for common SaaS data

Check the dbt package hub (hub.getdbt.com) for the current list.

Using a package responsibly

  • Read the macro source (in dbt_packages/) before trusting it.
  • Pin versions and review upgrades in a pull request.
  • Prefer maintained packages with recent releases.
  • Override a package macro by defining one with the same name in your project; use the dispatch config for adapter-specific variants.
  • Configure package models in dbt_project.yml under the package name, and disable what you do not need.
flowchart LR
  P["packages.yml"] -->|"dbt deps"| D["dbt_packages/"]
  D --> M["Macros: dbt_utils.*"]
  D --> T["Tests: dbt_utils.*"]
  M --> Y["Your models"]
  T --> Z["Your YAML"]

Packages are code that lands in your project folder.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which command installs the packages listed in packages.yml?

    dbt deps downloads the packages into dbt_packages/.
  2. 2. Why use a version range like >=1.1.0, <2.0.0?

    Major releases may break your code, so the range stops before the next major version.
  3. 3. What does generate_surrogate_key do?

    It hashes the columns with consistent NULL handling, giving a stable key.
  4. 4. Which test checks that a combination of columns is unique?

    It treats several columns together as the key, which the built-in unique test cannot do.

Summary

  • Packages are dbt projects you install with packages.yml and dbt deps.
  • Pin versions and commit the lock file.
  • dbt_utils provides surrogate keys, star, date_spine and extra tests.
  • Review package code and upgrades like any dependency.

Next: Environments, Targets and Profiles