Pick a transformation tool from the actual work to be done
Fabric Data Factory pipelines, Dataflow Gen2, Spark notebooks, T-SQL and KQL can all appear in data-engineering solutions, but they operate at different abstraction and execution boundaries. Microsoft Fabric DP-700 explicitly expects the engineer to choose between these mechanisms. A pipeline is a coordinator of activities; Dataflow Gen2 provides an accessible Power Query transformation experience; notebooks can run programmatic Spark logic; SQL and KQL are query and transformation languages with different engine strengths.
Consider weekly supplier files, daily sales records and live device telemetry. A Dataflow Gen2 may be suitable for consistent type cleanup and joins among supported relational or file sources. A pipeline can schedule copying and trigger those transformations. A notebook may handle more complex distributed parsing and Delta merges. KQL belongs naturally with events ingested into an Eventhouse. Do not choose a tool based only on which service’s icon is easiest to find.
Microsoft’s Dataflow Gen2 documentation describes data connectors, Power Query transformations, destinations and refresh.
Dataflow Gen2 is a transformation item with source and destination contracts
A Dataflow Gen2 item can pull data from supported connectors, apply repeatable Power Query steps and write to a configured Fabric destination. The engineer must consider query folding, source authentication, capacity, schema changes, data types and destination write semantics. A visually simple series of steps can generate expensive processing if a filter cannot be pushed toward the data source or a merge creates large intermediate results.
Suppose a supplier CSV contains inconsistent dates and blank product categories. A dataflow can standardize them and load a staging table; it still needs a rule for invalid records and a defined schema contract. Replacing nulls with ‘unknown’ without checking business meaning may hide missing source data and distort downstream reporting. The transformation’s apparent completion is distinct from whether the destination contains acceptable records.
As of April 2026, newly created Dataflow Gen2 items support CI/CD and Git integration by default. Legacy Dataflow Gen2 items created without that support remain a different lifecycle case and should not be described as automatically converted. Before implementing, check the actual item type and any destination constraints.
A pipeline handles timing, dependencies and controlled execution
A Fabric pipeline can coordinate copy activities, invoke supported notebooks or dataflows, pass parameters, branch on outcomes and run on schedules or supported triggers. The orchestrator needs to know what should happen next and how to handle failure; the individual transformation still owns correctness. A pipeline that retries a non-idempotent write can duplicate sales even when every activity ultimately reports ‘Succeeded’.
For a nightly finance load, define start and end checkpoints, dependencies and failure paths. The copy activity might land raw files, a notebook might apply a Delta merge and a validation stage might compare source counts to a trusted control total. A later consumer refresh should not start merely because the notebook process exited without an exception. Use explicit data-completion evidence and appropriate dependencies.
The distinction matters when troubleshooting. A pipeline failure message may be a wrapper around a connection denial in a child activity. Start from the failed activity and its run details; do not change orchestration parameters before confirming the underlying cause. Design a safe rerun boundary rather than assuming the entire graph can be restarted from the beginning without data side effects.
Notebooks and PySpark support complex distributed processing
Spark notebooks are useful when transformations require code, distributed joins, schema management, streaming or direct interaction with Delta tables. A notebook can implement custom tests, use libraries and express precise merge behavior. That flexibility also increases the need for controlled environments, small enough output files, repeatable parameters and logs that explain record-level outcomes.
In a file-ingestion scenario, a notebook may parse nested JSON, deduplicate events by a business identifier and store curated rows. The logic needs to decide which event wins when timestamps are equal, what happens to a delete, and whether replay is safe. Those are data rules, not Spark tuning settings. A successful Spark application could produce systematically wrong facts if the deduplication policy is undefined.
Fabric notebook operations require explicit execution environments, parameters and operational checks. Use Dataflow Gen2 for supported low-code needs, but do not force every transformation into one item type if the requirement genuinely needs programmatic control.
Notebooks also require attention to how schema evolution affects downstream consumers. If a JSON payload introduces a nested structure that the writer did not expect, permissive schema inference can silently create new columns or null values.
A production transformation should decide whether to reject that record, version the accepted schema, or route the new field through a staged compatibility change. Monitor unknown fields rather than assuming any schema that Spark reads is safe to publish. Code flexibility is useful only when paired with an explicit data contract and test evidence.
T-SQL and KQL are chosen for different data and query semantics
T-SQL is natural for relational warehouse operations such as explicit joins, grouping, stored transformations and some dimensional loading patterns. The SQL engine, permissions and supported write semantics determine which commands are appropriate. A query that runs on a Fabric warehouse may not perform the same kind of write through a lakehouse SQL analytics endpoint; determine whether the target surface is read-only, managed or write-capable.
KQL is optimized for time-oriented, semi-structured or streaming data investigations and aggregation in Real-Time Intelligence environments. A telemetry query might use timestamp filtering and time windows rather than being implemented as a nightly dimensional warehouse transformation. KQL and SQL can both compute aggregates, but the underlying store, ingestion cadence and temporal semantics should determine which is used.
The correct selection is not a contest of syntax length. Define what data is available, when it arrives, which engine owns it, who can update it and what evidence must be retained. Only then choose a language with appropriate supported behavior and maintenance expectations.
Use one-source-to-many-consumers without creating hidden copies
A frequent anti-pattern is building separate Power Query, notebook and SQL transformations for the same business rule because different teams are comfortable with different tools. This can generate subtly inconsistent order totals. A better operating model establishes one authoritative transformation or published data contract and makes additional projections only when justified by specific consumer needs.
For example, customer records can land through a dataflow into a staging table, be validated and deduplicated by a notebook, and then feed a warehouse fact/dimension model. That may be appropriate if the steps have distinct reasons and clear ownership. Conversely, if a straightforward Dataflow transformation already produces a suitable managed destination, introducing Spark and SQL copies solely to demonstrate more Fabric technologies adds operational surface with little value.
Document lineage as decisions: source, activity, intermediate data, authoritative output, consumer. Decide which component handles failure and replay at each transition. Permissions at the source and destination remain relevant even when the transformation is low-code.
Design for failure diagnosis and safe reprocessing
A transformation can fail because of credentials, unsupported data types, query folding, network connectivity, schema drift, destination locks or resource pressure. A robust design identifies which signal would reveal each condition. For Dataflow Gen2, inspect refresh details and step behavior; for Spark, inspect job and executor evidence; for SQL/KQL, inspect query-level errors and engine scope. Do not treat all failed runs as the same generic ‘pipeline error’.
Safe replay depends on destination semantics. A full overwrite may be acceptable for a disposable derived table but dangerous for data requiring history. An append is suitable for truly immutable, once-delivered records but can double-count after retry. An upsert or merge can be appropriate for corrections provided its business key and change ordering are sound.
An exercise should specify one intentionally missing source file, one bad schema field and one duplicated record. Explain which step detects each, where the evidence goes and how the operator recovers without broad privilege escalation or destructive manual edits. That practical boundary knowledge is the center of the DP-700 tool-selection objective.
Prove the selected tool improves the workflow
A design review should compare development effort, operator skill, connector support, execution latency, runtime cost, security implications and recoverability. Visual transformation can improve maintainability for one team; code can make complex algorithms more reviewable for another. A tool is a good choice when its behavior can be explained and operated by the responsible team, not merely when the product page advertises a capability.
Do not describe a fictional configuration as a successful Fabric tenant rollout. Use source-backed expected outcomes, and where an isolated Fabric environment exists, capture actual outputs and denied cases. If a workspace cannot access a particular connector or engine feature, check licensing, region and current documentation before treating it as a content error.
DP-700 preparation should leave the reader able to say: this step moves data, this step transforms it, this component schedules it, this identity authorizes it, and these checks demonstrate the result is correct. That separation prevents accidental complexity and speeds trustworthy incident response.