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

Comparison 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() }}
Report incorrect code

Sample Output (PostgreSQL):

bigint
Report incorrect code

type_boolean​

Args:

  • None

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

Usage:

{{ dbt.type_boolean() }}
Report incorrect code

Sample Output (PostgreSQL):

BOOLEAN
Report incorrect code

type_float​

Args:

  • None

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

Usage:

{{ dbt.type_float() }}
Report incorrect code

Sample Output (PostgreSQL):

FLOAT
Report incorrect code

type_int​

Args:

  • None

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

Usage:

{{ dbt.type_int() }}
Report incorrect code

Sample Output (PostgreSQL):

INT
Report incorrect code

type_numeric​

Args:

  • None

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

Usage:

{{ dbt.type_numeric() }}
Report incorrect code

Sample Output (PostgreSQL):

numeric(28,6)
Report incorrect code

type_string​

Args:

  • None

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

Usage:

{{ dbt.type_string() }}
Report incorrect code

Sample Output (PostgreSQL):

TEXT
Report incorrect code

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() }}
Report incorrect code

Sample Output (PostgreSQL):

TIMESTAMP
Report incorrect code

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() }}
Report incorrect code

Sample output (PostgreSQL)

now()
Report incorrect code

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() }}
Report incorrect code

Sample Output (PostgreSQL):

except
Report incorrect code

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() }}
Report incorrect code

Sample Output (PostgreSQL):

intersect
Report incorrect code

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'") }}
Report incorrect code

Sample Output (PostgreSQL):

array_append(array_column, element_column)
array_append(array_column, 5)
array_append(array_column, 'blue')
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

array_cat(array_column_1, array_column_2)
Report incorrect code

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'"]) }}
Report incorrect code

Sample Output (PostgreSQL):

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

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"]) }}
Report incorrect code

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
Report incorrect code

hash​

Args:

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

Usage:

{{ dbt.hash("column") }}
{{ dbt.hash("'Pennsylvania'") }}
Report incorrect code

Sample Output (PostgreSQL):

md5(cast(column as 
    varchar
))
md5(cast('Pennsylvania' as 
    varchar
))
Report incorrect code

length​

Args:

This macro calculates the number of characters in a string.

Usage:

{{ dbt.length("column") }}
Report incorrect code

Sample Output (PostgreSQL):

    length(
        column
    )
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

    position(
        substring_column in text_column
    )

    position(
        '-' in text_column
    )
Report incorrect code

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", "'-'", "'_'") }}
Report incorrect code

Sample Output (PostgreSQL):

    replace(
        string_text_column,
        old_chars_column,
        new_chars_column
    )

    replace(
        string_text_column,
        '-',
        '_'
    )
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

    right(
        string_text_column,
        length_column
    )

    right(
        string_text_column,
        3
    )
Report incorrect code

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) }}
Report incorrect code

Sample Output (PostgreSQL):

    split_part(
        column_to_split,
        delimiter_column,
        1
        )

    split_part(
        '1|2|3',
        '|',
        1
        )
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

they''re
ain''t ain''t a word
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

select 'Pennsylvania'
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

any(column_name)
Report incorrect code

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") }}
Report incorrect code

Sample Output (PostgreSQL):

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

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) }}
Report incorrect code

Sample Output (PostgreSQL):

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

Cast functions​

cast​

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

Args:

This macro casts a value to the specified data type. Unlike safe_cast, this macro will raise an error when the cast fails.

Usage:

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

Sample Output (PostgreSQL):

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

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") }}
Report incorrect code

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
)
Report incorrect code

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")) }}
Report incorrect code

Sample Output (PostgreSQL):

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

Comparison functions​

Comparison functions are macros that compare two SQL expressions and return a boolean SQL expression (for example, TRUE, FALSE, or UNKNOWN).

equals​

Args:

This macro compares two expressions for equality.

By default, the equals() macro follows SQL's three-valued logic (3VL), so NULL = NULL evaluates to UNKNOWN rather than TRUE.

When the enable_truthy_nulls_equals_macro flag is enabled, equals() behaves like the IS NOT DISTINCT FROM SQL operator and treats two NULL values as the same.

Usage:

{{ dbt.equals("column_a", "column_b") }}
{{ dbt.equals("id", "previous_id") }}
Report incorrect code

Sample output (PostgreSQL with enable_truthy_nulls_equals_macro enabled):

(column_a IS NOT DISTINCT FROM column_b)
(id IS NOT DISTINCT FROM previous_id)
Report incorrect code

Date and time functions​

date​

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

Args:

  • year: an integer
  • month: an integer
  • day: an integer

This macro converts the year, month, and day into an SQL DATE type.

Usage:

{{ dbt.date(2023, 10, 4) }}
Report incorrect code

Sample output (PostgreSQL):

to_date('2023-10-04', 'YYYY-MM-DD')
Report incorrect code

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'") }}
Report incorrect code

Sample Output (PostgreSQL):

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

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") }}
Report incorrect code

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))
Report incorrect code

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'") }}
Report incorrect code

Sample Output (PostgreSQL):

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

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") }}
Report incorrect code

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)
Report incorrect code

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
Report incorrect code

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"

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading