Study guide
7.2 Data Warehousing Storage & Load Patterns
DAMA Certified Data Management Professional CDMP
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & DMBoK Framework
1.1 CDMP Exam Details & Strategy 1.2 The DMBoK Framework 1.3 Data Management Organization & Roles
2 Chapter 2: Data Governance (Part A)
2.1 Data Governance Principles & Drivers 2.2 Operating Models & Structures 2.3 Policies, Standards, and Procedures
3 Chapter 2: Data Governance (Part B)
2.4 Data Stewardship 2.5 Governance Metrics & Implementation
4 Chapter 3: Data Modeling and Design (Part A)
Section 3.1: Conceptual, Logical, and Physical Models Section 3.2: ERD Notations & Relational Modeling Section 3.3: Normalization
5 Chapter 3: Data Modeling and Design (Part B)
3.4 Dimensional Modeling 3.5 Naming Standards & Modeling Best Practices
6 Chapter 4: Data Quality (Part A)
Section 4.1: Data Quality Dimensions Section 4.2: Data Quality Policy & Rules Section 4.3: DMAIC & DQ Improvement Lifecycle
7 Chapter 4: Data Quality (Part B)
4.4 Root-Cause Analysis & Remediation 4.5 DQ Metrics, Tools, and Reporting
8 Chapter 5: Metadata Management (Part A)
5.1 Business, Technical, and Operational Metadata 5.2 Metadata Lineage & Impact Analysis 5.3 Repository Architectures
9 Chapter 5: Metadata Management (Part B)
5.4 Standards and Semantics 5.5 Metadata Governance & Integration
10 Chapter 6: Master and Reference Data Management
6.1 Master vs Reference Data 6.2 MDM Repository Architectures 6.3 Match-Merge & Identity Resolution 6.4 Reference Data Governance & Standards
11 Chapter 7: Data Warehousing and Business Intelligence
7.1 DW Architectures: Inmon vs Kimball 7.2 Data Warehousing Storage & Load Patterns 7.3 BI Delivery & OLAP 7.4 Data Marts & Enterprise Data Warehouses
12 Chapter 8: Data Architecture
8.1 Enterprise Data Architecture & Zachman Framework 8.2 Data Flow Diagrams & Value Chains 8.3 Architecture Frameworks & Standards
13 Chapter 9: Document and Content Management
9.1 Records Management & ECM 9.2 Retention, Disposal, and E-Discovery 9.3 ARMA GARP Principles & Taxonomies
14 Chapter 10: Data Integration and Interoperability
Section 10.1: Integration Patterns & Technologies Section 10.2: Replication & Virtualization Section 10.3: Canonical Models & Integration Standards
15 Chapter 11: Data Security
Section 11.1: Security Principles: CIA Triad & Privacy Section 11.2: Access Control & Encryption Section 11.3: Security Regulations & Standards
16 Chapter 12: Data Storage and Operations
Section 12.1: Database Technologies & DBA Roles Section 12.2: Backup, Recovery, and High Availability Section 12.3: Performance Tuning & Operations
17 Chapter 13: Big Data and Data Science
13.1 Big Data V's & Technologies
18 Chapter 14: Data Ethics
14.1 DMBoK Data Ethics Framework
19 Chapter 15: Data Management Process and Maturity
15.1 Maturity Assessment Frameworks
7.2 Data Warehousing Storage & Load Patterns
Key Takeaways Explain This Topic AI
ETL transforms data on an external server before loading, while ELT loads raw data directly and transforms it using the target database's compute power (MPP).
The staging area is a temporary storage zone that decouples source systems, consolidates data, and cleanses it without affecting operational system performance.
Slowly Changing Dimension (SCD) Type 1 overwrites old values and destroys historical records, whereas SCD Type 2 creates a new row with a surrogate key to preserve full history.
SCD Type 2 requires a surrogate key because natural business keys are repeated across historical rows, preventing them from serving as unique primary keys.
Last updated: July 2026
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:
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.
Consolidation : Gathers data from disparate systems (relational databases, flat files, APIs) into a single physical location.
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)
- Surrogate Key Customer ID Name City Effective Date End Date Is Current
- 101 C-5001 Jane Doe Seattle 2024-01-01 2026-07-17 False
- 102 C-5001 Jane Doe Miami 2026-07-18 NULL True
- 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.