01 · The challenge
Off by one week is still wrong.
Operational source dates and week-ending conventions can produce off-by-one reporting windows, incomplete periods, and repeated store-week records.
02 · What I did
What I owned and built.
- Defined Sunday-through-Saturday reporting periods independently of SQL Server
DATEFIRSTsettings. - Designed the weekly extract around the previous fully completed reporting week.
- Aggregated source activity to a single store-week output grain instead of exporting individual transaction rows.
- Extended the extraction logic to support a six-month historical backfill.
03 · Key decisions
The judgment behind the build.
Why not rely on DATEFIRST?
It’s a session setting, and it can differ between servers and connections. Computing week boundaries explicitly makes the result the same everywhere.
Why only completed weeks?
Publishing a partial week invites people to compare a half-week to a full one. Waiting for the week to close keeps every number comparable.
Why store-week grain?
Downstream analysis asks store-week questions. Delivering that grain removes repeated rows and saves every consumer from re-aggregating.
04 · Impact
What changed because of the work.
- Consistent date boundaries across routine deliveries and historical loads.
- A stable store-week grain for downstream service and retail analysis.
05 · What’s next
Where it goes from here.
Confirm source refresh latency and add freshness checks before scheduling final publication.
This case study is a sanitized reconstruction. Organization names, proprietary code, data, and internal schemas are intentionally left out.