Recreating dynamics 365 synapse link tables got the error Msg 16588, Level 16, State 1, Line 305 Virtual or partition columns found in Delta external table.

Hello !

Thank you for posting on Microsoft Learn Q&A.

You’re mixing Delta with a virtual or partition column. In serverless SQL, Delta external tables don't allow virtual columns like filepath()/filename() or computed partition columns. That’s exactly what this bit does:

[PartitionId] AS CAST(filepath(1) AS VARCHAR(20))

If your delta table is partitioned by PartitionId, that column already exists in the delta schema and just declare it as a normal column:

CREATE EXTERNAL TABLE [dbo].[tablename]
(
    [Id] UNIQUEIDENTIFIER,
    [SinkCreatedOn] DATETIME2(7),
    [SinkModifiedOn] DATETIME2(7),
    [accountid] UNIQUEIDENTIFIER,
    [accountleadid] UNIQUEIDENTIFIER,
    [importsequencenumber] BIGINT,
    [leadid] UNIQUEIDENTIFIER,
    [name] VARCHAR(400),
    [overriddencreatedon] DATETIME2(7),
    [timezoneruleversionnumber] BIGINT,
    [utcconversiontimezonecode] BIGINT,
    [versionnumber] BIGINT,
    [IsDelete] BIT,
    [CreatedOn] DATETIME2(7),
    [PartitionId] VARCHAR(20)  
)
WITH (
    DATA_SOURCE = [link],
    LOCATION = N'tablename_partitioned',  
    FILE_FORMAT = [DELTA]
);

If you don’t create an external table, you can use a VIEW over OPENROWSET(… FORMAT='DELTA') and this way you can avoid schema drift pain and the virtual column restriction:

CREATE OR ALTER VIEW dbo.tablename AS

SELECT *
FROM OPENROWSET(
    BULK 'tablename_partitioned/',     
    DATA_SOURCE = [link],
    FORMAT = 'DELTA'
) AS r;

If you need the folder value, you could add filepath() in a view over parquet/CSV but for delta specifically, virtual columns aren’t supported so you can rely on the real partition column instead.

If the folder doesn’t have _delta_log, it isn’t Delta then either switch to FILE_FORMAT = PARQUET and you may use filepath():

CREATE EXTERNAL TABLE dbo.tablename
(
    ...,
    PartitionId AS CAST(filepath(1) AS VARCHAR(20))
)
WITH (
    DATA_SOURCE = [link],
    LOCATION = 'tablename_partitioned',
    FILE_FORMAT = [ParquetFormat]
);

or convert it properly to delta first.