On this page
- What data pipeline automation is
- Who it is for, and who it is not for
- What we actually build
- How it works technically
- The five promises a pipeline makes
- The build process stage by stage
- What you get at handover
- Where data pipeline projects go wrong
- What it costs to run once live
- How to tell whether you need this
- How to start
What data pipeline automation is
Data pipeline automation is the scheduled, monitored movement of data between systems: pulling from sources, landing it somewhere durable, reshaping it into the tables people query, and publishing it to whatever consumes it. Moving the data is not the hard part. The hard part is knowing, without a person checking, whether today's run produced data that is complete, correctly shaped and true, and making sure that when it did not, somebody is told before the person reading the dashboard is.
Almost every company already has pipelines: scripts on a cron, an automation somebody built in 2023 and left, two scheduled queries, a person who exports a CSV every Monday. That collection works until it stops, and the way it stops is the problem. It rarely crashes. It produces a table quietly two days old, or one missing a source that failed without complaining, while everything downstream keeps rendering normal-looking numbers.
So the discipline is boundaries and evidence. Every hop between systems is a place a promise can break, and each is worth checking at the moment it is made. What makes a pipeline survive is that every run leaves enough evidence to answer one question without asking its author: is this table safe to use right now.
- Silent, not loudThe dangerous failure is not the job that crashes and pages you. It is the job that succeeds on partial data, because nothing raises a flag.
- Per boundaryChecks belong at every hop. A row count that looks right in the warehouse says nothing about what the source refused to hand over.
- Replay, not rerunEvery run must be safe to execute twice. Without idempotent writes, the fix for a failed night is worse than the failure was.
- Route by causeA source outage, a schema change and a broken business rule are three messages to three people. One channel called alerts is the same as none.
Who it is for, and who it is not for
This is for teams where several systems must agree before a number is publishable, and where the current arrangement is scripts with no owner. The value is reliability, plus the hours nobody spends checking whether the data is right today.
| Your situation | Verdict | Why |
|---|---|---|
| Reports are wrong often enough that somebody checks them by hand first | Good fit | A completeness assertion performed by a person, at a much higher price, only on the days they remember. |
| Scripts on a personal machine or a private cron, with no named owner | Good fit | The most common shape and the most fragile. Moving the logic into an orchestrator with contracts is well understood work. |
| Several sources that must reconcile before a figure is publishable | Good fit | Where invariant checks pay for themselves, and where a human checking by eye eventually misses something on a Friday. |
| One source, one destination, tens of rows a day | Not a fit | A scheduled query and an email on failure will do this. Buying a pipeline is buying machinery you will never load. |
| Nobody can say what the tables are for or who reads them | Fix first | Contracts need consumers. Map who reads what, expect to delete a third of it, and the build shrinks. |
| Sub-second latency, event by event, with ordering guarantees | Careful | A streaming system, priced and staffed differently. Batch machinery bent into a streaming shape is expensive. |
What has to exist before the build
- A list of the tables or reports that actually get read, and by whom. Not the catalogue. The five things where a wrong number causes a conversation.
- Credentials with a named owner and a rotation date. Expired credentials are the second most common cause of a pipeline going quiet, and they expire on weekends.
- Four weeks of history for any table you want a volume band on. Bands fitted on a week alert on every public holiday, then get widened until they detect nothing.
Who should not buy this
- Anyone who wants the pipeline to fix the source system. A transformation that patches upstream errors becomes an undocumented business rule nobody can find later.
- Teams whose real complaint is that reports take too long to read, not that they are late or wrong. That is reporting automation, a different build.
- Anyone expecting alerting to substitute for ownership. An alert with no name attached only announces that a problem exists and will continue to.
What we actually build
Six components. Two of them move data. The other four make those two trustworthy, and they take most of the engineering time.
The inventory and the ownership map
Before any code: every job that moves data, with reads, writes, schedule, who is paged, and who would notice within a day if it stopped. This regularly finds jobs nobody can account for, two systems writing one table, and a report reading a deprecated view. Cheapest stage, largest effect on scope.
Extraction and a raw landing zone
Each source is pulled and written unmodified to immutable storage, partitioned by ingest date, including fields you do not currently use. This costs almost nothing and it is the only thing that makes a backfill possible after you find a transformation bug in month four.
The transformation layer, in the repository
Every reshape lives in version control, reviewed like code, tested against fixtures that include the ugly rows. Not a query saved in a console. Somebody must be able to read a diff and know what a number will do before it does it.
The check tier
Freshness, volume, schema conformance and business invariants, evaluated inside the run rather than by a monitoring product looking in from outside. Checks that live outside can only tell you afterwards. Checks inside can block the publish.
Orchestration with real dependencies
A dependency graph, not a set of times chosen so jobs probably do not collide. Two capabilities people forget to ask about: retries with backoff per task, and backfills over a date range that do not fight the scheduled run for the same partitions.
Alerting, routing and a status surface
Each failure is routed by cause to a named recipient, and no alert exists without a line saying what it means and what to check first. Beside it sits a page answering the safe-to-use question per dataset, so analysts stop asking engineers. Retry and dead letter mechanics are in error handling that stops silent failures.
How it works technically
The architecture is unremarkable and that is the point. Interesting data architectures usually mean somebody optimised for a problem the business does not have.
Landing zone, then warehouse
Extraction writes raw payloads to object storage keyed by source, table and ingest date, transforming nothing on the way in. A second stage produces typed, deduplicated warehouse tables from that raw layer. Separating them means a transformation bug is repairable from data you already hold, without returning to a source that may have changed.
Watermarks, and the ordering that matters
Each source table has a watermark, usually an update timestamp or a monotonic id. A run reads rows above it and advances it only after the write succeeds. Advancing first is a small mistake with a long tail: the failed run's rows are skipped forever and nothing reports a problem, because every later run behaves perfectly.
Late-arriving rows, and the allowance
Source systems backdate. A row stamped Tuesday can appear on Thursday because something upstream reprocessed it, so the watermark is not a hard cutoff. Each source gets a lateness allowance, after which older rows go to a dead letter table with the reason recorded. Set it from observed behaviour, not a default.
Idempotency, which decides everything else
Writes are merges on a stable business key, never appends and never truncate-and-load, and every row carries the id of the run that last wrote it. With that, rerunning a failed night is routine. Without it, every rerun is a senior decision made at night under pressure, which is how duplicates reach a ledger. Worked through in idempotency in automation.
Backfills that do not collide
A backfill reprocesses history while the scheduled run continues, and the two must not write one partition at once. A lock per partition, bounded concurrency, and writes through the same merge path as the daily job. A separate backfill script is a second implementation of your logic, and it will drift.
# contract.orders_daily.yaml
# One file per published table, in the repo beside the code.
# The orchestrator reads this. A wiki page does not stop a bad publish.
table: analytics.orders_daily
owner: data-platform # the rota that gets paged
consumers: [finance-weekly-report, ops-dashboard, crm-sync]
schedule:
cron: "0 5 * * *" # 05:00 UTC
expected_duration_minutes: 12
heartbeat_deadline_minutes: 45 # PROMISE 1. Alert on silence past this deadline.
# A job that dies before it starts reports nothing.
sources:
- name: shop_orders
system: postgres_replica
watermark_column: updated_at
lateness_allowance_hours: 6 # backdated rows accepted this long, then written
# to dead_letter with a reason, never dropped
- name: payments
system: stripe_api
watermark_column: created
lateness_allowance_hours: 24
checks:
freshness: # PROMISE 2
max_age_hours: 26
on_fail: block_publish
volume:
method: rolling_band
window_days: 28
day_of_week_aware: true # Sunday is not an incident
lower_pct: 55
upper_pct: 160
per_source: true # one source of five timing out while the job
# returns success is the classic silent failure
on_fail: block_publish
schema: # PROMISE 3
mode: strict
allow_new_columns: true # additive is fine
allow_type_change: false # never silently coerce
required_columns: [order_id, customer_id, ordered_at, gross_minor, currency, status]
on_fail: block_publish
invariants: # PROMISE 4
- name: no_negative_gross
sql: "select count(*) from analytics.orders_daily where gross_minor < 0"
expect: 0
- name: one_row_per_order
sql: "select count(*) from (select order_id from analytics.orders_daily
group by 1 having count(*) > 1) d"
expect: 0
- name: closed_days_do_not_move
sql: "select abs(sum(gross_minor) - (select total_minor from analytics._snapshot
where d = current_date - 2)) from analytics.orders_daily
where date(ordered_at) = current_date - 2"
expect_max: 10000 # tolerance in minor units, never a percentage
on_fail: publish_with_warning
write:
mode: merge # never append, never truncate and load
key: [order_id]
partition: date(ordered_at)
run_id_column: _run_id # every row records the run that last wrote it
deleted_source_rows: soft # tombstone, do not vanish
alerting:
liveness: { to: pager, severity: high, runbook: RB-014 }
completeness: { to: pager, severity: high, runbook: RB-021 }
schema: { to: data_eng, severity: medium, ticket: true, runbook: RB-033 }
invariants: { to: data_eng, cc: finance_owner, severity: medium, runbook: RB-041 }
usage: { to: monthly_review, severity: none } # PROMISE 5, never a page
retention:
raw_landing_days: 400 # replay depends on this. Never trim it for storage.
dead_letter_days: 180
The most common data incident is not a crash. It is somebody rerunning a failed job that appends rather than merges, so the day's rows land twice and every downstream total is inflated by exactly one day. It survives for weeks because the numbers stay plausible. Make every write a merge on a stable key before improving anything else.
The five promises a pipeline makes
Monitoring is usually built around the job, which is the wrong object. Nobody downstream cares whether a job succeeded. They care whether the table is usable, and a table can be unusable in five separate ways.
The ChatGPTalker Five Promises of a Pipeline
Five promises to the people downstream. Each breaks in its own way, needs its own detector, and belongs to a different person. Most teams monitor the first and none of the rest, which is why the failure that reaches the board deck is never the one that appeared in the alert channel.
Liveness, detected by a heartbeat with a deadline set outside the job, because a job that dies before it starts cannot report its own death. Scheduler outages, expired credentials and a paused schedule nobody resumed emit no failure event. Alert on silence, not on error.
Completeness. Volume against a rolling band that knows about days of the week, plus a census naming which expected partitions are missing rather than only counting rows. Count per source: one source of five timing out while the job returns success is the textbook silent failure.
Schema conformance at the boundary. New columns pass, type changes do not, and a missing required column blocks the publish rather than nulling a field three systems downstream. Upstream teams change schemas without telling anyone and should not have to remember. The check is the notification.
Invariants encoding what the business knows and the table cannot. Gross is never negative. One row per order. A closed month does not move beyond a stated tolerance. These catch the errors nobody predicted, because they describe the outcome rather than the mechanism.
Usage, reviewed monthly rather than alerted on. Query counts per published table, with anything untouched for ninety days proposed for deletion. It is the only promise with no page attached, which is why it is never kept and why estates grow until the on-call rota becomes unbearable.
- Watermark
- The highest source timestamp or id a run has already processed, stored so the next run knows where to resume. Watermarks are why incremental loads are cheap, and why late rows disappear when the lateness allowance is too tight.
- Idempotency
- The property that running the same job twice produces the same result as running it once. Achieved with merge writes on a stable key rather than appends. It is what turns a failed night into a rerun instead of an incident.
- Backfill
- Reprocessing a historical range after a bug or a schema change. It is the operation that most often corrupts a warehouse, because it competes with the scheduled run for the same partitions.
- Freshness deadline
- The maximum age a published table may reach before the pipeline declares itself broken, agreed with the people who read it. Without one, the claim that data is late is an opinion rather than a fact.
- Dead letter table
- Where rows go when they cannot be processed, stored with the error and the original payload so they can be replayed after a fix. A pipeline without one drops those rows and reports success.
The build process stage by stage
Ten weeks is the long end and usually means many sources or a strict network. The sequence matters more than the duration. Nothing is built before the inventory, and nothing goes live before a shadow run.
- Inventory and ownership
Every job that moves data, with reads, writes, schedule, owner, and who would notice if it stopped. We interview the people who read the outputs, not only those who built the jobs.
- Contracts and thresholds
Per table: freshness deadline, a volume band fitted on four to eight weeks of your own history, required columns, invariants. We also ask what a wrong number would cost, because that decides which checks block a publish and which only warn.
- Landing zone and extraction
Raw, immutable, partitioned by ingest date, with the source response stored as received. Extraction handles pagination, rate limits and resumption from a watermark, with the retry policy chosen per source using rate limits, retries and backoff.
- Transformation and idempotent writes
Versioned transformations in the repository, tested against fixtures. Writes are merges on a stable key with a run id column. We prove idempotency by running the same job twice against staging and comparing row counts, which settles the argument permanently.
- Checks, routing and runbooks
Each promise wired to a detector and a named recipient. No alert ships without its runbook line. We then break each check deliberately in staging and confirm the right person receives something they can act on. An untested alert is an assumption.
- Shadow run and cutover
The new pipeline runs beside the old one for a full cycle including a month end, outputs compared row by row. Discrepancies are explained before cutover. Old jobs are disabled rather than deleted for one further cycle, so rollback is a switch.
What you get at handover
Everything sits in your accounts, under your billing, in your repository. The test of a good handover is whether your team can add a sixth source without calling us, and safely replay a bad day at nine on a Monday.
- The pipeline code in your repository, with the contract files beside it rather than in a document.
- Infrastructure as code for the orchestrator, landing storage and warehouse objects, so the estate rebuilds in a fresh account.
- A runbook per alert: what it means, the first thing to check, who owns the fix, and how to replay safely.
- The lineage map, and a recorded walkthrough of one deliberate failure and its recovery.
- A written list of what we chose not to build, so the next person does not assume it was an oversight.
Any vendor can show you a green run. Ask to see a source deliberately broken, the alert arriving, the runbook followed, and the replay completed with row counts matching. That demonstration tells you more than a month of status reports.
Where data pipeline projects go wrong
Four failures account for most of it, and only one is technical in an interesting way. The rest are about attention, ownership and thresholds set once and never revisited.
Alerts that nobody reads
One channel receives every notification from every job. Within a quarter it is muted during working hours. Nothing has failed yet and detection has stopped. The fix is unglamorous: route by cause, delete alerts nobody acted on in sixty days, and refuse to create an alert with no runbook line and no owner.
Thresholds widened instead of made smarter
A volume band fires on a genuine seasonal dip, so somebody widens it rather than making it aware of weekdays or your holiday calendar. Widening feels like tuning. It is turning the check off slowly, and a band that no longer alerts on Sundays also misses a source that stopped delivering.
The transformation that patches upstream errors
A source sends a broken value and someone corrects it inside the pipeline. It works, so it stays. Two years later that correction is the only reason a report balances, and fixing the source breaks the report. Corrections belong upstream, or in a named, tested rule with a review date.
No owner, or an owner who left
Ownership written on a slide is not ownership. It is a rota, a name in the contract file, and time in that person's week. A pipeline without one degrades at a predictable rate, shown below.
Checks are new, alerts get read, and the builder still remembers why the volume band sits where it does.
A band fires on a real seasonal dip and gets widened rather than made day-aware. It is now wide enough to miss a source that stopped delivering entirely.
Alert volume crosses the point where a human filters it, so notifications are muted during business hours. Nothing has broken yet and monitoring has already ended.
An upstream team renames a column. The strict check was relaxed during a release crunch in month eleven. A wrong number reaches a report in a direction nobody questions.
It is declared unmaintainable and replaced with mostly the same logic, by a team that repeats this arc unless ownership and alert hygiene are treated as deliverables.
Relaxing a schema check to ship on a Friday is reasonable. Relaxing it without a dated ticket to restore it is how a system loses detection one reasonable decision at a time. Write the restore ticket in the same commit, or do not relax the check.
What it costs to run once live
Running cost splits into compute, storage, licences, any model calls, and the human on call. The largest line is almost never the one people budget for, and the biggest saving is usually deleting things rather than tuning them.
| Cost line | What actually drives it | The lever that works |
|---|---|---|
| Warehouse compute | Full refreshes on tables that could be incremental, and a schedule frequency chosen by habit | Move the three largest tables to merge writes on a key, then drop frequency to the slowest cadence a real consumer needs |
| Orchestration and hosting | Concurrency, and how long each task holds a worker while waiting on an API | Fewer, larger tasks beat many small ones, since managed schedulers tend to bill worker minutes rather than work done |
| Managed connector licences | Rows or events synced per month, which grows whether or not anyone reads the data | Audit which synced tables are actually queried and drop the rest. Regularly the largest saving available |
| Storage | Raw landing retention plus warehouse table history | Compress and partition raw, but never shorten retention below your longest plausible backfill window |
| The on-call human | Alert volume, and the share of alerts that are not actionable | Route by cause, delete dead alerts, require a runbook line before an alert may exist. Usually the most expensive line here |
Model cost, as arithmetic you can rerun
Some pipelines call a model to classify a record or extract a field. Assume, purely as a stated assumption, two units per million input tokens and eight per million output. A record using 900 input and 120 output tokens costs 0.0018 plus 0.00096, so roughly 0.0028. At 40,000 records a month, about 110. Substitute the price you actually pay, checked today rather than taken from this paragraph, because provider pricing moves and any figure on a web page ages badly.
Warehouse compute, where the real money is
The biggest avoidable cost in most estates is a large table rebuilt from scratch every run, because the first version was written that way and it was fast enough then.
Warehouse pricing models differ, so put your own cost per million rows scanned in the last field. The ratio is the point, not the absolute figure.
A live estate needs roughly half a day a month per active pipeline for credential rotation, threshold review, dead alert pruning and the usage cull. Unallocated, that time gets taken from the next project and the estate decays along the schedule above.
How to tell whether you need this
Three signals, ordered by how strongly they predict that a build pays back.
- Somebody checks a number by hand before it goes anywhere. That check is a completeness assertion performed by a person, at a much higher price, only on the days they remember.
- You have had at least one incident where wrong data reached a decision. Not a crash, a plausible number that was wrong. It will happen again, because nothing about the system has changed.
- Nobody can tell you within a minute which report depends on which source. That missing lineage turns a two hour source outage into a two day investigation.
Against that, three signals you do not need this yet. Your data comes from one system and stays there. The people reading the numbers are the people producing them. Or the numbers are directional and nobody would act differently if they moved ten percent.
Managed connectors against a pipeline you own
The honest answer is usually a mix. Use connectors for the common sources they support well, build the rest, and put your own checks in front of everything regardless of who moved it. A connector that lands data faithfully still cannot tell you whether the resulting table is safe to use.
Take your three most-read tables. For each, write down the freshness deadline, the row count you expect on a normal Tuesday, and one sentence that must always be true. That afternoon produces most of a data contract, and tells you whether anyone can currently answer those questions.
How to start
It starts with a scoping call and the inventory, not a proposal. We would rather spend a week finding that two of your five pipelines should be deleted than sell you a build that includes them.
- A scoping call. What breaks, how often, who notices, and what a wrong number has already cost you once.
- An inventory exercise: every job that moves data, its owner, and who reads the output.
- A written scope: the tables that get contracts, the checks each carries, the alert routing, and what is out of scope.
- A first slice in production, one source and one published table with the full check tier, so you see the shape before committing.
- The remaining sources, then the shadow run, then cutover and handover.
If your problem is connecting applications rather than moving data on a schedule, start at systems integration instead. The distinction matters for pricing: integration is events and APIs between applications, pipelines are scheduled, checked, replayable movement into a place people query.
ChatGPTalker. Data Pipeline Automation With Alerting That Reaches a Human. chatgptalker.com/services/data-pipeline-automation/