"Numeric value '' is not recognized" - what column?

Question

I am trying to insert data from a staging table into the master table. The table has nearly 300 columns, and is a mix of data-typed Varchars, Integers, Decimals, Dates, etc.

Snowflake gives the unhelpful error message of "Numeric value '' is not recognized"

I have gone through and cut out various parts of the query to try and isolate where it is coming from. After several hours and cutting every column, it is still happening.

Does anyone know of a Snowflake diagnostic query (like Redshift has) which can tell me a specific column where the issue is occurring?

Answer 1

You can run a query like this one to find mismatched types between your two tables:

SELECT a.column_name,
       a.data_type,
       b.data_type
FROM information_schema.columns a
LEFT JOIN information_schema.columns b ON a.column_name = b.column_name
WHERE a.table_schema = 'schema1'
  AND a.table_name = 'table1'
  AND b.table_schema = 'schema2'
  AND b.table_name = 'table2'
  AND a.data_type <> b.data_type

Note: depending on the exact way you concatenate your tables, it can be better to join on ordinal_position instead of column_name.

The output will look like this:

+-------------------+-----------+-------------+
| COLUMN_NAME       | DATA_TYPE | DATA_TYPE_2 |
+-------------------+-----------+-------------+
| SOME_COLUMN       | FLOAT     | TEXT        |
+-------------------+-----------+-------------+
| SOME_OTHER_COLUMN | FLOAT     | TEXT        |
+-------------------+-----------+-------------+

Note for visibility: Another type of error message frequently encountered when trying to combine tables with conflicting data types in Snowflake is: inconsistent data type for result columns for set operator input branches, expected [...], got [...].