Data Modeling - Slowly Changing Dimensions and Idempotency - Day 2 Lecture - DataExpert.io Boot Camp

TLDR;

This lecture explains slowly changing dimensions (SCDs) and their impact on data pipeline item potency. It covers why item potency is crucial for reliable data pipelines, detailing common pitfalls that compromise it, such as using "insert into" without truncate, neglecting start and end dates in queries, and failing to use a full set of partition sensors. The lecture also explores different SCD types (0, 1, 2, and 3), emphasizing the importance of Type 0 and Type 2 for maintaining data integrity and item potency.

• Item potent pipelines are critical for data quality and consistency.
• Slowly changing dimensions (SCDs) are attributes that change over time and require careful modeling.
• SCD Type 2 is the gold standard for maintaining historical data and item potency.

Intro to Slowly Changing Dimensions [0:05]

The lecture introduces the concept of slowly changing dimensions, which are attributes in a data warehouse that change over time, such as a person's favorite food or address. It emphasizes the importance of tracking these changes to maintain data accuracy and consistency. The lecture highlights that properly modeling slowly changing dimensions is crucial for ensuring item potency in data pipelines, which is the ability of a pipeline to produce the same results regardless of when or how many times it is run.

Understanding Item Potency [1:43]

Item potency is defined as the ability of data pipelines to produce consistent results regardless of when or how many times they are run, provided the inputs remain the same. The lecture stresses that pipelines should yield identical data whether executed today, next year, or in a decade, assuming all necessary inputs and signals are available. Failures in item potency can lead to data discrepancies, erode trust in data sets, and cause significant issues for analytics teams.

Common Pitfalls in Data Pipelines [4:30]

Several common mistakes can compromise item potency in data pipelines. These include using "insert into" without a truncate statement, which leads to data duplication upon multiple runs. Another pitfall is using start dates in queries without corresponding end dates, resulting in an unbounded accumulation of data over time. Additionally, the lecture warns against not using a full set of partition sensors, which can cause pipelines to run with incomplete data, and depending on past data in cumulative pipelines without proper sequential processing.

Real-World Example of Non-Item Potency [13:00]

The lecture shares a personal experience from Facebook involving a data model for tracking fake accounts. The "Dim All fake accounts" table relied on the latest data from the pipeline rather than specific daily data, leading to inconsistencies. This table sometimes used current day's data and sometimes previous day's data, depending on data availability, causing irreproducible results and significant frustration. The speaker emphasizes that prioritizing data latency over data quality can lead to severe data integrity issues and distrust in data.

Consequences of Non-Item Potent Pipelines [18:32]

Non-item potent pipelines can lead to several adverse outcomes, including backfill inconsistencies, hard-to-troubleshoot bugs, and the inability of unit tests to replicate production behavior. Silent failures and failures upon backfill or restatement are common, making data engineering more time-consuming and painful. The lecture underscores that even small oversights can have significant consequences for data quality and reliability.

Introduction to Slowly Changing Dimensions (SCDs) [20:18]

The lecture transitions to a detailed discussion of slowly changing dimensions (SCDs), which are dimensions that change over time. Examples include age, phone preference (e.g., iPhone vs. Android), and country of residence. While some dimensions like birthday remain constant, most dimensions evolve, necessitating careful modeling. The lecture also touches on rapidly changing dimensions like heart rate, noting that the slower the change, the more efficient it is to model as an SCD.

Different Approaches to Modeling Dimensions [24:01]

There are three primary ways to model dimensions: latest snapshot, daily snapshot, and slowly changing dimension. The latest snapshot only retains the current value, which can lead to item potency issues during backfills. Daily snapshot, advocated by Max, involves capturing the dimension value daily, ensuring historical accuracy but potentially increasing storage costs. Slowly changing dimension is a method of collapsing daily snapshots based on data changes, optimizing storage by storing changes only when they occur.

Why Dimensions Change [27:05]

Dimensions change for various reasons, including shifts in preferences, relocation to different countries, and changes in technology usage. The lecture uses the example of changing preferences to illustrate how dimensions can evolve over time.

Modeling Dimensions That Change [28:10]

The lecture outlines three main ways to model changing dimensions: latest snapshot, daily partition snapshots, and slowly changing dimension modeling. It strongly advises against using the latest snapshot due to its item potency issues. Daily partition snapshots, while effective, can be storage-intensive. Slowly changing dimension modeling includes Type 1, Type 2, and Type 3 approaches, each with different characteristics and trade-offs.

SCD Types: 0, 1, and 2 [29:48]

The lecture describes SCD Type 0, where the dimension is assumed not to change, making it suitable for attributes that are fixed. Type 1 involves overwriting the existing value with the new value, which is discouraged for analytical purposes due to its lack of historical data and item potency issues. Type 2, considered the gold standard, maintains a history of all changes with start and end dates, ensuring item potency and historical accuracy.

SCD Type 2 in Detail [31:47]

SCD Type 2 involves creating new records whenever a dimension changes, with each record having a start date and an end date. This method allows for accurate historical reporting and ensures item potency. The lecture mentions that Airbnb uses a distant future date (e.g., December 31, 9999) as the end date for the current value.

SCD Type 3 and Recap of SCD Types [34:19]

SCD Type 3 involves storing both the original and current values of a dimension, which is a middle ground that may not provide sufficient historical context. The lecture recaps the item potency of each type: Type 0 and Type 2 are item potent, while Type 1 and Type 3 are not. It recommends focusing on Type 0 and Type 2 for most data engineering contexts.

Loading SCD Type 2 Tables [36:58]

SCD Type 2 tables can be loaded in two ways: through one giant query that processes all historical data or incrementally, where only new data is processed each day. The lecture suggests using the incremental approach for production runs to avoid reprocessing all historical data. It also advises data engineers to prioritize tasks based on business value, rather than striving for perfect efficiency in every pipeline.