🔄 Syncing Microsoft Fabric OneLake Using Logic Apps + OneLake APIs

🔄 Syncing Microsoft Fabric OneLake Using Logic Apps + OneLake APIs

Keeping Microsoft Fabric OneLake in sync with upstream systems
doesn't have to be batch-driven. By combining Logic Apps with the
OneLake DFS REST API, we can process webhook events from a
Compliance System and land them as JSON files in the right OneLake folders for downstream pipelines.

---

1. Event-Driven Trigger

The Compliance System pushes webhook events for create, update, and
delete actions.

A Logic App with an HTTP trigger receives these events:

{
  "EventType": "UPDATED",
  "Data": { "CustomerId": "12345", "Status": "Inactive" }
}

---

2. Logic Apps Orchestration

The Logic App workflow handles all orchestration before calling the
OneLake API:

Below is the high-level flow:

Key steps:

1. Receive webhook via HTTP trigger (RcvReqSyncMSFab).
2. Initialize variables (file name, path, OneLake URL).
3. Prepare data (retrieve payload from event source system,serialize payload, calculate byte length).
4. Switch on EventType (created, update, deleted).
5. Create file in OneLake → using the DFS API.
6. Append payload → write JSON data into the file.
7. Flush file → finalize so downstream Fabric pipelines can read.

---

3. OneLake API Calls

The OneLake DFS API exposes file system-like operations. The Logic App invokes these in sequence:

1. Create empty file
   

   PUT https://onelake.dfs.fabric.microsoft.com/<workspace>/<lakehouse>/Files/webhook/<folder>/<filename>.json?resource=file
   Authorization: Bearer @{body('GenerateToken')}
   content-type: application/json
   x-ms-version: 2023-11-03
   x-ms-date: @{utcNow()}
   Body: {null body}
   

2. Append payload
   

   PATCH https://onelake.dfs.fabric.microsoft.com/.../Files/webhook/<folder>/<filename>.json?action=append&position=0
   Authorization: Bearer @{body('GenerateToken')}
   content-type: application/json
   x-ms-version: 2023-11-03
   x-ms-date: @{utcNow()}
   Body: {event payload}
   

3. Flush (commit)
   

   PATCH https://onelake.dfs.fabric.microsoft.com/.../Files/webhook/<folder>/<filename>.json?action=flush&position=<length>
   Authorization: Bearer @{body('GenerateToken')}
   content-type: application/json
   x-ms-version: 2023-11-03
   x-ms-date: @{utcNow()}
   Body: {null body}
   

These API calls are orchestrated entirely inside Logic Apps using HTTP
actions.

---

Calculating the Flush Offset

When calling the OneLake flush API, you must pass the exact byte
offset of the appended data.

🧮 Attempt 1: Math-based calculation

I first tried to compute the offset length with:

`div(mul(length(base64(string(outputs('Payload')))), 3), 4)`

➡️ The result should equal the true byte length of the payload.

However, in practice this can be off by 1--2 bytes (UTF-8 encoding, whitespace, or serialisation quirks). OneLake then rejects the flush with an InvalidFlushPosition error.

---

✅ Workaround: Probe OneLake for the exact position

To make it reliable, we can let OneLake tell us the correct offset:

A flush request is sent without a position, but still includes the payload in the body. OneLake rejects this with BadRequest, but the error response contains the correct Content-Length value.

Implementation (Logic Apps):

Action: HackToGetPosition (HTTP PATCH) — expect BadRequest; then read error.detail.HeaderValue from the response.

This probe + flush handshake ensures the offset is always correct, making the operation deterministic and resilient across different payload shapes.

---

4. End Result in OneLake

Once the Logic App runs, the events are captured and written into OneLake as JSON files. Files are automatically organised into folders by event type (created, update, deleted).

Example — files created under the created folder:

Each file is named with a unique GUID and contains the raw payload of the appropriate data relevant to the webhook event, ready for downstream pipelines to consume.