🗃️

Capstone — LienSight: UCC Lien Risk Data Pipeline Build a three-stage Medallion pipeline that turns messy public lien filings into a lender-ready risk dataset — with Gemini Code Assist generating, testing, and extending every stage under your review. Prerequisite: modules G00–G08.

Capstone: LienSight

A commercial lender asks one question before lending against a business's equipment or inventory: does someone else already have a claim on it? The answer lives in public lien filings — and the data is famously filthy. Your job: make it answerable.

Domain Briefing: UCC Filings

The Domain, Plainly

When a bank lends money against a company's assets, it files a UCC-1 financing statementA public notice filed with a state's Secretary of State declaring a creditor's security interest (lien) in a debtor's assets, under Article 9 of the Uniform Commercial Code. with the state — a public "dibs" notice on those assets. Before any new lender extends credit, they search these filings. The pain: filings are entered by humans at dozens of state offices. The same company appears as "ACME Industrial Supply LLC", "Acme Industrial Supply, L.L.C.", and "ACME INDUST SUPPLY". Amendments (UCC-3A follow-up filing that amends, continues, assigns, or terminates an existing UCC-1 — identified by referencing the original filing number.) terminate or continue earlier filings but only reference them by filing number. Filings silently lapseA UCC-1 is effective for 5 years from its file date. Unless a continuation (UCC-3) is filed, it lapses and the lien is no longer perfected. after 5 years. A lender who misreads this data either loses collateral priority (real money) or wrongly denies a good loan (real customers). Companies like Moody's, Dun & Bradstreet, and a dozen fintech startups run exactly the pipeline you're about to build.

Why This Is a Real Data Engineering Test

This project hits every classic DE problem in miniature: ingestion of inconsistent source files, data quality rules, entity resolution (the name-variant problem), slowly changing state (amendments and lapses), and serving aggregates for a downstream consumer. If you can explain your silver-layer decisions in a job interview, this capstone did its job.

What You Build

The Spec

liensight — a Python pipeline (pandas + SQLite, no cloud required) with a CLI runner:

python pipeline.py run --stage bronze|silver|gold|all — executes stages idempotently (re-running a stage never duplicates data)
python pipeline.py report — prints a data-quality report (rows in/out per stage, rejects with reasons)
A pytest suite covering every transformation rule
GEMINI.md + .aiexclude from day zero; one feature shipped via agent mode; the data-quality report generated headlessly via Gemini CLI

BRONZE data/bronze/filings.db Raw CSVs loaded as-is + load metadata (source file, loaded_at). Never edited — the replayable source of truth.

SILVER cleaned_filings, entities Typed, deduplicated, validated rows; debtor names normalized; name variants resolved to entity_ids; rejects quarantined with reasons.

GOLD entity_risk, lapse_alerts Per-entity aggregates: active lien count, secured parties, earliest expiry, risk score, and filings lapsing within 90 days.

Executing This Capstone in VS Code / PyCharm

Everything in this project is built through Gemini Code Assist inside your IDE — never by copying finished code from elsewhere. Each phase uses some of these five moves; every paste-block below names its move in the header.

Before you start (5-point checklist)

Create the project folder and open it: VS Code File → Open Folder (PyCharm: File → Open). New files via the Explorer panel / Project tool window.
Confirm Gemini Code Assist is signed in: the ✦ icon in VS Code's Activity Bar opens the panel with no error badge (PyCharm: the Gemini tool window on the right edge greets you by account). Setup is module G01.
Open the integrated terminal for git, Python, and test runs: Ctrl+` in VS Code (PyCharm: Alt+F12). All git/pytest/python commands in this page run there.
After creating GEMINI.md in Phase 1, start a new chat so the rules load into context (the G05 lesson).
Chat and smart commands act on the active file or selection — keep the file you're working on focused, and select code before asking about it (the G03 gotcha).

Move	VS Code	PyCharm / IntelliJ
1 · Spec-comment	Type the comment in the editor and pause — ghost text appears. `Tab` accepts, `Ctrl+→` accepts word-by-word, `Esc` rejects (G02)	Same: type, pause, `Tab`; hover the suggestion to cycle alternatives
2 · Chat / smart cmd	Click the ✦ icon in the Activity Bar → chat panel opens. Select code first to scope it; `/explain /fix /doc /simplify` work here (G03)	Gemini tool window (right edge) → Chat tab; same slash commands
3 · Generate tests	Select the function/class → right-click → Gemini Code Assist → Generate unit tests; save the output into `tests/` (G04)	Select → right-click → Gemini → Generate unit tests
4 · Agent ticket	In the chat input, switch the Agent toggle ON → paste the ticket → review the plan in the panel → Accept/Reject each diff → click Allow when it asks to run a command (G06)	Gemini tool window → Agent tab → paste the ticket; leave Auto-approve changes OFF; review diffs the same way
5 · Headless CLI	Integrated terminal: `gemini -p "..."` — same login, same GEMINI.md rules as the IDE (G08)

Example: a block labeled "Move 4 · paste in Agent mode" means: open the Gemini panel, flip the Agent toggle (VS Code) or open the Agent tab (PyCharm), paste, and work the plan→diff→approve loop.

Architecture & Data Contracts

Source data: three monthly CSV drops (you'll generate them — messy on purpose — in Phase 1). Columns:

source schema — ucc_filings_2026_0X.csv

filing_number   e.g. "2026-0142337" (unique per filing... usually)
filing_type     UCC1 | UCC3-AMEND | UCC3-CONT | UCC3-TERM  (inconsistent casing)
file_date       MIXED FORMATS: "2026-03-14", "03/14/2026", "14-Mar-26"
debtor_name     free text, name variants, stray punctuation, ALL CAPS sometimes
debtor_state    2-letter code, sometimes full state name
secured_party   lender name, same chaos as debtor_name
collateral_desc free text ("all inventory and equipment", may be empty)
ref_filing      for UCC3 rows: the UCC-1 filing_number it modifies (sometimes missing!)

Core transformation rules your silver stage must enforce (this IS the project — put these in GEMINI.md):

Dates: parse all three formats to ISO; unparseable → reject with reason BAD_DATE.
Dedup: same filing_number + same content → keep first; same number, different content → reject both as CONFLICT (a human must look).
Entity resolution: normalize debtor names (uppercase, strip punctuation, expand/strip suffixes LLC/INC/CORP, collapse whitespace) → identical normalized names share an entity_id.
Lien status: a UCC-1 is ACTIVE until a UCC3-TERM references it, LAPSED 5 years after file_date unless a UCC3-CONT extends it 5 more, else TERMINATED.
Orphans: UCC-3 rows whose ref_filing is missing or matches nothing → quarantine as ORPHAN_AMENDMENT, never silently dropped.

The Trap Most Students Hit

Lien status is order-dependent state, not a row-level transform: a TERM filed before a CONT means something different than the reverse. Process amendments in file_date order per referenced filing. When your generated tests disagree with your code about a TERM→CONT sequence, draw the timeline on paper before deciding which is right — this is the capstone's "failing test is information" moment (G04).

Build Phases

Each phase names the course skills it exercises and gives you the key prompt. You write the judgment; Gemini writes the labor.

About the "expected output" blocks

Every phase below shows a sample of what success looks like. Because Gemini generates your code, your exact counts, wording, and formatting will differ — what must match is the shape (the same kinds of lines appear) and the invariants (e.g., bronze = silver + rejects). If your output is structurally different — missing reject reasons, no idempotency, counts that don't reconcile — that's a finding to fix, not a formatting quirk.

Phase	What happens	Skills from
1. Foundation	Rules + messy mock data	G05, G08
2. Bronze	Idempotent ingestion	G02
3. Silver	Cleaning + entity resolution + tests	G02–G04
4. Gold	Risk aggregates via agent mode	G06
5. Ship	DQ report, PR review, docs	G08–G09

Foundation: rules before code, then manufacture chaos

New folder liensight/, git init. Write GEMINI.md containing the five transformation rules above, plus: "pandas for transforms, sqlite3 for storage; every stage is a pure function frame-in/frame-out; rejects are quarantined with a reason code, never dropped; type hints everywhere; pytest with tmp_path fixtures." Add .aiexclude with data/ (generated data stays out of prompts — it's bulky noise). Then generate the test data with Gemini CLI:

Move 5 · integrated terminal (Ctrl+` / Alt+F12) — headless data generation

gemini -p "Write a Python script make_data.py that generates 3 CSVs (data/raw/ucc_filings_2026_01.csv, _02, _03) with ~120 rows total matching this schema: filing_number, filing_type (UCC1/UCC3-AMEND/UCC3-CONT/UCC3-TERM, inconsistent casing), file_date (deliberately mixed formats: ISO, MM/DD/YYYY, DD-Mon-YY), debtor_name (include 4 companies that each appear under 3 name variants like 'ACME Industrial Supply LLC' / 'Acme Industrial Supply, L.L.C.'), debtor_state, secured_party, collateral_desc (some empty), ref_filing (UCC3 rows reference real UCC1 numbers, but make 3 of them orphans and 2 missing). Include 2 duplicate filing_numbers with identical content and 1 with conflicting content. Use only the stdlib + random with a fixed seed." > make_data.py
python make_data.py

expected output — shape matters, your seeded values will differ

PS> python make_data.py
Wrote data/raw/ucc_filings_2026_01.csv  (42 rows)
Wrote data/raw/ucc_filings_2026_02.csv  (40 rows)
Wrote data/raw/ucc_filings_2026_03.csv  (44 rows)

PS> Get-Content data\raw\ucc_filings_2026_01.csv -TotalCount 4
filing_number,filing_type,file_date,debtor_name,debtor_state,secured_party,collateral_desc,ref_filing
2026-0142337,UCC1,2026-03-14,ACME Industrial Supply LLC,OH,First Commercial Bank,all inventory and equipment,
2026-0142401,ucc3-amend,03/22/2026,"Acme Industrial Supply, L.L.C.",OH,First Commercial Bank,,2026-0142337
2026-0142498,UCC1,22-Mar-26,BLUE RIVER LOGISTICS,TX,Lone Star Credit,fleet vehicles,

✅

Checkpoint Three CSVs exist; open one and visually confirm the chaos (mixed dates, name variants, orphan refs). You just used AI to build a realistic test fixture — a legitimate professional trick.

Bronze: boring on purpose

Create stages/bronze.py with a header docstring stating the bronze contract (load raw, add source_file and loaded_at, never transform, idempotent by source_file). Generate load_bronze() from spec-comments (Move 1: type the comment in bronze.py, pause for ghost text, Tab to accept). The interesting requirement is idempotency: re-running must not duplicate rows — spec it in the comment ("delete existing rows for this source_file before insert") and verify by running it twice.

expected output — note the second run changes nothing

PS> python pipeline.py run --stage bronze
[bronze] ucc_filings_2026_01.csv -> 42 rows loaded
[bronze] ucc_filings_2026_02.csv -> 40 rows loaded
[bronze] ucc_filings_2026_03.csv -> 44 rows loaded
[bronze] total rows in data/bronze/filings.db: 126

PS> python pipeline.py run --stage bronze     # idempotency check
[bronze] total rows in data/bronze/filings.db: 126   <-- unchanged

✅

Checkpoint python pipeline.py run --stage bronze twice in a row → same row count both times. Bronze row count equals raw CSV row count exactly (rejects don't exist yet — bronze takes everything).

Silver: where the thinking lives

Build stages/silver.py function by function with spec-comments (Move 1): parse_dates, dedupe_filings, normalize_name, resolve_entities, compute_lien_status. After each function, immediately generate its tests (Move 3: select the function → right-click → Gemini Code Assist → Generate unit tests) and run them with pytest tests/ -v in the integrated terminal. Use chat to pressure-test your own rules before coding them:

Move 2 · paste in chat (✦ panel / Gemini tool window) — design review before code

My name normalization rule: uppercase, strip punctuation, remove suffixes
LLC/INC/CORP/LP, collapse whitespace. Give me 5 realistic company-name pairs
where this rule WRONGLY merges two different companies, and 3 pairs it
wrongly keeps apart. Should I add anything cheap to reduce the worst risk?

What you'll discover

Chat will show you that "JOHNSON SUPPLY" (Ohio) and "JOHNSON SUPPLY" (Texas) merge wrongly — which is why your entity_id should include debtor_state. Real entity-resolution teams fight exactly this; your cheap fix (state-scoped identity) is the same first move they make.

expected output — the invariant: input = cleaned + rejected

PS> python pipeline.py run --stage silver
[silver] input rows: 126
[silver] cleaned: 114    rejected: 12
[silver]   BAD_DATE: 3 · CONFLICT: 2 · ORPHAN_AMENDMENT: 5 · DUPLICATE: 2
[silver] entities resolved: 58 distinct (4 variant groups merged)

PS> pytest tests/ -q
............................                                  [100%]
28 passed in 0.61s

✅

Checkpoint Silver runs end-to-end; the 4 variant-named companies resolve to 4 entity_ids (not 12); the conflict-duplicate and orphan rows sit in the rejects table with correct reason codes; pytest all green.

Gold: delegate it to the agent

The gold layer is well-specified mechanical work over clean data — perfect agent-mode material (G06). Clean git state first, then the ticket:

Move 4 · paste in Agent mode (Agent toggle ON / Agent tab)

Add the gold stage per GEMINI.md rules:
1. stages/gold.py builds two tables from silver:
   - entity_risk: entity_id, canonical_name, state, active_lien_count,
     distinct_secured_parties, earliest_expiry_date, risk_score
   - lapse_alerts: ACTIVE filings whose 5-year expiry (incl. continuations)
     falls within the next 90 days from a --as-of date parameter
2. risk_score 0-100: propose a simple weighted formula from the available
   fields in your plan and JUSTIFY the weights before implementing.
3. Wire --stage gold and --as-of into pipeline.py.
4. pytest coverage for both tables including a continuation-extends-expiry case.
Show me your plan first.

expected output — gold stage and a peek at the risk table

PS> python pipeline.py run --stage gold --as-of 2026-06-11
[gold] entity_risk: 58 rows
[gold] lapse_alerts: 6 filings expire within 90 days of 2026-06-11

PS> python -c "import sqlite3; [print(r) for r in sqlite3.connect('data/gold/gold.db').execute('SELECT canonical_name,state,active_lien_count,risk_score FROM entity_risk ORDER BY risk_score DESC LIMIT 3')]"
('ACME INDUSTRIAL SUPPLY', 'OH', 5, 78)
('BLUE RIVER LOGISTICS', 'TX', 3, 52)
('HILLTOP FOODS', 'CA', 2, 41)

PS> pytest tests/ -q
....................................                           [100%]
36 passed in 0.74s

✅

Checkpoint You challenged at least one thing in the agent's plan (the risk formula is designed to provoke this — if its weights seem arbitrary, say so and make it defend or revise them). Tests green from your terminal; git diff --stat footprint matches the ticket.

Ship: report, review, document

Implement pipeline.py report (rows in/out per stage, rejects by reason, entities resolved, alerts found). Then close the professional loop: push to GitHub, add .gemini/styleguide.md (reuse your GEMINI.md rules), open the gold stage as a PR, and process the bot's review (G09 discipline: fix / push back / interrogate). Finally, generate the docs headlessly:

Move 5 · integrated terminal

gemini -p "Write README.md for this pipeline: the UCC domain in 5 sentences, the medallion stages and what each guarantees, every CLI command with examples, the five silver transformation rules, and how to run the tests. Only markdown." > README.md
python pipeline.py report   # paste this output into the README yourself

expected output — the report a risk manager would read

PS> python pipeline.py report
LIENSIGHT DATA QUALITY REPORT          as of 2026-06-11
------------------------------------------------------
bronze   126 rows from 3 source files
silver   114 cleaned (90.5%)  |  12 quarantined
         BAD_DATE 3 · CONFLICT 2 · ORPHAN_AMENDMENT 5 · DUPLICATE 2
entities 58 distinct (4 variant groups merged; largest group: 3 names)
gold     58 entity_risk rows  ·  6 lapse alerts (90-day window)
invariant: bronze(126) == silver(114) + rejects(12)   OK

Grading Rubric

Replayability: deleting data/bronze onward and re-running --stage all reproduces identical gold tables (fixed seed, idempotent stages)

Nothing vanishes: every raw row is accounted for — cleaned, deduped, or quarantined with a reason code; bronze count = silver count + rejects count

Entity resolution works: the 4 variant-named companies resolve to exactly 4 state-scoped entities, with a test proving it

State machine correct: TERM ends a lien, CONT extends expiry 5 years, ordering by file_date respected — each with a test

Agent discipline: you redirected the agent's gold-stage plan (risk weights) or rejected at least one diff hunk, and verified independently

Review gate used: the gold PR got a bot review; at least one finding fixed and one pushed back on with reasoning

You can defend it: you can explain to an interviewer why entity_id includes state, and what your pipeline does with an orphan UCC-3

Stretch Goals

Fuzzy entity resolution: add rapidfuzz similarity matching above your exact-normalized baseline; measure how many new merges it makes and how many are wrong (precision matters more than recall for lenders).
BigQuery edition (G10): load the gold tables into BigQuery and use Gemini in BigQuery to write the "top 10 riskiest entities" query in natural language.
Scheduled DQ report: wrap pipeline.py report | gemini -p "summarize for a non-technical risk manager" in a scheduled task — your first AI-in-the-loop data ops job.
Incremental loads: add a fourth monthly CSV and make silver/gold process only the delta.

Where this goes on a resume

"Built a Medallion-architecture pipeline for public lien filings with quarantine-based data quality, state-scoped entity resolution, and amendment state tracking; AI-assisted development with human-reviewed agent workflows." Every phrase in that sentence is now true.