In this lesson, you will learn how to implement timestamp tracking to fetch only delta changes from an API, drastically reducing resource consumption.
When integrating with external web APIs, downloading the entire dataset every time is inefficient and often triggers rate limits. Instead, we use incremental loading, which relies on a "high-water mark"—the timestamp of the most recently processed record—to request only the data that has changed since that specific moment.
The Strategy
The process consists of three steps:
- Retrieve the High-Water Mark: Query your local database for the maximum
updated_atvalue of the records currently stored. - Request Delta: Pass this timestamp to the API as a filter parameter (e.g.,
?since=2023-10-27T10:00:00Z). - Upsert: Insert new records or update existing ones in your local database using their unique identifiers.
Implementation Example
Assuming you have a records table in SQLite, your Python logic would look like this:
import sqlite3
def get_last_sync_time(cursor):
cursor.execute("SELECT MAX(updated_at) FROM records")
return cursor.fetchone()[0] or "1970-01-01T00:00:00Z"
def sync_data(api_client, db_conn):
cursor = db_conn.cursor()
last_sync = get_last_sync_time(cursor)
# Fetch only what has changed since the last successful sync
new_data = api_client.get(f"/data?since={last_sync}")
for item in new_data:
# Use an UPSERT pattern (INSERT OR REPLACE) to handle new and updated records
cursor.execute("""
INSERT OR REPLACE INTO records (id, data, updated_at)
VALUES (?, ?, ?)
""", (item['id'], item['value'], item['updated_at']))
db_conn.commit()
By storing the updated_at field locally, you ensure that every API call only retrieves the specific "delta." This approach transforms a resource-heavy, full-table scan into a surgical extraction. Not only does this keep your local storage in sync, but it also minimizes network latency and prevents the database from performing unnecessary write operations on static records.
Key Takeaway: By tracking and passing the latest updated_at timestamp as a query parameter, you efficiently limit API requests to only new or modified data.