Unified Data Access Pattern Across Fabric

Have you ever seen a situation when in an ETL pipeline engineers create a not strictly necessary dataset only because they can use a tool such as a Spark or SQL notebook to process data in that dataset? I have and I don't think this is a best practice when data is replicated just to satisfy a tool selection preference. In this post I'll explain how Microsoft Fabric solves this problem by providing a unified way to access data in various formats by using Lakehouse table shortcuts.

Storage Choice Should Not Dictate the Exploration Tool

What is a Lakehouse table shortcut? You can read the official definition here(https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts), but a short explanation is they are very similar to Windows file shortcuts and allow accessing various tabular data sources in Fabric in a universal way as if they were Lakehouse delta tables.

Just imagine sources like SQL database or Warehouse tables and KQL tables, which all require different native tools to work with. A table shortcut makes various sources stored on OneLake in Delta format appear in a Lakehouse as delta tables and immediately accessible from a Spark Notebook or a SQL script via a Lakehouse SQL endpoint.

This is not a magic trick, and a table shortcut does not copy data behind the scene. When you create a table shortcut in a Lakehouse data is not duplicated but instead a new reading path becomes available through a Lakehouse API and associated SQL endpoint. A table shortcut looks like a Lakehouse delta table and can be accessed with the same tools, though it still points to the source data, and you can use your familiar tool of choice to work with that data source.

The Tree Decision Criteria

Now that you understand the concept of the Table shortcuts, how do you decide if you want to use a Lakehouse table shortcut for a particular data source that you need to explore? I start from answering these three questions:

1. Is the data source format and location supported by a table shortcut?

2. Will a Spark or T-SQL Notebook be a sensible choice for this data source exploration scenario?

3. Are there any hard road blocks such as permissions, latency, or performance for using a table shortcut?

If I pass on all three questions my choice is clear: create a table shortcut and start exploring the data.
Now let's talk about a few scenarios where a Lakehouse table shortcut could be a useful choice.

1. SQL Database and Warehouse

From the first glance it's not obvious why you would want to use a table shortcut for a SQL database or a Warehouse. Both already support exploration with T-SQL, so the shortcut has to earn its place.
For me, the strongest reason is that the work needs something that T-SQL does not do well, usually Python-based exploration or processing. Reusing existing PySpark code or working with the data as DataFrames next to other Lakehouse tables fall in the same category.

Why I Use It

• Explore the data with PySpark without creating a duplicate table first.

• Profile or transform the data before deciding what a curated result should look like.

Where It Shines

• Exploratory analysis in Python.
• Data-quality checks that are easier to express in code.
• Data shaping for a larger Spark workflow.
• Complex joins and transformations that combine SQL source data with native Lakehouse tables in Spark.

What to Keep in Mind

• A SQL database and a Warehouse are not the same kind of source. A SQL database is transactional, 

  but its data is mirrored to OneLake as Delta files, so analytics reads against the mirrored copy don't load the primary workload. A Warehouse is built for analytical queries, so that concern is even smaller.

• Treat shortcut-backed access as read-only for this pattern.
• Shortcut behavior may change, so confirm against current documentation.

2. Mirrored SQL Database

A mirrored SQL database can also be queried with T-SQL via a SQL Endpoint, so it could belong in the first scenario. But I give it its own section because its role is different. A mirror is a projection of an external data source into OneLake not the actual data source such as SQL database or a Warehouse. This distinction makes it closer to a raw data landing area that requires ingestion first.

This is where using a shortcut gets exciting. A Lakehouse shortcut to the mirrored tables lets you read the data without running any ingestion workload. The shortcut removes the copy operations and brings the data into a bronze layer, from where it can be further cleaned or transformed as needed.

Why I Use It

• Enable Spark and T-SQL to read replicated source data without another ingestion job.
• Use the mirror as a starting point for initial data exploration.

Where It Shines

• Early exploration of source data before the medallion design is settled.
• Checking data quality against the source before any transformation.
• Supplying data for a Silver layer for transformation without an initial ingestion.

What to Keep in Mind

• A shortcut saves the copy operation, but downstream may still require their own tables.

• The mirror reflects the current state of the source. Delta history is kept only for the retention period, which defaults to one day for newer mirrored databases, so materialize snapshots if you need longer history.

• Replication is near real time, but latency depends on the source, region, and change volume. Check the current figures before promising freshness.

3. Lakehouse: The Simplest Data Sharing Case

A Lakehouse-to-Lakehouse shortcut is the easiest case. The team owning the data keeps the table where it is, and another team creates a table shortcut in their own Lakehouse. Both teams work with the same Delta table without creating a copy.

Why I Use It

• Keep a table in the Lakehouse that owns it while another team uses it.
• Join Delta tables from different Lakehouses via shortcuts in Spark or through the SQL analytics endpoint with simplified syntax.

Where It Shines

• Cross-workspace analytics, where a central team needs a domain's curated table.
• Domain-oriented ownership, where each team publishes its tables and others consume them.

What to Keep in Mind

• A shortcut doesn't grant access. The calling user needs read permission on the target table.
• Schema changes sync automatically, but timing can vary. A new shortcut may take time to show up.

• Deleting a shortcut leaves the target untouched.

• Shortcut names with spaces aren't recognized as Delta tables.

• The workspace lineage view only shows shortcuts within the same workspace.

4. KQL Database: Same Goal, Different Mechanics

KQL Database in an Eventhouse isn't just another Delta table shortcut. Data is stored in the KQL database, not OneLake. To make a KQL table visible to other Fabric engines, you turn on OneLake availability, which creates a Delta copy of that table in OneLake. The platform maintains that copy, so this path isn't zero-copy the way a Lakehouse shortcut is but the result is similar: a table shortcut for that KQL table can be created in a Lakehouse.

Why I Use It

• Keep ingestion in the KQL database but expose the same data in the Lakehouse, without building a second ingestion job.
• Explore the table in the Lakehouse with Spark or T-SQL, the same way you explore any other Delta table.

Where It Shines

• Analysts who work in Spark or T-SQL can read the time-series data without KQL database access, when the shortcut uses a delegated identity.
• Power BI reports in Direct Lake mode can read the time-series data from the Lakehouse.

What to Keep In Mind

• The Delta copy is a managed replica of the KQL table and there is always a delay, so this isn't a real-time view.
• If OneLake availability is turned off and back on the replica is removed and rebuilt. During this period data is not available via a shortcut.

• The access model is different from other sources because additional permissions are needed on the KQL database.

To learn more about OneLake availability for KQL check out this article.

When I Would Still Create Another Dataset

A shortcut is my preference, not a rule. I still create a separate dataset when the data is filtered, transformed and enriched and not an exact copy. Additional considerations may include performance optimization, data isolation and governance.

Final Thoughts

Table shortcuts in Fabric are extremely powerful and may enable many scenarios with unexpected data access paths. While it's important to manage your data redundancy and eliminate unnecessary copy operations the final architecture of your data estate may be much more complex than a simple choice of using a table shortcut.

I am curios, where in your Fabric estate are you duplicating data mainly to change tools, and what would make a shortcut-based access path a better choice?

Drop your thoughts and comments below.