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:

CREATE EXTERNAL TABLE ExternalProductData (
    ProductID INT,
    Name NVARCHAR(100)
)
WITH (
    LOCATION = '/data/products/',
    DATA_SOURCE = MyHadoopDataSource,
    FILE_FORMAT = ParquetFormat
);

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 with CREATE 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 regular CREATE TABLE, with the EXTERNAL keyword and the WITH (...) clause both commented using /* SQLTRAN FIX */ annotations.

  • CREATE EXTERNAL DATA SOURCE and CREATE 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 or DROP 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:

CREATE EXTERNAL TABLE dbo.ExternalSales (
    OrderID INT,
    Region VARCHAR(50),
    TotalAmount FLOAT
)
WITH (
    LOCATION = 'https://mystorage.blob.core.windows.net/sales/2023/',
    DATA_SOURCE = MyExternalDataSource,
    FILE_FORMAT = CsvFormat
);

Fabric Warehouse (generated by SQL Tran):

CREATE /* SQLTRAN FIX: EXTERNAL */ TABLE dbo.ExternalSales (
    OrderID INT,
    Region VARCHAR(50),
    TotalAmount FLOAT
)
/* SQLTRAN FIX: WITH (
    LOCATION = 'https://mystorage.blob.core.windows.net/sales/2023/',
    DATA_SOURCE = MyExternalDataSource,
    FILE_FORMAT = CsvFormat
) */;
External table emulation in 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, or EXTERNAL FILE FORMAT.

  • Diagnostic comments emitted: SQL Tran adds a warning comment to CREATE EXTERNAL DATA SOURCE and CREATE 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 or DROP 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, or OPENROWSET with CTAS. All logic involving unsupported constructs like CREATE EXTERNAL TABLE, DATA SOURCE, FILE FORMAT, and CETAS 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.

  • Data migration with Omni Loader: When SQL Tran does not emit ingestion logic for external tables, Spectral Core’s high-performance migration tool Omni Loader provides a robust solution. It enables the migration of external tables from SQL Server or Synapse Analytics into Microsoft Fabric by converting them into standard Fabric tables, exporting data via PolyBase for high-throughput performance, and automating Parquet-based staging with ADLS Gen2. Data can be moved using either PolyBase, which offers excellent performance but limited progress visibility, or Omni Loader’s internal engine, which provides full progress tracking and operational control. The staging process supports optional compression and slicing for parallelism, and can trigger ingestion using COPY INTO or OPENROWSET with CREATE TABLE AS SELECT. Omni Loader is fully configurable, supports large-scale parallel transfers, and requires no installation on the source system. It also includes advanced features such as schema mapping, blob extraction, and clustered parallel execution, making it an ideal choice for enterprise-grade external table migration workflows.

PreviousTemporary tablesNextIdentity columns

Last updated