📋 ref¶
Reference and cross-referencing tables for financial entity identification. Links SEC CIK identifiers to GLEIF LEI codes and OpenFIGI instrument identifiers. Enables cross-system entity resolution for financial data analysis.
16 datasets · 125 columns
gleif_entities · table¶
GLEIF Legal Entity Identifier (LEI) records from the Global LEI Foundation golden copy CSV. Contains entity registration details, jurisdiction, legal form, addresses, and registration authority links. The registration_authority_entity_id field contains the SEC CIK number for entities registered with RA000602 (SEC). Source: GLEIF Golden Copy (goldencopy.gleif.org).
| Column | Type | Null | Description |
|---|---|---|---|
lei |
string | no | Legal Entity Identifier (20-character alphanumeric) |
legal_ |
string | yes | Legal name of the entity |
legal_ |
string | yes | Language of the legal name (ISO 639-1) |
jurisdiction |
string | yes | Jurisdiction of legal formation (ISO 3166-2, e.g., US-DE) |
entity_ |
string | yes | Entity status (ACTIVE, INACTIVE, etc.) |
entity_ |
string | yes | Entity legal form code (ELF code) |
registration_ |
string | yes | Registration authority identifier (e.g., RA000602 for SEC) |
registration_ |
string | yes | Entity ID at the registration authority (CIK for SEC registrants) |
headquarters_ |
string | yes | Country of headquarters (ISO 3166-1 alpha-2) |
headquarters_ |
string | yes | City of headquarters |
registered_ |
string | yes | Country of legal registration (ISO 3166-1 alpha-2) |
registered_ |
string | yes | City of legal registration |
registration_ |
string | yes | Initial LEI registration date (ISO 8601) |
last_ |
string | yes | Last update date of the LEI record (ISO 8601) |
next_ |
string | yes | Next renewal date for the LEI (ISO 8601) |
gleif_cik_mapping · table¶
Bridge table mapping GLEIF LEI codes to SEC CIK numbers. Derived from the GLEIF golden copy CSV filtered where registration_authority_id = 'RA000602' (SEC). Provides fast LEI <-> CIK lookups without scanning the full GLEIF dataset.
| Column | Type | Null | Description |
|---|---|---|---|
lei |
string | no | Legal Entity Identifier (20-character alphanumeric) |
cik |
string | no | SEC Central Index Key (CIK) number |
legal_ |
string | yes | Legal name of the entity |
entity_ |
string | yes | Entity status (ACTIVE, INACTIVE, etc.) |
sec_company_tickers · table¶
SEC EDGAR company ticker mapping. Contains ~10,000 active US exchange-listed companies with their SEC CIK and ticker symbol. Sourced from the SEC EDGAR company_tickers.json file (updated daily). Feeds the ticker dimension for figi_instruments — enables the full chain LEI → CIK → ticker → FIGI within the ref schema with no dependency on other schemas. Source: https://www.sec.gov/files/company_tickers.json
| Column | Type | Null | Description |
|---|---|---|---|
cik |
string | no | SEC Central Index Key (plain numeric string, matches gleif_cik_mapping.cik) |
ticker |
string | no | Exchange ticker symbol |
title |
string | yes | Company name as registered with SEC |
as_ |
date | no | Ingest date this snapshot was materialized (UTC today; group by for history) |
figi_instruments · table¶
Financial instrument identifiers from OpenFIGI. Maps tickers to FIGI codes with exchange, market sector, and security type information. Requires an OpenFIGI API key (set OPENFIGI_API_KEY environment variable). Rate limited to 25 requests/minute with API key, 5 requests/minute without.
| Column | Type | Null | Description |
|---|---|---|---|
figi |
string | no | Financial Instrument Global Identifier (12-character) |
ticker |
string | yes | Trading ticker symbol |
name |
string | yes | Instrument name |
exchange_ |
string | yes | Exchange code (e.g., US, LN, JP) |
market_ |
string | yes | Market sector (Equity, Govt, Corp, etc.) |
security_ |
string | yes | Security type (Common Stock, ADR, ETF, etc.) |
security_ |
string | yes | Secondary security type classification |
security_ |
string | yes | Security description |
composite_ |
string | yes | Composite FIGI (country-level aggregate identifier) |
share_ |
string | yes | Share class FIGI |
countries · table¶
Country identity crosswalk: one row per country carrying the Census 4-digit trade code, ISO 3166-1 alpha-2/alpha-3/numeric, FIPS 10-4, a name-based BEA area key, plus M49 region/subregion/continent and ISO 4217 currency. Lets the Census-coded trade tables (econ.trade_by_state / trade_exports / trade_imports), the ISO-3-coded econ.world_indicators, and the name-keyed BEA tables (econ.ita_data / fdi_*) all join to a single reference. Sources: Census Schedule C country list (live, freshness-gated) + a bundled ISO spine derived from DataHub datasets/country-codes (public domain).
| Column | Type | Null | Description |
|---|---|---|---|
census_ |
string | no | Census 4-digit foreign-trade country code (FK target for the trade tables) |
iso_ |
string | yes | ISO 3166-1 alpha-2 code |
iso_ |
string | yes | ISO 3166-1 alpha-3 code |
iso_ |
string | yes | ISO 3166-1 numeric code |
fips_ |
string | yes | FIPS 10-4 country code |
country_ |
string | yes | Census display name (clean short form) |
official_ |
string | yes | ISO English official name |
bea_ |
string | yes | country_name with any parenthetical stripped; best-effort join key for BEA area_or_country |
currency_ |
string | yes | ISO 4217 currency alphabetic code |
region |
string | yes | M49 region (e.g. Americas, Europe) |
subregion |
string | yes | M49 sub-region (e.g. Northern America) |
intermediate_ |
string | yes | M49 intermediate region (nullable) |
continent |
string | yes | Continent code (e.g. NA, EU, AS) |
is_ |
boolean | yes | True when the Census row is a grouping/area with no single ISO country |
naics · table¶
NAICS 2022 industry classification: one row per 2-to-6-digit code with its title and hierarchy level (2=sector, 3=subsector, 4=industry group, 5=NAICS industry, 6=national industry). Sector codes retain the Census range form (e.g. '31-33' manufacturing). Canonical FK target for naics_code across census/econ/environment. Source: Census 2022 NAICS code list.
| Column | Type | Null | Description |
|---|---|---|---|
naics_ |
string | no | 2-6 digit NAICS code (sectors may be a range, e.g. '31-33') |
naics_ |
string | no | Industry title |
level |
integer | no | Hierarchy depth = number of digits in the base code (2=sector … 6=national industry) |
naics_vintage · table¶
Multi-edition NAICS classification: one row per 2-6 digit code PER vintage (edition year). Use for period-correct industry lookups — join fact.year to ref.naics_vintage_map to pick the edition, then join on (naics_vintage, naics_code). For the single current-edition FK spine use ref.naics. Source: Census per-edition code lists (editions 2017, 2022).
| Column | Type | Null | Description |
|---|---|---|---|
vintage |
integer | no | NAICS edition year (e.g. 2017, 2022) |
naics_ |
string | no | 2-6 digit NAICS code (sectors may be a range, e.g. '31-33') |
naics_ |
string | no | Industry title for this code in this edition |
level |
integer | no | Hierarchy depth = number of digits in the base code (2=sector … 6=national industry) |
naics_vintage_map · table¶
Data-year to applicable NAICS edition. Join fact.year = naics_vintage_map.data_year to resolve naics_vintage, then join ref.naics_vintage on (naics_vintage, naics_code) for the period-correct title. Loaded editions: 2017, 2022; years before the earliest loaded edition map to it (documented fallback — refine when older editions are sourced).
| Column | Type | Null | Description |
|---|---|---|---|
data_ |
integer | no | Calendar/data year |
naics_ |
integer | no | NAICS edition year in effect for data_year (matches ref.naics_vintage.vintage) |
sic · table¶
SEC EDGAR Standard Industrial Classification codes: one row per 4-digit SIC code with its EDGAR Assistant-Director office and industry title. FK target for sec.filing_metadata.sic_code. Source: SEC EDGAR siccodes list.
| Column | Type | Null | Description |
|---|---|---|---|
sic_ |
string | no | 4-digit SIC code, zero-padded (e.g. '0100', '7372') |
ad_ |
string | yes | EDGAR Assistant-Director office / industry group that reviews filers in this SIC |
industry_ |
string | no | SIC industry title (SEC EDGAR display form) |
currencies · table¶
ISO 4217 active currencies: one row per alphabetic code with its name, numeric code, and minor-unit (decimal places). FK target for ref.countries.currency_code. Withdrawn codes are excluded. Source: ISO 4217 (datasets/currency-codes).
| Column | Type | Null | Description |
|---|---|---|---|
currency_ |
string | no | ISO 4217 3-letter alphabetic code (e.g. 'USD') |
currency_ |
string | no | Currency name (e.g. 'US Dollar') |
numeric_ |
string | yes | ISO 4217 3-digit numeric code, zero-padded (e.g. '840') |
minor_ |
integer | yes | Number of decimal places in the minor unit (e.g. 2 for cents; null when not applicable) |
calendar · table¶
Daily date spine from 2010-01-01 through two years past the current year, one row per calendar day. The full-date representations (date_key, iso_date, basic_iso YYYYMMDD, iso_ordinal YYYY-DDD, iso_week_date YYYY-Www-D, epoch_day) are each unique and serve as FK targets for date columns in any form. Also carries numeric and named date parts, calendar flags, and US-federal fiscal attributes (fiscal_year FKs ref.fiscal_year). Generated by CalendarDataProvider — no external source.
| Column | Type | Null | Description |
|---|---|---|---|
date_ |
date | no | Calendar day as a native DATE (canonical FK target for DATE-typed columns) |
iso_ |
string | no | ISO-8601 calendar date YYYY-MM-DD (FK target for ISO date strings) |
basic_ |
string | no | ISO-8601 basic date YYYYMMDD (FK target for compact date stamps) |
iso_ |
string | no | ISO-8601 ordinal date YYYY-DDD |
iso_ |
string | no | ISO-8601 week date YYYY-Www-D |
epoch_ |
long | no | Days since 1970-01-01 (numeric full-date form) |
year |
integer | no | Calendar year |
quarter |
integer | no | Calendar quarter (1-4) |
month |
integer | no | Month of year (1-12) |
day_ |
integer | no | Day of month (1-31) |
day_ |
integer | no | ISO day of week (1=Monday .. 7=Sunday) |
day_ |
integer | no | US day of week (1=Sunday .. 7=Saturday) |
day_ |
integer | no | Day of year (1-366) |
day_ |
integer | no | Day of quarter (1-92) |
iso_ |
integer | no | ISO-8601 week of week-based year (1-53) |
iso_ |
integer | no | ISO-8601 week-based year (may differ from year near Jan/Dec boundaries) |
days_ |
integer | no | Number of days in this month |
month_ |
string | no | Full month name (English, e.g. January) |
month_ |
string | no | Abbreviated month name (English, e.g. Jan) |
day_ |
string | no | Full weekday name (English, e.g. Monday) |
day_ |
string | no | Abbreviated weekday name (English, e.g. Mon) |
year_ |
string | no | Month-grain key YYYY-MM |
year_ |
string | no | Quarter-grain key YYYY-Qn |
year_ |
string | no | ISO week-grain key YYYY-Www |
is_ |
boolean | no | True on Saturday or Sunday |
is_ |
boolean | no | True Monday through Friday |
is_ |
boolean | no | True on the first day of the month |
is_ |
boolean | no | True on the last day of the month |
is_ |
boolean | no | True on the first day of the quarter |
is_ |
boolean | no | True on the last day of the quarter |
is_ |
boolean | no | True on January 1 |
is_ |
boolean | no | True on December 31 |
is_ |
boolean | no | True when the calendar year is a leap year |
fiscal_ |
integer | no | US federal fiscal year (starts Oct 1; FK to fiscal_year.fiscal_year) |
fiscal_ |
integer | no | US federal fiscal quarter (1=Oct-Dec .. 4=Jul-Sep) |
fiscal_ |
integer | no | US federal fiscal month (1=October .. 12=September) |
fiscal_ |
string | no | Fiscal year label, e.g. FY2024 |
day_ |
integer | no | Day of the federal fiscal year (1-366, from Oct 1) |
fiscal_year · table¶
US federal fiscal-year dimension: one row per fiscal year (FY starts Oct 1, labelled by the calendar year it ends — FY2024 = 2023-10-01..2024-09-30). Boundary and per-quarter start dates are ISO strings that join to ref.calendar. Conformed FK target for fiscal_year across schemas (ref.calendar.fiscal_year references it). Generated by FiscalYearDataProvider — no external source. Covers FY2010 through two years past the current fiscal year.
| Column | Type | Null | Description |
|---|---|---|---|
fiscal_ |
integer | no | US federal fiscal year (e.g. 2024 = 2023-10-01..2024-09-30) |
fiscal_ |
string | no | Fiscal year label, e.g. FY2024 |
start_ |
date | no | First day of the fiscal year (Oct 1 of the prior calendar year) |
end_ |
date | no | Last day of the fiscal year (Sep 30) |
start_ |
string | no | start_date as ISO YYYY-MM-DD (FK to calendar.iso_date) |
end_ |
string | no | end_date as ISO YYYY-MM-DD (FK to calendar.iso_date) |
calendar_ |
string | no | Spanning calendar years, e.g. 2023-2024 |
days_ |
integer | no | Number of days in the fiscal year (365 or 366) |
q1_ |
string | no | Fiscal Q1 start (Oct 1) as ISO YYYY-MM-DD |
q2_ |
string | no | Fiscal Q2 start (Jan 1) as ISO YYYY-MM-DD |
q3_ |
string | no | Fiscal Q3 start (Apr 1) as ISO YYYY-MM-DD |
q4_ |
string | no | Fiscal Q4 start (Jul 1) as ISO YYYY-MM-DD |
prior_ |
integer | no | The preceding fiscal year |
next_ |
integer | no | The following fiscal year |
holidays · table¶
Public holidays for every country supported by the Nager.Date API (~110), one row per (country, holiday) per year. Joins to ref.calendar via holiday_date (ISO YYYY-MM-DD) and to ref.countries via country_code (ISO 3166-1 alpha-2). Generated by HolidaysDataProvider, which pulls /api/v3/AvailableCountries then /api/v3/PublicHolidays/{year}/{country}. Source: date.nager.at (free, no API key).
| Column | Type | Null | Description |
|---|---|---|---|
country_ |
string | no | ISO 3166-1 alpha-2 country code (FK to countries.iso_alpha2) |
country_ |
string | yes | English country name (Nager AvailableCountries) |
holiday_ |
string | no | Holiday date ISO YYYY-MM-DD (FK to calendar.iso_date) |
holiday_ |
string | no | Holiday name in English |
local_ |
string | yes | Holiday name in the local language |
is_ |
boolean | yes | True when the holiday applies nationwide (not subdivision-scoped) |
is_ |
boolean | yes | True when the holiday falls on the same calendar date every year |
counties |
string | yes | Comma-separated ISO-3166-2 subdivisions when the holiday is regional (else null) |
launch_ |
integer | yes | Year the holiday was first observed, when known |
holiday_ |
string | yes | Comma-separated Nager holiday types (Public, Bank, Optional, ...) |
ticker_instrument_map · view¶
Connects OpenFIGI instruments to SEC filers via shared ticker symbol. One row per ticker+figi combination — enables instrument-to-company resolution and cross-referencing FIGI, CIK, and company name for equity analysis. Self-contained within the ref schema; no dependency on other schemas.
View — columns are resolved by the query engine at runtime.
current_naics · view¶
Current NAICS edition — the latest vintage in naics_vintage. Tracks the newest loaded edition automatically (ref.naics remains the materialized current-edition FK spine; use this view when working from the vintage table). See naics_vintage_map for period-correct (non-current) edition selection.
View — columns are resolved by the query engine at runtime.
current_gleif_entities · view¶
Current-state GLEIF entities: the latest row per LEI (max last_update) from the append-only gleif_entities changelog. Use for "as-now" lookups; query gleif_entities directly (group by last_update) for point-in-time / history.
View — columns are resolved by the query engine at runtime.