This runbook sets up the data dictionary tables and loads core reference datasets used across analytics. It covers downloading upstream code lists, converting files, staging into Postgres, and upserting into dimension tables.
Scope: NAICS, Gazetteer geographies (counties, ZCTAs, CBSAs, CSAs), ACS/ECN/NES variable catalogs, and common code lists such as employment-size classes and periods.
curl, jq, unzip, xlsx2csv (or LibreOffice headless).download_ref_dicts.sh — pulls upstream dictionaries into data/ref/...create_db.sql — creates dimensions and staging loaders used below.sudo apt-get update
sudo apt-get install -y curl jq unzip xlsx2csv postgresql-client
# Or: sudo apt-get install -y libreoffice-calc # if xlsx2csv is unavailable
Set environment for database connectivity:
export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=postgres # change as needed
export PGPASSWORD='***' # change as needed
export PGDATABASE=analytics # or your target DB
The downloader expects a structure like:
data/
ref/
naics/
gazetteer/
acs/
ecn/
nes/
napcs/
You can customize ROOT="data/ref" in the script if desired.
Run the downloader. It retrieves NAICS, Gazetteer, ACS/ECN variable catalogs, NES code lists, and NAPCS, and converts XLSX → CSV when xlsx2csv is present.
chmod +x ./download_ref_dicts.sh
./download_ref_dicts.sh
Outputs:
data/ref/naics/${NAICS_YEAR}_NAICS_Structure.xlsx (+ CSV if converted)data/ref/gazetteer/*_Gaz_*_national.txtdata/ref/acs/acs5_*_vars_${ACS_YEAR}.{json,csv}data/ref/ecn/ecnbasic_vars_${ECN_YEAR}.{json,csv}data/ref/nes/{lfo,rcpszfi,yibszfi,urszfi}_${NES_YEAR}.csvdata/ref/napcs/napcs_{2017,2022}.csv, napcs_2022_to_2017.csvDownloader reference: fileciteturn0file0
-- Schemas
create schema if not exists ref;
create schema if not exists core;
create schema if not exists stg;
-- Roles (example)
-- create role app_reader noinherit;
-- create role app_writer noinherit;
-- grant usage on schema ref,core,stg to app_reader, app_writer;
-- grant select on all tables in schema ref,core to app_reader;
-- alter default privileges in schema ref,core grant select on tables to app_reader;
-- ========== Reference + Dimensions ==========
-- 5.1 dim_geography
create table if not exists core.dim_geography (
geokey bigserial primary key,
geo_id text not null unique,
geo_type text not null check (geo_type in ('us','state','county','place','zcta','msa','csa','tract','bg')),
name text not null,
state_fips text,
county_fips text,
place_fips text,
zcta5 text,
tract text,
block_group text
);
create index if not exists dim_geography_type_state on core.dim_geography(geo_type, state_fips);
create index if not exists dim_geography_geo_id_idx on core.dim_geography(geo_id);
-- 5.2 dim_naics
create table if not exists core.dim_naics (
naicskey bigserial primary key,
naics text not null unique,
title text not null,
naics2 text,
naics3 text,
vintage text not null
);
create index if not exists dim_naics_naics_idx on core.dim_naics(naics);
-- 5.3 dim_period
create table if not exists core.dim_period (
year smallint primary key,
vintage text,
span text
);
-- 5.4 dim_emp_size
create table if not exists core.dim_emp_size (
sizekey bigserial primary key,
code text not null unique,
label text not null
);
-- 5.5 ref_vars (ACS/ECN/NES/… variable dictionary)
create table if not exists ref.ref_vars (
dataset text not null,
year smallint not null,
varname text not null,
label text,
concept text,
group_id text,
predicate_type text,
primary key (dataset, year, varname)
);
create index if not exists ref_vars_group_idx on ref.ref_vars(dataset, year, group_id);
-- 5.6 NES code lists
create table if not exists ref.ref_nes_lfo (year smallint not null, code text not null, label text not null, primary key(year,code));
create table if not exists ref.ref_nes_rcpszfi (year smallint not null, code text not null, label text not null, primary key(year,code));
create table if not exists ref.ref_nes_yibszfi (year smallint not null, code text not null, label text not null, primary key(year,code));
create table if not exists ref.ref_nes_urszfi (year smallint not null, code text not null, label text not null, primary key(year,code));
-- 5.7 NAPCS
create table if not exists ref.dim_napcs (
code text primary key,
title text not null,
vintage text not null
);
create table if not exists ref.ref_napcs_concordance_2022_2017(
code_2022 text not null, title_2022 text,
code_2017 text not null, title_2017 text
);
create index if not exists napcs_cc_22_idx on ref.ref_napcs_concordance_2022_2017(code_2022);
create index if not exists napcs_cc_17_idx on ref.ref_napcs_concordance_2022_2017(code_2017);
-- CBP
create table if not exists core.fact_cbp(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
naicskey bigint not null references core.dim_naics(naicskey),
year smallint not null references core.dim_period(year),
sizekey bigint references core.dim_emp_size(sizekey),
establishments bigint, employees bigint, pay_q1 numeric, pay_annual numeric,
constraint fact_cbp_pk primary key (state_fips, fact_id),
constraint fact_cbp_uk unique (state_fips, geokey, naicskey, year, sizekey)
) partition by list (state_fips);
-- NES (Nonemployer)
create table if not exists core.fact_nes(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
naicskey bigint not null references core.dim_naics(naicskey),
year smallint not null references core.dim_period(year),
firms bigint, receipts numeric,
constraint fact_nes_pk primary key (state_fips, fact_id),
constraint fact_nes_uk unique (state_fips, geokey, naicskey, year)
) partition by list (state_fips);
-- ECN Basic
create table if not exists core.fact_ecn_basic(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
naicskey bigint not null references core.dim_naics(naicskey),
year smallint not null references core.dim_period(year),
establishments bigint, employees bigint, payroll numeric, shipments_receipts numeric,
constraint fact_ecn_basic_pk primary key (state_fips, fact_id),
constraint fact_ecn_basic_uk unique (state_fips, geokey, naicskey, year)
) partition by list (state_fips);
-- ACS5 detailed
create table if not exists core.fact_acs5_detailed(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
year smallint not null references core.dim_period(year),
table_id text not null, line_number int not null,
value numeric, moe numeric,
constraint fact_acs5_detailed_pk primary key (state_fips, fact_id),
constraint fact_acs5_detailed_uk unique (state_fips, geokey, year, table_id, line_number)
) partition by list (state_fips);
-- ACS5 subject
create table if not exists core.fact_acs5_subject(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
year smallint not null references core.dim_period(year),
table_id text not null, line_number int not null,
value numeric, moe numeric,
constraint fact_acs5_subject_pk primary key (state_fips, fact_id),
constraint fact_acs5_subject_uk unique (state_fips, geokey, year, table_id, line_number)
) partition by list (state_fips);
-- ACS5 profile
create table if not exists core.fact_acs5_profile(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
year smallint not null references core.dim_period(year),
variable text not null, value numeric, moe numeric,
constraint fact_acs5_profile_pk primary key (state_fips, fact_id),
constraint fact_acs5_profile_uk unique (state_fips, geokey, year, variable)
) partition by list (state_fips);
-- ACS5 comparative profile
create table if not exists core.fact_acs5_cprofile(
fact_id bigint generated always as identity,
state_fips text not null,
geokey bigint not null references core.dim_geography(geokey),
year smallint not null references core.dim_period(year),
variable text not null,
current_value numeric, prior_value numeric, change numeric, significance_flag text,
constraint fact_acs5_cprofile_pk primary key (state_fips, fact_id),
constraint fact_acs5_cprofile_uk unique (state_fips, geokey, year, variable)
) partition by list (state_fips);
Table: stg.cbp_raw (landed)
Columns
| name | type | purpose |
|---|---|---|
| geo_id | text | Census GEOID (county, zcta, etc.) |
| state_fips | text | 2-digit state code |
| naics | text | NAICS code |
| year | smallint | reference year |
| empsz | text | employment size code |
| establishments | bigint | establishments |
| employees | bigint | employment |
| pay_q1 | numeric | Q1 payroll |
| pay_annual | numeric | annual payroll |
| src_file | text | provenance |
| loaded_at | timestamptz | audit |
create table if not exists stg.cbp_raw(
geo_id text, state_fips text, naics text, year smallint,
empsz text, establishments bigint, employees bigint, pay_q1 numeric, pay_annual numeric,
src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.cbp_norm(
geokey bigint, state_fips text, naicskey bigint, year smallint,
sizekey bigint, establishments bigint, employees bigint, pay_q1 numeric, pay_annual numeric,
src_file text, primary key(geokey,naicskey,year,sizekey)
);
Table: stg.nes_raw, stg.nes_norm
create table if not exists stg.nes_raw(
geo_id text, state_fips text, naics text, year smallint,
firms bigint, receipts numeric, src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.nes_norm(
geokey bigint, state_fips text, naicskey bigint, year smallint,
firms bigint, receipts numeric, src_file text,
primary key (geokey,naicskey,year)
);
create table if not exists stg.ecn_raw(
geo_id text, state_fips text, naics text, year smallint,
establishments bigint, employees bigint, payroll numeric, shipments_receipts numeric,
src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.ecn_norm(
geokey bigint, state_fips text, naicskey bigint, year smallint,
establishments bigint, employees bigint, payroll numeric, shipments_receipts numeric,
src_file text, primary key(geokey,naicskey,year)
);
create table if not exists stg.acs_detailed_raw(
geo_id text, year smallint, table_id text, line_number int,
value numeric, moe numeric, src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.acs_detailed_norm(
geokey bigint, state_fips text, year smallint, table_id text, line_number int,
value numeric, moe numeric, src_file text,
primary key(geokey,year,table_id,line_number)
);
create table if not exists stg.acs_subject_raw (like stg.acs_detailed_raw);
create table if not exists stg.acs_subject_norm(like stg.acs_detailed_norm);
create table if not exists stg.acs_profile_raw(
geo_id text, year smallint, variable text, value numeric, moe numeric,
src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.acs_profile_norm(
geokey bigint, state_fips text, year smallint, variable text,
value numeric, moe numeric, src_file text,
primary key(geokey,year,variable)
);
create table if not exists stg.acs_cprofile_raw(
geo_id text, year smallint, variable text,
current_value numeric, prior_value numeric, change numeric, significance_flag text,
src_file text, loaded_at timestamptz default now()
);
create table if not exists stg.acs_cprofile_norm(
geokey bigint, state_fips text, year smallint, variable text,
current_value numeric, prior_value numeric, change numeric, significance_flag text,
src_file text, primary key(geokey,year,variable)
);