Understanding Slowly Changing Dimensions in Data Engineering

The lecture begins with an introduction to Slowly Changing Dimensions (SCDs), which are attributes that change over time. The speaker illustrates this concept using a personal example of their favorite food, which transitioned from lasagna in the early 2000s to curry after moving to the Bay Area. This highlights the need to track changes in dimensions over time, as some attributes, like a birthday, remain constant.

The speaker emphasizes the critical nature of item potency in data pipelines, defining it as the ability for pipelines to produce consistent results regardless of when or how many times they are run. This property is essential for maintaining data quality and avoiding discrepancies in analytics. The speaker encourages further research on item potency, suggesting that mastering this concept can significantly enhance one's skills as a data engineer and potentially lead to better financial opportunities.

Item potency is defined as an element of a set that remains unchanged when operated on by itself. The speaker relates this definition to data engineering, explaining that pipelines should yield the same results irrespective of the time of execution. This consistency is likened to a mathematical function, where specific inputs should always produce the same outputs, underscoring the importance of adhering to best practices in pipeline construction.

The speaker warns of the potential issues arising from non-item potent pipelines, where running a pipeline at different times could yield varying results, even without code changes. This inconsistency can lead to significant challenges for analytics teams, including data discrepancies and confusion over mismatched numbers. The speaker stresses the importance of following established rules to avoid such 'pain and suffering' in data management.

The speaker emphasizes the importance of item potency in data pipelines, explaining that a non-item potent pipeline does not fail but produces non-reproducible data. This inconsistency can lead to confusion among data analysts, who may question why data appears different across tables. The speaker warns that if downstream data engineers rely on non-item potent data, it can cause inconsistencies to propagate throughout the entire data warehouse, ultimately eroding trust in the data sets.

The speaker discusses the pitfalls of using 'insert into' without a truncate statement, which can lead to data duplication and non-item potency. They advocate for using 'merge' and 'insert overwrite' as better alternatives. 'Merge' allows for the integration of new data with existing data without creating duplicates, while 'insert overwrite' replaces existing data in a partition, ensuring that the data remains consistent regardless of how many times the pipeline is run. This approach is particularly relevant in the context of Hive metastore and Hadoop, where maintaining item potency is crucial.

The speaker highlights a common issue in data pipelines related to date filtering, specifically when using a 'where' clause that specifies a start date without a corresponding end date. For instance, if a pipeline is set to filter data where the date is greater than yesterday, running the pipeline multiple times can yield different results, further complicating the issue of data consistency. This underscores the need for careful consideration of date parameters in data processing.

The discussion begins with the importance of timing in data pipelines. If a pipeline is run today, it processes one day of data; if run tomorrow, it processes two days, and so forth. This variability in data processed based on the run date creates a non-idempotent situation, where the results differ depending on when the pipeline is executed. To mitigate this, it is crucial to define a specific time window for data processing, such as processing a fixed number of days (e.g., six or seven days), rather than allowing for an unbounded number of days, which can lead to complications and potential out-of-memory exceptions during backfilling.

Another significant issue arises from not using a complete set of partition sensors. If a pipeline runs prematurely, before all necessary input data is available, it may lead to incomplete processing. This can result in discrepancies between backfilled data and production runs, as the pipeline may initially execute with missing inputs. Ensuring that all inputs are ready before execution is essential to maintain consistency and accuracy in the data pipeline's output.

The concept of 'depends on past' is introduced, highlighting the challenges of cumulative pipelines that rely on previous data. Such pipelines cannot run in parallel; they must execute sequentially—yesterday's data must be processed before today's, and so on. This sequential dependency can lead to issues if yesterday's data is not yet available when today's processing begins, potentially resulting in incomplete data sets. The speaker emphasizes the importance of ensuring that cumulative pipelines are designed to handle these dependencies effectively to maintain idempotency between production and backfill processes.

The speaker reiterates the significance of item potency in data pipelines, stressing that production and backfill behaviors should yield the same results. This consistency is crucial for reliable data processing. The discussion transitions to a personal experience at Facebook, where the speaker faced challenges related to tracking fake accounts. This experience underscored the complexities involved in maintaining accurate data models, particularly when accounts could be labeled as fake or not based on their behavior, leading to potential false positives and the need for continuous monitoring.

The discussion begins with the complexities of managing fake account data, highlighting the existence of various states for fake accounts. A chart was developed to track the inflows and outflows of these accounts, utilizing a table named 'Dim All Fake Accounts.' This table was not idempotent, meaning it relied on the latest data from the pipeline rather than today's data. The urgency for quick data availability led to this approach, which the speaker warns is a dangerous shortcut for data engineers, as it can lead to significant data quality issues.

The speaker elaborates on the problems arising from the reliance on the latest data. The 'Dim All Fake Accounts' table sometimes pulled data from 'Dim All Users' that was outdated, leading to inconsistencies in the data. This inconsistency caused analytical discrepancies, as the analytics team questioned the reproducibility of the numbers. The speaker, feeling frustrated and inadequate as an engineer, spent over a month trying to resolve these discrepancies, only to discover that the issue stemmed from the upstream team's data handling.

The frustration from dealing with non-idempotent pipelines and data quality bugs ultimately led the speaker to quit Facebook six to seven weeks later. This experience underscores the potential consequences of poor data management practices, which can drive skilled data engineers away from their roles due to the stress and challenges posed by unreliable data systems.

An exception to the rule of avoiding reliance on the latest data is noted when backfilling data with a properly modeled Slowly Changing Dimension (SCD) table. The speaker emphasizes that this is the only scenario where using the latest data is acceptable, contrasting it with the general guideline that all other datasets should not follow this practice to maintain data integrity.

The speaker concludes by discussing the potential for cumulative bugs introduced by non-idempotent pipelines. These bugs can result in incorrect data being presented, depending on whether the data is backfilled or in production. This highlights the critical need for robust data management practices to prevent such issues from arising.

The speaker discusses the challenges of maintaining a cumulative pipeline that relies on inconsistent data, emphasizing that such a pipeline perpetuates errors daily. This leads to significant difficulties when attempting to backfill or troubleshoot data, particularly if the pipeline is not item potent. The speaker reflects on their experiences at Facebook, highlighting the importance of ensuring that pipelines are designed to be item potent to avoid painful data overwrites and silent failures.

The speaker introduces the concept of slowly changing dimensions (SCDs), which are dimensions that evolve over time. They provide relatable examples, such as age, which changes annually, and personal technology preferences, like transitioning from Android to iPhone. The speaker notes that while some dimensions, like age and country of residence, are subject to change, others, such as birth date and eye color, remain constant. They also touch on rapidly changing dimensions, like heart rate, which can fluctuate minute by minute, and stress the importance of modeling these changes efficiently, with slower changes yielding better efficiency gains.

The discussion begins with a reference to Max, a friend and former colleague at Facebook, who created Apache Airflow. Max argues against the use of slowly changing dimensions (SCDs), claiming they are inherently non-idempotent. He advocates for a functional data engineering approach, treating data pipelines as functions with clear inputs and outputs. Max believes that instead of modeling dimensions with SCDs, one should simply record daily values, as storage costs are low, and the costs associated with data quality issues are high. This perspective emphasizes the importance of simplicity and efficiency in data modeling.

The speaker outlines three methods for modeling dimensions. The first method is the 'latest snapshot,' which captures only the current value of a dimension. This approach can lead to issues, such as misrepresenting historical data during backfills, as it may inaccurately reflect a person's age at a given time. The second method, favored by Max, is the 'daily snapshot,' which records daily values, ensuring that historical data accurately reflects the state of the dimension at that time. This prevents potential misinterpretations, such as the speaker being misrepresented as an adult when they were actually a teenager. The third method involves collapsing daily snapshots into a single row for each period where the dimension remains unchanged, allowing for significant data compression. This method is particularly effective for slowly changing dimensions, as it reduces the number of rows while maintaining accurate historical context.

The speaker highlights various factors that contribute to changes in dimensions, including personal preferences, changes in location, and technology upgrades, such as switching phones. These changes necessitate a flexible approach to data modeling to accurately reflect the evolving nature of the data.

The speaker reflects on their time at Netflix, where a majority of employees displayed either 'catflix' or 'dog flicks' stickers on their laptops. Initially, the speaker identified with 'catflix' but has since transitioned to 'dog flicks,' illustrating a personal evolution in preferences. They emphasize that such changes in personal dimensions can occur for various reasons and may continue to evolve over time.

The speaker outlines three primary methods for modeling dimensions that change over time. They caution against backfilling data with only the latest values, as this can lead to inaccuracies in older data. Instead, they recommend using daily partition snapshots, which provide a straightforward approach by capturing daily values. Additionally, they introduce the concept of slowly changing dimensions (SCD), which includes three types: type one, type two, and type three, with type zero also mentioned as a fixed model.

The speaker elaborates on the different types of slowly changing dimensions. Type zero is suitable for dimensions that are fixed and unlikely to change, requiring no temporal component. Type one, which the speaker dislikes, only retains the latest value, disregarding historical data. While this may be acceptable in online transactional processing (OLTP) contexts, it poses significant challenges for data engineers focused on analytics, as it disrupts the item potency of data pipelines. Type two, referred to as the 'gold standard' by Airbnb, involves tracking changes with start and end dates, allowing for a more comprehensive historical record.

The discussion begins with an overview of Slowly Changing Dimensions (SCD) type 2, highlighting its effectiveness in tracking changes over time. The speaker illustrates this with an example of lasagna being the primary dish from 2000 to 2010, which then transitioned to curry from 2010 to 2015. This method allows for the retention of historical values without losing clarity, as it enables users to reference specific values based on defined start and end dates. The speaker emphasizes the importance of these dimensions in data management, noting their item potency and the ability to accurately reflect past states.

The speaker explains how Airbnb manages current values within their data structure, using a start date that marks when a change occurs and an end date set far into the future, specifically December 31, 9999. This approach has sparked debate among data professionals, with some advocating for the use of null values instead of a distant end date. Additionally, SCD type 2 often includes a Boolean column indicating whether a value is current, which adds another layer of clarity to the data.

Transitioning to SCD type 3, the speaker describes a method that retains only the original and current values, omitting historical data. This approach is seen as a compromise, as it fails to capture changes that occur more than once, leading to potential data loss. The speaker notes that while this method simplifies data management by maintaining a single row per dimension, it compromises clarity and can produce incorrect results during backfilling due to the lack of historical context.

In summarizing the discussion, the speaker categorizes SCD types based on their item potency. Type 0 is deemed item potent only if the dimension is unchanging, while type 2 is generally item potent but requires careful management of start and end dates. Type 1 is not item potent, as it can lead to inaccuracies when backfilling data. Type 3 is positioned as a middle ground but still lacks the desired clarity. The speaker concludes that only types 0 and 2 are truly essential for effective data management, while acknowledging the existence of other types (4, 5, and 6) that are rarely utilized in practice.

The discussion shifts to the two primary methods for loading slowly changing dimension (SCD) tables: the first method involves executing a single comprehensive query that processes all daily data at once, while the second method entails an incremental approach, where only the new data from the current day is processed alongside the previous day's data. The speaker emphasizes that the incremental method is generally preferred for production runs, as it avoids the need to reprocess historical data continuously.

Reflecting on his experience at Airbnb, the speaker shares insights about a pipeline he worked on related to unit economics, which utilized an SCD table. This table tracked changes in transaction values over time, such as when a customer initially pays but later receives a refund, thus altering the profit calculation. He expresses a desire to implement incremental loading for this pipeline but acknowledges that the existing method of processing all historical transactions since 2016 was sufficient, despite the inefficiency it entailed.

The speaker highlights a crucial lesson for data engineers: not every pipeline needs to be optimized to perfection. He reflects on the opportunity cost of spending excessive time on marginal improvements, suggesting that focusing on more impactful projects, such as pricing and availability, can yield greater value for the business. This perspective encourages engineers to balance efficiency with the overall priorities of their work, reminding them that sometimes, the most significant contributions come from addressing broader business needs rather than perfecting every technical detail.