dbt-variant-utils

Various dbt utilities for working with semi-structured variant data in Snowflake (including objects and arrays).

Macros

as_primitive (source)

Converts a variant to a primitive type using Snowflake's built-in typeof function. Returns null if the value cannot be cast to a primitive type.

Usage:

select
    {{ dbt_variant_utils.as_primitive(ref('table'), 'column') }} as primitive_column
from {{ ref('table') }}

object_pivot (source)

Pivots a column of object types into a table with each column representing a key and each row representing a value. If a key is missing from the input, its value will be null in the output table.

with pivot_table as (
    {{ dbt_variant_utils.object_pivot(ref('table'), 'column') }}
)

select * from pivot_table

Configuration:

1. primitive – Returns primitive types if true else returns variants. Defaults to true.
2. include_columns – Additional columns to include from the source table, useful for including primary keys. Defaults to [].
3. exclude_keys – Keys to exclude while flattening the object. Defaults to ['null'].