Summary: Copy Job incremental refresh dies due to data type of unmapped column

[JPTADIVADA]

Steps to Reproduce:

1. In SQL Server (on my end, using SQL Server 2025 Enterprise Development edition):
1. Ensure that CDC is enabled on the database to be used.
2. Create the following table:
   CREATE TABLE dbo.TestCDC (
          Number int IDENTITY PRIMARY KEY NOT NULL,
          Text varchar(100),
          Geo geography
   );
3. Enable CDC on the just-created table:
   EXEC sys.sp_cdc_enable_table
      @source_schema = N'dbo',
       @source_name   = N' TestCDC ',
       @role_name     = NULL,
       @supports_net_changes = 1
4. Insert some data into the table:
   INSERT dbo.TestCDC (Text)
   VALUES ('ABC'), ('DEF'), ('XYZ')
2. In Fabric, create a Copy Job activity that copies from the just-created table to a lakehouse.
1. Set/edit the job’s column mappings for the table, ensuring that the “Geo” column is not mapped but the other two columns are mapped.
2. Run the copy job (i.e. as a full load). Job should succeed (good).
3. Run the copy job again (i.e. as an incremental load). Job fails with the following error (not good):
   ErrorCode=DeltaNotSupportedLogicalType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The logical type SqlGeography is not supported in Delta format. Reason: Cannot find supported delta type for column name Geo, logical type SqlGeography,Source=Microsoft.DataTransfer.ClientLibrary,'
   

Observations:

• Incremental load fails due to the data type of unmapped column “Geo”. An unmapped column should have no effect on the load. If the column is not mapped, it shouldn’t be loaded, so its data type should be irrelevant to whether/how the job runs.
• Full load succeeds while incremental load fails—suggests that a different code path is being used to handle application of mapping between full loads and incremental loads.

[V-yubandi-msft]

Hi @JPTADIVADA ,

Everything looks good now. If you're still experiencing any issues or need any further details or clarification, please feel free to let us know.

Thanks.

[V-yubandi-msft]

Hi @JPTADIVADA ,

If you get a chance, please review the responses shared by @deborshi_nag & @Ben-Dev . They have correctly pointed out the key points, so kindly check and let us know if you need any additional details.

Thank you all for your valuable support @Ben-Dev , @deborshi_nag .

Regards,

Yugandhar.

[JPTADIVADA]

Hello  @deborshi_nag & @Ben-Dev Thanks for your response. We are talking here about Geometry column not Geography column. We have created a view on top of the CDC table excluding the Geometry columns. tested the full load and it was successful but incremental load failed with the error "ErrorCode=FailedToUpsertDataIntoDeltaTable,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Hit an error when upsert data to table in Lakehouse. Error message: Unable to cast object of type 'System.DBNull' to type 'System.String'.,Source=Microsoft.DataTransfer.Connectors.LakehouseTableConnector,''Type=System.InvalidCastException,Message=Unable to cast object of type 'System.DBNull' to type 'System.String'.,Source=Microsoft.DataTransfer.Connectors.LakehouseTableConnector,'" 

Can you all still advice.

BR

JP

[V-yubandi-msft]

Hi @JPTADIVADA ,

This error typically occurs when the incremental load receives NULL values for a column, and the upsert step attempts to process them as strings. Since the full load completes successfully but the incremental load does not, it might be due to the way NULLs are handled in the view during CDC.

Could you try addressing or replacing NULL values in the view for text or string columns and then test the incremental load again?

Thanks.

[JPTADIVADA]

Failing again at incremental load.

[V-yubandi-msft · Accepted solution]

Hi @JPTADIVADA ,

Thank you for checking again. Since the full load works but the incremental load keeps failing, even after excluding the Geometry column and handling NULLs, it’s possible this is due to the Copy Job incremental feature not working reliably when the source is a view.

CDC is set up on the base table, and using a view can lead to schema or type mismatches during incremental merges, causing casting issues like the one you’re experiencing.

For a more stable solution, you could try

1. Create a new table in SQL Server with only the needed columns (excluding Geometry)

2. Enable CDC on this new table & Use this table as the source for incremental load

This should help avoid spatial type and view-related schema issues during CDC processing.

Please try this approach  hopefully it helps resolve the issue. If you’re still facing any problems after trying it, please let us know and we’ll be happy to assist further.

[deborshi_nag]

Hello @JPTADIVADA

Fabric Lakehouse tables (underlying Delta Lake) don't support Geography types, so it needs a conversion before or during ingestion. 

It is quite possible that during full-load the geographic values may be serialised automatically by the source connector or copied without merge/upsert logic, hence it succeeds, but incremental fails! 

You can change the Geography column into Latitude and Longitudes i.e. into numeric columns. 

SELECT

    Geo.Lat AS Latitude,

    Geo.Long AS Longitude

[mikeburek]

The geography data is not being ingested.

[deborshi_nag]

even so.

[mikeburek]

The full load does not bring any geography data into the Fabric table.

The incremental load does not bring any geography data into the Fabric table.

[deborshi_nag]

Even if you manually exclude a column in the Copy Activity mapping, Fabric Data Factory still inspects the table schema during CDC initialization. This line refers to it -

CDC-based incremental copy: If your source database has CDC enabled, Copy job automatically captures and replicates inserts, updates, and deletes to the destination, applying the exact changes.

Change data capture (CDC) in Copy Job - Microsoft Fabric | Microsoft Learn

[mikeburek]

I just ended up making an ETL process to get around this.

You're right, if you can pick the specific columns, it should not error out if the selected columns are valid. It's just lazy error checking, or updating it is in a backlog somewhere.

I had an idea of trying to make a view of the table to incrementally copy. I don't know if that would work. I have not tried it.

Maybe another possible way is to replicate the table with specific columns on SQL Server to a secondary table, and then incrementally load that secondary table. I don't know if it will work, just an idea I had to test out.

[deborshi_nag]

creating a SQL view will work. 

[Ben-Dev]

> creating a SQL view will work. 

Well, the original poster is trying to do incremental loads using Copy Job's CDC-aware functionality. While a view could be used to exclude the troublesome column, wouldn't using the view from the Copy Job cause Copy Job's CDC-based incremental load behavior to be disabled (i.e. not supported)?

On the databse side, tables can be CDC-enabled. However, I don't believe CDC behaviors are "inherited" by a view that is based on a CDC-enabled table. Wouldn't the view be seen by Copy Job as just an ordinary view, not someting CDC-enabled?

[abhidotnet]

What do we know?

• Delta Lake supports only primitive types (string, int, double, binary, etc.)

• SQL CLR types like geography have no equivalent Delta logical type

• Fabric cannot auto‑convert them

So you must convert before ingestion.

Since onelake doesn't support this, our options are described in this article.
geography (Transact-SQL) - SQL Server | Microsoft Learn

My preference would be to store the data using WKT representation.
https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stastext-geometry-data-type?view=sql-se...

[mikeburek]

The question says they are not bringing the geography data to Fabric.

[Ben-Dev]

> So you must convert before ingestion.

But the column isn't being ingested. 🙂

In the repo steps given, the geography column is not mapped for ingestion by Copy Job:
> Set/edit the job’s column mappings for the table, ensuring that the “Geo” column is not mapped but the other two columns are mapped.