Edit

Microsoft Fabric decision guide: copy activity, dataflow, or Spark

  • Article

Use this reference guide and the example scenarios to help you in deciding whether you need a copy activity, a dataflow, or Spark for your Microsoft Fabric workloads.

Copy activity, dataflow, and Spark properties

Pipeline copy activity Dataflow Gen 2 Spark
Use case Data lake and data warehouse migration,
data ingestion,
lightweight transformation		 Data ingestion,
data transformation,
data wrangling,
data profiling		 Data ingestion,
data transformation,
data processing,
data profiling
Primary developer persona Data engineer,
data integrator		 Data engineer,
data integrator,
business analyst		 Data engineer,
data scientist,
data developer
Primary developer skill set ETL,
SQL,
JSON		 ETL,
M,
SQL		 Spark (Scala, Python, Spark SQL, R)
Code written No code,
low code		 No code,
low code		 Code
Data volume Low to high Low to high Low to high
Development interface Wizard,
canvas		 Power query Notebook,
Spark job definition
Sources 30+ connectors 150+ connectors Hundreds of Spark libraries
Destinations 18+ connectors Lakehouse,
Azure SQL database,
Azure Data explorer,
Azure Synapse analytics		 Hundreds of Spark libraries
Transformation complexity Low:
lightweight - type conversion, column mapping, merge/split files, flatten hierarchy		 Low to high:
300+ transformation functions		 Low to high:
support for native Spark and open-source libraries

Review the following three scenarios for help with choosing how to work with your data in Fabric.

Scenario1

Leo, a data engineer, needs to ingest a large volume of data from external systems, both on-premises and cloud. These external systems include databases, file systems, and APIs. Leo doesn’t want to write and maintain code for each connector or data movement operation. He wants to follow the medallion layers best practices, with bronze, silver, and gold. Leo doesn't have any experience with Spark, so he prefers the drag and drop UI as much as possible, with minimal coding. And he also wants to process the data on a schedule.

The first step is to get the raw data into the bronze layer lakehouse from Azure data resources and various third party sources (like Snowflake Web, REST, AWS S3, GCS, etc.). He wants a consolidated lakehouse, so that all the data from various LOB, on-premises, and cloud sources reside in a single place. Leo reviews the options and selects pipeline copy activity as the appropriate choice for his raw binary copy. This pattern applies to both historical and incremental data refresh. With copy activity, Leo can load Gold data to a data warehouse with no code if the need arises and pipelines provide high scale data ingestion that can move petabyte-scale data. Copy activity is the best low-code and no-code choice to move petabytes of data to lakehouses and warehouses from varieties of sources, either ad-hoc or via a schedule.

Scenario2

Mary is a data engineer with a deep knowledge of the multiple LOB analytic reporting requirements. An upstream team has successfully implemented a solution to migrate multiple LOB's historical and incremental data into a common lakehouse. Mary has been tasked with cleaning the data, applying business logics, and loading it into multiple destinations (such as Azure SQL DB, ADX, and a lakehouse) in preparation for their respective reporting teams.

Mary is an experienced Power Query user, and the data volume is in the low to medium range to achieve desired performance. Dataflows provide no-code or low-code interfaces for ingesting data from hundreds of data sources. With dataflows, you can transform data using 300+ data transformation options, and write the results into multiple destinations with an easy to use, highly visual user interface. Mary reviews the options and decides that it makes sense to use Dataflow Gen 2 as her preferred transformation option.

Scenario3

Adam is a data engineer working for a large retail company that uses a lakehouse to store and analyze its customer data. As part of his job, Adam is responsible for building and maintaining the data pipelines that extract, transform, and load data into the lakehouse. One of the company's business requirements is to perform customer review analytics to gain insights into their customers' experiences and improve their services.

Adam decides the best option is to use Spark to build the extract and transformation logic. Spark provides a distributed computing platform that can process large amounts of data in parallel. He writes a Spark application using Python or Scala, which reads structured, semi-structured, and unstructured data from OneLake for customer reviews and feedback. The application cleanses, transforms, and writes data to Delta tables in the lakehouse. The data is then ready to be used for downstream analytics.

Related content