¶ 
data_downloader.py
OUTDATED: please visit api-gateway for up to date documentation and information (July 2026).
¶ 🏗️ System Architecture
The program is built on a metadata-drive architecture. Instead of hardcoding API endpoints or database schemas, the systesm read from a sources.json file. This allows us to add new Census products (or products from other sources) simply by updating the json file.
¶ The 5-Zone UI Layout
The interface is divided into five functional "Focus Zones" to manage the complexity of geographic data:
-
Data Source (Top Bar 1): Horizontal selection of the Census product (e.g.,
ecnbasic). -
Geo Scope (Top Bar 2): Horizontal selection of granularity (
National,State, orCounty). -
Actions (Middle Left): Vertical menu for
Sync,Backup, andRestore. -
State List (Middle Center): Scrollable list of US States with status icons.
-
County Status (Middle Right): Dynamic drill-down showing residency status for counties in the selected state.
¶ ⚙️ The ETL Pipeline
The core logic follows a strict three-step process to ensure data integrity and performance.
¶ 1. Extraction (API Ingestion)
The program utilizes the requests library with a retry strategy to fetch data from the Census API.
url = f"{base_url}?get={variables}&for={location}&key={your_key_here}"
-
Parameters: It dynamically builds the get (variables) and for (geography) strings based on your UI selection.
-
Storage: Raw data is stored as a JSON file.
¶ 2. Transformation (Temporary Preparation)
Before data hits the database, it is "cleaned" into a Tab-Separated Values (TSV) format.
-
Null Handling: Census "None" or empty values are explicitly converted to the string
NULL. -
Normalization: Geographic FIPS codes are padded (e.g.,
1becomes01) to match thereferenceschema.
¶ 3. Loading (Bulk Ingestion)
To handle millions of rows, the program avoids standard INSERT statements, which are slow.
-
COPY Command: It uses the PostgreSQL
COPYcommand to stream the TSV file into a_tmp(temporary) table. -
UPSERT: It then performs an internal database merge:
INSERT INTO production_table SELECT * FROM temporary_table
ON CONFLICT (unique_hash) DO NOTHING;
¶ 🛡️ Data Safety & Versioning
The Backup and Restore system acts as a manual version control for your datasets.
-
Snapshots: The
Backupaction uses a "Shadow Table" approach (table_name_bk). It performs a server-side clone of the production data. -
Restoration: The
Restoreaction drops the production table and reinstates the backup. Crucial: The program manually reconstructs theUNIQUEindex on theunique_hashcolumn after a restore, as cloning data alone does not copy table constraints.
¶ 🕹️ User Controls (Hotkeys)
| Key | Action |
|---|---|
TAB |
Cycle focus between the 5 UI Zones |
Arrows |
Navigate within the active zone (horizontal (left/right) for bars, vertical (up/down) for lists) |
SPACE |
Queue a state or county for batch processing |
C |
Select / deselect ALL counties in the highlighted state |
ENTER |
Execute the highlighted action (sync, backup or restore) |
Q |
Gracefully exit the program |
¶ 📁 File Requirements
-
.env: Must contain DB_NAME, DB_USER, DB_PASS, DB_HOST, and CENSUS_API_KEY. -
sources.json: The metadata configuration file. -
PostgreSQL Tables:
- Raw Schema: The raw schema must contain the tables defined in the JSON, each with a temporary counterpart and a backup version (e.g.,
ecn_2022_standecn_2022_st_tmpandecn_2022_st_bk) - Reference Schema: Tables with FIPS codes for the states and counties.
- Raw Schema: The raw schema must contain the tables defined in the JSON, each with a temporary counterpart and a backup version (e.g.,
¶ Program Summary
- Initialization
- Load environmental variables
- Set up logging file:
data_downloader_county.log
- Global Data Loading
-
Load json file
sources.json
Loads metadata configuration from json; drives entire dynamic ETL logic -
chunk_list(lst, n)
Yield successive n-sized chunks from a list -
load_states_from_db()
Fetches the master list of 50 states (plus territories) from the reference.states_fips table. This populates the central UI panel. -
load_counties_from_db()
Fetches the complete national directory of counties (~3,200 records) from reference.county_fips. This is stored in memory to allow for instant filtering when browsing states.
- Database Operations
-
get_synced_states(geo_meta)
Queries the active production table to see which states already have data. It returns a set of FIPS codes used to turn UI icons Green (the "Green Code" logic). -
get_synced_counties(table, state_fips)
Performs a targeted query for a specific state to identify which of its counties are already in the database. This is used for the "drill-down" status panel. -
backup(stdscr, geo_meta)
Clones the current production table into a shadow table (suffixed with_bk). This uses server-side cloning for near-instant snapshots of millions of rows. -
restore_backup(stdscr, geo_meta)
The "Undo" function. It drops the current production table and replaces it with the backup. It also manually restores the UNIQUE constraints that are lost during table cloning. -
extract_column_label(label_string)
Extracts the column (or cohort) name in the ACS data.
- Decoupled Stage 1: Extract (API Downloads)
-
extract_bls_timeseries(stdscr, source-meta, active_dataset_idx, start_year='2015', end_year="2024")
Parses structural metadata layout tables to dynamically chunk and fetch large Series ID arrays from the BLS API. -
extract_data_acs5(stdscr, s_fips, c_fips, source_meta, geo_meta, s_abbr)
The core engine for ACS (American Community Survey) data. This data is significantly different from the other datasets. -
extract_data_standard(stdscr, s_fips, c_fips, source_meta, geo_meta, s_abbr)
The core engine. It handles:
- Decoupled Stage 2: Upload (Transform and Postgres Bulk Load)
-
upload_bls_timeseries(stdscr, source_meta, active_dataset_idx)
Transforms raw multi-chunk JSON arrays into relational structures and updates the core database tables. -
upload_data_acs5(stdscr, s_abbr, source_meta, geo_meta) -
upload_data_standard(stdscr, s_abbr, source_meta, geo_meta)
- Action Handler
-
confirm_action(stdscr, message)
Displays a modal confirmation window (for backup and restoring backup tables). -
run_action(stdscr, selections, selected_states, selected_counties, geo_meta)
The "Switchboard." It interprets which action is highlighted in the menu and builds the work queue (determining if it should process one county, one state, or a batch of states). Determines which ETL pipeline to run (ACS or main).
- UI Rendering & Feedback
-
draw_ui(stdscr, status, details)
A "blocking" screen overlay. It hides the dashboard and shows a progress bar or status message (e.g., "SYNCING 5/50") during heavy network or database tasks. -
draw_interface(stdscr, focus_idx, sel, scroll, c_scroll, synced_st, selected_st, selected_co, synced_co)
The primary rendering engine. It draws all 5 zones of the dashboard, calculates scroll positions, and applies color pairs based on the sync status of the data. -
show_summary_popup(stdscr, targets_synced, record_count)
Displays a modal box summarizing the ETL results.
- Main Loop
main(stdscr)
The entry point. It initializes the curses environment, manages the focus_idx (which zone is active), and listens for keyboard interrupts to update the program state.
Selection Logic Hierarchy
When you hit ENTER to sync, the program decides what to download based on this priority:
-
County Queue: If any counties were selected via the
Ckey. -
State Queue: If any states were checked via
SPACE. -
Active Highlight: If nothing is checked, it syncs only the state/level currently under the cursor.
¶ Program Diagram
¶ Function Details
¶ chunk_list(lst, n)
Yield successive n-sized chunks from a list
Returns: a n-sized chunks
¶ Details
¶ load_states_from_db()
Indentifies all states in reference schema: reference.states_fips
Returns: a dataframe with columns ['fips_codes', 'state_name', 'state_abbr']
¶ Details
¶ load_counties_from_db()
Identifies all counties from reference schema: reference.county_fips
Returns: a dataframe with columns ['state_fips', 'county_fips', 'county_name'].
¶ Details
¶ get_synced_states(geo_meta)
Identifies states already in database
Returns: a set of 2 digit fips strings of the states present in the database
¶ Details
¶ get_synced_counties(table, state_fips)
Identifies which counties within a specific state are already present in database
Returns: a set of 3-digit county fips strings
¶ Details
¶ backup(stdscr, geo_meta)
Creates a backup of the production table
¶ Details
¶ restore_backup(stdscr, geo_meta)
Replaces production table with data from backup table
¶ Details
¶ extract_column_label(label_string)
Used in the ACS5 ETL pipeline to extract the main column category label
Returns: column_label
¶ Details
¶ extract_bls_timeseries(stdscr, source_meta, active_dataset_idx, start_year="2015", end_year="2024")
Parses structural metadata layout tables to dynamically chunk and fetch large Series ID arrays from the BLS API.
Returns:
¶ Details
¶ extract_data_acs5(stdscr, s_fips, c_fips, sourcemeta, geo_meta, s_abbr)
Processes the data extraction, transformation, and loading processing for the ACS data (since it's different from the other data sets).
Returns: number of rows merged
¶ Details
¶ extract_data_standard(stdscr, s_fips, c_fips, source_meta, geo_meta)
Donwloads standard (census) data.
Returns: number of rows merged into database
¶ Details
¶ upload_bls_timeseries(stdscr, source_meta, active_dataset_idx)
Transforms raw multi-chunk JSON arrays into relational structures and updates the core database tables.
Returns: numer of rows added to database
¶ Details
¶ upload_data_acs5(stdscr, s_abbr, source_meta, geo_meta)
Returns:
¶ Details
¶ upload_data_standard(stdscr, s_abbr, source_meta, geo_meta)
Returns:
¶ Details
¶ confirm_action(stdscr, message)
Displays a confirmation window
Returns: True if 'y' or 'Y' is pressed
¶ Details
¶ run_action(stdscr, selections, selected_states, selected_counties, geo_meta)
Orchestrates user interface actions
Returns: news synced states and counties
¶ Details
¶ draw_ui(stdscr, status, details)
¶ draw_interface(stdscr, focus_idx, sel, scroll, c_scroll, synced_st, selected_st, selected_co, synced_co)
¶ main(stdscr)
Primary control loop initialized by curses.wrapper. Manages keyboard input, zone-based focus, and internal state.
¶ Details
¶ Data Validation Tests
¶ Raw Data Files
All raw data files are saved in the ./raw_data directory.
| Database | Number of files | Size |
|---|---|---|
acs5 |
7801 | 1.4 GB |
cbp |
104 | 1.1 GB |
ecnbasic |
104 | 336 MB |
nes |
104 | 177 MB |
| TOTAL | 2.8 GB |