ETL & Data Integration
374 words
2 min read
Visual companion
Python
Type and operator map
Python Week 1: the first filter for runtime behavior
View
Revision summary
What this note is really saying
Short form
# ETL & Data Integration ## 🎯 Learning Objectives - Design an ETL pipeline for a business scenario - Understand extract strategies for various data sources - Apply data quality transformations - Compare ETL and ELT approaches ## 📖 Core Content ### 2.1 The ETL Pipeline *(Diagram)* ### 2.2 Extract Phase Source Type...
ETL & Data Integration
🎯 Learning Objectives
- Design an ETL pipeline for a business scenario
- Understand extract strategies for various data sources
- Apply data quality transformations
- Compare ETL and ELT approaches
📖 Core Content
2.1 The ETL Pipeline
(Diagram)
2.2 Extract Phase
| Source Type | Method | Challenges |
|---|---|---|
| Relational DB | Full/Incremental dump, CDC | Schema changes, locks |
| API | REST/SOAP calls | Rate limits, pagination |
| Flat Files | CSV, JSON, XML, Parquet | Parsing, encoding, delimiters |
| Streaming | Kafka, Kinesis | Ordering, exactly-once |
| Web Scraping | HTML parsing | Structure changes, legal |
2.3 Transform Phase
Common transformations:
- Cleaning: Remove duplicates, fix types, handle nulls
- Validation: Range checks, referential integrity, format validation
- Business logic: Calculate derived fields, aggregate
- Encoding: Convert formats, standardize units
- Surrogate keys: Generate warehouse-specific IDs
2.4 Load Phase
| Strategy | Description | Best For |
|---|---|---|
| Full load | Replace all data | Small dimensions, first load |
| Incremental load | Only changed records | Large fact tables |
| Snapshot | Periodic full copy | Slowly changing dimensions |
| SCD Type 1 | Overwrite old values | When history not needed |
| SCD Type 2 | Add new row with version | When history matters |
2.5 ETL vs ELT
| Aspect | ETL | ELT |
|---|---|---|
| Transform location | Staging server | Target warehouse |
| Speed of load | Slower (transform first) | Faster (load raw) |
| Flexibility | Less (transform decisions early) | More (transform as needed) |
| Storage cost | Less (raw data discarded) | More (raw data stored) |
| Modern tools | Informatica, SSIS | dbt, Matillion, Snowflake |
📝 Practice Questions
Q1: What is CDC (Change Data Capture)?CDC captures changes made to a source database in real-time (INSERT, UPDATE, DELETE). Instead of periodic full extracts, CDC streams changes continuously. Methods: database logs (log-based CDC), timestamps, triggers, or diff snapshots. Used for real-time data pipelines. Q2: When would you use ELT instead of ETL?ELT is preferred when: (1) the target system is a cloud data warehouse with massive compute (Snowflake, BigQuery), (2) you want to keep raw data for flexible analysis, (3) transform logic is iterative and exploratory. ETL is preferred when: (1) you need to reduce data volume before loading, (2) source data has quality issues, (3) target system has limited compute. Q3: What is a Slowly Changing Dimension (SCD) Type 2?When a dimension attribute changes (e.g., customer moves), SCD Type 2 keeps the old record and adds a new one with effective dates. This preserves historical accuracy — a sale from 2020 is linked to the customer's 2020 address, not their current one. Common implementation: add start_date, end_date, current_flag columns. Join Discord PreviousCourse OverviewNextOLAP & Cubes