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.
Part 1 · The source system
| # | Question | Why we ask | Your answer |
|---|---|---|---|
| A · Ownership and changewho answers questions and who tells us about changes | |||
| A1 | Who owns this system, and who knows its data best? | Someone has to answer 'is this value right?' and approve fixes on the source side. | |
| A2 | When does the source change (releases, upgrades, patches), and who tells us beforehand? | A column renamed on their side is a broken pipeline on ours. | |
| A3 | Is 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. | |
| A4 | What 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 | |||
| B1 | How 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. | |
| B2 | Can we read from a replica, standby or backup instead of the live system? | Our extract must never slow down their customers. | |
| B3 | What 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. | |
| B4 | What 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 | |||
| C1 | How 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. | |
| C2 | Will 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. | |
| C3 | Do 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. | |
| C4 | How do our long reads avoid blocking their writers? | Long reads can hold locks or grow the source's undo and temp space. | |
| C5 | For CDC: what does change capture cost the source, and who watches it? | Change capture keeps log on the source until we read it. | |
| C6 | Do 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. | |
| C7 | Can 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 | |||
| D1 | Can 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. | |
| D2 | For 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. | |
| D3 | For 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. | |
| D4 | Does 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 | |||
| E1 | Is there a column that reliably says when each row last changed? | Incremental loads depend on it completely. | |
| E2 | Is 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. | |
| E3 | If 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. | |
| E4 | Can 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) | |||
| F1 | How 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. | |
| F2 | Is 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. | |
| F3 | For 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. | |
| F4 | Can 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. | |
| F5 | On 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. | |
| F6 | How often should a full comparison run alongside incremental loads: weekly, monthly? | Incremental loads drift: missed changes, bulk fixes, clock problems. | |
| F7 | How 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. | |
| F8 | At 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) | |||
| G1 | Which of these happen: inserts only, updates, hard deletes, soft deletes? | This decides the load method and how the next layer merges rows. | |
| G2 | For 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. | |
| G3 | Do 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. | |
| G4 | Can 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. | |
| G5 | Does the source ever resend or restate a whole period, or the whole table? | A resend must replace, not add to, what we already hold. | |
| G6 | Can a business key change, be reused, or be merged (two customer records joined into one)? | History must follow the real thing, not the number. | |
| G7 | Are 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) | |||
| H1 | What 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. | |
| H2 | If 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. | |
| H3 | Can 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. | |
| H4 | When 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. | |
| H5 | Can the same real thing appear under two keys (one customer created twice)? | Duplicate entities inflate counts and split history. | |
| H6 | Which date means what: transaction, effective, posting, created, modified? | Reports must use the right date for the right question. | |
| H7 | What 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. | |
| H8 | What 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) | |||
| I1 | Which 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. | |
| I2 | For 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. | |
| I3 | Are foreign keys enforced in the source database, or only by the application? | Unenforced relationships produce orphans: children without parents. | |
| I4 | Do related tables arrive together, from the same point in time? | Parent and child read at different moments are out of step. | |
| I5 | How 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. | |
| I6 | Must 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. | |
| I7 | Which 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 | |||
| J1 | Is there a data dictionary? What do the status codes and flags mean? | A code means nothing to us without its definition. | |
| J2 | How 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. | |
| J3 | Are there splits by company, country, tenant, currency or unit? | Numbers in different currencies or units cannot be added. | |
| J4 | Are 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 | |||
| K1 | How 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. | |
| K2 | How much history do we need, and does the source still hold it? | This decides the backfill plan. | |
| K3 | When is the data ready at the source each day, and how fresh do users need it? | Freshness is a chain; every link must fit. | |
| K4 | Can 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 | |||
| L1 | Can 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. | |
| L2 | If 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. | |
| L3 | Who 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 | |||
| M1 | Which columns hold personal, health, payment or other sensitive data? | This decides masking, who may see what and where data may be stored. | |
| M2 | What are the retention, deletion-on-request, residency and consent rules? | A deletion request must reach every copy we hold. | |
| M3 | Are 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 | |||
| N1 | What quality problems do you already know about? | Known problems are cheap to plan for and expensive to discover live. | |
| N2 | Can 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. | |
| N3 | Which 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 | |||
| O1 | Who 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. | |
| O2 | What 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. | |
| O3 | What 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. | |
| O4 | Does 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. | |
| O5 | How 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. | |
| O6 | What 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. | |
| O7 | Can 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. | |
| O8 | How 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. | |
| O9 | Is 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. | |
| O10 | How 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. | |
| O11 | Who 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. | |
| O12 | Is 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. | |
| O13 | Do we need to send commands or settings back to devices? | Two-way traffic changes the security design and the products. | |
| O14 | Can 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. | |
| O15 | Is 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: __________
| Role | Column 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
| Option | Possible? (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
| # | Question | Why we ask | Your answer |
|---|---|---|---|
| API1 | Besides 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. | |
| API2 | How does it page: offset, cursor, keyset or time window, and is the sort order stable and unique? | Paging decides whether a read is complete. | |
| API3 | Can 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. | |
| API4 | What 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. | |
| API5 | Does the API charge per call or per record, and who pays? | Some APIs bill by usage, outside the cloud bill. | |
| API6 | How do we learn about deleted records? | Most APIs return only records that still exist. | |
| API7 | Does the API give a total count, or anything else we can check completeness against? | Without a total, a partial load looks complete. | |
| API8 | Does the API read the live database or a search index or replica that lags? | Lag means recent changes appear late. | |
| API9 | How are new fields and versions announced, and when do old versions retire? | APIs change on the vendor's schedule, not ours. | |
| API10 | How 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
| # | Question | Why we ask | Your answer |
|---|---|---|---|
| FL1 | Is each file a full snapshot or only the changes, and does the file name say which? | Full and delta files are loaded completely differently. | |
| FL2 | What is the naming pattern: source, dataset, business date, sequence number? | The name is how we order, de-duplicate and find files. | |
| FL3 | How 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. | |
| FL4 | How are corrections sent: a full resend, a correction file, or edits to the old file? | Corrections decide whether we replace or add. | |
| FL5 | Can files arrive late, twice or out of order, and in which order must they be processed? | Order matters for history and for deltas. | |
| FL6 | What is the exact format: delimiter, quoting, header, encoding, line endings, nulls, date and number formats? | Small format differences corrupt data silently. | |
| FL7 | How are column changes announced, and are columns matched by name or by position? | Files change shape whenever the exporting system changes. | |
| FL8 | How big are the files, how many a day, and how are they compressed? | Size and count decide parsing speed and cost. | |
| FL9 | Where 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. | |
| FL10 | What does an empty file mean, and what does no file mean? | 'No data today' and 'the export broke' must look different. | |
| FL11 | Do files hold personal data, and are they encrypted in transit and at rest? | Files are easy to copy and forget. | |
| FL12 | For Excel or hand-made files: is there a locked template, and who owns it? | Hand-made files change whenever someone edits them. |