dbt Packages and Hub

dbt packages are collections of pre-built models, macros, and tests that other teams have shared publicly. Instead of writing common data transformations from scratch, you install a package and use its ready-made solutions. The dbt Package Hub at hub.getdbt.com lists all available packages.

What Packages Contain

A dbt package is just another dbt project. When you install it, its macros, models, and tests become available inside your project as if you had written them yourself.

Package Contents    What You Get
----------------    ------------
Macros              Reusable SQL functions you can call in your models
Models              Pre-built transformation models (e.g. Stripe, Shopify models)
Generic tests       Additional data quality tests beyond dbt's four built-ins
Seeds               Reference data (e.g. date dimension, country codes)

Creating packages.yml

To install packages, create a file named packages.yml in the root of your project:

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: 1.2.0
  - package: calogica/dbt_expectations
    version: 0.10.3
  - package: dbt-labs/codegen
    version: 0.12.1

Installing Packages

dbt deps

This command downloads all packages listed in packages.yml into a dbt_packages/ folder. Run it after creating or updating packages.yml.

$ dbt deps
Running with dbt=1.8.3
Installing dbt-labs/dbt_utils@1.2.0
  Installed from version 1.2.0
Installing calogica/dbt_expectations@0.10.3
  Installed from version 0.10.3
All packages installed.

The dbt_utils Package

The most popular package. It provides utilities that fill gaps in standard SQL across different warehouse adapters.

Most Used dbt_utils Macros

{{ dbt_utils.generate_surrogate_key(['order_id', 'product_id']) }}
-- Generates a hashed surrogate key from multiple columns

{{ dbt_utils.star(from=ref('stg_orders'), except=["_fivetran_deleted"]) }}
-- Selects all columns except the ones listed

{{ dbt_utils.date_spine(datepart="day", start_date="2024-01-01", end_date="2024-12-31") }}
-- Generates a table with one row per day in the range

{{ dbt_utils.pivot(column='payment_method', values=['credit_card','paypal','bank_transfer']) }}
-- Pivots a column into multiple boolean columns

dbt_utils Generic Tests

# Test that no NULL values appear together in multiple columns
- dbt_utils.not_null_proportion:
    at_least: 0.95
    # at least 95% of rows must be non-null

# Test that a combination of columns is unique
- dbt_utils.unique_combination_of_columns:
    combination_of_columns:
      - order_id
      - product_id

The dbt_expectations Package

Inspired by the Great Expectations Python library. Provides over 50 additional generic tests for thorough data quality checking.

# Test that values are within a range
- dbt_expectations.expect_column_values_to_be_between:
    min_value: 0
    max_value: 10000

# Test that column has no leading/trailing whitespace
- dbt_expectations.expect_column_values_to_not_match_regex:
    regex: "^\\s|\\s$"

# Test that column value is a valid email format
- dbt_expectations.expect_column_values_to_match_regex:
    regex: "^[a-zA-Z0-9._%+\\-]+@[a-zA-Z0-9.\\-]+\\.[a-zA-Z]{2,}$"

# Test row count is within expected range
- dbt_expectations.expect_table_row_count_to_be_between:
    min_value: 1000
    max_value: 1000000

The codegen Package

Generates boilerplate YAML for your models automatically. Instead of writing schema.yml files by hand, run a codegen macro and it generates the YAML from the actual warehouse table:

# Generate YAML for all columns in stg_orders
dbt run-operation codegen.generate_model_yaml \
    --args '{"model_names": ["stg_orders"]}'

Output:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        description: ''
      - name: customer_id
        description: ''
      - name: order_date
        description: ''
      - name: amount_dollars
        description: ''

Copy this output into your schema.yml and fill in the descriptions.

Installing from GitHub

Install a package directly from a GitHub repository instead of the Hub:

packages:
  - git: "https://github.com/your-org/your-private-dbt-package"
    revision: main   # branch, tag, or commit hash

Use this for private packages your organization builds internally.

gitignore for Packages

Always add dbt_packages/ to your .gitignore file. Packages are downloaded by dbt deps and should not be committed to your repository:

# .gitignore
target/
dbt_packages/
logs/