ADF Copy Activity Issue - Copy sql data -> ADLS -> Snowflake

Question

ADF Copy Activity Issue - Copy sql data -> ADLS -> Snowflake

Anil Kestur 5

Hi,

We got a data flow with Azure managed sql instance db as source. CDC is enabled (SQL Server CDC) in the source with Allow schema drift and Infer drifted column types. Sink is the ADLS gen 2 type as dataset and allow schema drift. There is derived column to add additional column between the source and the sink.

When the pipeline runs the data is saved as parquet file in the container/folder. Once the parquet file is available, there is a copy activity which reads the parquet file and insert into Snowflake table. Problem is that I can see only the derived columns in the snowflake but not all the columns (source+derived columns) when copy activity runs. Parquet file on the ADLS gen 2 has all the source columns including the derived columns.

Any thoughts please?

Thanks

Anil

0 comments

1 answer

Your answer

Answer 1

Hi @Anil Kestur

Source projection in the Copy Activity

Open the Copy Activity and check:

Source → Import Schema / Preview Data

Verify that the Parquet source dataset is exposing all columns. Sometimes the schema was imported when only a few columns existed and ADF caches the schema.

Try:

Refresh the schema

Re-import projection

Copy Activity Mapping

This is the most common cause.

Go to:

Copy Activity

→ Mapping

Check whether:

A fixed mapping exists.

Only the derived columns are mapped to Snowflake.

If you enabled a custom mapping earlier, ADF will not automatically map new drifted columns.

Try:

Delete the existing mapping.

Re-import schemas.

Enable auto mapping.

Snowflake table definition

Run:

SQL

DESC TABLE <table_name>;

Show more lines

Confirm the Snowflake table contains all expected columns.

I've seen cases where:

Source Parquet has 50 columns.

Snowflake table has only 3 columns.

Copy Activity loads only those 3 matching columns.

Parquet schema vs Mapping behavior

Data Flow supports:

Plain Text

Allow Schema Drift

Infer Drifted Column Types

Show more lines

However, the downstream Copy Activity does not automatically inherit schema drift behavior from Data Flow.

The Copy Activity still relies on:

Imported source schema

Auto mapping

Explicit mapping

So drifted columns written to Parquet may not be picked up unless the Copy Activity schema is refreshed.

Check translator settings in JSON

Look at the Copy Activity JSON.

If you see something similar to:

JSON

"translator": {

"type": "TabularTranslator",

"mappings": [...]

}

Show more lines

and only the derived columns appear in mappings, then the Copy Activity is intentionally copying only those columns.

Thanks.