Connect and extract

The shape of the source decides the read pattern. There is no universal connector - part of the craft is matching tool to shape.

• Relational (Daybreak's tables): query with SQL. A SELECT is your extract. Structured, typed, easy - the friendliest source.
• Files (CSV / JSON): someone drops an export in a folder. You read the file, not a database. Formats vary wildly.
• Object storage (S3 / GCS): files at cloud scale. Same idea as local files, but you point a reader at a bucket path.
• API: you call an endpoint over HTTP and get JSON back. Rate limits and pagination make this its own skill (card below).
• Logs: append-only text lines, semi-structured, huge volume. Often parsed and batched.
• Streams: unbounded events arriving continuously - a different mindset entirely, covered in b6.

Each source shape was built for its own job, not for you. An app database is tuned for fast single-row writes, not big analytical scans. An API is built to serve a mobile screen, not to dump a million rows. Logs are optimised for cheap appends. None of them were designed to feed a warehouse - which is exactly why ingestion (b3) and storage (b4-b7) exist: to translate whatever the source hands you into something analytics can use.

This is your extract from a relational source: a SELECT that names only the columns the pipeline needs, not SELECT *. Press ▶ Run (first run downloads the ~8 MB engine once, then cached).

SELECT order_id, order_date, channel
FROM orders
WHERE status = 'completed'
LIMIT 8;

Half your sources arrive as files, not databases. We can simulate that exactly: write orders out to a CSV in the browser's virtual file system, then read it back as if a partner team had dropped it in a folder. The extract is now a file reader, not a SQL query against the source.

COPY (SELECT * FROM orders) TO 'drop/orders.csv' (FORMAT CSV, HEADER);
SELECT order_id, order_date, status
FROM read_csv_auto('drop/orders.csv')
LIMIT 5;

Write customers to a JSON file, then read it with read_json_auto. An API response looks just like this file's contents - DuckDB infers the columns and flattens the structure into rows for you.

COPY (SELECT * FROM customers) TO 'api/customers.json' (FORMAT JSON);
SELECT customer_id, name, city, plan
FROM read_json_auto('api/customers.json')
LIMIT 5;

The browser cannot call external APIs, so this part is concept, honestly labelled. When you extract from a real API three things bite you that a database never does:

• Rate limits: the API caps how many calls you can make per minute. Blow past it and you get throttled or blocked. You add backoff and retry logic.
• Pagination: the API returns 100 rows at a time with a "next page" token. A full extract means looping until the token runs out - not one query.
• Nested JSON: a single response often nests orders inside customers inside a wrapper. You flatten it after landing, not during the call.

Extract only completed orders' id, date, and channel to a Parquet file, then read it back to confirm the count.

COPY (SELECT order_id, order_date, channel
      FROM orders
      WHERE status = 'completed')
TO 'out/completed.parquet' (FORMAT PARQUET);

SELECT count(*) AS completed_rows FROM 'out/completed.parquet';

COPY order_items to a CSV, read it back with read_csv_auto, and sum quantity times unit price to get total revenue - all from the landed file, not the source.

COPY (SELECT * FROM order_items) TO 'out/items.csv' (FORMAT CSV, HEADER);

SELECT ROUND(SUM(quantity * unit_price), 2) AS revenue
FROM read_csv_auto('out/items.csv');

A thought exercise, no SQL. Why land data to a file instead of just querying the source every time you need it? Three reasons that matter in production:

• Decoupling: once data is in your file, your pipeline no longer depends on the source being up, fast, or unchanged. The source team can restart their database and your job keeps running on the landed copy.
• Replay: if a downstream transform has a bug, you re-run it against the saved file - you do not have to re-extract from the source, which may have changed or aged out.
• Not hammering prod: repeatedly running heavy analytical queries against a live app database can slow or crash the product. Extract once, then work on your own copy as much as you like.