📋 `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.

27 datasets · 268 columns

gleif_entities · table¶

GLEIF Legal Entity Identifier (LEI) records from the Global LEI Foundation golden copy CSV — entity registration details, jurisdiction, legal form, HQ/registered addresses, and registration authority links. The registration_authority_entity_id field contains the SEC CIK number for entities registered with RA000665 (SEC EDGAR); most other registration authorities carry that jurisdiction's own business-registry number instead (e.g. RA000602, Delaware Division of Corporations), so this column is only a CIK when registration_authority_id is actually RA000665 — see gleif_cik_mapping, which applies that filter. Append-only changelog: one row per LEI per publish in which it changed, with last_update as the history axis; use the current_gleif_entities view for the latest row per LEI. Source: GLEIF Golden Copy (goldencopy.gleif.org).

Column	Type	Null	Description
`lei`	string	no	Legal Entity Identifier (20-character alphanumeric)
`legal_name`	string	yes	Legal name of the entity
`legal_name_language`	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_status`	string	yes	Entity status (ACTIVE, INACTIVE, etc.)
`entity_legal_form`	string	yes	Entity legal form code (ELF code)
`registration_authority_id`	string	yes	Registration authority identifier (e.g., RA000665 for SEC EDGAR; RA000602 is Delaware Division of Corporations, the most common non-SEC value for US entities)
`registration_authority_entity_id`	string	yes	Entity ID at the registration authority (CIK for SEC registrants)
`headquarters_country`	string	yes	Country of headquarters (ISO 3166-1 alpha-2)
`headquarters_city`	string	yes	City of headquarters
`registered_country`	string	yes	Country of legal registration (ISO 3166-1 alpha-2)
`registered_city`	string	yes	City of legal registration
`registration_date`	string	yes	Initial LEI registration date (ISO 8601)
`last_update`	string	yes	Last update date of the LEI record (ISO 8601)
`next_renewal`	string	yes	Next renewal date for the LEI (ISO 8601)

gleif_cik_mapping · table¶

Bridge table mapping GLEIF LEI codes to SEC CIK numbers (lei, cik, legal_name, entity_status). Derived from the GLEIF golden copy CSV filtered where registration_authority_id = 'RA000665' (SEC EDGAR). That authority ID covers both real filer CIKs and SEC fund-series identifiers (e.g. "S000005113") registered with SEC but not themselves filers — a validator drops any non-numeric cik value, since a series ID never matches a real CIK downstream. Current snapshot only, overwritten each run — unlike gleif_entities, which keeps an append-only change history. Provides fast LEI <-> CIK lookups without scanning the full GLEIF dataset. entity_org_bridge.sec_cik (EIN-path matches) joins here on cik = sec_cik to reach the LEI — the exact bridge for that column; do not name-match its org against gleif_entities.legal_name.

Column	Type	Null	Description
`lei`	string	no	Legal Entity Identifier (20-character alphanumeric)
`cik`	string	no	SEC Central Index Key (CIK) number
`legal_name`	string	yes	Legal name of the entity
`entity_status`	string	yes	Entity status (ACTIVE, INACTIVE, etc.)

gleif_relationships · table¶

GLEIF Level 2 relationship records — the LEI-to-LEI edges that LEI-CDF (gleif_entities) does not carry. One row per directed relationship. Both endpoints are always LEIs, so the table self-joins to gleif_entities on either side and, through gleif_cik_mapping, resolves to SEC CIKs. relationship_type values (2026-07-31 publish, 482,849 rows): IS_DIRECTLY_CONSOLIDATED_BY (126,021) and IS_ULTIMATELY_CONSOLIDATED_BY (132,196) are the two levels of the ownership hierarchy; IS_FUND-MANAGED_BY (148,576), IS_SUBFUND_OF (72,731), IS_FEEDER_TO (1,386) are fund structures; IS_INTERNATIONAL_BRANCH_OF (1,939) is branch registration. In every type start_node_id is the subordinate entity and end_node_id is the superior one — but only consolidation means ownership. A fund commonly carries a manager AND an umbrella (59,419 entities; the two are different entities in 60,266 of 60,370 cases): two distinct relations, not two parents. GLEIF flattens the ownership chain to two attested levels and never publishes the intermediate one, so IS_ULTIMATELY_CONSOLIDATED_BY is largely derivable — walking the direct chain reaches the declared ultimate for 113,860 of the 118,627 entities that report both. It is kept because it is REPORTED, not computed: 13,569 entities declare an ultimate with no direct edge to walk from, and 4,765 chains break at an intermediate holding company that has no LEI. Dropping it loses those ~18k attestations. Only entities that report a relationship to GLEIF appear here — an absent edge means "not reported", which the REPEX (reporting exception) publish explains and this table does not carry. Source: GLEIF Golden Copy RR (goldencopy.gleif.org).

Column	Type	Null	Description
`start_node_id`	string	no	LEI of the subordinate entity in the relationship (start_node_id_type is LEI on all 482,849 rows)
`start_node_id_type`	string	yes	Node identifier scheme; LEI throughout the current publish
`end_node_id`	string	no	LEI of the superior entity in the relationship
`end_node_id_type`	string	yes	Node identifier scheme; LEI throughout the current publish
`relationship_type`	string	no	IS_DIRECTLY_CONSOLIDATED_BY, IS_ULTIMATELY_CONSOLIDATED_BY, IS_FUND-MANAGED_BY, IS_SUBFUND_OF, IS_FEEDER_TO, IS_INTERNATIONAL_BRANCH_OF
`relationship_status`	string	yes	ACTIVE or INACTIVE
`period_1_start_date`	string	yes
`period_1_end_date`	string	yes
`period_1_type`	string	yes
`period_2_start_date`	string	yes
`period_2_end_date`	string	yes
`period_2_type`	string	yes
`period_3_start_date`	string	yes
`period_3_end_date`	string	yes
`period_3_type`	string	yes
`period_4_start_date`	string	yes
`period_4_end_date`	string	yes
`period_4_type`	string	yes
`period_5_start_date`	string	yes
`period_5_end_date`	string	yes
`period_5_type`	string	yes
`qualifier_1_dimension`	string	yes
`qualifier_1_category`	string	yes
`qualifier_2_dimension`	string	yes
`qualifier_2_category`	string	yes
`qualifier_3_dimension`	string	yes
`qualifier_3_category`	string	yes
`qualifier_4_dimension`	string	yes
`qualifier_4_category`	string	yes
`qualifier_5_dimension`	string	yes
`qualifier_5_category`	string	yes
`quantifier_1_method`	string	yes
`quantifier_1_amount`	string	yes
`quantifier_1_units`	string	yes
`quantifier_2_method`	string	yes
`quantifier_2_amount`	string	yes
`quantifier_2_units`	string	yes
`quantifier_3_method`	string	yes
`quantifier_3_amount`	string	yes
`quantifier_3_units`	string	yes
`quantifier_4_method`	string	yes
`quantifier_4_amount`	string	yes
`quantifier_4_units`	string	yes
`quantifier_5_method`	string	yes
`quantifier_5_amount`	string	yes
`quantifier_5_units`	string	yes
`registration_initial_date`	string	yes
`registration_last_update`	string	yes	Last update of the relationship record (ISO 8601) — the computed_delta change key
`registration_status`	string	yes	PUBLISHED, LAPSED, PENDING_ARCHIVAL or PENDING_TRANSFER — lifecycle of the relationship record itself
`registration_next_renewal`	string	yes
`registration_managing_lou`	string	yes	LEI of the Local Operating Unit maintaining the record
`registration_validation_sources`	string	yes	FULLY_CORROBORATED, PARTIALLY_CORROBORATED or ENTITY_SUPPLIED_ONLY
`registration_validation_documents`	string	yes
`registration_validation_reference`	string	yes

sec_company_tickers · table¶

SEC EDGAR company ticker mapping: cik, ticker, title (registered company name), and as_of (the UTC ingest date). ~10,000 active US exchange-listed companies, sourced from the SEC EDGAR company_tickers.json file (updated daily). Each run stamps a fresh as_of snapshot, so grouping by as_of recovers ticker/name history over time. 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_of`	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_code`	string	yes	Exchange code (e.g., US, LN, JP)
`market_sector`	string	yes	Market sector (Equity, Govt, Corp, etc.)
`security_type`	string	yes	Security type (Common Stock, ADR, ETF, etc.)
`security_type2`	string	yes	Secondary security type classification
`security_description`	string	yes	Security description
`composite_figi`	string	yes	Composite FIGI (country-level aggregate identifier)
`share_class_figi`	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_cty_code`	string	no	Census 4-digit foreign-trade country code (FK target for the trade tables)
`iso_alpha2`	string	yes	ISO 3166-1 alpha-2 code
`iso_alpha3`	string	yes	ISO 3166-1 alpha-3 code
`iso_numeric`	string	yes	ISO 3166-1 numeric code
`fips_code`	string	yes	FIPS 10-4 country code
`country_name`	string	yes	Census display name (clean short form)
`official_name`	string	yes	ISO English official name
`bea_area_name`	string	yes	country_name with any parenthetical stripped; best-effort join key for BEA area_or_country
`currency_code`	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_region`	string	yes	M49 intermediate region (nullable)
`continent`	string	yes	Continent code (e.g. NA, EU, AS)
`is_aggregate`	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_code`	string	no	2-6 digit NAICS code (sectors may be a range, e.g. '31-33')
`naics_title`	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_code`	string	no	2-6 digit NAICS code (sectors may be a range, e.g. '31-33')
`naics_title`	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_year`	integer	no	Calendar/data year
`naics_vintage`	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_code`	string	no	4-digit SIC code, zero-padded (e.g. '0100', '7372')
`ad_office`	string	yes	EDGAR Assistant-Director office / industry group that reviews filers in this SIC
`industry_title`	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_code`	string	no	ISO 4217 3-letter alphabetic code (e.g. 'USD')
`currency_name`	string	no	Currency name (e.g. 'US Dollar')
`numeric_code`	string	yes	ISO 4217 3-digit numeric code, zero-padded (e.g. '840')
`minor_unit`	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_key`	date	no	Calendar day as a native DATE (canonical FK target for DATE-typed columns)
`iso_date`	string	no	ISO-8601 calendar date YYYY-MM-DD (FK target for ISO date strings)
`basic_iso`	string	no	ISO-8601 basic date YYYYMMDD (FK target for compact date stamps)
`iso_ordinal`	string	no	ISO-8601 ordinal date YYYY-DDD
`iso_week_date`	string	no	ISO-8601 week date YYYY-Www-D
`epoch_day`	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_of_month`	integer	no	Day of month (1-31)
`day_of_week_iso`	integer	no	ISO day of week (1=Monday .. 7=Sunday)
`day_of_week_us`	integer	no	US day of week (1=Sunday .. 7=Saturday)
`day_of_year`	integer	no	Day of year (1-366)
`day_of_quarter`	integer	no	Day of quarter (1-92)
`iso_week`	integer	no	ISO-8601 week of week-based year (1-53)
`iso_week_year`	integer	no	ISO-8601 week-based year (may differ from year near Jan/Dec boundaries)
`days_in_month`	integer	no	Number of days in this month
`month_name`	string	no	Full month name (English, e.g. January)
`month_abbr`	string	no	Abbreviated month name (English, e.g. Jan)
`day_name`	string	no	Full weekday name (English, e.g. Monday)
`day_abbr`	string	no	Abbreviated weekday name (English, e.g. Mon)
`year_month`	string	no	Month-grain key YYYY-MM
`year_quarter`	string	no	Quarter-grain key YYYY-Qn
`year_iso_week`	string	no	ISO week-grain key YYYY-Www
`is_weekend`	boolean	no	True on Saturday or Sunday
`is_weekday`	boolean	no	True Monday through Friday
`is_month_start`	boolean	no	True on the first day of the month
`is_month_end`	boolean	no	True on the last day of the month
`is_quarter_start`	boolean	no	True on the first day of the quarter
`is_quarter_end`	boolean	no	True on the last day of the quarter
`is_year_start`	boolean	no	True on January 1
`is_year_end`	boolean	no	True on December 31
`is_leap_year`	boolean	no	True when the calendar year is a leap year
`fiscal_year`	integer	no	US federal fiscal year (starts Oct 1; FK to fiscal_year.fiscal_year)
`fiscal_quarter`	integer	no	US federal fiscal quarter (1=Oct-Dec .. 4=Jul-Sep)
`fiscal_month`	integer	no	US federal fiscal month (1=October .. 12=September)
`fiscal_year_label`	string	no	Fiscal year label, e.g. FY2024
`day_of_fiscal_year`	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_year`	integer	no	US federal fiscal year (e.g. 2024 = 2023-10-01..2024-09-30)
`fiscal_year_label`	string	no	Fiscal year label, e.g. FY2024
`start_date`	date	no	First day of the fiscal year (Oct 1 of the prior calendar year)
`end_date`	date	no	Last day of the fiscal year (Sep 30)
`start_iso_date`	string	no	start_date as ISO YYYY-MM-DD (FK to calendar.iso_date)
`end_iso_date`	string	no	end_date as ISO YYYY-MM-DD (FK to calendar.iso_date)
`calendar_years`	string	no	Spanning calendar years, e.g. 2023-2024
`days_in_fiscal_year`	integer	no	Number of days in the fiscal year (365 or 366)
`q1_start_date`	string	no	Fiscal Q1 start (Oct 1) as ISO YYYY-MM-DD
`q2_start_date`	string	no	Fiscal Q2 start (Jan 1) as ISO YYYY-MM-DD
`q3_start_date`	string	no	Fiscal Q3 start (Apr 1) as ISO YYYY-MM-DD
`q4_start_date`	string	no	Fiscal Q4 start (Jul 1) as ISO YYYY-MM-DD
`prior_fiscal_year`	integer	no	The preceding fiscal year
`next_fiscal_year`	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_code`	string	no	ISO 3166-1 alpha-2 country code (FK to countries.iso_alpha2)
`country_name`	string	yes	English country name (Nager AvailableCountries)
`holiday_date`	string	no	Holiday date ISO YYYY-MM-DD (FK to calendar.iso_date)
`holiday_name`	string	no	Holiday name in English
`local_name`	string	yes	Holiday name in the local language
`is_global`	boolean	yes	True when the holiday applies nationwide (not subdivision-scoped)
`is_fixed`	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_year`	integer	yes	Year the holiday was first observed, when known
`holiday_types`	string	yes	Comma-separated Nager holiday types (Public, Bank, Optional, ...)

entity_org_bridge · table¶

Tall audit/match log: one row per (source_schema, source_table, source_column, source_key) org-name mention, carrying its best-match LEI (via GLEIF fuzzy/exact match) or SEC CIK (via EIN exact match against sec.filing_metadata.irs_number). Rebuilt in full every run by EntityBridgeListener from the org-type source registry (fec, patents, sec, health, environment, energy, transport, fiscal). Absence of a row below match_score 0.85 is a clean "no confident match" signal, not a data gap. Primary consumers should generally query canonical_org_entity instead; use this table when the full audit trail or per-source detail matters.

Column	Type	Null	Description
`source_schema`	string	no	Owning AskAmerica schema, e.g. 'fec', 'patents', 'sec'
`source_table`	string	no	Source table name, e.g. 'committees', 'patent_assignees'
`source_column`	string	no	Name column matched, e.g. 'connected_org_name'; disambiguates same-table dual-role columns (eia_coal_mines controller_name vs operator_name)
`source_key`	string	no	Registry key_column value; the normalized name itself for unstructured (name-only) sources
`source_name_raw`	string	yes	Original source name, for audit
`source_name_normalized`	string	yes	Normalized name (lowercase, punctuation/legal-suffix stripped), for debugging match misses
`lei`	string	yes	GLEIF Legal Entity Identifier, populated for GLEIF-path matches
`sec_cik`	string	yes	SEC CIK, populated for EIN-path matches via sec.filing_metadata.irs_number. To reach this CIK's GLEIF LEI, join ref.gleif_cik_mapping on cik = sec_cik — that table is the exact bridge (RA000665-filtered GLEIF golden copy); do not name-match sec_cik's org against gleif_entities.legal_name.
`gleif_legal_name`	string	yes	Denormalized from ref.gleif_entities, avoids a join for the common case
`match_method`	string	no	exact_ein \| exact_normalized \| fuzzy
`match_confidence`	string	no	high \| low \| ambiguous. high/low apply to fuzzy matches (score-threshold split); exact_ein is always high (EIN->CIK is 1:1 by construction). exact_normalized is high only when the normalized name resolves to exactly one GLEIF LEI; when it resolves to more than one (Defect Register B2-1 -- e.g. 6 distinct LEIs shared source_name_normalized='apple' before this fix), every candidate row is still written but labelled ambiguous rather than asserted as equally high-confidence.
`match_score`	double	no	1.0 for exact; raw jaro_winkler_similarity for fuzzy
`match_run_id`	string	no	ISO timestamp of the build run, so bridge quality is diffable across reruns

entity_person_bridge · table¶

Tall N-way pairwise person-match log across person-type sources (fec.candidates, sec.insider_transactions individual-type rows, health.cms_open_payments, transport.faa_aircraft_registry individual-type registrants). One row per matched pair of source entities; only holds rows where a match was found — most people known to one source are never in another, so a "no match" majority is expected and not written. A person appearing in 3+ sources yields multiple pairwise rows rather than one N-ary row. Rebuilt in full every run.

Column	Type	Null	Description
`source_a_schema`	string	no	First side of the match — owning schema
`source_a_table`	string	no	First side of the match — source table
`source_a_key`	string	no	First side of the match — registry key_column value
`source_a_name_raw`	string	yes	First side of the match — original raw name
`source_b_schema`	string	no	Second side of the match — owning schema
`source_b_table`	string	no	Second side of the match — source table
`source_b_key`	string	no	Second side of the match — registry key_column value
`source_b_name_raw`	string	yes	Second side of the match — original raw name
`match_method`	string	no	exact_normalized \| fuzzy_first_name
`match_confidence`	string	no	high \| low — low is never silently promoted
`match_score`	double	no	First-name jaro_winkler_similarity for fuzzy rows; 1.0 for exact
`match_run_id`	string	no	ISO timestamp, diffable across reruns

canonical_org_entity · table¶

THE CROSS-SOURCE JOIN TABLE FOR ORGANISATIONS — a wide crosswalk you join on, carrying one nullable foreign key per source alongside a _confidence sibling for each. Use it to define a SET by exact predicate rather than by matching names: "every firm that files with the SEC and holds patents" is WHERE sec_cik IS NOT NULL AND patents_assignee_id IS NOT NULL, then a direct join to sec.filing_metadata on cik and patents.patent_assignees on assignee_id. No name matching, no match rate, no candidate review. The resolve_entity tool is for the other case — a person handed you one name and you need its keys. One row per SOURCE MENTION, not per real-world org: a company whose patents are split across three assignee_ids gets three rows, all sharing one canonical_entity_id and canonical_name, each carrying a different patents_assignee_id. Identity columns (canonical_name, lei, sec_cik) are resolved across the whole org and repeated on every one of its rows, so a cross-source predicate still matches on a single row — and now returns every key a source holds for that org rather than one arbitrary representative. COUNT rows here and you are counting mentions; COUNT DISTINCT canonical_entity_id to count organisations. An org identified only in patents.patent_assignees (never resolved) still gets a row, with lei left null. Nullable FK columns point at each source's natural key; a "_confidence" sibling column (high | low | null) accompanies every FK. Consumer tables, each carrying a cross-reference back to its own FK column here: fec.committees (fec_committee_id), sec.insider_transactions (sec_reporting_person_cik), sec.institutional_holdings (sec_manager_cik), patents.patent_assignees (patents_assignee_id), patents.trademark_owner (patents_trademark_owner_id), health.fda_drug_approvals (fda_sponsor_name), health.fda_drug_recalls (fda_drug_recalling_firm), health.fda_device_recalls (fda_device_recalling_firm), health.fda_ndc_products (fda_labeler_name), health.cms_open_payments (cms_paying_entity_name), health.clinical_trials (clinical_trials_nct_id), environment.ghg_facilities (ghg_parent_company_name), energy.eia_utility_annual (eia_utility_id), energy.eia_coal_mines (eia_coal_controller_name, eia_coal_operator_name), transport.fmcsa_carriers (fmcsa_dot_number), transport.faa_aircraft_registry (faa_registrant_name), fiscal.exempt_org_master (exempt_org_ein), fiscal.sba_loan_approvals (sba_borrower_name, sba_lender_name), research.nsf_herd_by_institution (nsf_herd_inst_id), edu.ipeds_institutions (ipeds_unitid). Structured-key sources (a per-row id/CIK/EIN already on the source table — fec_committee_id, sec_reporting_person_cik, sec_manager_cik, patents_assignee_id, patents_trademark_owner_id, eia_utility_id, fmcsa_dot_number, exempt_org_ein, clinical_trials_nct_id, ipeds_unitid) get an exact join here on that id; unstructured sources (name-only, no per-row id) get the fuzzy-resolved identity this table already carries, so a cross-schema link still avoids re-running the fuzzy match by hand.

Column	Type	Null	Description
`canonical_entity_id`	string	no	Surrogate PK — the LEI when present; the SEC CIK when EIN-matched but no LEI; else a deterministic hash of the first-seen (source_schema, source_table, source_column, source_key)
`canonical_name`	string	yes	GLEIF legal_name when lei is populated, else the longest/most-complete raw source name seen
`lei`	string	yes
`sec_cik`	string	yes	Via EIN exact match against sec.filing_metadata.irs_number
`fec_committee_id`	string	yes	Scalar FK, safe 1:1 — a committee represents one org
`fec_committee_id_confidence`	string	yes
`sec_reporting_person_cik`	string	yes	Entity-type SEC insiders — SEC's own stable per-filer ID, safe 1:1
`sec_reporting_person_cik_confidence`	string	yes
`patents_assignee_id`	string	yes	PatentsView assignee_id. Its disambiguation is imperfect, so one company can hold several — measured 5,224 companies split across 13,650 ids, one holding 78. Each gets its own row (see this table's grain), so joining patents.patent_assignees on this column reaches all of a company's patents.
`patents_assignee_id_confidence`	string	yes
`fda_sponsor_name`	string	yes	Unstructured source, keyed by normalized name
`fda_sponsor_name_confidence`	string	yes
`ghg_parent_company_name`	string	yes	Unstructured source, keyed by normalized name
`ghg_parent_company_name_confidence`	string	yes
`cms_paying_entity_name`	string	yes	Unstructured source, keyed by normalized name
`cms_paying_entity_name_confidence`	string	yes
`eia_utility_id`	string	yes
`eia_utility_id_confidence`	string	yes
`eia_coal_controller_name`	string	yes	Mine controller — logically distinct from operator, same source table
`eia_coal_controller_name_confidence`	string	yes
`eia_coal_operator_name`	string	yes	Mine operator — logically distinct from controller, same source table
`eia_coal_operator_name_confidence`	string	yes
`fmcsa_dot_number`	string	yes	Lossy potential given ~4.47M-row source scale — see table comment
`fmcsa_dot_number_confidence`	string	yes
`faa_registrant_name`	string	yes	Unstructured, entity-type registrants only (registrant_type != Individual)
`faa_registrant_name_confidence`	string	yes
`exempt_org_ein`	string	yes	EIN exact-match hub key, alongside GLEIF LEI
`exempt_org_ein_confidence`	string	yes
`sba_borrower_name`	string	yes	Unstructured, distinct role from lender on the same source table
`sba_borrower_name_confidence`	string	yes
`sba_lender_name`	string	yes	Unstructured, distinct role from borrower on the same source table
`sba_lender_name_confidence`	string	yes
`sec_manager_cik`	string	yes	Form 13F institutional investment manager, SEC's own stable per-filer ID
`sec_manager_cik_confidence`	string	yes
`patents_trademark_owner_id`	string	yes	USPTO trademark applicant/owner (own_id), org-shaped rows
`patents_trademark_owner_id_confidence`	string	yes
`fda_device_recalling_firm`	string	yes	Structural twin of fda_drug_recalling_firm below, for medical device recalls
`fda_device_recalling_firm_confidence`	string	yes
`fda_labeler_name`	string	yes	Lossy potential — product_ndc is per-product, many products share one labeler
`fda_labeler_name_confidence`	string	yes
`nsf_herd_inst_id`	string	yes	NSF HERD survey institution; ipeds_unitid below is the same real institution via a different registry
`nsf_herd_inst_id_confidence`	string	yes
`ipeds_unitid`	string	yes	NCES IPEDS institution directory
`ipeds_unitid_confidence`	string	yes
`clinical_trials_nct_id`	string	yes	ClinicalTrials.gov lead sponsor
`clinical_trials_nct_id_confidence`	string	yes
`fda_drug_recalling_firm`	string	yes	FDA drug (not device) recall — recall_number is per-recall, not per-firm
`fda_drug_recalling_firm_confidence`	string	yes

canonical_person_entity · table¶

One row per real-world person known to any person-type registry source, whether or not they cross-matched another source. A row with 2+ FKs populated is a person-track match (e.g. a political candidate who is also a corporate insider); a row with only one FK populated is simply "known to that one source, never cross-matched" — both are legitimate outcomes. No third-party canonical individual registry exists (unlike GLEIF for orgs), so these FKs are direct pairwise links between source pools, not resolutions to an external hub.

Column	Type	Null	Description
`canonical_entity_id`	string	no	Surrogate PK — deterministic hash of the first-seen source key; no external hub key exists for individuals
`canonical_name`	string	yes	Best-available parsed (first, last) name
`fec_candidate_id`	string	yes
`fec_candidate_id_confidence`	string	yes
`sec_reporting_person_cik`	string	yes	Already 1:1 by SEC's own construction
`sec_reporting_person_cik_confidence`	string	yes
`cms_physician_profile_id`	string	yes	Already 1:1 by CMS's own construction
`cms_physician_profile_id_confidence`	string	yes
`faa_registrant_name`	string	yes	Unstructured, individual-type registrants only
`faa_registrant_name_confidence`	string	yes
`patents_individual_assignee_id`	string	yes	patents.patent_assignees rows where assignee_type is '4' (US individual) or '5' (foreign individual) — a patent assigned directly to a person rather than a company. Distinct from the org-track's patents_assignee_id (canonical_org_entity), which only covers assignee_type '2'/'3' (company rows).
`patents_individual_assignee_id_confidence`	string	yes
`officials_member_bioguide_id`	string	yes	House/Senate member, Congress.gov's own stable per-member ID
`officials_member_bioguide_id_confidence`	string	yes
`officials_judge_jid`	string	yes	Article III federal judge, FJC's own stable per-judge ID
`officials_judge_jid_confidence`	string	yes

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.