Calendars · Public data

Public holidays

Give sales patterns a calendar context.

holidays (Vacanza) · MIT · commons_pubdata.calendars.public_holidays

Read Markdown ↗ Copy page

• 161,039rows
• 250 countries and 620+ subdivisionscoverage
• Monthlyrefresh
• 10 Oct 2026last refreshed
• 0.3 MBtable size

Chart Sample rows

Query it with DuckDB.

With an account Without an account

BashCopy

lhbox duckdb --catalog commons/pubdata

SQL · National holidays of Spain and the United States in 2026Copy

SELECT country_code, date, name
FROM commons_pubdata.calendars.public_holidays
WHERE year = 2026 AND subdivision IS NULL AND country_code IN ('ES', 'US')
ORDER BY date;

Read it where you work.

Public, read-only data. With an account, lhbox duckdb attaches the catalog as commons_pubdata; an agent with the LakehouseBox connector opens it with open_public_catalog("commons/pubdata"). Without one, DuckDB reads the public files directly.

Example queries

• National holidays of Spain and the United States in 2026

  SELECT country_code, date, name
  FROM commons_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 commons_pubdata.calendars.public_holidays AS h
    ON h.date = s.day AND h.country_code = s.country_code AND h.subdivision IS NULL;

Know your columns.

Column	Type	Meaning
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.

Before you query.

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

• Combine with Airports →Joins on country_code.
• Combine with Countries →Joins on country_code.
• Combine with Countries & regions →Joins on country_code.

Source & licence.

Source

holidays (Vacanza)

Licence

MIT

Attribution

Public holiday rules from the holidays Python library, © Vacanza contributors, MIT licence.

Coverage

250 countries and 620+ subdivisions; ten years back, two years ahead (rolling)

Refresh

monthly, the whole table replaced; source version holidays 0.105

Table

commons/pubdata · calendars.public_holidays · Iceberg v2 · browse the catalog

Recipe

pubdata/recipes/public_holidays.py, registry entry pubdata/registry/public_holidays.yaml

Source: holidays (Vacanza) · MIT · Snapshot shown: 10 Oct 2026HTML for people. Markdown for agents.

# 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.