locked
Data Flows - Cheaper or more expensive that running a stored procedure RRS feed

  • Question

  • Quick one on data flows.

    I do a lot of work in Stored procedures and run these to do alot of my transformations.

    If I attempt to change some of my code to data flows, would it be prices the same as a stored procedure or would it be more expensive?


    Debbie

    Thursday, December 5, 2019 10:39 AM

Answers

  • Thank you for the clarification.  Since you are doing a stored proc after the copy, all the activity happens on your database.  The cost in ADF  for the transformation in this case is negligible (<$0.01 per Stored Proc).  The bulk of your cost comes from the Copy Activity.

    Does your database have a data ingestion cost?  If you are doing transformations which shrink the size of data (such as aggregation), Data Flow may help cut those costs.

    In short, executing a single Stored Proc is cheaper than spinning up a Data Flow.

    Replacing Copy Data + Stored Proc with Data Flow can save money depending upon implementation and scale.  I recommend an A/B test using separate Data Factories.

    Friday, December 6, 2019 7:50 PM

All replies

  • Hello Debbie and thank you for your question.  Depending upon your implementation, there is a possibility that Data Flows may be less expensive.  First let me share a base comparison:

    Cost per hour of Copy Activity, which you are using: Data movement activities: $0.25/DIU-hour.

    Cost per hour of Data Flow Activity: $0.193 per vCore-hour.

    I know that you are using Stored Proc, not Copy Activity.  I am reaching out internally to verify whether Stored Proc is priced the same as Copy Activity.

    I mentioned that price depends upon implementation.  Can you tell me which of the following scenarios more closely resembles your current implementation?

    • Data is pulled from source using a single Lookup Activity.  The data is iterated through in a For-Each activity.  Inside the For-Each, there is a Stored Proc activity.
    • Data is pulled from source using a single Lookup Activity.  The data in its entirety is passed to a Stored Proc activity.
    Thursday, December 5, 2019 8:27 PM
  • Copy Activity and Stored Proc are priced differently.  Stored Proc is on a per-activity basis.  This means price depends on how many times Stored Proc is called.
    Thursday, December 5, 2019 10:05 PM
  • Hi,

    Im using a copy activity to move the data from source to destination.

    Then i run a SP to do some of the transformations in destination


    Debbie

    Friday, December 6, 2019 9:28 AM
  • Thank you for the clarification.  Since you are doing a stored proc after the copy, all the activity happens on your database.  The cost in ADF  for the transformation in this case is negligible (<$0.01 per Stored Proc).  The bulk of your cost comes from the Copy Activity.

    Does your database have a data ingestion cost?  If you are doing transformations which shrink the size of data (such as aggregation), Data Flow may help cut those costs.

    In short, executing a single Stored Proc is cheaper than spinning up a Data Flow.

    Replacing Copy Data + Stored Proc with Data Flow can save money depending upon implementation and scale.  I recommend an A/B test using separate Data Factories.

    Friday, December 6, 2019 7:50 PM
  • you need to ask the question is if you want to maintain stored procedure code and handle volumes of data in your transactional system
    • Proposed as answer by dataflowuser Saturday, December 7, 2019 7:46 AM
    Saturday, December 7, 2019 7:46 AM