📤 Data Loading & Change Data Capture (CDC)

📥 Data Loading

Data Loading is the final step in the pipeline — moving processed, ready-to-use data into the Data Warehouse or Lakehouse.

🏗️ Types of Data Loading

How often and how fast you load data depends on what the business needs.

🔄 Change Data Capture (CDC)

CDC is a modern approach to incremental loading. Instead of querying a table to see what changed, CDC listens directly to the source database's transaction log (e.g., the binlog in MySQL or WAL in PostgreSQL).

How CDC Works:

1. An UPDATE happens in the source database.
2. The database writes this change to its internal log.
3. The CDC tool (like Debezium) reads the log instantly.
4. The change is streamed into the Data Warehouse.

Why CDC is powerful:

• Zero impact on the source database (no heavy SELECT queries)
• Instant updates (enables real-time replication)
• Captures every single change (even if a row is updated 5 times in a minute, CDC catches all 5; normal incremental loading would only catch the final state).

🧠 Key Loading Concepts

1️⃣ Data Freshness (Latency)

How up-to-date is the data in the warehouse?

• If a pipeline runs every night at 2 AM, the data freshness during the day is up to 24 hours old.
• If a business asks for "real-time data", they are actually asking for very low latency (high freshness).

2️⃣ Scheduling & Orchestration

Data doesn't load itself. You need a tool to trigger the extraction, wait for the transformation, and execute the load.

• Tools used: Apache Airflow, Dagster, Prefect, or cloud-native schedulers.
• Dependency Graphs: Jobs are run as a DAG (Directed Acyclic Graph). For example, Load Customers must finish before Load Sales can begin.

3️⃣ Incremental vs Full Load

We covered this in Extraction, but it applies to Loading too.

• Full Load: Wipe the target table clean (Truncate) and insert everything from scratch. Used for small dimension tables (e.g., a list of 50 states).
• Incremental Load: Only insert new rows or update changed rows. Used for massive fact tables (e.g., millions of daily transactions).

🛠️ Handling Updates During Load (SCD)

When incrementally loading data, what happens if a customer's address changes? You have to decide how to load that change into the DW. This is called Slowly Changing Dimensions (SCD).

🧪 Practice Drill

// Try answering these:
// Q1. A retail manager checks a dashboard every morning to see yesterday's total sales. What type of loading strategy is most appropriate for this?
// Q2. What is the primary advantage of CDC over traditional incremental loading using timestamp columns?
// Q3. An analyst runs a query at 3 PM. The ETL pipeline runs once a day at midnight. What is the data freshness (latency) of the results?
// Q4. A company needs to trigger a Python script, wait for it to finish, and then trigger 5 SQL queries in Snowflake. What kind of tool do they need for this?
// Q5. Match the scenario to the correct SCD Type:
// | Scenario                                                                                           | SCD Type |
// | -------------------------------------------------------------------------------------------------- | -------- |
// | A customer corrects a typo in their name. The old name doesn't matter.                             | ?        |
// | A customer moves to a new city. We need to track sales in both their old and new cities over time. | ?        |

← Previous Topic | Next Topic → Next Topic