Pipelines that fail loudly: reliability engineering for Azure data platforms

The most common way we are introduced to a data platform is through its failure. Someone notices a report looks wrong, discovers the overnight load failed three days ago, and realises nobody was told, because the only monitoring was a human opening a dashboard.

Reporting is only as reliable as the pipeline underneath it. This is what production-grade actually means for an Azure data platform, and most of it is unexciting.

Idempotency comes before everything

A load is idempotent when running it twice produces the same result as running it once. Without that property, nothing else in this article is safe. You cannot retry, you cannot replay, and you cannot recover from a partial failure without manual repair.

Three patterns, in order of preference:

Delete-insert by partition. For each load window, delete the target partition and insert fresh. Simple, predictable, and the right default for most warehouse loads.

Merge on a stable business key. Where the grain genuinely requires upsert, merge on a key you control. The failure mode is a source that reuses keys, which is why the key needs validating rather than trusting.

Append-only with deduplication at read. Appropriate for event data where late-arriving duplicates are expected. Push the deduplication into the silver layer with a documented rule, not into every downstream query.

What is not idempotent: INSERT with no delete and no key, driven by a "last run" timestamp stored in a control table that is updated before the load completes. That pattern double-loads on retry, and we find it regularly.

Replay is a requirement, not a luxury

Source systems backfill. An ERP corrects three months of postings; a SaaS API reissues records with amended values. If your platform cannot reload an arbitrary date range on demand, every such event becomes a manual repair.

What replay needs:

• Raw data retained at source grain, for a period that covers your realistic backfill window. This is the main argument for a bronze layer.
• Parameterised loads that accept a date range rather than computing "yesterday" internally.
• Watermarks stored transactionally with the load, updated only on success.
• A tested procedure. Write the runbook, then actually run it in a non-production environment. An untested replay is a hope.

Data quality gates that block, not log

Tests that write a warning to a log nobody reads are decoration. Assertions that run with every load and have consequences are controls.

The set we implement as a baseline:

• Row count within tolerance of the same window historically, which catches truncated extracts.
• Referential integrity on every key used in a relationship, which catches joins that will silently drop rows.
• Uniqueness on each business key at the declared grain, which catches mixed-grain unions.
• Null checks on required fields.
• Reconciliation totals against the source of record, the single most valuable test, and the one most often skipped because it requires agreeing what the source of record is.

Each test gets a severity agreed with the business, not chosen by the engineer. High severity blocks promotion to the serving layer so users see yesterday's correct data rather than today's wrong data. Lower severity raises an alert and proceeds. That distinction is a business decision about which failure mode is worse.

Alerting that reaches a human who can act

Monitoring that nobody is paged by is observability theatre. What an alert needs to be useful:

1. A named recipient, with a rota and an escalation path, not a shared mailbox.
2. The failing entity and the error, in the alert body, so triage does not start with portal navigation.
3. The remediation command, usually the replay invocation, so the first response is a known action.
4. Suppression of known-noisy conditions, because an alert channel that cries wolf gets muted within a fortnight.

Then build a status view the business can check themselves. A large share of support contacts are "is the data up to date", and a self-service answer removes them entirely.

Environments and CI/CD, before the first pipeline

A platform with only a production environment will be changed in production, usually on a Friday. We have inherited several, and the remediation always costs more than doing it correctly would have.

The minimum:

• Three environments (development, test, production) with separate data and separate credentials.
• Infrastructure as code in Bicep or Terraform, so an environment can be rebuilt rather than archaeologically reconstructed.
• Pipelines in source control, with changes reviewed before merge. Data Factory and Synapse both support Git integration; use it.
• Automated deployment through Azure DevOps or GitHub Actions. If promotion requires a human to remember a step, that step will eventually be forgotten.
• Secrets in Key Vault, referenced by managed identity. Never in a pipeline parameter, never in source control.

Standing this up takes one to two weeks at the start of a programme and saves considerably more than that within the first quarter.

Cost control that is maintained, not performed once

Azure spend drifts upward unless someone owns it. The findings in a typical review are rarely clever:

• A dedicated SQL pool left running at the weekend.
• Diagnostic logs retained for years by default, in premium storage.
• A pipeline doing a full reload because incremental was started and never finished.
• Development resources at production sizing.
• Storage with no lifecycle policy, so cold data sits in hot tiers indefinitely.

What makes the saving stick:

Tag everything with workload and owner, so cost can be attributed rather than discussed in aggregate. Schedule pausing and scaling for anything that is not needed continuously. Lifecycle policies on storage, with retention set against an actual obligation. A monthly review against a baseline, with a named owner. This is the part that is usually missing.

Taking over someone else's platform

Half of what we do is inheriting platforms. The one rule: do not accept responsibility for infrastructure you have not been allowed to document.

An onboarding assessment inventories every pipeline, dependency, failure mode and cost line, and classifies criticality. It produces three outputs: what we can support as-is, what needs remediation before it can be responsibly supported, and what is costing money for no benefit.

In one engagement that assessment flagged three items as unsupportable until fixed, quoted the remediation separately, and the client later said that was the point at which they decided to trust the rest of the proposal. Saying no to part of the work is sometimes the strongest thing you can do.

The order we would do it in

1. Assessment, document what exists, rank risks by blast radius.
2. Environments and CI/CD, so every later change ships the same way.
3. One domain end to end (ingestion, transformation, model, report), proving the pattern under real conditions.
4. Reliability hardening, alert routing, SLA definition, replay procedures, and a deliberate failure drill in non-production.
5. Scale out by domain, with cost watched as the estate grows.
6. Monthly cost review and quarterly architecture review, both with named owners.

Steps two and four are the ones clients are most tempted to defer, and they are the ones that determine whether anyone trusts the platform in a year's time.

Service levels that mean something

An SLA that says "best endeavours" is a document, not a commitment. What a usable one states:

• Severity definitions agreed in advance, with examples from this client's estate. "A board-level report or regulatory return unavailable" is severity one; "a report is slow" is not, however loudly it is reported.
• Response targets per severity, distinguishing response from resolution. Promising resolution times for problems caused by a third-party source system is a promise you will break.
• Data freshness targets per dataset, because this is what users actually experience. "Finance data is current to 06:00 on each working day" is a commitment people can plan around.
• An escalation path with names, and what happens when the first name does not answer.
• What is excluded, explicitly: source system outages, changes made outside the change process, and anything in an environment we are not permitted to monitor.

Agreeing severity definitions during onboarding rather than during an incident is the single most useful thing in this list. Nobody negotiates well at 04:00.

Schema drift, the quiet failure

A source adds a column, renames one, or changes a type. The pipeline either fails loudly, which is fine, or silently loads nulls, which is not.

What we implement:

• Explicit schemas at ingestion, not inferred ones. Inference is convenient and it means a source change becomes a data change rather than a pipeline error.
• A contract test per source: expected columns present, types as declared, no unexpected columns without a flag. Unexpected additions get logged and surfaced rather than ignored, because they are usually the first sign that an upstream team has changed something.
• A documented owner per source and an agreed notification route. Most schema drift is avoidable if the team making the change knows someone downstream depends on it.
• Versioned landing, so an old schema remains readable. Retro-fitting a schema change across three years of history is a different and much worse project.

Monitoring that covers the right things

Pipeline success is the easy signal and the least useful one. A pipeline can succeed and still be wrong.

What we monitor beyond success and failure:

Each of those should have a threshold and a named recipient. A dashboard nobody opens is not monitoring.

The failure drill

Once a quarter, in a non-production environment, break something deliberately: kill a pipeline mid-run, corrupt a source file, revoke a credential. Then watch what happens.

The point is not the technology. It is to find out whether the alert reaches a human, whether the runbook is accurate, and whether the replay procedure works as written. In every drill we have run, at least one of those three turned out to be wrong, usually the runbook, which had drifted since it was written.

If you want the assessment (documented, risk-ranked, and yours whether or not anything follows), that is how we prefer to start. Book a technical review.