Dataflows and data movement

8.1Dataflows: standard and analytical

the destination decides the kind

A dataflow is a Power Query definition held in your environment and refreshed on a schedule. It reads from a connector, transforms in Power Query, and writes to a destination. The destination is not a property of the transformation: it is chosen when the dataflow is created, and it decides what the dataflow is called and what it can do.

Selecting a storage destination determines the dataflow's type. A dataflow that loads into Dataverse tables is a standard dataflow; one that loads into analytical tables is an analytical dataflow. A standard dataflow can be created in Power Apps and nowhere else. Dataflows created in Power BI are analytical, and dataflows created in Power Apps are either kind, depending on the choice made at creation.citedsource: Power Query docs — Dataflow types › what decides standard or analytical · artifacts-ch08.md

The destination decides the feature set, and the documentation gives six differences between the two kinds.citedsource: Power Query docs — Dataflow types › the capability differences · artifacts-ch08.md

A computed table — a table in a dataflow whose input is another table in the same dataflow — belongs to the analytical kind alone. When a standard dataflow meets Dataverse service protection, the documented behaviour is that a record is retried up to three times.citedsource: Power Query docs — Dataflow types › the capability differences · artifacts-ch08.md

A dataflow may be refreshed on a schedule up to 48 times a day, and one table's refresh may run for up to 2 hours. Incremental refresh is bounded at 50 partitions for a single query and 150 for the whole dataflow, checked at publish time rather than at refresh time. A backup and restore of an environment does not copy its dataflows.citedsource: Power Query docs — Dataflows in Power Platform › refresh limits and known limitations · artifacts-ch08.md

Usage: pac [admin] [application] [auth] [canvas] [catalog] [code] [connection] [connector] [copilot] [env] [help] [managed-identity] [model] [modelbuilder] [package] [pages] [pcf] [pipeline] [plugin] [power-fx] [solution] [telemetry] [test] [tool]
(neither command printed a line)

8.2Dataflows inside a solution

what travels, and what has to be done again at the other end

A dataflow can be added to a solution and moved between environments with it. What arrives at the other end is the definition, and the parts of a dataflow that are not the definition are the parts a deployment has to deal with.

A dataflow added to a solution is a solution-aware dataflow, and a solution may hold several. Only a dataflow created in a Power Platform environment can be solution-aware. The data a dataflow loaded into its destination is not portable as part of the solution — only the definition is — so the data is recreated at the other end by refreshing the dataflow there. Credentials for the connections a dataflow uses are not persisted by a solution: after deployment the connections have to be edited before the dataflow can be scheduled, and that holds for an update import and an upgrade import alike, whether or not the dataflow itself changed.citedsource: Power Query docs — Dataflows in solutions › what travels and what does not · artifacts-ch08.md

The documented limitations name four that decide whether a pipeline can carry a dataflow at all. A dataflow cannot use a connection reference for any connector, and cannot use an environment variable — the two mechanisms a solution has for deferring a value to its target (§9.3). A dataflow does not add its required components, so a custom table it loads into is added to the solution by hand. An application user cannot deploy a dataflow, which rules out a service principal (§9.5). An incremental refresh configuration is not carried by the solution and is reapplied at the target after deployment, as is a link to another dataflow.citedsource: Power Query docs — Dataflows in solutions › the known limitations list · artifacts-ch08.md

Teams that ship on a schedule tend to stop putting dataflows in the deployed solution. The dataflow becomes a separately managed artifact, configured once per environment and left there, and the solution carries the tables it writes into. You trade the ingestion definition's place in source control for a release that finishes without a person.experientialbasis: deployments where the dataflow in a solution was the one component that made an otherwise unattended release need a person, and teams that responded by moving ingestion out of the solution rather than by automating around it

8.3Paths out of Dataverse

the axis is direction, latency and who operates it

Six mechanisms move data between Dataverse and something else, and they are not generally alternatives — each answers a different question. What separates them is which direction data travels, how fresh it is at the far end, and which team runs the thing.

Sorted on that axis, the six fall out as follows. The table is a judgment about which question each path answers; what each path does is cited below it.experientialbasis: the sorting a team actually performs when choosing an integration path, drawn from projects where the wrong one was chosen first; each path's own behaviour is cited separately in this section rather than in the table

Three of the six are in the table for the comparison and settled elsewhere: the dataflow in §8.1 and §8.2, the Web API in §8.4 and §8.5, and the cloud flow in the chapter before this one. This section takes up the remaining three.

Azure Synapse Link for Dataverse, formerly Export to data lake, writes into an Azure Data Lake Storage account the customer owns. A table is eligible only if its Track changes property is on, the person creating the link holds the Dataverse system administrator role, and an environment is bounded at 10 Synapse Link profiles. It produces two copies of each table: near real-time data, synchronised by detecting what changed since the last synchronisation, and snapshot data, a read-only copy of the near real-time data refreshed every hour. Under high transaction volumes the documentation states that near real-time availability cannot be guaranteed.citedsource: Power Apps docs — Azure Synapse Link for Dataverse › what it produces · artifacts-ch08.md

Link to Microsoft Fabric needs no storage account and no Synapse workspace. Dataverse writes an optimised replica of the data in delta parquet format into Dataverse's own storage and shortcuts it into OneLake, so the data stays in Dataverse and consumes Dataverse storage rather than the customer's. Every non-system table whose Track changes property is on is added when the link is made. An environment links to one Fabric workspace.citedsource: Power Apps docs — Link to Microsoft Fabric › how it differs from Synapse Link · artifacts-ch08.md

The Tabular Data Stream endpoint emulates a SQL connection over the Dataverse business layer and is read-only: an INSERT or an UPDATE does not work through it. The Enable TDS endpoint setting is on by default. Only Microsoft Entra ID authentication is accepted, and a client needs port 1433 or port 5558 open. A query is bounded by a fixed five-minute timeout, which the platform reduces to two minutes for a complex query. A query through this endpoint does not fire plug-ins registered on RetrieveMultiple or Retrieve, it executes under the service protection limits, and it cannot read an elastic table.citedsource: Power Apps docs — Use SQL to query data › the TDS endpoint and its bounds · artifacts-ch08.md

<OrgSettings>
<IsCommandingModifiedOnEnabled>true</IsCommandingModifiedOnEnabled>
<IsLinkToFabricEnabled>true</IsLinkToFabricEnabled>
<IsFabricVirtualTableEnabled>true</IsFabricVirtualTableEnabled>
<CanCreateApplicationStubUser>false</CanCreateApplicationStubUser>
<EnableActivitiesTimeLinePerfImprovement>1</EnableActivitiesTimeLinePerfImprovement>
<EnableActivitiesFeatures>1</EnableActivitiesFeatures>
<IsRetentionEnabled>true</IsRetentionEnabled>
<IsArchivalEnabled>true</IsArchivalEnabled>
<AllowRoleAssignmentOnDisabledUsers>false</AllowRoleAssignmentOnDisabledUsers>
</OrgSettings>

8.4Alternate keys and upsert

addressing a row by the identifier the other system has

An integration writes rows it did not create. The system on the far side holds its own identifier for each of them and has no Dataverse row id. An alternate key is what lets you address a Dataverse row by that foreign identifier, in the URL, without a lookup first.

An alternate key is one or more columns whose combination is unique. A table may have up to 10 of them. The columns that may take part are decimal, whole number, single line of text, date and time, lookup and choice, and the key as a whole must fit the underlying index constraints of 900 bytes and 16 columns. Where a key column holds a null, uniqueness is not enforced. Where a key column's data contains any of <, >, *, %, &, :, /, \ or #, an update or upsert through PATCH does not work.citedsource: Power Apps docs — Define alternate keys › limits and the index system job · artifacts-ch08.md

Creating an alternate key starts a system job that builds the indexes enforcing its uniqueness, and the key is not in effect until they exist. The key's state follows that job through Pending, In Progress, Active and Failed, and only Active means the key can be used.citedsource: Power Apps docs — Define alternate keys › limits and the index system job · artifacts-ch08.md

An upsert is a PATCH to the URI of a record: where the record does not exist it is created, and where it does it is updated. The documented use is synchronising with an external system that has no Dataverse row id, addressing the row by an alternate key instead. The response is the same either way, and the OData-EntityId header carries the key values rather than the row id — so a caller that needs to know which happened sends Prefer: return=representation and reads 201 Created for a create and 200 OK for an update, at the cost of an added retrieve. Alternate key values are not put in the request body. If-Match: * is the header that stops a PATCH creating a record by accident.citedsource: Power Apps docs — Update and delete rows with the Web API › upsert · artifacts-ch08.md

      "LogicalName": "ch89_releasecode",
      "SchemaName": "ch89_ReleaseCode",
      "KeyAttributes": [
        "ch89_code"
      ],
      "EntityKeyIndexStatus": "Pending"
… the same request later in the same session …
      "EntityKeyIndexStatus": "Active"

==== upsert 1 — PATCH by alternate key, record does not exist
HTTP 201
Preference-Applied: return=representation
x-ms-ratelimit-burst-remaining-xrm-requests: 7998
x-ms-ratelimit-time-remaining-xrm-requests: 1,199.86
{
  "@odata.context": "https://org5014704c.crm.dynamics.com/api/data/v9.2/$metadata#ch89_releases(ch89_releaseid)/$entity",
  "@odata.etag": "W/\"2123651\"",
  "ch89_releaseid": "052c1e2d-9190-f111-8076-70a8a59af8d5"
}

==== upsert 2 — the same URL again, record now exists
HTTP 200
Preference-Applied: return=representation
x-ms-ratelimit-burst-remaining-xrm-requests: 7997
x-ms-ratelimit-time-remaining-xrm-requests: 1,199.86
{
  "@odata.context": "https://org5014704c.crm.dynamics.com/api/data/v9.2/$metadata#ch89_releases(ch89_releaseid)/$entity",
  "@odata.etag": "W/\"2124255\"",
  "ch89_releaseid": "052c1e2d-9190-f111-8076-70a8a59af8d5"
}

==== upsert 3 — If-Match: * refuses to create
HTTP 404
x-ms-ratelimit-burst-remaining-xrm-requests: 7996
x-ms-ratelimit-time-remaining-xrm-requests: 1,199.86
{
  "error": {
    "code": "0x80060891",
    "message": "A record with the specified key values does not exist in ch89_release entity"
  }
}

==== upsert 4 — If-None-Match: * refuses to update
HTTP 412
x-ms-ratelimit-burst-remaining-xrm-requests: 7995
x-ms-ratelimit-time-remaining-xrm-requests: 1,199.86
{
  "error": {
    "code": "0x80040237",
    "message": "A record with matching key values already exists."
  }
}

8.5Batching and the service protection counters

what one request costs, and what a batch is one of

A row-at-a-time integration spends most of its time waiting for the network. The Web API gives you $batch, which carries many operations in one HTTP request, and the change set, which makes a group of them atomic.

A batch request carries up to 1,000 individual requests and cannot contain another batch. Operations grouped in a change set are atomic: where one of them fails, the batch rolls back the ones that had completed. A GET is not allowed inside a change set. The payload's line endings must be CRLF, and other line endings produce deserialisation errors. By default the batch stops at the first error and returns it; Prefer: odata.continue-on-error makes the server carry on and report each failure in the body.citedsource: Power Apps docs — Execute batch operations with the Web API › batches and change sets · artifacts-ch08.md

HTTP 200
--batchresponse_9ed5058b-f992-424c-8d7b-3eacbfa917c5
Content-Type: multipart/mixed; boundary=changesetresponse_9264b309-bee9-405f-a5c7-bcaf14a0e7d2

--changesetresponse_9264b309-bee9-405f-a5c7-bcaf14a0e7d2
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 1

HTTP/1.1 204 No Content
OData-Version: 4.0
Location: https://org5014704c.crm.dynamics.com/api/data/v9.2/ch89_releases(ch89_code='REL-0003')
OData-EntityId: https://org5014704c.crm.dynamics.com/api/data/v9.2/ch89_releases(ch89_code='REL-0003')
X-Content-Type-Options: nosniff
… the parts for Content-ID 2 and Content-ID 3, in the same shape …
--changesetresponse_9264b309-bee9-405f-a5c7-bcaf14a0e7d2--
--batchresponse_9ed5058b-f992-424c-8d7b-3eacbfa917c5--

HTTP 400
--batchresponse_d9374753-b113-4f07-a479-552b368e08de
Content-Type: multipart/mixed; boundary=changesetresponse_86a9d9c8-fea3-4753-8e92-429d5d6fc8fb

--changesetresponse_86a9d9c8-fea3-4753-8e92-429d5d6fc8fb
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 3

HTTP/1.1 400 Bad Request
REQ_ID: c6652958-c214-476c-a8b7-9c3582919265
X-Content-Type-Options: nosniff
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0

{"error":{"code":"0x80044331","message":"A validation error occurred.  The length of the 'ch89_name' attribute of the 'ch89_release' entity exceeded the maximum allowed length of '100'."}}
--changesetresponse_86a9d9c8-fea3-4753-8e92-429d5d6fc8fb--
--batchresponse_d9374753-b113-4f07-a479-552b368e08de--

HTTP 200
x-ms-ratelimit-burst-remaining-xrm-requests: 7992
x-ms-ratelimit-time-remaining-xrm-requests: 1,198.52
{
  "@odata.context": "https://org5014704c.crm.dynamics.com/api/data/v9.2/$metadata#ch89_releases(ch89_code,ch89_name,ch89_releaseid)",
  "value": [
    {
      "@odata.etag": "W/\"2124255\"",
      "ch89_releaseid": "052c1e2d-9190-f111-8076-70a8a59af8d5",
      "ch89_name": "Release 0001, renamed",
      "ch89_code": "REL-0001"
    },
    {
      "@odata.etag": "W/\"2124721\"",
      "ch89_releaseid": "192e1e2d-9190-f111-8076-70a8a59af8d5",
      "ch89_name": "Release 0003",
      "ch89_code": "REL-0003"
    },
    {
      "@odata.etag": "W/\"2124722\"",
      "ch89_releaseid": "1a2e1e2d-9190-f111-8076-70a8a59af8d5",
      "ch89_name": "Release 0004",
      "ch89_code": "REL-0004"
    },
    {
      "@odata.etag": "W/\"2124723\"",
      "ch89_releaseid": "1b2e1e2d-9190-f111-8076-70a8a59af8d5",
      "ch89_name": "Release 0005",
      "ch89_code": "REL-0005"
    }
  ]
}

before: burst-remaining=7994 time-remaining=1,199.55
20 rows, one request each: 8.32 s (416 ms per row)
20 rows, one $batch:       1.04 s (52 ms per row), HTTP 200, 20 success parts
after:  burst-remaining=7973 time-remaining=1,199.19