How to Use dbt_utils Package in dbt Projects

To use the dbt_utils package, add it to your packages.yml file and run dbt deps to install it. Then, you can call its macros in your models using {{ dbt_utils.macro_name() }} to simplify common SQL tasks.

Syntax

The dbt_utils package is added to your project by listing it in the packages.yml file. You then install it with dbt deps. Inside your SQL models, you use its macros by calling {{ dbt_utils.macro_name(arguments) }}. This lets you reuse tested SQL snippets easily.

yaml and sql

packages:
  - package: dbt-labs/dbt_utils
    version: [">=0.8.0", "<1.0.0"]

-- In your model SQL file
select * from {{ dbt_utils.star(from=ref('my_table'), except=['id']) }}

Example

This example shows how to use the dbt_utils.star macro to select all columns except one from a referenced table.

yaml and sql

packages:
  - package: dbt-labs/dbt_utils
    version: ">=0.8.0,<1.0.0"

-- Run in terminal:
dbt deps

-- In models/my_model.sql
select
  {{ dbt_utils.star(from=ref('raw_customers'), except=['password']) }}
from {{ ref('raw_customers') }}

Output

This SQL will select all columns from the 'raw_customers' table except the 'password' column.

Common Pitfalls

Common mistakes include forgetting to run dbt deps after adding dbt_utils to packages.yml, which means macros won't be available. Another is calling macros without the dbt_utils. prefix or using incorrect macro arguments. Also, ensure your dbt version supports the dbt_utils version you install.

sql

-- Wrong: missing dbt_utils prefix
select {{ star(from=ref('table')) }} from {{ ref('table') }}

-- Right:
select {{ dbt_utils.star(from=ref('table')) }} from {{ ref('table') }}

Quick Reference

Macro	Purpose	Example Usage
star	Select all columns with options to exclude or rename	{{ dbt_utils.star(from=ref('table'), except=['id']) }}
surrogate_key	Create a unique key from multiple columns	{{ dbt_utils.surrogate_key(['col1', 'col2']) }}
get_column_values	Get distinct values from a column	{{ dbt_utils.get_column_values(ref('table'), 'column') }}
date_spine	Generate a continuous date range	{{ dbt_utils.date_spine('day', '2023-01-01', '2023-01-31') }}

Key Takeaways

Add dbt_utils to packages.yml and run dbt deps to install it.

Use macros with the dbt_utils prefix inside your SQL models.

Macros simplify common SQL tasks like selecting columns or generating keys.

Always check macro arguments and your dbt version compatibility.

Run dbt deps after any package changes to avoid missing macros.