Blog  Dbt & Airflow

Ultimate dbt-utils Cheat Sheet

Q: How do I install dbt-utils in my dbt project?

Add the package to packages.yml as - package: dbt-labs/dbt_utils with version: [latest version], then run dbt deps to install. Always pin to a specific version to avoid unexpected breaking changes from future releases. Check the dbt-utils hub page for the current version.

ByMayra Peña 13 mins read

Table of Contents

dbt-utils Cheat sheet

SQL Generators

Generic tests

Key Summary

A practical cheat sheet for dbt-utils, covering essential macros, generic tests, and tips to streamline SQL modeling and boost data transformation workflows in dbt.

The dbt-utils package enhances the dbt experience by offering a suite of utility macros. Designed to tackle common SQL modeling patterns, it streamlines complex operations, allowing users to focus on data transformation rather than the intricacies of SQL. dbt-utils is a must-have tool for dbt aficionados!

dbt-utils Cheat sheet

The dbt-utils package is a gem in the world of data transformations. Let this cheat sheet guide you swiftly through its features, ensuring you get the most out of dbt-utils. Enjoy!

Get our free ebook dbt Cloud vs dbt Core

Comparing dbt Core and dbt Cloud? Download our eBook for insights on feature, pricing and total cost. Find the best fit for your business!

Get the PDF

SQL Generators

The SQL generators in the dbt-utils package streamline your modeling tasks. By automating common SQL patterns, they minimize manual coding and guarantee consistent, high-quality queries. Think of it as a handy toolkit for every dbt user’s SQL endeavors!

SQL Generators
date_spine(datepart, start_date, end_date) (view source)	This macro provides the SQL needed to create a sequence of dates. It starts with the 'start_date' (if it matches the specified date part) but doesn't go up to the 'end_date'.
deduplicate(relation, partition_by, order_by) (view source)	This macro produces the SQL needed to delete duplicate rows from a model, source, or CTE.
haversine_distance(lat1, lon1, lat2, lon2, unit='mi') (view source)	This macro computes the haversine distance between a pair of x/y coordinates. You can also specify a 'unit' for the distance.
group_by(n) (view source)	This macro constructs a "group by" statement for fields ranging from 1 to N.
star(from, except= [], relation_alias= [], prefix=' ', suffix=' ', quote_identifiers=True) (view source)	This macro creates a list of fields separated by commas from the 'from' argument, but it leaves out any fields mentioned in the 'except' argument. Further customizations are available with the optional arguments.
union_relations(relations, exclude, include, column_override, source_column_name='_dbt_source_relation', where) (view source)	This macro merges the items from the relations argument using 'union all'. It can handle when the columns are in different orders or when some columns are missing in certain Relations. Any unique columns will have 'null' values where they don't exist in some relations. Additionally, a new column, '_dbt_source_relation', is added to show where each record originated.
generate_series(upper_bound) (view source)	This macro creates a cross-database way to produce a list of numbers up to a specified maximum. It generates a SQL result set where the numbers start from 1 and go up to the number you've chosen.
generate_surrogate_key(<field list>) (view source)	This macro creates a cross-database way to produce a hashed surrogate key based on the fields you specify. Note: Older versions of this macro is called 'surrogate_key()'. Both nulls and blank strings were treated identically. If you need to revert to this behavior for reasons like maintaining backward compatibility, you should add the surrogate_key_treat_nulls_as_empty_strings variable to your 'dbt_project.yml'.
safe_add(<field list>) (view source)	This macro cross-database way to sum up fields that can have null values, based on the fields you indicate.
safe_divide(numerator, denominator) (view source)	This macro divides values but gives a null result if the bottom number (denominator) is 0.
safe_subtract(<field list>) (view source)	This macro implements a cross-database to calculate the difference between fields that might have null values, based on the fields you select.
pivot(column, values, alias=True, agg= 'sum', cmp='=', prefix= ' ', suffix= ' ', then_value= 1, else_value=0, quote_identifiers=True) (view source)	This macro transforms data by turning row values into column headers.
unpivot(relation, cast_to= 'varchar', exclude=none, remove=none, field_name=none, value_name=none, quote_identifiers=False) (view source)	This macro converts a table from a wide layout to a long layout, similar to the melt function in pandas. It replaces Boolean values with the strings 'true' or 'false'.
width_bucket(expr, min_value, max_value, num_buckets) (view source)	This macro creates equal-width histograms by dividing the range into uniform intervals. It then determines the bucket number for a given value after evaluating an expression. The output is either an integer representing the bucket number or null if any input is null.

Generic tests

^Within the dbt-utils package lies a set of generic tests, designed to validate your data effortlessly. These tests ensure consistency and quality, checking for common issues without the need to craft custom validation rules. It’s data integrity made simple for dbt users.

Generic Tests
equal_rowcount(compare_model, group_by_columns) (view source)	This test compares two models or tables and asserts they have the same number of rows.
fewer_rows_than(compare_model, group_by_columns) (view source)	This test asserts that a specific model or table has fewer rows than a specified threshold or compared to another table.
equality(compare_model, compare_columns, group_by_columns) (view source)	This test compares two models or tables and asserts their equality. You can specify a subset of columns to compare.
expression_is_true(expression, where) (view source)	The test ensures that a given SQL expression holds true for every record, which is particularly helpful in maintaining column integrity. For instance, this can be used to confirm results from simple algebraic operations between columns, validate the length of a column, or assess the truth value of a column. Note: Use the where clause to check a subset of records.
recency(datepart, field, interval, group_by_columns) (view source)	This test ensures the timestamp column in the given model has data that's newer than a specific date range.
at_least_one(group_by_columns) (view source)	This test checks a specified column and asserts it has at least one value.
not_constant(group_by_columns) (view source)	This test makes sure a column doesn't have the same value for every row.
not_empty_string(trim_whitespace=true) (view source)	This test asserts that none of the values are equal to an empty string. The optional argument, trim_whitespace will remove whitespace from the column.
cardinality_equality(field, to) (view source)	This test asserts that a specific column has the same number of unique values as another column in a different table.
not_null_proportion(at_least, at_most=1.0, group_by_columns) (view source)	This test checks that a column has non-empty values within a certain range. If you don't specify an upper limit, it assumes up to 100% (or 1.0) of the values can be non-empty.
not_accepted_values(values) (view source)	This test checks that there aren't any rows with the specified values.
relationships_where(to, field, from_condition, to_condition) (view source)	This test checks the connection between two models, similar to the basic relationship checks. It also allows for filtering out specific rows, like test entries or recent entries, which might have temporary inconsistencies because of data processing limits.
mutually_exclusive_ranges(lower_bound_column, upper_bound_column, partition_by=None, gaps='allowed', zero_length_range_allowed=False) (view source)	By setting the ‘lower_bound_column’ and ‘upper_bound_column’ ranges, This test asserts that the ranges don't overlap with those of other rows.
sequential_values(interval=1, datepart=None, group_by_columns) (view source)	This test checks that a column has values in order, either numbers or dates
unique_combination_of_columns(combination_of_columns, quote_columns=false) (view source)	This test ensures that when certain columns are combined, their values are unique. For instance, while a month or a product alone might repeat, the pairing of a month with a product is always distinct.
accepted_range(min_value, max_value, where, Inclusive=true) (view source)	This test ensures a column's values are within a certain range, set by a minimum and maximum value. You can choose if the range includes or excludes the boundary values. If needed, you can also focus on specific records with a filter. As long as the data type can be compared using > or < signs, you can use this test.
Grouping in tests	Some tests offer an extra group_by_columns option to get more detailed checks. Arg for group_by_column must be in list format. Tests that support this include: equal_rowcount, fewer_rows_than, recency, at_least_one, not_constant, sequential_values, non_null_proportion.

Introspective macros

The introspective macros within the dbt-utils package are a window into your data’s metadata. They empower you to dynamically interact with and understand the underlying structure of your datasets. It’s like having a magnifying glass for the intricacies of your dbt projects!

Introspective Macros
get_column_values(table, column, where=none, order_by='count(*) desc', max_records=none, default=[]) (view source)	This macro produces an array containing the distinct values of a column from a specified relation. Note: Setting order_by='my_column' will sort alphabetically, while order_by='min(created_at)' will sort by when the value was first observed.
get_filtered_columns_in_relation(from, except=[]) (view source)	This macro provides a Jinja list, containing columns from a specific relation. It's meant for actual relations and not for CTEs.
get_relations_by_pattern(schema_pattern, table_pattern, exclude, database=target.database) (view source)	This macro produces a list of Relations that align with a specified schema or table name pattern. It pairs well with the 'union_relations' macro.
get_relations_by_prefix(schema, prefix, exclude, database= target.database) (view source)	DEPRECATED SOON: Use 'get_relations_by_pattern' instead. This macro gives a list of Relations matching a specific prefix and has an option to exclude certain patterns. It works especially well when combined with 'union_relations'.
get_query_results_as_dict(<sql query>) (view source)	This macro provides a dictionary based on a SQL query, eliminating the need to work with the Agate library to process the outcome.
get_single_value(<sql query>) (view source)	This macro fetches a single value from a SQL query, allowing you to bypass the use of the Agate library when handling the result.

‍

Please contact us with any errors or suggestions.

FAQ

Does dbt-utils work across different data warehouses?

Yes. dbt-utils is designed to be warehouse-agnostic. Its cross-database macros detect your adapter and generate the correct SQL syntax automatically. It supports Snowflake, BigQuery, Redshift, Databricks, Postgres, DuckDB, and any warehouse with a supported dbt adapter.

How do I fix version compatibility errors when upgrading dbt-utils?

Most errors come from using deprecated macro names or mismatched dbt Core versions. Common fixes include replacing surrogate_key with generate_surrogate_key, replacing condition with where in expression_is_true, and updating packages.yml to the latest compatible version. If you are on dbt Core 1.10.6 or higher, nest test arguments under an arguments key.

How do I install dbt-utils in my dbt project?

Add the package to packages.yml as - package: dbt-labs/dbt_utils with version: [latest version], then run dbt deps to install. Always pin to a specific version to avoid unexpected breaking changes from future releases. Check the dbt-utils hub page for the current version.

Is dbt-utils still relevant now that dbt Core has absorbed some of its macros?

Yes. While many cross-database macros from dbt-utils have moved into dbt Core under the dbt namespace, dbt-utils remains the standard for testing utilities, SQL generators like date_spine and pivot, and introspective macros. The package continues to be actively maintained and is still recommended for production dbt projects.

What are the most commonly used dbt-utils macros?

The dbt-utils package provides a collection of reusable macros that simplify common data transformation patterns. Some of the most commonly used macros include:

• generate_surrogate_key: Creates a deterministic hashed key from one or more columns

• date_spine: Generates a continuous sequence of dates or timestamps

• union_relations: Combines multiple tables using UNION ALL while aligning columns automatically

• pivot: Converts row values into columns

• star: Selects all columns from a relation except those explicitly excluded

• get_column_values: Returns distinct values from a column, for generating SQL from data

• safe_divide: Performs division while returning null instead of raising an error when dividing by zero

dbt-utils also includes many reusable test macros that extend the tests provided with dbt Core, making it easier to validate data quality and enforce modeling assumptions.

What does the date_spine macro do and when is it useful?

date_spine generates a continuous sequence of dates between a start and end date. It is useful when your source data has gaps and you need a complete date range for time-series analysis, dashboards, or forecasting. Join your fact data to the date spine to fill missing periods and avoid broken trend lines in BI tools.

What generic tests does dbt-utils add that are not in dbt Core?

dbt-utils extends dbt’s built-in tests with additional checks including equal_rowcount, fewer_rows_than, expression_is_true, not_empty_string, sequential_values, and equality tests to compare two relations column by column.

What is dbt-utils and why do data teams use it?

dbt-utils is an open-source package maintained by dbt Labs that provides reusable macros, SQL generators, and generic tests for dbt projects. Data teams use it to eliminate repetitive SQL patterns, enforce data quality standards, and write cross-database compatible code. It is the most widely installed package in the dbt community and is used in many dbt projects.

What is generate_surrogate_key and when should I use it?

generate_surrogate_key is a macro from dbt-utils that creates a deterministic hashed identifier from one or more columns. It is commonly used to produce stable primary or join keys when your source data does not contain a reliable unique identifier.Surrogate keys are preferred over auto-incrementing IDs because they produce the same value across development, test, and production environments, ensuring consistent joins and reproducible models.

What is the difference between surrogate_key and generate_surrogate_key?

surrogate_key is the legacy version of the macro and was deprecated in dbt-utils v1.0. generate_surrogate_key is the current replacement and handles nulls more precisely, distinguishing between null values and empty strings. If you are still using surrogate_key, replace it with generate_surrogate_key in your models.

Last updated onDecember 29, 2024