แผนการเรียนรู้
/

Week 9: ETL & Modern
Data Engineering

Building the Lifeline of the Data Warehouse: From Static Pipelines to Agentic Workflows

Project Milestone

Phase 3: Automated Pipeline Development

"The ETL system is the back room of the data warehouse, where raw data is refined into valuable assets"

Learning Outcomes

1
Explain the 3 main stages of the ETL process: Extraction, Transformation, and Loading
2
Differentiate between traditional ETL and modern Cloud-native ELT architectures
3
Apply data cleaning and conforming techniques to ensure data quality
4
Integrate Agentic AI for autonomous, self-healing pipeline monitoring

Part 1: The Traditional ETL Paradigm

"Making data usable"

ETL is the process of extracting data from source systems, transforming it into a common format, and loading it into the target data warehouse.

Challenges of Data Extraction

Heterogeneous Sources

Connecting to RDBMS, XML, Flat Files, and ERP systems simultaneously.

CDC (Change Data Capture)

Identifying only new or modified records to reduce processing time.

Source System Impact

Minimizing the performance overhead on production systems during extract.

Data Cleaning: Ensuring Integrity

"Quality is the most visible metric of ETL success"

⚠️
**Parsing:** Breaking single fields (e.g., Address) into structured components.
🔄
**Deduplication:** Identifying and merging duplicate records from different systems.
📏
**Standardization:** Converting character sets (EBCDIC to ASCII) and units.
Group Activity (20 min)

Designing a Logical Data Map

Scenario: Customer Ingestion

You have two source systems: **CRM (Oracle)** and **Web App (CSV)**. Create a map for the `Dim_Customer` table.

**CRM Columns:** `cust_id`, `fname`, `lname`, `addr_line`
**Web Columns:** `email`, `full_name`, `signup_date`

Task: Identify 3 transformation rules needed to merge these into one dimension.

Solution 1: Step-by-Step Data Map

Step 1: Entity Identification

Use **Email** as the natural key to link CRM records to Web App records.

Step 2: String Concatenation

Merge `CRM.fname` + `CRM.lname` to match the format of `Web.full_name`.

Step 3: Surrogate Key Generation

Assign a unique integer `Customer_Key` to decouple the warehouse from source IDs.

Reference: **The Surrogate Key Pipeline**

Part 2: The ELT & Cloud Evolution

"Flip the script"

ELT leverages the massive compute power of modern cloud data warehouses to transform data *after* it is loaded.

ETL vs. ELT Comparison

Traditional ETL

  • • **Transform:** Happens in a staging area (outside the DB).
  • • **Load:** Only cleaned data enters the warehouse.
  • • **Pros:** Efficient storage, robust quality control.
  • • **Cons:** Scalability bottleneck as data grows.

Modern ELT

  • • **Load:** Raw data is loaded into the warehouse first.
  • • **Transform:** Scalable cloud compute does the work.
  • • **Pros:** Infinite scalability, supports ML/AI directly.
  • • **Cons:** Requires strict data governance.

Citation: **ETL vs ELT Evolution**

Medallion Architecture

The standard for organizing data in modern Lakehouses:

Bronze (Raw)
Silver (Cleaned)
Gold (Business)
  • • **Bronze:** Landing zone for raw data (e.g., Kaggle Olist JSON).
  • • **Silver:** Conformed dimensions and facts (Kimball compliant).
  • • **Gold:** Aggregated tables ready for AI Agents and BI dashboards.

Read more: **Medallion Architecture Guide**

Part 3: Agentic AI & Autonomous Data Engineering

"Self-healing pipelines"

AI Agents are transforming ETL from static scripts into intelligent, autonomous workflows.

The AI-Native Pipeline Framework

Self-Healing

Agents detect pipeline failures (e.g., schema drift) and automatically suggest or apply SQL fixes.

"Anomaly detection is key to autonomous systems."

Cost Optimization

ML models predict workload spikes and scale cloud compute resources before they fail.

"Adaptive indexing based on query frequency."

Citation: **AI-Driven Data Warehousing**

AV-SQL for Transformation

Traditional Text-to-SQL fails on complex joins. AV-SQL solves this by:

  1. 1. **Question Rewriting:** Clarifying implicit constraints (e.g., "Active Users").
  2. 2. **Agent View Generation:** Creating modular CTE-based views for large schemas.
  3. 3. **Iterative Validation:** Running execution-based checks to fix syntax errors.

Research Paper: **AV-SQL Framework**

Workshop (30 min)

Designing an Agentic ETL Workflow

Problem: Schema Drift

"A source system added a new column `loyalty_tier` to the users table without notice. Your pipeline is failing."

**Step 1:** Describe how a **Monitoring Agent** detects this failure.
**Step 2:** How does the **LLM Planner** design a fix?
**Step 3:** What metadata is updated after the repair?

Solution 2: AI-Native Healing Steps

  • ✅ **Detection:** Monitoring Agent compares the incoming data frame schema against the **Metadata Repository**.
  • ✅ **Planning:** The LLM Planner identifies the mismatch and generates a dynamic `ALTER TABLE` statement for the Silver layer.
  • ✅ **Audit:** The **Governance Layer** records the change in data lineage to ensure auditability.
"Modern pipelines use 'Prompt Engineering' as part of their logic."

Week 9 Summary

1. ETL is complex; quality transformations often require human judgment or AI assistance.
2. ELT on Cloud (Medallion) is the modern standard for high-volume Lakehouses.
3. Agentic AI enables self-healing and performance optimization beyond static code.
Next Week: Data Visualization using Tableau!