Dataverse Dataflows vs. Power Automate: Which One to Choose?

Introduction

Mastering data handling is like wielding a superpower for Data Analysts and Developers, enabling them to uncover hidden insights, make informed decisions, and craft innovative solutions. While Power Automate can feel like navigating a maze with its complexity and throttling issues, Dataflows can swoop in as the hero with a powerful yet user-friendly approach utilizing Power Query transform data at lighting speeds. It simplifies workflows and turbocharges efficiency, making data processing a much less complex.

But why choose just one solution when you can have it all? By blending Power Automate and Dataverse Dataflows with Power Query, professionals can unlock extraordinary capabilities in data management. This dynamic trio not only tackles the throttling issues of Power Automate but also enhances the entire data transformation journey. Visualize Power Query's intuitive interface and powerful features effortlessly weaving through Dataverse's terrain, transforming data like magic. It streamlines workflows, drastically reduces manual labor, and seamlessly integrates with Power Automate's vast array of connectors to simplify complex authentications or create smooth-running pipelines. No more waiting for one task to finish before the next begins! It's a tech lover's dream come true!

Sound to good to be true!? Its not! Its absolutely here! Lets first understand the differences of these fantastic technologies to better help understand when to choose which technology. PS: I apologize in advance this is a long one! But its important to understand to build better solutions that are scalable and maintainable!

Understanding Throttling in Power Automate

Throttling in Power Automate refers to the mechanism that limits the number of requests a flow can make to a service within a specific period. This limitation is imposed to prevent overloading the service and ensure fair usage for all users. Throttling can significantly impact data workflows by causing delays in processing requests, which may lead to slower performance and potential timeouts.

• Common scenarios where throttling occurs include when a flow makes repeated requests to a service in a short amount of time, exceeding the allowed rate limits. This can happen when dealing with large datasets or when multiple flows are running concurrently.

• Throttling can have profound implications on project timelines and data accuracy. Delays caused by throttling can disrupt the flow of data between systems, leading to missed deadlines and impacting the overall project schedule. Moreover, throttling can introduce inconsistencies in data processing, affecting the reliability and integrity of the information being handled.

Example:

Imagine you have a treasure trove of data—25,000 records that get updated or created every hour through a synapse connection. Now, picture Power Automate as your diligent courier, triggered each time a record is created or modified, ready to deliver it to the right team or individual.

In this scenario, throttling is like a mischievous imp creating chaos with significant delays for the end user. The unfortunate recipient might feel like they’re stuck in a never-ending slinky effect on their access due to those pesky throttling and rate limits. Depending on your organization’s chosen licensing and availability, things may start off smoothly. But after a few hours, the system starts feeling the heat—like a marathon runner hitting the wall—because of the constant strain and rapid-fire triggers with such a large volume of records.

There's also another conundrum looming: Is sharing really the best method to handle this tidal wave of information? Stay tuned as we dive deeper into this intriguing topic!

Microsoft-Imposed Limits in Power Automate

Microsoft has specific limits across different aspects of Power Automate, as documented in their Limits and Configuration guidelines. These limitations apply to various areas of Power Automate, including flow runs, trigger frequency, and connector usage.

For exact details read Microsoft's documentation:

1. Flow Run Limits:

   • Flow Frequency: Each flow has a maximum frequency for running, which varies depending on the licensing plan. For example, flows created with a per-user license can run as frequently as every minute, while those with lower licenses may be limited to a 15-minute minimum interval.

   • Run Duration: There are constraints on how long a single flow can run. For instance, a flow under a standard license can run for a maximum of 30 days, while premium users might have extended durations.

2. Connector Limits:

   • API Call Quotas: Each Microsoft service and connector has a limit on the number of API requests that can be made. For example, connectors to popular services like Microsoft 365, SharePoint, and SQL have defined API call limits, which are higher for premium users.

   • Daily Request Limits: There are also daily request limits for certain connectors to ensure fair usage. Users exceeding these limits may experience delays or failures until the quota resets.

3. Concurrency Limits:

   • Parallel Execution Limits: Power Automate restricts the number of concurrent actions or loops that can run simultaneously, which affects flows with parallel branches or looping actions (e.g., Apply to each).

   • Timeouts on Concurrent Actions: Each concurrent action has a timeout limit, after which it will fail if not completed. This constraint ensures stability but can also interrupt complex workflows if concurrency settings are not optimized.

Impact of Throttling and Limits on Workflows

Throttling and limits can significantly impact workflows, especially those dealing with high-frequency or high-volume data processing. These constraints can introduce delays that disrupt project timelines, reduce system performance, and impact data reliability.

• Workflow Delays: Throttling-induced delays can stall data movement between systems, potentially causing workflows to miss deadlines or fail to meet real-time processing requirements.

• Data Consistency Issues: Interrupted workflows can create data inconsistencies, where only partial data is processed before throttling triggers a delay, potentially leading to data inaccuracies or discrepancies.

• Increased Error Rates: High-frequency or high-volume flows can experience increased error rates if they regularly hit throttling or concurrency limits, impacting user experience and necessitating flow adjustments.

Best Practices for Managing Throttling and Limits in Power Automate

To ensure smooth operation within the Microsoft-imposed constraints, consider the following best practices:

1. Optimize Flow Design:

   • Minimize unnecessary requests by using actions only when necessary, and consolidate steps where possible to reduce API calls.

   • Use trigger conditions to avoid running flows on every event, filtering the trigger to only respond to changes that require action.

2. Schedule Flows Strategically:

   • Schedule high-frequency or data-intensive flows during off-peak hours if possible, reducing the chances of hitting throttling limits due to concurrent activity.

   • Set polling frequency appropriately based on your licensing plan to prevent excessive API calls.

3. Leverage Batch Operations:

   • Where available, use batch processing options or group data operations into fewer API calls, which can help avoid throttling and speed up data handling.

4. Monitor and Adjust Concurrency:

   • Adjust concurrency settings for actions like Apply to each to limit the number of simultaneous actions, reducing strain on the system and avoiding concurrency limits.

   • Monitor flow runs for bottlenecks and make adjustments, such as breaking up large datasets across multiple flows to distribute the load.

5. Utilize Power Query in Dataflows:

   • Consider using Power Query within Dataverse Dataflows for complex data transformations and cleansing, which can offload some of the processing from Power Automate and reduce the risk of throttling.

So what can I do about it!?

Well that is a great question! As with anything IT related there are endless ways only stopped by your imagination of how to assemble all the pieces but the key to remember is to always pay attention to the amount of API calls you are making, how often you are triggering your flows and the size, scope and complexity of your data set.

Instead of the more complex example from above that goes into way more architecture lets cover end to end a much simpler example where we just regularly import a set of Amazon Products from the Kaggle Open Source Data that is relatively large and for this example are unable to import via API for whatever reason and instead recieve a .csv or .xslx file via an automated email attachment on a daily basis.

amazon.csv

Download CSV • 4.74MB

In Power Platform, a typical approach for processing large volumes of data from email attachments, such as daily SQLite database files, is to use Power Automate. A flow is triggered when an email with a specific header arrives, extracting the attachment, and processing each row in a loop to upload the data to a target data source, such as Dataverse, SharePoint, or SQL. While Power Automate is capable of managing this across multiple data sources, performance issues often arise when processing more than a few hundred rows. For instance, processing data beyond around 1,000 rows can strain performance without premium licensing or meticulous flow design. This limitation exists because Power Automate relies heavily on HTTP requests to move data row by row. As each row requires multiple requests to update the dataset, the process can be throttled and slowed significantly—especially when reaching thousands of rows. Consequently, tasks that should ideally complete within minutes can instead take hours when throttling kicks in due to high request volumes.

By contrast, using dataflows (e.g., in Power BI or Synapse) or Dataverse Dataflows offers a much more efficient approach. These tools leverage server-based processing, where data transformations and uploads occur directly on the server rather than being transmitted via numerous HTTP requests. When dataflows execute an upsert operation (updating existing records and inserting new ones), they do so in bulk, reducing the need for repetitive requests. This server-side processing minimizes throttling and can manage significantly larger datasets at much higher speeds. Instead of each row being individually processed through client-server requests, dataflows load entire batches at once, which is optimized for high-volume data handling. This design enables dataflows to handle what would otherwise require complex flows in Power Automate with a fraction of the time and resources, providing greater efficiency and stability for large-scale data uploads.

BUT I CAN ONLY RUN DATAFLOWS ON A SCHEDULE!!... This doesnt allow for me to have instant start and as efficient of data processing.... NOT TRUE! This situation is actualy able to be rectified in a very simple way by marrying our above solutions together! If you take and build the above flow, lets do that now.

First Navigate to Power Automate and Select Create a Flow. Then name your flow, in this case I called it Amazon Example Flow. Then search for new email and ensure you select the correct when an email arrives for your situation. If your doing this on a personal private email account it would be outlook.com, if your doing it on Office 365 for a work or school account it might be either your own account or as shown below you can also do it from a shared inbox as well, but make sure its not outlook.com or you wont be able to authenticate into your account!

Next select Mark as read or Unread by search after clicking the + to add another step

Then click the + and add a Condition. Add the attachments name from the dynamic content and switch the choice from equals to contains and also the AND to OR and fill in .xlsx, .csv and continue this process for any additional file types you might want to include. You will note that after adding the first Attachments Name from the Dynamic content it will automatically add a For each loop meaning that for each attachment on the email it will take your next steps. The reason for this condition is otherwise if someone has a photo or other content in the body of their email like a company logo it will also treat that as an attachment so this helps ensure you only get the datafile attachment you are expecting!

The next step then is to just create the file wherever you would like to save it. In this case we stored it on SharePoint in our general documents library.

We can then start the magic! We can then start the refresh of a dataflow on demand whenever our email shows up to be processed.

Note: One thing to note here is if you have an expectation that there will often be numerous files that are going to be processed via the same dataflow it is recommended to utilize the 30 minutes timed refresh to refresh the data over constantly attempting to trigger dataflows. This is more for those situations where you get a report only every few hours or days but when a report does show up you do not want to wait for that next 30 minute window to be processed.

You can now either build a new dataflow or if you have an existing simply select it and it will begin refreshing right away rather than having to have it wait for the next scheduled time hack. Another option you can also use is there is also a similar function if you want to chain dataflows together to create a simple flow that triggers when another dataflow finishes. This can help make very large dataflows more manageable if they have several joins or transformations to accomplish before being written to their final home.

If you would like to know more about dataflows please check out the blog under Power Query