snowflake-labs TSA Throughput Setup
This subskill cannot be run independently. It must be invoked from the
aviation-installerrouter.This subskill is optional -- only execute if the user wants TSA checkpoint throughput data. No API key is required (public FOIA data).
Creates the TSA checkpoint throughput ingestion pipeline: network rule, EAI, PDF stages, throughput table, Python procedures for PDF download and AI_EXTRACT-based data extraction, and a weekly task DAG (Monday 9am PT).
The pipeline fetches the latest weekly TSA throughput PDF from the TSA FOIA reading room, splits it into single-page PDFs, extracts structured tabular data via AI_EXTRACT, and loads it into the TSA_THROUGHPUT table. Data covers all US airports; the dashboard filters by airport IATA code at query time.
Prerequisites
adsb-ingestioncompleted (provides task DAG root)- Variables from router:
{TARGET_DB},{SCHEMA},{IATA},{WAREHOUSE}
Required Privileges
| Privilege | Scope | Reason |
|---|---|---|
| CREATE NETWORK RULE | Database | Creates rule for tsa.gov |
| CREATE INTEGRATION | Account | Creates EAI for tsa.gov HTTPS access |
| CREATE STAGE | Schema | Creates 2 PDF stages (source + pages) |
| CREATE TABLE | Schema | Creates TSA_THROUGHPUT table |
| CREATE PROCEDURE | Schema | Creates 3 ingestion/extraction procedures |
| CREATE TASK | Schema | Creates 2-task DAG (fetch + extract) |
Configuration
| Parameter | Default | Description |
|---|---|---|
| CRON_SCHEDULE | 0 9 * * 1 America/Los_Angeles |
Weekly Monday 9am PT |
| TASK_PARENT | TASK_INGEST_ADSB |
Parent task for DAG chaining |
| EAI_TSA_GOV | {TARGET_DB}_{SCHEMA}_TSA_GOV_EAI |
External access integration name |
Friction Logging
Report all friction points (errors, warnings, workarounds, race conditions) back to the parent installer using the F1/F2/F3 format from .cortex/skills/logs/README.md. The parent writes the consolidated friction log. If executing standalone, write to .cortex/skills/logs/friction-log_{YYYY-MM-DD}_{HH-MM}.md.
Workflow
Read the
references/subfiles for complete SQL:
references/01-network-stages-tables.md-- Network rule, EAI, stages, tablereferences/02-procedures.md-- All 3 procedures (fetch PDF, find latest, process/extract)references/03-tasks.md-- Task definitions + initial fetch/extract callsExecute ALL SQL from each file in order. Do NOT skip or optimize away any queries.
Step 1: Set Query Tag
ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-tsa-throughput","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';
Step 2: Create Network Rule and EAI
Create network rule for tsa.gov:443 (HTTPS) and EAI {EAI_TSA_GOV}. No secret needed -- the FOIA reading room is public.
See references/01-network-stages-tables.md for full SQL.
Step 3: Create Stages and Table
TSA_PDF_STAGE-- stores downloaded weekly PDF (SNOWFLAKE_SSE encryption, directory enabled)TSA_PDF_PAGES_STAGE-- scratch space for 1-page PDF splits (cleaned after extraction)TSA_THROUGHPUT-- destination table for extracted checkpoint data
See references/01-network-stages-tables.md for full SQL.
Step 4: Create Procedures
PROC_FETCH_PDF_TO_STAGE(url, stage_path)-- generic PDF download utilityPROC_FETCH_LATEST_TSA_PDF(stage_path)-- scrapes FOIA page, finds latest throughput PDF, deduplicatesPROC_PROCESS_TSA_PDF(pages_stage, target_table)-- splits PDF, AI_EXTRACT on each page, bulk insert, cleanup
See references/02-procedures.md for full SQL.
Step 5: Create Tasks
TASK_FETCH_TSA_PDF-- root TSA task, weekly CRON, callsPROC_FETCH_LATEST_TSA_PDFTASK_EXTRACT_TSA_PDF-- child task (AFTER fetch), callsPROC_PROCESS_TSA_PDF
See references/03-tasks.md for full SQL.
Note: Tasks are created SUSPENDED. The router resumes them after all tasks are ready.
Step 6: Trigger Initial Fetch and Extract
CALL {TARGET_DB}.{SCHEMA}.PROC_FETCH_LATEST_TSA_PDF('@{TARGET_DB}.{SCHEMA}.TSA_PDF_STAGE');
CALL {TARGET_DB}.{SCHEMA}.PROC_PROCESS_TSA_PDF('@{TARGET_DB}.{SCHEMA}.TSA_PDF_PAGES_STAGE', '{TARGET_DB}.{SCHEMA}.TSA_THROUGHPUT');
Step 7: Verify
SELECT COUNT(*) AS TSA_ROWS FROM {TARGET_DB}.{SCHEMA}.TSA_THROUGHPUT;
SELECT COUNT(DISTINCT airport_code) AS AIRPORTS FROM {TARGET_DB}.{SCHEMA}.TSA_THROUGHPUT;
SELECT COUNT(*) AS LOCAL_ROWS FROM {TARGET_DB}.{SCHEMA}.TSA_THROUGHPUT WHERE UPPER(airport_code) = '{IATA}';
Expected: > 0 rows overall; local rows depend on whether the airport appears in TSA data.
Stopping Points
- After Step 2: Confirm EAI exists (
SHOW INTEGRATIONS LIKE '%TSA_GOV%') - After Step 6: Verify TSA_THROUGHPUT has rows
Troubleshooting
| Error | Cause | Fix |
|---|---|---|
| EAI creation fails | Insufficient privileges | ACCOUNTADMIN required for CREATE INTEGRATION |
| PROC_FETCH fails with connection error | Network rule misconfigured | Check network rule allows tsa.gov:443 (HTTPS) |
| No PDF found on FOIA page | Page structure changed | TSA may have changed page structure; check https://www.tsa.gov/foia/readingroom/ manually |
| AI_EXTRACT returns empty results | Wrong encryption type | Ensure stages use SNOWFLAKE_SSE encryption (required for AI_EXTRACT) |
| 0 rows for airport | Airport not in TSA data | Not all airports appear in TSA data; major US airports should have data |
Cleanup
All objects carry the sf_sit-is-aviation COMMENT tracking tag and are auto-discovered by the aviation-cleanup skill. EAIs are matched by name pattern %TSA_GOV_EAI.
Return to Router
After completing all steps, return to the aviation-installer router and continue with derived-analytics.