Back to Utils
dbt macro
Uniform Datatypes
Automatically cast all columns of a source table to uniform Snowflake data types, without manually specifying each column.
Goal
Source data often contains a jumble of data types. The uniform_datatypes macro inspects the metadata of a table and automatically generates a SELECT statement where each column is cast to a consistent target type.
Invalid values are handled via TRY_TO_* functions: instead of throwing an error, they are converted to NULL. So a single dirty value no longer breaks your entire staging layer.
Type mapping
| Source type | Target type |
|---|---|
| VARCHAR, CHAR, TEXT, STRING, … | TEXT |
| NUMBER, DECIMAL, FLOAT, INT, … | DECIMAL(digits_before, digits_after) |
| DATE, DATETIME, TIMESTAMP_* | TIMESTAMP_TZ |
| TIME | TIMESTAMP_TZ (anchor date 2000-01-01) |
| BOOLEAN, VARIANT, ARRAY, OBJECT | Unchanged |
Parameters
| Parameter | Default | Description |
|---|---|---|
| relatie | — | Required. Reference to the source table via ref() or source(). |
| voor_komma | 18 | Number of digits before the decimal point for DECIMAL casting. |
| na_komma | 4 | Number of digits after the decimal point for DECIMAL casting. |
| kolommen_uitsluiten | [] | List of column names that are not transformed. |
Usage
1. Call in a dbt model
Use the macro as the full body of a staging model:
{{ uniform_datatypes(ref('my_source_table')) }}2. Adjust precision
Set the desired precision for numeric columns:
{{ uniform_datatypes(
source('raw', 'orders'),
voor_komma = 15,
na_komma = 2
) }}3. Exclude columns
Pass a list of columns you want to leave unchanged:
{{ uniform_datatypes(
ref('my_source_table'),
kolommen_uitsluiten = ['_loaded_at', '_row_hash']
) }}