Crosswalks are weighted relationships, not lookups
A crosswalk must support:
A crosswalk connects a source classification to the platform’s canonical product, industry, geography, demographic, labor, or price concept.
Required supported crosswalk families:
NAICS → NAPCS
NAPCS → NAICS
CEX UCC → NAPCS
CEX UCC → CPI ELI
CPI ELI → NAPCS
PPI Commodity → NAPCS
PPI Industry → NAICS
PPI Industry → NAPCS
PCE Category → NAPCS
PCE Category → CEX UCC
NAICS → SOC
SOC → OEWS occupation
Geography source code → Census GEOID
create table analytics.crosswalk (
crosswalk_id uuid primary key,
crosswalk_key text not null unique,
source_system text not null,
source_classification text not null,
source_vintage text not null,
target_system text not null,
target_classification text not null,
target_vintage text not null,
crosswalk_purpose text not null check (
crosswalk_purpose in (
'product_alignment',
'industry_alignment',
'price_deflation',
'consumer_spending',
'labor_cost',
'geography',
'benchmarking',
'custom'
)
),
methodology text not null,
version_number integer not null,
status text not null check (
status in ('draft', 'review', 'approved', 'deprecated')
),
owner_name text not null,
approved_by text,
approved_at timestamptz,
created_at timestamptz not null default now()
);
create table analytics.crosswalk_member (
crosswalk_member_id uuid primary key,
crosswalk_id uuid not null references analytics.crosswalk,
source_code text not null,
source_label text,
target_code text not null,
target_label text,
relationship_type text not null check (
relationship_type in (
'exact',
'contains',
'contained_by',
'partial_overlap',
'proxy',
'derived',
'unmapped'
)
),
allocation_weight numeric(14,10),
coverage_ratio numeric(14,10),
confidence_score numeric(5,4) check (
confidence_score between 0 and 1
),
evidence_type text not null check (
evidence_type in (
'official_concordance',
'published_table',
'empirical_revenue_share',
'expert_judgment',
'equal_allocation',
'model_estimate',
'manual_review'
)
),
effective_start_date date,
effective_end_date date,
methodology_note text not null,
review_status text not null check (
review_status in ('pending', 'approved', 'rejected')
)
);
Weights must be assigned in this order of preference:
1. Official published concordance
2. Source-table product or revenue share
3. Empirical observed revenue or transaction share
4. Published industry or expenditure distribution
5. Modeled allocation
6. Expert-reviewed allocation
7. Equal allocation only as a documented fallback
For a given source code and crosswalk version:
sum(allocation_weight) = 1.000000
except where the crosswalk is intentionally partial.
For partial mappings:
sum(allocation_weight) <= 1.000000
coverage_ratio must be populated
unmapped residual must be recorded
Example:
CEX UCC 123456
→ NAPCS A: 0.60
→ NAPCS B: 0.25
→ NAPCS C: 0.10
→ Unmapped residual: 0.05
The platform must never silently rescale the mapped total to 100 percent unless the metric explicitly requests normalize_to_mapped_coverage = true.
exact:
Source and target represent substantially the same concept.
contains:
Source category includes the target plus additional concepts.
contained_by:
Source category is narrower than the target.
partial_overlap:
Source and target overlap but neither contains the other.
proxy:
Target is used as an approximation because no direct mapping exists.
derived:
Target is calculated from multiple source concepts.
unmapped:
No defensible mapping exists.
Only exact, contains, and contained_by mappings may be used in high-confidence estimates by default.