Share via

Precision loss when migrating data from SQL Server to Snowflake using ADF via CSV staging (but not with Direct Copy)

Arud Seka Berne S 0 Reputation points
2025-11-27T05:50:05.4233333+00:00

I’m migrating SQL Server tables to Snowflake using Azure Data Factory. Direct Copy from SQL Server to Snowflake works, but I can’t use it because of the 100 MB row size limit. That forces me to take an intermediate staging approach.

Approach 1: Direct Copy (works, no precision issues)

SQL Server → (ADF Direct Copy → internal TEXT file) → Snowflake

The internal TEXT format generated by ADF preserves numeric precision exactly as shown in SSMS results.

Approach 2: Indirect Copy via Staging (causes precision rounding)

SQL Server → CSV in Azure Blob → COPY INTO Snowflake

Using CSV, some numeric columns get rounded, unlike the internal TEXT output used by Direct Copy. I cannot use Parquet because Parquet cannot handle ancient dates (e.g., date values in 1600s).

Problem If ADF Direct Copy can internally generate a TEXT file with full precision preserved, there should be a way to stage the same data (via file format) without losing precision.

Question How can I export data from SQL Server to a staging file (CSV or another supported format) without precision loss, achieving the same numeric accuracy that Direct Copy produces internally?

SQL Server Table:enter image description here

Direct Copy (Internal TXT file):
enter image description here
Note: Please look at the first column values alone.

Stage Copy CSV file:
enter image description here

Data Comparison:
enter image description here

CREATE
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vinodh247 40,031 Reputation points MVP Volunteer Moderator
    2025-11-28T15:22:18.3733333+00:00

    Hi ,

    Thanks for reaching out to Microsoft Q&A.

    FLOAT and REAL in SQL Server are IEEE-754 binary floating-point types. They cannot guarantee exact decimal precision. They always round when converted to character formats, and every tool (ADF, SSMS, .NET, JDBC, ODBC) prints floating-point values differently because each one decides how many digits to emit.

    Why the huge difference?

    Direct Copy works because ADF uses its own internal text serializer, which chooses enough digits so that Snowflake can reconstruct the same binary value.

    Your CSV export does not. ADF and SQL Server connectors choose their own default formatting (typically 7 digits for REAL/24-bit float, 15–17 for FLOAT(53)), which causes the rounding you are seeing. This is why your CSV looks different from SSMS and different from the ADF direct-copy internal file. If you want bit-exact reproducibility for FLOAT/REAL values, you have only one reliable option --> You must export them as text, not as numeric.

    Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.