SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system features
    • Synapse Analytics to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Temporary tables
      • External tables
      • Identity columns
      • Default values
      • Materialized views
      • Create table as select (CTAS)
      • Unsupported system features
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
  • Microsoft training - SQL Tran
    • Introduction
    • Licensing plans
    • Deploying and activating
    • Creating a new project
    • Exploring the "Overview" screen
Powered by GitBook
On this page
  • Emulation context
  • Emulation strategy
  • Code example
  • Important notes
  1. Emulation scenarios
  2. Synapse Analytics to Fabric Warehouse

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
) */;

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.

PreviousTemporary tablesNextIdentity columns

Last updated 11 days ago

Data migration with Omni Loader: When SQL Tran does not emit ingestion logic for external tables, Spectral Core’s high-performance migration tool 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.

Omni Loader
External table emulation in SQL Tran