Week 9: Data Transformation
ETL - Extract, Transform, Load
-
- Convert various data formats and types to one consistent system
- Migrate data into data warehouse
- Predefined process for accessing and manipulating data in loading
into target database
ETL Architecture
- Business requirements, data compliance, security, integration, end
user delivery interface, archiving, and alignment with overall
enterprise architecture
Extract
- Gather data - raw directly into disk
- Legacy operational systems, written to relational tables from
structured (specified) source
- External data purchased from 3rd party
- Like: point of sales data, online line transaction, social
media
- Cleansing data
- Eliminate duplicates/fragmented data and exclude unwanted
info
- Hae dummy variables, missing data, multipurpose fields,
cryptic, personally identifiable info, dirty data (data entry error)
- Steps in cleansing = parsing, correcting, standardizing,
matching, and consolidating
Transform
- Accordance with business rules and standards that have been
established
- EX: format changes, deduplication, split up, replace codes, derived
values
- Aggregates (totals) pre-calculated and stored in warehouse to speed
queries that require totals
Load
- Data physically move to data warehouse
