A Practical Guide to Generating a Time Spline (dim_dates) in dbt

From Seed to Macro

In an earlier post about refactoring our own dbt repo, we mentioned a dim_dates table was added for building the semantic layer. My colleague already covered why we moved from a CSV seed to a macro. This post picks up from there and walks through how we did it, so your team can apply the same approach.

If you’re still using a dim_dates.csv seed file, this one’s for you.

The Problem: Seeds Are Loaded Every Time

Our original setup was simple, e.g. a CSV seed. We liked that as it is version-controlled and easy to modify. But in practice, this seed with 55152 rows added ~3 minutes to every dbt build.

 14:25:43  1 of 2 OK loaded seed file PR_103.dim_dates .................................... [INSERT 55152 in 165.21s]

This slowed us down reviewing PRs since we build for every PR and compare with the production environment using Recce to ensure it won’t break the production environment.

Plus, 55152 rows is too large as a seed and we cannot skip unchanged seeds with dbt selector.

We needed something better.

The Principle: Version-Control the Logic, Not the Data

The dim_dates table is basically static. We don’t need to recalculate and reload it on every build. But we still want to version control the table in the dbt project, which means keeping the logic and generate the table only when needed.

The Solution: Use a dbt Macro + run-operation

Here’s what we did:

1. Convert Your SQL into a Macro

Here is our macro to generate the dim_dates table in Snowflake.

{% macro generate_dim_dates(schema_name=target.schema, table_name='dim_dates') %}

    {% set create_table_query %}
    CREATE OR REPLACE TABLE {{ schema_name }}.{{ table_name }} AS
    WITH date_spine AS (
        SELECT
            DATEADD(day, seq4(), '1900-01-01') AS date_at
        FROM TABLE(GENERATOR(ROWCOUNT => 55152))
        WHERE date_at <= '2050-12-31'
    )

    SELECT
        date_at::date as date,
        DATE_TRUNC('week', date) as week,
        DATE_TRUNC('month', date) as month,
        DATE_TRUNC('quarter', date) as quarter,
        DATE_TRUNC('year', date) as year,
        date::string as date_string,
        date_at,
        DATE_TRUNC('week', date_at) as week_at,
        DATE_TRUNC('month', date_at) as month_at,
        DATE_TRUNC('quarter', date_at) as quarter_at,
        DATE_TRUNC('year', date_at) as year_at,
        DAYOFMONTH(date) AS day_of_month,
        DAYOFWEEK(date) AS day_of_week,
        WEEKOFYEAR(date) AS week_of_year,
        MONTH(date) AS month_of_year,
        QUARTER(date) AS quarter_of_year,
        DAYNAME(date) AS day_name,
        MONTHNAME(date) AS month_name,
        IFF(day_of_week in (0, 6), True, False) as is_weekend
    FROM date_spine
    ORDER BY date desc
    {% endset %}

    {% do run_query(create_table_query) %}

    {% set result_message %}
    Table {{ schema_name }}.{{ table_name }} has been successfully created.
    {% endset %}

    {{ log(result_message, info=True) }}

    {{ return(result_message) }}

{% endmacro %}

2. Create the Table Manually When Needed

To generate or update the dim_dates table, run the this.

dbt run-operation generate_dim_dates --args '{schema_name: <your_schema>}'

This executes the macro and materializes the table once in your warehouse.

You can also parameterize start_date and end_date if you want tighter control or shorter test versions.

Final Thoughts

dim_dates is foundational, but rebuilding it on every run is a waste of time and compute. This simple shift—from seed to macro—saves minutes on every build and gives you full control over how the table evolves.

If your team is using dbt at any meaningful scale, this change pays for itself almost instantly.

Want to implement it yourself? Just grab your existing seed SQL and wrap it in a macro like the one above.

Happy building! 🚀