Mapping Dataflow vs SQL Stored Procedure in ADF pipeline

user_11134277 0
Viewed 1284

I have a requirement where I need to choose between Mapping Data Flow vs SQL Stored Procedures in an ADF pipeline to implement some business scenarios. The data volume is not too huge now but might get larger at a later stage. The business logic are at times complex where I will have to join multiple tables, write sub queries, use windows functions, nested case statements, etc.

All of my business requirements could be easily implemented through a SP but there is a slight inclination towards mapping data flow considering that it runs spark underneath and can scale up as required. Does ADF Mapping data flow has an upper hand over SQL Stored Procedures when used in an ADF pipeline? Some of the concerns that I have with the mapping data flow are as below.

  1. Time taken to implement complex logic using data flows is much more than a stored procedure
  2. The execution time for a mapping data flow is much higher considering the time it takes to spin up the spark cluster.

Now, if I decide to use SQL SPs in the pipeline, what could be the disadvantages? Would there be issues with the scalability if the data volume grows rapidly at some point in time?

azure-data-factory-pipeline azure-data-factory-2 azure-data-factory azure
2 Answers

This is kind of an opinion question which doesn't tend to do well on stackoverflow, but the fact you're comparing Mapping Data Flows with stored procs tells me that you have Azure SQL Database (or similar) and Azure Data Factory (ADF) in your architecture.

If you think about the fact Mapping Data Flows is backed by Spark clusters, and you already have Azure SQL DB, then what you really have is two types of compute. So why have both? There's nothing better than SQL at doing joins, nested queries etc. Azure SQL DB can easily be scaled up and down (eg via its REST API) - that seemed to be one of your points.

Having said that, Mapping Data Flows is powerful and offers a nice low-code experience. So if your requirement is to have low-code with powerful transforms then it could be a good choice. Just bear in mind that if your data is already in a database and you're using Mapping Data Flows, that what you're doing is taking data out of SQL, up into a Spark cluster, processing it, then pushing it back down. This seems like duplication to me, and I reserve Mapping Data Flows (and Databricks notebooks) for things I cannot already do in SQL, eg advanced analytics, hard maths, complex string manipulation might be good candidates. Another use case might be work offloading, where you deliberately want to offload work from your db. Just remember the cost implication of having two types of compute running at the same time.

I also saw an example recently where someone had implemented a slowly changing dimension type 2 (SCD2) using Mapping Data Flows but had used 20+ different MDF components to do it. This is low-code in name only to me, with high complexity, hard to maintain and debug. The same process can be done with a single MERGE statement in SQL.

So my personal view is, use Mapping Data Flows for things that you can't already do with SQL, particularly when you already have SQL databases in your architecture. I personally prefer an ELT pattern, using ADF for orchestration (not MDF) which I regard as easier to maintain.

Some other questions you might ask are:

  • what skills do your team have? SQL is a fairly common skill. MDF is still low-code but niche.
  • what skills do your support team have? Are you going to train them on MDF when you hand this over?
  • how would you rate the complexity and maintainability of the two approaches, given the above?

HTH

user_15275040

One disadvantage with using SP's in your pipeline, is that your SP will run directly against the database server. So if you have any other queries/transactions or jobs running against the DB at the same time that your SP is executing you may experience longer run times for each (depending on query complexity, records read, etc.). This issue could compound as data volume grows.

We have decided to use SP's in our organization instead of Mapping Data Flows. The cluster spin up time was an issue for us as we scaled up. To address the issue I mentioned previously with SP's, we stagger our workload, and schedule jobs to run during off-peak hours.

user_129223810
Related