top of page
Search
  • Writer's pictureTim Burns

Creating a Calendar Dimension

Calendars and dates drive our lives; a calendar dimension is essential to most data warehouses.


Pope Gregory introduced the Gregorian Calendar in XIII in 1582. It took over the world, and we take it for granted.

create or replace table WAREHOUSE.DIM_CALENDAR as
with years as (
    select dateadd(day, seq4(), '2010-01-01')::date date
    from table (generator(rowcount => (14 * 365+ 3)))
)
select date
     , date_part(year, date)       year
     , date_part(month, date)      month
     , monthname(date)             month_name
     , date_part(dayofmonth, date) day_of_mon
     , date_part(dayofweek, date)  day_of_week
     , date_part(weekofyear, date) week_of_year
     , date_part(dayofyear, date)  day_of_year
     , date_part(weekday, date)    weekday

     , dayname(date)               dayname
from years;


select min(date), max(date)
from WAREHOUSE.DIM_CALENDAR;

+----------+----------+
|MIN(DATE) |MAX(DATE) |
+----------+----------+
|2010-01-01|2023-12-31|
+----------+----------+

Retailers generally use a 4-4-5 calendar to track inventory. That means four weeks, four weeks, then five weeks or 13 weeks makes a quarter. The fiscal year can then be digested into equal quarters and Owlstart and end on any date.




13 views0 comments

Recent Posts

See All

Carto, Snowflake, and Data Management

A basic principle of data management: Don't move data unless you have to. Moving data is expensive and error-prone. Data Egress Cost: How To Take Back Control And Reduce Egress Charges Archiving to S

Comments


bottom of page