BankOrg Docs

In progress

BankOrg Data Platform / Data Modeling / Silver

Core

Interactive physical model of the PRD_SILVER.CORE schema: the three dimensions, the transaction fact and the two balance facts, their keys and relationships.

PRD_SILVER.CORE holds the three dimensions dim_branch, dim_customer and dim_account, the fact fact_transaction and the two balance facts fact_account_balance_hist and fact_account_balance_daily. dbt creates them as tables. Pick a table in the explorer or on the canvas to see its columns, keys and relationships.

Banking Data Architecture Explorer

drag tables · scroll to zoom

Grain

  • Each dimension has one row per version of its business key, with valid_from, valid_to and is_current. The key of a row is a surrogate key of the version (branch_key, customer_key, account_key), taken from dbt_scd_id.
  • fact_transaction has one row per side of a posting leg, keyed by transaction_id, leg_no and direction (DR or CR). The two sides of a leg share its leg_no; a transaction has several legs, for example the transfer and its fee.
  • fact_account_balance_hist has one row per POSTED, not deleted posting of an account, keyed like fact_transaction, with the balance after it. fact_account_balance_daily has one row per account and day, keyed by account_id and balance_date. Both are described in Gold.

Relationships

  • The relationships join on the business key, not on the surrogate key: a customer points to dim_branch.branch_code, an account to dim_customer.customer_id and dim_branch.branch_code, and a posting and a balance row to dim_account.account_id. Each of them has a dbt relationships test.
  • Because a business key repeats across versions, a join to a dimension is filtered on is_current or on the validity range.

The models, the incremental merge of the fact and the tests are described in Silver, and the choice of the grain in ADR 0003.