Data & Pipelines

The shape of financial data

Finance data is unusual compared to product data in three ways:

• Immutable history. You never delete. Corrections are reversing entries, not updates.
• Period-bounded. Once a period closes, data in that period is frozen. Re-opening a period is a control event.
• Exact, not statistical. To-the-penny correctness matters; "approximately right" is wrong.

These properties shape every pipeline. You can't "just rerun" without idempotency. You can't "fix" historical data without an audit trail of the fix.

Sources

Subledger vs. general ledger

One of the most consistently misunderstood pieces for non-finance candidates. Worth ten seconds:

• General Ledger (GL): the summary book. Each account is a single line: "Cash USD: $4.2M." High-level.
• Subledger: the detail. "Cash USD" rolls up thousands of bank transactions. AR subledger holds open invoices per customer. AP subledger holds open bills per vendor. Fixed-assets subledger holds individual assets. Crypto subledger (Lukka) holds individual lots.

The GL has to tie to the subledger. Reconciliation often means: "the GL says X for this account; the subledger detail sums to Y; show me X vs. Y with explanation." A common bug is the GL and subledger drifting apart because someone posted a top-side JE to the GL without flowing through the subledger.

Batch ETL vs. streaming

Most finance pipelines are batch — overnight loads, monthly closes. Streaming shows up for:

• Treasury monitoring: intraday cash positions, threshold alerts.
• Custody event capture: Fireblocks webhooks for transaction completion.
• Fraud / anomaly: rare in core finance, more in compliance-adjacent.

Trade-offs to articulate:

Senior answer: "Default to batch for posting and close; reserve streaming for monitoring and alerting where latency matters."

Reconciliation pipelines — the canonical shape

One pipeline per (account, source) pair. The shape:

    [Extract GL detail]   [Extract source detail]
            │                       │
            └──────────┬────────────┘
                       ▼
                [Normalize: schema,
                 amounts as Decimal,
                 timezone, currency]
                       ▼
                [Deterministic match
                 on (amount, date) ± tolerance]
                       ▼
                [Agent on residual: propose
                 matches and reconciling items]
                       ▼
                [Human approval gate]
                       ▼
                [Post to BlackLine / stage JE in NetSuite]
                       ▼
                [Audit-log every step]
  

What goes in your pipeline orchestrator (n8n or Airflow):

• Schedule (typically end-of-month + ad hoc).
• Source-system health checks before extracting.
• Extract step with checkpoints.
• Normalize step (deterministic, well-tested).
• Match step (deterministic).
• Agent step (LLM, on residual only).
• Approval step (Slack/email HITL).
• Post step (idempotent, with key).
• Audit-log writes throughout.

Data warehouse patterns for finance

A real finance warehouse (Snowflake, BigQuery, Redshift, or Databricks) tends to hold:

• Raw zone: untransformed copies of source data (NetSuite saved searches, Lukka exports, bank files) — for replay and audit.
• Staging zone: cleaned, typed, deduped versions of raw.
• Mart zone: business-friendly aggregates (trial balance by period, recon status by account, treasury daily cash).
• Audit log: agent-action log, immutable, indexed.

Patterns to call out:

• Slowly Changing Dimensions (SCD Type 2) for the chart of accounts — account codes evolve; historical balances must tie to the structure that existed at the time.
• Snapshot fact tables for period-end balances so a re-run of any prior close produces identical numbers.
• dbt (or equivalent) for transformation lineage — auditors love lineage.

Why finance teams live in spreadsheets (and what AI changes)

Walk into almost any finance org and you'll find Excel exports glued together with manual logic. This is not because accountants are unsophisticated; it's because:

• The ERP doesn't expose the cross-system view they need.
• Each close has unique adjustments that don't fit clean automation.
• Spreadsheets give them cell-level explainability auditors accept.
• IT bandwidth to build proper pipelines is finite; spreadsheets are zero-IT.

What AI agents change is the third constraint. A correctly-built agent can produce explainable, citation-backed analysis at spreadsheet-level transparency without the manual labor. The trick is making the agent's output look as transparent as a spreadsheet: every number cites a source, every assumption is named.

What AI actually changes in finance pipelines

Be specific. The wins:

• The unstructured tail: invoice OCR, contract terms extraction, vendor terms ingestion. AI does what regex + RPA couldn't.
• Long-tail recon items: fuzzy matching the 5% that deterministic rules can't handle.
• Narrative generation: variance commentary, audit-prep summaries.
• Cross-system summarization: pull from NetSuite + BlackLine + Kyriba into a CFO-readable brief.
• Anomaly explanation: not just "flag," but "here are the three transactions driving the flag."
• Onboarding new sources: parsing a new bank's statement format, a new exchange's CSV.

What AI doesn't change:

• Money math (use Decimal; don't ask the LLM to add).
• Deterministic matches (use code; faster, auditable).
• Period mechanics (close-period rules are deterministic policy).
• Audit trail (it's a database problem, not an AI problem).

Crypto-specific pipelines

This is where the company is unusual. Crypto-native pipelines:

• On-chain ingestion: full-node or indexer feed per chain; capture confirmed transactions for owned addresses.
• Address book: every owned address tagged with entity, purpose, account; new addresses go through a discovery/classification step.
• Cost basis & lot tracking: every disposal needs a lot selection; Lukka handles this.
• Fair value snapshots: principal-market FV at the timestamp required by FASB ASU 2023-08.
• Cross-source reconciliation: exchange records vs. on-chain reality (for transfers in/out); custody records vs. on-chain.
• Event classification: airdrop vs. staking reward vs. fork — accounting treatment differs.

For your interview, you don't need to be a blockchain engineer. You need to know that Lukka does this work, and that the agentic layer reconciles Lukka's outputs to NetSuite — not the raw blockchain feed.

Data quality & lineage

Data quality in finance is non-negotiable. A practical program:

• Schema tests on every load: every field has expected type, range, non-null where required.
• Reconciliation tests: source row count matches loaded count; sum of amounts matches.
• Freshness checks: every source has a "last updated" SLA; alert when stale.
• Lineage tooling (dbt docs, OpenLineage, Marquez) so you can show an auditor: "this $4.2M cash balance comes from these 47 bank transactions, ingested at 02:14 from this file, processed by this dbt model version."
• Replay: anything can be reproduced from the raw zone deterministically.

That last bullet is the foundation for audit-prep being cheap. If you can replay any close from raw inputs, the auditor's "show me how this number was produced" is a 30-second answer.

✓ Check yourself

Answer from memory before opening each one.

1. After a top-side entry, the GL balance for a crypto asset is $180K different from the subledger total. What is the likely cause, and how do you frame the reconciliation?

   Show answer

   The usual cause is an entry posted straight to the GL that never flowed through the subledger, so the two drifted apart. Frame the work as GL balance versus subledger detail, show the difference, and explain it item by item. The GL has to tie to the subledger, so an unexplained gap is a finding. See subledger versus GL.

2. The Treasurer wants intraday cash alerts, and someone suggests moving month-end posting to streaming too, for freshness. Where do you use each, and why?

   Show answer

   Use streaming for monitoring and alerting where latency matters, and keep posting and close on batch. Batch has clean period boundaries, trivial replay and auditable cutoffs, while streaming needs event sourcing and a defined cutoff for every reconciliation. Freshness is not worth that cost for entries that post once a month. See batch versus streaming.

3. Bank lines arrive in UTC and the ledger records local dates. What happens if the pipeline matches on date without normalising first, and where does the fix belong?

   Show answer

   A late-evening local transaction carries the next UTC date, so identical items fail to match and land in the residual, where they cost model calls and human time. Normalise amounts to Decimal, timezone and currency in a deterministic, well-tested step before matching. See reconciliation pipelines.

4. In July an account is moved under a different parent in the chart of accounts. You re-run the Q1 report in August. What should it show, and which warehouse patterns make that true?

   Show answer

   It should show Q1 as it was reported, under the structure that existed then. A Type 2 slowly changing dimension keeps the old mapping with validity dates, and snapshot fact tables freeze period-end balances so a re-run reproduces the same numbers. See warehouse patterns.

5. A team proposes that the model add up 2,000 invoice lines and also parse the layout of a new exchange's CSV. Which of the two fits, and why does the other not?

   Show answer

   Parsing a new, irregular format fits, since it is the unstructured tail that rules and regexes handle badly. The addition does not: money math belongs in Decimal code, which is exact and auditable. See what AI changes.

6. An auditor asks how a $4.2M cash balance from six months ago was produced. What must you have kept for a 30-second answer?

   Show answer

   The raw source files, the versioned transformations, the lineage from balance back to source rows, and the period snapshot. With those you can replay the close deterministically and show the 47 bank lines behind the figure. Without raw data, the answer becomes a reconstruction. See data quality and lineage.

Takeaway

• Finance data is immutable, period-bounded and exact, so corrections are reversing entries, closed periods stay frozen, and reruns need idempotency.
• The general ledger must tie to the subledger, and breaks often come from entries posted top-side that skipped the subledger.
• Default to batch for posting and close, and use streaming only for monitoring and alerting where latency pays.
• A reconciliation pipeline runs per account and source: extract, normalise, match deterministically, let the agent work the residual, gate on a human, post idempotently and log every step.
• Keep a raw zone, Type 2 dimensions for the chart of accounts and period snapshots, so any prior close can be replayed to identical numbers.
• AI helps with the unstructured tail, long-tail matches, narratives and cross-system summaries. It does not change money math, deterministic matching, period rules or the audit trail.
• For crypto, the agent reconciles Lukka's outputs to the ledger and does not read raw chain data.

The pipelines are only useful once they run safely in production, which is the subject of deployment and operations. → Deployment & Ops

Further reading

• Snapshots — dbt documentation. Tracking changing records over time, the Type 2 pattern.
• OpenLineage — OpenLineage documentation. An open standard for data lineage metadata.
• Slowly Changing Dimension Type 2 — Kimball Group. The original description of the technique.
• ASU 2023-08: Accounting for and Disclosure of Crypto Assets — FASB, 2023. The fair value requirement behind the crypto snapshots.
• SuiteQL — Oracle NetSuite documentation. Querying ledger data for extracts.
• API Overview — Fireblocks developer docs. The balance and transaction endpoints the custody feed relies on.