7.2 Data Warehousing Storage & Load Patterns

Data Ingestion and Transformation Patterns

In data warehousing, the ingestion process is the mechanism by which data is captured from operational sources, cleaned, integrated, and loaded into the analytical repository. The choice of ingestion pattern impacts system performance, operational windows, data latency, and storage costs. Historically, the dominant pattern has been Extract, Transform, Load (ETL). However, with the advent of high-performance cloud databases and big data platforms, Extract, Load, Transform (ELT) has emerged as a powerful alternative. Additionally, managing historical changes in dimension tables is a critical aspect of warehousing, handled through Slowly Changing Dimensions (SCD) design patterns.

ETL vs. ELT Ingestion Pipelines

Both ETL and ELT processes are designed to extract data from multiple source systems, apply business rules, and load the final structured data into a data warehouse. However, they execute these steps in a different order and utilize different computing environments.

Extract, Transform, Load (ETL)

In traditional ETL pipelines, data is extracted from source databases and immediately sent to a dedicated staging area or processing server (often a mid-tier ETL engine like Informatica, Talend, or SSIS). All transformations—such as data cleansing, deduplication, formatting, business logic validation, and key generation—occur on this external server. Once the data is transformed into its final schema, it is loaded into the target data warehouse.

• Pros: Minimizes compute and storage overhead on the target data warehouse database. Sensitive data (such as Personal Identifiable Information, or PII) can be masked or removed on the ETL server before the data is written to the warehouse. Data is cleaned before landing, reducing target database clutter.
• Cons: The ETL server represents a single point of failure and a processing bottleneck. Large volumes of data must be moved across networks twice (source to ETL server, ETL server to warehouse). Requires specialized ETL tools and skills.

Extract, Load, Transform (ELT)

In modern ELT pipelines, data is extracted from source systems and loaded directly into the target database in its raw, unmodified state (often into a raw staging schema). Once the data is safely stored in the target system, transformations are executed using the target database's own query engine (typically leveraging SQL or specialized scripting).

• Pros: Highly scalable. It utilizes the Massively Parallel Processing (MPP) capabilities of modern cloud data warehouses (e.g., Snowflake, BigQuery, Amazon Redshift). Ingestion is extremely fast because data is not blocked by a mid-tier transformation step. It supports 'schema-on-read' and allows analysts to access raw, historical data if transformation requirements change later.
• Cons: Requires high-performance target database engines, which can increase storage and compute costs. Raw data is stored within the data warehouse, demanding strict security and access controls. Poorly written transformation queries can impact concurrent analytical query performance.

The Staging Area

Regardless of whether ETL or ELT is used, a staging area is a critical component of the architecture. The staging area is a temporary storage zone where data is landed prior to transformation and loading. Its primary purposes are:

1. Decoupling: Minimizes the performance impact on operational source databases by extracting data as quickly as possible and landing it in staging, allowing source connections to be closed.
2. Consolidation: Gathers data from disparate systems (relational databases, flat files, APIs) into a single physical location.
3. Cleansing: Provides a workspace to identify corrupt records, perform data profiling, and apply standardization rules without modifying the active data warehouse.

Slowly Changing Dimensions (SCD)

Dimensions in a data warehouse represent the context of business events (e.g., who bought the product, where it was sold). Unlike fact tables, which accumulate transactions rapidly, dimension data changes slowly. When a dimension attribute changes (for example, a customer relocates or a product is re-categorized), the warehouse must handle this change systematically. The three primary patterns defined by Ralph Kimball are:

SCD Type 1: Overwrite

In an SCD Type 1 update, the old attribute value is simply overwritten with the new value. No historical record of the previous value is maintained.

• Behavior: Destructive update. The table rows remain unchanged in number, but the values in specific columns are replaced.
• Impact on Reporting: All historical sales records associated with this dimension entity will immediately be aggregated under the new attribute value. If a customer moves from Seattle to Miami, all of their past purchases will appear in reports as having occurred in Miami. Use Type 1 only for correcting errors (e.g., fixing a misspelled name) or when history is irrelevant.

SCD Type 2: Add New Row

In an SCD Type 2 update, a new row is inserted into the dimension table to represent the new state, while the old row is preserved. This is the industry standard for tracking complete, historical changes.

• Behavior: Non-destructive insert. To implement this, the table must use a surrogate key as the primary key because the natural business key (e.g., Customer ID) is no longer unique. The table also includes historical metadata columns: Effective_Date, End_Date, and Is_Current_Flag.
• Impact on Reporting: Transactions before the change remain linked to the old row, and new transactions link to the new row. Historical reports reflect the true state of the entity at the time of the transaction.

Example Table Schema for SCD Type 2 (Customer Dimension)

SCD Type 3: Add New Column

In an SCD Type 3 update, a new column is added to the dimension table to store the previous attribute value, while the current column is updated with the new value.

• Behavior: Column-level history. The table structure is modified to include columns like Current_City and Previous_City.
• Impact on Reporting: Tracks only the current and immediately preceding state. It allows analysts to write queries that report on both the current and old values simultaneously, but cannot track multiple historical transitions.

CDMP Exam Tips and Common Pitfalls

• Surrogate Keys in SCD Type 2: A recurring CDMP question focuses on why surrogate keys are required. You must understand that because Type 2 inserts new rows for the same business entity, the operational business key (natural key) is repeated. Thus, a surrogate key is mandatory to maintain entity uniqueness and join integrity with fact tables.
• ETL vs. ELT Resource Use: Understand that ETL is processed on a middleware engine and is tool-driven, whereas ELT is processed inside the database engine and is SQL-driven.