Data flow task using synapse serverless pool vs copy activity in synapse dedicated pool

Question

Hi All,

My need is to process source files (.dat format, size is >30 GB and some file is of 86 GB as well) from source to synapse data mart schema and below are 2 possible ways,

1- Process the source file using parquet format in serverless pool using data flow activity and load the processed data to synapse table

2- Use copy activity to load the source file to Synapse staging schema and then do processing using stored procedure and load to data mart schema in synapse.

Now I need to know which will be faster and cost effective?

Thanks in advance.

Mahasweta Das

Answer 1

Hi Das, Mahasweta ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

It seems that you want to determine the most efficient and cost-effective way to process large source files in Synapse Analytics and load the processed data .

Both options that you have mentioned have their advantages and disadvantages, and the choice depends on your specific requirements and constraints.

Here are some factors to consider:

1. Data volume: If you have large data volumes, the copy activity may be faster than the data flow activity as it uses bulk insert/polybase(recommended) operations to load data into Synapse. However, the data flow can handle complex data transformations and may be more flexible in some cases.
2. Cost: The cost of the data flow activity depends on the compute size of integration runtime and the amount of data processed. The cost of the copy activity depends on the number of copy operations and the amount of data copied. You need to consider the cost of both options and choose the one that is more cost-effective for your specific scenario.
3. Processing complexity: If you need to perform complex data transformations, the data flow activity may be more suitable as it provides a visual interface for building data transformation logic. However, if your data transformations are simple, you may be able to use the copy activity and a stored procedure to load data into Synapse.

Based on the above factors, you can choose the option that best meets your requirements. If you have large data volumes and need to load data quickly, the copy activity may be a better option. If you need to perform complex data transformations, the data flow activity may be more suitable.

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Answer 2

Dear Annu,

Thanks for your answer.

Yes it helped me, but could you please give me the cost calculation for serverless vs dedicated please?

Thanks,

Mahasweta