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_toandis_current. The key of a row is a surrogate key of the version (branch_key,customer_key,account_key), taken fromdbt_scd_id. fact_transactionhas one row per side of a posting leg, keyed bytransaction_id,leg_noanddirection(DRorCR). The two sides of a leg share itsleg_no; a transaction has several legs, for example the transfer and its fee.fact_account_balance_histhas one row per POSTED, not deleted posting of an account, keyed likefact_transaction, with the balance after it.fact_account_balance_dailyhas one row per account and day, keyed byaccount_idandbalance_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 todim_customer.customer_idanddim_branch.branch_code, and a posting and a balance row todim_account.account_id. Each of them has a dbtrelationshipstest. - Because a business key repeats across versions, a join to a dimension is filtered on
is_currentor 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.