External tables
Emulation context
In SQL Server, external tables are used to define schema over externally stored data — such as files in Azure Data Lake Storage Gen2, Azure Blob Storage, or other Hadoop-compatible storage systems. They enable querying data without ingesting it into the warehouse and are typically defined using:
CREATE EXTERNAL DATA SOURCE
CREATE EXTERNAL FILE FORMAT
CREATE EXTERNAL TABLE
CREATE EXTERNAL TABLE AS SELECT
(CETAS)
These external tables allow schema-on-read, staging, or virtualized access to external datasets and are tightly coupled with external metadata definitions.
For example:
However, Microsoft Fabric Warehouse does not support external tables. There is no support in Fabric SQL for EXTERNAL
, DATA_SOURCE
, FILE_FORMAT
, or LOCATION
keywords. Fabric requires external data to be ingested using supported mechanisms such as:
COPY INTO
(T-SQL)Pipelines (Dataflows, Data Factory)
OPENROWSET ... WITH (...) CREATE TABLE AS SELECT
(CTAS)
As a result, all such constructs must be rewritten or manually replaced during migration.
Emulation strategy
As of the current version, SQL Tran supports external table constructs by preserving their structure and inserting diagnostic comments. The following behavior has been confirmed:
CREATE EXTERNAL TABLE
statements are translated into regularCREATE TABLE
definitions. TheEXTERNAL
keyword is commented out using/* SQLTRAN FIX: EXTERNAL */
, and the entireWITH (...)
clause — includingLOCATION
,DATA_SOURCE
,FILE_FORMAT
, and related metadata — is commented out using/* SQLTRAN FIX */
for traceability.CREATE EXTERNAL DATA SOURCE
andCREATE EXTERNAL FILE FORMAT
statements are passed through without structural transformation but trigger diagnostic comments for manual review.CREATE EXTERNAL TABLE AS SELECT
(CETAS) statements are not supported by SQL Tran. These are not translated or annotated and must be rewritten manually using Fabric-supported ingestion methods.SELECT
andDROP
statements that reference external tables are preserved without modification or diagnostic comments. These may still fail at runtime in Fabric if the external table is not recreated using supported ingestion methods.No Fabric-compatible ingestion logic is generated by SQL Tran to replace external table functionality. No
COPY INTO
,OPENROWSET
, orCTAS
statements are produced. All ingestion-related logic must be manually rewritten using Fabric-supported tools.
Code example
SQL Server:
Fabric Warehouse (generated by SQL Tran):
Important notes
External tables not supported in Fabric: Fabric has no support for querying data via
CREATE EXTERNAL TABLE
or its associated metadata objects.SQL Tran annotates unsupported metadata: External metadata such as
DATA_SOURCE
,LOCATION
, andFILE_FORMAT
is retained in the translated script but fully commented out using/* SQLTRAN FIX */
.CETAS unsupported:
CREATE EXTERNAL TABLE AS SELECT
statements are not recognized by SQL Tran and must be replaced manually.External metadata declarations not implemented:
CREATE EXTERNAL DATA SOURCE
andCREATE EXTERNAL FILE FORMAT
are passed through but trigger diagnostic messages and require manual resolution.Manual ingestion and review required: SQL Tran does not generate
COPY INTO
,OPENROWSET
, or any Fabric-compatible ingestion logic for external tables. All external table usage must be reviewed, refactored, and manually reimplemented using supported Fabric ingestion mechanisms such as pipelines, T-SQLCOPY
, orCREATE TABLE AS SELECT
viaOPENROWSET
. For streamlined, enterprise-ready ingestion, Omni Loader offers a robust solution to automate the migration of external tables into Fabric.
Last updated