Date Dimension with Global Holiday Calendar - Direct Pay

ABOUT<br/>======

A date dimension is an essential table in a data model that allows to analyze performance more effectively across different time periods. It should be included in every dimensional model that contains a date or requires date intelligence as part of the analysis.

A key problem when working with dates is the impact of holidays and weekends. To manage this complexity, a holiday calendar is used. A holiday calendar implementation keeps track of which dates are holidays and which are weekends.

Another key problem when working with dates is calculating one date relative to another taking into account holidays, weekends and other conventions.

This holiday calendar contains the data for 86 countries.

CORE TABLES<br/>===========

The data model includes the following list of tables

DIM_DATE<br/>COUNTRY_CODE<br/>SUBDIVISION_CODE<br/>HOLIDAY_CALENDAR_COUNTRY<br/>HOLIDAY_CALENDAR_SUBDIVISION

The date dim table contains the date range from January 01, 1900 to December 31, 2100.

Country code has all the available countries and its ISO 3166 country code. The code is a two letter code which defines the country name. The other field has the actual country name.

Subdivisions belong to countries. Hence each subdivision has a mapping country as shown in the above data model.

This calendar table contains all the holidays of the particular country. This table has 40 years (20 years previous data and 20 years future data) holiday data for the countries.

This calendar table contains all the holidays of the particular subdivision. This table has 40 years (20 years previous data and 20 years future data) holiday data for the subdivisions.

DOCUMENTATION<br/>===============

We have put together a comprehensive documentation which includes a description of all of the tables and columns in the data set and sample queries.

https://sonra.io/snowflake/date-dimension-with-global-h-oliday-calendar/

搜集汇总

背景与挑战

背景概述

该数据集是一个包含全球假日日历的日期维度表，涵盖1900年至2100年的日期范围及86个国家的假日信息，旨在解决数据模型中因假日和周末导致的日期分析复杂性问题。它提供了核心数据表（如日期维度、国家代码和假日日历），支持跨时间段的有效性能分析和相对日期计算。