Date Dimension with Global Holiday Calendar

[nurita]

Hi,

I need data about all holidays and days off  in world.

I'd like to get example script for all tables

[andy4]

You will need to first create a column with dates. Here is Bigquery sql to do so - if you are using a different SQL dialect then you will need to find a different query.

SELECT
  date
from unnest(generate_date_array('2020-01-01', '2021-12-31', interval 1 day)) as date
order by date;    

Next, create a dimension (or another database column) that runs the calendar date through a case statement to determine whether it is a holiday.

dimension_group: calendar {
  type: time
  timeframes: [raw, date, day_of_month, month_num]
  sql: ${TABLE}.date
}

dimension: holiday {
  type: string
  sql: case
    when ${calendar_month_num} = 12 and ${calendar_day_of_month} = 25 then 'Christmas'
    when ...
    else null end ;;
}
dimension: is_holiday {
  type: yesno
  sql: ${holiday} is not null ;;
}

As for a list of holidays/dates, i would google it! And as for holidays which change each year, consider things like week_of_month and day_of_week_index. More info about timeframes here.