Learning Objectives

• Extract data from multiple sources
• Build data warehouse schema
• Implement ETL pipeline

Extract from SQL

python
Run Inline
import pandas as pd
from sqlalchemy import create_engine
# Connect to database
engine = create_engine('postgresql://user:pass@localhost:5432/dbname')
# Extract data
query = """
SELECT o.order_id, o.order_date, o.total_amount,
       c.customer_id, c.segment, c.region
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01'
"""
df = pd.read_sql(query, engine)

Extract from API

python
Run Inline
import requests
import pandas as pd
def fetch_api_data(url, params, max_pages=10):
    all_data = []
    for page in range(1, max_pages + 1):
        params['page'] = page
        response = requests.get(url, params=params)
        if response.status_code == 200:
            data = response.json()
            all_data.extend(data['results'])
        else:
            break
    return pd.DataFrame(all_data)

Q1: What is ETL?

Extract (from sources), Transform (clean, validate, integrate), Load (into warehouse). Core data pipeline process. Q2: Star schema vs Snowflake schema?

Star: fact table + dimension tables (denormalized, simpler queries). Snowflake: dimensions normalized (less redundancy, complex queries). Star more common in BI. Q3: What is data quality and how to measure?

Accuracy (correct values), Completeness (no missing), Consistency (across sources), Timeliness (up-to-date), Validity (conforms to schema). Measure: % of rows passing quality checks. Q4: What tools for ETL?

Python (pandas/airflow), dbt (data build tool), Apache Spark (big data), Fivetran/Stitch (SaaS connectors), Talend/Informatica (enterprise). Q5: What is data validation?

Check data meets expectations: schema validation, range checks, uniqueness, referential integrity, business rules. Fail pipeline if quality thresholds not met. Join Discord PreviousMilestone 1: Business Problem & Data StrategyNextMilestone 3: Descriptive Analytics & Visualization