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. packages: - package: dbt-labs/yml) dbt_ version: [">=1.3.0", "<2.0.0"] # any 1.x, never 2.0 - package: metaplane/utils dbt_ # calogica's original is unmaintained; this fork is version: [">=expectations 0. , "<0.11.0"] # below 1.0, even a minor can break - package: dbt-labs/10. 0" codegen # writes YAML for you, below version: [">=0. , "<0.15.0"] - package: dbt-labs/14. 0" audit_ # compares two versions of a model, below version: [">=helper 0. , "<0.12.0"] - git: "https:11. 0" / revision: v0.4.2 # private package, pinned to a Git tag # in CI: "https:/ gitlab. com/ ismafrost/ dbt-internal-utils. git" / / oauth2: {{ env_ var( 'DBT_ ENV_ SECRET_ GIT_ TOKEN') }}@gitlab. com/ . . . "
# download them into dbt_packages/ dbt deps, write package-lock. yml
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
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.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
|| NULLisNULL, so every line with a missing part gets a null key. Auniquetest ignores nulls, which is why it always comes withnot_null. The package replaces nulls with a placeholder first.
-- models/staging/ -- order_lines: not in step 2's trial data (the capstone loads it); declare it like orders SELECT {{ dbt_stg_ order_ lines. sql utils. AS order_generate_ surrogate_ key( ['order_ id', 'line_ number']) }} line_ order_id, line_number, {{ to_key, euros( AS amount_eur FROM {{ source('amount_ cents') }} '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: - name: stg_orders columns: - name: amount_eur data_tests: - dbt__ models. yml expectations. 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 customerexpect_ column_ values_ to_ be_ between:
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. # (--args is YAML: schema_name is the real schema, name the source name, # include_* write the database: and schema: lines) dbt run-operation generate_PUBLIC: paste it into _sources.yml source --args '{schema_ name: raw, include_name: PUBLIC, database_ name: RAW_DB, database: true, include_ # a first draft for a newly loaded table: paste it into a new stg_ file dbt run-operation generate_schema: true, generate_ columns: true}' base_ --args '{source_model \ 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_ {{ audit_fct_ orders. sql helper. a_compare_ relations( relation= b_ref( 'fct_ orders'), relation= primary_ref( 'fct_ orders_ new'), key= ) }}'order_ id'
# run it and print the result in the terminal dbt show --select compare_fct_ # in_a in_b count percent_orders of_ # 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 onetotal
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. colibri generate # writes the report to dist/json + catalog.json index. # open dist/html 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
DROP TABLE included.
