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:

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 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 regular CREATE TABLE definitions. The EXTERNAL keyword is commented out using /* SQLTRAN FIX: EXTERNAL */, and the entire WITH (...) clause — including LOCATION, DATA_SOURCE, FILE_FORMAT, and related metadata — is commented out using /* SQLTRAN FIX */ for traceability.

  • CREATE EXTERNAL DATA SOURCE and CREATE 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 and DROP 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, or CTAS statements are produced. All ingestion-related logic must be manually rewritten using Fabric-supported tools.

Code example

SQL Server:

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

  • 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, and FILE_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 and CREATE 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-SQL COPY, or CREATE TABLE AS SELECT via OPENROWSET. 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