Hermes Wiki
Projects/Citibank-Compliance-DQ-Audit-Automation

Citibank Compliance DQ & Audit Automation

Portfolio project plan, built to mirror what a friend actually does at Citibank: a Data Quality / Compliance Testing role in the Deposits domain — Airflow-orchestrated DQ jobs, rules provided per domain (Loans, Mortgages, Deposits, Credit, Regions), data sourced from an internal data lake via a tool called Genesis. See Profile for career context on why this is on the roadmap.

Three-phase build, each phase shippable on its own, phase 3 depending on phases 1–2's output:

  1. Deposits DQ Pipeline — domain-specific rules engine + Airflow orchestration
  2. Credit DQ Pipeline — same generic engine, second domain's rule config (proves reusability)
  3. Audit Copilot — dashboard (SLI/SLO/SLA) + chat interface over both pipelines' results, the "observability for compliance" layer

Why This Project

Quarterly audits currently take an auditor 3–4 weeks, mostly spent manually gathering evidence: pulling DQ job results, checking rule coverage, cross-referencing lineage. That evidence-gathering step is exactly what's automatable — continuous testing + a queryable result store + a lineage trail turns "re-run everything by hand once a quarter" into "read the dashboard, drill into any exception." What stays human: judgment on ambiguous rules, regulatory attestation/sign-off, and root-cause investigation on genuinely novel failures. The goal of this project is to build the automatable 80% convincingly, not to claim 100%.

Architecture (all 3 phases)

Data Lake (synthetic)
   │
   ▼
Genesis-equivalent ingestion (simple extract step)
   │
   ▼
┌─────────────────────────────┐
│  Airflow DAGs (per domain)   │
│  extract → run_dq_rules →    │
│  generate_report → alert     │
└─────────────────────────────┘
   │                    │
   ▼                    ▼
Rules Engine        Result Store (Postgres)
(generic, reads      - rule, domain, region
 YAML per domain:     - pass/fail counts
 deposits.yaml,       - failing record IDs
 credit.yaml)         - source_version (lineage)
                       - timestamp
                          │
                          ▼
              ┌───────────────────────┐
              │   Audit Copilot        │
              │  - SLI/SLO/SLA layer   │
              │  - Dashboard (trends)  │
              │  - Chat (RAG over      │
              │    result store +      │
              │    rule definitions)   │
              │  - Audit export (PDF)  │
              └───────────────────────┘

Key design principle carried through all 3 phases: one generic rules engine, N per-domain YAML configs. This is the real lesson from how production DQ platforms are actually built — Phase 2 should not fork Phase 1's code, it should just add credit.yaml and prove the engine holds up.

Phase 1 — Deposits DQ Pipeline

Goal: stand up the full mechanical loop — synthetic data → declarative rules → Airflow orchestration → report — for one domain.

Tasks

  • Generate synthetic deposits dataset (account_id, customer_id, balance, interest_rate, account_type, region, open_date), seeded with bad records (negative balances, nulls, mismatched regions, duplicate account IDs)
  • Write deposits.yaml — declarative rules: not-null, range, referential integrity (customer_id exists), regex format, cross-field logic (savings → interest_rate > 0)
  • Build the rules engine (pandas or Great Expectations) that reads YAML, applies checks, outputs pass/fail per record + summary
  • Stand up local Airflow (Docker Compose) with DAG: extract_deposits_data → run_dq_rules → generate_report → alert_on_failures
  • Partition results by region, simulating region-specific rule thresholds
  • Persist run output (not just print) — dated, versioned CSV/report: rule ID, description, pass/fail count, sample failing records, timestamp
  • Stretch: reconciliation check — aggregate balances between two simulated "systems" (lake vs. downstream)

Deliverable

A working Airflow DAG that runs Deposits DQ checks on demand and produces an auditable report.

Phase 2 — Credit DQ Pipeline

Goal: prove the Phase 1 engine is domain-agnostic by plugging in a second, structurally different rule set — reuse the engine, don't rebuild it.

Tasks

  • Generate synthetic credit dataset (account_id, customer_id, credit_limit, current_balance, available_credit, apr, delinquency_days, payment_due_date, last_payment_date, account_status, region, risk_score), seeded with bad records (balance > limit, negative available_credit, inconsistent delinquency/status, implausible APR)
  • Write credit.yaml — reuse referential/not-null patterns from deposits, add credit-specific logic:
    • current_balance <= credit_limit
    • available_credit == credit_limit - current_balance
    • state-machine: delinquency_days > 0 ⟹ account_status != "current"
    • region-parameterized APR/usury caps
    • freshness rule: risk_score recalculated within N days
  • Plug credit.yaml into the same engine and DAG shape from Phase 1 — no forked codebase
  • Cross-domain reconciliation: sum of current_balance per customer vs. a "customer risk profile" aggregate exposure field

Deliverable

credit.yaml running through the Phase 1 engine, proving "rules provided by different domains" plugs into one framework — the actual shape of a real DQ platform team's work.

Phase 3 — Audit Copilot (the automation-of-the-audit layer)

Goal: turn Phase 1 + 2's accumulated run history into something an auditor can query in minutes instead of weeks — dashboard for SLI/SLO/SLA, chat interface for ad-hoc "why did X fail" questions, with a full evidence trail.

Scope note: this automates evidence gathering and continuous testing, not the auditor's judgment or regulatory attestation — see "Why This Project" above. Frame all outputs as auditor-assist, not auditor-replacement.

3a. Result Store (persistent, queryable)

  • Postgres schema: dq_runs(run_id, domain, region, rule_id, timestamp, pass_count, fail_count, source_version, dag_run_id)
  • dq_failures(run_id, record_id, rule_id, failure_detail) — the actual evidence, not just counts
  • rules(rule_id, domain, description, definition_yaml, version, effective_date) — versioned, so a report can cite exactly which rule version ran

3b. Lineage

  • Tag every run with source_version (hash or version string of the synthetic data snapshot it consumed) — minimal stand-in for real lineage
  • Look at OpenLineage as the real-world standard to reference/imitate

3c. SLI/SLO/SLA layer

  • Define SLI examples: pass_rate per domain/region, pipeline freshness, rule coverage per domain
  • Define SLO config per domain, e.g. deposits: pass_rate >= 99.5%, credit: freshness <= 24h
  • Compute breach status per period, store as a derived table

3d. Dashboard

  • Build with Streamlit (fast) or FastAPI + React (more realistic) — SLO status per domain/region over time, trend charts, drill-down into failing records
  • This view is what replaces "quarterly audit" with "live status"

3e. Chat interface (RAG over structured audit data, not free-text RAG)

  • LLM-backed query layer: user asks e.g. "why did credit domain fail SLO in Region=US in Q2"
  • Retrieval step queries the result store + rule definitions (structured SQL/lookup, not vector search over prose) for the relevant runs, rule text, and failing samples
  • Response composes an answer with the trail as proof — rule definition → data snapshot version → job run → result → any remediation
  • This is the direct analog of "why is Kafka traffic high" observability chat, applied to DQ/compliance data instead of metrics/traces

3f. Audit export

  • Generate a point-in-time PDF/report bundling SLO status + evidence trail + rule versions for a given quarter — the artifact an actual auditor would review/sign off on

Deliverable

A dashboard + chat interface sitting on top of Phase 1/2's result history, demonstrating the full "3-4 week audit → minutes of review" narrative with real (if synthetic) evidence trails.

Tech Stack Summary

Layer Choice Why
Orchestration Airflow (Docker Compose) matches real-world tool used in the role
Rules engine Great Expectations (or pandas-based custom) industry-standard DQ library
Rule definitions YAML, versioned declarative, auditable, domain-agnostic
Result store Postgres queryable audit log + lineage
Lineage reference OpenLineage (concept, minimal impl) real-world standard to speak to
Dashboard Streamlit or FastAPI+React SLI/SLO/SLA visualization
Chat interface LLM + structured retrieval over Postgres RAG-over-structured-data, not prose RAG
Real-world commercial analogs Monte Carlo, Bigeye, Soda Cloud, Databand reference points for scope/credibility

Open Questions / Decisions To Revisit

  • Great Expectations vs. hand-rolled pandas rules engine — GE is more "real," pandas is faster to iterate on early
  • Streamlit (fast, less "real") vs. FastAPI+React (slower, more portfolio-credible) for the dashboard
  • How much of the chat interface's retrieval should be pure SQL templating vs. actual LLM-driven query generation — start with SQL templating, consider LLM query generation as stretch goal
Hermes Wiki