| name | fabric-app-sqldb-writeback |
| description | Use when a Fabric App needs transactional WRITE-BACK (comments, edits, requests, approvals) on top of Lakehouse data — 'users edit data in my Fabric app', 'write back to the source', 'my saved comment takes forever to appear', 'read-your-own-writes', 'mirror Gold into the app database', 'seed the Rayfin SQL DB headless', or when the app is juggling multiple data tiers/auth flows and needs consolidation. Encodes the operational-data-store architecture: app owns its SQL DB, a copy job mirrors Lakehouse Gold into it, one SDK for everything. For read-only apps use fabric-app-lakehouse-live; for app creation/deploy use fabric-app-bootstrap. |
| license | MIT |
| metadata | {"author":"Gus Bavia","version":"0.1.1","category":"microsoft-fabric","tags":["fabric-apps","rayfin","sql-database","write-back","tds","pyodbc","service-principal","mirror","operational-data-store","entities","dab","read-your-own-writes","seed","refresh"],"companions":["fabric-app-bootstrap","fabric-app-lakehouse-live"],"support":"https://linkedin.com/in/gusbavia"} |
Fabric App · SQL DB Write-back
The architecture for Fabric Apps that write: the app owns its SQL Database (a managed child of the App item), a small job mirrors Lakehouse Gold into it, and write-back entities live alongside the mirrors. One database, one SDK, instant read-your-own-writes.
Lakehouse (canonical, untouched)
↓ copy job (~30s, headless via SP over TDS)
App SQL Database
├─ mirror entities (DimX, FactY...) ← refreshed by re-running the job
└─ write-back entities (Comment, Edit…) ← transactional, instant
↓ ONE SDK (RayfinClient)
SPA — every read and write
Why not write to the Lakehouse directly (the scars)
- Its SQL endpoint is read-only — the GraphQL over it has no mutations.
- Its sync is lazy — a write landed in OneLake in seconds and surfaced through the API a day later. Users will not wait a day to see their own comment.
- Two sources = two auth flows + dual-source merge logic + optimistic-cache band-aids in the frontend, forever.
The copy job moves that complexity out of the frontend into one script. Microsoft's name for this serving pattern: operational data store.
Step 1 · Model entities (TypeScript decorators)
Mirror entities for the Gold tables the app reads + the app's own write-back entities. Keep mirror columns text-friendly (no enum constraints — source data will surprise you). Register everything in the schema the client SDK is typed against.
@entity()
export class DimCustomer {
@field({ type: "uuid", primaryKey: true }) id!: string;
@field({ type: "text", unique: true }) customer_id!: string;
@field({ type: "text" }) customer_name!: string;
@field({ type: "text", optional: true }) segment?: string;
}
Schema apply is OWNER-ONLY: npx rayfin up db apply needs one interactive login by the item owner. The SP cannot apply schema (403 "Only AppBackend artifact owner") — but handles everything else headless.
Never DROP entities, only ADD. Dropping requires a force flag the server currently ignores; a blocked apply costs a night, a retired entity costs nothing.
Step 2 · Seed + refresh over TDS (the automation unlock)
The app's GraphQL data plane accepts delegated user tokens only (SP tokens are rejected regardless of headers). The unlock: the SQL Database child has a real TDS endpoint, and a workspace-Contributor SP connects with plain pyodbc — read AND write, fully headless:
tok = credential.get_token("https://database.windows.net/.default")
packed = struct.pack(f"<I{len(t)}s", len(t), t) if (t := tok.token.encode("utf-16-le")) else None
conn = pyodbc.connect(
"Driver={ODBC Driver 17 for SQL Server};"
"Server=<db-host>.database.fabric.microsoft.com,1433;"
"Database=<db-name>;",
attrs_before={1256: packed},
)
Find the server/database names on the SQL Database child item in the portal. Re-running this script IS the refresh — schedule it (cron/Actions/pipeline) or trigger it from the UI via a delegated pipeline-run REST call.
Reading the SOURCE side (Lakehouse Gold) in the same script — proven path: read the Delta tables straight from OneLake with deltalake (delta-rs), no Spark:
tok = credential.get_token("https://storage.azure.com/.default")
df = DeltaTable(
f"abfss://{WORKSPACE_ID}@onelake.dfs.fabric.microsoft.com/{LAKEHOUSE_ID}/Tables/dim_customer",
storage_options={"bearer_token": tok.token, "use_fabric_endpoint": "true"},
).to_pyarrow_table()
Same SP, two tokens: storage.azure.com scope to READ Gold, database.windows.net scope to WRITE the app DB.
One-time backfill of existing write-back rows (e.g. comments previously written to the Lakehouse): same script, read the old table from Gold, insert into the new entity with the dedup rule below.
TDS wire gotchas
| Gotcha | Detail |
|---|
| Tables are pluralized | entity DimEmployee → dbo.DimEmployees on the wire (the data-API layer names them). Don't guess irregular cases — confirm with SELECT name FROM sys.tables |
| Reserved words | columns named plan, usage etc. need [brackets] in T-SQL |
fast_executemany truncates | string buffer sized from the FIRST row; longer strings later get cut. Use it only on all-numeric facts |
| Dedup key for insert-only tables | dedup write-back rows on a natural composite key (e.g. `author |
Step 3 · One SDK in the frontend
Every read and write goes through the client SDK against the app DB. Short cache (~60s) on mirror reads; write-back reads always fresh. Auth: silent SSO bootstrap when embedded in the Fabric portal; explicit sign-in gate standalone.
SDK gotchas
| Gotcha | Detail |
|---|
| Date auto-conversion | the SDK converts ISO-looking strings to Date objects EVEN ON TEXT COLUMNS. Guard every date-ish field back to string at the fetch boundary or .slice() crashes a page |
Endpoint is /graphql | docs say /api/graphql; the SDK source is the truth |
| Embedded vs standalone | test flows embedded in the portal first; standalone tabs have flaky token refresh |
| Pagination | use the SDK's cursor loop for >1k rows; a flat first(1000) silently caps |
Step 4 · Verify end-to-end
Write from the UI → row visible in the DB inspector/SQL immediately → re-read in the app shows it instantly (same DB, no sync lag). If a "saved" item lags, you are accidentally still reading the Lakehouse path.
Bonus finding
"API for GraphQL" exists on the SQL Database item too — same menu item as the Lakehouse one, but transactional with mutations. Useful as an external-consumer API over the app DB (scripts, Power Automate, a second app) without TDS. Note the distinction: this is a separate Fabric item over the same DB, NOT the app's own data plane (which stays delegated-user-only). For headless automation, TDS is the proven path; treat the SQL DB GraphQL as plan-B and verify SP auth on it before depending on it.
