U.S. Visa Bulletin data engineering

Objective 1 · Deliverable 01

Building the database

The first deliverable is already built: the multiseries panel $y_{p, c, b, t}$ of the U.S. Visa Bulletin —extracted, annotated, consolidated, and audited end to end— from the public repository VisaPredictAI. This section documents in detail how it was built and what was found along the way.

194

Country×category×table series

27,611

Monthly observations

296

Bulletins · Dec-2001 → 2026

21

Canonical categories

PASS

Mega-audit verdict

01 The multiseries panel $y_{p, c, b, t}$

Each panel cell is the priority date of a combination (country $p$ , category $c$ , table $b$ ) in month $t$ , transformed into days since a base date ( $t_{0} =$ January 1, 1975, earlier than the oldest observed priority date: November 1979 in Philippines F4). Each observation also retains its administrative status —Current (C), Final (F, specific date), or Unavailable (U)— as a descriptive annotation. The predictive target is trained exclusively on observations with status F.

5 countries: MX · IN · CN · PH · All Charg. 16 employment categories · 5 family 2 tables: FAD & DFF (separate) 15,931 trainable obs (status F · 58 %)

Employment · FAD

11,548

since Dec-2001

Employment · DFF

5,698

since Oct-2015

Family · FAD

7,115

since Dec-2001

Family · DFF

3,250

since Oct-2015

02 Data pipeline architecture

From raw HTML to an audited panel in six reproducible stages, run by a GitHub Action whenever a new bulletin is published.

t_{0}

= 1975 5. Panel y(p,c,b,t) 194 series 27,611 obs open-license CSV 6. Audit 12 dimensions 0 critical PASS verdict Run whenever each bulletin is published · raw data generated only by script, never hand-edited.

03 The five gaps resolved

The inherited base covered only four employment categories, one table, and lost the status information. Each gap was closed with a verified fix (audit before → after).

H1 · Status

C/F/U annotation preserved

The code flattened Current→bulletin date and Unavailable→empty, erasing the regime. Now a status column preserves it and the model trains only on F.

before: status lostnow: C/F/U/UNK

H2 · DFF table

Employment Dates for Filing

The scraper stopped after the first table (FAD only). Now it captures both employment-based tables labeled by type; DFF available since Oct-2015.

before: FAD onlynow: FAD + DFF

H3 · Categories

EB-5 and subcategories

The filter discarded everything except EB-1 through EB-4. A classifier normalizes 20 years of label drift into 16 canonical codes (EB-5, Other Workers, set-asides…).

before: 4 categoriesnow: 16 + 5 family

H4 · Coverage

Extension to the source floor

Robust column detection (category = col 0; country by normalized name) recovered 2001–2003 and repaired the residual series (All Charg. was truncated to 2016).

before: since Oct-2003now: since Dec-2001

H5 · UNK

Empty value disambiguated

The old «empty» mixed Unavailable with unparsed cells. The status now distinguishes U from UNK (a sentinel chosen so it does not collide with pandas's coercion to NaN): in the current panel, a single UNK cell.

before: ambiguous emptynow: U ≠ UNK

Bonus · Robustness

No silent losses

The audit detected one month (Dec-2007) lost to a transient HTTP failure. A retry with backoff and explicit reporting was added: no month is dropped silently.

before: silent failurenow: retry + alert

04 Category taxonomy and the EB-5 odyssey

Normalizing the categories required absorbing 68 distinct label variants (line breaks, hard spaces, typos) accumulated over two decades. The extreme case is the fifth employment preference (EB-5), whose structure changed regime three times: the system captures each era under a stable canonical code.

05 Deep search for missing bulletins

Every expected month was enumerated and probed against both the constructed URL and the Wayback Machine. Bulletin-level coverage is 296 of 296 months (100 %). The five bulletins withdrawn from travel.state.gov (March, September, October, and November 2009; October 2012) were recovered by hand from the historical archive, frozen alongside the rest of the snapshots, and are part of the panel.

296 / 296 months present 5 bulletins recovered from the archive 0 unexplained gaps

🗓️ Honest temporal scope

The Final Action Dates have been published since 1992 [14], but the structurally homogeneous monthly series that the module consolidates from the bulletin HTML spans continuously from December 2001 – 2026 (296 complete months per series). The 1992–2001 period exists in statistical and archival sources, reserved for later integration. The Dates for Filing exist only since October 2015 (130 observations).

06 Quality mega-audit (12 dimensions)

The panel undergoes an exhaustive audit: schema, completeness, series inventory, status distribution, gaps, key uniqueness, date validity, FAD vs DFF coherence, jump anomalies, source↔panel reconciliation, coverage matrix, and trainability. Verdict: PASS — zero critical findings, zero warnings.

Administrative status distribution

Two informative findings (real phenomena, not errors)

Informative · 1

6 inversions DFF < FAD

The Department of State occasionally publishes filing dates a few days behind the final action. The raw values parse correctly: they are real data, not extraction failures.

Informative · 2

14 strong month-to-month jumps

Real retrogressions and advances —the December 2022 EB-4 transition and the Mexico F1/F3 backlogs of the 1980s—. The predictive system must tolerate retrogressions as a legitimate phenomenon.

Pipeline engineering

MLOps practices

The database is not a loose script: it is a reproducible, tested, and self-protecting engineering system that runs in an automated way. This section documents each MLOps practice in production in the VisaPredictAI repository.

139

Automated tests

2

CI workflows

≥65 %

Coverage floor (CI gate)

ruff+mypy

Lint · format · types

0

Critical audit findings

01 Deterministic reproducibility

The development environment and the continuous integration environment are identical: same versions of Python and of each dependency, fixed with exact pins. Anyone reproduces the same data byte by byte.

exactly pinned dependencies (`pandas==3.0.0`…) Python 3.14 · dev = CI `.python-version` + `requirements.txt` + `pyproject.toml` pipeline output verified byte-identical

02 Automated tests at four levels

The repository's test suite runs under pytest with an executable coverage floor (`fail_under=65`): coverage cannot erode without breaking CI (the exact count lives in CI, not in this prose).

Level 1 · 12 cases

Pure functions

State classifiers (C/F/U/UNK) and category classifiers (20 years of label drift, substring disambiguation). No I/O, deterministic.

Level 2 · 10 cases

Offline extraction

The actual parsing runs over 4 saved HTML fixtures (format eras 2002/2007/2020/2022) with no network: fast, deterministic, covers the logic where the bugs live.

Level 3 · 12 invariants

Panel contract

Hard product invariants: unique key, 0 negative days, state domain, priority date only on `F`, month completeness (a month dropped by the network fails the gate and triggers the alert). Any regression blocks the commit.

Level 4 · 31 cases

Warehouse contract

The DuckDB star schema is loaded under live PK/FK/CHECK constraints: they verify that the panel is reconstructed without loss (v_panel_long), that each fact respects the contract, and that the views and marts reconcile.

03 Continuous integration: two pipelines

One pipeline validates the code on every change; the other refreshes the data when a new bulletin arrives, with a quality gate before publishing.

04 Robustness against the real world

The source is 20 years of live HTML with changing formats and transient failures. The pipeline is hardened against every observed failure mode.

Network

Retry + backoff

A transient HTTP failure (5xx, timeout) retries with increasing wait; a permanent 4xx is no longer retried. A month is no longer lost silently (previously an `except: pass` swallowed them).

Reproducibility

Deterministic ordering

Rows are sorted by their full key, not by an unstable sort: a dropped month no longer cascades a reorder across the rest of the file.

Integrity

Abort on mass failure

If >10 bulletins fail (source redesign/outage), the scraper aborts without writing: a degraded panel is never published.

Observability

Alerts on failure

A red gate or a crash opens (or comments on) a tracking issue: the failure speaks up, it does not die silently in the Actions tab.

Correctness

`UNK` sentinel

The «no data» state uses `UNK`, not `NA`: the string `"NA"` is coerced by pandas to NaN on read and erased the annotation. `UNK` is safe for any consumer.

Detection

Caught by the tests

The sentinel bug and a month lost to the network were caught by the tests and the failure report, not by a user: the safety net working.

05 Data contract, lineage, and versioning

Contract & lineage

The data validates itself

Hard panel invariants as executable tests (the schema is the contract).
Cell-level lineage: `raw_value` preserves the original cell exactly as published.
Programmatic audit of 12 dimensions with a PASS verdict (0 critical findings).
Pre-commit hooks: large-file blocking, end-of-line, YAML/TOML, ruff.

Versioning

Each artifact where it belongs

The open CSVs live in git: directly downloadable, with no DVC or credentials (they are the deliverable).
The figures are not versioned (regenerable artifacts) so as not to bloat the history.
DVC initialized and reserved for next semester's model artifacts (checkpoints, forecasts).
`Makefile`: `make install · scrape · panel · test · check · all` (single-command operation).

Repository structure

Structure adapted from Cookiecutter Data Science

The repository does not organize its files at random: it follows its own structural contract adapted from Cookiecutter Data Science (CCDS v2, a template that itself encourages adaptation), with deliberate, documented deviations: two packages with layered import direction instead of a single src/, zero notebooks (everything is a reproducible script), and model binaries via DVC rather than git. Anyone who knows CCDS finds each file where they expect it —raw data kept apart from derived data, reports and documentation in their place, and an installable package with a single source of truth for the dependencies— in the VisaPredictAI repository.

01 The tree, at a glance

The CCDS convention separates by role, not by file type: the raw data (immutable source) is never mixed with the derived data (regenerable), and the code lives apart from the reports and documentation.

VisaPredictAI/
├── data/
│   ├── raw/          # 10 CSVs per country, freshly scraped (source)
│   └── processed/    # visa_panel_long.csv — the derived panel
├── reports/          # role-structured artifacts: campaign · eval · prospective · governance
├── docs/             # documentation (DVC.md…)
├── tests/            # pytest + offline HTML fixtures
├── vp_data/          # core package: config · visa_common · tracking
├── pipeline/         # data DAG: freeze → scrape_* (employment · family · DV) → build_panel → build_database → mega_audit
├── pyproject.toml    # deps + tooling + package (single source)
├── Makefile          # single-command operation
├── LICENSE           # MIT
└── .github/workflows/ # code CI + data cron

02 What each piece does

data/raw/

Immutable source

The 10 CSVs per country exactly as they come out of the scrapers. Never edited by hand: if something changes, the scraper is fixed, not the data.

data/processed/

Regenerable derived data

The multiseries panel $y_{p, c, b, t}$ produced by pipeline/build_panel.py. Keeping it apart from the raw data makes clear what is source and what is result.

reports/ · docs/

Reports and docs

The quality audits (12 dimensions) are written to reports/; the operational documentation (e.g. the DVC strategy) lives in docs/.

tests/

Executable contract

pytest suite with HTML fixtures for offline extraction and hard panel invariants: the schema is the contract.

vp_data/ · pipeline/

Shared core

The vp_data/ package centralizes paths, constants and scraping helpers (single source); pipeline/ chains the data-DAG steps, each invocable as python -m pipeline.<module>.

pyproject.toml

A single source of truth

Dependencies (runtime and dev), ruff/mypy/pytest configuration, and packaging, all in one file: the package is installable with pip install -e ..

03 How it is used day to day

The structure enables a single-command, reproducible flow: the data flows from raw/ to processed/, and the whole toolchain installs from a single source.

git clone https://github.com/UACJ-MIAAD/VisaPredictAI.git
cd VisaPredictAI
make install   # = pip install -e ".[dev]"  (runtime + tools)
make scrape    # writes data/raw/*.csv
make panel     # consolidates data/processed/visa_panel_long.csv
make check     # ruff + mypy + pytest (coverage gate)
make audit     # audits → reports/

04 Honest adaptations

CCDS is a starting point, not a dogma. What serves a data acquisition project is adopted, and what does not yet apply is deliberately left out —no filler structure.

MIT license in a public repo installable package + flat layout (`pip install -e .`) no `notebooks/` or `models/` yet — modeling is next semester's work (DVC already reserved) no `.env` — the scraping is of public data, no secrets `data/raw` and `data/processed` versioned in git (they are the open deliverable)

Data engineering · dimensional modeling

Star-schema data warehouse

The flat panel is promoted to a normalized dimensional warehouse in DuckDB: central facts by grain, conformed dimensions around them, and the contract invariants raised to declarative constraints (PK/FK/CHECK) that reject any invalid row at load time. It covers every category of the Visa Bulletin —family, employment and Diversity Visa— with lineage, governance and marts ready for modeling. The definition lives in `schema.sql`; the model is rebuilt with `make db`.

7

Dimensions

2

Facts · dates + DV rank

PK·FK·CHECK

The schema is the contract

139

Automated tests

DuckDB

Embedded · SQL · Parquet

Star schema · facts in gold, dimensions in blue, conformed dimensions highlighted, gold marts in green, governance in gray.

00 Why DuckDB: selection criteria

The engine choice follows the usage profile, not the trend. Access to the database is analytical and read-only (scans, aggregations and star joins over ~27 000 rows), it is regenerated from a versioned CSV, and it must run on any machine right after cloning: no server, no cloud and no cost. Against that profile, six criteria were set.

Embedded

Zero server

It runs inside the process (Python/CLI), with no daemon to install nor port to open. Cloning and make db is enough: full dev↔CI reproducibility.

Analytical

Columnar and vectorized

A column-oriented OLAP engine: the panel's scans and aggregations are its natural use case, not the row by row transactional one.

Local and free

No cloud, no account

An explicit NO-AWS decision: no credentials, billing or network. The academic deliverable reproduces without barriers.

SQL + contract

Native PK/FK/CHECK

Standard SQL with declarative constraints that reject any invalid row at load time: the schema is the data contract.

Interoperable

Parquet and pandas

It reads and writes Parquet and DataFrames natively; the bridge to modeling in Python is direct, with no intermediate ETL.

One file

Regenerable

The database is a single derived .duckdb file (gitignored); if it is deleted or corrupted, make db rebuilds it in seconds.

State of the art: comparison framework

In the 2024–2025 landscape, DuckDB is the reference for an embedded analytical database (often described as «the SQLite of OLAP»). Compared with the alternatives considered for this component:

Option	Model	Server	Verdict	Reason
DuckDB	Columnar OLAP, embedded	No	Chosen	Analytical, serverless, local and free, SQL with constraints, native Parquet
SQLite	Row-based OLTP, embedded	No	Partial	Embedded and free, but row-oriented: slow on scans and aggregations; no columnar nor native Parquet
PostgreSQL	Row-based OLTP, client–server	Yes	No	Excellent for concurrent apps, but it requires a running server — it breaks the «clone and run»
Parquet + pandas	File + memory	No	Partial	Fast and columnar, but with no SQL engine nor relational integrity (no PK/FK/CHECK = no contract)
BigQuery / Snowflake / Redshift	Cloud OLAP	Managed	No	Powerful at large scale, but paid, with account and network — incompatible with NO-AWS reproducibility

🦆 Decision synthesis

For an analytical, small and read-only panel that must reproduce for free and without a server, DuckDB combines the embedded nature of SQLite with the columnar performance of a data warehouse and the integrity guarantees of SQL. The typed Parquet it exports leaves the door open to scaling to the cloud in the future without rewriting the model.

01 Catalog of tables, fields and keys

The warehouse has 12 tables — 7 dimensions, 2 facts and 3 governance and provenance — plus 6 views/marts. Each column carries its key: PK primary · FK foreign · UK unique. Types in parentheses.

Dimensions · 7

dimension

dim_area

area_id PK (int)
slug UK · name (str)
is_residual_group (bool)

dimension

dim_category

category_id PK
block · code UK(block,code)
parent_code · preference_level
is_subcategory · ina_basis

dimension

dim_date

date_id PK
bulletin_date UK (date)
year · month · quarter
us_fiscal_year (int)

dimension

dim_status

status PK (C/F/U/UNK)
label · description
is_predictable (bool · F)

dimension

dim_table

table_id PK
code UK (FAD/DFF)
name (str)

dimension

dim_region

region_id PK
slug UK · name
6 DV regions

dim · lineage

dim_category_alias

alias_id PK
category_id FK→dim_category
raw_label UK(cat,label)
valid_from·valid_to·n_months

Facts · 2

fact · dates

fact_priority

PK composite: area_id·category_id·table_id·date_id (all 4 FK)
status FK→dim_status
priority_date (date · F only)
days_since_base = $y$ (int · F only) · raw_value
CHECK: status domain · days ≥ 0 · defined only if F

fact · DV rank

fact_dv_rank

PK composite: region_id·date_id (FK)
status FK→dim_status
rank_cutoff (int · F only)
raw_value · exceptions (by country)
CHECK: rank ≥ 0 · defined only if F

Governance · 2

governance

etl_run

run_id PK · built_at_utc (ts)
schema_version · n_fact_priority · n_fact_dv
n_trainable_f · pct_trainable (quality)
panel_floor · panel_ceiling (date)

governance

schema_version

version (int)
description (str)
detects schema drift / evolution

Views and marts · 6: v_panel_long and v_dv_long reconstruct the panel/DV losslessly; v_category_alias (lineage), v_trainable_by_preference (roll-up by preference), and the modeling marts mart_training_F and mart_series_summary.

02 Every category, including the Diversity Visa

Family (F1–F4) and employment (EB-1 through EB-5 with 16 canonical subcodes) live in the dates fact fact_priority ( $y_{p, c, b, t}$ ). The Diversity Visa publishes a rank number by region, not a date — so it does not fit in the panel and gets its own fact fact_dv_rank (6 regions × month) and its dimension dim_region.

family + employment + Diversity Visa DV = regional rank (separate fact) Schedule A excluded with evidence (not a category with a date)

03 Hierarchy, conformed dimensions and lineage

Hierarchy

Roll-ups by preference

dim_category carries parent_code, preference_level and INA statutory basis: subcategories fold into their preference (all EB-5 under EB-5) via the view v_trainable_by_preference.

Conformed

Shared dimensions

dim_date and dim_status are conformed: both facts share them, so time and the C/F/U/UNK regime are defined once and join the same way across the whole warehouse.

Lineage

20 years of drift, auditable

dim_category_alias moves label normalization out of code and into data: each published spelling → canonical category with its month window (48 aliases; EB5_TEA had 7 spellings 2001–2015).

04 Governance, quality and modeling marts

Contract

The schema rejects the invalid

Composite PK, FK to each dimension and CHECK (status domain, days ≥ 0, variable defined only if status='F'): the contract lives in the schema, not only in the tests.

Provenance

Load audit + quality

etl_run records each build (schema version, counts, trainability score, temporal floor/ceiling); schema_version marks the evolution. Medallion architecture raw→panel→gold.

Gold marts

Ready for modeling

mart_training_F = clean trainable set (only F with the dependent variable and time features); mart_series_summary summarizes each series to filter the evaluable ones.

05 Declared limits (adversarial audit)

An independent audit re-derived the model from scratch. It passed strongly —0 orphan foreign keys, 48/48 aliases re-classify to their canonical form, the constraints reject every invalid row, reproducible content— and left these limits unvarnished:

DV coverage

2002–2003 partial

The DV floor reaches 2001-12, but ~20 months of 2002–2003 publish the Diversity Visa in non-tabular HTML, not yet parsed (future work). The advance notification (future month) is also out of scope.

No silent degradation

DV coverage gate

Those gaps are no longer silent: test_dv_coverage_floor (in the data gate) requires floors of rows, months and complete months, and aborts the commit if a scrape degrades coverage.

Building the database

01 The multiseries panel yp,c,b,t​

02 Data pipeline architecture

03 The five gaps resolved

C/F/U annotation preserved

Employment Dates for Filing

EB-5 and subcategories

Extension to the source floor

Empty value disambiguated

No silent losses

04 Category taxonomy and the EB-5 odyssey

05 Deep search for missing bulletins

06 Quality mega-audit (12 dimensions)

Administrative status distribution

Two informative findings (real phenomena, not errors)

6 inversions DFF < FAD

14 strong month-to-month jumps

MLOps practices

01 Deterministic reproducibility

02 Automated tests at four levels

Pure functions

Offline extraction

Panel contract

Warehouse contract

03 Continuous integration: two pipelines

04 Robustness against the real world

Retry + backoff

Deterministic ordering

Abort on mass failure

Alerts on failure

`UNK` sentinel

Caught by the tests

05 Data contract, lineage, and versioning

The data validates itself

Each artifact where it belongs

Structure adapted from Cookiecutter Data Science

01 The tree, at a glance

02 What each piece does

Immutable source

Regenerable derived data

Reports and docs

Executable contract

Shared core

A single source of truth

03 How it is used day to day

04 Honest adaptations

Star-schema data warehouse

00 Why DuckDB: selection criteria

Zero server

Columnar and vectorized

No cloud, no account

Native PK/FK/CHECK

Parquet and pandas

Regenerable

State of the art: comparison framework

01 Catalog of tables, fields and keys

Dimensions · 7

dim_area

dim_category

dim_date

dim_status

dim_table

dim_region

dim_category_alias

Facts · 2

fact_priority

fact_dv_rank

Governance · 2

etl_run

schema_version

02 Every category, including the Diversity Visa

03 Hierarchy, conformed dimensions and lineage

Roll-ups by preference

Shared dimensions

20 years of drift, auditable

04 Governance, quality and modeling marts

The schema rejects the invalid

Load audit + quality

Ready for modeling

05 Declared limits (adversarial audit)

01 The multiseries panel $y_{p, c, b, t}$