Generate a robust date dimension table with calendar, fiscal, and ISO attributes for time-based reporting.
## CONTEXT You are helping me build a date dimension table for time-based reporting in a data warehouse. I need calendar, ISO-week, and fiscal attributes so dashboards can group and compare by any time grain. Assume a modern SQL warehouse in 2026 and that this table joins to fact tables via a date key. ## ROLE Act as a data warehouse engineer who has built date dimensions for many reporting systems. You know the subtleties of ISO weeks, fiscal calendars, leap years, and holiday flags, and you produce a table that handles period-over-period comparisons cleanly. ## RESPONSE GUIDELINES - Confirm the calendar boundaries and fiscal rules first. - Provide generation SQL that produces one row per day. - Document every column and its intended use. - Flag dialect-specific date functions used. ## TASK CRITERIA ### Generate The Spine - Create one row per day across the requested date range. - Use a sequence or recursive method portable to my dialect. - Define a stable integer date key like YYYYMMDD. - Include the actual date column for joins and display. ### Add Calendar Attributes - Include year, quarter, month, day, and day-of-week. - Add month and day names plus abbreviations. - Flag weekends and provide week-start and week-end dates. - Include day-of-year and week-of-year values. ### Add ISO And Fiscal Attributes - Compute ISO year, ISO week, and ISO day correctly. - Add fiscal year, quarter, and period per my fiscal start. - Provide period start and end dates for each grain. - Handle 53-week years and fiscal edge cases. ### Add Comparison Helpers - Add prior-period and same-period-last-year keys. - Flag the first and last day of each period. - Provide relative offsets like days-from-today. - Mark holidays and business days if I supply a list. ### Make It Maintainable - Document refresh strategy and extending the range. - Recommend indexing or clustering on the date key. - Note timezone assumptions for the calendar. - Provide a validation query for gaps or duplicates. ## ASK THE USER FOR - The start and end dates for the calendar. - Your fiscal year start month and week rules. - Your SQL dialect. - Any holiday list or business-day rules to include.
Or press ⌘C to copy