dbt macro
Timestamps
Parse date strings from virtually any format and language into a consistent Snowflake timestamp.
Goal
Source data contains dates in all possible forms: ISO 8601, European notation, US format, with or without timezone, with month names in German, French or Spanish. The Timestamps macro set automatically converts all these variants to a uniform timestamp_tz or timestamp_ntz in Snowflake.
Instead of writing a separate TRY_TO_TIMESTAMP chain for each source system, you call one macro that handles the detection order for you.
What to expect
The set consists of three macros:
to_timestamp
Converts a date string to a Snowflake timestamp. Automatically tries to find the correct format via a fixed priority order.
fix_months
Normalises month names to 3-letter English abbreviations (e.g. 'janvier' → 'jan', 'März' → 'mar'). Supports 13+ languages including accents.
fix_weekdays
Removes leading day names so a date string like 'Monday, 12 April 2021' can be parsed correctly.
Supported format groups (configurable):
| Group | Examples |
|---|---|
| iso | 2024-04-12T14:30:00Z |
| european | 12-04-2024, 12/04/2024 |
| us | 04/12/2024 |
| compact | 20240412 |
| oracle / mssql / postgresql / mysql / sap | Database-specific formats |
Usage
1. Call in a dbt model
Use to_timestamp directly in a SQL expression:
SELECT
{{ to_timestamp('raw_date_column') }} AS parsed_date
FROM {{ source('source', 'table') }}2. Adjust configuration (_config.sql)
Set which languages, format groups and output type you want to use:
-- _config.sql languages: ['nl', 'de', 'fr', 'en'] format_groups: ['iso', 'european', 'us'] output_type: 'timestamp_tz' two_digit_years: false
3. Testing
Validate the configuration with the built-in test macros:
{{ create_test() }} -- creates test table
{{ do_test() }} -- fills and validates, logs pass/fail