Understanding advanced temporal modeling patterns step by step. In my earlier article, I explained why we combine Slowly Changing Dimension Type 2 (SCD2) with Point-in-Time (PIT) tables instead of relying only on snapshots. This piece is the technical deep dive: we’ll cover storage vs compute trade-offs, a metadata-driven approach, and the mechanics of implementation. In my earlier article, I explained why we combine Slowly Changing Dimension Type 2 (SCD2) with Point-in-Time (PIT) tables instead of relying only on snapshots. This piece is the technical deep dive: we’ll cover storage...

Why snapshots aren’t enough for consistent history tracking. When new data arrives from a source system, the simplest thing to do is to store a snapshot — a full copy of the data exactly as it looked on that day. It feels straightforward: every day, a new file, a new copy. But soon the problems appear: Storage explodes because every snapshot is nearly identical to the last. History is hard to analyze — to answer “What was this customer’s address...

Data is the lifeblood of modern organizations. Yet managing it effectively remains one of the biggest challenges. Centralized data platforms promise consistency and control, but they often become bottlenecks. Decentralized models empower teams, but can lead to fragmentation and chaos. The Federated Common Data Platform (F-CDP) offers a middle ground — combining the reliability of central governance with the agility of domain ownership. A Federated Common Data Platform is an evolution of the traditional Common...

In modern data platforms, historisation and integration layers often rely on dual SCD2 (Slowly Changing Dimension Type 2) tables to store both technical and functional timelines of data. These dual timelines allow us to track the full lineage of source data, including both business-driven reporting periods (functional timeline) and technical events such as reloads, hotfixes, or late-arriving data (technical timeline). While dual SCD2 tables are powerful for preserving history, querying across multiple timelines can be computationally expensive,...

Here’s a sample DDA template/checklist tailored for a bank: Parties Data Provider: [Business Unit / System of Record] Data Consumer: [Receiving Department / Use Case] Platform: [Common Data Platform (Data Lake / Warehouse / Hub)] Name: [e.g., “Daily Customer Master Data”] Business Purpose: [KYC, Risk Reporting, CRM, etc.] Owner / Steward: [Responsible data owner] Data Classification: Confidential / Personal / Restricted Content: [Fields, business definitions, reference data rules] Format: [Parquet, CSV, Delta Table, API feed]...

Think of a bank’s common data platform as having three governance layers: These are bank-wide rules set by the Chief Data Office (CDO), Risk, Compliance, and IT Security: BCBS 239 principles (for risk data aggregation & reporting) GDPR/Privacy policies (handling of PII, legal basis for processing) Data Management Policies (ownership, classification, retention) Security & Access Policies (least privilege, encryption, logging) These policies define what must be true for all data, regardless of source or consumer....

Roelant Vos has posted a brief status update on what’s happening in the open source #datawarehouse #automation ecosystem! The generic interface for Data Warehouse Automation had to be re-based (recreated) in GitHub due to various issues around forking and branching. The repository has been re-created with the same name and is available at https://github.com/RoelantVos/Data_Warehouse_Automation_Metadata_Interface . TEAM is nearing completion for the v1.6 release. The following Kanban board shows what’s happening for v1.6: https://github.com/RoelantVos/TEAM/projects/1 . Virtual Data Warehouse v.1.6.1...