Design an Auditable Financial Reporting Pipeline

Problem

Finance needs daily and monthly revenue reporting (gross, refunds, net, by product, country, currency) built from the order system, payment provider files and the refund service. Numbers must match the general ledger, be reproducible months later (“why did March revenue change?”) and satisfy auditors (SOX).

Clarifying questions

1. Architecture

flowchart LR
    ODB[(Orders DB)] -->|CDC| BR[(Bronze)]
    PSP[PSP settlement files] -->|SFTP → landing,<br/>checksum + manifest| BR
    REF[Refund events] --> BR
    FX[FX rates API] --> BR
    BR --> SIL[Silver: typed, deduped,<br/>immutable facts]
    SIL --> REC[Reconciliation jobs<br/>orders ↔ payments ↔ refunds]
    REC --> EXC[(Exceptions queue<br/>unmatched, mismatched amounts)]
    SIL --> LED[(gold.revenue_ledger<br/>append-only journal entries)]
    LED --> REP[(gold.revenue_daily / monthly<br/>by product, country, currency)]
    REP --> WAP{WAP checks +<br/>GL tie-out}
    WAP -->|pass| PUB[Published reports<br/>finance BI]
    WAP -->|fail| HOLD[Hold + alert finance data team]
    CLOSE[Period close: snapshot + lock] --> REP

2. Deep dives

2.1 Model revenue as an append-only ledger

Instead of updating “order revenue” in place, record journal entries: every business event produces immutable rows.

• Corrections after close are new entries in the open period, never edits to closed periods.
• Any report = SUM(...) GROUP BY ... over entries → fully traceable to source events.
• Amounts in integer minor units + currency; FX rate stored per entry (rate as of the agreed timestamp), never recomputed later.

2.2 Idempotency and reproducibility

• Every entry has a deterministic id (hash(source, source_id, event_type, version)) → re-runs never duplicate.
• Pipelines are parameterised by business date; INSERT OVERWRITE / MERGE by deterministic ids.
• Period close creates a snapshot (Delta table version tagged/cloned: revenue_monthly_2026_03_final) that never changes. Time travel alone isn’t enough (VACUUM); use explicit snapshots or deep clones.

2.3 Three-way reconciliation

flowchart LR
    O[Orders captured] <-->|order_id, amount| P[PSP settlements]
    P <-->|payout batch| B[Bank statements]
    O <-->|refund_id| R[Refunds]
    O --> M{Match?}
    P --> M
    M -->|exact| OK[Reconciled]
    M -->|timing difference| T[Expected: settles T+2]
    M -->|amount / missing| X[Exception queue → finance ops]

Match rates and ageing of open exceptions are KPIs; reports show reconciled vs unreconciled amounts.

2.4 SOX controls in the data platform

• Change management: code review, CI, approvals; no direct prod edits; deployment logs.
• Segregation of duties: developers can’t approve their own production releases or change prod data by hand.
• Access: finance gold tables read-only to most; write only by service principals.
• Evidence: pipeline run logs, DQ results, reconciliation results retained and queryable.
• Data lineage from report cell → ledger entries → source records.

2.5 Quality gates before publishing

Totals tie to the GL (tolerance 0); no duplicate entry ids; every order with capture has a ledger entry; FX rates present for all currencies/dates; day-over-day anomaly checks. Failures block publishing.

3. Trade-offs

4. What separates a senior answer

• Ledger thinking: immutable entries, corrections as new entries, closed periods.
• Money in integer minor units, FX stored not recomputed.
• Reconciliation as a first-class pipeline with exception workflows.
• SOX controls expressed as platform features.
• Reproducibility via explicit snapshots.

5. Follow-up questions

Self-assessment rubric

• Append-only ledger model with deterministic ids
• Integer money, stored FX rates
• Period close, snapshots, corrections policy
• Reconciliation design and exception handling
• SOX controls and lineage
• Blocking quality gates and GL tie-out