Ísis Santos Costa’s Reviews > The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling > Status Update

p. 518
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop Incremental ETL Processing
... ... ↳ Step 8: Fact Table Incremental Processing
... ... ... ↳ Late Arriving Facts & the Surrogate Key Pipeline

If SCD Type 2 attributes are involved, a lookup must be performed using valid from / to information. Even fresh, this not necessarily is the currently valid information.

p. 516
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop Incremental ETL Processing
... ... ↳ Step 8: Fact Table Incremental Processing
... ... ... ↳ FT Transformations & Surrogate Key Pipeline

Handling referential integrity violations:
✗ Halt the load
✓ Throw away (when not relevant)
✗ Write errors file
✓ Create dummy dim + surrogate key
✗ Map to single unknown dim

p. 516
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop Incremental ETL Processing (cont.)
... ... ↳ Step 8: Fact Table Incremental Processing (cont.)
... ... ... ↳ Fact Table Extract & Data Quality Checkpoint

« Data is written to a staging area.
Data Quality metrics of the raw data are computed. »

p. 515
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop Incremental ETL Processing (cont.)
... ... ↳ Step 8: Fact Table Incremental Processing

You can stop a historic load process;
« the incremental processing, by contrast, must be fully automated. »

p. 514
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop Incremental ETL Processing
... ... ↳ Step 7: Dimension Table Incremental Processing
... ... ... ↳ Dimension Table Extracts
... ... ... ↳ Identify New and Changed Dimension Rows
... ... ... ↳ Process Changes to Dimension Attributes

p. 511
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 6: Perform the Fact Table Historic Load (cont.)
... ... ... ↳ Fact Table Transformations
➤ Null Fact Values
➤ Improve Fact Table Content
➤ Pipeline the Dimension Surrogate Key Lookup
➤ Assign Audit Dimension Key

p. 509
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 6: Perform the Fact Table Historic Load (cont.)
... ... ... ↳ Fact Table Transformations

« A lot of time (is spent) improving the dimension table, facts usually require modest transformation. »

System values like -1 shall be replaced by NULL. All FKs be NOT NULL.

p. 508
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 6: Perform the Fact Table Historic Load
... ... ... ↳ Historic Fact Table Extracts
... ... ... ↳ Audit Statistics

It is not always possible to tie the data back to source systems; in these cases, it is crucial that reasons for differences be documented clearly.

p. 507
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 5: Populate Dim Tables w Historic Data (cont.)
... ... ... ↳ Dim Table Loading
... ... ... ↳ Load Type 2 Dimension Table History
... ... ... ↳ Populate Date and Other Static Dimensions

p. 506
20 • ETL System Design + Dev Process & Tasks (cont.)
↳ ETL Process Overview (cont.)
... ↳ Develop One-Time Historic Load Processing
... ... ↳ Step 5: Populate Dim Tables w Historic Data
... ... ... ↳ Populate Type 1 Dim Tables
... ... ... ↳ Dim Transformations
➤ Simple Data Transformations
➤ Combine from Separate Sources
➤ Decode Production Codes
➤ Validate M2M one-to-one
➤ Dimension Surrogate Key Assignment