Getting your data in: syncs, APIs and CSVs, without a pipeline team

The least glamorous part of a warehouse decides whether anybody believes it. If a number is missing a day, or counts a refund twice, people notice, and once they stop trusting the totals they go back to their spreadsheets.

In part one we met Pondside Coffee and its four sources: a café on Square, a web shop on Shopify, a coffee subscription on Stripe, and a Google Sheet of what the green beans cost. Part two chose the engine, DuckDB, and part three showed why it is fast. This part is about the plumbing between the two: how records get from the tools that run the business into the warehouse, completely, on time, and exactly once.

You may never write a sync yourself. Most people should not. But knowing how a good one works tells you which numbers to trust, what “fresh” means, and where to look when a total looks wrong.

Copy first, shape later

There are two ways to fill a warehouse, and the names are just the same three letters in a different order.

Extract

Read the records out of the source: an API, a database, a file.

Transform

Clean them up: fix types, rename columns, drop test data, join things together.

Load

Write them into the warehouse.

ETL made sense when warehouse storage and compute were scarce and expensive, so you only loaded what you had already boiled down. Once warehouses could store raw data cheaply and run SQL over it quickly, it became simpler to land everything first and transform it where it sits. That is ELT, and it is how almost every modern warehouse is filled.

Keep the raw copy. Every mistake you make later is fixable in SQL, as long as the original is still there.

The strongest argument for ELT is humility. You will not know on day one that Shopify keeps a few test orders from the week the store was set up, or that one cell in the costs sheet has a dollar sign in it. When you find out, you want to fix a view, not re-extract two years of history. So the raw layer stays raw, and all the shaping happens on top of it, in part five.

How Pondside's sources get in

In DuckHouse, each source becomes a Connection: a form that tests the credentials before saving, lists what it can sync, and starts the first sync straight away. Each one writes into a schema of its own, so sources never collide and their tables join like any others.

Pondside does not run its own app, so it has no Postgres to sync, but many businesses do, and the same ideas apply. Everything on this list lands as plain tables: square.payments, shopify.orders, stripe.invoices. That is the raw layer. Nobody has to like it; it just has to be complete.

Full syncs and incremental syncs

The simplest sync reads the whole source every time and replaces the copy. It is called a full refresh, and for small things it is the right answer. Pondside's costs sheet has five rows; reading it in full every fifteen minutes costs nothing and means edits and deleted rows always show up. That is exactly how DuckHouse syncs a Google Sheet.

It stops being the right answer quickly. Pondside's café has taken around ninety-four thousand payments in under two years. Reading all of them every fifteen minutes would be slow, would burn through the API's rate limits, and would mostly re-read rows that have not changed since 2025. What you want is an incremental sync: read only what changed since last time, and merge it in.

An incremental sync needs two things from each table:

• A primary key, so a changed row can be matched to the copy already in the warehouse and replaced rather than duplicated.
• A column that records when a row last changed, usually updated_at, so the sync can ask for “everything changed since the newest change I have already copied.” That newest change is called the high-water mark, or watermark.

For Postgres and MySQL, DuckHouse picks the change column for you, usually updated_at or something named like it, and you can choose a different one per table. A table missing either one is copied in full on every sync. The docs put it plainly: if a large table has no such column, adding one at the source is the single most effective thing you can do for sync speed.

Here is the watermark on Pondside's café payments, and where the next run would start reading. The five minutes are deliberate: a transaction that was still open during the last read can commit rows stamped slightly earlier than the newest one the sync saw. Re-reading a short window catches them.

SELECT
  strftime(max(updated_at) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M') AS high_water_utc,
  strftime((max(updated_at) - INTERVAL 5 MINUTE) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M') AS next_read_from_utc
FROM square.payments;

Re-reading rows you already have would duplicate them, if the sync simply inserted. It does not. It merges: rows whose key is already in the copy are updated, new keys are inserted. In SQL that is one statement, MERGE INTO, which DuckDB has supported since version 1.4. Try it on two small temporary tables: a copy holding two payments, and a page of changes in which one of them was refunded and a third is new.

CREATE TEMP TABLE p4_copy AS
FROM (VALUES
  ('pay_1', 'COMPLETED', 450, TIMESTAMP '2026-09-30 08:00'),
  ('pay_2', 'COMPLETED', 525, TIMESTAMP '2026-09-30 08:05')
) t(id, status, amount, updated_at);

CREATE TEMP TABLE p4_changes AS
FROM (VALUES
  ('pay_2', 'REFUNDED', 525, TIMESTAMP '2026-09-30 09:10'),
  ('pay_3', 'COMPLETED', 300, TIMESTAMP '2026-09-30 09:12')
) t(id, status, amount, updated_at);

MERGE INTO p4_copy AS t
USING p4_changes AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (id, status, amount, updated_at)
  VALUES (s.id, s.status, s.amount, s.updated_at);

FROM p4_copy ORDER BY id;

Now run the very same merge a second time, as a retried sync would. Nothing changes: still three rows, one per payment. That property has a name, idempotence, and it is what makes overlaps, retries and interrupted runs safe. A sync that is idempotent can always just try again.

MERGE INTO p4_copy AS t
USING p4_changes AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (id, status, amount, updated_at)
  VALUES (s.id, s.status, s.amount, s.updated_at);

SELECT count(*) AS rows, count(DISTINCT id) AS ids FROM p4_copy;

The updated_at trap

Incremental syncs rest on one assumption: every change to a row moves its updated_at forward. When that assumption breaks, rows go missing silently, and silent is the worst way for data to go wrong.

It breaks in three common ways.

Some writes skip the column. An application sets updated_at on every save, but a one-off script fixes a thousand rows directly in the database and forgets to. Those rows changed, their timestamp did not, and an incremental sync will never see the fix. If you run bulk fixes at the source, touch updated_at too.

Some rows have no value at all. This one is subtle. Square's payments API can return cash and external payments with no updated_at at all, and when sorted by it they come first. A first load that walked payments by updated_at would stall on them and never reach the rest. So DuckHouse walks Square's first load by created_at, which every payment has and which never changes, and only switches to following updated_at once history is complete. In SQL terms, the problem is that a comparison with NULL is neither true nor false, so a filter quietly drops the row:

SELECT id, updated_at, updated_at > TIMESTAMP '2026-09-30 08:00' AS picked_up
FROM (VALUES
  ('pay_4', TIMESTAMP '2026-09-30 10:15'),
  ('pay_5', NULL)
) t(id, updated_at)
ORDER BY id;

Defaults that look like everything. When no start time is given, Square's ListPayments endpoint quietly assumes “the current time minus one year.” A first load that does not spell out a start date gets one year of history, looks complete, and is not. DuckHouse always passes an explicit start, earlier than any Square account could exist.

Deletes: the change you cannot see

A watermark finds rows that changed. It cannot find rows that are gone, because a deleted row has no timestamp left to compare. There are two honest ways to deal with that, and DuckHouse uses both.

• Compare keys. For Postgres, MySQL and DuckDB sources, each sync reads every primary key still present at the source and removes rows from the copy whose key has gone. It costs one scan of each table's key per sync. If a run cannot finish reading the keys in time, it skips the delete rather than act on a partial list, which would otherwise look like “everything missing was deleted.”
• Keep the row, flag it. For API sources like Stripe, Shopify and Square, a deleted record is kept and marked, so its history is not lost and you decide whether it counts.

Every table an API source writes carries three extra columns for exactly this kind of bookkeeping:

_raw

The complete record as the source sent it, as JSON. Any field without a column of its own is still one _raw ->> '$.field' away.

_deleted

True once the record has been deleted at the source. The row stays; filter on it when you want live records only.

_synced_at

When the warehouse last wrote this row. The honest answer to "how fresh is this?"

Not every source reports deletions. Shopify, for one, does not report deleted customers or products, so they stay in the table. Knowing which sources can and cannot tell you is part of reading the numbers well; the source guides list the caveats for each.

First loads, rate limits and event streams

The very first sync is the hardest one. Pondside's first Square load has to page through every payment since the café opened, a hundred at a time, inside limits Square sets on how fast any app may ask. A large store can take a while: the docs note that Shopify limits reads to roughly a thousand orders every few minutes.

So first loads happen in stages. A sync works for a while, saves its place, and carries on in the next run, and tables fill in progressively rather than all at once. The detail that makes this safe is small but important: the saved place is written in the same transaction as the rows it describes. A run that is cut off mid-page, by a timeout or a restart, either recorded both or neither, so the next run neither skips records nor counts any twice.

Stripe gets one more trick. After the first load, instead of asking each list endpoint what changed, DuckHouse follows Stripe's event stream: changes to customers, subscriptions, invoices and most other objects are also recorded as events, so one poll of /v1/events covers them all at once. (The few object types that emit no events have their most recent window re-listed instead.) Stripe lets you list events going back up to 30 days, which sets a limit: a Stripe Connection paused for longer than that has lost changes it can no longer replay. DuckHouse watches for that, and when a connection has been away for more than 25 days it starts over with a full load rather than carry on with a gap.

Is the data fresh?

“Fresh” is two different questions: when did the warehouse last hear from the source, and how recent is the newest thing the source told it? The first is _synced_at; the second is the newest timestamp in the data itself. Put them side by side for each source and you have a freshness check worth running before any important meeting.

SELECT 'shopify.orders' AS synced_table, count(*) AS rows,
  strftime(max(created_at) AT TIME ZONE 'America/Chicago', '%Y-%m-%d %H:%M') AS newest_record,
  strftime(max(_synced_at) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M') AS synced_utc
FROM shopify.orders
UNION ALL
SELECT 'square.payments', count(*),
  strftime(max(created_at) AT TIME ZONE 'America/Chicago', '%Y-%m-%d %H:%M'),
  strftime(max(_synced_at) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M')
FROM square.payments
UNION ALL
SELECT 'stripe.invoices', count(*),
  strftime(max(created) AT TIME ZONE 'America/Chicago', '%Y-%m-%d %H:%M'),
  strftime(max(_synced_at) AT TIME ZONE 'UTC', '%Y-%m-%d %H:%M')
FROM stripe.invoices
ORDER BY synced_table;

The café's newest payment is from 4:34 in the afternoon, which is right: it closes at five. The web shop and the subscription keep going into the night. A source whose newest record is far older than its last sync is either quiet or broken, and the check tells you which question to ask.

Notice the time zones, too. The records are shown on the café's clock and the sync time in UTC, and both are written out explicitly. A timestamp printed in “whatever zone my laptop is in” is a classic source of off-by-one-day totals; part five fixes that once, for everything.

Files: the connector that never goes away

Not everything has an API. The roaster's profile logs come off a machine as CSV. The accountant sends a spreadsheet. A supplier drops a price list on an FTP server every Monday. Files are the universal connector, and a warehouse needs a good answer for them.

1. Upload it

   In DuckHouse's data browser, drop a CSV onto the page and it becomes a table: a new one, a replacement for an existing one, or rows appended to it. Files can be up to 1 GB each. DuckDB does the reading, so the column types are what you would get running read_csv yourself.

2. Drop it in a folder

   For files that keep coming, an SFTP or FTP Connection watches a folder. Each sync imports files that are new or changed (by size and modification time), oldest first, and upserts their rows by an identifier column you choose. Nothing on the server is changed or deleted, and nothing in the table is ever deleted either.

3. Read it with DuckDB

   DuckDB reads CSV, Parquet and JSON directly, from a local path or a URL, and works out the columns and types on its own. From the CLI, with your warehouse attached as wh, loading a file is one statement (see loading data).

-- See what DuckDB makes of the file before loading it
DESCRIBE FROM read_csv('roast_log.csv');

-- Then load it into the warehouse
CREATE TABLE wh.roast_log AS FROM read_csv('roast_log.csv');

-- Or append next week's file to it
INSERT INTO wh.roast_log FROM read_csv('roast_log_2026_10.csv');

Doing it safely

A sync holds a key to your business, so it should hold the smallest key that works. Three habits cover most of it.

• Read-only everywhere. For a database, create a user that can only SELECT the tables you want, and point it at a read replica if you have one, so syncs never compete with customers. For Stripe, create a restricted key with read access to the objects you sync instead of pasting a secret key that can move money.
• Leave out what should never leave. Password hashes, API tokens and personal details you do not need can be excluded column by column. Excluded columns are left out of the query sent to the source, so their values never reach the warehouse at all.
• Trust the storage, not the screen. In DuckHouse, source passwords and keys are encrypted before they are stored, each Connection under its own key, and never shown again in the dashboard or the API.

CREATE ROLE duckhouse_reader LOGIN PASSWORD 'choose-a-long-one';
GRANT USAGE ON SCHEMA public TO duckhouse_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO duckhouse_reader;

Look before you shape

With the syncs running, Pondside's warehouse holds a faithful copy of everything. Faithful includes the embarrassing parts. Here is one: the week the Shopify store was set up, someone placed four test orders to check the checkout worked, and Shopify kept them.

SELECT
  name,
  strftime(created_at AT TIME ZONE 'America/Chicago', '%Y-%m-%d %H:%M') AS placed,
  total_price
FROM shopify.orders
WHERE test
ORDER BY name;

Four orders, ten thousand pretend dollars. Here is what they would do to January if nobody noticed:

SELECT
  round(sum(total_price)) AS all_orders,
  round(sum(total_price) FILTER (WHERE NOT test)) AS real_orders
FROM shopify.orders
WHERE created_at AT TIME ZONE 'America/Chicago' < TIMESTAMP '2025-02-01';

Three quarters again on top of the real number, from four rows. This is not a reason to filter the raw copy: the raw layer should say exactly what Shopify says. It is a reason to have a layer above it where “real web orders” is defined once, so nobody has to remember the WHERE NOT test. The same goes for money in cents from Square and Stripe but dollars from Shopify, for email addresses typed with capital letters, and for the dollar sign in the costs sheet.

That layer is the model, and it is where a warehouse stops being a pile of copies and starts being something people, and AI assistants, can read. It is the subject of part five: modeling a warehouse that people and AI can read.