Forum Discussion

harrybao0901's avatar
harrybao0901
Frequent Visitor
2 months ago
Solved

Trimming Space in Copy Data in Pipeline item

Hi everyone, I got into a interesting case when moving data from SQL MI to Snowflake using Copy Data activity in Fabric pipeline. As I checked Fabric tries to preserve the extra space in source even if I add TRIM_SPACE = TRUE in the Copy Data acitivity. I read Snowflake Docs and see that, they indicate that if text is enclosed by double quote, extra spaces will be preserved within the double quote. Eg, " Hello " will be kept as " Hello ".

 

Currently, I am applying trim directly to the source query to SQL MI, does anyone have a better approach on this ? Thank you!!!

data engineering
data pipeline
  • Dev_Dholakia's avatar
    Dev_Dholakia
    2 months ago

    Hi harrybao0901 ,

     

    This is expected Snowflake behavior, not a Fabric bug. The Copy activity stages your data as a quoted CSV and then runs COPY INTO — and TRIM_SPACE = TRUE only strips spaces outside the quotes. Anything inside quoted values (like " Hello ") is preserved as-is. So " Hello " will always come in as Hello, regardless of TRIM_SPACE.


    So trimming in the source SQL (LTRIM(RTRIM(col))) is a perfectly valid and honestly the cleanest fix — you clean the data before it ever gets quoted.


    Other options:

    • Post-load transform in Snowflake — land the raw data as-is and apply TRIM() in a view or a downstream MERGE/INSERT step.
    • Staging + transform pattern — land in a Fabric Lakehouse/Warehouse first, trim there, then push to Snowflake.
    • Additional Snowflake format options in the Copy activity destination — you can pass extra options to the underlying COPY INTO, but changing FIELD_OPTIONALLY_ENCLOSED_BY to NONE to make TRIM_SPACE fully effective is risky if any value contains your delimiter, so I wouldn't recommend it as a general fix.

     

    Quick check on your side: in the Copy activity destination, what's the staging file format (CSV vs Parquet), and are you passing TRIM_SPACE via Additional Snowflake format options or expecting it to be applied elsewhere? That helps confirm whether the quoted-field behavior is really what's biting you.

     

    trimming in the source query is fine and proably the least painful path. Fixing it after load in Snowflake works too.


    References:

    Configure Snowflake in a copy activity 

    Snowflake — CREATE / ALTER FILE FORMAT (TRIM_SPACE)
    Snowflake — COPY INTO <table>

     

    If this got you what you needed, a Kudos and an Accepted Solution mark would be great — it helps others searching for the same thing find the answer quicker.

2 Replies

  • Dev_Dholakia's avatar
    Dev_Dholakia
    Icon for Resolver IV rankResolver IV
    2 months ago

    Hi harrybao0901 ,

     

    This is expected Snowflake behavior, not a Fabric bug. The Copy activity stages your data as a quoted CSV and then runs COPY INTO — and TRIM_SPACE = TRUE only strips spaces outside the quotes. Anything inside quoted values (like " Hello ") is preserved as-is. So " Hello " will always come in as Hello, regardless of TRIM_SPACE.


    So trimming in the source SQL (LTRIM(RTRIM(col))) is a perfectly valid and honestly the cleanest fix — you clean the data before it ever gets quoted.


    Other options:

    • Post-load transform in Snowflake — land the raw data as-is and apply TRIM() in a view or a downstream MERGE/INSERT step.
    • Staging + transform pattern — land in a Fabric Lakehouse/Warehouse first, trim there, then push to Snowflake.
    • Additional Snowflake format options in the Copy activity destination — you can pass extra options to the underlying COPY INTO, but changing FIELD_OPTIONALLY_ENCLOSED_BY to NONE to make TRIM_SPACE fully effective is risky if any value contains your delimiter, so I wouldn't recommend it as a general fix.

     

    Quick check on your side: in the Copy activity destination, what's the staging file format (CSV vs Parquet), and are you passing TRIM_SPACE via Additional Snowflake format options or expecting it to be applied elsewhere? That helps confirm whether the quoted-field behavior is really what's biting you.

     

    trimming in the source query is fine and proably the least painful path. Fixing it after load in Snowflake works too.


    References:

    Configure Snowflake in a copy activity 

    Snowflake — CREATE / ALTER FILE FORMAT (TRIM_SPACE)
    Snowflake — COPY INTO <table>

     

    If this got you what you needed, a Kudos and an Accepted Solution mark would be great — it helps others searching for the same thing find the answer quicker.