Simplifying file access in OPENROWSET using data sources and relative paths (Preview)
The introduction of data sources and relative paths in the OPENROWSET function unlocks a new level of simplicity and flexibility for querying files in your Lakehouse and beyond. By leveraging external data sources, you can write cleaner, more maintainable SQL queries and easily access data wherever it resides — making data engineering and analytics more efficient than ever.
Access Lakehouse files via SQL Endpoint
You can use the OPENROWSET function in SQL analytical endpoints to access the files in your Lakehouse using the relative paths. Instead of referencing the files with long, absolute, GUID-based paths, you can simply specify the file path relative to the Lakehouse’s root folder.
In the following example, we can see how to query the Lakehouse file using the OPENROWSET function with a relative path in a T-SQL notebook:
This notebook uses SQL endpoint for Lakehouse to retrieve the content of sales.csv file located in the data/2025/09 folder relative to the root of your Lakehouse. Since the SQL endpoint is bound to its own Lakehouse, it can directly reference the files by relative location within the Lakehouse.
This not only makes your queries easier to read and maintain, but also allows you to avoid hardcoding absolute paths, which can be error-prone and cumbersome.
Cross-Lakehouse access made easy
When you access the files in OneLake using the OPENROWSET function, you need to find the workspace id and lakehouse id of your source Lakehouse and use them as part of the URI in the OPENROWSET function. Use a GUID-based URI (with workspace and lakehouse ids), in every OPENROWSET and COPY INTO query to access the files in the Lakehouses.
The external data sources might slightly simplify this coding experience by enabling you to define GUID-based root URI once in an external data source, and reference it by name. For example, you create an external data source named MyLakehouse that points to a Lakehouse root path with a GUID using the following T-SQL statement:
CREATE EXTERNAL DATA SOURCE MyLakehouse
WITH ( LOCATION = 'https://onelake.dfs.fabric.microsoft.com/{wsid}/{lhid}' );
In this statement, replace {wsid} and {lhid} with the specific workspace and Lakehouse identifiers that you can find in URI when you access the Lakehouse via browser:
Once this external data source is defined, you can use it in your OPENROWSET queries with relative paths to reference other Lakehouse locations:
This approach greatly simplifies data integration and analysis across multiple lakehouses since you can define the GUID-based root location once and access the files by referencing the Lakehouse by name instead of the full GUID-based path.
Referencing ADLS locations with Data Sources
You can also use external data sources to reference the root location of any external data source, such as remote Azure Data Lake Storage (ADLS) accounts. In the following example you can see how to create an external data source that references the root location of ADLS container:
CREATE EXTERNAL DATA SOURCE MyAdls
WITH ( LOCATION = 'abfss://{mycontainer}@{mystorage}.dfs.core.windows.net' );
Once the external data source is defined, you simply use the OPENROWSET function with a relative path to query files stored remotely:
SELECT * FROM OPENROWSET( BULK '/Files/data/2025/0 9/sales.csv', DATA_SOURCE = 'MyAdls')
This means you can seamlessly integrate and analyze data from a variety of external locations using straightforward, readable SQL queries—no more dealing with unwieldy absolute URIs every time you reference a remote file.
Conclusion: Easier Queries, Greater Flexibility
These features are currently in preview as we continue to enhance the Fabric user interface, which will enable you to take even greater advantage of them. Try them out and let us know what you think in the comments.
