← Writing
data-engineeringfintech

Your financial data model is a contract, not a convenience

tl;dr A few schema decisions upfront - tx_guid, source_type, account_class - determine whether your PFM analytics are clean GROUP BYs or painful rewrites.

You’re three sprints into a PFM product. The bank transaction pipeline is working. A PM asks: “Can we show net worth - just combine the investment account too, shouldn’t be much work?”

A couple of weeks later, the investment data is in - but net worth figures don’t add up and nobody’s quite sure why. A transfer from checking to brokerage is showing up on both sides. A pipeline is being partially rebuilt, and aggregations that used to be clean GROUP BYs now carry a growing list of exclusion filters.

The data wasn’t bad. The schema just wasn’t ready for the question.

I’ve seen this happen more than once in financial analytics products - not because engineers are careless, but because the schema decisions that prevent it look like accounting formalism rather than practical data modeling. Structuring your ledger around double-entry principles - where every transaction is two rows, not one, and every account is a named, typed entity - turns out to be the difference between clean GROUP BYs and painful rewrites. By the time you feel that difference, you’re already mid-sprint on something else.

If you’re building a PFM tool, an investment dashboard, or anything where financial data from multiple sources needs to produce coherent analytics, the schema decisions below are the ones worth getting right early. Examples are in DuckDB.

Double-entry bookkeeping has one rule: every movement of money touches at least two accounts. Money leaving your checking account doesn’t disappear - it shows up somewhere else. A salary credit has an equal debit on the other side. For analytics, this matters less as an accounting constraint and more as a data shape: every financial event is a pair of rows, and the accounts table is the taxonomy that makes those rows meaningful. Get the taxonomy right and aggregations compose cleanly. Get it wrong and every new analytics query requires reconciliation nobody planned for.

Start with the schema

GnuCash - the open-source accounting tool - has a schema worth knowing. Its core is three tables: accounts, transactions, and splits. Each split is one side of a transaction; every transaction must have at least two balanced splits. GnuCash also ships an extensive collection of built-in reports - balance sheets, income statements, cash flow - that are battle-tested across real finance use cases. If you want to see what a correct double-entry model produces, it’s a solid reference point.

The schema below follows the same structure and naming, with a few additions that make it analytics-ready:

CREATE TABLE accounts (
    guid          VARCHAR PRIMARY KEY,
    name          VARCHAR NOT NULL,
    account_type  VARCHAR NOT NULL,   -- ASSET, LIABILITY, INCOME, EXPENSE, EQUITY
    account_class VARCHAR NOT NULL,   -- checking, savings, brokerage, credit_card, loan
    source_type   VARCHAR NOT NULL,   -- bank, investment, credit, manual
    commodity     VARCHAR NOT NULL,   -- USD, EUR, AAPL
    parent_guid   VARCHAR
);

CREATE TABLE transactions (
    guid        VARCHAR PRIMARY KEY,
    post_date   TIMESTAMP NOT NULL,
    description VARCHAR,
    currency    VARCHAR NOT NULL
);

-- One row per side of a transaction - the main analytics surface
CREATE TABLE splits (
    guid              VARCHAR PRIMARY KEY,
    tx_guid           VARCHAR NOT NULL,
    account_guid      VARCHAR NOT NULL,
    account_name      VARCHAR NOT NULL,   -- denormalized from accounts
    account_type      VARCHAR NOT NULL,   -- denormalized from accounts
    account_class     VARCHAR NOT NULL,   -- denormalized from accounts
    source_type       VARCHAR NOT NULL,   -- denormalized from accounts
    direction         VARCHAR NOT NULL,   -- debit | credit
    amount            DECIMAL(18, 4) NOT NULL,
    currency          VARCHAR NOT NULL,
    counterparty_guid VARCHAR,
    post_date         TIMESTAMP NOT NULL, -- denormalized from transactions
    memo              VARCHAR,
    reconcile_state   VARCHAR
);

tx_guid links the two sides of a transaction. Every debit has a corresponding credit under the same tx_guid. This is the double-entry constraint expressed as a column, not a database rule.

account_name, account_type, account_class, and source_type are denormalized into splits deliberately. In an OLAP context, joins are friction. You want analysts and notebooks reaching for GROUP BY, not JOIN.

account_class and source_type are the additions GnuCash doesn’t have. They’re what let you query across bank, investment, and credit data without the sources bleeding into each other. More on this shortly.

counterparty_guid is what makes self-transfer detection possible without a separate lookup.

A basic net spending query:

SELECT
    account_name,
    SUM(CASE WHEN direction = 'debit' THEN amount ELSE 0 END)  AS total_debits,
    SUM(CASE WHEN direction = 'credit' THEN amount ELSE 0 END) AS total_credits,
    SUM(CASE WHEN direction = 'debit' THEN amount ELSE -amount END) AS net
FROM splits
WHERE account_type = 'EXPENSE'
  AND post_date >= '2024-01-01'
GROUP BY account_name
ORDER BY net DESC;

No joins. No subqueries. That’s what the upfront work buys you.

The accounts table is shared infrastructure

The account_guid and account_name columns in your splits table aren’t just labels. They’re a taxonomy that multiple teams will build on - often without realising it.

A data science team training a transaction classifier uses account categories as training labels. A BI team builds a dashboard filtered by account type. A product team defines “savings rate” as a ratio between two account classes. None of these teams coordinated. All of them now depend on that taxonomy staying stable.

This is fine - until an account gets renamed, reclassified, or split.

Renaming "Food & Dining" to "Restaurants" is a one-line migration. Downstream, it’s a silent label mismatch in a trained model, a broken dashboard filter, and a metric that quietly changes definition. Nobody gets an error. The numbers just drift.

Treat the accounts taxonomy the way you’d treat a public API:

  • Define it before you need it. Don’t let account categories emerge organically from transaction imports. Start with a deliberate chart of accounts scoped to your product. PFM needs are different from a general ledger. Twenty well-defined categories beat sixty emergent ones.
  • Version it. Any change to an existing account - rename, reclassification, merge, split - is a migration, not an edit. Keep an accounts_history table or at minimum a changelog.
  • Announce changes like breaking changes. Teams depending on the taxonomy should know before a change ships, not after their aggregates drift.

Before touching an account, this query tells you how much depends on it:

SELECT
    source_type,
    COUNT(*) AS split_count,
    MIN(post_date) AS earliest,
    MAX(post_date) AS latest
FROM splits
WHERE account_guid = 'food_dining_guid'
GROUP BY source_type;

Not a governance framework. Just a gut check before you touch something.

Multi-source data is where schemas quietly fall apart

Bank data and investment data look similar at the row level - both have dates, amounts, descriptions. But they represent different things. A bank transaction is a cash movement. An investment transaction might be a share purchase, a dividend reinvestment, or a portfolio rebalancing. They’re not the same event wearing different clothes.

Adding a second source anyway feels like a configuration change. Connect the brokerage API, map the fields, append to the same pipeline. Done.

It isn’t done.

Without source_type and account_class as first-class columns, there’s no way to distinguish these at query time. The typical result is net worth calculations that double-count: a transfer from checking to brokerage appears as an outflow on the bank side and an inflow on the investment side. Sum both naively and the money appears twice - once leaving, once arriving. Net worth goes up on a day it shouldn’t. With explicit source tracking in the schema, this becomes something you can detect and exclude rather than something that silently corrupts your aggregates.

With source_type and counterparty_guid in the schema, cross-source transfers are identifiable:

SELECT
    a.tx_guid,
    a.account_name AS from_account,
    b.account_name AS to_account,
    a.amount,
    a.post_date
FROM splits a
JOIN splits b
    ON a.tx_guid = b.tx_guid
    AND a.direction != b.direction
WHERE a.source_type != b.source_type
  AND a.account_class IN ('checking', 'savings')
  AND b.account_class = 'brokerage';

Net worth that’s actually correct across sources:

SELECT
    SUM(CASE
        WHEN account_type = 'ASSET'     THEN amount
        WHEN account_type = 'LIABILITY' THEN -amount
        ELSE 0
    END) AS net_worth
FROM splits
WHERE direction = 'debit'
  AND tx_guid NOT IN (
      SELECT tx_guid
      FROM splits
      GROUP BY tx_guid
      HAVING COUNT(DISTINCT source_type) > 1
         AND COUNT(DISTINCT direction) = 2
  );

The subquery isn’t elegant, but it’s explicit: exclude movements that are just money changing pockets.

Equity data makes this harder. A stock sale produces cash, but it also produces a realised gain or loss that belongs in a different account class from the cash movement. Without account_class as a first-class column, separating operating cash flow from investment returns becomes a string-parsing exercise on transaction descriptions. That’s the rebuild the PM didn’t budget for.

The fix isn’t complicated. A few columns defined early, and a clear mapping per source type before ingestion - not after the dashboard is showing wrong numbers.

The queries that become simple

By the time a PM asks for savings rate, spending trends, or “why did net worth drop last month”, the schema is either ready for the question or it isn’t.

Self-transfer detection. Money moving between a user’s own accounts shouldn’t count as income or expense. Without counterparty_guid, finding these means fuzzy matching on amounts and dates. With it:

SELECT tx_guid, SUM(amount) AS transfer_amount, post_date
FROM splits
WHERE counterparty_guid IN (
    SELECT DISTINCT account_guid FROM splits
)
GROUP BY tx_guid, post_date;

Savings rate. One of the most requested PFM metrics, and one of the most commonly miscalculated:

SELECT
    DATE_TRUNC('month', post_date) AS month,
    SUM(CASE WHEN account_type = 'INCOME'  AND direction = 'credit' THEN amount ELSE 0 END) AS income,
    SUM(CASE WHEN account_type = 'EXPENSE' AND direction = 'debit'  THEN amount ELSE 0 END) AS expenses,
    ROUND(1 - (
        SUM(CASE WHEN account_type = 'EXPENSE' AND direction = 'debit'  THEN amount ELSE 0 END) /
        NULLIF(SUM(CASE WHEN account_type = 'INCOME'  AND direction = 'credit' THEN amount ELSE 0 END), 0)
    ), 4) AS savings_rate
FROM splits
GROUP BY month
ORDER BY month;

Unusual outflows. A basic anomaly signal, no ML required:

SELECT
    account_name,
    DATE_TRUNC('month', post_date) AS month,
    SUM(amount) AS monthly_spend,
    AVG(SUM(amount)) OVER (
        PARTITION BY account_name
        ORDER BY DATE_TRUNC('month', post_date)
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS trailing_avg,
    SUM(amount) / NULLIF(AVG(SUM(amount)) OVER (
        PARTITION BY account_name
        ORDER BY DATE_TRUNC('month', post_date)
        ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ), 0) AS spend_ratio
FROM splits
WHERE account_type = 'EXPENSE'
GROUP BY account_name, month
HAVING spend_ratio > 1.5
ORDER BY spend_ratio DESC;

More than 1.5x the trailing three-month average in a category - flag it. The point isn’t that these queries are clever. It’s that they’re possible without joins, without a pipeline rebuild, and without a mid-sprint argument about what “income” means. That argument happened at schema design time.

Small decisions, long shadows

The PM’s question was reasonable. It usually is. The problem is that the schema underneath wasn’t built to absorb it. Financial data compounds: a missing column early becomes a reconciliation step in every downstream query. An ungoverned accounts taxonomy means every new ML feature starts with a data audit. A naive union of two source types means net worth is wrong in ways that are hard to explain to a stakeholder.

None of these schema decisions are large. A source_type column. A tx_guid that groups both sides of a transaction. An accounts taxonomy defined before it’s needed. They cost an afternoon at the start and a sprint in the middle.

Further reading