Modern ETL for businesses that can’t afford downtime

Traditional ETL was designed for a reality that no longer exists: a four-hour nightly window, stable sources, tolerance for a full day of delay. Modern operations need freshly updated data during business hours — and any interruption triggers an alert on the CFO’s phone.

The 3-layer medallion architecture

Popularized by Databricks, the medallion pattern splits the pipeline into three layers named by increasing data quality:

Bronze — raw ingestion

Ingests data exactly as it arrives. No transformation, no validation. The golden rule: bronze is the reprocessable source of truth. If a downstream transformation produced an error, you go back to bronze, fix the logic and reprocess without asking anything of the source.

In practice, on an Azure stack: Azure Data Factory writes Parquet files to Azure Data Lake, partitioned by ingestion date.

Silver — validated data

Applies deduplication, explicit typing, validation against the expected schema, and key normalization. Silver is the level where analytics teams can query safely. Two principles:

Each entity has a documented and versioned schema.
Invalid records go to a _rejects table, never silently discarded.

Gold — business models

Tables ready for consumption — star schema for BI, aggregated views for dashboards, features for ML. Gold is designed for the use case, not to mirror the source. Avoid the trap of building a single “one gold table” that turns into a slow, generic data mart.

Incremental ingestion: the detail that decides

A nightly full load is acceptable for small volumes (≤10M rows). Above that, you need incremental ingestion — and this is where 70% of pipelines fail. Three techniques, in order of preference:

1. Change Data Capture (CDC)

When the source supports it (SQL Server CDC, Azure SQL, Postgres logical replication), this is the ideal pattern. It captures only changed rows via the transaction log, with minimal impact on the source. Azure Data Factory has direct CDC connectors.

2. Timestamp watermark

The source has a reliable updated_at column. You persist the last timestamp read and bring in only records above it. Simple, cheap, but watch out for two common pitfalls:

Deleted records are not detected (you need a soft delete at the source).
A clock skew between source and pipeline causes rows to be lost.

3. Comparison hash

Last resort. You hash the contents of each row and compare it against the previous hash. It works on sources without an update column, but consumes far more resources. Reserve it for small, stable tables.

A pipeline that goes down at noon isn’t rare — but it is avoidable.

We design data architectures on Microsoft Fabric, Azure Synapse and Databricks with a real operational SLA.

See our data engineering service

Observability: what you need to know by 9 a.m.

A pipeline without observability is a gamble. Three minimum signals on an operational dashboard:

Freshness: the age of the most recent data in each gold table. Red when > SLA.
Integrity: record count per day vs baseline (±20% of history). A sharp drop = alert.
Completeness: coverage of expected keys. If you expected 400 stores and got 380, where are the missing 20?

Tools from the Microsoft ecosystem: Azure Monitor + Log Analytics for runs, Power BI for the operational dashboard, Microsoft Purview for visual lineage.

Lightweight governance that scales

Table catalog: owner, description, SLA, last audit. Microsoft Purview, or a simple SharePoint list to begin with.
Explicit retention: bronze 90 days, silver 2 years, gold as the business requires. Storage isn’t free.
Deployment by environment: dev → staging → production. Never edit a pipeline directly in production, not even for a “quick fix”.

When to modernize (and when not to)

Not every company needs a full medallion. Signs that the traditional stack still works:

Volume < 10M rows/day.
A manageable nightly processing window.
A single analytics consumer.

Signs that it is time to modernize:

Reports arriving with a delay of a day or more.
Multiple consumers (BI, ML, apps) requesting the same data with conflicting rules.
Nightly ETLs overrunning the window and stealing capacity from the next day.
Inability to reprocess history without touching the production source.

Mature data engineering isn’t the kind that uses the newest technology — it’s the kind that delivers freshness, integrity and completeness with an explicit operational SLA.