{"course":{"slug":"agents-and-automation","title":"Agents and Automation","url":"https://addaly.com/learn/agents-and-automation","level":"intermediate","track":"build"},"lesson":{"slug":"where-the-data-lives","title":"Files, inboxes, sheets and APIs","url":"https://addaly.com/learn/agents-and-automation/where-the-data-lives","position":21,"of":72,"minutes":9,"answer":"Land the raw source untouched before you transform anything, and remember that an incremental sync on a timestamp will never show you a deletion.","keyIdea":"Land the raw source untouched before you transform anything, and remember that an incremental sync on a timestamp will never show you a deletion."},"body":{"format":"markdown","text":"## Four shapes, four sets of problems\n\nAlmost every automation reads from one of four places, and each fails differently.\n\n**A file that lands somewhere.** SFTP drop, a shared folder, an email attachment. Easy to read, and the problems are ordering and duplication: the same file arriving twice, a file arriving half-written while you read it, yesterday's file being replaced silently. Read only files whose modification time has been stable for a minute, and record a hash of every file you have already processed.\n\n**An API.** Auth, token refresh, pagination, rate limits, and their idea of an error. Everything a vendor's quickstart leaves out is what you will spend the week on.\n\n**A database you are allowed to read.** The best case by a distance: types, constraints, transactions, and a schema you can query. Ask for a read-only replica before you build anything cleverer.\n\n**A spreadsheet a team maintains by hand.** The most common source in the real world and the worst-behaved. Merged cells, a header row that moves down when somebody adds a title, numbers stored as text, dates that Excel has helpfully reinterpreted, and a \"Notes\" column that contains business rules nobody has written anywhere else.\n\nFor that last one, one habit prevents most of the pain: **match columns by header name, never by position**, and fail loudly when an expected header is missing. Position-based readers keep working after somebody inserts a column, and quietly read the wrong field into your database for a month.\n\n## Land the raw thing first\n\nThe most useful architectural habit in this whole subject: store the source exactly as received, before any parsing, with a fetch timestamp and a hash. A folder of original files, or a table with one text column holding the raw payload.\n\nEverything after that is derived and can be recomputed. When you discover in March that your parser has been dropping the second line of every address since January, you re-run the parser over stored raw data and you are finished in an hour. Without it, the data is gone and you are writing apologies.\n\nThis costs almost nothing. Raw text compresses hard, and disk is the cheapest thing in the system.\n\n## Incremental sync, and the two traps\n\nYou do not want to pull everything every time. So you keep a cursor (the highest `updated_at` you have seen) and ask for rows newer than that.\n\n**Trap one: the boundary.** Several rows can share a timestamp, clocks skew between servers, and a transaction can commit after a row with a later timestamp is already visible. Ask for `updated_at >= last_seen - 5 minutes` and deduplicate by primary key on your side. Overlap is cheap; a missed row is invisible forever.\n\n**Trap two: deletions are silent.** A deleted row does not have a new `updated_at`. It stops existing, and an incremental sync will never mention it, so your copy keeps a customer who was removed in February for as long as the system runs. The fix is a periodic full reconcile (weekly, compare the complete set of ids and mark what has vanished) or a source that gives you soft deletes with a `deleted_at` you can read.\n\nAdvance the cursor over the data, never over the clock. A job that asks for \"yesterday's rows\" and skips a day when the machine is down has lost that day permanently.\n\n## Schema drift\n\nUpstream systems change without telling you. A column is renamed, a field that was always a string becomes an object, an enum gains a seventh value that your code maps to null.\n\nAssert on every run. Check the set of columns you expect is present, check that the types are what you assumed, check the row count is in a plausible range against the last run. Fail the run rather than write a table full of nulls: a job that stops is an inconvenience, and a job that carries on wrongly is a month of bad data.\n\n## The free path\n\nYou need less than you think. **DuckDB** reads CSV, Parquet, JSON and Excel with SQL, runs anywhere, and will comfortably handle files far larger than memory: it is the fastest way to look at a strange file and find out what is actually in it. **SQLite** is an excellent landing store for anything under a few million rows and needs no server. **pandas** or **Polars** for transformation, `openpyxl` for the awkward spreadsheets, `curl` and `jq` for poking at an API before you write any code.\n\nStart by loading one real file and printing the distinct values of every column. Ten minutes of that tells you more about the data than any documentation the source system has, and it is where you find that 4 per cent of the \"country\" column says \"n/a\"."},"check":{"question":"A nightly job syncs customer records incrementally by asking the source for everything with `updated_at` later than the highest value it saw last night. After six months, the local copy contains 3,000 customers that no longer exist in the source. What is the mechanism?","options":[{"text":"The cursor advanced past rows that were updated during the sync window, so those records were skipped.","correct":false,"tempting":"Describes a real boundary problem, but a skipped row produces a missing record, not an extra one that the source has removed."},{"text":"The source system’s clock drifted ahead of the job’s clock, so some updates were fetched twice and duplicated.","correct":false,"tempting":"Clock skew does cause double-fetching, which duplicate keys absorb; it does not explain records that exist locally and not upstream."},{"text":"A deletion does not produce a new `updated_at` value, so an incremental pull can never observe one; only a periodic full reconcile finds them.","correct":true},{"text":"The job has been retaining soft-deleted rows because it does not filter on a `deleted_at` column.","correct":false,"tempting":"Assumes the source offers soft deletes; when it hard-deletes, there is no column to filter and nothing arrives at all."}]},"prev":{"slug":"multi-agent-or-not","title":"When a second agent helps, and when it just costs more","url":"https://addaly.com/learn/agents-and-automation/multi-agent-or-not"},"next":{"slug":"parsing-real-documents","title":"PDFs, scans and the layout problem","url":"https://addaly.com/learn/agents-and-automation/parsing-real-documents"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Files, inboxes, sheets and APIs', in Agents and Automation. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/agents-and-automation/where-the-data-lives (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Files, inboxes, sheets and APIs","courseTitle":"Agents and Automation","module":{"index":3,"slug":"data-plumbing","title":"The data underneath: sources, documents, records"},"answer":null,"keyIdea":"Land the raw source untouched before you transform anything, and remember that an incremental sync on a timestamp will never show you a deletion.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"four-shapes-four-sets-of-problems","runs":[{"text":"Four shapes, four sets of problems"}]},{"t":"p","runs":[{"text":"Almost every automation reads from one of four places, and each fails differently."}]},{"t":"p","runs":[{"text":"A file that lands somewhere.","b":true},{"text":" SFTP drop, a shared folder, an email attachment. Easy to read, and the problems are ordering and duplication: the same file arriving twice, a file arriving half-written while you read it, yesterday’s file being replaced silently. Read only files whose modification time has been stable for a minute, and record a hash of every file you have already processed."}]},{"t":"p","runs":[{"text":"An API.","b":true},{"text":" Auth, token refresh, pagination, rate limits, and their idea of an error. Everything a vendor’s quickstart leaves out is what you will spend the week on."}]},{"t":"p","runs":[{"text":"A database you are allowed to read.","b":true},{"text":" The best case by a distance: types, constraints, transactions, and a schema you can query. Ask for a read-only replica before you build anything cleverer."}]},{"t":"p","runs":[{"text":"A spreadsheet a team maintains by hand.","b":true},{"text":" The most common source in the real world and the worst-behaved. Merged cells, a header row that moves down when somebody adds a title, numbers stored as text, dates that Excel has helpfully reinterpreted, and a “Notes” column that contains business rules nobody has written anywhere else."}]},{"t":"p","runs":[{"text":"For that last one, one habit prevents most of the pain: "},{"text":"match columns by header name, never by position","b":true},{"text":", and fail loudly when an expected header is missing. Position-based readers keep working after somebody inserts a column, and quietly read the wrong field into your database for a month."}]},{"t":"h2","id":"land-the-raw-thing-first","runs":[{"text":"Land the raw thing first"}]},{"t":"p","runs":[{"text":"The most useful architectural habit in this whole subject: store the source exactly as received, before any parsing, with a fetch timestamp and a hash. A folder of original files, or a table with one text column holding the raw payload."}]},{"t":"p","runs":[{"text":"Everything after that is derived and can be recomputed. When you discover in March that your parser has been dropping the second line of every address since January, you re-run the parser over stored raw data and you are finished in an hour. Without it, the data is gone and you are writing apologies."}]},{"t":"p","runs":[{"text":"This costs almost nothing. Raw text compresses hard, and disk is the cheapest thing in the system."}]},{"t":"h2","id":"incremental-sync-and-the-two-traps","runs":[{"text":"Incremental sync, and the two traps"}]},{"t":"p","runs":[{"text":"You do not want to pull everything every time. So you keep a cursor (the highest "},{"text":"updated_at","code":true},{"text":" you have seen) and ask for rows newer than that."}]},{"t":"p","runs":[{"text":"Trap one: the boundary.","b":true},{"text":" Several rows can share a timestamp, clocks skew between servers, and a transaction can commit after a row with a later timestamp is already visible. Ask for "},{"text":"updated_at >= last_seen - 5 minutes","code":true},{"text":" and deduplicate by primary key on your side. Overlap is cheap; a missed row is invisible forever."}]},{"t":"p","runs":[{"text":"Trap two: deletions are silent.","b":true},{"text":" A deleted row does not have a new "},{"text":"updated_at","code":true},{"text":". It stops existing, and an incremental sync will never mention it, so your copy keeps a customer who was removed in February for as long as the system runs. The fix is a periodic full reconcile (weekly, compare the complete set of ids and mark what has vanished) or a source that gives you soft deletes with a "},{"text":"deleted_at","code":true},{"text":" you can read."}]},{"t":"p","runs":[{"text":"Advance the cursor over the data, never over the clock. A job that asks for “yesterday’s rows” and skips a day when the machine is down has lost that day permanently."}]},{"t":"h2","id":"schema-drift","runs":[{"text":"Schema drift"}]},{"t":"p","runs":[{"text":"Upstream systems change without telling you. A column is renamed, a field that was always a string becomes an object, an enum gains a seventh value that your code maps to null."}]},{"t":"p","runs":[{"text":"Assert on every run. Check the set of columns you expect is present, check that the types are what you assumed, check the row count is in a plausible range against the last run. Fail the run rather than write a table full of nulls: a job that stops is an inconvenience, and a job that carries on wrongly is a month of bad data."}]},{"t":"h2","id":"the-free-path","runs":[{"text":"The free path"}],"role":"free"},{"t":"p","runs":[{"text":"You need less than you think. "},{"text":"DuckDB","b":true},{"text":" reads CSV, Parquet, JSON and Excel with SQL, runs anywhere, and will comfortably handle files far larger than memory: it is the fastest way to look at a strange file and find out what is actually in it. "},{"text":"SQLite","b":true},{"text":" is an excellent landing store for anything under a few million rows and needs no server. "},{"text":"pandas","b":true},{"text":" or "},{"text":"Polars","b":true},{"text":" for transformation, "},{"text":"openpyxl","code":true},{"text":" for the awkward spreadsheets, "},{"text":"curl","code":true},{"text":" and "},{"text":"jq","code":true},{"text":" for poking at an API before you write any code."}]},{"t":"p","runs":[{"text":"Start by loading one real file and printing the distinct values of every column. Ten minutes of that tells you more about the data than any documentation the source system has, and it is where you find that 4 per cent of the “country” column says “n/a”."}]}],"check":{"question":[{"text":"A nightly job syncs customer records incrementally by asking the source for everything with "},{"text":"updated_at","code":true},{"text":" later than the highest value it saw last night. After six months, the local copy contains 3,000 customers that no longer exist in the source. What is the mechanism?"}],"options":[{"text":[{"text":"The cursor advanced past rows that were updated during the sync window, so those records were skipped."}],"correct":false,"tempting":[{"text":"Describes a real boundary problem, but a skipped row produces a missing record, not an extra one that the source has removed."}]},{"text":[{"text":"The source system’s clock drifted ahead of the job’s clock, so some updates were fetched twice and duplicated."}],"correct":false,"tempting":[{"text":"Clock skew does cause double-fetching, which duplicate keys absorb; it does not explain records that exist locally and not upstream."}]},{"text":[{"text":"A deletion does not produce a new "},{"text":"updated_at","code":true},{"text":" value, so an incremental pull can never observe one; only a periodic full reconcile finds them."}],"correct":true,"tempting":null},{"text":[{"text":"The job has been retaining soft-deleted rows because it does not filter on a "},{"text":"deleted_at","code":true},{"text":" column."}],"correct":false,"tempting":[{"text":"Assumes the source offers soft deletes; when it hard-deletes, there is no column to filter and nothing arrives at all."}]}],"order":[0,3,2,1],"why":null},"prev":{"slug":"multi-agent-or-not","title":"When a second agent helps, and when it just costs more","minutes":9},"next":{"slug":"parsing-real-documents","title":"PDFs, scans and the layout problem","minutes":10}}}