book

The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data

Name: The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data
ISBN: 9780764567575

by Ralph Kimball, Joe Caserta

October 2004

Beginner to intermediate

528 pages

13h 39m

English

Wiley

Read now

Unlock full access

Copyright
Credits
Acknowledgments
About the Authors
Introduction
I. Requirements, Realities, and Architecture
1. Surrounding the Requirements
1.1. Requirements1.1.1. Business Needs1.1.2. Compliance Requirements1.1.3. Data Profiling1.1.4. Security Requirements1.1.5. Data Integration1.1.6. Data Latency1.1.7. Archiving and Lineage1.1.8. End User Delivery Interfaces1.1.9. Available Skills1.1.10. Legacy Licenses1.2. Architecture1.2.1. ETL Tool versus Hand Coding (Buy a Tool Suite or Roll Your Own?)1.2.2. The Back Room – Preparing the Data1.2.3. The Front Room – Data Access1.3. The Mission of the Data Warehouse1.3.1. What the Data Warehouse Is1.3.2. What the Data Warehouse Is Not1.3.3. Industry Terms Not Used Consistently1.3.4. Resolving Architectural Conflict: The Hybrid Bus Approach1.3.5. How the Data Warehouse Is Changing1.4. The Mission of the ETL Team
2. ETL Data Structures
2.1. To Stage or Not to Stage2.2. Designing the Staging Area2.3. Data Structures in the ETL System2.3.1. Flat Files2.3.2. XML Data Sets2.3.3. Relational Tables2.3.4. Independent DBMS Working Tables2.3.5. Third Normal Form Entity/Relation Models2.3.6. Nonrelational Data Sources2.3.7. Dimensional Data Models: The Handoff from the Back Room to the Front Room2.3.8. Fact Tables2.3.9. Dimension Tables2.3.10. Atomic and Aggregate Fact Tables2.3.11. Surrogate Key Mapping Tables2.4. Planning and Design Standards2.4.1. Impact Analysis2.4.2. Metadata Capture2.4.3. Naming Conventions2.4.4. Auditing Data Transformation Steps2.5. Summary
II. Data Flow
3. Extracting
3.1. Part 1: The Logical Data Map3.1.1. Designing Logical Before Physical3.2. Inside the Logical Data Map3.2.1. Components of the Logical Data Map3.2.2. Using Tools for the Logical Data Map3.3. Building the Logical Data Map3.3.1. Data Discovery Phase3.3.2. Data Content Analysis3.3.3. Collecting Business Rules in the ETL Process3.4. Integrating Heterogeneous Data Sources3.4.1. Part 2: The Challenge of Extracting from Disparate Platforms3.4.2. Connecting to Diverse Sources through ODBC3.5. Mainframe Sources3.5.1. Working with COBOL Copybooks3.5.2. EBCDIC Character Set3.5.3. Converting EBCDIC to ASCII3.5.4. Transferring Data between Platforms3.5.5. Handling Mainframe Numeric Data3.5.6. Using PICtures3.5.7. Unpacking Packed Decimals3.5.8. Working with Redefined Fields3.5.9. Multiple OCCURS3.5.10. Managing Multiple Mainframe Record Type Files3.5.11. Handling Mainframe Variable Record Lengths3.6. Flat Files3.6.1. Processing Fixed Length Flat Files3.6.2. Processing Delimited Flat Files3.7. XML Sources3.7.1. Character Sets3.7.2. XML Meta Data3.8. Web Log Sources3.8.1. W3C Common and Extended Formats3.8.2. Name Value Pairs in Web Logs3.9. ERP System Sources3.10. Part 3: Extracting Changed Data3.10.1. Detecting Changes3.10.2. Extraction Tips3.10.3. Detecting Deleted or Overwritten Fact Records at the Source3.11. Summary

4. Cleaning and Conforming
4.1. Defining Data Quality4.2. Assumptions4.3. Part 1: Design Objectives4.3.1. Understand Your Key Constituencies4.3.2. Competing Factors4.3.3. Balancing Conflicting Priorities4.3.4. Formulate a Policy4.4. Part 2: Cleaning Deliverables4.4.1. Data Profiling Deliverable4.4.2. Cleaning Deliverable #1: Error Event Table4.4.3. Cleaning Deliverable #2: Audit Dimension4.4.4. Audit Dimension Fine Points4.5. Part 3: Screens and Their Measurements4.5.1. Anomaly Detection Phase4.5.2. Types of Enforcement4.5.3. Column Property Enforcement4.5.4. Structure Enforcement4.5.5. Data and Value Rule Enforcement4.5.6. Measurements Driving Screen Design4.5.7. Overall Process Flow4.5.8. The Show Must Go On—Usually4.5.9. Screens4.5.10. Known Table Row Counts4.5.11. Column Nullity4.5.12. Column Numeric and Date Ranges4.5.13. Column Length Restriction4.5.14. Column Explicit Valid Values4.5.15. Column Explicit Invalid Values4.5.16. Checking Table Row Count Reasonability4.5.17. Checking Column Distribution Reasonability4.5.18. General Data and Value Rule Reasonability4.6. Part 4: Conforming Deliverables4.6.1. Conformed Dimensions4.6.2. Designing the Conformed Dimensions4.6.3. Taking the Pledge4.6.4. Permssible Variation of Conformed Dimensions4.6.5. Conformed Facts4.6.6. The Fact Table Provider4.6.7. The Dimension Manager: Publishing Conformed Dimensions to Affected Fact Tables4.6.8. Detailed Delivery Steps for Conformed Dimensions4.6.9. Implementing the Conforming Modules4.6.10. Matching Drives Deduplication4.6.11. Surviving: Final Step of Conforming4.6.12. Delivering4.7. Summary
5. Delivering Dimension Tables
5.1. The Basic Structure of a Dimension5.2. The Grain of a Dimension5.3. The Basic Load Plan for a Dimension5.4. Flat Dimensions and Snowflaked Dimensions5.5. Date and Time Dimensions5.6. Big Dimensions5.7. Small Dimensions5.8. One Dimension or Two5.9. Dimensional Roles5.10. Dimensions as Subdimensions of Another Dimension5.11. Degenerate Dimensions5.12. Slowly Changing Dimensions5.13. Type 1 Slowly Changing Dimension (Overwrite)5.14. Type 2 Slowly Changing Dimension (Partitioning History)5.15. Precise Time Stamping of a Type 2 Slowly Changing Dimension5.16. Type 3 Slowly Changing Dimension (Alternate Realities)5.17. Hybrid Slowly Changing Dimensions5.18. Late-Arriving Dimension Records and Correcting Bad Data5.19. Multivalued Dimensions and Bridge Tables5.20. Ragged Hierarchies and Bridge Tables5.21. Technical Note: POPULATING HIERARCHY BRIDGE TABLES5.22. Using Positional Attributes in a Dimension to Represent Text Facts5.23. Summary
6. Delivering Fact Tables
6.1. The Basic Structure of a Fact Table6.2. Guaranteeing Referential Integrity6.3. Surrogate Key Pipeline6.3.1. Using the Dimension Instead of a Lookup Table6.4. Fundamental Grains6.5. Transaction Grain Fact Tables6.5.1. Periodic Snapshot Fact Tables6.5.2. Accumulating Snapshot Fact Tables6.6. Preparing for Loading Fact Tables6.6.1. Managing Indexes6.6.2. Managing Partitions6.6.3. Outwitting the Rollback Log6.6.4. Loading the Data6.6.5. Incremental Loading6.6.6. Inserting Facts6.6.7. Updating and Correcting Facts6.6.8. Negating Facts6.6.9. Updating Facts6.6.10. Deleting Facts6.6.11. Physically Deleting Facts6.6.12. Logically Deleting Facts6.7. Factless Fact Tables6.8. Augmenting a Type 1 Fact Table with Type 2 History6.9. Graceful Modifications6.10. Multiple Units of Measure in a Fact Table6.11. Collecting Revenue in Multiple Currencies6.12. Late Arriving Facts6.13. Aggregations6.13.1. Design Requirement #16.13.2. Design Requirement #26.13.3. Design Requirement #36.13.4. Design Requirement #46.13.5. Administering Aggregations, Including Materialized Views6.14. Delivering Dimensional Data to OLAP Cubes6.14.1. Cube Data Sources6.14.2. Processing Dimensions6.14.3. Changes in Dimension Data6.14.4. Processing Facts6.14.5. Integrating OLAP Processing into the ETL System6.14.6. OLAP Wrap-up6.15. Summary
III. Implementation and Operations
7. Development
7.1. Current Marketplace ETL Tool Suite Offerings7.2. Current Scripting Languages7.3. Time Is of the Essence7.3.1. Push Me or Pull Me7.3.2. Ensuring Transfers with Sentinels7.3.3. Sorting Data during Preload7.3.4. Sorting on Mainframe Systems7.3.5. Sorting on Unix and Windows Systems7.3.6. Trimming the Fat (Filtering)7.3.7. Extracting a Subset of the Source File Records on Mainframe Systems7.3.8. Extracting a Subset of the Source File Fields7.3.9. Extracting a Subset of the Source File Records on Unix and Windows Systems7.3.10. Extracting a Subset of the Source File Fields7.3.11. Creating Aggregated Extracts on Mainframe Systems7.3.12. Creating Aggregated Extracts on UNIX and Windows Systems7.4. Using Database Bulk Loader Utilities to Speed Inserts7.4.1. Preparing for Bulk Load7.5. Managing Database Features to Improve Performance7.5.1. The Order of Things7.5.2. The Effect of Aggregates and Group Bys on Performance7.5.3. Performance Impact of Using Scalar Functions7.5.4. Avoiding Triggers7.5.5. Overcoming ODBC the Bottleneck7.5.6. Benefiting from Parallel Processing7.6. Troubleshooting Performance Problems7.7. Increasing ETL Throughput7.7.1. Reducing Input/Output Contention7.7.2. Eliminating Database Reads/Writes7.7.3. Filtering as Soon as Possible7.7.4. Partitioning and Parallelizing7.7.5. Updating Aggregates Incrementally7.7.6. Taking Only What You Need7.7.7. Bulk Loading/Eliminating Logging7.7.8. Dropping Databases Constraints and Indexes7.7.9. Eliminating Network Traffic7.7.10. Letting the ETL Engine Do the Work7.8. Summary
8. Operations
8.1. Scheduling and Support8.1.1. Reliability, Availability, Manageability Analysis for ETL8.1.2. Etl Scheduling 1018.1.3. Scheduling Tools8.1.4. Load Dependencies8.1.5. Metadata8.2. Migrating to Production8.2.1. Operational Support for the Data Warehouse8.2.2. Bundling Version Releases8.2.3. Supporting the ETL System in Production8.3. Achieving Optimal ETL Performance8.3.1. Estimating Load Time8.3.2. Vulnerabilities of Long-Running ETL processes8.3.3. Minimizing the Risk of Load Failures8.4. Purging Historic Data8.5. Monitoring the ETL System8.5.1. Measuring ETL Specific Performance Indicators8.5.2. Measuring Infrastructure Performance Indicators8.5.3. Measuring Data Warehouse Usage to Help Manage ETL Processes8.6. Tuning ETL Processes8.6.1. Explaining Database Overhead8.7. ETL System Security8.7.1. Securing the Development Environment8.7.2. Securing the Production Environment8.8. Short-Term Archiving and Recovery8.9. Long-Term Archiving and Recovery8.9.1. Media, Formats, Software, and Hardware8.9.2. Obsolete Formats and Archaic Formats8.9.3. Hard Copy, Standards, and Museums8.9.4. Refreshing, Migrating, Emulating, and Encapsulating8.10. Summary
9. Metadata
9.1. Defining Metadata9.1.1. Metadata—What Is It?9.1.2. Source System Metadata9.1.3. Data-Staging Metadata9.1.4. DBMS Metadata9.1.5. Front Room Metadata9.2. Business Metadata9.2.1. Business Definitions9.2.2. Source System Information9.2.3. Data Warehouse Data Dictionary9.2.4. Logical Data Maps9.3. Technical Metadata9.3.1. System Inventory9.3.2. Data Models9.3.3. Data Definitions9.3.4. Business Rules9.4. ETL-Generated Metadata9.4.1. ETL Job Metadata9.4.2. Transformation Metadata9.4.3. Batch Metadata9.4.4. Data Quality Error Event Metadata9.4.5. Process Execution Metadata9.5. Metadata Standards and Practices9.5.1. Establishing Rudimentary Standards9.5.2. Naming Conventions9.6. Impact Analysis9.7. Summary
10. Responsibilities
10.1. Planning and Leadership10.1.1. Having Dedicated Leadership10.1.2. Planning Large, Building Small10.1.3. Hiring Qualified Developers10.1.4. Building Teams with Database Expertise10.1.5. Don't Try to Save the World10.1.6. Enforcing Standardization10.1.7. Monitoring, Auditing, and Publishing Statistics10.1.8. Maintaining Documentation10.1.9. Providing and Utilizing Metadata10.1.10. Keeping It Simple10.1.11. Optimizing Throughput10.2. Managing the Project10.2.1. Responsibility of the ETL Team10.2.2. Defining the Project10.2.3. Planning the Project10.2.4. Determining the Tool Set10.2.5. Staffing Your Project10.2.6. Project Plan Guidelines10.2.7. Managing Scope10.3. Summary
IV. Real Time Streaming ETL Systems
11. Real-Time ETL Systems
11.1. Why Real-Time ETL?11.2. Defining Real-Time ETL11.3. Challenges and Opportunities of Real-Time Data Warehousing11.4. Real-Time Data Warehousing Review11.4.1. Generation 1—The Operational Data Store11.4.2. Generation 2—The Real-Time Partition11.4.3. Recent CRM Trends11.4.4. The Strategic Role of the Dimension Manager11.5. Categorizing the Requirement11.5.1. Data Freshness and Historical Needs11.5.2. Reporting Only or Integration, Too?11.5.3. Just the Facts or Dimension Changes, Too?11.5.4. Alerts, Continuous Polling, or Nonevents?11.5.5. Data Integration or Application Integration?11.5.6. Point-to-Point versus Hub-and-Spoke11.5.7. Customer Data Cleanup Considerations11.6. Real-Time ETL Approaches11.6.1. Microbatch ETL11.6.2. Enterprise Application Integration11.6.3. Capture, Transform, and Flow11.6.4. Enterprise Information Integration11.6.5. The Real-Time Dimension Manager11.6.6. Microbatch Processing11.6.7. Choosing an Approach—A Decision Guide11.7. Summary
12. Conclusions
12.1. Deepening the Definition of ETL12.2. The Future of Data Warehousing and ETL in Particular12.2.1. Ongoing Evolution of ETL Systems

Overview

Cowritten by Ralph Kimball, the world's leading data warehousing authority, whose previous books have sold more than 150,000 copies
Delivers real-world solutions for the most time- and labor-intensive portion of data warehousing-data staging, or the extract, transform, load (ETL) process
Delineates best practices for extracting data from scattered sources, removing redundant and inaccurate data, transforming the remaining data into correctly formatted data structures, and then loading the end product into the data warehouse
Offers proven time-saving ETL techniques, comprehensive guidance on building dimensional structures, and crucial advice on ensuring data quality

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Building a Scalable Data Warehouse with Data Vault 2.0

Publisher Resources

ISBN: 9780764567575Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills