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.
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).
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.
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]| Snapshot | Unique key | Source | Rows |
|---|---|---|---|
| snp_branches | branch_code | stg_core_banking__branches | 50 |
| snp_customers | customer_id | stg_core_banking__customers | 309 |
| snp_accounts | account_id | stg_core_banking__accounts | 511 |
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.
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') }}| Model | Built from | Surrogate key | Business key | Rows |
|---|---|---|---|---|
| dim_branch | snp_branches | branch_key | branch_code | 50 |
| dim_customer | snp_customers | customer_key | customer_id | 309 |
| dim_account | snp_accounts | account_key | account_id | 511 |
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.
{{
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_id | leg_no | direction | account_id | transaction_type | amount |
|---|---|---|---|---|---|
| TXN000004421 | 1 | CR | ACC0000317 | TRANSFER_IN | 17487000 |
| TXN000004421 | 1 | DR | ACC0000345 | TRANSFER_OUT | 17487000 |
| TXN000004421 | 2 | DR | ACC0000345 | FEE | 21000 |
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
| Kind | What is checked |
|---|---|
| Keys | unique 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 version | one_current_row_per_key: a custom generic test, no business key has two current versions in a dimension |
| Relationships | dim_customer and dim_account to dim_branch, dim_account to dim_customer, fact_transaction to dim_account |
| Allowed values | direction is DR or CR |
| Unit tests | The 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.
dbt buildThe fact was then built on its own and tested: 11 tests and 4 unit tests passed.
dbt run -s fact_transaction
dbt test -s fact_transactionThe row count of each Silver table after the build, and the rows that are current versions or not deleted:
| Table | Rows | Current / not deleted |
|---|---|---|
| SNAPSHOTS.snp_branches | 50 | 50 |
| SNAPSHOTS.snp_customers | 309 | 309 |
| SNAPSHOTS.snp_accounts | 511 | 511 |
| CORE.dim_branch | 50 | 50 |
| CORE.dim_customer | 309 | 309 |
| CORE.dim_account | 511 | 511 |
| CORE.fact_transaction | 5854 | 5852 not deleted |
Jobs
- The Silver models were first built by hand with the commands above. The daily workflow
daily-dbt-staging.ymlbuilds 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_NOandDIRECTIONas the second and third column (Daily Data).