Forum Discussion
Moving data from S3 to Snowflake
Hi eveyone, I got into an interesting case when moving data from csv file in S3 bucket into Snowflake table. Since I cannot copy data directly from S3 since it kept getting issue with SSL certificate, I created a shortcut in lakehouse pointing to S3 bucket, and used it as a source to copy data into Snowflake table. It's working fine until today. The provider provided the file with a backslash in a value like this "O\'Neal" and it caused the issue when copying data. I enabled 'Skip incompatible row' to make sure it still runs even if getting some bad data. However, when checking in Snowflake table, I still see all the data are there, even the bad data row it notified. Is it some self-heal thing in copy data ? I see it first copy the data in S3 into lakehouse, then from lakehouse to Snowflake. I tried removing the backslash and ran the pipeline and it worked, but I'm curious, is this self-heal mechanism something in copy data for fault tolerance ?
There's no self-heal mechanism in the copy activity — but you've spotted something real. What you're seeing is the staged copy path, and that's where the "healing" impression comes from.
A Lakehouse source (your S3 shortcut) doesn't meet the direct copy criteria for a Snowflake destination, so your copy runs through staging — which matches the two-step behavior you noticed. The service parses your CSV using the source format settings, rewrites the data into a format the Snowflake COPY command accepts, lands it in Fabric's built-in workspace staging, then runs COPY INTO from there.
The important consequence: on this path, Snowflake never reads your original file. It reads the rewritten version the engine produced. Details: https://learn.microsoft.com/en-us/fabric/data-factory/connector-snowflake-copy-activity
The backslash matters because of that parse step. In the DelimitedText format settings, the default Escape character is backslash and the default Quote character is double quote, so a value like "O'Neal" gets interpreted under those rules before the data is rewritten for staging. Reference: https://learn.microsoft.com/en-us/fabric/data-factory/format-delimited-text
Skip incompatible rows does exactly one thing: it detects rows the engine can't reconcile between source and destination, skips them, and counts them — plus writes them to a log file if you enable logging under Settings. It never edits a value.
Three checks will tell you what actually happened.
First, look at that value in Snowflake: if it reads O'Neal without the backslash, the escape character was consumed during the parse, which is why the row loaded.
Second, compare rows read vs rows written vs rows skipped in the copy activity output against a COUNT(*) in Snowflake — read should equal written plus skipped, and if the table holds more than rows written, the extras are leftovers from your earlier failed run (worth a duplicate check, since I don't see a truncate step in your setup).
Third, enable logging and rerun the original file; the log shows exactly which row was skipped and why.
My bet: the value in your table lost its backslash. Let me know which check matches.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist
💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
9 Replies
There's no self-heal mechanism in the copy activity — but you've spotted something real. What you're seeing is the staged copy path, and that's where the "healing" impression comes from.
A Lakehouse source (your S3 shortcut) doesn't meet the direct copy criteria for a Snowflake destination, so your copy runs through staging — which matches the two-step behavior you noticed. The service parses your CSV using the source format settings, rewrites the data into a format the Snowflake COPY command accepts, lands it in Fabric's built-in workspace staging, then runs COPY INTO from there.
The important consequence: on this path, Snowflake never reads your original file. It reads the rewritten version the engine produced. Details: https://learn.microsoft.com/en-us/fabric/data-factory/connector-snowflake-copy-activity
The backslash matters because of that parse step. In the DelimitedText format settings, the default Escape character is backslash and the default Quote character is double quote, so a value like "O'Neal" gets interpreted under those rules before the data is rewritten for staging. Reference: https://learn.microsoft.com/en-us/fabric/data-factory/format-delimited-text
Skip incompatible rows does exactly one thing: it detects rows the engine can't reconcile between source and destination, skips them, and counts them — plus writes them to a log file if you enable logging under Settings. It never edits a value.
Three checks will tell you what actually happened.
First, look at that value in Snowflake: if it reads O'Neal without the backslash, the escape character was consumed during the parse, which is why the row loaded.
Second, compare rows read vs rows written vs rows skipped in the copy activity output against a COUNT(*) in Snowflake — read should equal written plus skipped, and if the table holds more than rows written, the extras are leftovers from your earlier failed run (worth a duplicate check, since I don't see a truncate step in your setup).
Third, enable logging and rerun the original file; the log shows exactly which row was skipped and why.
My bet: the value in your table lost its backslash. Let me know which check matches.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist
💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
Yep, the value in table does not contain backslash anoymore, it becomes O'Neal". The output of the copy data acitivty does imply that there is 1 row skipped. But it captures all the data from source to the target table, that's why I see it's kinda strange.
Sorry for not providing the full context, I do have a TRUNCATE table in pre-script to truncate table before copy data to target table. Also, source file has 138 rows (including the header); and output of the copy data activity is 138 rows read, 137 rows write and 1 row skipped.
Your two results together close the case. The value came out as O'Neal without the backslash — so the escape character was consumed during the engine's parse, exactly as suspected. And the numbers explain the part you found strange.
Your file has 137 data rows (138 lines minus the header). The activity reports 138 rows read — one more logical row than the file physically contains. That extra row is the leftover: the parser split the O'Neal line into the clean record it could recover (which was written, backslash consumed) plus a malformed fragment with the wrong column count. Skip incompatible rows skipped exactly that fragment. So 138 read = 137 real records + 1 fragment, 137 written, 1 skipped — every actual record landed, and the "skipped row" was never a data record to begin with. Nothing self-healed; the row counter and the data were counting different things.
You can see this black on white: turn on Enable logging under the copy activity's Settings and rerun the original file once — the log captures the skipped row's content, and you should find the fragment there rather than the O'Neal record. One small detail worth a glance while you're at it: if the stored value is literally O'Neal" with a trailing quote, that's the split boundary showing in the data itself.
Going forward, the real fix is agreeing on escape semantics with your provider: either they emit standard CSV (quotes doubled inside quoted fields, no backslash escaping), or you keep the default Escape character = \ deliberately and leave fault tolerance plus logging on, so the next odd line is visible instead of fatal. Your TRUNCATE pre-script already rules out duplicate buildup, so you're covered on that side.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist
💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
Hi harrybao0901
I know it might be an obvious question, but I need to ask why you are using. Microsoft Fabric to copy daily between S3 and Snowflake. Shouldn't this be done natively between AWS and Snowflake?
Currently, my team uses Fabric as orchestrator, and S3 bucket is managed by a third-party team, files will be uploaded daily. So that's why I use pipeline to copy data into SF.
Hi,
That's an interesting scenario, and thanks for sharing it—it highlights one of those edge cases that many people don't encounter until they're working with real-world data. You're doing a great job investigating the behavior instead of just accepting that the pipeline succeeded.
From what you've described, this doesn't sound like a "self-heal" mechanism in Copy Data. A few things could explain what you're seeing:
Skip incompatible row is designed to allow the pipeline to continue processing when a row can't be parsed or written, but whether a row is truly skipped depends on where the issue occurs.
Since your flow is S3 → Lakehouse (via Shortcut) → Snowflake, the file may have been read successfully by the Lakehouse engine, and the value O\'Neal may have been interpreted as a valid escaped string before the data was written to Snowflake.
Another possibility is that the warning was generated during parsing, but the connector or underlying engine was still able to normalize or escape the value before the final write. This can happen because different engines (Spark, Fabric, and Snowflake) don't always interpret escape characters in exactly the same way.
I'd recommend comparing the problematic row in the Lakehouse table (or staging data) with the corresponding row in Snowflake to see whether the backslash was preserved, removed, or escaped differently. Also, reviewing the detailed Copy Activity logs may reveal whether the row was actually skipped or simply flagged with a recoverable warning.
Out of curiosity:
Was the CSV using a backslash (\) as the escape character, or was it just a literal character in the field?
Did the Copy Activity metrics report 1 skipped row, or only log a warning without affecting the row counts?
Thanks again for sharing this case—it's a valuable example for the community and could help others facing similar CSV parsing issues.
If you found this explanation helpful, please consider marking it as the Accepted Solution or giving it a Like. It helps others in the community find useful answers more quickly.
Yes it's the best platform
Hi harrybao0901,
Thank you for reaching out to Microsoft Fabric Community.
Thank you GilbertQ, Prince0011, Parchitect and Kabir_82 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
