External tables
Emulation context
In Azure Synapse Analytics (dedicated SQL pools), external tables allow users to define structured views over data stored outside the data warehouse — such as in Azure Data Lake Storage Gen2, Azure Blob Storage, or other Hadoop-compatible sources. These tables are defined using a combination of the following statements:
CREATE EXTERNAL DATA SOURCE
CREATE EXTERNAL FILE FORMAT
CREATE EXTERNAL TABLE
CREATE EXTERNAL TABLE AS SELECT
(CETAS)
External tables in Synapse can be queried like regular tables, enabling schema-on-read analytics and staging workflows without first ingesting data into the warehouse.
For example:
However, Microsoft Fabric Warehouse does not support external tables. There is no engine-level support for EXTERNAL
keywords or metadata references such as LOCATION
, DATA_SOURCE
, or FILE_FORMAT
. Instead, Fabric requires external data to be ingested into the warehouse using supported mechanisms like:
COPY INTO
(T-SQL)Data pipelines
Dataflows
OPENROWSET
withCREATE TABLE AS SELECT
As a result, external table definitions from Synapse must be rewritten or replaced during migration to Fabric.
Emulation strategy
SQL Tran handles external table constructs by retaining their structure in the translated output and annotating them with diagnostic comments to guide manual replacement using Fabric-compatible ingestion methods. Specifically:
CREATE EXTERNAL TABLE
is translated as a regularCREATE TABLE
, with theEXTERNAL
keyword and theWITH (...)
clause both commented using/* SQLTRAN FIX */
annotations.CREATE EXTERNAL DATA SOURCE
andCREATE EXTERNAL FILE FORMAT
are passed through unchanged but annotated with a diagnostic comment.CREATE EXTERNAL TABLE AS SELECT
(CETAS) statements are not supported by SQL Tran and must be removed or rewritten using Fabric-compatible ingestion logic.SELECT
orDROP
statements that reference external tables are retained without warnings. These may fail at runtime if the referenced external table is not recreated in Fabric.
SQL Tran does not convert external table definitions into COPY INTO
, CTAS
, or other Fabric-compatible ingestion logic, nor does it restructure external metadata such as data sources or file formats. All external data access logic must be manually refactored to align with Fabric’s supported ingestion patterns.
Code example
Synapse Analytics:
Fabric Warehouse (generated by SQL Tran):
Important notes
Not supported in Fabric: External tables are not supported by Microsoft Fabric. There is no Fabric equivalent to
CREATE EXTERNAL TABLE
,EXTERNAL DATA SOURCE
, orEXTERNAL FILE FORMAT
.Diagnostic comments emitted: SQL Tran adds a warning comment to
CREATE EXTERNAL DATA SOURCE
andCREATE EXTERNAL FILE FORMAT
statements.CETAS not supported:
CREATE EXTERNAL TABLE AS SELECT
(CETAS) statements are not recognized by SQL Tran and must be manually rewritten using Fabric-compatible ingestion logic.No linkage preserved: The external metadata (e.g.,
LOCATION
,DATA_SOURCE
,FILE_FORMAT
) is retained in comments but not transformed. Data connectivity must be re-established manually.Table references are retained: References to external tables in
SELECT
orDROP
statements are retained and not flagged by SQL Tran. These may fail at runtime if not removed or replaced.Manual ingestion and review required: SQL Tran does not generate ingestion logic. External data must be loaded into Fabric manually using supported mechanisms such as
COPY INTO
(T-SQL), pipelines, dataflows, orOPENROWSET
withCTAS
. All logic involving unsupported constructs likeCREATE EXTERNAL TABLE
,DATA SOURCE
,FILE FORMAT
, andCETAS
must be reviewed, reimplemented, and validated for correctness using Fabric-compatible tools. For streamlined, enterprise-ready ingestion, Omni Loader offers a robust solution to automate the migration of external tables into Fabric.
Last updated