Syntax error unexpected 'with' while using dbt_utils.date_spine

Question

I am trying to use the dbt_utils.date_spine macro :

select
  {{ dbt_utils.date_spine(datepart="day", start_date="cast('2019-01-01' as date)", end_date="cast('2020-01-01' as date)") }} as purchase_date
from table(generator(rowcount=>10))

And get the following error :

Database Error in model purchase (models/data_generation/purchase.sql)
  001003 (42000): SQL compilation error:
  syntax error line 21 at position 0 unexpected 'with'.
  syntax error line 29 at position 5 unexpected ','.
  compiled SQL at target........purchase.sql

Has anyone seen this before?

Answer 1

I'm afraid that's not the right way to use/call this macro.

One of the most common options is to materialize a table somewhere and then have other models referencing that table. You can think about that as a Calendar Table or if you want to go further down you can build your own Date Dimension.

For example, let's say you have a calendar_table model defined as:

{{
  config(
    materialized = 'table',
    )
}}

{{ dbt_utils.date_spine(
    datepart="day",
    start_date="to_date('01/01/2020', 'mm/dd/yyyy')",
    end_date="to_date('01/01/2027', 'mm/dd/yyyy')"
   )
}}

Once built the model in the data warehouse, then you can reference it in other models, like:

-- another_model.sql

select * from {{ ref('calendar_table') }}

You can also materialize the calendar_table model as ephemeral in case you don't want to build it in the DW.

But in case you don't want to have a separate model for that, then you can use with a CTE, for example:

with date_spine as (
  {{- dbt_utils.date_spine(
    datepart="day",
    start_date="to_date('01/01/2020', 'mm/dd/yyyy')",
    end_date="to_date('01/01/2027', 'mm/dd/yyyy')"
    )
  -}}
)
, other_cte as (
  ...
)
...

Note that the code generated from the macro is not friendly to be used in a view materialization. You might prefer to use table for that.

Here are some related posts from dbt discourse:

https://discourse.getdbt.com/t/date-dimensions/735

https://discourse.getdbt.com/t/building-a-calendar-table-using-dbt/325

Answer 2

Thanks gasscoelho, How do I add the generated date_spine as a new column in a table e.g. as purchase date in purchase table? This is what I tried.

with
purchase_date_spine as (
  {{- dbt_utils.date_spine(
    datepart="day",
    start_date="to_date('01/01/2010', 'mm/dd/yyyy')",
    end_date="to_date('12/31/2020', 'mm/dd/yyyy')"
    )
  -}}
),

purchase as (
select
  {{ var('purchase_id_start') }} + row_number() over(order by random()) as purchase_id, --primary_key for the table
  -- row_number| https://docs.snowflake.com/en/sql-reference/functions/row_number.html#row-number
  uniform({{ var('account_id_start') }}, {{ var('account_id_start') }} + {{ var('account_rows') }}, random()) as account_id, --foreign key
  -- uniform | https://docs.snowflake.com/en/sql-reference/functions/uniform.html#uniform
  uniform({{ var('product_id_start') }}, {{ var('product_id_start') }} + {{ var('product_rows') }}, random()) as product_id, --foreign key
  {{ ref('purchase_date_spine') }} as purchase_date
from table(generator(rowcount=>{{ var('purchase_rows') }}))
)

select * from purchase```

Answer 3

I was able to solve this by joining the date_spine with the purchase table. Posting the solution below for completeness.

purchase_date_spine.sql

{{- dbt_utils.date_spine(
    datepart="day",
    start_date="to_date('01/01/2010', 'mm/dd/yyyy')",
    end_date="to_date('12/31/2020', 'mm/dd/yyyy')"
    )
-}}

purchase.sql

{{- 
with
purchase_date_stub as (
select
    date_day as purchase_date,
    row_number() over(order by date_day) as row_id
from {{ ref('purchase_date_spine') }} sample({{ var('purchase_rows') }} rows)
),

purchase_stub as (
select
  {{ var('purchase_id_start') }} + row_number() over(order by random()) as purchase_id, --primary_key for the table
  -- row_number| https://docs.snowflake.com/en/sql-reference/functions/row_number.html#row-number
  uniform({{ var('account_id_start') }}, {{ var('account_id_start') }} + {{ var('account_rows') }}, random()) as account_id, --foreign key
  -- uniform | https://docs.snowflake.com/en/sql-reference/functions/uniform.html#uniform
  uniform({{ var('product_id_start') }}, {{ var('product_id_start') }} + {{ var('product_rows') }}, random()) as product_id, --foreign key
  row_number() over(order by random()) as row_id
from table(generator(rowcount=>{{ var('purchase_rows') }}))
)

select
    purchase_stub.purchase_id,
    purchase_stub.account_id,
    purchase_stub.product_id,
    purchase_date_stub.purchase_date
from purchase_stub
join purchase_date_stub on purchase_stub.row_id = purchase_date_stub.row_id
-}}