# Public holidays

*Give sales patterns a calendar context.*

Public holidays for every country the `holidays` library models, with the subdivisions it knows (US states, Canadian provinces, German Länder, Australian states, ...), for the last ten years and the next two. One row per holiday and day; when a country and one of its subdivisions both observe a day, both rows exist. Names are in English where the library has them. The library counts every Sunday as a Swedish holiday ("Sunday"); those rows are left out, so a count of holidays per country compares like with like (a holiday that falls on a Sunday stays).

## At a glance

| | |
|---|---|
| Table | `pubdata.calendars.public_holidays` |
| Category | Calendars · Country, Date |
| Rows | 161,039 |
| Size | 0.3 MB in 1 data file(s) |
| Iceberg format | v2 |
| Last refreshed | 2026-10-10T11:26:27Z |
| Source version | holidays 0.105 |
| Refresh cadence | monthly (replace) |
| Coverage | 250 countries and 620+ subdivisions; ten years back, two years ahead (rolling) |

## Origin

- **Source:** [holidays (Vacanza)](https://github.com/vacanza/holidays)
- **Licence:** MIT
- **Attribution:** Public holiday rules from the holidays Python library, © Vacanza contributors, MIT licence.
- **Note:** Rules, not observations; a government can move a holiday after the library's release.
- **Recipe:** `pubdata/recipes/public_holidays.py` in the LakehouseBox repository; registry entry `pubdata/registry/public_holidays.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `country_code` | string | ISO 3166-1 alpha-2, upper case (joins reference.countries) |
| `subdivision` | string | ISO 3166-2 subdivision code without the country prefix (US-CA -> CA); null for the national calendar |
| `date` | date | the day of the holiday |
| `year` | int | the calendar year, for cheap filtering |
| `name` | string | the holiday's name in English |
| `observed` | boolean | true when the row is a substitute day ("(observed)") |

**Join keys:** `country_code`, `date`.

## For agents

Filter `subdivision IS NULL` for the national calendar; a subdivision row means "also (or only) here".
The same day can carry several rows (two holidays, or national + subdivision). De-duplicate on (country_code, date)
with `ANY_VALUE(name)` when you only need a flag. Years outside the rolling window are absent, not empty.

## Example SQL (DuckDB)

```sql
-- national holidays of Spain and the United States in 2026
SELECT country_code, date, name
FROM pubdata.calendars.public_holidays
WHERE year = 2026 AND subdivision IS NULL AND country_code IN ('ES', 'US')
ORDER BY date;
-- your daily sales with a holiday flag
SELECT s.day, s.revenue, h.name AS holiday
FROM my.sales_daily AS s
LEFT JOIN pubdata.calendars.public_holidays AS h
  ON h.date = s.day AND h.country_code = s.country_code AND h.subdivision IS NULL;
```

## How to read it

Without an account, over HTTPS (the bucket is public):

```sql
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
-- anonymous: the bucket is public, requests go unsigned, through the public-data cache
CREATE OR REPLACE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
CREATE OR REPLACE VIEW public_holidays AS SELECT * FROM iceberg_scan('s3://commons--pubdata/calendars/public_holidays');
SELECT * FROM public_holidays LIMIT 10;
```

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.calendars.public_holidays LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/calendars/public_holidays/`. This card: `https://lakehousebox.com/pubdata/cards/public_holidays.md`; the page: https://lakehousebox.com/public-data/public_holidays. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.