INFER_SCHEMA function: DECFLOAT(38) inferred for high-precision numeric values in CSV and JSON files (Pending)¶

When the 2026_08 bundle is enabled, the INFER_SCHEMA function with KIND => 'STANDARD' infers DECFLOAT(38) for numeric values in CSV and JSON files that NUMBER and REAL can’t represent without losing digits or numeric value. DECFLOAT(38) stores up to 38 significant digits with a wide exponent range, so these values stay numeric instead of losing precision or becoming TEXT. This change applies to CSV and JSON files only. Parquet, ORC, Avro, and KIND => 'ICEBERG' inference are unchanged.

Before the change:

INFER_SCHEMA had no numeric type between NUMBER and REAL for a value that fit neither, so it fell back to a type that lost information:

• A value with more than 17 significant digits that didn’t fit NUMBER(38, scale) was inferred as REAL, which keeps only about 17 digits, so the extra precision was lost.
• A value outside the range of a double (for example, 1.23e309) was inferred as TEXT in CSV files and as REAL in JSON files.
• A very small value that underflowed to zero (for example, 1E-453), or a subnormal value with degraded precision, was inferred as REAL.
• When the merged precision of two NUMBER values exceeded 38, the scale was truncated to fit NUMBER(38), and a column that mixed different numeric types was inferred as TEXT.

For a CSV file with these columns (PARSE_HEADER = TRUE):

big_int,huge_exp,tiny,mixed_scale
123456789012345678901234567890123456789,1.23e309,1E-453,12345678901234567890123456789012345678
1,2,3,0.1234567890123456789012345678901234567

INFER_SCHEMA inferred types that lost precision or numeric value:

Copy code

SELECT COLUMN_NAME, TYPE
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/numbers.csv'
      , FILE_FORMAT=>'my_csv_format'
      )
    );

+-------------+---------------+
| COLUMN_NAME | TYPE          |
|-------------+---------------|
| big_int     | REAL          |
| huge_exp    | TEXT          |
| tiny        | REAL          |
| mixed_scale | NUMBER(38, 0) |
+-------------+---------------+

After the change:

INFER_SCHEMA infers DECFLOAT(38) for the same values, keeping them numeric with up to 38 significant digits:

Copy code

-- Run the same query as in the previous example.
SELECT COLUMN_NAME, TYPE
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/numbers.csv'
      , FILE_FORMAT=>'my_csv_format'
      )
    );

+-------------+--------------+
| COLUMN_NAME | TYPE         |
|-------------+--------------|
| big_int     | DECFLOAT(38) |
| huge_exp    | DECFLOAT(38) |
| tiny        | DECFLOAT(38) |
| mixed_scale | DECFLOAT(38) |
+-------------+--------------+

A value with more than 38 significant digits is inferred as DECFLOAT(38) with rounding applied beyond the 38th digit.

In JSON files, INFER_SCHEMA applies the same rules to unquoted numbers. A quoted numeric string, such as "00123", stays TEXT. A number outside the DECFLOAT range stays TEXT.

When a column combines different numeric types (for example, NUMBER in one file and REAL in another), the merged type is now DECFLOAT(38) instead of TEXT. If the column contains Infinity or NaN, the merged type stays REAL.

The inferred type is intentionally unchanged for:

• Values that fit NUMBER(38, scale) or REAL.
• Infinity and NaN, which stay REAL because DECFLOAT doesn’t support special values.
• KIND => 'ICEBERG', which doesn’t support DECFLOAT columns.
• Parquet, ORC, and Avro files, which carry their own type metadata.
• Automatic schema evolution for COPY and Snowpipe Streaming.
• Existing tables and stored schemas, which are never rewritten.

How to update your code¶

No action is required to keep inferring the previous types for values that already fit NUMBER or REAL.

CREATE TABLE … USING TEMPLATE converts INFER_SCHEMA output into column definitions, so a regular table that you generate from CSV or JSON files after the bundle is enabled might contain DECFLOAT(38) columns where it previously contained REAL, NUMBER, or TEXT.

For an external table, an Iceberg table, or a hybrid table, USING TEMPLATE doesn’t accept a DECFLOAT(38) column, so a template that infers one causes the statement to fail with an unsupported data type error. For an Iceberg target, use INFER_SCHEMA with KIND => 'ICEBERG', which doesn’t infer DECFLOAT. Otherwise, define the affected columns explicitly instead of generating them from a template.

If you use schema allowlists, custom type parsers, connectors, ORMs, or SQL that assumes text semantics for these columns, confirm that they support the DECFLOAT type. For the drivers and versions that support DECFLOAT, see Drivers and driver versions that support the DECFLOAT data type; an unsupported driver returns DECFLOAT values as TEXT. Review the Limitations for the DECFLOAT data type, which include features and languages that don’t support DECFLOAT. Existing tables aren’t modified.

Ref: 2374