Course contents

The Analytics Engineer’s Field Guidedbt 2: make it trustworthyStep 6: Packages

Packages

Part 2 · dbt · step 6

Packages: don't reinvent the macro

Alice needs a surrogate key and column-level lineage. Someone has already written both.

· · ·

A package is shared dbt code: macros, generic tests, sometimes models. List it in packages.yml, run dbt deps to download it, then use it as if it were yours.

# packages.yml  (project root, next to dbt_project.yml)
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.3.0", "<2.0.0"]   # any 1.x, never 2.0
  - package: metaplane/dbt_expectations  # calogica's original is unmaintained; this fork is
    version: [">=0.10.0", "<0.11.0"]  # below 1.0, even a minor can break
  - package: dbt-labs/codegen              # writes YAML for you, below
    version: [">=0.14.0", "<0.15.0"]
  - package: dbt-labs/audit_helper         # compares two versions of a model, below
    version: [">=0.11.0", "<0.12.0"]
  - git: "https://gitlab.com/ismafrost/dbt-internal-utils.git"
    revision: v0.4.2                     # private package, pinned to a Git tag
    # in CI: "https://oauth2:{{ env_var('DBT_ENV_SECRET_GIT_TOKEN') }}@gitlab.com/..."
# download them into dbt_packages/, write package-lock.yml
dbt deps

Public packages come from hub.getdbt.com, private ones from a Git URL. Versions read MAJOR.MINOR.PATCH: a patch fixes bugs, a minor adds features, a major may break your code. So the first range takes any 1.x and stops before 2.0. A Git tag like v0.4.2 names one fixed commit. dbt deps clones it with git, so your laptop's saved Git login works. CI has none, so it puts a token in the URL (last comment above).

Commit package-lock.yml, not dbt_packages/ (the .gitignore from dbt init lists it, next to target/). The lock file records the exact versions dbt deps picked, so CI installs exactly what you tested. To update, bump the range, rerun dbt deps, and merge once the tests pass.

The ones worth knowing

Package
What it gives you
dbt_utils
The toolbox. generate_surrogate_key (below), star (every column but a few), pivot (rows into columns), date_spine (one row per day), plus generic tests like unique_combination_of_columns.
dbt_expectations
Ready-made data tests in the style of Great Expectations (a Python data-quality library): value ranges, row counts, distributions.
codegen
Writes boilerplate for you: source YAML, and the first staging model for a raw table (codegen calls it a "base model").
audit_helper
Compares two versions of a model row by row. The safety net when you refactor a mart and need to prove the numbers didn't move.

Alice actually uses one: the surrogate key

Alice's order lines have no single key column: order_id plus line_number identify a line. That pair is the natural key. A surrogate key is one column built from it, here a 32-character hash. Hand-rolling md5(order_id || line_number) breaks two ways:

  • Collisions. Order 1 line 23 and order 12 line 3 both glue into '123': same hash. The package adds a delimiter: '1-23' vs '12-3'.
  • Nulls. In Snowflake, anything || NULL is NULL, so every line with a missing part gets a null key. A unique test ignores nulls, which is why it always comes with not_null. The package replaces nulls with a placeholder first.
-- models/staging/stg_order_lines.sql
-- order_lines: not in step 2's trial data (the capstone loads it); declare it like orders
SELECT
    {{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }} AS order_line_key,
    order_id,
    line_number,
    {{ to_euros('amount_cents') }} AS amount_eur
FROM {{ source('raw', 'order_lines') }}

The dbt_utils. prefix is the package name: package macros need it, your own (like to_euros) don't. The argument is a list because a key can span several columns. Add unique and not_null tests on order_line_key (step 7) and the grain is guarded. When the SQL you're about to write feels generic, someone has probably written and tested it already.

Three more, in action

dbt_expectations: Alice bounds her amounts, so a currency bug (cents where euros should be) fails a test, not a dashboard:

# models/staging/_models.yml
models:
  - name: stg_orders
    columns:
      - name: amount_eur
        data_tests:
          - dbt_expectations.expect_column_values_to_be_between:
              arguments:               # dbt 1.10.5+, see step 7
                min_value: 0.01
                max_value: 100000   # an order above 100k EUR is a bug, not a customer

codegen: dbt run-operation runs one of its macros on its own, outside any model, and prints the result in your terminal. You paste it into a file and tidy it.

# source YAML for every table in RAW_DB.PUBLIC: paste it into _sources.yml
# (--args is YAML: schema_name is the real schema, name the source name,
#  include_* write the database: and schema: lines)
dbt run-operation generate_source --args '{schema_name: PUBLIC, database_name: RAW_DB,
  name: raw, include_database: true, include_schema: true, generate_columns: true}'

# a first draft for a newly loaded table: paste it into a new stg_ file
dbt run-operation generate_base_model \
  --args '{source_name: raw, table_name: menu_items}'

audit_helper is the refactoring safety net. Alice saves her rewrite of fct_orders as a second model, fct_orders_new.sql, builds both in her userland, and compares them. The comparison lives in analyses/, a folder for SQL that dbt compiles but never builds (dbt init creates it; step 1's tree leaves it out):

-- analyses/compare_fct_orders.sql
{{ audit_helper.compare_relations(
    a_relation=ref('fct_orders'),
    b_relation=ref('fct_orders_new'),
    primary_key='order_id'
) }}
# run it and print the result in the terminal
dbt show --select compare_fct_orders

# in_a   in_b   count   percent_of_total
# true   true   9996    99.96      identical in both
# true   false     2     0.02      only in the old model
# false  true      2     0.02      only in the new one

Rows are compared whole. Anything short of 100% identical gets explained before merge. Then swap it in: copy the new SQL into fct_orders.sql and delete fct_orders_new.sql.

One more, not a package: dbt-colibri

dbt's docs (step 9) show that fct_orders depends on stg_orders. They don't show that fct_orders.amount_eur comes from raw.orders.amount_cents. dbt-colibri draws that column-by-column map as an interactive report. It's a Python tool, not a dbt package: install it with pip in dbt's virtual environment, never in packages.yml. It reads two files from target/. manifest.json describes the project, compiled SQL included; any dbt command writes it. catalog.json holds the real column types; only dbt docs generate writes it.

# from the project folder, virtual environment active
pip install dbt-colibri
dbt docs generate      # compiles, writes target/manifest.json + catalog.json
colibri generate       # writes the report to dist/index.html

# open dist/index.html in a browser; add dist/ to .gitignore
raw.orders
  • order_id
  • customer_id
  • amount_cents
  • status
stg_orders
  • order_id
  • amount_eur
  • status
  • order_date
fct_orders
  • order_id
  • amount_eur
  • country
Treat packages like any dependency
Read a package before you add it. Its macros and hooks (SQL dbt runs at the start or end of every run) can do anything your role can, DROP TABLE included.

Lesson quiz

  1. dbt deps writes a package-lock.yml. Why commit it?

    1. So CI installs exactly the package versions you tested
    2. So the packages' macros appear on the docs site
    3. Because dbt refuses to compile when it isn't in Git
    4. So dbt deps can skip downloading the packages next time
  2. Which tool from the lesson answers 'if I change raw.orders.amount_cents, exactly which downstream columns break?'

    1. audit_helper
    2. dbt_utils
    3. codegen
    4. dbt-colibri
  3. After order 1 line 23 and order 12 line 3 collided, Alice changes her key to md5(order_id || '-' || line_number), and the unique test passes. What can this hand-rolled key still get wrong?

    1. Order 1 line 23 and order 12 line 3 still collide
    2. md5 returns a different hash on each run, so keys drift
    3. A null part gives a null key, which unique ignores
    4. Snowflake truncates md5 output beyond 16 characters