Packages and dbt_utils

Concepts covered: Macros and packages

A package is a dbt project someone else published, pulled into yours so its macros and tests can be called by name. dbt_utils, maintained by the team that maintains dbt, holds the macros and generic tests most projects would otherwise write for themselves: a hashed key over several columns, a test that a pair of columns is unique together, a test that a value sits inside a range. Installing it takes one file and one command.

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

The package name is the GitHub owner and repository the package hub keys it by, and the version is a range dbt is free to resolve within. dbt deps reads the file, picks the newest version inside the range, downloads that project into dbt_packages/ and records the exact version it chose in package-lock.yml. No SQL runs and nothing in the warehouse changes; the only effect is files on disk. A package also declares which dbt versions it supports, and dbt deps stops with an error when the running dbt falls outside that range.

dbt_packages/dbt_utils/ now holds the package's own macros/ and tests/ folders, a dbt project inside yours
package-lock.yml records the version resolved, so the next dbt deps installs the same one
dbt_packages/ stays out of version control, so every fresh checkout runs dbt deps before anything builds
Every macro and test in the package is called with dbt_utils. in front of its name, the package's project name

Name	Kind	What it does
generate_surrogate_key	macro	Prints a hash of several columns joined into one key, with nulls replaced by a fixed string
safe_divide	macro	Prints a division that returns null instead of failing when the denominator is 0
date_spine	macro	Prints SQL producing one row per day, week or month between 2 dates
unique_combination_of_columns	test	Fails when 2 or more rows share the same values across a set of columns
accepted_range	test	Fails when a value falls below a minimum or above a maximum
not_empty_string	test	Fails when a string column holds an empty string
expression_is_true	test	Fails on any row where a SQL expression you give is false

Declaring a Package Test

A package's test is declared like a built-in one, with the package prefix in front of the name. The arguments the test takes sit under arguments, and settings dbt itself reads, such as severity, sit under config. A test over several columns at once belongs at the model level, since no single column owns it.

models:
  - name: stg_order_items
    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
              config:
                severity: warn

The test is still a query that returns failing rows. unique_combination_of_columns groups by the columns it was given and keeps the groups with more than 1 row, which is the query you wrote by hand for a compound key, now written once in the package and filled in from the YAML.

with validation_errors as (
    select
        order_id,
        line_number
    from analytics.stg_order_items
    group by order_id, line_number
    having count(*) > 1
)
select
    *
from validation_errors

Calling a Package Macro

The macros are called the same way, prefix first. generate_surrogate_key takes a list of column names and prints a hash of them, so a model with no single key column gets one from the pair it is unique on, and the test above and the key below agree on what that pair is.

select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }} as order_item_key,
    order_id,
    line_number,
    quantity
from {{ source('raw', 'order_items') }}

select
    lower(to_hex(md5(to_utf8(
        coalesce(cast(order_id as varchar), '_dbt_utils_surrogate_key_null_')
        || '-' ||
        coalesce(cast(line_number as varchar), '_dbt_utils_surrogate_key_null_')
    )))) as order_item_key,
    order_id,
    line_number,
    quantity
from raw.order_items

Each column is cast to text, a null becomes the literal _dbt_utils_surrogate_key_null_ so that a null in one column cannot make 2 different rows hash alike, the pieces are joined with -, and the whole string is hashed. The hash function and the cast syntax come from the warehouse adapter rather than from the package, which is how one macro serves every warehouse dbt runs on: the Jinja is shared, the SQL it prints is local.

About this lesson

This section is part of the Jinja and Macros: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.