Data Extraction Strategies

Getting Data Out

Once you know where your data lives, the next challenge is getting it out. You need to move data from its source, like a production database or a third-party API, into a system built for analysis, like a data warehouse. How you do this can have a big impact on the performance of your source systems and the freshness of your data. The two main strategies are grabbing everything at once or just picking up what's new.

Full vs. Incremental

A full extraction is the simplest approach. It's exactly what it sounds like: you copy the entire dataset from the source to the destination every time the process runs. Think of it like taking a complete census of a town every single day. It's straightforward and ensures you have a perfect, up-to-date copy, but it's also slow and resource-intensive.

An incremental extraction is more surgical. Instead of copying everything, you only pull the records that are new or have been updated since your last extraction. It's like asking the town clerk for just a list of new residents and address changes since yesterday. This method is far more efficient and has a minimal impact on the source system, but it requires a reliable way to identify what has changed.

Strategy	Pros	Cons	Best For
Full Extraction	Simple to implement. Guarantees data consistency.	High load on source system. Slow. High network traffic.	Small, non-critical datasets that don't change often.
Incremental Extraction	Fast and efficient. Low impact on source systems. Scalable.	More complex to set up. Can miss changes if not implemented carefully.	Large, frequently updated datasets where performance is key.

How to Track Changes

To perform an incremental load, you need a system for tracking changes. Two common patterns are watermarking and Change Data Capture.

Watermarking is a technique where you use a consistently increasing value, usually a timestamp, to mark your progress. Most database tables have a column like last_updated_at that records when a row was last modified. Your extraction process saves the most recent timestamp it has seen. The next time it runs, it only requests records where the last_updated_at value is newer than the saved watermark. This effectively filters for only the new or updated data.

A key weakness of timestamp-based watermarking is that it can't track deletes. If a row is removed from the source table, an incremental pull based on last_updated_at will never know it's gone.

For a more robust solution, engineers often turn to (CDC). Instead of querying the data tables directly, CDC tools tap into the database's internal transaction log. This log is a running record of every single change, including inserts, updates, and deletes. By reading this log, a CDC process can replicate every change to the target system with perfect accuracy, without ever putting a heavy query load on the production database itself.

Extraction in Practice

Let's see how these patterns look in code. Extracting data from an API often involves making HTTP requests. The requests library in Python is perfect for this. For a simple full extraction, you just fetch the data and process it.

import requests
import json

# API endpoint for user data
URL = "https://api.example.com/users"

try:
    response = requests.get(URL)
    # Raise an exception for bad status codes (4xx or 5xx)
    response.raise_for_status()

    users = response.json()

    # Save the full data to a file
    with open("users_full.json", "w") as f:
        json.dump(users, f)

except requests.exceptions.RequestException as e:
    print(f"API request failed: {e}")

For databases, we can use libraries like SQLAlchemy or psycopg2 to execute SQL queries from Python. Here is a simplified example of a timestamp-based incremental extraction. The script would need to store the last_extraction_time between runs, perhaps in a simple file or a dedicated tracking table.

import sqlalchemy
import pandas as pd
from datetime import datetime

# Assume last_extraction_time is loaded from a file or config
# For this example, we'll set it manually
last_extraction_time = "2023-10-26 10:00:00"

# Database connection details
DATABASE_URL = "postgresql://user:password@host:port/dbname"
engine = sqlalchemy.create_engine(DATABASE_URL)

query = sqlalchemy.text(f"""
    SELECT id, email, updated_at
    FROM users
    WHERE updated_at > '{last_extraction_time}'
""")

try:
    with engine.connect() as connection:
        # Execute the query and load results into a DataFrame
        df_new_users = pd.read_sql(query, connection)

        # Process the new data...
        print(f"Extracted {len(df_new_users)} new or updated records.")

        # Update the watermark for the next run
        new_watermark = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        # In a real pipeline, you would save this new_watermark
        print(f"New watermark: {new_watermark}")

except Exception as e:
    print(f"Database extraction failed: {e}")

Notice the try...except blocks in both examples. Network connections can fail and databases can become unavailable. A robust extraction pipeline must anticipate these failures. A common strategy is to implement a retry mechanism, where the script waits for a short period before attempting the extraction again. If it continues to fail after a few retries, it should log the error and send an alert.

Quiz Questions 1/6

What is the primary characteristic of a full data extraction?

Quiz Questions 2/6

An incremental extraction is generally less resource-intensive on the source system compared to a full extraction.

Choosing the right extraction strategy is a balancing act between simplicity, performance, and data freshness. Incremental methods are usually preferred for large-scale systems, as they respect the source system's resources while delivering timely data.

Mastering Modern ETL Engineering