Data & Analytics
Data ingestion: every load leaves a receipt
Data that arrives by habit stops arriving
In most businesses the weekly numbers depend on a routine: someone downloads the accounting export on Monday, pulls the ad spend, pastes both into the sheet. It works until they're on holiday, or the export gains a column, or the download quietly includes only half the week.
The report still goes out. It looks fine. Nothing in it says that one source is three days old. Ingestion replaces the routine with loads that run on their own, and a record of every one of them, so “how fresh is this?” has an answer.
Four ways data arrives
API
Pulled on a schedule
CRMs, ad platforms and most modern tools expose an API. The load asks for what changed since the last receipt.
File export
Picked up when it lands
Accounting packages and older systems often only export files. The load watches for the file, checks it's the one expected, and stages it untouched.
Database
Read directly
When a system's database is reachable, reading it avoids an export step. Read-only access, on a schedule that doesn't slow the system down.
Event
As it happens
Call tracking and payment tools can send each event as it occurs. Each one is staged and acknowledged, so a missed delivery can be asked for again.
A week of loads, at a glance
| Mon | Tue | Wed | Thu | Fri | Sat | Sun | |
|---|---|---|---|---|---|---|---|
| CRMCRM | CRM, Mon: Loaded | CRM, Tue: Loaded | CRM, Wed: Loaded | CRM, Thu: Loaded | CRM, Fri: Loaded | CRM, Sat: Loaded | CRM, Sun: Loaded |
| Sched.Scheduling | Scheduling, Mon: Loaded | Scheduling, Tue: Loaded | Scheduling, Wed: Loaded | Scheduling, Thu: Loaded | Scheduling, Fri: Loaded | Scheduling, Sat: Loaded | Scheduling, Sun: Loaded |
| Acct.Accounting | Accounting, Mon: Loaded | Accounting, Tue: Loaded | Accounting, Wed: Loaded | Accounting, Thu: Failed | Accounting, Fri: Recovered | Accounting, Sat: Loaded | Accounting, Sun: Loaded |
| AdsAds | Ads, Mon: Loaded | Ads, Tue: Loaded | Ads, Wed: Loaded | Ads, Thu: Loaded | Ads, Fri: Loaded | Ads, Sat: Loaded | Ads, Sun: Loaded |
| CallsCall tracking | Call tracking, Mon: Loaded | Call tracking, Tue: Loaded | Call tracking, Wed: Loaded | Call tracking, Thu: Loaded | Call tracking, Fri: Loaded | Call tracking, Sat: Late | Call tracking, Sun: Loaded |
Thursday's accounting export failed and was recovered on Friday, so Thursday's figures were marked incomplete until then. Saturday's call-tracking load arrived late. Without the log, both would just be numbers in a report.
The ingestion contract
01
What's expected
The shape of a good load
The fields, their types, and roughly how many rows a normal day brings.
Watch for: a source that adds or renames a field without telling anyone.
02
When
How fresh it has to be
Hourly, daily at six, or as it happens. A load that misses its window is late, and lateness is recorded, not ignored.
Watch for: reports scheduled before their slowest source arrives.
03
Repeats
What happens when the same data comes twice
Every record has a key, so a reload replaces what it already sent instead of adding to it.
Watch for: exports with no stable ID; they need one built from their fields.
04
Failure
Who hears about it
A failed or empty load raises an alert to a named owner and marks the period incomplete.
Watch for: alerts that go to a shared inbox nobody reads.
Retries that don't double count
Loads fail: a timeout halfway through, a file that arrives twice. The fix is to run them again, and that's where careless pipelines do their damage. If a retry appends, Thursday's revenue counts twice and nobody notices until month end.
Retry by appending
- First attempt
- 214 rows (stopped)
- Retry
- 214 rows
- Thursday in staging
- 428 rows
Thursday revenue: doubled
Retry by key
- First attempt
- 214 rows (stopped)
- Retry
- matched on invoice ID
- Thursday in staging
- 214 rows
Thursday revenue: unchanged
What ingestion hands off
In
Out
Worth building when the numbers come from several places
Good fit
- Recurring reports or systems need data from several sources.
- Someone downloads and uploads files on a schedule today.
- You need to know how fresh a number is before acting on it.
Not the right fit
- The source has no reliable way to get data out, or nobody owns it. Settle how the data can be obtained before automating its movement.
- It's a one-time migration; that's a project, not a pipeline.
Questions about data ingestion
Should data be cleaned on the way in?
Land it untouched first, then transform it in a separate step. Keeping the raw load means a wrong rule can be fixed and re-run without asking the source for the data again, and it shows whether a bad number came from the source or from the logic.
What if a system only offers a CSV export?
That's common and workable. The load watches for the file, checks it's the expected one (date range, columns, rough row count), and stages it. Files with no stable ID get a key built from their fields so reloads don't duplicate.
How often should data load?
As often as the decisions that use it need, and no more. A daily report needs a daily load that finishes before the report runs. Call and payment events can arrive as they happen when someone acts on them the same hour.
What happens when a source changes its format?
The load checks the shape it receives against what it expects. A renamed or missing field stops that source and alerts its owner; the other sources keep loading, and the affected period is marked incomplete.