Need to create date dimension table in snowflake

Question

I have create a date dimension table with columns : D_DATEKEY, D_DATE, D_DAYOFWEEK, D_MONTH, D_YEAR, D_YEARMONTHNUM, D_YEARMONTH ,D_DAYNUMINWEEK, D_DAYNUMINMONTH, D_DAYNUMINYEAR, D_MONTHNUMINYEAR ,D_WEEKNUMINYEAR, D_SELLINGSEASON, D_LASTDAYINWEEKFL, D_LASTDAYINMONTHFL ,D_HOLIDAYFL , D_WEEKDAYFL.

I need to insert data in this from year 1992 to 1998 with holidays and sellingseasons.

`    SELECT 
 CAST(TO_VARCHAR(DATE_TRUNC('DAY', d), 'YYYYMMDD') AS INTEGER) AS D_DATEKEY,
 DATE_TRUNC('DAY', d) AS D_DATE,
 TO_VARCHAR(DATE_TRUNC('DAY', d), 'DAY') AS D_DAYOFWEEK,
 TO_VARCHAR(DATE_TRUNC('DAY', d), 'MONTH') AS D_MONTH,
 DATE_PART('YEAR', d) AS D_YEAR,
 DATE_PART('YEAR', d) * 100 + DATE_PART('MONTH', d) AS D_YEARMONTHNUM,
 TO_VARCHAR(DATE_TRUNC('DAY', d), 'MON YYYY') AS D_YEARMONTH,
 DATE_PART('ISODOW', d) AS D_DAYNUMINWEEK,
 DATE_PART('DAY', d) AS D_DAYNUMINMONTH,
 DATE_PART('DOY', d) AS D_DAYNUMINYEAR,
 DATE_PART('MONTH', d) AS D_MONTHNUMINYEAR,
 DATE_PART('WEEK', d) AS D_WEEKNUMINYEAR,
 NULL AS D_SELLINGSEASON,
 CASE WHEN DATE_TRUNC('DAY', d) = DATE_TRUNC('WEEK', d, 1) + INTERVAL '6 DAY' THEN 1 ELSE 0 END AS D_LASTDAYINWEEKFL,
 CASE WHEN DATE_TRUNC('DAY', d) = DATE_TRUNC('MONTH', d) + INTERVAL '1 MONTH - 1 DAY' THEN 1 ELSE 0 END AS D_LASTDAYINMONTHFL,
 CASE WHEN MONTH(d) = 1 AND DAY(d) = 1 THEN 1
    WHEN MONTH(d) = 7 AND DAY(d) = 4 THEN 1
    ELSE 0 END AS D_HOLIDAYFL,
 CASE WHEN DATE_PART('ISODOW', d) IN (6,7) THEN 0 ELSE 1 END AS D_WEEKDAYFL;  `

I tried this query but this doesn't work.

When i run this query i'm getting error " SQL compilation error: syntax error line 1 at position 8 unexpected '-'. " — Lolita, Feb 21 '23 at 09:20

score 1 · Answer 1 · answered Feb 21 '23 at 08:36

Well it seems to work so far... (I mean D_MONTH was wrong)

with data as (
    select 
        row_number() over (order by seq8())-1 as rn
        ,dateadd('day', rn, '1992-01-01'::date) as d
    from table(generator(rowcount=>20))
)

SELECT 
    *,
    CAST(TO_VARCHAR(DATE_TRUNC('DAY', d), 'YYYYMMDD') AS INTEGER) AS D_DATEKEY
    ,DATE_TRUNC('DAY', d) AS D_DATE
    ,TO_VARCHAR(DATE_TRUNC('DAY', d), 'DD') AS D_DAYOFWEEK
    ,TO_VARCHAR(DATE_TRUNC('DAY', d), 'MON') AS D_MONTH
    ,DATE_PART('YEAR', d) AS D_YEAR
    ,DATE_PART('YEAR', d) * 100 + DATE_PART('MONTH', d) AS D_YEARMONTHNUM
    ,TO_VARCHAR(DATE_TRUNC('DAY', d), 'MON YYYY') AS D_YEARMONTH
 from data;

I would skip the DATE_TRUNC('DAY', d) inside the cast to text, as those are not presenting the hours parts, so the truncation is pointless..

Need to create date dimension table in snowflake

1 Answers1