OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts

Introduction

Picture this: your organization has a Power BI dataset pulling from an Azure SQL Database, an Azure Synapse Analytics pool reading from ADLS Gen2, a data science team working in Azure Databricks against yet another storage account, and a reporting team that's copied the same sales data three times because nobody trusted anyone else's version. You have four systems, at least three copies of critical data, and a growing sense that the data engineering budget is mostly paying for duplication and confusion.

This is the problem OneLake was designed to solve — not by adding another storage system to your portfolio, but by replacing all of them with a single, unified data lake that every Fabric workload reads from and writes to natively. The promise is audacious: one copy of your data, accessible by every tool in the Fabric ecosystem, with no export, no syncing, and no drift between what the data engineer sees and what the analyst reports on.

By the end of this lesson, you'll understand the architectural decisions that make OneLake work, why Delta Parquet is the storage format everything converges on, how Shortcuts let you virtually unify data without actually moving it, and where the real-world gotchas are that the marketing materials won't tell you.

What you'll learn:

• How OneLake is physically structured and how it maps to Fabric workspaces and items
• Why Delta Lake format is the foundation of OneLake's interoperability story
• The internal anatomy of a Delta table: what files actually exist and what they do
• How Shortcuts work architecturally, including cross-cloud and cross-tenant scenarios
• The performance, cost, and consistency implications of sharing data through OneLake
• How to avoid the most common mistakes teams make when designing their OneLake topology

---

Prerequisites

You should be comfortable with the concepts covered in What Is Microsoft Fabric? Workloads, OneLake, and How It Fits with Power BI and have a working Fabric workspace. If you haven't set one up yet, Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace will walk you through the process. You should also have a working knowledge of Parquet files and basic familiarity with cloud object storage concepts.

---

How OneLake Is Actually Structured

OneLake is built on top of Azure Data Lake Storage Gen2 (ADLS Gen2), but it adds a managed, hierarchical namespace that Microsoft controls on your behalf. You don't provision a storage account. You don't manage redundancy settings. You don't configure network rules. Instead, OneLake is provisioned automatically the moment you create a Fabric tenant, and it scales invisibly behind the scenes.

The hierarchy works like this:

Tenant
└── Workspace (e.g., "Sales Analytics")
    ├── Lakehouse (e.g., "SalesLakehouse")
    │   ├── Files/
    │   │   └── raw/
    │   │       └── orders_2024.csv
    │   └── Tables/
    │       └── orders/
    │           ├── _delta_log/
    │           └── part-00000-abc123.snappy.parquet
    ├── Warehouse (e.g., "SalesWarehouse")
    │   └── Tables/
    │       └── fact_sales/
    └── Dataset / Semantic Model

Each Fabric item — a Lakehouse, a Warehouse, an Eventstream, a KQL database — gets its own folder inside the workspace folder in OneLake. This isn't just organizational convenience. It's the architectural reason why a Spark notebook and a Power BI report can both point at the same physical data without a copy existing between them.

The path to any file in OneLake follows this pattern:

https://<tenant>.dfs.fabric.microsoft.com/<workspace-guid>/<item-guid>/Tables/orders/

You'll also see an alias-based URL pattern using workspace and item names, but the GUID-based paths are stable even when you rename things. This matters when you're hardcoding paths in Spark jobs or external tooling.

The Managed vs. Unmanaged Distinction

Within a Lakehouse, OneLake has two root folders: Files/ and Tables/. This distinction is load-bearing for how different Fabric workloads interact with your data.

Files/ is the unmanaged zone. You can put anything here — CSV files, JSON, Parquet, images, XML, arbitrary binary blobs. Fabric doesn't enforce any schema or format. Spark notebooks can read and write here freely. The Fabric Lakehouse explorer will show these files, but the SQL analytics endpoint won't automatically surface them as queryable tables.

Tables/ is the managed zone. Everything in here is expected to be a Delta table. When Fabric detects a valid Delta table in this folder, it automatically registers it in the Lakehouse metastore and exposes it through the SQL analytics endpoint. This is the "automatic table discovery" behavior you'll read about in Microsoft's docs — but what actually triggers it is the presence of a _delta_log folder with valid transaction log JSON files.

---

Delta Lake: The Format That Makes Everything Work

If OneLake is the container, Delta Lake is the language everyone in OneLake speaks. Understanding Delta's internals isn't optional if you want to build reliable data products on Fabric — it's the difference between knowing how to use a tool and knowing why the tool works.

What a Delta Table Actually Is on Disk

A Delta table at rest is not a single file. It's a folder structure containing two things: Parquet data files and the Delta transaction log.

Tables/
└── orders/
    ├── _delta_log/
    │   ├── 00000000000000000000.json
    │   ├── 00000000000000000001.json
    │   ├── 00000000000000000002.json
    │   └── 00000000000000000010.checkpoint.parquet
    ├── part-00000-a1b2c3d4-e5f6-7890-abcd-ef1234567890.c000.snappy.parquet
    ├── part-00001-b2c3d4e5-f6a7-8901-bcde-f12345678901.c000.snappy.parquet
    └── year=2024/
        └── month=01/
            └── part-00000-c3d4e5f6-...snappy.parquet

The _delta_log folder is the heart of Delta. Each JSON file in there is a commit — an atomic record of what changed in the table. Here's what a simplified commit looks like after an initial data load:

{
  "commitInfo": {
    "timestamp": 1704067200000,
    "operation": "WRITE",
    "operationParameters": {"mode": "Overwrite", "partitionBy": "[]"},
    "engineInfo": "Fabric Spark"
  },
  "add": {
    "path": "part-00000-a1b2c3d4-e5f6-7890-abcd-ef1234567890.c000.snappy.parquet",
    "size": 2048576,
    "modificationTime": 1704067200000,
    "dataChange": true,
    "stats": "{\"numRecords\":150000,\"minValues\":{\"order_date\":\"2024-01-01\"},\"maxValues\":{\"order_date\":\"2024-01-31\"}}"
  }
}

And here's what a subsequent update commit looks like — notice that the old file is marked remove and a new file is added:

{
  "commitInfo": {
    "timestamp": 1704153600000,
    "operation": "MERGE",
    "operationParameters": {"predicate": "(source.order_id = target.order_id)"}
  },
  "remove": {
    "path": "part-00000-a1b2c3d4-e5f6-7890-abcd-ef1234567890.c000.snappy.parquet",
    "deletionTimestamp": 1704153600000,
    "dataChange": true
  },
  "add": {
    "path": "part-00000-d4e5f6a7-b8c9-0123-defg-456789012345.c000.snappy.parquet",
    "size": 2101248,
    "dataChange": true,
    "stats": "{\"numRecords\":150423,...}"
  }
}

This log-based approach gives you several things that a naive Parquet-on-storage system can't provide: ACID transactions, time travel, schema enforcement, and the ability for multiple readers and writers to operate concurrently without corrupting each other.

Checkpointing and Why It Matters for Query Performance

As your Delta table accumulates commits, reading the log sequentially becomes expensive. Delta mitigates this with checkpoints: Parquet files that consolidate all the active add entries from the log up to a certain commit number. By convention, checkpoints are created every 10 commits (this is configurable).

When a reader wants to reconstruct the current state of the table, it finds the most recent checkpoint, reads that Parquet file to get the baseline file list, then replays only the JSON commits that occurred after the checkpoint. For a table with thousands of commits, this can mean the difference between reading 1 file versus 10,000 JSON blobs.

Delta Table Features You'll Actually Use in Fabric

Time Travel lets you query a table as it existed at a previous point in time or at a specific commit version. In a Spark notebook:

# Read the table as it was 7 days ago
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# By timestamp
df_historical = spark.read.format("delta") \
    .option("timestampAsOf", "2024-01-15 00:00:00") \
    .load("abfss://workspace-guid@onelake.dfs.fabric.microsoft.com/lakehouse-guid/Tables/orders")

# By version number
df_v5 = spark.read.format("delta") \
    .option("versionAsOf", "5") \
    .load("abfss://workspace-guid@onelake.dfs.fabric.microsoft.com/lakehouse-guid/Tables/orders")

You can also use T-SQL syntax through the SQL analytics endpoint for time travel, though the syntax differs slightly from what you might know from Databricks:

-- This works in the Fabric SQL analytics endpoint
SELECT TOP 1000 *
FROM orders
FOR TIMESTAMP AS OF '2024-01-15 00:00:00.000'

Schema Evolution is the ability to add new columns to a table without rewriting existing data. In Fabric Spark, you enable it like this:

df_new = spark.createDataFrame([
    (1001, "Widget A", 29.99, "EXPRESS"),  # new 'shipping_tier' column
], ["order_id", "product_name", "unit_price", "shipping_tier"])

df_new.write.format("delta") \
    .mode("append") \
    .option("mergeSchema", "true") \
    .save("abfss://workspace-guid@onelake.dfs.fabric.microsoft.com/lakehouse-guid/Tables/orders")

Existing rows will have NULL in the new column. Queries against old versions of the table via time travel will also show NULL for the column since it didn't exist then.

Z-Ordering is a data layout optimization that co-locates related values in the same files, dramatically speeding up range queries. For a sales table where you frequently filter by region and order_date:

# In a Spark notebook
spark.sql("""
    OPTIMIZE delta.`abfss://workspace-guid@onelake.dfs.fabric.microsoft.com/lakehouse-guid/Tables/orders`
    ZORDER BY (region, order_date)
""")

Z-ordering rewrites your data files to pack rows with similar region and order_date values together. A query filtering WHERE region = 'EMEA' AND order_date >= '2024-01-01' can then skip large swaths of the file set entirely, using the min/max statistics embedded in each file's Delta log entry. For a table with 500 million rows, the difference can be orders of magnitude.

---

The SQL Analytics Endpoint: T-SQL Without a Warehouse

Every Lakehouse in Fabric automatically provisions a SQL analytics endpoint — a read-only T-SQL interface over all the Delta tables in the Tables/ folder. This is not a traditional database engine with its own storage. It's a query translation layer that reads Delta files directly from OneLake and returns results.

This architecture has important implications:

-- This works immediately after a Lakehouse is created, no setup required
SELECT
    region,
    COUNT(*) AS order_count,
    SUM(order_total) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY region
ORDER BY revenue DESC;

The SQL endpoint uses the same Delta statistics that Spark writes when creating the table. If your Spark notebook wrote the table with proper statistics (which happens by default), the SQL endpoint benefits from file-level pruning automatically.

What the SQL analytics endpoint cannot do:

• Write data (it's strictly read-only; use Spark or Dataflow Gen2 for writes)
• Execute DML statements like INSERT, UPDATE, or DELETE
• Access files in the Files/ zone of the Lakehouse
• Execute stored procedures or create views that persist across sessions (though views can be created in the Lakehouse explorer and they do persist)

---

Shortcuts: Virtualizing Data Without Moving It

Shortcuts are arguably the most architecturally interesting feature in OneLake, and they're also the most misunderstood. Let's get precise about what they actually are.

A Shortcut is a symbolic link inside OneLake that points to data stored elsewhere — either in another location within OneLake, or in an external storage system like Azure Data Lake Storage Gen2, Amazon S3, or Google Cloud Storage. When Fabric follows a Shortcut to read data, the data does not move. The bytes travel from source to the compute engine directly, with OneLake acting as a transparent namespace.

Internal Shortcuts: Same Tenant, Different Workspace

The most common Shortcut scenario in enterprise Fabric deployments is sharing a certified, curated table from a central data domain into multiple consuming workspaces without duplication.

Imagine a central "Enterprise Data" workspace maintained by a data platform team. It contains a Lakehouse with a dim_customer table that's been carefully cleaned, deduped, and certified. Three different domain teams — Marketing, Finance, and Operations — all need this table.

Without Shortcuts, each team would either:

1. Copy the data into their own Lakehouse (creating drift and maintenance burden), or
2. Query cross-workspace using long connection strings (creating tight coupling and access management headaches)

With Shortcuts, each domain Lakehouse can create a Shortcut that points to dim_customer in the Enterprise Lakehouse. From the Marketing team's perspective, the table appears to live inside their Lakehouse. Their Spark notebooks, SQL queries, and Power BI reports reference it like any other local table. When the data platform team updates dim_customer, every consumer sees the update instantly — there's only one copy.

To create an internal Shortcut in the Fabric UI, navigate to your target Lakehouse, open the "..." menu on the Tables folder or Files folder, select "New shortcut," choose "Microsoft OneLake" as the source, then browse to the workspace and Lakehouse that contains your source table. Select the specific table folder (e.g., Tables/dim_customer) and confirm.

You can also create shortcuts programmatically using the Fabric REST API:

import requests
import json

# Using a service principal or user token
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}

shortcut_payload = {
    "path": "Tables/dim_customer",
    "name": "dim_customer",
    "target": {
        "oneLake": {
            "workspaceId": "source-workspace-guid",
            "itemId": "source-lakehouse-guid",
            "path": "Tables/dim_customer"
        }
    }
}

response = requests.post(
    f"https://api.fabric.microsoft.com/v1/workspaces/{target_workspace_id}/items/{target_lakehouse_id}/shortcuts",
    headers=headers,
    data=json.dumps(shortcut_payload)
)

External Shortcuts: S3, ADLS, and GCS

External Shortcuts extend the same virtualization principle to data that lives outside Fabric entirely. This is the migration path Microsoft is positioning for organizations that want Fabric's compute capabilities without a big-bang data migration.

ADLS Gen2 Shortcuts are the most mature. If your organization has data in Azure Data Lake Storage Gen2, you can create a Shortcut in a Fabric Lakehouse that points to a container or folder in that storage account. Fabric authenticates using one of three methods: organizational account (user OAuth), service principal, or account key. The organizational account option is the most secure for interactive use; service principal is the right choice for automation.

Shortcut configuration for ADLS Gen2:
  URL: https://mycompany.dfs.core.windows.net/
  Connection: [select or create a connection credential]
  Path: raw-data/sales/transactions/

Once the shortcut is created, files in that ADLS path appear inside your Lakehouse Files/ folder. If those files happen to be a valid Delta table (they have a _delta_log), you can move the Shortcut to point to Tables/ and Fabric will treat it as a registered table.

Amazon S3 Shortcuts work similarly but come with one additional complexity: Fabric accesses S3 through a managed VNet integration, and the S3 bucket needs to have a bucket policy that allows access from Fabric's managed IP ranges. The authentication options for S3 are access key/secret key, or an IAM role trust policy that Fabric's managed identity can assume.

Google Cloud Storage Shortcuts follow a similar pattern to S3, using GCS HMAC keys for authentication. GCS shortcuts are available but tend to be less commonly used in practice and have somewhat lower first-party support in terms of documentation.

What Shortcuts Can't Do

This is where most teams hit friction when they design their OneLake architecture:

1. Shortcuts don't transform data. If the source data is CSV, it appears as CSV through the Shortcut. You can't apply a schema or filter through the Shortcut definition itself. You need a separate ingestion step (Dataflow Gen2, a pipeline, or a Spark notebook) to land clean data.

2. External Shortcuts don't inherit Fabric's ACID guarantees. If an external process is writing to the ADLS Gen2 path while Fabric is reading it through a Shortcut, you have no transaction isolation. A Shortcut to an ADLS path sees the raw file system, including partially written files.

3. Shortcuts can't cross tenant boundaries in all scenarios. Internal OneLake Shortcuts work across workspaces in the same tenant. Shortcuts to external storage obviously can span tenants, but direct OneLake-to-OneLake Shortcuts across different Fabric tenants are not natively supported. For cross-tenant data sharing, you'd use the external storage mechanism (e.g., both tenants point to the same ADLS account).

4. Row-level security on source tables doesn't automatically propagate through Shortcuts. If dim_customer has row-level security in the source Lakehouse, a consumer accessing it via Shortcut does not automatically inherit that security. The Shortcut respects file system permissions on OneLake, but not the semantic-layer security policies.

---

OneLake File Explorer: Direct Access from Windows

Microsoft ships a Windows application called OneLake File Explorer that mounts your OneLake as a mapped drive in Windows Explorer. For data engineers and analysts who prefer to work with files directly — dragging CSVs into the Files/ zone, inspecting Parquet file layouts, or running Python scripts against local paths — this is genuinely useful.

The mount shows your workspace hierarchy exactly as it exists in OneLake. You can copy a file into a Lakehouse's Files/ folder from your local machine, and it appears in the Fabric UI within seconds. Under the hood, this uses the WebDAV protocol over ADLS Gen2 endpoints.

Where it gets interesting for advanced users: because the local path is just a virtual mount, you can point any POSIX-compatible tool that works with Windows paths at OneLake data. A Python script using pandas.read_parquet("C:\\Users\\YourName\\OneLake - Contoso\\Sales Analytics\\SalesLakehouse.Lakehouse\\Tables\\orders") will stream data from OneLake as if it were reading a local file. The performance is bounded by your internet connection, so this pattern makes sense for exploratory work, not production batch processing.

---

Data Residency, Replication, and the Cost Model

Let's talk about the things the marketing materials gloss over.

Where Data Actually Lives

OneLake data lives in the Azure region where your Fabric tenant is homed. By default, every workspace in your tenant uses the same OneLake in the same region. This has two consequences:

First, compute in a workspace is not always co-located with the data it reads. If your Fabric capacity is in East US and your OneLake data is in West Europe (because your tenant is homed there), every Spark job in East US reads data across Azure regions. This incurs both latency and egress costs.

Second, for compliance scenarios requiring data residency (GDPR, HIPAA, financial regulations), the single-region default may not be acceptable. Microsoft's multi-geo feature for Fabric allows you to designate specific workspaces as residing in a different region from the tenant home. But Multi-Geo means data replication, which means cost and potential consistency windows.

Storage Costs

OneLake storage is billed based on consumed capacity in GB-months, separate from the F-SKU compute cost. As of the time of writing, OneLake storage is billed at rates comparable to ADLS Gen2 hot tier. This means:

• Data you load once and read frequently is cheap
• Data you write repeatedly (lots of Delta commits, lots of small files) can generate surprising storage costs from orphaned Parquet files that aren't yet cleaned up by VACUUM
• Shortcuts to external storage don't add OneLake storage costs (the data stays in the external system), but you do pay egress costs when reading data through external shortcuts

VACUUM and Storage Management

Delta's copy-on-write model means that every UPDATE, DELETE, or MERGE operation leaves orphaned Parquet files behind — the old versions of changed data. These files are kept to support time travel, but they consume storage and cost money.

The VACUUM command deletes files that are older than a specified retention threshold:

# In a Spark notebook
spark.sql("""
    VACUUM delta.`abfss://workspace-guid@onelake.dfs.fabric.microsoft.com/lakehouse-guid/Tables/orders`
    RETAIN 168 HOURS  -- 7 days retention for time travel
""")

---

Designing Your OneLake Topology

Given everything above, what does a well-designed OneLake architecture actually look like? Here are the patterns that experienced Fabric architects converge on:

Pattern 1: Domain-Aligned Lakehouses with a Central Catalog Lakehouse

Create one Lakehouse per data domain (Sales, Finance, HR, Operations). Designate one "Enterprise" Lakehouse as the source of truth for shared dimension tables. Domain Lakehouses use Shortcuts to consume Enterprise dimensions rather than copying them.

Enterprise Lakehouse (owned by Data Platform team)
├── Tables/
│   ├── dim_customer/
│   ├── dim_product/
│   ├── dim_date/
│   └── dim_geography/

Sales Lakehouse (owned by Sales Analytics team)
├── Tables/
│   ├── fact_orders/ (written by Sales pipelines)
│   ├── fact_returns/ (written by Sales pipelines)
│   ├── dim_customer -> [SHORTCUT to Enterprise/dim_customer]
│   ├── dim_product -> [SHORTCUT to Enterprise/dim_product]
│   └── dim_date -> [SHORTCUT to Enterprise/dim_date]

This pattern gives each team autonomy over their fact data while ensuring dimension tables are authoritative and up-to-date everywhere.

Pattern 2: Medallion Architecture Within a Single Lakehouse

Bronze, Silver, Gold layers all within one Lakehouse, separated by folder naming conventions inside Files/ (for Bronze raw data) and Tables/ (for Silver and Gold Delta tables):

DataPlatformLakehouse
├── Files/
│   └── bronze/
│       ├── sales_api_raw/     (JSON responses from Sales API)
│       └── erp_export/        (CSV dumps from ERP system)
└── Tables/
    ├── silver_orders/         (cleaned, validated Delta table)
    ├── silver_customers/      (deduplicated, canonicalized)
    ├── gold_revenue_daily/    (pre-aggregated business metric)
    └── gold_customer_360/     (joined, enriched)

The single-Lakehouse medallion pattern works well for small to medium teams. It simplifies access control (one item to grant access to) but creates tighter coupling between layers and makes it harder to enforce "Bronze is read-only to everyone except the ingestion pipeline" policies.

Pattern 3: Separate Lakehouses per Medallion Layer

More operationally complex but allows fine-grained access control:

BronzeLakehouse (only ingestion service principal has write access)
SilverLakehouse (data engineers have write access; analysts have read via SQL endpoint)
GoldLakehouse (data product owners write here; BI tools, Power BI connect here)

Shortcuts flow directionally: Silver Lakehouse shortcuts to Bronze for raw data, Gold Lakehouse shortcuts to Silver for refined data. No data is physically copied — the compute engine reads across Shortcut boundaries transparently.

---

Hands-On Exercise

In this exercise you'll create a Lakehouse, write a Delta table with realistic structure, create a Shortcut pointing to that table from a second Lakehouse, and verify that both Lakehouses see the same data.

Step 1: Create Two Lakehouses

In your Fabric workspace, create two Lakehouses:

• SourceLakehouse (this will be the authoritative data store)
• ConsumerLakehouse (this will consume data via Shortcut)

Step 2: Write a Delta Table to SourceLakehouse

Open a new Spark notebook in your workspace and attach it to the SourceLakehouse. Run the following:

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, DoubleType, DateType, IntegerType
from datetime import date

spark = SparkSession.builder.getOrCreate()

schema = StructType([
    StructField("transaction_id", StringType(), False),
    StructField("customer_id", IntegerType(), False),
    StructField("product_sku", StringType(), False),
    StructField("region", StringType(), True),
    StructField("order_date", DateType(), False),
    StructField("unit_price", DoubleType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("revenue", DoubleType(), False)
])

data = [
    ("TXN-001", 10042, "WIDGET-A", "EMEA", date(2024, 1, 15), 29.99, 3, 89.97),
    ("TXN-002", 10089, "GADGET-B", "AMER", date(2024, 1, 16), 149.00, 1, 149.00),
    ("TXN-003", 10042, "WIDGET-A", "EMEA", date(2024, 1, 17), 29.99, 5, 149.95),
    ("TXN-004", 10203, "SERVICE-C", "APAC", date(2024, 1, 18), 499.00, 2, 998.00),
    ("TXN-005", 10089, "WIDGET-A", "AMER", date(2024, 1, 19), 29.99, 10, 299.90),
]

df = spark.createDataFrame(data, schema)

# Write to the Tables/ zone - this registers automatically as a queryable table
df.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .partitionBy("region") \
    .saveAsTable("transactions")

print("Table written. Checking Delta log...")

# Verify the Delta log exists
from pyspark.sql.functions import col
history_df = spark.sql("DESCRIBE HISTORY transactions")
history_df.select("version", "timestamp", "operation").show(truncate=False)

Step 3: Verify Table Appears in SQL Endpoint

Open the SourceLakehouse in Fabric, navigate to the SQL analytics endpoint view (toggle in the top-right corner), and run:

SELECT
    region,
    COUNT(*) as transaction_count,
    SUM(revenue) as total_revenue
FROM transactions
GROUP BY region
ORDER BY total_revenue DESC;

You should see results grouped by EMEA, AMER, and APAC.

Step 4: Create a Shortcut in ConsumerLakehouse

Open ConsumerLakehouse. In the left panel, hover over the "Tables" folder and click the "..." menu. Select "New shortcut." In the dialog:

• Choose "Microsoft OneLake" as the source
• Select your workspace from the dropdown
• Select "SourceLakehouse" as the item
• Navigate to Tables/transactions
• Accept the default name and click Create

Step 5: Verify the Shortcut Works

Back in ConsumerLakehouse, switch to the SQL analytics endpoint and run:

-- This is running against ConsumerLakehouse but reading SourceLakehouse data
SELECT
    product_sku,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM transactions
GROUP BY product_sku;

Step 6: Add Data Through Source and See It in Consumer

Return to your Spark notebook (still pointed at SourceLakehouse) and append a new row:

new_data = [
    ("TXN-006", 10350, "GADGET-B", "EMEA", date(2024, 1, 20), 149.00, 4, 596.00),
]

df_new = spark.createDataFrame(new_data, schema)
df_new.write.format("delta") \
    .mode("append") \
    .saveAsTable("transactions")

print("Appended 1 row. Version should now be 1.")
spark.sql("DESCRIBE HISTORY transactions").select("version", "timestamp", "operation").show()

Now rerun the SQL query against ConsumerLakehouse. The new row appears immediately — no refresh, no sync, no copy. This is the OneLake promise in action.

---

Common Mistakes & Troubleshooting

Mistake 1: Writing Small Files and Never Running OPTIMIZE

Every Spark job that appends to a Delta table creates new Parquet files — one per Spark partition. If you're running hourly micro-batch jobs and your DataFrame has 200 partitions, you'll generate 200 new Parquet files per hour. After a month, that's 144,000 files in the table folder.

This kills query performance because both the Delta log reader and the Parquet scanner have to open and process many small files. The fix is OPTIMIZE:

spark.sql("OPTIMIZE transactions")
# Or with Z-ordering:
spark.sql("OPTIMIZE transactions ZORDER BY (customer_id, order_date)")

Schedule this as a weekly or daily maintenance notebook. For tables receiving continuous micro-batch writes, consider using AUTO OPTIMIZE via the Delta table property:

spark.sql("""
    ALTER TABLE transactions
    SET TBLPROPERTIES (
        'delta.autoOptimize.optimizeWrite' = 'true',
        'delta.autoOptimize.autoCompact' = 'true'
    )
""")

Mistake 2: Creating Shortcuts to the Wrong Path Level

If you create a Shortcut that points to the parent of a Delta table folder rather than the Delta table folder itself, Fabric won't recognize it as a table. For example, if your shortcut points to Tables/ instead of Tables/dim_customer, you get a folder full of subfolders, not a queryable table.

Conversely, if you point a shortcut to Tables/dim_customer/_delta_log — which seems logical if you're thinking "I need the transaction log" — you'll get an empty folder view because you're pointing at the metadata subdirectory, not the table root.

The correct path for a Shortcut to a Delta table is the root folder of the table: Tables/dim_customer.

Mistake 3: Assuming Schema Enforcement Is Automatic

Delta tables enforce schema by default — if you try to write a DataFrame with different column types than the existing table schema, the write will fail with a schema mismatch error. But "enforcement" only applies to writes through the Delta writer. If you drop a raw file into the Tables/ directory that doesn't conform to the table's schema, Delta won't stop you. The file just won't appear in any read because it's not referenced in the Delta log.

This means you can end up with orphaned files in your table folder that consume storage but aren't accessible. They'll never be read, but they also won't be cleaned up by VACUUM since VACUUM only removes files that were once referenced in the log and then removed. Truly orphaned files require manual cleanup.

Mistake 4: Confusing the SQL Endpoint Connection with Direct Lake

When you connect Power BI to a Lakehouse using "Direct Lake" mode, you are not connecting to the SQL analytics endpoint. Direct Lake is a first-party Power BI engine mode that reads Delta files from OneLake directly, with its own internal read path. The SQL endpoint connection string (which looks like <guid>.datawarehouse.fabric.microsoft.com) is for external SQL tools — SSMS, DBeaver, Azure Data Studio, Power BI import mode, etc.

If your Power BI report is connecting to the SQL endpoint, you're in Import or DirectQuery mode, not Direct Lake. The performance characteristics are dramatically different. Direct Lake is the mode that enables Power BI to operate at the speed of Analysis Services against petabyte-scale Delta tables.

Mistake 5: Treating External Shortcuts as Guaranteed Fresh Data

Shortcuts to ADLS Gen2 or S3 show you whatever is in that external storage at query time. If an external process has partially written a new version of a file when your Spark job reads through the Shortcut, you may read incomplete data. There's no consistency boundary at the Shortcut level for external targets.

For external data sources where you need consistency guarantees, the right pattern is: use the Shortcut for initial landing, then run a Spark pipeline that reads from the Shortcut and writes to a managed Delta table in the Tables/ zone. The managed table gets full ACID guarantees; the Shortcut is just the ingestion path.

---

Summary & Next Steps

OneLake is more than a storage account with a nice UI. It's a principled architectural decision to make data gravity work in your favor: bring compute to a single copy of data, rather than moving data to every compute system that needs it.

The key ideas to carry forward:

• OneLake is hierarchical and automatic. Every Fabric item gets a folder in OneLake. The Files/ vs Tables/ boundary determines what's queryable via T-SQL without registration.

• Delta Lake is the lingua franca. The transaction log is the source of truth for what files are in a table, what changed, and when. Understanding the log internals — commits, checkpoints, stats — is the foundation for debugging, optimization, and time travel.

• Shortcuts virtualize without copying. Internal Shortcuts enable data mesh-style domain architectures where dimension tables are authoritative in one place but appear natively in every domain. External Shortcuts enable lift-and-shift migration scenarios where data stays in ADLS or S3 but is queryable through Fabric.

• Operations matter. OPTIMIZE, VACUUM, ZORDER aren't optional finishing touches — they're ongoing maintenance that determines whether your tables stay fast and cost-efficient at scale.

• The SQL endpoint, Spark, and Direct Lake are different access paths. Know which one your tools are using and design accordingly.

Your natural next areas to explore are how data gets into OneLake at scale — Dataflow Gen2 for business-user-friendly ETL, and Data Pipelines for orchestrated ingestion with dependencies and error handling. You'll also want to go deep on how Power BI's Direct Lake mode reads these Delta tables and what semantic model design decisions unlock its full performance potential.