About Fed Capture Dashboard

Pulls federal contract opportunities from SAM.gov each week, classifies each one by the kind of work it is, and turns them into a filterable Tableau dashboard of bids worth chasing.

What This Is

Fed Capture Dashboard watches the firehose of new U.S. federal contract opportunities and narrows it down to the ones you could actually win. Every run pulls every opportunity posted on SAM.gov in the last seven days (no keyword guessing, the whole window), then labels each one: what industry it sits in (NAICS), what the government is actually buying (PSC), where it is in its lifecycle (pre-bid, open bid window, already awarded), and which set-aside it is restricted to (8(a), HUBZone, SDVOSB, women-owned, small business, or open competition).

On top of those labels it derives a "work path" for each notice, so you can filter to software and IT, studies and analysis, or consulting work and screen out construction, physical services, and product buys you do not chase. It even flags a personal target zone: bid-ready, open-competition-or-qualifying intellectual work, minus the credentialed-engineering and specialized-research lanes. The output is a flat table where a single click in Tableau answers "what is open this week that I should bid on?"

Architecture at a Glance

managed by dbt 1. GET /opportunities (paginated) 2. MERGE upsert by notice_id 3. dbt reads RAW (parses JSON) 3a. seed dims 4. build MART_OPPORTUNITIES 5. SELECT * (reader role) 6. write mart_opportunities.csv 7. Tableau loads the dashboard SAM.gov API Opportunities v2 (public) ingest/sam.py pull + dedup by noticeId Snowflake RAW loaded by ingest; dbt source stg_sam_opportunities staging view CSV Seeds NAICS + PSC taxonomies MART_OPPORTUNITIES mart table (+ dim joins) export/mart_to_csv.py read-only export mart_opportunities.csv flat file in exports/ Tableau Public interactive dashboard
Compute Storage External dbt scope

The dashed box marks the layers dbt builds and manages (the staging view and the mart table); RAW is loaded by the ingest script and read by dbt as a source. Single-headed arrows are one-way (load, build, write); double-headed are request/response (API pull or SQL query). Numbers match the steps below.

What Happens When You Run the Pipeline

  1. ingest/sam.py calls the SAM.gov Opportunities API for everything posted in the trailing seven-day window, paging 1,000 records at a time and backing off on rate limits (HTTP 429) with exponential retry. It pulls every opportunity in the window regardless of NAICS code.
  2. The loader flattens each opportunity, deduplicates by noticeId, stages the batch, and runs a MERGE into RAW.SAM_OPPORTUNITIES. The merge keys on notice ID, so re-running the same window updates rows in place instead of duplicating them; the load is idempotent. The full original JSON is kept in a VARIANT column for fields the staging model has not pulled out yet.
  3. dbt reads RAW and builds the staging view stg_sam_opportunities, parsing the JSON to pull out solicitation number, PSC code, agency path, place of performance, links, and attachment flags. (Step 3a) it also loads the CSV Seeds into the dim_naics and dim_psc dimension tables, which carry the NAICS sector names and PSC service-or-product categories.
  4. dbt joins the staged opportunities to those dimensions and builds the MART_OPPORTUNITIES table. This is where the analytical work happens: deadline urgency buckets, lifecycle stage, set-aside category, a split of the agency path into department and office, the boolean work-path flags, the primary work path, and the is_bid_ready and is_personal_target_zone indicators.
  5. export/mart_to_csv.py connects with a read-only reader role and runs SELECT * against the finished mart.
  6. It writes the result to exports/mart_opportunities.csv as a flat UTF-8 file, logging the row count and size.
  7. Tableau Public opens that CSV as a file-based data source and builds the interactive dashboard. Its views include a list of active opportunities, an opportunities-by-state map, and a weeks-until-deadline view, alongside a raw data table. A filter panel drives all of them: work path, set-aside category, PSC type, bid-ready, the personal target zone, deadline urgency, days until deadline, and US-only location. Because Tableau Public cannot connect to Snowflake directly, the CSV is the hand-off; refreshing the dashboard means re-running the export and re-uploading the file.

Where the Computation Happens

The Python scripts stay deliberately thin. ingest/sam.py does pagination, retry, dedup, and a single staged MERGE; export/mart_to_csv.py does one query and one file write. Neither script transforms or classifies the data.

All of the analytical logic lives in SQL inside Snowflake, expressed as dbt models. dbt does not load RAW; the ingest script owns that table, and dbt reads it as a source, then builds and manages everything downstream. The staging layer is materialized as views (cheap, always-fresh reads over RAW), while the marts and dimensions are materialized as tables so Tableau reads a fully precomputed result. The work-path flags, set-aside buckets, and target-zone logic are CASE expressions in mart_opportunities.sql, with the PSC and NAICS category mappings kept resilient to gaps in the seed dimensions by falling back to code-prefix derivation.

Tech Stack

Layer Tools
Language & runtime Python 3.13, managed with uv
Ingestion requests (SAM.gov API), pandas, python-dotenv
Warehouse Snowflake (RAW and MARTS schemas; VARIANT for raw JSON)
Transformation dbt (dbt-core, dbt-snowflake), SQL models, seeds, snapshots
Reference data CSV seeds for NAICS (Census) and PSC (acquisition.gov) taxonomies
Export snowflake-connector-python[pandas] via a read-only reader role
Visualization Tableau Public, reading the exported CSV as a file-based source

Source Code

The whole project lives in one public repository (branch main):
github.com/JackVance/fed-capture-dashboard