dbt packages are the primary mechanism for code reuse and standardization. They enable organizations to share common transformations, tests, and documentation across multiple projects.
Package Types
Type
Hosted On
Example
Public
dbt Hub
dbt-utils, dbt-expectations
Private
Private Git repos
Internal shared code
Local
Local file path
Development/testing
Internal
Organization-specific
Shared across teams
Package Components
Component
Purpose
Location
Macros
Reusable SQL/Jinja logic
macros/
Models
Reusable data transformations
models/
Tests
Custom data test logic
macros/ (test macros)
Sources
Source definitions
models/sources/
Documentation
Package descriptions
docs/
Variables
Configurable parameters
dbt_project.yml
Key Takeaway: Keep the package API small and well-documented. Use variables for configuration and always test against multiple dbt versions and database platforms.
-- macros/generate_schema_name.sql (package)
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- else -%}
{{ var('default_schema', default_schema) }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}
Package with Models
-- models/staging/stg_utils_dates.sql
{{
config(materialized='view')
}}
with date_spine as (
{{ dbt_utils.date_spine(
start_date="'" ~ var('date_start') ~ "'",
end_date="'" ~ var('date_end') ~ "'",
datepart="day"
)}}
),
formatted as (
select
date_day as date_value,
extract(year from date_day) as year,
extract(month from date_day) as month,
extract(day from date_day) as day,
extract(dayofweek from date_day) as day_of_week,
case
when extract(dayofweek from date_day) in (0, 6) then 'weekend'
else 'weekday'
end as day_type
from date_spine
)
select * from formatted
Package Test Macro
-- macros/tests/test_accepted_range.sql
{% test accepted_range(model, column_name, min_value=none, max_value=none, inclusive=true) %}
{%- set min_clause = "and " ~ column_name ~ " >= " ~ min_value if min_value is not none else "" -%}
{%- set max_clause = "and " ~ column_name ~ " <= " ~ max_value if max_value is not none else "" -%}
select
'{{ model }}' as model_name,
'{{ column_name }}' as column_name,
count(*) as failures
from {{ model }}
where {{ column_name }} is not null
and (
{{ "1=1" if min_value is none else column_name ~ " < " ~ min_value if not inclusive else column_name ~ " < " ~ min_value }}
or
{{ "1=1" if max_value is none else column_name ~ " > " ~ max_value if not inclusive else column_name ~ " > " ~ max_value }}
)
{% endtest %}
Versioning and Changelog
# CHANGELOG.md
## [1.2.0] - 2024-06-15
### Added
- `test_accepted_range` generic test with inclusive/exclusive options
- `date_spine` wrapper macro for cross-database compatibility
### Changed
- Updated `generate_schema_name` to support custom schema prefix
### Fixed
- Fixed null handling in `cast_column` macro
## [1.1.0] - 2024-05-01
### Added
- `dim_date` model for calendar dimensions
- `stg_utils_dates` staging model
## [1.0.0] - 2024-04-01
### Added
- Initial release
- `generate_schema_name` macro
- `cast_column` macro
-- integration_tests/models/test_model.sql
{{
config(materialized='ephemeral')
}}
with test_dates as (
select * from {{ ref('dim_date') }}
),
test_acceptance as (
select
count(*) as total_rows,
count(distinct date_value) as unique_dates,
min(date_value) as min_date,
max(date_value) as max_date
from test_dates
)
select
case
when total_rows = 31 then 'PASS'
else 'FAIL: expected 31 rows, got ' || total_rows
end as test_result
from test_acceptance