Fix Snowflake’s Numeric Value Is Not Recognized Error

Snowflake’s “Numeric value is not recognized” error means a query or load is trying to convert a value to a number, but the value cannot be parsed by that conversion. The fix is to locate the conversion, then keep the value as text, correct a column mapping, or explicitly validate and parse the input. Use the symptoms below to choose where to start.

Symptom	Likely Cause	Appropriate Fix
Error contains `''` or `' '`	An empty or space-only string reaches a numeric conversion	Normalize blanks to SQL `NULL` if they represent missing data
Error contains `'null'` or `'none'`	A text placeholder is being treated as a number	Map documented missing-value markers to SQL `NULL`
Error contains `'f'` or `'referral'`	A flag or text label reaches a numeric expression or destination	Correct the expression’s type or the destination-column mapping
`SELECT` works, but `INSERT` fails	Source expressions do not match destination columns	Specify destination columns and check their order and types
Error appears in a comparison, `CASE`, or `UNION`	Snowflake is implicitly converting text to a numeric type	Make the participating types compatible
Error contains a formatted value such as `'1,250'`	The conversion does not accept the source’s formatting	Apply a matching format model or narrowly defined normalization

What Snowflake’s Numeric Value Error Means

The error often appears as 100038 (22018): Numeric value '…' is not recognized. The quoted value identifies an input that failed conversion; it does not necessarily identify the column or expression responsible.

Conversion can be explicit, through TO_NUMBER or a numeric cast, or implicit. For example, comparing a text column with an unquoted numeric literal can cause Snowflake to interpret the text as a number. Arithmetic, mixed-type expressions, and writes into numeric columns can also trigger conversion.

A column declared as VARCHAR is therefore not protected from this error. Its stored type can be text while an operation using it requires a number.

Also distinguish SQL NULL from the string 'null'. A numeric conversion can preserve an actual SQL NULL; the letters in 'null' are ordinary text unless you explicitly normalize them.

How to Fix the Numeric Conversion Error in Snowflake

The examples use illustrative table and column names. Replace them with your own; raw_value represents a source column declared as VARCHAR.

Step 1: Locate the Conversion and Inspect the Types

Trace the failing query or load to the explicit or implicit conversion that requires a numeric value. Check comparisons, arithmetic, casts, function arguments, and the destination columns of an INSERT or load.
Inspect each relevant source and destination table:
```
DESCRIBE TABLE table_name;
```

Pay attention to both the column type and its position. A correctly typed source value can still be sent to the wrong destination column.

For Snowflake, a NUMBER column created without precision and scale defaults to NUMBER(38, 0), according to Snowflake’s numeric data type documentation. Precision is the total number of digits; scale is the number of digits to the right of the decimal point.

When matching a decimal source to a destination, Snowflake’s NUMBER(20, 2) allows 18 digits to the left of the decimal point and 2 digits to the right. That is an example, not a universal recommendation: use the actual target definition returned by the table description.

Step 2: Find Values That Cannot Be Converted

Test the suspected text column and review values that do not fit the destination’s required range.
Inspect the returned values before changing the query. Separate legitimate text, missing-value markers, formatting differences, and genuinely invalid numeric data.

Unlike TO_NUMBER, TRY_TO_NUMBER returns SQL NULL when conversion fails instead of stopping the query. This diagnostic can also identify values outside the requested numeric range.

Use it on a string expression: Snowflake’s error-handling numeric conversion functions expect string input. Also, a successful conversion is not complete validation, excess fractional digits can be rounded rather than rejected.

If several columns are candidates, test them separately so the results identify which column contains the problem.

Step 3: Keep Text Values as Text

Choose the fix from what the value means, not just whether Snowflake can parse it.

Choose a numeric type for quantities that need arithmetic, and keep labels or identifiers as text. Account codes, postal codes, and identifiers can contain only digits and still belong in VARCHAR.

For a text comparison, use a text literal:

SELECT *
FROM source_table
WHERE raw_value = '100';

The quoted literal avoids asking Snowflake to interpret every compared value as numeric. This query can return the text code '100' without trying to convert unrelated labels.

Do not convert identifiers merely to make a comparison work. Numeric conversion can erase meaningful distinctions such as leading zeros.

Likewise, 'f' is not a numeric value. If it represents a Boolean flag, use the flag’s documented Boolean mapping and a compatible destination type; otherwise, retain or reject it according to the field’s contract. Increasing numeric precision will not fix a text-versus-number mismatch.

Step 4: Normalize Missing Values and Parse Genuine Numbers

Define the field’s missing-value markers before mapping any source text to SQL NULL. Do not assume every unparseable value should become missing.

Normalize those markers, parse the remaining values, and preserve a status for review:

WITH normalized AS (
    SELECT
        raw_value,
        CASE
            WHEN LOWER(TRIM(raw_value)) IN ('', 'null', 'none')
                THEN NULL
            ELSE TRIM(raw_value)
        END AS cleaned_value
    FROM source_table
),
parsed AS (
    SELECT
        raw_value,
        cleaned_value,
        TRY_TO_NUMBER(cleaned_value, 20, 2) AS numeric_value
    FROM normalized
)
SELECT
    raw_value,
    numeric_value,
    CASE
        WHEN cleaned_value IS NULL THEN 'missing'
        WHEN numeric_value IS NULL THEN 'invalid'
        ELSE 'valid'
    END AS parse_status
FROM parsed;

Review the invalid rows before using the parsed output. Correct or quarantine them, and accept missing rows only if the business rules and destination nullability allow them.

This example assumes blanks, 'null', and 'none' are documented missing-value markers. Remove any marker that has a legitimate meaning in your data. TRIM handles surrounding ordinary spaces here; tabs or other whitespace require normalization appropriate to the source.

For formatted numbers, make only changes justified by the input contract. If commas are defined exclusively as thousands separators, this conversion returns 1250:

SELECT TRY_TO_NUMBER(
    REPLACE('1,250', ',', '')
) AS numeric_value;

Do not apply that replacement to decimal-comma data. Do not remove every non-digit character either: doing so can change signs, decimal points, or invalid labels into apparently valid numbers.

Once the rejected values are resolved, use the validated numeric output in the original calculation or load. Keep the raw value available so a failed conversion cannot silently disappear into an aggregate that ignores SQL NULL.

Numeric Conversion in CASE, COALESCE, and UNION

Some errors come from expression types rather than a dirty numeric column. In these cases, correcting the surrounding expression is usually better than cleaning the source.

CASE Results With Different Types

A CASE that returns a number in one branch and text in another can require an unwanted conversion. If the result is a label, return text from every non-null branch:

SELECT
    CASE
        WHEN status = 'active' THEN '1'
        ELSE 'inactive'
    END AS status_label
FROM accounts;

Here, both possible results are text. If the result should instead be numeric, use numeric results and a numeric-compatible missing result, rather than a text label.

Check all branches, not just the branch you expect the current rows to take.

COALESCE With a Numeric Fallback

A numeric fallback can make an otherwise textual COALESCE require numeric coercion. When the result should remain text, make the fallback text too:

SELECT
    COALESCE(raw_value, '0') AS text_value
FROM source_table;

This replaces actual SQL NULL values with the text '0' without converting other text to a number.

For a genuinely numeric result, validate and convert the input before applying a numeric fallback. Use zero only when missing data actually means zero, not simply because zero suppresses an error.

UNION Columns With Incompatible Types

UNION and UNION ALL need compatible types in corresponding output positions. If one query returns a number and another returns a text label in that position, explicitly choose the intended output type:

SELECT
    TO_VARCHAR(account_id) AS account_key
FROM numeric_accounts

UNION ALL

SELECT
    account_key
FROM text_accounts;

This produces textual account keys, assuming text_accounts.account_key is already text. Review every corresponding output column; fixing the first reported mismatch may reveal another.

Casting a numeric identifier to text cannot recover leading zeros already lost when it was stored numerically.

Numeric Errors During INSERT and COPY INTO

A source query can run successfully while a write fails because the destination introduces a conversion. Diagnose the source-to-target mapping before modifying the data.

Explicit INSERT Column Mapping

An insertion without an explicit destination-column list depends on table order. If a source label such as 'referral' lands in a numeric destination, the label itself is not necessarily bad data.

Suppose events contains a numeric event_id and a textual channel. Map them explicitly:

INSERT INTO events (event_id, channel)
SELECT
    event_id,
    channel
FROM staging_events;

Before running the write, preview the selected source fields and confirm that their types match the named destinations. Naming destination columns does not make mapping name-based: the selected expressions still correspond to that list by position.

After the insert completes, check the inserted row count and inspect the destination fields to confirm that each value reached the intended column. Avoid INSERT … SELECT * where schema changes could shift that mapping.

Validate Staged Files Before Loading

For a standard COPY INTO operation without a transformation query, validate the files against the destination first:

COPY INTO target_table
FROM @my_stage/incoming/
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format')
VALIDATION_MODE = 'RETURN_ERRORS';

Replace the table, stage path, and file-format name with existing objects in your environment.

Use the results to check:

Field positions against destination-column positions.
Delimiters and quoting that might shift fields.
Header rows being interpreted as data.
Empty fields and configured null markers.
Numeric formatting and destination precision.

Correct the source, file format, or mapping, then repeat validation. When validation is clean, perform the load:

COPY INTO target_table
FROM @my_stage/incoming/
FILE_FORMAT = (FORMAT_NAME = 'my_csv_format');

Inspect the returned load status, rows loaded, and errors seen. A successful command alone does not prove that every expected row was loaded.

For a COPY transformation using a source SELECT , inspect the selected expressions and their mapping to destination columns. Also, changing ON_ERROR to continue past failures is not a conversion repair, it can leave rejected rows out of the destination.

When the Error Is “Numeric Value Out of Range”

“Not recognized” and “out of range” require different investigations. The first concerns interpreting the input as a number; the second concerns whether a numeric value fits the requested representation.

When checking whether an integer can fit, Snowflake’s NUMBER(38, 0) supports the inclusive range -99999999999999999999999999999999999999 to +99999999999999999999999999999999999999. A narrower precision, or a scale that reserves digits for a fractional part, reduces the available integer capacity.

Compare the source’s required integer and fractional digits with the actual destination definition. Increase precision only when the values are legitimate and the downstream schema can accommodate them; correct invalid source values instead of widening a column around them.

Do not assume that changing INTEGER to BIGINT increases capacity in Snowflake. Those names are aliases for NUMBER, not progressively wider integer storage types.

Nor is DOUBLE a harmless replacement for exact numeric data. Snowflake’s DOUBLE provides approximately 15 decimal digits of precision, according to its numeric data type documentation. That can be unsuitable for exact financial values or long identifiers, and it will not make arbitrary text parseable.

Confirm the Fix and Prevent Recurrence

Verify the corrected operation against the full intended dataset, not just the rows that first exposed the error.

Reconcile rows. Every input should be accounted for as accepted, missing, or rejected.
Check values, not just execution. Confirm that rounding, normalization, and casts have not changed the intended meaning.
Inspect text identifiers. Preserve leading zeros and meaningful distinctions.
Exercise alternate expression branches. A CASE or combined query may encounter different types on other rows.
Keep rejection details. Preserve the original value and enough row context to correct the source.

For recurring ingestion, retain raw text in a staging layer, validate it once, and publish typed values with a clear rejection policy. That is easier to audit than scattering permissive conversions throughout reporting queries.

Next, apply the corrected type contract at the ingestion boundary so downstream queries receive consistent values rather than repeatedly guessing whether text is numeric.

Frequently Asked Questions

How Do I Check an Expression’s Data Type in Snowflake?

When diagnosing an expression, check the types of its literals and function arguments as well as the declared types of any source columns. This example distinguishes a quoted value from a numeric literal:

SELECT
    SYSTEM$TYPEOF('123') AS text_expression_type,
    SYSTEM$TYPEOF(123) AS numeric_expression_type;

The distinction matters when literals or function arguments influence an expression’s conversion behavior.

How Do I Count Actual NULL Values in Snowflake?

Subtract COUNT(column) from COUNT(*) to count actual SQL NULL values. Empty strings and the string 'null' are non-null values and are not included in that null count:

SELECT
    COUNT(*) AS total_rows,
    COUNT(raw_value) AS non_null_rows,
    COUNT(*) - COUNT(raw_value) AS sql_null_rows
FROM source_table;

Why Does a Left Join Fail Instead of Returning NULL for an Invalid Key?

A LEFT JOIN preserves unmatched left rows; it does not suppress conversion errors while evaluating its join condition. For genuinely numeric keys stored as text, a nonthrowing conversion can turn unparseable keys into nonmatches:

SELECT
    l.raw_key,
    r.account_id
FROM incoming_accounts AS l
LEFT JOIN accounts AS r
    ON TRY_TO_NUMBER(l.raw_key) = r.account_id;

Use this only when numeric equality is intended and integer-key validation rules are satisfied; otherwise, compare compatible text keys. Verify that invalid keys remain visible as unmatched rows, and keep right-side restrictions in the ON clause when they should not eliminate those rows.