This dbt package contains macros that can be (re)used across dbt projects.

Installation Instructions

Check dbt Hub for the latest installation instructions, or read the docs for more information on installing packages.

---

Macros

Cross-database

current_timestamp (source)

This macro returns the current timestamp.

Usage:

{{ dbt_utils.current_timestamp() }}

dateadd (source)

This macro adds a time/day interval to the supplied date/timestamp. Note: The datepart argument is database-specific.

Usage:

{{ dbt_utils.dateadd(datepart='day', interval=1, from_date_or_timestamp="'2017-01-01'") }}

datediff (source)

This macro calculates the difference between two dates.

Usage:

{{ dbt_utils.datediff("'2018-01-01'", "'2018-01-20'", 'day') }}

split_part (source)

This macro splits a string of text using the supplied delimiter and returns the supplied part number (1-indexed).

Usage:

{{ dbt_utils.split_part(string_text='1,2,3', delimiter_text=',', part_number=1) }}

date_trunc (source)

Truncates a date or timestamp to the specified datepart. Note: The datepart argument is database-specific.

Usage:

{{ dbt_utils.date_trunc(datepart, date) }}

last_day (source)

Gets the last day for a given date and datepart. Notes:

• The datepart argument is database-specific.
• This macro currently only supports dateparts of month and quarter.

Usage:

{{ dbt_utils.last_day(date, datepart) }}

---

Date/Time

date_spine (source)

This macro returns the sql required to build a date spine.

Usage:

{{ dbt_utils.date_spine(
    datepart="minute",
    start_date="to_date('01/01/2016', 'mm/dd/yyyy')",
    end_date="dateadd(week, 1, current_date)"
   )
}}

---

Geo

haversine_distance (source)

This macro calculates the haversine distance between a pair of x/y coordinates.

Usage:

{{ dbt_utils.haversine_distance(lat1=<float>,lon1=<float>,lat2=<float>,lon2=<float>) }}

---

Schema Tests

equal_rowcount (source)

This schema test asserts the that two relations have the same number of rows.

Usage:

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('other_table_name')

equality (source)

This schema test asserts the equality of two relations.

Usage:

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.equality:
          compare_model: ref('other_table_name')

expression_is_true (source)

This schema test asserts that a valid sql expression is true for all records. This is useful when checking integrity across columns, for example, that a total is equal to the sum of its parts, or that at least one column is true.

Usage:

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.expression_is_true:
          expression: "col_a + col_b = total"

The macro accepts an optional parameter condition that allows for asserting the expression on a subset of all records.

Usage:

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.expression_is_true:
          expression: "col_a + col_b = total"
          condition: "created_at > '2018-12-31'"

recency (source)

This schema test asserts that there is data in the referenced model at least as recent as the defined interval prior to the current timestamp.

Usage:

version: 2

models:
  - name: model_name
    tests:
      - dbt_utils.recency:
          datepart: day
          field: created_at
          interval: 1

at_least_one (source)

This schema test asserts if column has at least one value.

Usage:

version: 2

models:
  - name: model_name
    columns:
      - name: col_name
        tests:
          - dbt_utils.at_least_one

not_constant (source)

This schema test asserts if column does not have same value in all rows.

Usage:

version: 2

models:
  - name: model_name
    columns:
      - name: column_name
        tests:
          - dbt_utils.not_constant

cardinality_equality (source)

This schema test asserts if values in a given column have exactly the same cardinality as values from a different column in a different model.

Usage:

version: 2

models:
  - name: model_name
    columns:
      - name: from_column
        tests:
          - dbt_utils.cardinality_equality:
              field: other_column_name
              to: ref('other_model_name')

relationships_where (source)

This test validates the referential integrity between two tables (same as the core relationships schema test) with an added predicate to filter out some rows from the test. This is useful to exclude records such as test entities, rows created in the last X minutes/hours to account for temporary gaps due to ETL limitations, etc.

Usage:

version: 2

models:
  - name: model_name
    columns:
      - name: id
        tests:
          - dbt_utils.relationships_where:
              to: ref('other_model_name')
              field: client_id
              from_condition: id <> '4ca448b8-24bf-4b88-96c6-b1609499c38b'

---

SQL helpers

get_column_values (source)

This macro returns the unique values for a column in a given relation. It takes an options default argument for compiling when the relation does not already exist.

Usage:

-- Returns a list of the top 50 states in the `users` table
{% set states = dbt_utils.get_column_values(table=ref('users'), column='state', max_records=50, default=[]) %}

{% for state in states %}
    ...
{% endfor %}

...

get_tables_by_prefix (source)

This macro returns a list of tables that match a given prefix, with an optional exclusion pattern. It's particularly handy paired with union_tables.

Usage:

-- Returns a list of tables that match schema.prefix%
{% set tables = dbt_utils.get_tables_by_prefix('schema', 'prefix') %}

-- Returns a list of tables as above, excluding any with underscores
{% set tables = dbt_utils.get_tables_by_prefix('schema', 'prefix', '%_%') %}

-- Example using the union_tables macro
{% set event_tables = dbt_utils.get_tables_by_prefix('events', 'event_') %}
{{ dbt_utils.union_tables(tables = event_tables) }}

group_by (source)

This macro build a group by statement for fields 1...N

Usage:

{{ dbt_utils.group_by(n=3) }} --> group by 1,2,3

star (source)

This macro generates a list of all fields that exist in the from relation, excluding any fields listed in the except argument. The construction is identical to select * from {{ref('my_model')}}, replacing star (*) with the star macro. This macro also has an optional relation_alias argument that will prefix all generated fields with an alias.

Usage:

select
{{ dbt_utils.star(from=ref('my_model'), except=["exclude_field_1", "exclude_field_2"]) }}
from {{ref('my_model')}}

union_tables (source)

This macro implements an "outer union." The list of relations provided to this macro will be unioned together, and any columns exclusive to a subset of these tables will be filled with null where not present. The column_override argument is used to explicitly assign the column type for a set of columns. The source_column_name argument is used to change the name of the_dbt_source_table field.

Usage:

{{ dbt_utils.union_tables(
    tables=[ref('table_1'), ref('table_2')],
    column_override={"some_field": "varchar(100)"},
    exclude=["some_other_field"],
    source_column_name='custom_source_column_name'
) }}

generate_series (source)

This macro implements a cross-database mechanism to generate an arbitrarily long list of numbers. Specify the maximum number you'd like in your list and it will create a 1-indexed SQL result set.

Usage:

{{ dbt_utils.generate_series(upper_bound=1000) }}

surrogate_key (source)

Implements a cross-database way to generate a hashed surrogate key using the fields specified.

Usage:

{{ dbt_utils.surrogate_key('field_a', 'field_b'[,...]) }}

pivot (source)

This macro pivots values from rows to columns.

Usage:

{{ dbt_utils.pivot(<column>, <list of values>) }}

Example:

Input: orders

| size | color |
|------|-------|
| S    | red   |
| S    | blue  |
| S    | red   |
| M    | red   |

select
  size,
  {{ dbt_utils.pivot(
      'color',
      dbt_utils.get_column_values(ref('orders'), 'color')
  ) }}
from {{ ref('orders') }}
group by size

Output:

| size | red | blue |
|------|-----|------|
| S    | 2   | 1    |
| M    | 1   | 0    |

Arguments:

- column: Column name, required
- values: List of row values to turn into columns, required
- alias: Whether to create column aliases, default is True
- agg: SQL aggregation function, default is sum
- cmp: SQL value comparison, default is =
- prefix: Column alias prefix, default is blank
- suffix: Column alias postfix, default is blank
- then_value: Value to use if comparison succeeds, default is 1
- else_value: Value to use if comparison fails, default is 0
- quote_identifiers: Whether to surround column aliases with double quotes, default is true

unpivot (source)

This macro "un-pivots" a table from wide format to long format. Functionality is similar to pandas melt function.

Usage:

{{ dbt_utils.unpivot(
  table=ref('table_name'),
  cast_to='datatype',
  exclude=[<list of columns to exclude from unpivot>],
  remove=[<list of columns to remove>],
  field_name=<column name for field>,
  value_name=<column name for value>
) }}

Example:

Input: orders

| date       | size | color | status     |
|------------|------|-------|------------|
| 2017-01-01 | S    | red   | complete   |
| 2017-03-01 | S    | red   | processing |

{{ dbt_utils.unpivot(ref('orders'), cast_to='varchar', exclude=['date','status']) }}

Output:

| date       | status     | field_name | value |
|------------|------------|------------|-------|
| 2017-01-01 | complete   | size       | S     |
| 2017-01-01 | complete   | color      | red   |
| 2017-03-01 | processing | size       | S     |
| 2017-03-01 | processing | color      | red   |

Arguments:

- table: Table name, required
- cast_to: The data type to cast the unpivoted values to, default is varchar
- exclude: A list of columns to exclude from the unpivot operation but keep in the resulting table.
- remove: A list of columns to remove from the resulting table.
- field_name: column name in the resulting table for field
- value_name: column name in the resulting table for value

---

Web

get_url_parameter (source)

This macro extracts a url parameter from a column containing a url.

Usage:

{{ dbt_utils.get_url_parameter(field='page_url', url_parameter='utm_source') }}

get_url_host (source)

This macro extracts a hostname from a column containing a url.

Usage:

{{ dbt_utils.get_url_host(field='page_url') }}

get_url_path (source)

This macro extracts a page path from a column containing a url.

Usage:

{{ dbt_utils.get_url_host(field='page_url') }}

---

Logger

pretty_time (source)

This macro returns a string of the current timestamp, optionally taking a datestring format.

{#- This will return a string like '14:50:34' -#}
{{ dbt_utils.pretty_time() }}

{#- This will return a string like '2019-05-02 14:50:34' -#}
{{ dbt_utils.pretty_time(format='%Y-%m-%d %H:%M:%S') }}

pretty_log_format (source)

This macro formats the input in a way that will print nicely to the command line when you log it.

{#- This will return a string like:
"11:07:31 + my pretty message"
-#}

{{ dbt_utils.pretty_log_format("my pretty message") }}

log_info (source)

This macro logs a formatted message (with a timestamp) to the command line.

{{ dbt_utils.log_info("my pretty message") }}

11:07:28 | 1 of 1 START table model analytics.fct_orders........................ [RUN]
11:07:31 + my pretty message

Materializations

insert_by_period (source)

insert_by_period allows dbt to insert records into a table one period (i.e. day, week) at a time.

This materialization is appropriate for event data that can be processed in discrete periods. It is similar in concept to the built-in incremental materialization, but has the added benefit of building the model in chunks even during a full-refresh so is particularly useful for models where the initial run can be problematic.

Should a run of a model using this materialization be interrupted, a subsequent run will continue building the target table from where it was interrupted (granted the --full-refresh flag is omitted).

Progress is logged in the command line for easy monitoring.

Usage:

{{
  config(
    materialized = "insert_by_period",
    period = "day",
    timestamp_field = "created_at",
    start_date = "2018-01-01",
    stop_date = "2018-06-01")
}}

with events as (

  select *
  from {{ ref('events') }}
  where __PERIOD_FILTER__ -- This will be replaced with a filter in the materialization code

)

....complex aggregates here....

Configuration values:

• period: period to break the model into, must be a valid datepart (default='Week')
• timestamp_field: the column name of the timestamp field that will be used to break the model into smaller queries
• start_date: literal date or timestamp - generally choose a date that is earlier than the start of your data
• stop_date: literal date or timestamp (default=current_timestamp)

Caveats:

• This materialization is compatible with dbt 0.10.1.
• This materialization has been written for Redshift.
• This materialization can only be used for a model where records are not expected to change after they are created.
• Any model post-hooks that use {{ this }} will fail using this materialization. For example:

models:
    project-name:
        post-hook: "grant select on {{ this }} to db_reader"

A useful workaround is to change the above post-hook to:

        post-hook: "grant select on {{ this.schema }}.{{ this.name }} to db_reader"

---

Contributing

We welcome contributions to this repo! To contribute a new feature or a fix, please open a Pull Request with 1) your changes, 2) updated documentation for the README.md file, and 3) a working integration test. See this page for more information.

---

Getting started with dbt

• What is dbt?
• Read the dbt viewpoint
• Installation
• Join the chat on Slack for live questions and support.

Code of Conduct

Everyone interacting in the dbt project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the PyPA Code of Conduct.