Macros and packages

Macros are reusable Jinja functions that generate SQL. Packages are bundles of macros, models, and tests you install from the dbt Hub or Git. dbt-nexus is a package.

Learning Objectives

By the end of this lesson, you will be able to:

  • Write a Jinja macro that generates SQL
  • Call macros from your models with {{ my_macro(args) }}
  • Install third-party packages via packages.yml and dbt deps
  • Recognize what dbt_utils and dbt-nexus give you as packages

Macros: reusable Jinja functions

A macro is a Jinja function that lives in macros/<name>.sql. Calling it from a model splices its rendered output into the SQL.

For example, macros/family_role_label.sql:

{% macro family_role_label(role_col) %}
case
    when {{ role_col }} = 'parent' then 'Parent'
    when {{ role_col }} = 'child'  then 'Child'
    else 'Other'
end
{% endmacro %}

Call it from a model:

select
    member,
    {{ family_role_label('role') }} as role_label
from {{ ref('family_members') }}

At compile time, dbt replaces the call with the macro's rendered body:

select
    member,
    case
        when role = 'parent' then 'Parent'
        when role = 'child'  then 'Child'
        else 'Other'
    end as role_label
from `doe-family-dwh`.`doe_family_dev`.`family_members`

Macros earn their keep when the snippet would otherwise be repeated in five places. Two rules of thumb:

  1. Don't macro-fy something that's only used once. It hides logic for no benefit.
  2. Macro names should describe what they emit, not what they do internally. is_business_day(...) not if_else_with_holidays(...).

Macros that take a value, vs macros that return one

The macro above renders SQL inline. Macros can also return values for use in other Jinja:

{% macro get_default_threshold() %}
    {{ return(10) }}
{% endmacro %}

Called like a variable:

{% set threshold = get_default_threshold() %}
select * from ... where interaction_count > {{ threshold }}

This second form is less common in day-to-day modeling but very common inside macro implementations.

Built-in dbt macros

dbt ships a bunch of macros you can use without installing anything:

Macro What it gives you
ref(name) The fully-qualified table name of a model
source(src, table) The fully-qualified name of a declared source table
var(name, default) A variable defined in dbt_project.yml or --vars
target.name The current target's name (dev, prod, etc.)
this The fully-qualified name of the model being built (useful in incremental)
is_incremental() True when an incremental model is being built in incremental mode
run_query(sql) Execute a SQL query at compile time and use the result in Jinja

You've used several of these already.

Packages: macros + models someone else maintains

A package is a dbt project that you install into yours. Anyone can publish one; the conventional places are:

  • dbt Hub — hub.getdbt.com, the official registry
  • A Git repo — for unpublished or private packages

Packages can ship:

  • Macros — pre-written Jinja you call from your models
  • Models — pre-written .sql files dbt builds into your warehouse
  • Tests — schema and data tests
  • Sources — declared source tables

The two packages every dbt-nexus project uses are dbt_utils and dbt-nexus itself.

Declaring packages

Create packages.yml at the project root:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.0
  - git: https://github.com/slide-rule-tech/dbt-nexus
    revision: v0.9.1

Install with:

dbt deps

That fetches each package into dbt_packages/ (already in your .gitignore from lesson 1.5). Both forms work:

Form When to use
package: org/name The package is on the dbt Hub
git: https://... The package is in a Git repo (public or private with auth set up)
local: ../path/to/pkg Local development of the package itself

Pin to a specific version (Hub) or revision (Git tag/branch/SHA). In production you want a pinned version so changes to the package don't surprise you mid-week.

dbt_utils — the swiss army knife

dbt-labs/dbt_utils ships about 50 macros that fill gaps in standard SQL. The ones you'll use most:

Macro What it does
dbt_utils.generate_surrogate_key(['col_a', 'col_b']) Hash columns into a deterministic key
dbt_utils.star(from=ref('foo'), except=['x','y']) Select all columns except some
dbt_utils.pivot('col', dbt_utils.get_column_values(...)) Dynamic pivot
dbt_utils.union_relations(relations=[...]) Union a list of tables that share columns
dbt_utils.date_spine(...) Generate a daily date series

Every nexus project uses dbt_utils extensively. You'll see it in Module 2 source models.

dbt-nexus — what makes the rest of this course possible

The dbt-nexus package is the one we're building toward. It ships:

  • Macros: nexus.create_nexus_id(), nexus.unpivot_identifiers(), nexus.unpivot_traits(), nexus.limit_by_timestamp(), etc.
  • Models: nexus_events, nexus_entities, nexus_relationships, the identity resolution pipeline, the metadata tables
  • Tests: structural assertions on the unified tables
  • Configuration: a vars.nexus block that drives everything

You'll install and configure it for real in Module 3, lesson 3.3.

When not to write a macro

The temptation to factor everything into macros is real. Resist it when:

  • The snippet appears once or twice
  • The macro is a thin wrapper around plain SQL that's harder to read than the SQL itself
  • You'd need three or more arguments to make it general enough

Repetition is fine. Three identical CTEs is better than one macro that hides which fields differ. Macros are leverage, not a goal.

Hands-On Exercise

  1. Create macros/email_domain.sql:

    {% macro email_domain(email_col) %}
    split({{ email_col }}, '@')[offset(1)]
{% endmacro %}

  2. Use it in a new model models/marts/family_email_domains.sql:

    select
    member,
    {{ email_domain('email') }} as domain
from {{ ref('family_members') }}

  3. Run dbt compile -s family_email_domains and confirm the macro was inlined as expected.

  4. Add dbt_utils to your packages.yml, run dbt deps, and use dbt_utils.generate_surrogate_key() in a model to build a stable key from member + email.

  5. (Stretch) Add the dbt-nexus package alongside dbt_utils. Run dbt deps and confirm it lands in dbt_packages/. Don't try to use it yet — that's Module 2.

Summary

Concept Key takeaway
Macro A reusable Jinja function that emits SQL
Where they live macros/<name>.sql
Calling syntax {{ my_macro(arg1, arg2) }} in a model or another macro
Built-ins ref, source, var, target, this, is_incremental, run_query
Packages Bundles of macros / models / tests / sources, installed via dbt deps
dbt_utils Standard utility macros every nexus project leans on
dbt-nexus The package this whole course builds toward
When to extract When a snippet repeats; not as an end in itself

Next Lesson

You've covered the core dbt mental model: models, refs, the DAG, materializations, Jinja, macros, packages. Now apply all of it to a real project layout in 2.7 The Doe family project structure.