Home › Learn › Data systems › Upstreams

Data systems · block 1 of 3 · for data leaders, then builders

Upstreams: know your sources

Know how each source behaves before writing a single pipeline.

This is the page to walk a client through before any build. It lists the kinds of sources, the ways each can hand data over, and the questions we need answered for every source and every dataset, each with the reason we ask.

For leaders

Printable questionnaire for your team

In plain words

Ask about the well before laying the pipe

An engineer connecting a town to a new well asks a few things first. How much water does it give? When does it run low? Does it flood? Who owns it, and will they tell us before they dig a new one? Is the water clean, and can we take a sample to test? None of this is about the pipe. All of it decides whether the pipe works.

Source systems are the wells. They were built to run a business, not to feed a data platform. Each one has habits: how it records changes, whether it really deletes rows, when its nightly jobs finish. The questions on this page find those habits early, while changing course is cheap.

The big picture

Sources, the ways out, and what goes wrong

Before the detail, the whole map. A source is one of a few kinds, its data leaves by one or more of eight routes, and each route sees different changes: a scheduled query misses deletes, a log reader sees them. The problems in the bottom band are the ones to expect on every project.

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.

Read the big picture   Query-based and log-based extraction, the SAP routes, APIs, file drops, streams and events; how to find deletes and missed updates; big first loads; half-written files and manifests; schedule or event.

Kinds of sources

Seven kinds of sources

Most estates mix all seven. Each kind tends to offer some ways of handing data over and to fail in its own ways.

KindExamplesUsual ways to get the dataWhat to watch
Operational databasesOracle, SQL Server, PostgreSQL, MySQL behind your own appsLog-based CDC, read replica query, full extractLoad on the live primary; hard deletes; bulk fixes run directly in the database
Packaged and SaaS appsSAP, Salesforce, Dynamics, Workday, ServiceNowVendor extractor or native connector, bulk API, change eventsLicence and API limits, custom fields, vendor upgrades changing the schema
FilesCSV or Excel from partners, SFTP drops, exports to object storageSource drops files (push), we poll a folder (pull)Partial files, re-sent files, column order changes, no done marker
APIsPartner and payment APIs, public data, internal servicesPoll with a changed-since filter, webhooksRate limits, paging over changing data, no way to see deletes
Events and streamsKafka, Event Hubs, Kinesis, Pub/Sub, IoT, clickstreamSubscribe (push), replay from an offsetDuplicates, out-of-order events, schema changes inside messages, retention
Shares and mirrorsDelta Sharing, Snowflake shares, Fabric mirroring, BigQuery sharingRead in place, no copy jobWhose cost it is, freshness, and what happens when the share is changed
Spreadsheets and manual dataTargets, mappings, budgets kept by handUpload to a controlled place, form, or file dropNo owner, no versioning, silent edits to past values

Integration patterns

Push, pull or share

There are three basic ways data can reach us. The source can push changes to us, we can pull them on our schedule, or the source can share tables that we read in place. Which one is possible depends on what the source supports, so we ask about all of them.

Source systemdatabase, app,files, APILanding / bronzeoursPush: the source sendschange log (CDC), events, webhooks,vendor extractor, files it dropssees deletes · starts when data is readyPull: we fetchquery since last load, full extract,API polling, folder pollingneeds a trusted 'modified' column · runs on a clockor: read in place (share, mirror)
Push or pull. Push catches deletes and starts when data is ready; pull is simpler to start but runs on a clock and needs a trusted change column.
PatternPush or pullHow it worksSees hard deletes?Good forWatch for
Log-based CDCPushReads the database's transaction log and emits every insert, update and delete in order.YesBusy tables, deletes matter, low latency, minimal load on sourceNeeds logging switched on and enough log retention; DBA approval
Trigger-based CDCPushDatabase triggers copy each change into a change table.YesRarely; only when the log can't be readSlows every write on the source. Usually an anti-pattern
Outbox or business eventsPushThe app publishes an event (order placed, order cancelled) alongside its own write.If designed inEvent-driven apps, real-time needs, meaning-rich changesOnly as complete as the developers made it; replay rules
WebhooksPushThe source calls our endpoint when something changes.SometimesSaaS apps and paymentsMissed calls when our side is down; need a catch-up pull
Vendor extractor or connectorPush or pullThe vendor's own tool hands changes over (for example SAP ODP, Salesforce change events or bulk API).UsuallyPackaged apps where direct database reads are not allowedLicensing, connector limits, version upgrades
Source drops filesPushThe source writes files to a place we watch, ideally with a done marker.Only if sentPartners, legacy systems, mainframesPartial and re-sent files; no deletes unless agreed
Incremental query (watermark)PullWe read rows whose modified time is after the last load.NoTables with a trustworthy modified column and soft deletesMisses hard deletes and any change that doesn't touch the column
Full extractPullWe read the whole table every time and compare.Yes, by comparisonSmall tables, reference data, safety net for reconcilingCost and time grow with the table
API pollingPullWe call the API with a changed-since filter and page through results.RarelySaaS without eventsRate limits; unstable paging skips or repeats rows
Share or mirrorNeither (in place)The source exposes tables we read without copying.Yes, in the shareSame platform or same vendor familyWe don't control freshness or schema changes

Timing

Schedule or event

A scheduled load starts at a fixed time and hopes the data is ready. On the night the source runs late, it loads half a file, an empty folder or yesterday's data, and nothing fails. Most "the dashboard is wrong this morning" incidents start this way.

An event-driven load starts when the source says the data is ready: a change arrives on the CDC stream, a file lands with its done marker, or the source publishes a "batch complete" event. Time stops being a guess. So we ask every source whether it can signal readiness. When it can't, we still schedule, but we check completeness first (the expected row count, the marker file, the control total) and wait or alert instead of loading.

How data changes

How a dataset changes, and why we keep asking

Here is a customer table on day one, and the same table the next day. We load incrementally using updated_at, taking only the rows changed since the last load.

Day one

customer_idnamecityupdated_at
101AshaPune10:02
102RaviDelhi10:05
103MeenaAgra10:09

Day two: Asha moved to Mumbai, Ravi was deleted, Joseph was added

customer_idnamecityupdated_at
101AshaMumbai10:02
103MeenaAgra10:09
104JosephKochi09:15 (next day)

Look at what the incremental query sees. Joseph is new, so his row is picked up. Asha's city changed, but the app's bulk-update script didn't touch updated_at, so her change is missed. Ravi was hard-deleted, so there is no row left to find, and he is still a customer in our platform. The load succeeded, and two of three changes were lost.

That is why the question list asks which kinds of change happen, who sets the modified time, and whether deletes are hard or soft. The answers decide between an incremental query, change data capture and a full comparison; the load patterns below set them side by side.

In depth

Go deeper by source type and problem

The big picture

Every route out of a source on one map, what each can and cannot see, and the problems to expect: deletes, missed updates, big loads, half-written files, schedules.

API sources

Bulk versus paged APIs, pagination that doesn't lose records, rate limits and shared quotas, cost per call, deletes and completeness.

File sources

The file contract, done markers, corrections, order, encodings, column changes, and the common file problems.

Learning the joins

Declared keys, joins already in use, candidates measured on the data, owner sign-off, recorded in the data contract.

Where the answers go

One home per answer: the ODCS contract, the manifest, Unity Catalog, tests, the ontology, the code. A worked example, and liquid clustering choices.

Full or incremental

Full load or incremental: the patterns

Every dataset is loaded one of a few ways. A full load is the simplest and the safest when the table is small or the source has no reliable way to say what changed. An incremental load is cheaper for big tables but needs a change signal you can trust and a plan for deletes. Most platforms use both: incremental every run, and a full comparison now and then to prove nothing drifted.

PatternHow it worksSees hard deletes?Use it forWatch for
Full replace (truncate, then load)Empty the table and load the whole extractNoAlmost neverAn empty or half-full table if the load fails; no record of what changed
Full snapshot and compareLoad the whole extract to staging, then merge by keyYesSmall and medium tables, reference data, sources with no reliable change signalCost grows with the table; needs a trustworthy key
Period reloadReplace a whole period or partition (last 7 days, this month) each runWithin the periodSources that restate recent dataChoose the window from how far back changes really happen
Incremental by watermarkRead rows changed since the last load, using a modified columnNoLarge tables with a trustworthy modified column and soft deletesMisses hard deletes and changes that skip the column; needs an overlap window
Incremental plus key compareIncremental for changes, plus a regular key-only full read to find deletesYes, on the compare scheduleLarge tables with hard deletes and no CDCDeletes show up late, at the next compare
Change data captureRead every insert, update and delete from the database logYesBusy and large tables where deletes and order matterLog retention, capture lag, DBA approval
Initial load, then incrementalOne chunked full load of history, then incremental from a recorded positionDepends on the incremental methodEvery new source, onceThe hand-over point: record it before the full load starts

Questions F1 to F8 below decide which pattern each dataset gets.

Protecting the source

Take the data without hurting the system

The source system runs the business; our extraction is a guest. If we slow it down once, we lose access. These rules keep it safe (questions C1 to C7 below):

Relationships

How datasets join, and how joins go wrong

Silver and gold are built by joining datasets: orders to order lines, lines to products, customers across the CRM and billing. Two things go wrong. A join assumed to match one row that sometimes matches two multiplies numbers: an order with two rows in a "one per order" table counts its revenue twice. A join to a parent that is missing drops rows: order lines whose order hasn't arrived disappear from an inner join. So we ask for the relationship map, then measure in profiling how many rows really match on each side and how many have no parent (questions I1 to I7 below).

No key, or duplicates

When there is no business key, or there are duplicates

Many sources have no reliable business key, and many send duplicates. Neither stops a platform, but each needs a decision written down per dataset. Bronze always keeps every row exactly as it arrived; the decisions below are applied from bronze to silver, and anything that can't be decided is quarantined with a reason (questions H1 to H5 below).

SituationExampleWhat we doWatch for
No business key, but a column combination is uniqueProfiling finds, say, (store, till, receipt_no) uniqueUse it as the key once the owner confirms it; test its uniqueness on every loadA combination that is unique today may not be next year
No key at all, and rows changeA spreadsheet or export with no idLoad full snapshots and compare whole rows: a new row is an insert, a missing row a delete, an update shows as bothHistory is coarse; ask the source to add an id
No key, rows never change (events, logs, readings)Each row is a fact that happenedLoad as events; give each row an id from its content and position (file and line, or a hash plus a counter)A hash alone merges real identical events
Exact duplicates from resendsThe same file or message arrived twiceBronze keeps both; silver keeps one, using the event id or a hash of the row within the overlap windowCount them; a rising duplicate rate is a source problem
Same key, different valuesTwo versions of one orderKeep the latest by the source's sequence or log position, with a fixed tie-break; quarantine real conflictsTimestamps tie more often than people expect
Real repeats that look identicalTwo identical purchases in the same secondKeep both; separate them with a line number or event idRemoving duplicates by default under-counts
One real thing under two keysThe same customer created twiceMap both to one master id with matching rules the business ownsMerging is a business decision; never do it silently

Keeping history

Type 2, Type 1, both, or overwrite: which dataset gets which

Type 1 overwrites a value, so only today's is kept. Type 2 adds a new version with 'valid from' and 'valid to' each time a tracked value changes, so reports can see it as it was. Both means Type 2 on the few columns reports group by and Type 1 on the rest. Some datasets need none of these: events are appended, balances are snapshotted, and small derived tables are simply overwritten. The business owner decides which columns need history (questions G2 and G3 below).

Kind of datasetExamplesTreatmentWhy
Master data whose history mattersCustomer region and segment, product category and price, employee departmentBoth: Type 2 on the columns reports group by; Type 1 on the restReports by region or category must show the value at the time
Master data where only today mattersPhone, email, address for contact, descriptionsType 1 (overwrite)Nobody reports by an old phone number
Reference and code listsCountry, currency, status codes, unitsType 1, or a full overwriteSwitch to Type 2 only if a code's meaning changes over time
Hierarchies and mappingsSales territory, cost-centre tree, customer to accountType 2As-of reporting needs the tree as it was
Regulated or contractual termsPrices, credit limits, policy terms, interest ratesType 2 on every columnAuditors ask what was true on a given day
Immutable transactions and eventsPayments, clicks, sensor readings, log linesAppend only, no SCDCorrections arrive as new reversing rows
Transactions that change stateOrders, tickets, claims, shipmentsLatest row (upsert), plus an append-only status history when the journey mattersNot Type 2 on huge fact tables
Balances and levelsStock on hand, account balance, headcountPeriodic snapshot: append one row per item per day or monthNot SCD: the history is the snapshot series
Small lookups and derived tablesConfiguration, mapping files, aggregates rebuilt from silverFull overwrite every runOnly when nothing downstream needs their past

One warning: personal data in Type 2 history must still be erasable on request. Plan for the deletion to reach every version.

Missing columns

No last-modified column, or no date to order history

Two columns decide most of the design: one that says when a row last changed (for incremental loads) and one that puts versions in order (for history, SCD Type 2). When a source has neither, these are the options, best first (questions E3 and H7 below).

MissingOptionWhat you getWatch for
No last-modified columnChange data capture from the database logEvery insert, update and delete, in order, with a commit timeNeeds log access and DBA approval
No last-modified columnA row version the database keeps (SQL Server rowversion, Oracle ORA_ROWSCN, PostgreSQL xmin)A cheap 'changed since' filter without touching the appTest it first: ORA_ROWSCN is per block unless row dependencies are on; xmin wraps around
No last-modified columnFull read, comparing a hash of each row with the previous loadAlways works; finds inserts, updates and deletesCost grows with the table; read in key ranges
No last-modified columnAsk the source to add one, maintained by the databaseThe long-term fixA source change: plan it with the owner
No date to order versions (SCD Type 2)CDC commit time plus log sequenceExact order and an exact 'valid from'Only when CDC is available
No date to order versions (SCD Type 2)A source version or sequence numberCorrect order; 'valid from' is our load timeTwo changes between loads collapse into one version
No date to order versions (SCD Type 2)Our load time, labelled 'first seen by the platform'Honest history of when we saw each changeIt is system time, not business time; say so in the contract
No date to order versions (SCD Type 2)File order for file feeds (file timestamp, then line number)Order within and across filesRe-sent files must not reorder history

One rule covers both: if 'valid from' is our load time rather than the business's date, write that in the data contract, so nobody reads "first seen by the platform" as "happened then".

The question list

Every question, why we ask, and what breaks if nobody does

One list, grouped by category. Ask it once per source system, then once per dataset for categories F, G, H and I. The fourth column is what we have seen go wrong in production when the question was skipped.

#Question to chaseWhy we askWhat breaks in production if nobody asksDo this (not this)
A · Ownership and changewho answers questions and who tells us about changes
A1Who owns this system, and who knows its data best?Someone has to answer 'is this value right?' and approve fixes on the source side.Questions wait for weeks; a data bug can't be fixed because nobody owns the source.Name a business owner and a technical owner per source in the data contract. Not: 'ask the app team'.
A2When does the source change (releases, upgrades, patches), and who tells us beforehand?A column renamed on their side is a broken pipeline on ours.Loads fail the Monday after a weekend release, or worse, load wrong values without failing.Get on their release calendar; the contract sets a notice period. Not: finding out from a failed job.
A3Is this system being replaced, migrated or merged in the next year?A deep integration with a system that retires in six months is wasted work.The integration is rebuilt from scratch, and history from the old system is lost.Build the old source behind the same contract the new one will meet. Not: custom logic tied to the old system's quirks.
A4What is the source's own support window and service level?Our freshness promise can never beat theirs.We promise data by 7 a.m.; their batch finishes at 8 a.m. on busy days.Derive our service level from theirs, with a margin. Not: promising a time before asking.
B · Access routehow we are allowed to reach the data
B1How can we reach the data: direct database read, a read replica, an API, files, an event stream, or a share?The route decides the tools, the security work and the cost.A route chosen late means security approvals start again and go-live slips.List every route the source supports and choose in discovery. Not: defaulting to whatever worked last project.
B2Can we read from a replica, standby or backup instead of the live system?Our extract must never slow down their customers.A month-end extract locks tables and the app slows down; we get switched off.Read a replica, or use log-based CDC. Not: heavy queries on the primary in business hours.
B3What are the limits: API rate limits, rows per call, extract windows, connection counts?Limits decide how long a backfill takes and how we page and retry.Throttled 80% of the way through a backfill; a partial load looks complete.Design paging, retries and checkpoints around the published limits. Not: discovering the limit in production.
B4What network path and credentials do we need: private link or VPN, firewall rules, a service account, password rotation?These requests are slow; they decide the start date, not the code.Go-live waits on a firewall ticket; a password expires and loads stop with nobody alerted.Raise access requests on day one; keep secrets in a vault with expiry alerts. Not: a personal account in a config file.
C · Protecting the sourceour extraction must never hurt the system that runs the business
C1How much load can the source take, and when: peak hours, nightly batch, month-end close?Our extract competes with their business for the same database.An extract during month-end close slows the finance system, and our access is switched off.Agree an extract window and a load budget (queries, connections, rows per second) and throttle to it. Not: running whenever suits us.
C2Will we read through a read-only account with access to only the tables we need?An extraction account that can write, or see everything, is a risk to them and to us.A mistaken statement in a tool locks or changes source tables.A read-only service account with SELECT on named tables or views, no schema changes. Not: borrowing the app's or an admin account.
C3Do our extract queries use indexes, and has the source's DBA reviewed them?A filter on a column with no index reads the whole table on every run.An hourly 'changed since' query on an unindexed column scans 500 million rows each time.An index on the change column (or CDC instead), and a DBA review of every extract query. Not: SELECT * with an unindexed filter.
C4How do our long reads avoid blocking their writers?Long reads can hold locks or grow the source's undo and temp space.A three-hour extract blocks order updates, or fails with 'snapshot too old'.Read from a replica or a snapshot, and read in chunks by key range with short transactions. Not: one long transaction over a busy table.
C5For CDC: what does change capture cost the source, and who watches it?Change capture keeps log on the source until we read it.Our pipeline is down for a weekend; the replication slot keeps every log file, the source's disk fills and the app stops.Alerts on capture lag, a cap on retained log, and a runbook to drop the capture safely. Not: set up and forget.
C6Do we share an API quota with other users of the source, and how do we stay inside our part?API limits are often per company, not per integration.Our backfill uses the whole day's quota and the sales team's other integrations stop.Our own share of the quota, backoff on limit errors, backfills out of hours. Not: retrying as fast as possible.
C7Can the source team stop our extraction quickly if they need to?They must be able to protect their system without finding us first.During an incident on the source, nobody knows how to stop our jobs.A named contact, a pause flag in our metadata and a documented stop procedure. Not: 'call the data team'.
D · Push supportwhat the source can send us without being asked
D1Can the source push changes: log-based CDC, a vendor extractor, business events, webhooks, or files it drops?Push captures every change, including deletes, as it happens, without scanning tables.Polling misses deletes and the in-between states of a row (created, then cancelled within the hour).Prefer log-based CDC for busy databases. Not: trigger-based CDC, which slows every write.
D2For CDC: is the transaction log switched on at the level we need, and how long is it kept?If the log is cleared before we read it, those changes are gone for good.A two-day outage on our side outlasts a one-day log retention, and a full reload is forced.Keep log retention longer than our longest likely outage; alert on CDC lag. Not: default retention.
D3For events and webhooks: is delivery at least once, is order kept, can we replay, is there an event id and a sequence number?These answers decide how we remove duplicates and put changes in order.Duplicates double the counts; an old update arriving late overwrites a newer value.Remove duplicates on the event id; order by the source's sequence, not arrival time. Not: trusting arrival order.
D4Does the source say when a batch is complete: a done file, a row count, an event?Without it we cannot tell a half-written file from a whole one.A partial file loads, and the day's total drops by 40% with no error.Ask for a done marker with a row count (and ideally a checksum). Not: 'load whatever is in the folder at 2 a.m.'.
E · Pull supportwhat we can fetch, and whether we can trust it
E1Is there a column that reliably says when each row last changed?Incremental loads depend on it completely.Updates that don't touch the column are missed for months, and nobody notices.Test it during profiling: change a row, check the column moves. Not: trusting the column name.
E2Is that column set by the database on every change, including bulk fixes and scripts, or by the app?App-set times skip changes made outside the app.A DBA's correction script changes 10,000 rows and incremental loads never see it.Prefer a database-maintained time or CDC; ask the DBA how fixes are applied. Not: assuming every change goes through the app.
E3If there is no modified column at all, what else can show that a row changed: CDC, a row version the database keeps, or comparing row hashes?Every incremental method needs some change signal.The team falls back to a nightly full reload of a huge table that soon no longer fits the window.In order: CDC; a database row version (rowversion, ORA_ROWSCN, xmin) after testing it; a full read comparing a hash of each row with the last one; or ask the source to add the column. Not: guessing from other dates.
E4Can the API filter by changed-since time, and does it page in a stable order?Paging by position over changing data skips or repeats rows.Rows move between pages during the read; a few go missing on every run.Use cursor or key-based paging and a small overlap window. Not: page numbers over live data.
F · Full or incremental loadper dataset: reload everything, or only what changed
F1How big is each dataset, and how long does a full extract take and load the source?A full load's cost grows with the table, every run.A daily full load of a 2 TB table runs nine hours, spills into business hours and slows the source.Full loads for small and reference tables; incremental or CDC for large ones. Not: full for everything because it's simpler.
F2Is there any reliable way to get only the changes: a trusted modified column, CDC, or a change API?Without one, a full extract compared with what we hold is the only safe option.An incremental load built on an untrustworthy column silently misses changes for months.No reliable change signal means full snapshot and compare. Not: incremental on a guess.
F3For a full load, do we replace the table, or compare the new snapshot with what we hold?Replacing can't tell what changed and leaves the table empty if the load fails midway; comparing finds inserts, updates and deletes.Truncate-then-insert fails halfway and dashboards show an empty table for an hour.Land the snapshot in staging, check its row count, then merge by key or swap in one step. Not: truncate, then insert.
F4Can the source re-send a whole period (the last 7 days, the current month) instead of everything?A period reload is the middle ground for sources that restate recent data.Overlapping windows are appended and recent days are counted twice.Replace whole periods or partitions so a re-send is safe to repeat. Not: appending overlapping windows.
F5On incremental loads, how will we see rows that were deleted at the source?A 'changed since' query can't return a row that no longer exists.Cancelled and deleted orders stay in the platform forever.CDC, a soft-delete flag, or a regular key-only full extract compared with our keys. Not: ignoring deletes.
F6How often should a full comparison run alongside incremental loads: weekly, monthly?Incremental loads drift: missed changes, bulk fixes, clock problems.Small differences pile up for a year and nobody can say when they started.A scheduled full (or key and count) comparison that reports and repairs differences. Not: incremental forever with no check.
F7How will the first, historical load run: one big extract, or in chunks by date or key, and in which source window?The first load is the biggest and the most likely to hurt the source.A 30-hour initial extract fails at hour 28 and starts again from zero.Chunked, resumable loads with checkpoints, in an agreed window. Not: one giant query.
F8At what exact point do we switch from the first full load to incremental?A gap between the two loses rows; an overlap doubles them.Changes made while the initial load ran are never picked up.Record the CDC position or watermark before the full extract starts, begin incremental from it, and let the merge remove the overlap. Not: starting incremental 'from now'.
G · How the data changesthe answers that decide the load method and how silver merges
G1Which of these happen: inserts only, updates, hard deletes, soft deletes?This decides the load method and how the next layer merges rows.Hard deletes never reach the platform; cancelled orders keep counting as revenue.With hard deletes and no CDC, read all keys regularly to detect what disappeared. Not: assuming insert-only.
G2For each entity, which changes must reports see as they were at the time (keep history), and which are corrections that should simply replace the old value?This decides Type 2, Type 1 or both, column by column.Last year's sales move to a customer's new region, or a typo fix creates a fake 'version'.The business owner names the columns whose history matters (Type 2); everything else is overwritten (Type 1). Not: Type 2 on every column by default.
G3Do transactions change after they are created (an order's status), and do we need that history or only the latest state?Changing transactions are not dimensions; they need their own treatment.Only the final status is kept, so 'how long did orders sit in Packed?' can't be answered.Keep the latest row (upsert) plus a separate append-only table of status changes when the journey matters. Not: SCD Type 2 on a billion-row fact table.
G4Can old records change: late arrivals, backdated entries, corrections to a closed period? How far back?This sets how far back each load looks, and when a period counts as final.Last month's numbers change after the board has seen them, and nobody can explain why.Set a look-back window from the answer, and agree a period-close rule. Not: loading only 'yesterday'.
G5Does the source ever resend or restate a whole period, or the whole table?A resend must replace, not add to, what we already hold.A re-sent month doubles that month's revenue.Make loads safe to repeat: merge on the key, or replace the whole period. Not: plain appends.
G6Can a business key change, be reused, or be merged (two customer records joined into one)?History must follow the real thing, not the number.Order history attaches to the wrong customer after a key is reused.Keep a stable key of our own and capture merge events. Not: treating the source key as eternal.
G7Are any changes made outside the app, such as data fixes, migrations or mass updates?These are the changes most likely to bypass change tracking.Silent gaps after every quarterly cleanup.Ask for the fix log; prefer CDC on sources with frequent fixes. Not: hoping fixes are rare.
H · Keys and dateswhat makes a row unique and which date means what
H1What is the business key of each dataset: one column or several? Is it really unique?Keys drive de-duplication, merging and every join after this.A key that isn't unique multiplies rows in joins and inflates totals.Check uniqueness on real data during profiling. Not: trusting the documentation.
H2If there is no business key, what does identify a row: a technical id, a combination of columns, or nothing at all?Merging, change detection and deletes all need some identity.Without one, every update looks like a new row and history doubles on every load.Search for a column combination that is unique in profiling and confirm it with the owner; if none exists, load the dataset as full snapshots or as events. Not: inventing a key the business doesn't recognise.
H3Can two identical rows both be real (two identical purchases in the same second), or is an identical row always a resend?Removing duplicates is right for resends and wrong for real repeats.Two real orders collapse into one and revenue is understated.Ask the owner, and look for a line number, event id or sequence that separates them. Not: removing every identical row by default.
H4When two rows share a key but differ, which one wins, and what breaks a tie?Rows with the same key are usually versions of one thing.Two versions with the same timestamp; the 'latest' row changes from run to run.Keep the latest by the source's sequence or log position, with a fixed tie-break; quarantine true conflicts. Not: 'pick any one'.
H5Can the same real thing appear under two keys (one customer created twice)?Duplicate entities inflate counts and split history.Customer count is 12% too high and loyalty history is split across records.Map duplicates to one master id with matching rules the business owns (entity resolution). Not: merging them silently in silver.
H6Which date means what: transaction, effective, posting, created, modified?Reports must use the right date for the right question.'Sales by day' on our dashboard disagrees with finance because they use posting date.Name every date and its use in the data contract. Not: one column called 'date'.
H7What puts the versions of a row in order for history (SCD Type 2), and if nothing does, what will 'valid from' mean?History needs an order and a start time for each version.Versions land in the wrong order after a backfill, and 'customer region on the order date' is wrong.Use the CDC commit time and log sequence, or a source version number; otherwise use our load time and label it 'first seen by the platform'. Not: pretending load time is business time.
H8What time zone and precision are times stored in, and how is daylight saving handled?Rows near midnight and on clock changes go to the wrong day, or twice.Daily totals are off on the day the clocks change, every year.Store UTC alongside the source's local time and zone. Not: guessing the zone from the server.
I · Relationships and joinshow datasets connect, so silver and gold join them correctly
I1Which datasets relate to which (order, order lines, product, customer), and through which columns?Silver and gold are built by joining; the joins and the load order come from these relationships.Gold joins on the wrong column and totals are quietly wrong.A relationship map per source, confirmed by the owner and checked in profiling. Not: guessing from column names.
I2For each relationship, how many rows can match on each side: one-to-one, one-to-many, many-to-many?A join that is assumed one-to-one but isn't multiplies rows.Orders joined to a 'one per order' table that sometimes has two rows: revenue doubles for those orders.Measure it in profiling and test it on every load. Not: trusting the documentation.
I3Are foreign keys enforced in the source database, or only by the application?Unenforced relationships produce orphans: children without parents.Order lines point to orders that don't exist; an inner join silently drops their revenue.Profile orphan rates; decide per relationship whether to keep orphans under an 'unknown' parent or quarantine them. Not: inner joins that hide them.
I4Do related tables arrive together, from the same point in time?Parent and child read at different moments are out of step.Order lines arrive before their order header and drop out of the join until the next day.Read related tables from the same snapshot or log position, or handle late parents explicitly. Not: hoping the timing works out.
I5How do the same things link across systems (customer id in the CRM versus in billing)?Gold joins across sources; those joins need a known mapping.Orphan rows, and customers counted twice.Profile match rates across systems; give the mapping table an owner. Not: fuzzy joins in a dashboard.
I6Must some joins use the value as it was at the time (the customer's region on the order date)?Joining to today's value rewrites history.Last year's sales by region move every time a customer moves.Join as of the transaction date to the history kept in silver (SCD Type 2). Not: always joining to the current row.
I7Which join paths do the reports users trust today use?Trusted reports hold business rules inside their joins.Our joins are 'correct' but don't match the numbers the business knows.Get the logic of the trusted reports and test our joins against it. Not: designing joins from scratch.
J · Schema and meaningwhat the columns and codes mean
J1Is there a data dictionary? What do the status codes and flags mean?A code means nothing to us without its definition.'Status 7' means cancelled in one region and refunded in another; revenue is wrong in both.Load code lists as reference data with an owner. Not: hard-coding codes in transforms.
J2How often does the schema change, with how much notice, and are changes only additions?Added columns are easy; renamed, retyped or removed columns break things.A column changes from number to text; the load succeeds and every value becomes null.Detect schema drift on every load; allow additions, stop on breaking changes. Not: silent type coercion.
J3Are there splits by company, country, tenant, currency or unit?Numbers in different currencies or units cannot be added.Rupees and dollars summed into one revenue figure.Carry company, currency and unit as columns on every row. Not: assuming one of each.
J4Are there free-text fields, JSON blobs or customer-specific custom fields?They need their own parsing, and often hold personal data.Names and phone numbers hidden in a 'notes' field reach a dashboard.Keep them raw in bronze; parse and scan them on purpose. Not: flattening blindly.
K · Volume and timinghow much, how fast and when
K1How many rows exist today, how many arrive per day, how fast is it growing, and when are the peaks?Sizing, cost and partitioning come from these numbers.The month-end load runs ten times longer and misses its deadline.Size for the peak, not the average; test with peak volume. Not: sizing from one quiet day.
K2How much history do we need, and does the source still hold it?This decides the backfill plan.The source keeps only 13 months; the three-year trend users asked for can't be built.Agree history needs early; secure archives before they are purged. Not: discovering purge rules at backfill.
K3When is the data ready at the source each day, and how fresh do users need it?Freshness is a chain; every link must fit.The dashboard refreshes before the data has landed and shows yesterday as today.Start from the user's need and work back through each step. Not: picking a schedule time first.
K4Can the source tell us when data is ready, so we start on that signal instead of a clock?Starting on a signal removes most late or partial loads.The 2 a.m. job runs on an empty folder because the source was late tonight.Prefer an event, file-arrival trigger or done marker. If only a schedule is possible, add a completeness check before loading. Not: a fixed time with no check.
L · Access to profile real datawhy we ask to scan your data
L1Can we have a copy of production data in a sandbox: masked if it holds personal data, as is if it doesn't?Only real data shows real keys, gaps, odd values and edge cases.Logic built on test data breaks on the first real load.A masked production copy with consistent masking, so the same customer gets the same token everywhere and joins still work. Not: hand-made test data only.
L2If data cannot leave your environment, can we run profiling inside it and take only the results out?Profiles (counts, ranges, match rates) usually hold no personal data.Design proceeds on guesses, and errors surface in user testing.Run the profiler where the data lives and export the summary. Not: skipping profiling.
L3Who on your side will review the profile and confirm what is expected?Odd values are either bugs or business rules; only the owner knows which.We 'clean' values that were correct, or keep ones that were wrong.A short profile review with the data owner per source. Not: deciding alone.
M · Sensitivity and ruleswhat the law and your policies require
M1Which columns hold personal, health, payment or other sensitive data?This decides masking, who may see what and where data may be stored.Personal data shows up in a widely shared dashboard; an audit finding follows.Tag sensitive columns as they land; mask by default and grant access on purpose. Not: deciding at the dashboard.
M2What are the retention, deletion-on-request, residency and consent rules?A deletion request must reach every copy we hold.A customer asks to be deleted and they live in 40 tables nobody can list.Design the deletion path from day one, using lineage. Not: 'we will handle it when asked'.
M3Are there licence or contract limits on using third-party data?Some data may be used only for some purposes.Licensed data is shared with a partner and the contract is breached.Record usage limits as metadata on the dataset. Not: a note in someone's inbox.
N · Trust and reconciliationhow we will prove nothing was lost
N1What quality problems do you already know about?Known problems are cheap to plan for and expensive to discover live.We find them in front of executives and lose trust in week one.Write them down as expected exceptions with owners. Not: hoping profiling finds them all.
N2Can the source give control totals per load, such as row counts and the sum of amounts per day?This is the only way to prove nothing was lost or doubled.Rows go missing silently and the numbers are wrong for weeks.Reconcile every load against source totals and alert on the gap. Not: 'the job succeeded'.
N3Which report in the source system do users trust today?Users will compare our numbers with it on day one.Our total differs from the trusted report and nobody can explain why.Reconcile gold with that report before go-live and explain every difference. Not: launching first.
O · Streams and real timeonly for sources that send events, or that must feed something live
O1Who must act on this data, and how fast: within seconds, within minutes, or the next day?The answer decides the path and the bill: seconds need an always-on lane, minutes can run every 15 minutes, a day is batch.Everything is built 'real time' and costs three to ten times more; or an alert arrives after the damage is done.Set a speed tier per dataset and name the person who acts. Not: 'real time' for everything because it sounds better.
O2What format do messages use (JSON, Avro, Protobuf), is there a schema registry, and how is a breaking change announced?Inside a stream, a schema change arrives with no warning, message by message.A firmware or app release renames a field; readers drop the values silently for weeks.A registry with compatibility rules and a version in every message. Not: JSON with no schema.
O3What key does each message carry, and must messages for the same key stay in order (per device, per order)?The key decides how the stream is split across shards or partitions and what order is kept.Keyed by plant, one shard runs hot; or 'cancelled' is applied before 'placed'.Key by the thing whose order matters (device, order id). Not: a random key when order matters.
O4Does each message carry the time the event happened, and are the senders' clocks synced and in which time zone?Windows and hourly numbers must use event time, not the time the message reached us.After an outage, an hour of readings lands in the wrong hour; a device with a drifting clock skews every average.Event time plus sender time in every message; clocks synced; skew flagged. Not: arrival time used as event time.
O5How late can messages arrive: what happens when a device or gateway goes offline, how long does it buffer, and does it resend in order?This sets how long we wait before calling a window final, and how far back we recompute.An hour is reported as final, then buffered data arrives; or the buffer fills and data is lost.Agree the maximum lateness and buffer size; set the watermark and the recompute window from it. Not: assuming senders are always online.
O6What are the normal and peak message rates and sizes, and which bursts happen: shift start, reconnect after an outage, a firmware rollout?Streams are sized for the peak, not the average.Thousands of devices reconnect at once and writes are throttled exactly when catching up matters most.Size for the burst and test a reconnect storm. Not: sizing from the daily average.
O7Can senders batch several readings into one message, and how often may they send?Many services charge per message or per request; batching changes the bill tenfold or more.One message per reading makes the messaging bill larger than the rest of the platform.Batch at the edge within the latency budget, for example once a second. Not: one message per reading by default.
O8How is each device or app identified, and who revokes a lost or compromised one?A stream is an open door; each sender needs its own key.All devices share one credential, so one stolen device means rotating keys on every device.One identity per device or gateway, rotation, and a named owner for revocation. Not: a shared key.
O9Is there a device or asset registry (what each sender is, where it is installed, when it was moved or replaced), and how do we get it?Readings mean little without it, and the live lane needs a copy to label screens.Readings from a replaced sensor are credited to the old machine; unknown device ids pile up.Treat the registry as master data with history, and copy it to the live lane on a schedule. Not: a hand-kept mapping in code.
O10How do we tell a silent sender from one with nothing to report: is there a heartbeat?No messages can mean all is well or that the line is dead.A plant stops sending on Friday night and nobody notices until Monday.A heartbeat per sender or gateway, and an alert when it stops. Not: alerting only on errors.
O11Who else reads this stream: may we add our own reader, and how long does the stream keep messages?Shared streams have shared limits; retention decides how long we can be down without losing data.Our reader slows theirs, or one day of retention deletes messages before we catch up after an outage.Our own reader (consumer group or dedicated reader), retention longer than our worst outage, or a copy into our own log. Not: default settings on a shared stream.
O12Is past data available outside the stream (a historian, an archive, an export) for the first load?Streams keep days, not years; history has to come another way.Go-live starts with an empty history, or years of data are pushed through the live stream and swamp it.Backfill history with a separate bulk job, then switch to the stream. Not: replaying years through the live path.
O13Do we need to send commands or settings back to devices?Two-way traffic changes the security design and the products.Control is added later and the device identity and network design have to be redone.Decide in discovery; if yes, design commands and their audit with the identity. Not: bolting it on later.
O14Can a message identify a person, even indirectly: vehicle location, driver id, a user id in clickstream?Location and behaviour data are personal data even with no name in them.Raw locations of named drivers sit in an open bronze table.Tag those fields at the contract, restrict them in bronze, mask them in silver. Not: treating sensor data as safe by default.
O15Is there a test sender or a recording of real traffic we can replay in test, including late, duplicate and bad messages?Streams fail in ways a clean sample never shows.The first reconnect burst or out-of-order message is seen in production.A recorded sample plus a simulator with planted lateness, duplicates and silence. Not: testing on a tidy JSON file.

Per dataset

Fields we ask you to point at in every dataset

For each dataset, we ask which column (if any) plays each role. A blank is fine, but it is a decision. For example, "no last modified time" means change data capture or a full comparison.

RoleWhat it isWhy we need itExample
Business keyThe column or columns that identify one real thing.Merge, de-duplicate and join on it.order_no, or (company, invoice_no)
Last modified timeWhen this row last changed, set on every change.Find what changed since the last load.updated_at
Created timeWhen the row was first made.Late-arrival checks; first-seen history.created_at
Transaction or event dateWhen the business event happened.Report by the date the business means.order_date, posting_date
Effective from and toThe period a version was true, if the source keeps versions.Build history correctly.valid_from, valid_to
Delete or status flagMarks a soft-deleted, cancelled or reversed row.Exclude or reverse it downstream.is_deleted, status = 'X'
Change typeInsert, update or delete, when a change feed is used.Apply each change the right way.op = I / U / D
Source sequenceThe order of changes at the source (log position, version number).Apply changes in the right order.lsn, scn, row_version
Company, tenant or countryWhich part of the business the row belongs to.Split, secure and report correctly.company_code
Currency and unitWhat the amounts and quantities are measured in.Never add unlike numbers.currency, uom
Source systemWhich system and instance the row came from.Lineage, and two systems sharing one key.src = 'SAP-EU'

Profiling

Why we ask to scan your data

The later layers are written from what the data really contains. Silver's cleaning rules, gold's joins and the semantic layer's metric definitions all depend on it. Documentation describes what the data should contain; profiling shows what it does contain. So we ask for a copy of production data in a sandbox. If it holds personal data, it is masked consistently, so the same person gets the same token everywhere and joins still work. If it holds none, a read-only copy as is will do. If data may not leave your environment at all, we run the profiler inside it and take only the summary out.

What we look atWhat it decides later
Row counts and growthSizing, cost and the backfill plan
Key uniquenessWhether the business key is safe to merge and join on
Null and blank rates per columnWhich columns can be required in silver, and which need defaults
Value ranges and outliersQuality rules with real thresholds, not guesses
Distinct values and code listsReference data, mappings and the meanings to confirm
Date spread and late arrivalsThe look-back window and period-close rules
Join match rates across tables and systemsHow silver joins into gold, and how many orphans to expect
Hidden personal data in free textMasking rules before anything reaches a dashboard

Design decisions

The decisions this block forces

Full or incremental for this table?

OptionVerdict
Full snapshot and comparePursue · Pursue for small and medium tables, reference data, and any table without a change signal you can trust.
Incremental (watermark or CDC), plus a regular full comparisonPursue · Pursue for large tables. The comparison catches drift and, without CDC, deletes.
Incremental with no comparison, everDrop · Drop. Drift builds up silently.
Truncate and reloadDrop · Drop. A failed load leaves an empty table, and nothing records what changed.

How do we capture changes from a busy operational table?

OptionVerdict
Log-based CDCPursue · Pursue when updates or hard deletes matter or volumes are large. It catches everything with little load on the source.
Incremental query on a modified columnPursue when · Pursue only when the column passes the profiling test and deletes are soft.
Full extract and comparePursue when · Pursue only for small tables, reference data, or as the weekly safety net beside incremental loads.
Triggers on the source tablesDrop · Drop. They slow every write the business makes.

Where do we read from?

OptionVerdict
Read replica, standby or change logPursue · Pursue. Our load never competes with their customers.
Live primary, outside business hoursPursue when · Pursue only when no replica exists and the extract is light.
Live primary, any timeDrop · Drop. The first slow month end ends the integration.

When do loads start?

OptionVerdict
On a signal from the source (event, file arrival, done marker)Pursue · Pursue wherever the source can signal. It removes most late and partial loads.
On a schedule, with a completeness checkPursue when · Pursue when no signal is possible. Check the row count or marker before loading.
On a schedule with no checkDrop · Drop. It loads whatever is there, half-written or not.

How do we see real data before building?

OptionVerdict
Masked production copy in a sandboxPursue · Pursue. Use consistent masking so joins still work. A copy as is is fine when there is no personal data.
Profile in place, export only the resultsPursue when · Pursue when data may not leave the environment.
Hand-made test data onlyDrop · Drop for design. It never contains the edge cases that break production.

How are the answers kept?

OptionVerdict
A data contract per dataset, signed by the source ownerPursue · Pursue. The answers become checks that run on every load.
Notes in a discovery documentDrop · Drop as the only record. Nobody re-reads it when the source changes.

FAQ

Questions people ask

Why do data platform teams ask so many questions about source systems?

Because the platform can only be as correct as its understanding of each source. How rows change, which column marks a change, what the key is, and when data is ready decide whether loads are complete, repeatable and on time. Each question exists because skipping it once caused a production problem.

Can we just do a full load every time and skip these questions?

For small tables, often yes, and it is the simplest safe choice. For large or busy tables a full load gets slow and expensive, and you still need to know the keys, dates and deletes to merge and report correctly.

Why do you need production data? Can't you use test data?

Test data is made by people who already know the rules, so it never contains the surprises: duplicate keys, nulls in required fields, codes nobody documented, dates in the wrong zone. A masked production copy shows them before go-live instead of after.

What is the difference between push and pull?

With push, the source sends changes to us as they happen (change data capture, events, webhooks, files it drops). With pull, we ask the source on our schedule (queries, API calls, polling a folder). Push usually catches deletes and avoids timing problems. Pull is simpler to start but needs a trustworthy changed-since column.

What makes incremental loading safe?

A column that changes every time the row changes, including fixes made outside the app; a known business key; a way to see deletes; and a look-back window long enough for late and backdated changes. Plus a periodic full comparison to prove nothing drifted.

Next

How we build it, and where to go next

Hand it over: the printable questionnaire has every question above with a blank answer column, plus a per-dataset sheet.

How we build ingestion: Ingestion: sources to bronze (six ways in, a new source as a few lines of YAML) and the metadata store, where these answers live as configuration.

The project around it: Technical anchor: discovery.

Back up: Data systems, end to end.