Building and Managing dbt Packages: Creating Reusable Macros, Models, and Tests Across Multiple Analytics Projects

Introduction

Here's a situation that should feel familiar: you're six months into managing analytics for three separate dbt projects — one for marketing, one for finance, and one for operations. Each project has its own date_spine macro, its own not_null_proportion test, and its own set of staging conventions. When a bug crops up in the date logic, you fix it in one project, forget about the other two, and three weeks later someone finds a dashboard discrepancy that traces back to inconsistent date handling.

This is the package problem. dbt packages are the solution. Instead of copy-pasting logic across projects and hoping you remember to synchronize changes, packages let you define macros, models, and tests once, publish them to a shared location, and install them as dependencies in any project that needs them. Done well, this creates a genuine engineering culture around your analytics code — shared libraries, versioned releases, clear contracts between teams.

By the end of this lesson, you'll know how to build a production-grade dbt package from scratch, version and distribute it, consume it in downstream projects, and manage the lifecycle of shared analytics logic without creating a maintenance nightmare.

What you'll learn:

• How dbt packages work architecturally and when to build one versus when to use an existing community package
• How to structure a package project with reusable macros, models, and generic tests
• How to version, publish, and install packages from both GitHub and a private registry
• How to manage cross-project dependencies and handle breaking changes responsibly
• How to test a package in isolation before releasing it to dependent projects

---

Prerequisites

You should be comfortable with:

• Writing dbt models, macros, and YAML-based tests at an intermediate level
• Jinja templating basics (variable scoping, if/for blocks, caller())
• Git fundamentals — branching, tagging, releases
• Basic familiarity with packages.yml and dbt deps

If you've installed a community package like dbt-utils or dbt-expectations before, you're in the right place to learn how to build one yourself.

---

Understanding the Package Architecture

Before writing a single line of code, it's worth understanding what a dbt package actually is under the hood. A dbt package is simply a self-contained dbt project. It has a dbt_project.yml, it can have a models/ directory, a macros/ directory, and a tests/ directory. When you run dbt deps, dbt downloads that project and merges it into your local project's namespace.

That last point — merging into the namespace — is consequential. When you install a package, its macros become available in your project without any import statement. Its models compile and run alongside your own models. Its generic tests become available in your YAML files. This is powerful but also means you need to be deliberate about naming to avoid collisions.

The key architectural decision you'll make early is: what belongs in a package versus what belongs in the consuming project?

Good candidates for packages:

• Macros that implement generic data transformations (date spines, fiscal calendar logic, surrogate key generation)
• Generic tests that validate patterns across multiple domains (value between range, no gaps in sequence, referential integrity with a tolerance)
• Source freshness utilities and audit models
• Staging layer conventions that should be consistent across teams

Bad candidates for packages:

• Business logic that's specific to one domain (your specific revenue recognition rules)
• Models that depend on source tables only one project will ever have
• Quick fixes you haven't abstracted properly yet

Think of a package as a library. It should be general enough that two or more real consumers benefit from it, and it should have a clear, stable interface.

---

Setting Up Your Package Project

Let's build a real package. We'll call it analytics_toolkit — a package that provides shared utilities for a company running multiple dbt projects across their data platform.

Start by creating a new directory and initializing it as a dbt project:

mkdir analytics_toolkit
cd analytics_toolkit
dbt init analytics_toolkit --skip-profile-setup

Your initial structure will look like this:

analytics_toolkit/
├── dbt_project.yml
├── macros/
├── models/
├── tests/
└── README.md

Now configure dbt_project.yml. This is where package authorship starts to diverge from regular project configuration:

# dbt_project.yml
name: analytics_toolkit
version: "1.0.0"
config-version: 2

# This is critical — it tells dbt this is a package
# and prevents models from being materialized by default
# in the package itself during development

profile: analytics_toolkit_dev

models:
  analytics_toolkit:
    +materialized: view
    staging:
      +schema: staging
    utilities:
      +materialized: ephemeral

vars:
  analytics_toolkit:
    fiscal_year_start_month: 4  # Default to April; overridable by consumers
    date_spine_start: "2020-01-01"
    date_spine_end: "{{ modules.datetime.date.today().isoformat() }}"

Notice the vars block. Any variable your package uses should have a sensible default that consuming projects can override. This is the primary mechanism for configuring package behavior. Never hardcode business-specific values in a package.

---

Writing Production-Quality Macros

Macros are usually where packages deliver the most value. Let's build a few that solve real problems.

The Date Spine Macro

A date spine — a sequence of consecutive dates — is needed in almost every analytics project for filling gaps in time-series data. Here's a robust implementation:

-- macros/date_spine.sql

{% macro date_spine(
    datepart,
    start_date,
    end_date
) %}

{{ return(adapter.dispatch('date_spine', 'analytics_toolkit')(datepart, start_date, end_date)) }}

{% endmacro %}


{% macro default__date_spine(datepart, start_date, end_date) %}

with date_series as (
    {{ dbt_utils.date_spine(
        datepart=datepart,
        start_date=start_date,
        end_date=end_date
    ) }}
),

final as (
    select
        cast(date_{{ datepart }} as date) as date_day,
        extract(year from cast(date_{{ datepart }} as date)) as year_number,
        extract(month from cast(date_{{ datepart }} as date)) as month_number,
        extract(quarter from cast(date_{{ datepart }} as date)) as quarter_number,
        extract(dayofweek from cast(date_{{ datepart }} as date)) as day_of_week,
        case
            when extract(dayofweek from cast(date_{{ datepart }} as date)) in (1, 7)
            then true else false
        end as is_weekend,
        -- Fiscal year logic using the package variable
        case
            when extract(month from cast(date_{{ datepart }} as date)) 
                 >= {{ var('fiscal_year_start_month', 4) }}
            then extract(year from cast(date_{{ datepart }} as date)) + 1
            else extract(year from cast(date_{{ datepart }} as date))
        end as fiscal_year
    from date_series
)

select * from final

{% endmacro %}

Notice the adapter.dispatch pattern. This is how you write macros that work across multiple data warehouses. When Snowflake, BigQuery, and Redshift have different SQL dialects, dispatch lets you define a default__ implementation and override it with warehouse-specific ones like bigquery__date_spine or snowflake__date_spine. This is exactly what dbt-utils does internally, and your package should follow the same pattern.

A Surrogate Key Macro

-- macros/generate_surrogate_key.sql

{% macro generate_surrogate_key(field_list) %}

{{ return(adapter.dispatch('generate_surrogate_key', 'analytics_toolkit')(field_list)) }}

{% endmacro %}

{% macro default__generate_surrogate_key(field_list) %}

    {% if not field_list %}
        {{ exceptions.raise_compiler_error(
            "analytics_toolkit.generate_surrogate_key requires at least one field. "
            "Got an empty list."
        ) }}
    {% endif %}

    {%- set fields = [] -%}
    
    {%- for field in field_list -%}
        {%- set _ = fields.append(
            "coalesce(cast(" ~ field ~ " as " ~ dbt.type_string() ~ "), '')"
        ) -%}
    {%- endfor -%}

    {{ dbt.hash(dbt.concat(fields)) }}

{% endmacro %}

The exceptions.raise_compiler_error call is worth highlighting. Good package macros fail loudly with helpful messages. When a downstream developer passes an empty list, they get a clear error explaining what went wrong — not a cryptic SQL syntax error three steps later.

An Audit Helper Macro

This macro generates a standardized audit block that every model in a consuming project can include:

-- macros/audit_columns.sql

{% macro audit_columns(loaded_at_field=none) %}

    current_timestamp() as dbt_updated_at,
    '{{ invocation_id }}' as dbt_invocation_id,
    '{{ model.unique_id }}' as dbt_model_id
    {% if loaded_at_field %}
    , {{ loaded_at_field }} as source_loaded_at
    {% endif %}

{% endmacro %}

Consuming models use this as:

select
    order_id,
    customer_id,
    order_total,
    {{ analytics_toolkit.audit_columns(loaded_at_field='loaded_at') }}
from {{ source('ecommerce', 'orders') }}

---

Building Reusable Generic Tests

Generic tests are one of the most underutilized features in the package ecosystem. A generic test is a macro that dbt knows to call from YAML test blocks, and packages are a natural home for tests that encode cross-domain data quality rules.

The Value Between Test

-- tests/generic/value_between.sql

{% test value_between(model, column_name, min_value, max_value, inclusive=true) %}

{%- if min_value is none and max_value is none -%}
    {{ exceptions.raise_compiler_error(
        "value_between requires at least one of min_value or max_value."
    ) }}
{%- endif -%}

select
    {{ column_name }},
    count(*) as failing_row_count
from {{ model }}
where
    1=1
    {% if min_value is not none %}
        {% if inclusive %}
        and {{ column_name }} < {{ min_value }}
        {% else %}
        and {{ column_name }} <= {{ min_value }}
        {% endif %}
    {% endif %}
    {% if max_value is not none %}
        {% if inclusive %}
        or {{ column_name }} > {{ max_value }}
        {% else %}
        or {{ column_name }} >= {{ max_value }}
        {% endif %}
    {% endif %}
group by 1
having count(*) > 0

{% endtest %}

The No Gaps in Sequence Test

This is genuinely useful for validating event logs, invoice numbers, and any sequence that should be gapless:

-- tests/generic/no_gaps_in_sequence.sql

{% test no_gaps_in_sequence(model, column_name, partition_by=none) %}

with ordered_values as (
    select
        {{ column_name }} as seq_value,
        {% if partition_by %}
        {{ partition_by }},
        lead({{ column_name }}) over (
            partition by {{ partition_by }} 
            order by {{ column_name }}
        ) as next_seq_value
        {% else %}
        lead({{ column_name }}) over (
            order by {{ column_name }}
        ) as next_seq_value
        {% endif %}
    from {{ model }}
),

gaps as (
    select
        seq_value,
        next_seq_value,
        next_seq_value - seq_value as gap_size
    from ordered_values
    where next_seq_value is not null
      and next_seq_value - seq_value > 1
)

select * from gaps

{% endtest %}

Consuming projects use this test in YAML like:

# In a consuming project's schema.yml
models:
  - name: fct_invoices
    columns:
      - name: invoice_number
        tests:
          - analytics_toolkit.no_gaps_in_sequence:
              partition_by: customer_id
      - name: total_amount
        tests:
          - analytics_toolkit.value_between:
              min_value: 0
              max_value: 1000000

---

Including Models in a Package

Sometimes the right thing to package is a model — or a set of models — that consuming projects should materialize in their own warehouse. Common examples include a date dimension, an audit log model, or a base layer of utility tables.

-- models/utilities/dim_date.sql

{{
    config(
        materialized='table',
        tags=['utility', 'date-dimension']
    )
}}

{{ analytics_toolkit.date_spine(
    datepart='day',
    start_date="cast('" ~ var('date_spine_start', '2020-01-01') ~ "' as date)",
    end_date="cast('" ~ var('date_spine_end', modules.datetime.date.today().isoformat()) ~ "' as date)"
) }}

When a consuming project runs dbt run --select analytics_toolkit, it will materialize this model in its own target schema. You can also mark package models as ephemeral if they're meant to be inlined rather than persisted.

---

Declaring Package Dependencies

Your analytics_toolkit package depends on dbt-utils. You need to declare that dependency so dbt can resolve it when the package is installed:

# packages.yml (in the package project root)
packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]

Using version ranges rather than pinned versions is deliberate here. If you pin to 1.1.2 and a consuming project needs 1.2.0 for some other reason, they'll hit a dependency conflict. Ranges give you flexibility while still protecting against breaking changes. Follow semantic versioning logic: if dbt-utils follows semver, pin the major version and allow minor/patch updates.

---

Versioning and Publishing Your Package

dbt packages are distributed via Git. There's no package registry in the npm or PyPI sense for private packages — you publish by pushing to a Git repository and tagging releases.

Tagging Releases

Follow semantic versioning:

• Patch release (1.0.1): Bug fixes, no API changes
• Minor release (1.1.0): New macros or tests added, backwards compatible
• Major release (2.0.0): Breaking changes — renamed macros, changed argument signatures, removed features

# After committing your changes
git tag -a v1.0.0 -m "Initial release of analytics_toolkit"
git push origin v1.0.0

Writing a CHANGELOG

The CHANGELOG is not optional for a shared package. Downstream teams need to know what changed between versions so they can decide when to upgrade. Structure it clearly:

# Changelog

## [1.1.0] - 2024-03-15

### Added
- `no_gaps_in_sequence` generic test with optional `partition_by` argument
- `audit_columns` macro for standardized lineage tracking

### Changed
- `generate_surrogate_key` now raises a compiler error on empty field list (previously returned NULL)

## [1.0.0] - 2024-01-10

### Added
- Initial release with `date_spine`, `generate_surrogate_key`, and `value_between` test

Installing the Package in Consuming Projects

In any project that needs your package:

# packages.yml (in the consuming project)
packages:
  - git: "https://github.com/your-org/analytics_toolkit.git"
    revision: v1.1.0

For private repositories, use SSH:

packages:
  - git: "git@github.com:your-org/analytics_toolkit.git"
    revision: v1.1.0

Then run:

dbt deps

dbt clones the package into dbt_packages/analytics_toolkit/ and makes all its macros and tests available. Consuming projects should add dbt_packages/ to .gitignore — it's a build artifact, not source code.

---

Managing Cross-Project Variables

One of the trickiest aspects of package design is configuration. Your package may need to know things that differ between consuming projects — fiscal year definitions, timezone settings, schema naming conventions.

The right approach is the var() function with sensible defaults. Here's a pattern for documenting your package's expected variables:

# dbt_project.yml of the package
vars:
  analytics_toolkit:
    # The month number when the fiscal year begins (1=January, 4=April, 7=July, 10=October)
    # Override in your project's dbt_project.yml
    fiscal_year_start_month: 4
    
    # The earliest date to include in the date spine
    date_spine_start: "2020-01-01"
    
    # Whether to include weekend flags in date dimension
    include_weekend_flags: true

Consuming projects override these in their own dbt_project.yml:

# In the consuming project's dbt_project.yml
vars:
  # Overriding the package's fiscal year default
  fiscal_year_start_month: 1  # January fiscal year for this entity
  date_spine_start: "2018-01-01"  # Earlier start for this project's history

---

Testing Your Package in Isolation

Testing a package before publishing is a step that teams skip and regret. The challenge is that packages often need data to test against, but they don't have sources — they're meant to be source-agnostic.

The solution is a separate integration_tests/ directory inside your package repository that acts as a full dbt project consuming your package:

analytics_toolkit/
├── dbt_project.yml
├── macros/
├── models/
├── tests/
├── packages.yml
└── integration_tests/
    ├── dbt_project.yml
    ├── packages.yml
    ├── models/
    │   ├── test_date_spine.sql
    │   └── test_surrogate_key.sql
    └── seeds/
        └── sample_invoices.csv

The integration_tests/packages.yml installs your package from the local filesystem:

# integration_tests/packages.yml
packages:
  - local: ../

The integration test models exercise your macros with real data:

-- integration_tests/models/test_date_spine.sql
-- This model should compile and return rows for every day in the range

{{ analytics_toolkit.date_spine(
    datepart='day',
    start_date="cast('2024-01-01' as date)",
    end_date="cast('2024-03-31' as date)"
) }}

And you validate your generic tests work against known data:

# integration_tests/models/schema.yml
models:
  - name: test_date_spine
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('expected_date_count')  # a seed with expected row count
    columns:
      - name: date_day
        tests:
          - not_null
          - unique

  - name: test_invoice_sequence
    columns:
      - name: invoice_number
        tests:
          - analytics_toolkit.no_gaps_in_sequence:
              partition_by: customer_id

Run your integration tests in CI before every release:

cd integration_tests
dbt deps
dbt seed
dbt run
dbt test

---

Hands-On Exercise

In this exercise, you'll build a minimal but complete analytics package called company_analytics_core and install it in a consuming project.

Part 1: Create the Package

Create a new directory company_analytics_core and initialize it as a dbt project. Configure dbt_project.yml with a version of 1.0.0 and a package-level variable default_currency that defaults to USD.

Part 2: Write a Currency Conversion Macro

Create macros/convert_currency.sql. The macro should take three arguments: amount_column, from_currency_column, and exchange_rate_column. It should return a SQL expression that multiplies the amount by the exchange rate and casts the result to a numeric type with two decimal places. Include a null guard: if any input is null, return null rather than an error.

{% macro convert_currency(amount_column, from_currency_column, exchange_rate_column) %}

    case
        when {{ amount_column }} is null 
          or {{ exchange_rate_column }} is null
        then null
        else round(
            cast({{ amount_column }} as numeric) 
            * cast({{ exchange_rate_column }} as numeric),
            2
        )
    end

{% endmacro %}

Part 3: Write a Generic Test

Create tests/generic/not_null_proportion.sql — a test that passes when fewer than a specified proportion of values in a column are null. The test should accept max_null_proportion as an argument (a decimal between 0 and 1).

{% test not_null_proportion(model, column_name, max_null_proportion=0.05) %}

with validation as (
    select
        count(*) as total_rows,
        sum(case when {{ column_name }} is null then 1 else 0 end) as null_rows
    from {{ model }}
),
calculated as (
    select
        total_rows,
        null_rows,
        case 
            when total_rows = 0 then 0
            else cast(null_rows as numeric) / cast(total_rows as numeric) 
        end as null_proportion
    from validation
)
select *
from calculated
where null_proportion > {{ max_null_proportion }}

{% endtest %}

Part 4: Set Up Integration Tests

Create integration_tests/ with a seed file seeds/sample_transactions.csv:

transaction_id,amount,currency,exchange_rate
1,100.00,EUR,1.08
2,250.50,GBP,1.27
3,,USD,1.00
4,75.00,JPY,

Create a model that uses your convert_currency macro against this seed, then write a schema test that validates the not_null_proportion of the result (null_proportion should be less than 0.60 — two of four rows will return null).

Part 5: Publish and Install

Initialize a git repo in company_analytics_core, commit everything, and tag it v1.0.0. Create a separate directory consuming_project with a fresh dbt project. Add a packages.yml that installs your package from the local path using local: ../company_analytics_core. Run dbt deps and verify your macro and test are available.

---

Common Mistakes & Troubleshooting

Mistake: Hardcoding warehouse-specific SQL in macros

If you write extract(dayofweek from date_col) without checking the adapter, your macro will fail on BigQuery (which uses extract(dayofweek from date_col)) but produce wrong results on Snowflake (where day of week starts at 0, not 1, depending on the session setting). Always use adapter.dispatch for anything with warehouse-specific behavior, and use dbt's cross-database macros from dbt-core where they exist.

Mistake: Forgetting to namespace test calls in YAML

Generic tests from packages must be prefixed with the package name in YAML:

# Wrong — this looks for a test named 'no_gaps_in_sequence' in the consuming project
- no_gaps_in_sequence

# Correct
- analytics_toolkit.no_gaps_in_sequence

Without the namespace, dbt looks in the consuming project first and raises an error about an undefined test.

Mistake: Pinning to a branch instead of a tag

# Dangerous
packages:
  - git: "https://github.com/your-org/analytics_toolkit.git"
    revision: main

Pinning to main means your consuming project installs whatever is on main at the time dbt deps runs. This is non-deterministic — your CI build today might work, and tomorrow's might not. Always pin to a specific tagged version.

Mistake: Not testing that var() defaults work

If your macro uses var('fiscal_year_start_month', 4) but the consuming project's dbt_project.yml has a typo in the variable name, dbt silently falls back to the default. Run your integration tests with variable overrides explicitly set to catch these misconfigurations early.

Mistake: Breaking changes without a major version bump

Renaming a macro argument from start_date to date_start is a breaking change. Any consuming project that passes that argument by name will break silently (dbt will use the default value instead). If you must rename arguments, keep the old name as an alias using a deprecation warning pattern:

{% macro date_spine(datepart, start_date=none, date_start=none, end_date) %}
    
    {%- if start_date is none and date_start is none -%}
        {{ exceptions.raise_compiler_error("Provide start_date or date_start") }}
    {%- endif -%}
    
    {%- set _start = start_date if start_date is not none else date_start -%}
    
    {% if start_date is not none %}
        {{ log("WARNING: analytics_toolkit.date_spine argument 'start_date' is deprecated. Use 'date_start' instead.", info=true) }}
    {% endif %}
    
    {# rest of macro logic using _start #}
    
{% endmacro %}

Troubleshooting: "macro 'analytics_toolkit.generate_surrogate_key' not found"

This usually means one of three things:

1. dbt deps hasn't been run after adding the package to packages.yml
2. The macro file has a syntax error that prevented it from compiling (check dbt parse)
3. The macro name in the call doesn't match the macro name in the file

Run dbt parse before dbt run to catch compile errors early.

Troubleshooting: Dependency conflicts between package versions

If your package requires dbt-utils >= 1.0.0 and another installed package requires dbt-utils == 0.9.2, dbt will raise a dependency conflict error. Check dbt_packages/ after running dbt deps to see what resolved. The fix is usually to relax your version constraint or upgrade the conflicting package.

---

Summary & Next Steps

You've now got a complete mental model for building and managing dbt packages as a serious engineering discipline. The key ideas to carry forward:

• Packages are dbt projects. They have the same structure, the same configuration, and the same testing mechanisms. The only difference is intent — they're designed to be consumed by other projects.
• adapter.dispatch is not optional if you want your package to work across warehouses. Build it in from the start.
• Variables are your configuration interface. Define them with sensible defaults in the package; let consuming projects override them in their own dbt_project.yml.
• Integration tests are your quality gate. Maintain a proper integration_tests/ directory and run it in CI before every release.
• Semantic versioning is a contract. Major bumps for breaking changes, minor bumps for additions, patch bumps for fixes. Your downstream teams are depending on this discipline.

Where to go next:

• Study the source code of dbt-utils and dbt-expectations on GitHub. They're the gold standard for package design and cover edge cases you'll encounter building your own.
• Look into dbt Hub to understand what already exists before building something from scratch.
• Explore dbt's dispatch documentation for advanced cross-database macro patterns.
• If your organization has many teams consuming your package, consider setting up a private dbt Package Hub using Artifactory or a private GitHub Packages registry to manage access and versioning at scale.
• Learn about dbt's project dependencies feature (available in dbt Cloud), which extends the package concept with cross-project ref support — letting you reference models from other projects without copying them.

The best analytics teams treat their shared dbt packages the same way software engineering teams treat shared libraries: with versioning, changelogs, pull request reviews, and release notes. Start small, test thoroughly, and your package will become the kind of foundation that makes the whole organization's analytics faster and more reliable.