SQL Canonical Dataset Build (USAspending L24M + DSBS/SAM UEI Joins)

Job Description
We need a disciplined SQL/data engineer to produce a deterministic, re-runnable script-based build (lean MVP, not enterprise infra).

Goal

Create a single canonical, one-row-per-company dataset anchored on UEI, then enrich it using exact UEI matches (no name guessing).

Inputs (provided)

USAspending extract / source tables (L24M window)

DSBS export (CSV) with UEI + owner contact fields

SAM Active Entity extract (CSV) with UEI + phone + officer names

Written Segment B & C rules (explicit)

Deliverables

1. USAspending segmentation + canonical vendor table

>Filter to last 24 months
>Vendor-level aggregation → one row per UEI
>Apply Segment B & C logic exactly as written
>Deterministic CSV output
>Basic QA checks:

a. no duplicate UEIs in output
b. obligation reconciliation tie-out (aggregate totals match expected)

2. UEI-based enrichment + final CSV

>Left join canonical list → DSBS export on UEI (exact match only)
>Left join canonical list → SAM extract on UEI (exact match only)
>Compute dial_phone priority field (define precedence clearly; we will confirm the rule)
>No fuzzy matching, no name-based joins, no inference
>No companies dropped: all UEIs from canonical list must remain in final output (nulls allowed if no match)
>Final enriched CSV output

3.Exceptions / Integrity notes

Any rows missing UEI or breaking determinism must be placed into an exceptions table + flagged, not guessed.

Non-Negotiables

1. UEI is the identity anchor.
2. Do not dedupe by name.
3. If rules conflict or data is ambiguous: stop and flag before proceeding.
4. Follow SOP exactly.

Working Style

We want someone who:

>thinks in SQL terms (joins, GROUP BY, keys, aggregation)
>communicates clearly when data conflicts exist
>prefers correctness over speed

Tools

You can use:

a) SQL (Postgres/MySQL/BigQuery style acceptable)
b) Or SQL + Excel/Sheets only for validation
Output must be CSV.

Budget

Fixed USD 80 total, paid as 3 milestones:

Milestone 1: Stage/load + USAspending L24M filter (deliver staging SQL + intermediate output)
Milestone 2: Canonical vendor aggregation + Segment B/C logic + QA tie-outs
Milestone 3: DSBS + SAM UEI joins + dial_phone + final enriched CSV

How to Apply (Required)

Reply with:

1. Your SQL dialect experience
2. One example of a deterministic aggregation + join project (brief)
3. Answer this scenario (2–3 lines):
“After aggregation, 5–10% transaction rows are missing UEI. Names exist but inconsistent. You are told not to infer by name. What do you do and why?”

Applicants who propose fuzzy matching or name dedupe will be rejected.