A pipeline is not finished when it runs green once. It is finished when it survives the ERP upgrade that renames a column, the Tuesday the source server is down for three hours, the month a table grows ten-fold, and the day its author leaves. Azure Data Factory and its successor in Microsoft Fabric give you the building blocks; the habits below are what turn them into pipelines that last.
ADF and Fabric Data Factory: what carries over
Azure Data Factory is Microsoft’s managed cloud service for orchestrating and automating data movement and transformation, built from pipelines, activities, datasets, linked services, data flows and integration runtimes [1]. Data Factory in Fabric is described by Microsoft as the next generation of that service, a data-integration experience for connecting to, moving, transforming and orchestrating data inside the Fabric platform [1][2]. Pipelines, copy activities, control flow (ForEach, If, Until), parameters and triggers all carry over conceptually. The main shifts:
| Azure Data Factory | Fabric Data Factory |
|---|---|
| Linked services and datasets as separate objects | Connections managed centrally; sources configured on the activity |
| Mapping Data Flows (Spark under the hood) | Dataflow Gen2: Power Query transformations with output destinations such as lakehouse and warehouse [3] |
| Self-hosted integration runtime for on-premises sources | On-premises data gateway |
| Destination is wherever you point it | Destination is typically OneLake (lakehouse or warehouse); mirroring replaces many copy pipelines [4] |
| Billed per activity run and DIU-hours | Consumes Fabric capacity units |
If you have a mature ADF estate, you do not have to move it to use Fabric; ADF can land data in OneLake. If you are starting a new pipeline estate on Fabric, start in Fabric Data Factory and do not build a second control plane.
Habit 1: every load is idempotent
Running a pipeline twice must produce the same result as running it once. That means: stage to a landing table, then merge by business key into the target; never append without a key; and delete-and-reload a partition rather than rows when you reprocess a day. Re-running after a failure then becomes the normal recovery, not a data-corruption event.
Habit 2: watermarks, not timestamps on the clock
Incremental loads should read “rows changed since the last successful load,” where the last successful watermark is stored in a control table and updated only after the target commit succeeds. Using GETDATE() at pipeline start loses rows that commit during the run. Prefer a monotonically increasing column (rowversion, change-tracking version, or a trusted modified timestamp) and keep a small overlap window to absorb clock skew.
Habit 3: metadata-driven ingestion
Forty tables should not mean forty pipelines. One parameterized pipeline reads a control table (source, target, key columns, load type, watermark column, enabled flag) in a ForEach loop and copies each table with the same activity. Adding a table becomes an insert into the control table, reviewed like code. This is the single change that most reduces the maintenance load of a pipeline estate.
-- Control table that drives a metadata pipeline
CREATE TABLE etl.SourceTable (
SourceTableId int IDENTITY PRIMARY KEY,
SourceSystem sysname NOT NULL,
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
KeyColumns nvarchar(400) NOT NULL, -- comma-separated business key
LoadType char(1) NOT NULL, -- F=full, I=incremental
WatermarkColumn sysname NULL,
LastWatermark nvarchar(100) NULL,
IsEnabled bit NOT NULL DEFAULT 1,
LastSuccessUtc datetime2 NULL
);Habit 4: schema drift is expected, not exceptional
Sources change. Land bronze with the schema as received, compare it to the contract you recorded, and route differences to a quarantine table and an alert instead of failing the whole run. Additive changes (a new nullable column) should flow through; type changes and dropped columns should stop the affected table only. Dataflow Gen2 and copy activities both support mapping options; what matters is that the decision is explicit per table rather than left to defaults.
Habit 5: observability you can read at 6 a.m.
- Write one row per table per run to an audit table: start, end, rows read, rows written, rows rejected, status, error text.
- Build a small Power BI page on that audit table: last run status, duration trend, row-count anomalies against a 30-day median.
- Alert on conditions, not just failures: a nightly load that “succeeds” with zero rows is the dangerous one. Fabric Activator can trigger an email or Teams message when a measure or event meets a condition [5].
- Put the freshness of each gold table on the report that depends on it, so the business sees “as of 05:40” rather than assuming now.
Habit 6: test the pipeline like software
Three kinds of test, in order of value: reconciliation (row counts and sums between source and target per run, written to the audit table), data tests at the grain (uniqueness of keys, not-null foreign keys, accepted values), and a rerun test in a non-production workspace that proves idempotency before a change is promoted. Keep pipeline definitions in source control with a deployment pipeline between development, test and production workspaces.
Habit 7: separate orchestration from transformation
Let the pipeline move data and sequence work; let the transformation live in T-SQL stored procedures, notebooks or dbt models that can be unit-tested and reviewed on their own. Pipelines with hundreds of nested activities and business logic buried in expressions are the ones nobody dares to touch. If your team is code-first, the Prefect and dbt pattern applies the same separation outside the Microsoft stack.
When to replace a pipeline with mirroring
Many copy pipelines exist only to get operational tables into the analytics platform. Mirroring in Fabric replicates supported databases into OneLake continuously as Delta tables [4], which removes the copy pipeline, the watermark logic and the nightly window for those tables. Keep pipelines for sources mirroring does not cover (files, APIs, SaaS exports) and for the transformation sequence from bronze to gold. Fewer pipelines doing one job each is the goal.
Related: Microsoft Fabric for SQL Server shops and data governance you can run monthly.
References
- Microsoft Learn, “What is Azure Data Factory?” learn.microsoft.com/en-us/azure/data-factory/introduction (accessed 9 Oct 2026).
- Microsoft Learn, “What is Data Factory in Microsoft Fabric?” learn.microsoft.com/en-us/fabric/data-factory/data-factory-overview (accessed 9 Oct 2026).
- Microsoft Learn, “What is Dataflow Gen2?” learn.microsoft.com/en-us/fabric/data-factory/dataflows-gen2-overview (accessed 9 Oct 2026).
- Microsoft Learn, “What is Mirroring in Fabric?” learn.microsoft.com/en-us/fabric/mirroring/overview (accessed 9 Oct 2026).
- Microsoft Learn, “What is Fabric Activator?” learn.microsoft.com/en-us/fabric/real-time-intelligence/data-activator/activator-introduction (accessed 9 Oct 2026).
