Home › Learn › Data systems › Upstreams › Questionnaire

Source system questionnaire

Please fill one copy per source system. Sections F, G, H and I, and the dataset sheet at the end, are answered once per dataset. "Don't know" is a useful answer, because it tells us what to profile. The reasons behind every question are on the Upstreams page.

Source system:
Date:
Business owner:
Technical owner:
Filled in by:
Datasets covered:

Part 1 · The source system

#QuestionWhy we askYour answer
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.
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.
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.
A4What is the source's own support window and service level?Our freshness promise can never beat theirs.
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.
B2Can we read from a replica, standby or backup instead of the live system?Our extract must never slow down their customers.
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.
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.
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.
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.
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.
C4How do our long reads avoid blocking their writers?Long reads can hold locks or grow the source's undo and temp space.
C5For CDC: what does change capture cost the source, and who watches it?Change capture keeps log on the source until we read it.
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.
C7Can the source team stop our extraction quickly if they need to?They must be able to protect their system without finding us first.
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.
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.
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.
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.
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.
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.
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.
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.
F · Full or incremental loadper dataset: reload everything, or only what changed (answer per dataset)
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.
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.
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.
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.
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.
F6How often should a full comparison run alongside incremental loads: weekly, monthly?Incremental loads drift: missed changes, bulk fixes, clock problems.
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.
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.
G · How the data changesthe answers that decide the load method and how silver merges (answer per dataset)
G1Which of these happen: inserts only, updates, hard deletes, soft deletes?This decides the load method and how the next layer merges rows.
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.
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.
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.
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.
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.
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.
H · Keys and dateswhat makes a row unique and which date means what (answer per dataset)
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.
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.
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.
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.
H5Can the same real thing appear under two keys (one customer created twice)?Duplicate entities inflate counts and split history.
H6Which date means what: transaction, effective, posting, created, modified?Reports must use the right date for the right question.
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.
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.
I · Relationships and joinshow datasets connect, so silver and gold join them correctly (answer per dataset)
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.
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.
I3Are foreign keys enforced in the source database, or only by the application?Unenforced relationships produce orphans: children without parents.
I4Do related tables arrive together, from the same point in time?Parent and child read at different moments are out of step.
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.
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.
I7Which join paths do the reports users trust today use?Trusted reports hold business rules inside their joins.
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.
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.
J3Are there splits by company, country, tenant, currency or unit?Numbers in different currencies or units cannot be added.
J4Are there free-text fields, JSON blobs or customer-specific custom fields?They need their own parsing, and often hold personal data.
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.
K2How much history do we need, and does the source still hold it?This decides the backfill plan.
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.
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.
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.
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.
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.
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.
M2What are the retention, deletion-on-request, residency and consent rules?A deletion request must reach every copy we hold.
M3Are there licence or contract limits on using third-party data?Some data may be used only for some purposes.
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.
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.
N3Which report in the source system do users trust today?Users will compare our numbers with it on day one.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
O13Do we need to send commands or settings back to devices?Two-way traffic changes the security design and the products.
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.
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.

Part 2 · Per dataset: which column plays each role?

Dataset name: ______________________   Approx. rows: __________   Rows per day: __________

RoleColumn name(s), or "none"Notes
Business key
The column or columns that identify one real thing.
Last modified time
When this row last changed, set on every change.
Created time
When the row was first made.
Transaction or event date
When the business event happened.
Effective from and to
The period a version was true, if the source keeps versions.
Delete or status flag
Marks a soft-deleted, cancelled or reversed row.
Change type
Insert, update or delete, when a change feed is used.
Source sequence
The order of changes at the source (log position, version number).
Company, tenant or country
Which part of the business the row belongs to.
Currency and unit
What the amounts and quantities are measured in.
Source system
Which system and instance the row came from.

Part 3 · Access to profile real data

OptionPossible? (yes / no / with conditions)
A copy of production in a sandbox, as is (no personal data)
A masked copy of production in a sandbox (consistent masking so joins work)
Profiling run inside your environment; only the summary leaves
Person who will review the profile with us

Part 4 · Only if the source is an API

#QuestionWhy we askYour answer
API1Besides the normal API, is there a bulk export, a change feed or a webhook?Normal APIs are built for apps reading a few records, not for copying everything.
API2How does it page: offset, cursor, keyset or time window, and is the sort order stable and unique?Paging decides whether a read is complete.
API3Can we filter by 'changed since', on which clock, to what precision, and is the boundary inclusive?Incremental API loads depend on this filter being exact.
API4What are the limits: requests per second, daily quota, parallel connections, page size, and is the quota shared?Limits decide backfill time and how fast we can catch up after an outage.
API5Does the API charge per call or per record, and who pays?Some APIs bill by usage, outside the cloud bill.
API6How do we learn about deleted records?Most APIs return only records that still exist.
API7Does the API give a total count, or anything else we can check completeness against?Without a total, a partial load looks complete.
API8Does the API read the live database or a search index or replica that lags?Lag means recent changes appear late.
API9How are new fields and versions announced, and when do old versions retire?APIs change on the vendor's schedule, not ours.
API10How does authentication work, how long do tokens last, and who rotates the secret?Expired credentials are the most common cause of silent stops.

Part 5 · Only if the source sends files

#QuestionWhy we askYour answer
FL1Is each file a full snapshot or only the changes, and does the file name say which?Full and delta files are loaded completely differently.
FL2What is the naming pattern: source, dataset, business date, sequence number?The name is how we order, de-duplicate and find files.
FL3How do we know a file is complete: a done file, a manifest with row count and checksum, a trailer row?A file being written looks like a file that is finished.
FL4How are corrections sent: a full resend, a correction file, or edits to the old file?Corrections decide whether we replace or add.
FL5Can files arrive late, twice or out of order, and in which order must they be processed?Order matters for history and for deltas.
FL6What is the exact format: delimiter, quoting, header, encoding, line endings, nulls, date and number formats?Small format differences corrupt data silently.
FL7How are column changes announced, and are columns matched by name or by position?Files change shape whenever the exporting system changes.
FL8How big are the files, how many a day, and how are they compressed?Size and count decide parsing speed and cost.
FL9Where are files delivered, who can write there, and how long does the source keep them?The landing place is a security boundary and our replay window.
FL10What does an empty file mean, and what does no file mean?'No data today' and 'the export broke' must look different.
FL11Do files hold personal data, and are they encrypted in transit and at rest?Files are easy to copy and forget.
FL12For Excel or hand-made files: is there a locked template, and who owns it?Hand-made files change whenever someone edits them.