A macro for generating dim_dates table in Snowflake

generate_dim_dates.sql

{% 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 %}