Data Sources & Ingestion Patterns

What you will learn

1. The four kinds of source: databases, APIs, event streams and files.
2. Pull and push: who starts the transfer.
3. Full load and incremental load, with real rows.
4. The problems of reading a source: pages, limits, retries, shifting pages and duplicates.
5. Five short recipes, and when to build or buy.

You know the stages of a pipeline from The Data Pipeline Lifecycle. The first stage gets data in. People call it ingestion.

A source is any system that holds the data you want. You usually do not control it. It can change, slow down, or go quiet.

This chapter uses one shop with four sources. The first is the app database, PostgreSQL, with customers and orders 101 to 106. The second is a payment provider’s API, a way for one program to ask another program for data.

The third is a stream of click events from the website. The fourth is a CSV file that a delivery partner sends each day. A CSV file is plain text: each line is one row, and commas split the columns.

You also know ETL and ELT from ETL vs ELT. Both start with the same step: read from the source. This chapter is about that step.

Almost every source belongs to one of four families. Each family gives you data in a different shape. Each one also fails in its own way.

An API often sends its answer in a text format called JSON. JSON writes each row as a list of names and values.

An event is one small record of something that happened, such as one click. A stream is a never-ending line of events.

A database is the easiest family to read. It has a fixed shape and you can ask it any question. Its risk is the extra work you put on it.

The app uses this same database to take orders. A huge query at noon can slow down the checkout. Many teams read from a copy of the database for this reason.

In a pull, your job starts the transfer. It asks the source for data on a schedule.

In a push, the source starts it. It sends data when it is ready.

The website pushes each event into the stream. Your job then reads from the stream when it is ready.

Some APIs also push. A webhook is a message the source sends to your web address when something happens, such as a new payment. It is rare for big data loads.

The two kinds fail in different ways. A pull usually fails loudly when the source is down. Your query gives an error, and the job fails.

A push can fail silently. If the partner forgets to send the file, nothing breaks. The job just finds nothing.

So with a push, you must check that data arrived. Here is a made-up log of the files that landed. Today is 2026-01-22.

SELECT MAX(file_date) AS newest_file
FROM   landed_files;

The second question is how much to read. A full load reads every row on every run. An incremental load reads only rows that are new or changed.

In this chapter each order has one more column, updated_at. It holds the date the row last changed. This column is made up for the chapter.

An incremental load must remember how far it got. That saved value is the watermark. Here it is the newest updated_at the job has loaded.

The first run has no watermark, so it reads everything. On 2026-01-22 both ways read all six orders. The watermark becomes 2026-01-21, the date of order 106.

Now it is 2026-01-26. Order 105 was paid on 01-25, so its row changed, and order 177 is new. First, the full load:

SELECT order_id, customer_id, order_date,
       status, amount, updated_at
FROM   orders;

Now the incremental load. It asks only for rows whose updated_at is after the saved watermark, 2026-01-21.

SELECT order_id, customer_id, order_date,
       status, amount, updated_at
FROM   orders
WHERE  updated_at > '2026-01-21'   -- the saved watermark
ORDER  BY updated_at;

Here is the cost of both ways over the two runs:

With seven rows the saving is tiny. With seven million rows, it decides whether the job finishes in time.

Start with a full load for small tables. Move to incremental when the full load gets too slow.

A watermark that is only a date has traps. If a row changes later on the same day as the watermark, > skips it. Incremental Processing & Backfills covers the fixes, and the traps with late rows.

Reading from a source you do not control goes wrong in four common ways. This section names each one. Extraction: Pulling Data Reliably covers them in depth.

Three words first. HTTP is the set of rules that web calls follow. A status code is a number that says how the call went.

A header is an extra line of information that comes with the answer.

1. Pages: an API returns a limited number of rows per call. You ask for page after page until it says there are no more.
2. Limits: an API refuses calls that come too fast. It answers with HTTP status code 429, which means too many requests. Many APIs add a Retry-After header that says how long to wait.
3. Retries: a call can fail halfway. You try again, so the same rows can arrive twice.
4. Shifting pages: with an offset (skip the first N rows), a new row can make you read one row twice. A deleted row can make you skip one.

Shifting pages have a fix. A cursor is a bookmark the API gives you, and it stays correct while data changes.

Retries cause the problem you must handle in every pipeline: duplicates. Say the payment API fails after page 1. You retry from the start.

The landing table, the raw copy of what arrived, now holds some payments twice. This data is made up.

SELECT DISTINCT payment_id, order_id, amount
FROM   raw_payments
ORDER  BY payment_id;

DISTINCT removes rows that are the same in every column. The retry made exact copies, so this works.

A key is a value that is different for every payment, such as payment_id. If two copies can differ, you keep one row per key. Transformation Fundamentals shows how.

Here is one short recipe for each source. The database gets two, because its two tables need different loads. Each recipe is simplified.

Recipe 1: customers, a pull and a full load

The source is PostgreSQL. The target is a warehouse table, dw_customers. The prefix dw means data warehouse.

The customers table has five rows. A full load is the right choice for a table this small.

rows = query("SELECT customer_id, name FROM customers")   # in PostgreSQL
replace_table("dw_customers", rows)   # in the warehouse: delete all, insert all, one step

Recipe 2: orders, a pull and an incremental load

This is the incremental query from Full or incremental load. The new part is when you save the watermark. Save it only after the rows are written.

watermark = read_state("orders")        # 2026-01-21
rows = query("SELECT * FROM orders WHERE updated_at > %s", watermark)
write_to_warehouse(rows)                 # update or insert by order_id
save_state("orders", max((r.updated_at for r in rows), default=watermark))

Recipe 3: payments, an API with a cursor

The loop asks for one page, keeps the rows, and follows the cursor. On a 429 answer it waits and tries again, and real code stops after a few tries. The address and the pages here are made up.

import time, requests

rows, cursor = [], None
while True:
    resp = requests.get(PAYMENTS_URL, params={"after": cursor}, timeout=30)
    if resp.status_code == 429:   # real code also stops after a few tries
        # Retry-After can also be a date; this code reads seconds only
        time.sleep(int(resp.headers.get("Retry-After", 5)))
        continue
    resp.raise_for_status()
    body = resp.json()
    rows += body["data"]
    cursor = body["next_cursor"]
    if cursor is None:
        break

Recipe 4: clicks, a stream you read from a position

Each event has a position in the stream. The job remembers the last position it processed. Streaming Architecture Fundamentals teaches this in full.

events = stream.read(after_position=saved_position)   # saved_position = 1
if events:
    write_to_warehouse(events)
    save_position(events[-1].position)

Recipe 5: the partner file, a push

Do not read a file just because it exists. It may still be half written.

Ask the partner for a manifest, a small file that holds the count of rows. Then compare.

rows = read_csv("partner_2026-01-22.csv")
expected = read_manifest("partner_2026-01-22.manifest")   # 3
if len(rows) != expected:
    raise Exception("file is not complete, do not load")
write_to_warehouse(rows)

You can write each recipe yourself. This is building.

You can also use a tool with connectors. A connector is a ready-made reader for one source. Using one is buying.

Fivetran is a managed service. You pay the vendor, and the vendor runs the connectors for you.

Airbyte is free to run yourself, and its code is public. It also has a hosted version.

Open source means anyone can read, use, change and share the code for free.

For streams, Apache Kafka Connect and Debezium are open-source tools. Debezium reads database changes, which Change Data Capture (CDC) covers.

Tool & Technology Reference: Every Box on the Canvas lists more tools.

In the shop, a payment provider and a PostgreSQL database are common sources. A ready connector is a good fit. The partner CSV has its own rules, so you probably build that one.

Prices and connector lists change often. Check the vendor’s current page before you decide. Also check that a connector handles the hard parts, such as deletes and the fields you need.

• Sources come in four families: database, API, event stream and file.
• In a pull, your job starts. In a push, the source starts.
• A full load reads everything. An incremental load reads rows changed since the watermark.
• Retries make duplicates. Keep a key, such as payment_id, and remove repeats.
• Build for odd sources. Consider buying for common ones.

Six scenarios. Build each one on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.