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.

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2025-12-04T02:41:39.0133333+00:00

    Hi @Arud Seka Berne S

    Thank you for contacting to Microsoft QA, below are few detailed mitigation steps that may resolve your reported issue -

    Convert Numeric Columns to String During Export

    In your ADF Copy Activity, enable type conversion so floats are written as full-precision strings.

    JSON

    "typeConversionSettings": {

    "treatFloatAsString": true

    }

    This ensures the CSV contains the exact numeric representation from SQL Server.

    Configure DelimitedText Dataset for Full Precision

    In the ADF dataset settings for CSV, set:

    • quoteAll = true
    • encoding = UTF-8
    • precision = full (or use string conversion as above)

    This prevents scientific notation rounding and preserves all digits.

    Load into Snowflake Using FILE_FORMAT

    Create a Snowflake file format for CSV:

    SQL

    CREATE FILE FORMAT my_csv_format

    TYPE = 'CSV'

    FIELD_OPTIONALLY_ENCLOSED_BY = '"'

    NULL_IF = ('NULL');

    Then COPY INTO and cast back to FLOAT or NUMBER after load.

    By treating floats as strings during staging, you mimic ADF’s internal TEXT behavior. Snowflake can safely parse these values back to numeric types without losing precision.

    Was this answer helpful?

  3. Vinodh247 42,366 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.

    Was this answer helpful?

    0 comments