BankOrg Docs

In progress

BankOrg Data Platform / Data Engineering

Silver

The Silver layer in PRD_SILVER: the SCD Type 2 snapshots, the three dimensions, the incremental transaction fact at the grain of a posting leg, the balance facts, their tests and the build.

The Silver layer is the database PRD_SILVER. Its schema SNAPSHOTS holds the SCD Type 2 history of branches, customers and accounts, and its schema CORE holds three dimensions, the transaction fact built from them and the two account balance facts. Everything in it is created by dbt; the tables and their columns are in the data model pages Snapshots and Core.

Create the database

Create the PRD_SILVER database and the two schemas as SYSADMIN (script 31_create_silver_db.sql), keep 7 days of Time Travel on SNAPSHOTS, and let DBT_ROLE create tables in both schemas.

Snowflake
USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS PRD_SILVER;
CREATE SCHEMA IF NOT EXISTS PRD_SILVER.SNAPSHOTS;
CREATE SCHEMA IF NOT EXISTS PRD_SILVER.CORE;

ALTER SCHEMA PRD_SILVER.SNAPSHOTS SET DATA_RETENTION_TIME_IN_DAYS = 7;

GRANT USAGE ON DATABASE PRD_SILVER TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_SILVER.SNAPSHOTS TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_SILVER.CORE TO ROLE DBT_ROLE;

Configure dbt

In dbt_project.yml the marts of marts/core are tables in PRD_SILVER.CORE and the snapshots are written to PRD_SILVER.SNAPSHOTS. Both run the post-hook apply_pii_masking(), which sets the PII tag on the columns marked in the YAML (see PII Masking).

dbt/dbt_project.yml
models:
  bankorg:
    marts:
      core:
        +materialized: table
        +database: PRD_SILVER
        +schema: CORE
        +post-hook: "{{ apply_pii_masking() }}"

snapshots:
  bankorg:
    +database: PRD_SILVER
    +schema: SNAPSHOTS
    +post-hook: "{{ apply_pii_masking() }}"

Snapshots

The three snapshots are defined in snapshots/snapshots.yml with the check strategy on the row_hash of the staging view (ADR 0001). When the hash of a key changes, dbt fills dbt_valid_to on the current row and inserts a new version.

dbt/snapshots/snapshots.yml
snapshots:
  - name: snp_branches
    description: SCD2 history of branches, one row per version of a branch_code.
    relation: ref('stg_core_banking__branches')
    config:
      unique_key: branch_code
      strategy: check
      check_cols: [row_hash]
SnapshotUnique keySourceRows
snp_branchesbranch_codestg_core_banking__branches50
snp_customerscustomer_idstg_core_banking__customers309
snp_accountsaccount_idstg_core_banking__accounts511

The first build of the snapshots holds one version per key, because the staging views carry the latest snapshot only. The history starts from that build.

Dimensions

Each dimension selects from its snapshot, renames dbt_scd_id to the surrogate key and the validity columns to valid_from, valid_to and is_current, and drops the load metadata and row_hash.

dbt/models/marts/core/dim_branch.sql
select
    dbt_scd_id          as branch_key,
    branch_code,
    branch_name,
    region,
    city,
    province,
    address_line,
    branch_type,
    branch_status,
    open_date,
    close_date,
    dbt_valid_from      as valid_from,
    dbt_valid_to        as valid_to,
    dbt_valid_to is null as is_current
from {{ ref('snp_branches') }}
ModelBuilt fromSurrogate keyBusiness keyRows
dim_branchsnp_branchesbranch_keybranch_code50
dim_customersnp_customerscustomer_keycustomer_id309
dim_accountsnp_accountsaccount_keyaccount_id511

Transaction fact

fact_transaction is an incremental model with the merge strategy (ADR 0002). It keeps the latest change event of each side of a posting leg, and a delete event stays in the table flagged with is_deleted.

dbt/models/marts/core/fact_transaction.sql
{{
    config(
        materialized='incremental',
        incremental_strategy='merge',
        unique_key=['transaction_id', 'leg_no', 'direction'],
        on_schema_change='append_new_columns'
    )
}}

select
    transaction_id,
    leg_no,
    direction,
    account_id,
    ...
    change_type = 'D'       as is_deleted,
    _loaded_at              as loaded_at
from {{ ref('stg_core_banking__transactions') }}
where is_latest_event
{% if is_incremental() %}
    and _loaded_at >= (
        select dateadd(hour, -{{ var('transaction_lookback_hours', 24) }}, max(loaded_at))
        from {{ this }}
    )
{% endif %}

Watermark

The incremental filter is the load time of the event, _loaded_at, kept in the fact as loaded_at. A run reads the events loaded from 24 hours before the largest loaded_at in the table (the variable transaction_lookback_hours), and the merge on the key leaves the rows already stored unchanged, so a rerun changes nothing. The 5854 rows were built from the 6122 events of RAW, and a second run left the count at 5854.

Grain

A transaction can post to several accounts, so the fact has one row per side of a posting leg, keyed by transaction_id, leg_no and direction (ADR 0003). The two sides of a leg, debit DR and credit CR, share the leg_no. The transfer TXN000004421 has two legs, the transfer and its fee:

transaction_idleg_nodirectionaccount_idtransaction_typeamount
TXN0000044211CRACC0000317TRANSFER_IN17487000
TXN0000044211DRACC0000345TRANSFER_OUT17487000
TXN0000044212DRACC0000345FEE21000

amount is always positive; the direction says whether the posting adds to or subtracts from the account. A transfer between two accounts in the same currency has both sides, and a deposit, a withdrawal, an interest posting and a fee have one side.

Balance facts

fact_account_balance_hist and fact_account_balance_daily are built from fact_transaction in the same schema, and are read by the gold marts. They are described on the Gold page.

Tests

KindWhat is checked
Keysunique and not_null on each surrogate key; unique_combination_of_columns on (business key, valid_from) of each dimension and on (transaction_id, leg_no, direction) of the fact
One current versionone_current_row_per_key: a custom generic test, no business key has two current versions in a dimension
Relationshipsdim_customer and dim_account to dim_branch, dim_account to dim_customer, fact_transaction to dim_account
Allowed valuesdirection is DR or CR
Unit testsThe fact keeps the latest event of a transaction on a first run, reads only the events after the watermark on an incremental run, keeps a delete event flagged with is_deleted, and keeps each side of each leg

Build

dbt build from the dbt folder built the staging views, the three snapshots and the four marts in dependency order: 8 models, 3 snapshots, 95 tests and 12 unit tests. The 4 unit tests of fact_transaction failed on this first build, because an incremental unit test reads the schema of the table in Snowflake and the table did not exist yet. All the other objects passed.

Terminal
dbt build

The fact was then built on its own and tested: 11 tests and 4 unit tests passed.

Terminal
dbt run -s fact_transaction
dbt test -s fact_transaction

The row count of each Silver table after the build, and the rows that are current versions or not deleted:

TableRowsCurrent / not deleted
SNAPSHOTS.snp_branches5050
SNAPSHOTS.snp_customers309309
SNAPSHOTS.snp_accounts511511
CORE.dim_branch5050
CORE.dim_customer309309
CORE.dim_account511511
CORE.fact_transaction58545852 not deleted

Jobs

  • The Silver models were first built by hand with the commands above. The daily workflow daily-dbt-staging.yml builds the staging views, then the seed, the snapshots and the marts, which are the silver models and the gold marts (Staging).
  • The daily feed job sends the transactions files with LEG_NO and DIRECTION as the second and third column (Daily Data).