Scenario: Monthly Data Pipeline Straddling Cloud and On‑Prem

Imagine a small analytics team with this monthly pipeline:

1. Finance drops CSV exports on a network share (\\FIN-SHARE\Finance\Monthly).
2. An on‑prem SQL Server gets updated from those CSVs.
3. A Power BI dataset in the service is refreshed from that SQL Server.
4. Files are archived and a summary email goes out.

The team wants:

• No manual steps on analyst machines.
• Resilience if someone closes their laptop.
• Clear separation of responsibilities: file handling, on‑prem updates, and Power BI refresh.

This is exactly where the choice between Power Automate Desktop and cloud flows matters.

Core Differences That Matter for Data Work

You already know the high‑level distinction (desktop = local automation, cloud = service flows). For data tasks, a few specific behaviours drive the architecture.

1. Where the flow actually runs

• Cloud flows run in the Power Automate service in Microsoft 365. They:

  • Natively see cloud services (SharePoint, OneDrive, Dataverse, Power BI, etc.).
  • Do not see local drives or on‑prem shares directly, unless:
    • You use connectors via an on‑premises data gateway, or
    • You use desktop flows as child flows.

• Power Automate Desktop (desktop flows) run:

  • On a specific Windows machine (user’s PC or unattended VM).
  • With access to local file system, mapped drives, UNC paths, installed apps, and UI.

For our scenario, the network share and on‑prem SQL Server are naturally reachable from a desktop flow running on a server inside the network.

2. Triggers and scheduling

• Cloud flows offer:

  • Rich triggers: HTTP, Power BI, SharePoint, file created/modified, Power Apps, etc.
  • Recurrence for schedules (every X minutes/hours/days).

• Desktop flows:

  • Don’t have native triggers by themselves.
  • Are started by:
    • The Power Automate service (cloud flow calling a desktop flow), or
    • Manual run from the PAD UI.

For scheduled data pipelines, this means:

• Use a cloud flow with a recurrence trigger as the orchestrator.
• Call desktop flows for anything that must touch local/on‑prem resources without a connector.

3. Connectors vs UI / file automation

• Cloud flows rely on connectors:

  • File: SharePoint, OneDrive, Azure Blob, SFTP, etc.
  • Data: SQL Server, Azure SQL, Dataverse, Power BI, etc.
  • On‑prem connectors require gateway.

• Desktop flows use actions that operate on the OS:

  • File actions (copy, move, read, write) against local/UNC paths.
  • Database actions via ODBC/OLE DB or specific drivers.
  • UI automation (desktop apps, browsers).

For data tasks, the key distinction:

• If your system has a supported connector and you’re OK with gateway, cloud flows are simpler.
• If you need file system level access or UI automation, desktop flows are the right tool.

4. Power BI integration surface

As of now, the main Power BI integration points in Power Automate are:

• Power BI connector (cloud):

  • Trigger: When a data driven alert is triggered.
  • Actions: Refresh a dataset, Export report to file (depending on licensing and workspace type).

• No native Power BI actions in desktop flows.

So:

• Anything involving dataset refresh or report export is a cloud flow responsibility.
• Desktop flows can prepare data (files, DB loads) but must be coordinated via a cloud flow to trigger Power BI.

Pattern 1: File Handling – Local/Network Shares vs Cloud Storage

When to use Power Automate Desktop for files

Use desktop flows when:

• Files live on on‑prem network shares or local drives.
• You need Windows‑level operations:
  • Work with locked files, specific paths, file attributes.
  • Interact with legacy apps that generate or transform files.

In our scenario, finance drops CSVs on \\FIN-SHARE\Finance\Monthly. A desktop flow running on a server can:

• Monitor that folder on a schedule (via a cloud‑triggered run).
• Move files to a staging area.
• Validate file names and row counts.

Example desktop flow steps:

1. Get files in folder \\FIN-SHARE\Finance\Monthly.
2. For each CSV:
   • Copy to D:\Data\Finance\Monthly\Staging.
   • Append metadata to a log file.

This is straightforward with PAD file actions and doesn’t require gateway.

When to use cloud flows for files

Use cloud flows when:

• Files live in SharePoint, OneDrive, Azure Blob, or other cloud storage.
• You want event‑driven automation:
  • When a file is created in a folder (SharePoint/OneDrive trigger).

If the team decides to move finance outputs to SharePoint, a cloud flow could:

• Trigger on file creation in Finance/MonthlyUploads.
• Validate metadata.
• Kick off downstream actions including dataset refresh.

Hybrid file pattern that works well

A common, robust pattern for mixed environments:

1. Desktop flow:

   • Copies files from on‑prem share to a SharePoint library using the SharePoint connector via the Power Automate service (called from the cloud flow) or via other supported mechanisms.
   • Normalises file names and structure.

2. Cloud flow:

   • Triggers on new files in that SharePoint library.
   • Loads data into SQL or other cloud data store.
   • Calls Power BI dataset refresh.

This separates:

• On‑prem file access (desktop) from
• Cloud orchestration and BI (cloud).

Pattern 2: On‑Premises Systems – Gateway vs Desktop Flows

Option A: Cloud flow + on‑premises data gateway

If your on‑prem system has a supported connector (e.g. SQL Server), the cleanest pattern is:

• Install an on‑premises data gateway.
• Use the SQL Server connector in a cloud flow.

Pros:

• Fully service‑based; no dependency on a user session.
• Works well with Power BI datasets, which also use the gateway.

Cons:

• Requires gateway installation, configuration, and maintenance.
• Limited to systems with supported connectors.

In our scenario, if the SQL Server is reachable via gateway, you can:

1. Use a cloud flow to run on a schedule.
2. Call SQL actions to run stored procedures that import from a staging table.
3. Refresh Power BI when done.

Option B: Desktop flow for systems without connectors

Use Power Automate Desktop when:

• The system is legacy with no connector (older ERP, line‑of‑business app).
• You need UI automation or file‑based import/export.

Example desktop flow tasks:

• Open the ERP client.
• Navigate to import screen.
• Load the CSV from the network share.
• Trigger the import.

This is something a cloud flow simply cannot do. It can orchestrate, but not perform UI automation.

Combining both for reliability

For our scenario, a robust approach is:

1. Desktop flow on an unattended VM:

   • Pulls CSV from \\FIN-SHARE\Finance\Monthly.
   • Uses UI automation or ODBC/OLE DB to push data into a staging table in SQL Server.

2. Cloud flow:

   • Recurrence trigger (e.g. hourly).
   • Checks a status table or log written by the desktop flow.
   • When new data is ready, runs SQL stored procedures via gateway and then refreshes Power BI.

This keeps:

• Fragile UI automation inside a controlled machine.
• Long‑running orchestration and BI refresh in the cloud.

Pattern 3: Power BI Integrations – Dataset Refresh and Beyond

What cloud flows can do with Power BI

The Power BI connector in Power Automate (cloud) currently provides:

• Triggers:

  • When a data driven alert is triggered (based on Power BI alert rules).

• Actions (availability depends on licensing/workspace type):

  • Refresh a dataset.
  • Export report to file (PDF, PPTX, etc.) for supported capacities.

Key behaviours for dataset refresh:

• The action is asynchronous: it requests a refresh and returns.
• You can poll refresh status via the Power BI REST API using HTTP actions if needed.

Desktop flows do not directly integrate with Power BI; they must be coordinated via cloud flows.

Orchestration pattern for our scenario

A practical end‑to‑end design:

1. Cloud flow (orchestrator) – recurrence trigger (e.g. daily at 06:00):

   • Calls a desktop flow to process files and update on‑prem SQL.
   • Waits for completion (desktop flow returns status).
   • If success:
     • Runs a SQL stored procedure via gateway to finalise aggregations.
     • Calls Power BI – Refresh a dataset for the target dataset.
   • If failure:
     • Sends a notification (Teams, email) with log details.

2. Desktop flow (file + on‑prem prep):

   • Reads CSVs from \\FIN-SHARE\Finance\Monthly.
   • Validates files and writes a log.
   • Loads data into staging tables.
   • Returns a status object to the cloud flow.

This division keeps the Power BI piece entirely in the cloud, while still allowing on‑prem work.

Example: HTTP polling for refresh status

If you need to ensure the Power BI refresh completes before downstream actions, you can use the Power BI REST API in a cloud flow. For illustration, here’s a simple pattern using an HTTP call in a script (the same endpoint is used by the HTTP action in a cloud flow):

import requests
Assume you already obtained an Azure AD access token for Power BI
access_token = "<access_token>"  # placeholder, injected securely in practice
workspace_id = "<workspace_id>"
dataset_id = "<dataset_id>"
headers = {
"Authorization": f"Bearer ",
"Content-Type": "application/json"
}
Trigger refresh
refresh_url = f"https://api.powerbi.com/v1.0/myorg/groups//datasets//refreshes"
trigger_response = requests.post(refresh_url, headers=headers, json=)
trigger_response.raise_for_status()
Poll status
import time
while True:
status_response = requests.get(refresh_url, headers=headers)
status_response.raise_for_status()
refreshes = status_response.json().get("value", [])
if not refreshes:
time.sleep(10)
continue
latest = refreshes[0]
if latest.get("status") in ["Completed", "Failed"]:
    print("Refresh status:", latest.get("status"))
    break

time.sleep(10)

In Power Automate, you’d use the HTTP action instead of Python, but the endpoint and logic are the same.

Practical Decision Rules: Desktop vs Cloud for Data Tasks

For experienced practitioners, the decision usually boils down to these questions:

1. Where is the data and how is it accessed?

• Cloud‑native storage or APIs (SharePoint, OneDrive, Azure SQL, SaaS):

  • Prefer cloud flows with connectors.

• On‑prem files / legacy apps / local-only access:

  • Use desktop flows on an unattended machine inside the network.

2. Do you need Power BI integration?

• Any dataset refresh, alert‑driven action, or report export:
  • Must involve cloud flows.
  • Desktop flows can prepare data but should not be the integration point.

3. Is there a supported connector with gateway?

• If yes: consider cloud flow + gateway first.
• If no: use desktop flow for the gap, and expose status/data via a mechanism the cloud flow can see (DB table, SharePoint file, API).

4. Who owns the runtime and reliability?

• If you want no dependency on user machines:

  • Avoid running desktop flows on personal laptops.
  • Use unattended desktop flows on a managed VM or server.

• If you want minimal infrastructure and everything is cloud‑native:

  • Stay with cloud flows only.

A Concrete Takeaway

For any pipeline that touches on‑prem files or systems and ends in Power BI, treat Power Automate Desktop as your on‑prem worker and cloud flows as your orchestrator and BI gateway. Design flows so that desktop handles file system and legacy app work on a stable machine, while cloud flows own scheduling, gateway‑based data operations, and Power BI refresh, and you’ll avoid the fragile “analyst laptop as production server” pattern.