0

I am working on a building a Data Vault Temporal Point in Time (TPIT) table. For this I need to iterate through a list of dates and insert rows for each Date. Here is the SQL for one Date '2020-08-19' . I need to iterate over all dates from year 2000 to today.

What is the best way to implement this using SQL in Snowflake?

I can do this in Python, but I was looking for a SQL only way to do this, so that it can be embedded in an SQL refresh script that runs daily.

Thanks.

INSERT
    INTO
    MEMBERSHIP_TPIT
SELECT
    MEMBERSHIP_HUB.MEMBERSHIP_HASHKEY AS MEMBERSHIP_HASHKEY,
    '2020-08-19' AS SNAPSHOT_DATE,
    MEMBERSHIP_SAT.LOAD_DATE AS LOAD_DATE
FROM
    MEMBERSHIP_HUB INNER MEMBERSHIP_SAT ON
    (MEMBERSHIP_HUB.MEMBERSHIP_HASHKEY = MEMBERSHIP_SAT.MEMBERSHIP_HASHKEY
    AND '2020-08-19' BETWEEN START_DATE AND COALESCE(END_DATE,
    '9999-12-31'));
Saqib Ali
  • 3,953
  • 10
  • 55
  • 100
  • I can imagine different approaches for you to consider. 1) Using a [generator](https://docs.snowflake.com/en/sql-reference/functions/generator.html) to compute the series of dates you need as a CTE, then join them somehow in the main query; 2) what you describe seems a nice use case for incremental materializations in dbt: using dbt for a single task would be exagerated IMO, but as it seems to me you're dealing with some kind of analytics project, might be worth some investigation; 3) producing the SQL code with a script, Python or similar – larsen May 21 '20 at 13:39
  • @larsen , any sample code on how I can use a CTE for this purpose? – Saqib Ali May 22 '20 at 00:11

1 Answers1

-2

You can use below query along with WITH clause and refer DATE_RANGE as a table in your From clause and refer MY_DATE in place of '2020-08-19'.

WITH DATE_RANGE AS (
  SELECT DATEADD(DAY, -1*SEQ4(), CURRENT_DATE()) AS MY_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => (365000) )) where my_date >='01-Jan-2000'
 ) 
SELECT * FROM DATE_RANGE
Srini G
  • 1
  • 1