How to Build Reliable Data Pipelines
The report is due at 9. The source system is responding again, but the overnight import stopped halfway through and nobody knows which rows made it into the dashboard. Running the job again might fix the gap. It might also double the rows that arrived before the failure. That is the moment a data pipeline stops being a diagram and becomes an operational responsibility.
A data pipeline moves information from a source to a place where people or other systems can use it. It may be a scheduled API pull, a webhook receiver, a database replication stream, or a few transformations that turn raw events into a daily report. Reliability does not mean that no run ever fails. It means failures are detectable, recovery does not corrupt prior work, and someone can explain what the output contains.
This guide is for a junior engineer or analyst responsible for a small production pipeline. You do not need a large platform to get the fundamentals right. You do need a clear unit of work, stable record identity, a checkpoint you can trust, and enough evidence to distinguish fresh data from merely recent-looking data.
Start by defining what one run promises
Before choosing a scheduler or framework, write down the pipeline contract in ordinary language. Name the source, the destination, the records included, the expected delay, and how the team will learn that the result is incomplete. “Load yesterday’s invoice changes by 6 a.m. and show the source update time” is a contract. “Keep finance data synced” is not.
The contract should clarify whether the pipeline copies current state or preserves every event. A current-state table usually has one row per invoice, updated as the invoice changes. An event table records each change, such as created, approved, voided, or paid. Mixing these models causes confusing counts. A report that counts current invoices and an audit that counts invoice events answer different questions.
Identify the grain, which is the meaning of one row. State it explicitly, such as one row per source invoice ID or one row per invoice line. Write down the primary key, source timestamp, and the time field used to place the row into a reporting period. If a pipeline combines sources, define how IDs from each source are represented so invoice 123 from one application cannot collide with invoice 123 from another.
Make reruns safe with idempotency
A distributed job can lose its connection after the source accepted a request but before the worker received the reply. The worker cannot tell whether the operation happened. Retrying is necessary for resilience, but a naive retry can add the same data twice.
Idempotency means that repeating the same operation produces the same final state as doing it once. In a data load, the common pattern is an upsert, meaning insert a new row when its key is absent and update the existing row when the key is already present. The key must identify the business record, not the current run. A key such as source system plus invoice ID is stable. A key built from the current timestamp is not.
For event streams, retain the provider’s event ID and enforce uniqueness in the destination. If an event arrives again, the database rejects or ignores the duplicate. For a batch file, include a batch identifier and source row number for diagnosis, while the actual business key remains the merge key. Do not rely on a process-memory set because it disappears when the process restarts.
A useful rule is to make the destination write transactional where possible. Stage and validate a batch, then merge the accepted records as one transaction. If the merge fails, the checkpoint should not advance. If the source offers its own cursor or event ID, persist it only after the destination commit succeeds.
Check the shape of the data before trusting it
A schema is the expected structure of the data, including field names, types, and sometimes constraints. A schema check compares incoming data with that expectation before the pipeline transforms or publishes it. This matters because a vendor can add a field, change a date format, or stop sending a field your report needs.
Not every change should stop a load. An added optional field may be harmless. A missing invoice identifier is not. Divide checks into blocking and warning conditions. Blocking checks stop publication when the output would be misleading. Warnings record changes that are safe to accept but deserve review.
| Check | Example | Typical response |
|---|---|---|
| Required field | Every row has an invoice ID | Reject the row or stop the batch |
| Type and format | Amount parses as a decimal and date has an accepted format | Quarantine invalid rows with a reason |
| Allowed values | Status is one of the source’s documented states | Warn on a new value or block if downstream logic cannot handle it |
| Uniqueness | One current row per source invoice ID | Stop the merge and inspect duplicate keys |
| Range or relationship | Currency is supported and each invoice references a known account | Route exceptions for review |
| Volume sanity | Today’s row count is within a plausible range | Alert, but do not assume volume alone proves correctness |
A schema check is not a substitute for business validation. A valid decimal can still be in the wrong currency. A date can parse while representing a different time zone. Pair structural checks with rules that express what the business expects.
Use checkpoints that describe committed work
A checkpoint records how far the pipeline has safely progressed. For a timestamp-based API, it may be the last source update time successfully committed. For a message stream, it may be the last acknowledged offset. For file ingestion, it may be the list of file IDs already processed.
The most common checkpoint mistake is advancing it when records are fetched rather than stored. If a job reads changes through 10:00, writes half, then records 10:00 as progress, the next run skips the uncommitted half. Store the checkpoint with the destination merge when possible. Otherwise, write it only after success and make the merge safe to replay.
Timestamp watermarks need care. Many sources update rows with coarse timestamps or deliver changes late. Use an overlap window, such as rereading the previous hour or day, then upsert by stable ID. The overlap creates repeated reads, but idempotency makes them safe. Avoid selecting only rows with a timestamp strictly greater than the checkpoint if the source timestamp can be duplicated across many records.
Backfills are a normal operating mode
A backfill is a deliberate rerun of an earlier time range to fill missing data or apply a corrected transformation. Treat it as a supported workflow rather than an emergency shell command. It should accept explicit start and end bounds, identify the code version used, and report which partitions or records it changed.
A time partition is a slice such as one day or one month. Rebuilding one day is safer than deleting a year, but only if the source has complete data and the destination has no manual corrections that would be erased.
Before a large backfill, estimate source request volume, API rate limits, destination write load, and the effect on dashboards. Run a small period first. Compare record counts and key totals with the old output, and preserve the previous result until the new one passes. If the source cannot provide historical deletes or prior versions, document that limitation. A backfill cannot reconstruct history the source never retained.
Late-arriving data needs an explicit rule
A record can describe Monday’s work and arrive on Wednesday. An invoice may be approved after a delay, a device may reconnect, or a vendor may publish a corrected file. Event time is when the business event happened. Arrival time is when the pipeline received it. Both can matter.
For each report, decide how late arrivals are treated. One choice is to revise the original period, which gives the best historical representation but can change a closed report. Another is to record the correction in the current period, which preserves prior outputs but distorts the original period. Finance, operations, and compliance may have different requirements. Do not silently choose based on which implementation is easier.
Store both event time and ingestion time when the source provides them. Use a published close window, such as “daily totals are provisional for 48 hours,” if the business can accept one. If a late item changes a previously distributed report materially, record the adjustment and tell the report owner. A freshness timestamp alone does not reveal whether all expected source records have arrived.
Observability should answer useful questions
Observability is the ability to infer what a system is doing from its outputs. For a pipeline, useful evidence includes run start and finish, status, source range, records read, accepted, rejected and written, checkpoint before and after, software version, and error category. Avoid placing sensitive invoice or customer data in routine logs. Log IDs or masked references that an authorized operator can use to find the full record in a protected system.
A green “job succeeded” badge is insufficient if the source returned zero rows because a filter broke. Track freshness, volume, and quality separately. Freshness asks how old the newest processed source update is. Volume compares the run to a reasonable baseline. Quality tracks validation failures, missing keys, and duplicate rates. Thresholds should reflect the use. A 15-minute delay may be harmless for a monthly planning extract and serious for a queue used to release same-day orders.
Alert on conditions that need action. A page for every transient retry trains people to ignore alerts. Record retry counts and alert if the job is still behind after its recovery window. Name an owner and a next step in the alert. For example, “Invoice pipeline has not committed updates since 06:00. Check the vendor API status, then replay from the saved checkpoint.”
A short sketch of a safe batch
This pseudocode shows the control flow, not a particular vendor library.
read the last committed checkpoint
request a bounded range with a small overlap
validate the response and stage rows
if blocking checks fail, keep the checkpoint unchanged and report the batch
upsert staged rows by source system and record ID
commit destination changes
save the new checkpoint
publish run metrics and freshness
A production implementation also needs request timeouts, bounded retries, and a quarantine path for unprocessable records. Exponential backoff increases the delay between retries, often with random jitter, so requests do not overload a recovering service. Do not retry permanent errors forever. Preserve enough source data or metadata to investigate rejected rows safely.
Test the failure paths, not just the happy path
A test that proves one valid row loads is a start. It does not show that the pipeline is safe to rerun or recover. Build a small fixture containing a normal record, a record missing a required field, a duplicate event, an updated record, a deletion if supported, and a late record whose event date is old but arrival is new.
Test at least these properties. Running the same batch twice leaves one current row per key. A failed write does not advance the checkpoint. An unexpected field does not crash the parser unless policy says it should. A missing required field does not appear as a blank value in a published report. A backfill changes only its intended interval. These are behavior tests, not implementation details.
A reconciliation test compares counts and totals for a known range. Counts alone can miss compensating errors, so compare useful sums and sample IDs too. For financial data, compare amounts by currency and status. A report may total correctly while assigning rows to the wrong customer.
Use a staging environment or small isolated dataset before changing production. If no sandbox exists, ask for a read-only account or safe test records. Do not test destructive cleanup on live financial data.
A worked example with illustrative numbers
Consider a hypothetical analyst loading invoice updates into a reporting database every hour. The source returns records ordered by update time, but the API can deliver changes several minutes late. On Monday, a run reads 2,400 records. The database accepts 1,700, then the connection drops. The job had already advanced its checkpoint to 10:00, so the next run begins at 10:00 and misses the remaining 700. The dashboard looks plausible because most invoices loaded.
The corrected design stores the checkpoint after the merge commit and rereads a 30-minute overlap. Each row is upserted using source invoice ID. If the job repeats the first 1,700, it updates those rows rather than adding duplicates. The next run receives the missing records and advances only after all blocking checks pass. A dashboard tile shows the latest committed update time, while a run metric reports 2,400 read, 2,400 accepted, and 2,400 current rows written or unchanged. The numbers are illustrative. The key is that progress means committed progress, not fetched progress.
Now suppose 12 records fail because the vendor added a new status. The pipeline should not quietly map all 12 to “open.” If the new status affects a financial report, stop that publication and alert the owner. If it is irrelevant to the report, preserve the raw value, issue a warning, and update the accepted status map after review. One condition can warrant different actions depending on the business meaning.
Questions for a pipeline or connector vendor
Ask the vendor to demonstrate your records, duplicate and reordered events, recovery after a partial run, schema changes, and selected-range replay. Confirm who receives alerts, whether rejected rows have reasons, how credentials are scoped and rotated, and whether logs contain personal data. Ask if you can export mappings and logs, leave without losing source data, and identify charges tied to rows, tasks, history, or backfills. A proof of concept should include an outage and a historical rerun, not only the normal flow.
What to do on Monday morning
Pick one pipeline that supports a real decision and write down its promise. Find the code or connector configuration that determines its key, checkpoint, and failure behavior. Ask the report user what freshness they actually need and what they do when the data is late.
Then run the same small batch twice in a safe environment and compare destination counts. Simulate a failure before checkpoint update. If you cannot do either because the tool hides its behavior, make that a vendor question and limit the workflow until you have a safe recovery path. Add one freshness signal and one owner for failed runs before adding new transformations.
Reliability grows from unglamorous decisions made explicit. A pipeline that exposes its limits and can be replayed is more useful than one that looks fully automated but leaves nobody sure what happened. If you have a specific data flow to stabilize, Ozcorp InfraOps can help design and maintain the integration around your team's operating needs.
Discuss your infrastructure requirements
Review the current service offer or schedule a conversation about the scope you need.
Stay in touch
Follow Ozcorp
Leave your details for occasional notes about new articles and Ozcorp updates.