Skip to main content

Cross-database macros

These macros benefit three different user groups:

  • If you maintain a package, your package is more likely to work on other adapters by using these macros (rather than a specific database's SQL syntax)
  • If you maintain an adapter, your adapter is more likely to support more packages by implementing (and testing) these macros.
  • If you're an end user, more packages and adapters are likely to "just work" for you (without you having to do anything).
Note

Please make sure to take a look at the SQL expressions section to understand quoting syntax for string values and date literals.

All functions (alphabetical)​

Data type functions

Set functions

Array functions

String functions

String literal functions

Aggregate and window functions

Cast functions

Date and time functions

Data type functions​

type_bigint​

Args:

  • None

This macro yields the database-specific data type for a BIGINT.

Usage:

{{ dbt.type_bigint() }}

Sample Output (PostgreSQL):

bigint

type_boolean​

Args:

  • None

This macro yields the database-specific data type for a BOOLEAN.

Usage:

{{ dbt.type_boolean() }}

Sample Output (PostgreSQL):

BOOLEAN

type_float​

Args:

  • None

This macro yields the database-specific data type for a FLOAT.

Usage:

{{ dbt.type_float() }}

Sample Output (PostgreSQL):

FLOAT

type_int​

Args:

  • None

This macro yields the database-specific data type for an INT.

Usage:

{{ dbt.type_int() }}

Sample Output (PostgreSQL):

INT

type_numeric​

Args:

  • None

This macro yields the database-specific data type for a NUMERIC.

Usage:

{{ dbt.type_numeric() }}

Sample Output (PostgreSQL):

numeric(28,6)

type_string​

Args:

  • None

This macro yields the database-specific data type for TEXT.

Usage:

{{ dbt.type_string() }}

Sample Output (PostgreSQL):

TEXT

type_timestamp​

Args:

  • None

This macro yields the database-specific data type for a TIMESTAMP (which may or may not match the behavior of TIMESTAMP WITHOUT TIMEZONE from ANSI SQL-92).

Usage:

{{ dbt.type_timestamp() }}

Sample Output (PostgreSQL):

TIMESTAMP

current_timestamp​

This macro returns the current date and time for the system. Depending on the adapter:

  • The result may be an aware or naive timestamp.
  • The result may correspond to the start of the statement or the start of the transaction.

Args

  • None

Usage

  • You can use the current_timestamp() macro within your dbt SQL files like this:
{{ dbt.current_timestamp() }}

Sample output (PostgreSQL)

now()

Set functions​

except​

Args:

  • None

except is one of the set operators specified ANSI SQL-92 (along with union and intersect) and is akin to set difference.

Usage:

{{ dbt.except() }}

Sample Output (PostgreSQL):

except

intersect​

Args:

  • None

intersect is one of the set operators specified ANSI SQL-92 (along with union and except) and is akin to set intersection.

Usage:

{{ dbt.intersect() }}

Sample Output (PostgreSQL):

intersect

Array functions​

array_append​

Args:

  • array (required): The array to append to.
  • new_element (required): The element to be appended. This element must match the data type of the existing elements in the array in order to match PostgreSQL functionality and not null to match BigQuery functionality.

This macro appends an element to the end of an array and returns the appended array.

Usage:

{{ dbt.array_append("array_column", "element_column") }}
{{ dbt.array_append("array_column", "5") }}
{{ dbt.array_append("array_column", "'blue'") }}

Sample Output (PostgreSQL):

array_append(array_column, element_column)
array_append(array_column, 5)
array_append(array_column, 'blue')

array_concat​

Args:

  • array_1 (required): The array to append to.
  • array_2 (required): The array to be appended to array_1. This array must match the data type of array_1 in order to match PostgreSQL functionality.

This macro returns the concatenation of two arrays.

Usage:

{{ dbt.array_concat("array_column_1", "array_column_2") }}

Sample Output (PostgreSQL):

array_cat(array_column_1, array_column_2)

array_construct​

Args:

  • inputs (optional): The list of array contents. If not provided, this macro will create an empty array. All inputs must be the same data type in order to match PostgreSQL functionality and not null to match BigQuery functionality.
  • data_type (optional): Specifies the data type of the constructed array. This is only relevant when creating an empty array (will otherwise use the data type of the inputs). If inputs are data_type are both not provided, this macro will create an empty array of type integer.

This macro returns an array constructed from a set of inputs.

Usage:

{{ dbt.array_construct(["column_1", "column_2", "column_3"]) }}
{{ dbt.array_construct([], "integer") }}
{{ dbt.array_construct([1, 2, 3, 4]) }}
{{ dbt.array_construct(["'blue'", "'green'"]) }}

Sample Output (PostgreSQL):

array[ column_1 , column_2 , column_3 ]
array[]::integer[]
array[ 1 , 2 , 3 , 4 ]
array[ 'blue' , 'green' ]

String functions​

concat​

Args:

This macro combines a list of strings together.

Usage:

{{ dbt.concat(["column_1", "column_2"]) }}
{{ dbt.concat(["year_column", "'-'" , "month_column", "'-'" , "day_column"]) }}
{{ dbt.concat(["first_part_column", "'.'" , "second_part_column"]) }}
{{ dbt.concat(["first_part_column", "','" , "second_part_column"]) }}

Sample Output (PostgreSQL):

column_1 || column_2
year_column || '-' || month_column || '-' || day_column
first_part_column || '.' || second_part_column
first_part_column || ',' || second_part_column

hash​

Args:

This macro provides a hash (such as MD5) of an expression cast as a string.

Usage:

{{ dbt.hash("column") }}
{{ dbt.hash("'Pennsylvania'") }}

Sample Output (PostgreSQL):

md5(cast(column as 
    varchar
))
md5(cast('Pennsylvania' as 
    varchar
))

length​

Args:

This macro calculates the number of characters in a string.

Usage:

{{ dbt.length("column") }}

Sample Output (PostgreSQL):

    length(
        column
    )

position​

Args:

This macro searches for the first occurrence of substring_text within string_text and returns the 1-based position if found.

Usage:

{{ dbt.position("substring_column", "text_column") }}
{{ dbt.position("'-'", "text_column") }}

Sample Output (PostgreSQL):

    position(
        substring_column in text_column
    )

    position(
        '-' in text_column
    )

replace​

Args:

This macro updates a string and replaces all occurrences of one substring with another. The precise behavior may vary slightly from one adapter to another.

Usage:

{{ dbt.replace("string_text_column", "old_chars_column", "new_chars_column") }}
{{ dbt.replace("string_text_column", "'-'", "'_'") }}

Sample Output (PostgreSQL):

    replace(
        string_text_column,
        old_chars_column,
        new_chars_column
    )

    replace(
        string_text_column,
        '-',
        '_'
    )

Args:

This macro returns the N rightmost characters from a string.

Usage:

{{ dbt.right("string_text_column", "length_column") }}
{{ dbt.right("string_text_column", "3") }}

Sample Output (PostgreSQL):

    right(
        string_text_column,
        length_column
    )

    right(
        string_text_column,
        3
    )

split_part​

Args:

  • string_text (required): Text to be split into parts.
  • delimiter_text (required): Text representing the delimiter to split by.
  • part_number (required): Requested part of the split (1-based). If the value is negative, the parts are counted backward from the end of the string.

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

Usage:

When referencing a column, use one pair of quotes. When referencing a string, use single quotes enclosed in double quotes.

{{ dbt.split_part(string_text='column_to_split', delimiter_text='delimiter_column', part_number=1) }}
{{ dbt.split_part(string_text="'1|2|3'", delimiter_text="'|'", part_number=1) }}

Sample Output (PostgreSQL):

    split_part(
        column_to_split,
        delimiter_column,
        1
        )

    split_part(
        '1|2|3',
        '|',
        1
        )

String literal functions​

escape_single_quotes​

Args:

  • value: Jinja string literal value

This macro adds escape characters for any single quotes within the provided string literal. Note: if given a column, it will only operate on the column name, not the values within the column.

To escape quotes for column values, consider a macro like replace or a regular expression replace.

Usage:

{{ dbt.escape_single_quotes("they're") }}
{{ dbt.escape_single_quotes("ain't ain't a word") }}

Sample Output (PostgreSQL):

they''re
ain''t ain''t a word

string_literal​

Args:

  • value: Jinja string value

This macro converts a Jinja string into a SQL string literal.

To cast column values to a string, consider a macro like safe_cast or an ordinary cast.

Usage:

select {{ dbt.string_literal("Pennsylvania") }}

Sample Output (PostgreSQL):

select 'Pennsylvania'

Aggregate and window functions​

any_value​

Args:

This macro returns some value of the expression from the group. The selected value is non-deterministic (rather than random).

Usage:

{{ dbt.any_value("column_name") }}

Sample Output (PostgreSQL):

any(column_name)

bool_or​

Args:

This macro returns the logical OR of all non-NULL expressions -- true if at least one record in the group evaluates to true.

Usage:

{{ dbt.bool_or("boolean_column") }}
{{ dbt.bool_or("integer_column = 3") }}
{{ dbt.bool_or("string_column = 'Pennsylvania'") }}
{{ dbt.bool_or("column1 = column2") }}

Sample Output (PostgreSQL):

bool_or(boolean_column)
bool_or(integer_column = 3)
bool_or(string_column = 'Pennsylvania')
bool_or(column1 = column2)

listagg​

Args:

  • measure (required): The attribute name or expression that determines the values to be concatenated. To only include distinct values add keyword DISTINCT to beginning of expression (example: 'DISTINCT column_to_agg').
  • delimiter_text (required): Text representing the delimiter to separate concatenated values by.
  • order_by_clause (optional): An expression (typically one or more column names separated by commas) that determines the order of the concatenated values.
  • limit_num (optional): Specifies the maximum number of values to be concatenated.

This macro returns the concatenated input values from a group of rows separated by a specified delimiter.

Usage:

Note: If there are instances of delimiter_text within your measure, you cannot include a limit_num.

{{ dbt.listagg(measure="column_to_agg", delimiter_text="','", order_by_clause="order by order_by_column", limit_num=10) }}

Sample Output (PostgreSQL):

array_to_string(
        (array_agg(
            column_to_agg
            order by order_by_column
        ))[1:10],
        ','
        )

Cast functions​

cast​

Availability: dbt v1.8 or higher. For more information, select the version from the documentation navigation menu.

cast_bool_to_text​

Args:

This macro casts a boolean value to a string.

Usage:

{{ dbt.cast_bool_to_text("boolean_column_name") }}
{{ dbt.cast_bool_to_text("false") }}
{{ dbt.cast_bool_to_text("true") }}
{{ dbt.cast_bool_to_text("0 = 1") }}
{{ dbt.cast_bool_to_text("1 = 1") }}
{{ dbt.cast_bool_to_text("null") }}

Sample Output (PostgreSQL):

    cast(boolean_column_name as 
    varchar
)

    cast(false as 
    varchar
)

    cast(true as 
    varchar
)

    cast(0 = 1 as 
    varchar
)

    cast(1 = 1 as 
    varchar
)

    cast(null as 
    varchar
)

safe_cast​

Args:

For databases that support it, this macro will return NULL when the cast fails (instead of raising an error).

Usage:

{{ dbt.safe_cast("column_1", api.Column.translate_type("string")) }}
{{ dbt.safe_cast("column_2", api.Column.translate_type("integer")) }}
{{ dbt.safe_cast("'2016-03-09'", api.Column.translate_type("date")) }}

Sample Output (PostgreSQL):

    cast(column_1 as TEXT)
    cast(column_2 as INT)
    cast('2016-03-09' as date)

Date and time functions​

date​

Availability: dbt v1.8 or later. For more information, select the version from the documentation navigation menu.

dateadd​

Args:

  • datepart: date or time part.
  • interval: integer count of the datepart to add (can be positive or negative)
  • from_date_or_timestamp: date/time expression.

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

Usage:

{{ dbt.dateadd(datepart="day", interval=1, from_date_or_timestamp="'2016-03-09'") }}
{{ dbt.dateadd(datepart="month", interval=-2, from_date_or_timestamp="'2016-03-09'") }}

Sample Output (PostgreSQL):

    '2016-03-09' + ((interval '10 day') * (1))
    '2016-03-09' + ((interval '10 month') * (-2))

datediff​

Args:

This macro calculates the difference between two dates.

Usage:

{{ dbt.datediff("column_1", "column_2", "day") }}
{{ dbt.datediff("column", "'2016-03-09'", "month") }}
{{ dbt.datediff("'2016-03-09'", "column", "year") }}

Sample Output (PostgreSQL):

        ((column_2)::date - (column_1)::date)

        ((date_part('year', ('2016-03-09')::date) - date_part('year', (column)::date))
     * 12 + date_part('month', ('2016-03-09')::date) - date_part('month', (column)::date))

        (date_part('year', (column)::date) - date_part('year', ('2016-03-09')::date))

date_trunc​

Args:

This macro truncates / rounds a timestamp to the first instant for the given date or time part.

Usage:

{{ dbt.date_trunc("day", "updated_at") }}
{{ dbt.date_trunc("month", "updated_at") }}
{{ dbt.date_trunc("year", "'2016-03-09'") }}

Sample Output (PostgreSQL):

date_trunc('day', updated_at)
date_trunc('month', updated_at)
date_trunc('year', '2016-03-09')

last_day​

Args:

This macro gets the last day for a given date and datepart.

Usage:

  • The datepart argument is database-specific.
  • This macro currently only supports dateparts of month and quarter.
{{ dbt.last_day("created_at", "month") }}
{{ dbt.last_day("'2016-03-09'", "year") }}

Sample Output (PostgreSQL):

cast(
    date_trunc('month', created_at) + ((interval '10 month') * (1))
 + ((interval '10 day') * (-1))
        as date)

cast(
    date_trunc('year', '2016-03-09') + ((interval '10 year') * (1))
 + ((interval '10 day') * (-1))
        as date)

Date and time parts​

Often supported date and time parts (case insensitive):

  • year
  • quarter
  • month
  • week
  • day
  • hour
  • minute
  • second
  • millisecond
  • microsecond
  • nanosecond

This listing is not meant to be exhaustive, and some of these date and time parts may not be supported for particular adapters. Some macros may not support all date and time parts. Some adapters may support more or less precision.

SQL expressions​

A SQL expression may take forms like the following:

  • function
  • column name
  • date literal
  • string literal
  • <other data type> literal (number, etc)
  • NULL

Example: Suppose there is an orders table with a column named order_date. The following shows 3 different types of expressions:

select
    date_trunc(month, order_date) as expression_function,
    order_date as expression_column_name,
    '2016-03-09' as expression_date_literal,
    'Pennsylvania' as expression_string_literal,
    3 as expression_number_literal,
    NULL as expression_null,
from orders

Note that the string literal example includes single quotes. (Note: the string literal character may vary per database. For this example, we suppose a single quote.) To refer to a SQL string literal in Jinja, surrounding double quotes are required.

So within Jinja, the string values would be:

  • "date_trunc(month, order_date)"
  • "order_date"
  • "'2016-03-09'"
  • "'Pennsylvania'"
  • "NULL"
0
Edit this page