- 3calendars configuredretail 4-5-4, retail 4-4-5, Gregorian
- 504calendar configurations testedevery year 2000–2039
- 3error-level checks on real dataevery run, before any report
- 10.4%average monthly error of the shortcutwhat this removes
One configuration
Every calendar the business reports in is declared once, in config/calendars.toml, and changing it is a reviewed change because it alters reported periods. Each entry states the pattern, the day weeks start on, and how the year ends. Nothing about the calendar lives inside a report.
One configuration file declares three calendars: retail_454, a 4-5-4 calendar with Sunday weeks whose year ends on the Saturday nearest 31 January; retail_445, a 4-4-5 calendar with Monday weeks whose year ends on the last Sunday of December; and gregorian, with ISO Monday weeks.
From dates to every calendar
The generator writes one row per date per calendar. Weeks roll up into retail periods; calendar months are built from dates alone and never from weeks. Facts carry a date, join to the date spine, and aggregate through whichever calendar the question needs. That is why the shortcut on the overview page cannot happen here: there is no table that assigns a whole week to a month.
Gold calendar tables: calendar_date with one row per date per calendar, calendar_week, calendar_period and gregorian_month. Fact tables join on the date, so weekly, retail-period and calendar-month reports all aggregate the same daily facts.
The 53rd week
A retail year of whole weeks is 364 days, so every few years it needs a 53rd week. Where that week goes changes year-on-year comparisons. Here it is placed explicitly: appended to period 12 and flagged is_leap_week, so a report can include or exclude it on purpose. The calendar tests check that 53-week years recur every five or six years for every configuration.
Twelve retail periods follow the 4-5-4 pattern for 52 weeks. In a 53-week year the extra week is added to period 12, which becomes 5 weeks, and is flagged as the leap week.
Checked on every run
Unit tests prove the logic; these checks prove the data. They run on the real transactions each time the calendar step runs. Each is severity error: a failure stops the pipeline before anything reaches a report, and every result is written to ops.dq_results.
The calendar step generates the calendar tables, then runs three error-severity checks on real data: every transaction date maps to exactly one calendar date, every retail week sits in exactly one period, and totals are conserved by week and by period in every configured calendar. Only then do the tables feed reports.
- GenerateCalendar tablesFrom config/calendars.toml, for every configured calendar.
- Check 1Every transaction date mappedEach date with sales has exactly one calendar row.
- transaction_
dates_ mapped
errorstops the run - transaction_
- Check 2One period per weekNo retail week is split across periods.
- retail_
week_ in_ one_ period
errorstops the run - retail_
- Check 3Totals conservedSumming by week and by period gives the same total as summing by day.
- totals_
conserved_ by_ week_ and_ period
errorstops the run - totals_
- ServeReportsOnly a run that passes every check reaches Gold marts.
504 calendars, 40 years
The three configured calendars are only what this business uses. The engine is tested on every retail calendar it can be configured to produce. For each of the 504, every year from 2000 to 2039 must follow the previous one with no gap or overlap, start on the configured weekday, last 364 or 371 days and end where the year-end rule says, and 53-week years must recur every five or six years. Period lengths must follow the pattern, with the leap week only ever in period 12. That each date maps to exactly one week and period is tested on a representative set of calendars, and checked on the real data every run.
3 period patterns times 7 week-start days times 2 year-end rules times 12 year-end months gives 504 retail calendar configurations, whose year boundaries are tested for every year from 2000 to 2039.