Modern ETL for businesses that can’t afford downtime

Traditional ETL (extract, transform, load) was designed for a reality that no longer exists: a four-hour nightly window, stable sources, tolerance for a full day of delay. Today the business needs freshly updated data during working hours, and any interruption triggers an alert on the CFO’s phone.

The 3-layer medallion architecture

Popularized by Databricks, the medallion architecture splits the pipeline into three layers (Bronze, Silver and Gold). Because data is loaded raw and transformed later, the pattern is usually implemented as ELT. The layers are named by increasing data quality:

Bronze: raw ingestion

The Bronze layer stores data exactly as it arrives from the source, with no transformation or validation. The golden rule: Bronze is a faithful, reprocessable copy of the source. 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 Storage Gen2, 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 table has a documented and versioned schema.
• Invalid records go to a _rejects table and are 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” that turns into a slow, generic data mart.

Incremental ingestion: the detail that decides

A nightly full load still works when the whole table fits comfortably within the processing window. Beyond that, you need incremental ingestion, and this is where many pipelines fail. Three techniques, in order of preference:

1. Change Data Capture (CDC)

When the source has CDC enabled (SQL Server, Azure SQL Database, Azure SQL Managed Instance), this is the ideal pattern: it captures only inserted, updated and deleted rows from the transaction log, with minimal impact on the source. Azure Data Factory reads this CDC natively. PostgreSQL logical replication also works, but it falls outside Data Factory’s native CDC.

2. Timestamp watermark

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

• Deleted records are not detected (you need a soft delete at the source).
• Transactions committed late, or a watermark taken from the pipeline clock instead of the highest value read at the source, leave rows behind. Reread a small overlap window on every load and deduplicate in Silver.

3. Comparison hash

Last resort. You compute a hash of each row’s contents 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.

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 it exceeds the SLA.
• Volume: record count per day compared with the table’s own history, with the tolerance range set per table. A sharp drop triggers an 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 and Log Analytics for Azure Data Factory runs, the Microsoft Fabric monitoring hub for pipelines in Fabric, Power BI for the operational dashboard and Microsoft Purview for data lineage.

Lightweight governance that scales

• Table catalog: owner, description, SLA and last audit. Microsoft Purview or, to begin with, a simple SharePoint list.
• Explicit retention: decide how long to keep each layer, remembering that Bronze only lets you reprocess the period it still holds. Move old history to cheaper storage instead of deleting it. Storage isn’t free.
• Deployment by environment: development, staging and 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 the full medallion architecture. Signs that the traditional stack still works:

• A full load that finishes comfortably within the nightly window.
• A one-day delay that the teams using the data accept.
• A single analytics consumer.

Signs that it is time to modernize:

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

Where to start

Pick one critical table that arrives late today, publish a freshness indicator for it in Power BI, switch its full load to incremental (CDC or watermark), and only then roll the same pattern out to the other tables.

Mature data engineering is measured by freshness, volume and completeness against an explicit SLA, not by the newest technology.