# Banking data definitions This is a fictional event ledger. All timestamps and dates use UTC. The snapshot is 2026-08-03 00:00. Monetary values are integer minor units. CAD and USD are separate; no FX rate is supplied. Never sum currencies into one monetary figure. No default currency or reporting period is defined. ## Sources and relationships accounts has one row per account_id, its primary key. organization_id groups accounts. currency is the account's fixed currency. account_status is the status at the snapshot, either open or closed. opening_balance_minor is its ledger balance at 2026-07-01 00:00. Include closed and zero-activity accounts unless the question explicitly filters them. cards has one row per card_id, its primary key. cards.account_id references accounts.account_id; an account can have multiple cards or none. card_status is active, frozen or cancelled at the snapshot. Current card status never removes past posted spending. categories has one row per category_id, its primary key. category_name is a display label; reporting_group is a broader grouping. NULL category_id means uncategorized, not a missing row to drop. When a label is requested, use Uncategorized for that NULL bucket. transactions has one row per transaction_id, its primary key. account_id references accounts; card_id, when present, references cards for that same account. category_id is nullable and references categories. Transactions inherit their account's currency. amount_minor is positive; direction supplies the sign: credit adds to balance, debit subtracts. event_kind is purchase, refund, deposit, withdrawal or transfer. status is posted, pending or void. Only posted events change ledger balances or standard spend/cash-flow metrics. pending and void have no posted_at. occurred_at records the initiating event. posted_at records ledger recognition. Standard financial periods use posted_at, even when occurred_at is in another month. Use half-open intervals: the requested start at 00:00 inclusive, the next day/month at 00:00 exclusive. Explicit questions about pending or void activity use occurred_at. The date of a posted refund is its own recognition date, not the original purchase's date. Purchases are debits; refunds and deposits are credits; withdrawals are debits. A refund's related_transaction_id references its original posted purchase, with matching account, currency, card and category. Several partial refunds can reference one purchase; their cumulative amount cannot exceed it. No other event kind uses related_transaction_id. A transfer_group_id identifies exactly two transfer events between distinct accounts in the same currency: one debit and one equal credit with the same status and posting time. A transfer is internal movement and excluded from external cash flow and spend. Transfer volume counts only the debit leg; transfer count counts groups once. No FX transfer is present. ## Metric definitions Gross card spend is the sum of posted purchase amounts in the reporting period. Refunded amount is the sum of posted refund amounts in the reporting period, regardless of original purchase date. Net card spend is gross card spend minus refunded amount. Purchase count counts purchase events, not distinct cards or netted purchase/refund pairs. Refund count counts refund events. Deposits and withdrawals are their respective posted event amounts. Net external cash flow is all posted nontransfer credits minus all posted nontransfer debits, including purchases and refunds as well as deposits and withdrawals. It is not the deposits-minus-withdrawals subtotal. Opening ledger balance on a date includes the initial opening balance plus all posted signed movements before that UTC date. Closing ledger balance includes all posted signed movements through the end of that UTC date. It is a stock: sum across accounts on a single date, never across dates. Pending/void activity does not change it. Negative ledger balances are allowed in this fixture. Available balance, spendable cash, holds and credit limits are not modeled. Active-card count uses current card_status, counts cards, and retains accounts with zero active cards when reporting per account. An account with no posted purchases can still have deposits, withdrawals, transfers or refunds. "No purchases" does not mean "no ledger activity". ## Materialized marts and safe joins account_daily has exactly one row per (account_id, balance_date), including zero-activity accounts. It spans 2026-07-01 through 2026-08-02. currency is fixed by account. opening_minor and closing_minor are ledger stocks; posted_credit_minor and posted_debit_minor are that date's posted flows. opening_minor + posted_credit_minor - posted_debit_minor equals closing_minor. Join accounts on account_id, many daily rows to one account. Aggregate/filter the requested date first. Do not join unaggregated cards or transactions to balance rows; that multiplies the stock. card_spend_daily has one row per (spend_date, card_id, category_id), where spend_date is the posted UTC date and NULL category_id is a real bucket. account_id and currency are determined by card. It includes only posted purchases/refunds and no zero-filled calendar. purchased_minor, refunded_minor, net_spend_minor, purchase_count and refund_count are additive within a currency. net_spend_minor equals purchased_minor minus refunded_minor. A refund-only day can be negative. Allowed dimension joins: either mart to accounts on account_id; card_spend_daily to cards on card_id; card_spend_daily to categories on category_id with a LEFT JOIN to retain NULL categories; transactions to accounts on account_id, to cards on card_id and to categories with a LEFT JOIN. Each is many-to-one at the dimension key. Refund events can join original transactions on related_transaction_id = transaction_id, many refunds to one purchase. Preaggregate the refund side when summing original amounts, otherwise a partial-refund series repeats the original. No high-risk, suspicious-account or other risk threshold is defined. Ask for the missing business rule when requested. A question asking for "balance" without specifying ledger versus available balance requires clarification. Do not silently substitute ledger balance for available funds.