i

Description

Retenue d’eau bordée de montagnes, vue depuis le parapet d’un barrage.

Source

Daizy John · original page

Licence

CC0 Public domain

Original photo, unretouched. The background is a version of it stylised by the site.

OneLake + SaaS Mobile Apps: architecture

You'll be able to design a OneLake architecture fed by a mobile SaaS: ingestion, bronze/silver/gold medallion, semantic model, reports.

In brief

• You'll be able to design a OneLake architecture fed by a mobile SaaS: ingestion, bronze/silver/gold medallion, semantic model, reports.
• You'll be able to choose between lakehouse and warehouse, between pipeline, dataflow gen2, notebook, and eventstream, and between Direct Lake, Import, and DirectQuery.
• You'll be able to handle the hard cases of a field source: deletions, schema drift, offline mode, duplicates, timestamps.
• You'll be able to secure (RLS, OneLake security, roles) and industrialize (workspaces, Git, deployment).
• Topic interpretation: see the overview. Volatile facts verified in October 2026 (status 2026-10).

On this page

1. 1. Architecture overview
2. 2. OneLake fundamentals
   1. 2.1 Delta and Parquet
   2. 2.2 Shortcuts
   3. 2.3 Mirroring
3. 3. Fabric items and their uses
   1. 3.1 Lakehouse or warehouse?
   2. 3.2 Which tool for what?
4. 4. Ingestion from a mobile SaaS
   1. 4.1 Incremental load: the watermark pattern
5. 5. The bronze silver gold medallion
   1. 5.1 Idempotence and replay
6. 6. Hard field-data cases
   1. 6.1 Deletion handling
   2. 6.2 Drifting schema
   3. 6.3 Offline, synchronization, and timestamps
   4. 6.4 Duplicates and field-customer identity
   5. 6.5 Photos, signatures, and files
7. 7. The semantic model and storage modes
   1. 7.1 Direct Lake, Import, DirectQuery
   2. 7.2 Specific modeling rules
8. 8. SQL analytics endpoint
9. 9. Security
10. 10. Workspaces and capacities
11. 11. Deployment and Git
12. 12. Summary Notes
13. 13. See also

Level: 🟢→🔴

1. Architecture overview#

🟢 Novice.

Three principles drive everything else:

1. Decouple the SaaS (which changes) from the reporting model (which must remain stable) using at least one intermediate layer.
2. Replay: everything in bronze must allow rebuilding silver and gold; a pipeline you can't replay is a pipeline you can't fix.
3. Measure: each step logs volumes, durations, rejected rows (monitoring).

2. OneLake fundamentals#

🟢 Novice.

2.1 Delta and Parquet#

• Parquet: compressed, immutable columnar file format.
• Delta Lake: transactional layer on top of Parquet files: a log (_delta_log) records table versions. You get ACID transactions, reading a past version (time travel), MERGE (upsert), and controlled schema evolution.
• In Fabric, all lakehouse and warehouse tables are stored as Delta in OneLake.

2.2 Shortcuts#

A shortcut exposes, in a lakehouse, a folder or table located in another lakehouse, ADLS Gen2, Amazon S3, Google Cloud Storage, Dataverse... (evolving list, verify). Benefit: no copy, up-to-date reads. Limits: performance depends on the remote source; security must be designed end-to-end; a shortcut to a vendor structure inherits its schema drift. Delegated identity shortcuts (OneLake to OneLake, including cross-tenant) let you separate producer and consumer permission management; their status (preview or GA) must be checked in Microsoft Learn documentation.

2.3 Mirroring#

Mirroring continuously replicates an operational database to Delta tables in OneLake, with no pipeline to write. Generally available sources (status 2026-10): notably Azure SQL Database, Cosmos DB, Snowflake, PostgreSQL, Oracle; other sources in preview (Azure Database for MySQL, Dremio catalog, SharePoint List...). Value in our context: if the SaaS exposes its database (rare) or if your intermediate layer is a relational database (see SQL Server in SaaS — architecture). A pure SaaS generally does not “mirror”: you go through its APIs.

3. Fabric items and their uses#

🟡 Practitioner.

3.1 Lakehouse or warehouse?#

3.2 Which tool for what?#

Golden rule: orchestrate with a pipeline, compute with a notebook or SQL, and keep Power Query for lightweight transformations and analyst profiles.

4. Ingestion from a mobile SaaS#

🟡 Practitioner.

A SaaS SFA rarely provides access to its database. It offers one or more mechanisms, with very different properties.

4.1 Incremental load: the watermark pattern#

# Fabric Notebook (PySpark) — watermark pattern + Delta upsert (simplified example)
from delta.tables import DeltaTable
from pyspark.sql import functions as F

from datetime import timedelta
dernier = spark.sql("SELECT max(modifie_le) AS m FROM silver.visite").first()["m"]
# 2 h overlap window to absorb late synchronizations
debut = dernier - timedelta(hours=2)
lot = appeler_api_visites(modifie_apres=debut)       # pagination + recovery, out of scope here
df = spark.createDataFrame(lot).withColumn("ingere_le", F.current_timestamp())

cible = DeltaTable.forName(spark, "silver.visite")
(cible.alias("t")
   .merge(df.alias("s"), "t.visite_id = s.visite_id")
   .whenMatchedUpdateAll(condition="s.modifie_le >= t.modifie_le")
   .whenNotMatchedInsertAll()
   .execute())

Key points: window overlap (read a bit backward), unique natural key, the modifie_le >= condition that makes the operation idempotent (replayable without duplicates), and the ingere_le column that tracks reception.

5. The bronze silver gold medallion#

🟡 Practitioner.

Gold is the contract with the report. Anything that changes on the SaaS side must be absorbed in silver, not propagated into gold (Semantic Model, Data Layer and ETL).

5.1 Idempotence and replay#

A replayed batch must produce the same state. Means: natural key + MERGE, bronze partition by ingestion date (a day can be recalculated), run log table (lot_id, status, volumes). See also Acceptance testing and business validation.

6. Hard field-data cases#

🔴 Expert.

6.1 Deletion handling#

Three families of situations:

1. Exposed soft delete (est_supprime, deleted_at field): simple, propagate the flag.
2. Deletion stream or table (events, $deltatoken): replay as a MERGE WHEN MATCHED AND s.suppr THEN DELETE or marking.
3. No information: the only method is reconciliation: at regular intervals, extract current source keys and mark missing keys in silver (NOT MATCHED BY SOURCE).

-- Spark SQL: marking missing rows after full key extraction
MERGE INTO silver.visite t
USING clefs_source s
ON t.visite_id = s.visite_id
WHEN NOT MATCHED BY SOURCE AND t.supprime = false THEN
  UPDATE SET t.supprime = true, t.supprime_detecte_le = current_timestamp()

6.2 Drifting schema#

A SaaS vendor adds fields, renames, changes a type, rolls out customer-specific custom fields. Defenses:

• bronze in a flexible schema (JSON or controlled mergeSchema), never SELECT * into gold;
• explicit projection in silver (expected column list);
• contract test at each ingestion: expected columns present, compatible types, null rates; in case of mismatch, alert without breaking (quarantine a new field, hard fail for a missing required field);
• versioning of definitions (Git) and of the called API version number.

attendues = {"visite_id":"string","commercial_id":"string","debut":"timestamp","statut":"string"}
manquantes = [c for c in attendues if c not in df.columns]
if manquantes:
    raise Exception(f"Contract broken, missing columns: {manquantes}")
inconnues = [c for c in df.columns if c not in attendues]
if inconnues:
    journaliser_alerte("new unprojected columns", inconnues)   # proceed, inform

6.3 Offline, synchronization, and timestamps#

A visit entered Monday at 10 a.m. with no network, synchronized Tuesday at 6 p.m., arrives late. Consequences:

• Late facts: Monday's figures change Tuesday evening. The report should display a last synchronization date or last update date, and the business team must know that a “day D” is only stable at D+N.
• Three timestamps to keep: evenement_le (device, may be wrong), synchro_le (vendor server), ingere_le (your pipeline).
• Time zones: store in UTC, convert at display time (offset column by country or rep time zone). Time changes produce negative durations or one-hour duplicates.
• Sync conflicts: two devices modify the same record; the vendor arbitrates (last write wins?). You receive the arbitrated version: without vendor history, some edits are lost. Keep every received version in bronze.

6.4 Duplicates and field-customer identity#

The same customer entered twice by two reps, the same visit created by a double sync. Typical chain: vendor technical key → functional key (ERP customer code if available) → fuzzy matching rules (normalized name + address + geocode) → xref_client mapping table maintained by a human for ambiguous cases. AI can help suggest matches (AI file).

6.5 Photos, signatures, and files#

Attachments do not belong in tables: files in lakehouse Files (or ADLS via shortcut), metadata table (path, hash, size, associated visit). Shelf photos are high-volume and subject to GDPR if people appear in them.

7. The semantic model and storage modes#

🟡 Practitioner.

7.1 Direct Lake, Import, DirectQuery#

Principle of Direct Lake: the model reads Parquet files from Delta tables directly, loads into memory (VertiPaq) only the columns needed for a query, and a “refresh” only re-frames (framing) references to the latest version of the tables.

Two variants (as of 2026-10, according to Microsoft Learn documentation updated in September 2026):

Guardrails by SKU (documentation excerpts, as of 2026-10): F2 to F8: 1,000 Parquet files, 300 million rows per table, 10 GB model size on disk; F64: 5,000 files, 1.5 billion rows, unlimited model size; F512: 10,000 files, 12 billion rows.

7.2 Specific modeling rules#

• Star schema (Kimball): facts visit, order, reading; dimensions date, customer, product, sales_rep, sector. No 300-column “flat” tables.
• Types: Direct Lake does not accept complex Delta types or GUID/binary; convert to string in silver/gold.
• Keys: columns on the “one” side of a relationship must be unique, otherwise queries fail (as of 2026-10); validate in gold with a test.
• Measures: centralized DAX (DAX Measures and Calculations), with translations for multilingual support (Accessibility and Multilingual).
• Dates: one marked date table; specific commercial period calendars.

8. SQL analytics endpoint#

🟡 Practitioner.

Each lakehouse and each warehouse exposes a SQL analytics endpoint: a T-SQL interface (read-only for lakehouse) usable by SSMS, ODBC clients, and other tools. Uses: exploration, views, SQL reports, legacy DirectQuery reports, figure validation.

Points to watch:

• Metadata sync latency: after a Delta write, the endpoint may take some time to see new tables/columns (monitor this; the resync API exists, to be verified based on 2026-10 status).
• Views on the endpoint trigger DirectQuery fallback for a “Direct Lake on SQL” model; with “on OneLake,” materialized tables are preferred.
• Endpoint security (SQL RLS/CLS) does not apply to Direct Lake on OneLake: security is configured on the OneLake security side or the model side.

9. Security#

🔴 Expert.

Four levels, not to be confused (RLS Security and Access):

1. Workspace roles (Admin, Member, Contributor, Viewer): rights across all items. A workspace Viewer is not a harmless report consumer: they can read data depending on context.
2. Item permissions (sharing a report, model, or lakehouse): finer granularity, to be documented.
3. OneLake security: native fine-grained storage access control (folder, table, row, column), enforced by all engines; generally available since spring 2026 (exact date not confirmed by a primary source), enabled by default for new items (as of 2026-10). The main benefit is consistency: the rule follows the data whether accessed through Spark, SQL, or Power BI.
4. Semantic model RLS: DAX roles (e.g., [Commercial_Email] = USERPRINCIPALNAME() via a sales hierarchy table). With Direct Lake, documentation recommends a fixed identity (cloud connection with fixed identity) with model RLS.

SFA case: a sales rep sees their portfolio, their manager sees the team, a director sees the region. The hierarchy must be historized and updated without intervention (assignment changes), and the classic side effect is back-filtering: a measure counting customers varies by role (expected behavior, to be explained).

Personal data: minimization (pseudonymization of agent identities beyond what usage requires), retention periods by table, right to erasure (propagate deletion down to bronze) — see Security, Privacy, Compliance.

10. Workspaces and capacities#

🟡 Practitioner.

• A workspace is an item container, attached to one capacity (F-SKU, see difficulties).
• Recommended split: spaces by lifecycle stage (Dev / Test / Prod) and, for large projects, by layer (ingestion, transformation, semantic/reports) to isolate permissions and consumption.
• Separating the semantic model (BI team) from reports (power users) enables the “thin report” connected to a certified model.
• Multiple capacities: one protected production capacity, one smaller development/ad hoc capacity: test noise does not impact users.
• Regions: Direct Lake requires the model workspace and source to be in the same region (workaround via shortcuts documented for the SQL analytics endpoint variant, as of 2026-10).
• Copilot and AI consume capacity CUs (the AI file).

11. Deployment and Git#

🔴 Expert.

Fabric provides two complementary mechanisms:

• Git integration (Azure DevOps or GitHub): workspace content is serialized (TMDL for models, PBIR format for reports, item definitions for notebooks, pipelines, lakehouse…). One workspace = one branch. File-level commit is in preview, and branch switching by a Contributor requires an administrator to have enabled the corresponding workspace setting (as of 2026-10).
• Deployment pipelines: Dev → Test → Prod with parameter rules (change source, connection); note: some source rebind rules are not supported for Direct Lake on OneLake (as of 2026-10) — plan variable-based configuration or auto-binding.
• Azure DevOps Server (on-prem): Fabric Git integration targets Azure DevOps Services and GitHub; with an internal server you need a cloud Git repository or an API script (to be verified for your context) — see Source Control for Data: SQL and Power BI and Branching Strategies.

Dev workspace  <--feature/xyz branch--  Git (Azure DevOps / GitHub)
Test workspace <--main branch (PR)--    Git
Prod workspace <-- deployment pipeline (Test -> Prod) with parameter rules

12. Summary Notes#

Expert assessments, contextual (cf. RATING). Context: BI team of 3 to 6 people, SaaS SFA + complementary sources, 100–500 users.

13. See also#

• Series: Overview · Difficulties and Pitfalls · AI · SQL Server in SaaS — architecture · Comparison · Cross-Cutting Power BI Good Practices · Checklist
• Matrices: BI Development · Performance and Data Volume · Modeling and Architecture
• Terms: Glossary

✻ Written by Claude Sonnet 5.5