---

dbt-utils

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

Macros

Cross-database

current_timestamp (source)

This macro returns the current timestamp.

Usage:

{{ current_timestamp() }}

dateadd (source)

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

Usage:

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

split_part (source)

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

Usage:

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

---

Date/Time

date_spine (source)

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

Usage:

{{ date_spine(
    table=ref('organizations'),
    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:

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

---

Schema Tests

equality (source)

This schema test asserts the equality of two relations.

Usage:

model_name:
  constraints:
    equality:
      - ref('other_table_name')

at_least_one (source)

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

Usage:

model_name:
  constraints:
    at_least_one:
      - column_name

not_constant (source)

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

Usage:

model_name:
  constraints:
    not_constant:
      - column_name

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:

model_name:
  constraints:
    cardinality_equality:
    - {from: column_name, to: ref('other_model_name'), field: other_column_name}

---

SQL helpers

group_by (source)

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

Usage:

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

star (source)

This macro generates a select statement for each field that exists in the from relation. Fields listed in the except argument will be excluded from this list.

Usage:

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

union_tables (source)

This macro implements an "outer union." The list of tables 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.

Usage:

{{ union_tables(tables=[ref('table_1'), ref('table_2')], column_override={"some_field": "varchar(100)"}) }}

get_column_values (source)

This macro returns the unique values for a column in a given table.

Usage:

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

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

...

---

Web

get_url_parameter (source)

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

Usage:

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

---

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.