Home › Learn › Data systems › Upstreams › The big picture

Data systems · upstreams · the whole map on one page

Upstreams, the big picture

Every way data leaves a source, what each way can and cannot see, and the problems to expect.

The Upstreams page lists the questions to ask a source owner. This page is the map behind those questions: where sources live, what happens to rows inside them, the eight routes out, and what goes wrong on each. Every part links to the section that goes deeper.

For leaders

The map

Sources, the ways out, and what we keep

Read it left to right. A source is one of six kinds. Its data leaves by one or more of eight routes. On our side we keep the data as delivered, a bookmark that says how far we have read, and a set of checks that run before anything is loaded.

Sourceswhere the data is bornDatabases, on-premOracle, SQL Server, DB2, mainframeDatabases, cloudmanaged PostgreSQL, MySQL, NoSQLPackaged appsSAP, Dynamics, Oracle EBSSaaS appsSalesforce, Workday, ServiceNowPartners and peoplefiles, spreadsheets, portalsDevices and appsIoT, clicks, logs, app eventsWays out of the sourceeach route sees different changesQuery-based pullSELECT what changed since last time, or the whole tabledeletes: nopull · on a clockLog-based replication (CDC)read the transaction log: every insert, update, deletedeletes: yespush · continuousVendor extractor (SAP and others)ODP, SLT, CDS views: the vendor's own deltadeletes: usuallypush or pullAPIbulk export, paged reads, delta tokensdeletes: rarelypull · on a clockFile dropSFTP or object storage, with a done marker or manifestdeletes: only if sentpush · in batchesStreamKafka, Kinesis, Event Hubs: subscribe and replaydeletes: if designed inpush · continuousEvents and webhooksthe source says 'this changed' or 'the batch is ready'deletes: sometimespush · a signalShare or mirrorread the tables in place, no copy jobdeletes: yesin placeOur sidewhat we must keep and checkLandingas delivered, never editedBookmarkwatermark, log position,offset, token, file ledgerChecks before loadcomplete? in order?seen before? counts match?Bronzeevery row, with whenand how it arrivedTriggera clock, or a signalfrom the sourceWhat goes wrong on the waydeletes nobody saw · updates with no modified time · a first load too big to read in one go · files read while still being writtenloads that start before the data is ready · duplicates and replays · changes out of order · schema changes · a source slowed down by us
The upstream map. Every source reaches us by one or more of eight routes. The route decides which changes we can see, and the rest of this page follows from that.

Two labels on each route matter most. Deletes says whether the route can tell us a row was removed. Push or pull says who starts the conversation: on a pull we ask on our clock, on a push the source sends when something happens. The Upstreams page has the full comparison in Seven kinds of sources and Push, pull or share.

Where sources live

On-premises, cloud, SaaS, partners and the edge

Where a source runs decides how we are allowed to reach it, before any question about tables.

WhereExamplesHow we reach itWhat to watch
On-premisesDatabases and apps in your own data centre, mainframes, plant systemsA private link or VPN, and usually a small agent or gateway installed inside the network that reads the source and sends data outFirewall and access changes take weeks, so ask early. Bandwidth limits the first load. The source shares hardware with the business, so there are extraction windows. A DBA has to agree to log reading.
Cloud databasesManaged PostgreSQL, MySQL, SQL Server, Oracle; NoSQL storesA private endpoint, a read replica, or the cloud's own change capture service writing to storage or a streamMoving data between regions or clouds is charged per gigabyte. A replica can lag. Change logs kept for our reader fill the source's disk if our reader stops.
SaaS applicationsSalesforce, Workday, ServiceNow, payment and marketing toolsOnly what the vendor exposes: APIs, bulk exports, change events, a connector, sometimes a shareWe never see the database. Rate limits are shared with every other integration. The vendor upgrades on its own calendar and fields change.
Partners and other companiesSuppliers, banks, agencies, regulatorsFiles over SFTP or object storage, sometimes an APIThe contract is the only control we have. Agree names, format, done marker, corrections and timing before the first file.
The edgeMachines, sensors, vehicles, stores, mobile appsA gateway that buffers messages and forwards them to a stream or to filesConnections drop, so data arrives late and in bursts. Device clocks are wrong. The same reading is sent twice after a retry.

Questions B (access route) and C (protecting the source) on the Upstreams page cover this for each system.

Inside the source

What happens to rows in a source, and how each change can be tracked

A source does more to its rows than insert, update and delete. Each of the things below happens in real systems. The two middle columns show whether a scheduled query and a log reader can see it.

What happens in the sourceDoes a query pull see it?Does a log reader see it?How to track it
InsertYes, if the modified time or a rising id is set on insertYesA created time or an id that only goes up. Both are easy; this is the one change every route sees.
Update through the appYes, if the app sets the modified timeYes, with the old and new values if the log is set to keep themA modified time set by the database (a trigger or a default), not by each screen of the app.
Update that skips the modified timeNo. The row looks unchangedYesBulk scripts, data fixes run by a DBA and some batch jobs write straight to the table. Only the log or a full comparison finds these.
Soft deleteYes. It is an update to a flag or a deleted-at timeYesAsk which column, and whether soft-deleted rows are purged later (that purge is a hard delete).
Hard deleteNo. There is no row left to findYes, with the key of the rowThe log, a delete log table in the source, or a comparison of keys. See the section on deletes below.
Truncate or table reloadBy accident. Every row looks new, or the table looks emptyIt depends. One truncate event, or nothing at allAsk whether any job empties and refills the table. If one does, treat the dataset as a full load.
Key changePartly. The new key appears; the old key never leavesYes, as a delete of the old key and an insert of the newAsk whether keys are ever corrected or merged (two customers made into one). Without the log, a key comparison finds the orphan.
Archiving and purgingNoYes, and it looks exactly like a business deleteHousekeeping removes old rows that the business still counts. Ask for the archiving rules, and do not delete from the platform when the source archives.
Late or backdated changeOnly inside the look-back windowYes, in the order it was committedA posting dated last month, entered today. Use the time the row changed to find it and the business date to place it.
Long transactionCan be missed for goodYes, when it commitsA row stamped 10:00 that commits at 10:07 is invisible to a load at 10:05, and the next load starts after 10:05. Re-read an overlap on every run.
Schema changeFails, or silently drops the new columnIt depends on the toolCompare the source's column list with the last run before loading. Ask for notice of changes; expect not to get it.

The worked example of a missed update and a missed delete is in How a dataset changes. Questions G (how the data changes) ask a source owner about each row of this table.

The routes

The routes out of a source, one by one

1 · Database, query-based extraction

We connect to the database and run a query. Either we read the whole table, or we read the rows whose modified time (or id) is higher than the highest one we loaded last time. That remembered value is called a watermark.

  • Good for: small and medium tables, sources with a modified time the database itself sets, teams that cannot get approval to read the log.
  • It cannot see: hard deletes, and any change that did not move the modified time.
  • Watch for: rows that commit late and fall behind the watermark, many rows sharing one timestamp at the boundary, a heavy query on the live system during business hours, and related tables read at different moments.
  • Do: read from a replica, re-read an overlap on every run and merge on the key, and compare keys or run a full comparison on a schedule to catch what the query missed.

More: Full load or incremental: the patterns · No last-modified column · Incremental loads

2 · Database, log-based replication (change data capture)

Every database writes each change to a transaction log before applying it, so that it can recover after a crash. A log reader follows that log and passes on every insert, update and delete, in the order they were committed. It does not query the tables, so the load on the source is small.

  • Good for: busy tables, deletes that matter, data needed within minutes, sources where the modified time cannot be trusted.
  • How it starts: a first full copy of the table (the snapshot), then the stream of changes from the position noted before the copy began. Rows changed during the copy arrive twice, and a merge on the key settles them.
  • Watch for: the database keeping log for us while our reader is stopped, which fills its disk; log removed before we read it, which forces a new snapshot; tables with no primary key; column changes on the source; one very large transaction arriving as millions of changes at once; and a reader that restarts and sends the last few changes again.
  • It needs: the right logging switched on (so that updates and deletes carry the old values), a DBA who agrees, and monitoring of how far behind the reader is.

More: Push, pull or share (log-based and trigger-based capture compared) · Change data capture · Applying a change feed as history

3 · The vendor's own extractor

Packaged applications such as SAP, Salesforce and Dynamics usually do not let other tools read their database. They provide their own way to hand data over, which knows the application's business rules and its deletes. SAP has several, covered in the next section.

More: Seven kinds of sources

4 · API

We call the application over the web and it returns records, a page at a time or as a bulk export. Changes come from a changed-since filter or a delta token.

  • Watch for: rate limits shared with other integrations, paging over data that is changing, tokens that expire, and deletes, which most APIs do not report.

More: API sources (kinds of APIs, pagination, limits, cost, deletes)

5 · File drop

The source writes files to an SFTP server or to object storage, and we pick them up. It is the oldest route and still the most common one between companies.

  • Watch for: files read before they are complete, files sent twice, files out of order, one file of a set missing, and columns that change without notice. A manifest answers most of these.

More: File sources (the file contract, done markers, corrections, formats)

6 · Stream

The source writes messages to a stream (Kafka, Kinesis, Event Hubs, Pub/Sub) and we subscribe. The stream keeps messages for a set time, so we can stop, restart and replay from where we were.

  • Watch for: the same message delivered twice, messages out of order across partitions, the shape of a message changing, messages that cannot be parsed (send them to a side queue, do not stop the stream), and falling behind by more than the stream keeps.

More: Streaming · questions O (streams and real time) · Queues

7 · Events and webhooks

The word covers two different things, and it helps to keep them apart.

  • Events that carry data. The app publishes "order placed" or "order cancelled" as it happens, or calls our address (a webhook). These hold business meaning that a table change does not. They are only as complete as the developers made them, so a catch-up pull is still needed.
  • Events that carry a signal. "The file has landed", "tonight's batch is complete". These carry no data. They tell us when to start loading, and they are what replaces a schedule. See Schedule or event.

More: Schedule or event · Outbox and webhooks

8 · Share or mirror

The source exposes its tables and we read them where they are, with no copy job. It works within a platform or a vendor family.

  • Watch for: who pays for the queries, how fresh the share is, and what happens to us when the owner changes or withdraws it.

More: Push, pull or share

A packaged application

SAP: several ways in, and none of them is "read the table"

SAP is the clearest case of a source with its own rules. The data sits in thousands of tables with short names, the licence usually forbids reading the database directly, and the business meaning lives in the application layer. So the first question is which routes this SAP system and this licence allow.

RouteHow it worksGood forWatch for
ODP with the business extractorsThe same extractors SAP BW uses. They hold the business logic and hand over a delta from a queue inside SAP that remembers what each subscriber has had.Finance, logistics and sales data on ECC and S/4HANA where an extractor existsNot every extractor has a delta; some are full only. Some logistics extractors need set-up runs. Starting the delta again means a new full load.
ODP on CDS viewsOn S/4HANA, views marked for extraction. Many carry change data capture, so the delta includes deletes.S/4HANA, and the direction SAP itself is takingOnly views released for extraction. Custom fields may need the view extended.
SLT replicationA replication server puts triggers on chosen tables and ships each change within seconds.Table-level, near real time, real deletesIt is trigger-based, so it adds work to every write. It needs its own server or add-on and a licence. You get raw tables with no business logic.
Table reads over RFCA function call that reads a table directly.Small configuration and text tablesNo delta. Row width and size limits. Never for the large document tables.
OData services, APIs and BAPIsSAP's published services, some with delta tokens.S/4HANA Cloud, where little else is allowed; small and medium volumesPaging and volume limits; delta tokens expire.
IDocs and business eventsSAP sends a message when a business document is created or changed.Feeding other applications as things happenOnly what was configured to be sent. It is not a complete copy of the data.
Reading the database or its logChange capture on the database underneath SAP.Rarely possibleMost SAP licences do not allow other tools to read the database. On older ECC systems some tables are stored packed and cannot be read this way at all.
BW or Datasphere as the hand-over pointSAP's own warehouse extracts, and we read its output.When BW or Datasphere already holds the data, modelledWe inherit its schedule, its transformations and its gaps. Check the licence terms for sending data out.

Check the licence before the design. Two limits surprise teams. Many SAP database licences cover use by SAP only, which rules out a log reader on the database. And SAP has published a note (3255746) saying that tools from other vendors are not permitted to call the ODP interface over RFC; the OData route is the supported one. Ask the SAP team and the tool vendor for their current position in writing.

SAP habits to know before the first load:

The same thinking applies to any packaged application: ask for the vendor's extraction route first, and ask what it does about deletes and custom fields.

Problem 1

Deletes: how do we know?

A row that was deleted cannot be queried, because it is not there. A scheduled query sees inserts and updates and reports success, and the deleted customer stays in every report. This is the most common way a platform drifts from its source.

There are seven ways to find out, best first:

WayHow it worksWatch for
Read the change logEvery hard delete arrives as an event carrying the key.The only route that needs nothing from the app. Needs the log switched on and kept long enough.
Soft delete in the sourceThe app marks the row instead of removing it. A query pull sees the update.Ask whether marked rows are purged later, and whether support staff ever delete directly in the database.
A delete log in the sourceA trigger or the app writes the key and time of each delete into a small table we read.A change on the source side; it has to be asked for. Cheap to read once it exists.
Compare the keysPull only the key column (narrow and cheap), and find keys we hold that the source no longer has.A failed or partial key pull looks like a mass delete. Use the same filter on both sides.
Compare a full snapshotRead everything and compare with what we hold.Fine for small tables and as a weekly safety net. Costly on large ones.
A deleted-records call on an APISome APIs list what was deleted, usually only for a limited number of days.Read it more often than the retention period, or deletes are lost for good.
A delete record in the file or streamThe row is sent with an operation of D, or an event with the key and an empty body.Has to be in the contract. A delta file with no delete records cannot carry deletes.

Three rules hold whichever way is used:

More: How a dataset changes · which load pattern sees hard deletes · questions G · deletes on APIs

Problem 2

Updates with no last-modified time, or one that cannot be trusted

An incremental query depends on one column: the time the row last changed. Many tables do not have it. The options when it is missing are set out in No last-modified column, or no date to order history. In short: read the change log; use a row version or change tracking feature if the database has one; compare a hash of each row with the last load; treat a table that is only ever added to as append-only and follow its id; or ask the source team to add a modified time set by the database.

The harder case is a column that exists and is wrong some of the time. It fails without any error. These are the ways it happens:

So an incremental query is never the only check. Re-read an overlap on every run (the last hour or day again, merged on the key), and run a full comparison on a schedule to measure how much the query misses. If the comparison keeps finding differences, the column cannot be trusted and the dataset needs another route.

More: Missing columns · Fields we ask for in every dataset · questions E and F

Problems 3 and 4

Big tables and full loads

The first load of a very large table

Every dataset is loaded in full at least once. For a table of a billion rows, a single query is the wrong tool. It runs for hours, the database gives up on keeping a consistent view for that long and ends the query, the network drops near the end, and everything starts again from zero. Meanwhile the table keeps changing.

What works is to treat the first load as a planned piece of work:

  1. Write down where you are starting from before reading anything: the log position or the time. Changes made during the copy are picked up from there afterwards.
  2. Split the table into chunks by key range, date or partition. Record each chunk as done, so a failure at 92% restarts one chunk and not the table.
  3. Read from somewhere that is not the live system if you can: a replica, a restored backup, or the database's own export to files.
  4. Run a few chunks in parallel, with the number agreed with the people who run the source, inside an agreed window.
  5. Check each chunk as it lands: row count and a total or two against the source.
  6. Switch to incremental from the position written down in step one. Rows copied and then changed arrive twice; a merge on the key makes that harmless.
  7. Load the newest data first when the business can use it before the history arrives.

Full loads that run every day

A daily full load is the simplest pattern and the right one for small tables: it needs no change column and it sees deletes. It has its own ways to fail.

ProblemHow it shows upWhat to do
It gets slower every monthA full load that took ten minutes at go-live takes three hours two years later and runs into the next one.Record the duration of every run. Move the dataset to incremental before it outgrows its window.
A half-finished load replaces good dataThe read fails at 60% and the target now holds 60% of the table.Load into a staging copy, check it, then swap it in as one step. A failed run leaves yesterday's table in place.
The source hands over nothingThe export broke and wrote an empty file. The full load faithfully empties the table.Compare the row count with the last run and stop when it falls outside an agreed range.
'Full' is not the whole tableThe extract is the last two years only. Everything older looks deleted.Write the extract's filter into the contract and apply the same filter when comparing.
History is overwrittenYesterday's values are gone, so nobody can say what a report showed last week.Keep each day's snapshot in bronze, or compare snapshots and store the changes.
The table changes while it is readA row moves from one page to another during the read and is seen twice or not at all.Read as of one moment (a snapshot read) where the database supports it.

More: Full load or incremental: the patterns · Take the data without hurting the system · questions F and K · Backfill

Problem 5

The file was still being written when we started reading

A large file takes minutes to write. A loader that picks up "whatever is in the folder" during those minutes reads the first part, finds a valid-looking file, and loads it. The day's total is 40% low and nothing failed. The same happens with a set of files when two of three have arrived.

The fix is for the source to tell us when it has finished. There are four ways, best first:

WayHow it worksVerdict
Write under a temporary name, then renameThe source writes orders.csv.tmp and renames it when finished. A rename is one step, so the real name never points at a partial file.Best. Needs the source to do it.
A done marker or manifest written lastA small second file appears only when the data file is complete.Best for sets of files, and it can carry counts.
An event when the object is completeObject storage shows an object only once the upload has finished, and can raise an event.Good for a single file. SFTP servers and shared drives do not behave this way. A set of files still needs a manifest.
Wait until the size stops changingCheck the size twice, some minutes apart.A last resort. A stalled upload also stops changing.

More: What a file contract holds · Common file problems, and the fix

A small file that solves a lot

What a manifest does

A manifest is a small file the source writes last, after the data files, that describes the delivery. A done marker says "finished". A manifest says "finished, and here is what you should have received". Checking the delivery against it turns silent problems into loud ones.

{
  "dataset": "orders",
  "business_date": "2026-11-18",
  "sequence": 412,
  "load_type": "delta",
  "schema_version": 7,
  "replaces": null,
  "files": [
    {"name": "orders_20261118_001.csv.gz", "rows": 120400, "sha256": "9f2c..."},
    {"name": "orders_20261118_002.csv.gz", "rows": 118950, "sha256": "41ab..."}
  ],
  "total_rows": 239350,
  "control_totals": {"order_amount": "18422019.55"},
  "written_at": "2026-11-19T02:40:07Z"
}
In the manifestThe question it answersWhat we do with it
files, with rows and checksumA file cut short, or damaged on the wayWe count and checksum each file before loading and refuse a mismatch.
the list itselfOne file of a set is missingThree files are listed and two arrived, so we wait or raise an alert, and do not load two thirds of a day.
business_dateWhich day this is, whatever time it arrivedA file for Friday that arrives on Monday is loaded as Friday.
sequenceFiles arriving out of order, or one skippedSequence 41 after 39 means 40 is missing. A delta is never applied over a gap.
load_typeIs this everything, or only changes?A full file can show deletes by comparison; a delta cannot.
replacesA corrected file sent againWe know it is a correction of an earlier delivery and not new data to add on top.
total_rows and control totalsProof that nothing was lostThe sum of amounts in the file equals the sum the source reported.
schema_versionColumns added or reordered without noticeA new version is held for review and not loaded into the wrong columns.
the manifest arriving at allEmpty file or broken export?A manifest saying zero rows means no data today. No manifest by the deadline means something broke.

The same idea works beyond files. An API export can return a count to check against. A database extract can come with a control row of counts and totals. We also keep a manifest of our own for every load, which is the ledger described under Keeping our place.

More: What a file contract holds · Where the answers go (the pipeline manifest, which is a different file: our settings for how a dataset is loaded)

Problem 6

Schedule or event: when should the load start?

A schedule says "start at 02:00". What we mean is "start when last night's data is complete". Those are the same on most nights, and the difference on the other nights is where wrong dashboards come from.

01:0001:3002:0002:3003:00On a clocksource is writing tonight's exportload starts at 02:00, reads a half-written file, and succeedsOn a signalsource is writing tonight's exportdone marker and manifest land at 02:40load starts at 02:41 and the counts match the manifest
The same night, twice. The source finished 40 minutes late. The scheduled load read half the data and reported success; the signalled load waited for the marker.
Problem with a scheduleWhat happensWith a signal from the source
The source finishes lateWe load half the data, or yesterday's again, and report success.The load starts when the source says it is ready, however late.
The source finishes earlyThe data sits unused until the clock comes round.The load starts the minute the data is ready.
Padding between chained jobsEach job is scheduled an hour after the one before, to be safe. Five jobs make five hours.Each step starts when the one before it finishes.
Time zones and clock changes02:00 happens twice one night a year and not at all on another.No clock is involved.
Month end and holidaysThe source runs a longer job, or does not run, and our schedule does not know.No data, no event, no load. A missing event is itself the alert.
A re-run on the source sideThe corrected data arrives after we have already loaded.The correction raises a new event and is loaded as a correction.

An event-based load has rules of its own:

Where the source cannot send a signal, keep the schedule and add a check in front of it: is the marker there, does the count look like a normal day, is the newest row recent enough? If not, wait and try again, then alert. A late load is far cheaper than a wrong one.

More: Schedule or event · questions D and K · Orchestration · Freshness

State

Keeping our place on each route

Every incremental route depends on remembering how far we have read. That bookmark is small, and losing it or moving it at the wrong moment is behind many gaps and duplicates. Two rules apply to all of them: move the bookmark only after the data is safely written, and make the load safe to repeat, so that reading a little too much is harmless.

RouteThe bookmarkWhat to know
Query-based pullThe highest modified time or id we have loaded, per tableSave it only after the load has committed. Re-read an overlap each run. If it is lost, do a full comparison.
Log-based replicationThe position in the log (the names differ by database: LSN, SCN, binlog position)If our reader is down for longer than the log is kept, the changes are gone and the table needs a new snapshot.
SAP ODPA subscription held in SAP's delta queueThe bookmark lives in the source. Starting the subscription again means a new full load.
APIA cursor, a delta token, or a changed-since timeTokens expire. Know how long they last and what a full resync costs.
File dropA ledger of every delivery loaded: name, checksum, manifest sequenceThe ledger is what makes a re-sent file harmless and a missing file visible.
StreamAn offset for each partition or shardMessages are kept for a fixed time. Fall further behind than that and there is a gap.
Events and webhooksThe ids of events already handled, and the time of the last catch-up sweepUsed to drop repeats and to know from when to sweep.
Share or mirrorThe version of the table we last readThe provider decides how long old versions are kept.

More: The metadata store, where bookmarks and the run log live · Idempotency

Problems 7 to 18

The rest of the problems to expect

Shorter, because each has a home elsewhere on the site.

ProblemHow it shows upWhat to doRead more
Duplicates and replaysEvery route re-sends: a retry after a timeout, a file sent twice, a log reader restarting from an earlier position.Make every load safe to repeat: merge on the business key and keep the newest by an ordering column.No key, or duplicates · Idempotency
Changes out of orderThe cancellation arrives before the order. Two updates carry the same timestamp.Name the column that orders versions and a tie-breaker. Apply by that order, never by arrival.No ordering column · questions H and O
Schema changesA column is added, renamed, widened or reordered, with no notice.Compare with the last known schema before loading. Accept additions, hold everything else for a person.Schema evolution · questions J
Slowing the source downA heavy read at 10 a.m. slows the order screen, and the access is withdrawn.Read a replica or the log, in agreed windows, with a cap on parallel reads.Protecting the source · questions C
Tables read at different momentsOrders read at 02:00 and order lines at 02:10. Lines exist whose order we do not hold.Read related tables as of one moment, or keep the orphans and join again on the next run.How joins go wrong · Learning the joins
Paging over data that is changingRows shift between pages while we read, so some are skipped and some repeat.Page by a stable key or a cursor, never by page number.API pagination
Limits and quotasThe daily API quota is shared with five other integrations and runs out at 3 p.m.Know the quota and who else uses it. Prefer bulk exports for volume.API load and cost
Time zones and clocksA timestamp with no zone. A business day that ends at 6 a.m.Record the zone for every date column and store one standard zone.Fields in every dataset · questions H
Text and number formatsAn accent turned to a question mark, 1.234 that meant one thousand, a comma inside a name.Fix encoding, separators and quoting in the file contract.Common file problems
No proof that nothing was lostThe load is green and the total is 2% low. Nobody can say where.Reconcile counts and totals with the source on every run.questions N · Data quality
Personal and restricted dataA free-text notes column holding card numbers.Classify columns before the first extract, and mask at the earliest point.questions M · Why we scan your data
Nobody owns the sourceA column changes meaning and nobody tells us, because nobody knew we read it.A named owner and a data contract for every source.questions A · Data contracts

The full set of challenges by layer, with the checks that catch them, is in Hard problems by layer.

Deciding

Choosing a route for a source

The route is chosen per dataset, and a source often uses two: one for daily changes and a slower one that proves nothing drifted. In rough order of preference:

  1. If the source can share or mirror its tables on a platform we already use, read them in place.
  2. If it is a packaged application, use the vendor's extractor. Do not read the database underneath without checking the licence.
  3. If it is a database we may read and deletes or freshness matter, read the change log.
  4. If the log is not allowed, pull by a modified time the database sets, re-read an overlap, and compare keys on a schedule to find deletes.
  5. If the table is small, load all of it every time. It is the simplest route and it sees everything.
  6. If the only way in is an API, use bulk export for volume and changed-since calls for the rest, with a full resync now and then.
  7. If the source can only send files, agree the contract and the manifest first.
  8. Whatever the route, start the load on a signal from the source where one exists, and check completeness before loading where it does not.

The options and verdicts are in The decisions this block forces, and the reasons get written down as described in Where the answers go.

FAQ

Questions people ask

What are the main ways to extract data from a source system?

Eight: a query that pulls what changed, replication from the database's transaction log (change data capture), the vendor's own extractor for packaged applications such as SAP, an API, files the source drops, a stream we subscribe to, events or webhooks the source sends, and tables shared in place. Most estates use several.

How do you detect rows deleted in a source system?

A hard delete leaves no row to query. The ways to see it are to read the database's change log, to have the source mark rows as deleted instead of removing them, to read a delete log the source keeps, or to compare the source's keys with ours and treat the missing ones as deleted. A guard should stop the load when far more rows than usual appear deleted.

What if a table has no last-modified column?

Read the change log if the database allows it, use a row version or change tracking feature if the database has one, or read the whole table and compare a hash of each row with the last load. Asking the source team to add a modified time set by the database is also reasonable.

How do you avoid reading a file that is still being written?

Do not load a file because it exists. Load it when the source says it is complete: the file is renamed from a temporary name, or a done marker or manifest is written after it. A manifest also lists the files, their row counts and checksums, so a partial or missing file is caught before loading.

Why is event-based ingestion better than a schedule?

A schedule starts at a fixed time and assumes the data is ready. When the source is late, the load reads partial data and still succeeds. An event-based load starts when the source signals that the data is complete, so lateness delays the load and does not corrupt it. Keep a catch-up sweep and a deadline alert, because events can be lost.

Next

Where to go next

The questions to ask: Upstreams: know your sources and the printable questionnaire.

In depth: API sources · File sources · Learning the joins · Where the answers go.

How we build it: Ingestion: sources to bronze · Streaming · Hard problems by layer.

Back up: Data systems, end to end.