DP-600: Transforming and Cleaning Data in Microsoft Fabric

Choose the transformation tool according to the work it must own. Use Dataflow Gen2 for accessible, reusable Power Query shaping; a Data Factory pipeline to coordinate activities and operational dependencies; a notebook for code-first, distributed transformations over lakehouse data; and Transact-SQL (T-SQL) for set-based logic and reusable database objects in a Warehouse. A production solution often combines these tools, but each transformation rule should have one clear owner.

The decision turns on five questions: Does the logic need a low-code or code-first authoring experience? Where does the data live? How much data must the engine process? Must the result be a reusable relational object? How will the work be scheduled, monitored, retried, and parameterized? Answer those questions before choosing syntax. This article begins after data has been acquired and ends with clean, conformed data; source acquisition, dimensional target design, and interactive analytical querying remain separate concerns.

Architecture Overview

A maintainable Fabric transformation design separates processing logic from orchestration and separates raw inputs from trusted outputs. The medallion architecture guidance for Fabric describes a layered pattern in which data can progress from raw landing data through validated, enriched data to curated consumption structures. The layers are design boundaries rather than mandatory item names: the important point is that a failed or questionable record should not silently become a trusted analytical record.

Within that flow, tools have different responsibilities:

Tool	Choose it when	Primary responsibility	Avoid using it as
Dataflow Gen2	Analysts or engineers need low-code transformations expressed in Power Query	Connect, shape, type, filter, combine, and load tabular data	The scheduler for a multi-step production workflow
Data Factory pipeline	Several activities need sequencing, parameters, dependencies, schedules, or operational monitoring	Orchestrate copy, Dataflow Gen2, notebook, and other supported activities	A substitute for substantial row-level transformation logic
Notebook	Transformations require Spark-scale processing, code reuse, or complex programmable logic over lakehouse data	Clean, enrich, combine, and aggregate data with code	The orchestration owner for every unrelated workload step
T-SQL	Data is relational and logic belongs close to a Warehouse	Implement set-based transformations and reusable views, functions, and stored procedures	A write surface for a lakehouse SQL analytics endpoint

This division is not based on which tool can perform a join or filter; several can. It is based on who must maintain the rule, which engine should execute it, and how the result will be reused.

Core Transformation Components

Dataflow Gen2 for low-code Power Query transformations

Dataflow Gen2 is the default choice when transformation authors need the Power Query experience and the work is naturally tabular. According to the Dataflow Gen2 overview, a dataflow can ingest and transform data and deliver results to supported Fabric destinations. It is suitable for visible, step-based logic such as renaming columns, changing types, filtering invalid rows, replacing values, removing duplicates, joining reference data, and grouping records.

Choose Dataflow Gen2 when business-facing developers need to inspect transformation steps, when Power Query connectors and functions fit the sources, or when the same prepared output will support more than one downstream consumer. Keep steps purposeful and named clearly. A long chain of repeated conversions and merges is a signal to simplify the logic, push a stable operation to an appropriate source engine, or move code-intensive processing to a notebook.

A dataflow owns data shaping; it does not need to own the whole operating sequence. When upstream copy must finish first or several downstream tasks depend on its success, invoke the dataflow from a pipeline and let the pipeline own those dependencies.

Data Factory pipelines for orchestration

A Data Factory pipeline is the control plane for a workflow. The Fabric pipeline overview describes pipelines as a way to group activities that perform a task and to automate data-movement and data-processing scenarios. Use a pipeline to express order, branching, parameters, schedules, and dependencies among supported activities.

For example, a pipeline can coordinate this sequence:

Copy a source extract into a landing area.
Run a Dataflow Gen2 item that standardizes types and reference values.
Run a notebook that applies complex deduplication and enrichment logic.
Invoke a Warehouse transformation step that publishes a relational serving table.
Record success or route a failure for investigation.

The pipeline should pass control information deliberately and expose failures at meaningful boundaries. Do not hide all transformation logic inside a single opaque activity merely to make the canvas shorter. Conversely, do not split a coherent transformation into dozens of activities if that makes recovery and lineage harder to understand.

Notebooks for code-first lakehouse processing

Use a notebook when the transformation is best expressed as code and executed against lakehouse data. A Fabric lakehouse combines OneLake storage with Spark and SQL access, and its managed tables use Delta Lake storage, as summarized in the lakehouse overview. This makes a notebook a strong fit for distributed processing, multi-step programmatic rules, reusable functions, and transformations that are awkward to maintain as a long visual sequence.

Notebook logic can standardize nested or semi-structured records, apply multi-column matching rules, enrich large tables, calculate windowed values, and write validated Delta tables. The choice is justified by maintainability and execution needs, not simply by developer preference. A small tabular cleanup that a broader team must maintain may remain clearer in Dataflow Gen2.

Design notebook transformations so they can be rerun safely. Separate inputs, intermediate results, and published outputs; avoid depending on undeclared interactive state; validate the expected schema before writing; and decide explicitly whether a run replaces, appends, or merges data. Those controls reduce duplicate output and partial-load ambiguity when a pipeline retries an activity.

T-SQL for Warehouse transformation objects

Use T-SQL when transformations are relational, the data is in a Warehouse, and downstream consumers need governed database logic. The Fabric Data Warehouse documentation positions Warehouse as the SQL-first relational analytical store. Set-based joins, filters, aggregations, type conversions, and data-quality checks can remain close to stored relational data.

Choose the database object according to the kind of reuse required:

A view presents a reusable query projection. Use it to standardize selected columns, joins, filters, or derived expressions without treating the view as a separately persisted data-quality result.
A function packages reusable computation that participates in queries. Keep its responsibility narrow and verify that its supported form and behavior match the current Warehouse surface.
A stored procedure coordinates a reusable sequence of T-SQL statements. Use it when a transformation requires controlled multi-statement processing or writes rather than only a selectable expression.

Do not assume that a lakehouse SQL analytics endpoint has the same write boundary as a Warehouse. The endpoint provides a read-oriented SQL surface over lakehouse tables; write-oriented preparation belongs in Spark or another supported write path. This distinction prevents a design from depending on database operations that the selected item does not own.

Apply Data-Quality Operations Deliberately

The tool choice is only half the design. Every transformation must also define what a clean record means and how rejected or uncertain data is handled.

Enrich and join at a declared grain

Enrichment adds useful attributes from reference or master data. Before a join, identify the grain and expected uniqueness of both inputs. A many-to-one enrichment join should not multiply the base rows. Measure row counts before and after the join, test unmatched keys, and isolate ambiguous matches rather than accepting the first available value.

Denormalization can make a prepared table easier to consume, but this article treats it only as a transformation operation. Deciding the final fact and dimension structure, surrogate-key strategy, and history behavior belongs in DP-600: Dimensional Modeling for Fabric Lakehouses and Warehouses.

Aggregate only after confirming grain

An aggregation changes the grain of a dataset. State the grouping keys, the input grain, and the meaning of each measure before grouping. Confirm that joins have not duplicated rows and decide how null values affect counts, sums, averages, or other calculations. Preserve a detailed trusted layer when consumers may need to investigate an aggregate later.

Treat duplicates as a business-rule problem

Removing identical rows is not the same as resolving duplicate business events. Define the key that represents uniqueness, the time or sequence field that determines precedence, and the rule for conflicting values. When no authoritative winner exists, quarantine the conflict or preserve it with a quality flag. An unexplained “remove duplicates” step can discard legitimate repeated events or retain an arbitrary record.

Distinguish missing values from nulls

A missing source field, an empty string, a placeholder such as N/A, and a database null can express different conditions. Normalize representations only after defining their business meaning. Do not replace every null with zero: zero can be a valid measured value, while null commonly means unknown or not applicable. Add a quality flag when downstream users must distinguish an imputed value from an observed one.

Set data types before critical logic

Assign types early enough that filters, joins, and calculations use the intended semantics. Text sorting does not produce numeric or chronological order, and implicit conversions can conceal invalid source values. Parse dates with an explicit expectation, preserve identifiers such as codes that require leading zeros as text, and route conversion failures for inspection instead of silently dropping them.

Filter with traceable rules

Push selective filters early when doing so reduces unnecessary processing, but keep the rule visible and testable. Separate technical filters, such as excluding malformed records, from business filters, such as limiting a reporting population. Record rejected counts and avoid deleting raw evidence merely because a record does not qualify for a curated output.

How the Tools Work Together

Consider a sales feed that arrives daily and must be combined with customer and product references. One maintainable design is:

Land the acquired data without silently reshaping its business meaning. The acquisition choices are covered in DP-600: Discovering, Connecting, and Ingesting Data in Microsoft Fabric.
Use Dataflow Gen2 to apply transparent column selection, standard naming, basic type conversion, and reference mappings when Power Query is the maintainable authoring surface.
Use a notebook if deduplication requires ordered matching across large lakehouse tables or enrichment needs code-first logic.
Use T-SQL in a Warehouse to expose stable views, apply set-based relational checks, or execute a controlled stored procedure that publishes a serving result.
Use a pipeline to sequence the activities, pass parameters, and make operational success or failure observable.

This is an example, not a required tool chain. If Dataflow Gen2 can implement the whole transformation clearly and efficiently, adding a notebook creates needless ownership. If the data already resides in a Warehouse and the rules are relational, T-SQL may be the shortest path. If a pipeline has only one manually run activity and no orchestration requirement, it may not yet add value.

Design Considerations

Make each stage idempotent and observable

A retry should not create duplicate business records or publish a partially transformed result. Choose stable business or technical keys, define write behavior, and make completion boundaries explicit. Capture input counts, accepted counts, rejected counts, and output counts where those measures help diagnose quality. Validate both schema and business rules before advancing data to a trusted layer.

Place rules where they can be reused

Apply a rule once at the lowest appropriate shared layer. A source-format correction used by every consumer belongs in preparation. A reusable relational projection can belong in a Warehouse view. A calculation that must respond to semantic-model filter context belongs later in the semantic layer, not in this transformation workflow.

The same principle preserves the boundary with DP-600: Querying Microsoft Fabric with Visual Query Editor, SQL, KQL, and DAX: this article uses T-SQL to create and operate trusted relational data, while that guide selects query surfaces for analytical questions.

Plan for schema and rule changes

Cloud services and source systems evolve. Validate expected columns and types, decide how additions and removals are handled, and version material rule changes through the team's lifecycle process. Test representative edge cases before replacing a trusted output. When transformation steps can increase capacity use or reprocess large volumes, assess cost and recovery behavior before running them in production.

Common Mistakes

Using the pipeline canvas as the transformation engine

Pipelines coordinate work. Keep row-level joins, typing, cleansing, and aggregation in Dataflow Gen2, notebooks, or T-SQL so the logic can be tested in its execution engine.

Choosing a tool only because it is familiar

A familiar syntax does not guarantee the correct storage boundary, execution scale, or maintainership. Use the decision questions from the introduction and document why the selected engine owns the rule.

Deduplicating without a deterministic winner

Dropping duplicates without a key and precedence rule can remove valid events or retain inconsistent values. Define uniqueness, ordering, and conflict treatment explicitly.

Converting bad data to null and moving on

This hides the difference between an absent value and a conversion failure. Retain an error indicator or rejected-record path so the source problem can be measured and corrected.

Aggregating after a row-multiplying join

An aggregate can look plausible even when a join duplicated the input. Validate relationship cardinality and row counts before grouping.

Mixing final model design into preparation

Transformation produces trustworthy inputs; it does not by itself settle fact grain, dimension history, model relationships, or business measures. Keep those decisions in their dedicated design layers.

Certification Relevance

The current DP-600 study guide includes transforming data with views, functions, stored procedures, enrichment, denormalization, aggregation, joins, duplicate handling, missing and null values, types, and filters. It also expects familiarity with Dataflow Gen2, pipelines, notebooks, and T-SQL. The durable skill is not memorizing four feature lists; it is placing a transformation in the engine and operational layer that make the rule maintainable, observable, and reusable.

Practice by implementing the same small quality specification in more than one appropriate tool. For each implementation, explain the input and output grain, write behavior, retry behavior, monitoring owner, and downstream consumer. Then select one production approach and defend it using the decision framework rather than personal preference.

Conclusion

Use Dataflow Gen2 for maintainable low-code Power Query shaping, notebooks for code-first lakehouse processing, T-SQL for reusable relational transformations in a Warehouse, and pipelines to coordinate the workflow. Regardless of tool, define grain, null semantics, duplicate rules, types, filters, and rejection behavior before publishing trusted data. Clear ownership of both transformation logic and orchestration is what turns a collection of steps into a reliable Fabric data-quality process.