Oracle to Snowflake data type mapping: every column, and the eight places Snowflake's own two documents disagree about it

NUMBER with no precision

What each document says: The Openflow connector maps an undefined-precision Oracle NUMBER to NUMBER(38, 19). SnowConvert maps the same column to NUMBER(38, 18). One digit of scale, two Snowflake documents, same route.

Checked against the primary source: Oracle states that for NUMBER "the absence of precision and scale designators specifies the maximum range and precision for an Oracle number", which is 38 significant digits with scale from -84 to 127. Snowflake caps precision at 38 and scale at 37. Since both mappings spend the full 38 digits, the scale figure decides how many digits are left for the integer part: 18 leaves 20, 19 leaves 19. A value with 20 digits before the decimal point therefore fits one Snowflake mapping and overflows the other.

What to do: Measure the real magnitude on the source, then declare NUMBER(38,s) yourself rather than accepting either default

NUMBER with negative scale

What each document says: SnowConvert converts NUMBER(5,-2) to NUMBER(5), noting that "Snowflake does not allow negative scale, so it is being removed" and that this may cause functional inequivalence. The Openflow connector says only that when precision or scale exceeds Snowflake limits "the value is stored as TEXT".

Checked against the primary source: Oracle allows scale down to -84, and a negative scale rounds to the left of the decimal point, so NUMBER(5,-2) stores values rounded to the nearest hundred. Dropping the scale keeps the digits and drops the rounding rule, which is a silent change in meaning. Falling back to TEXT keeps the value and destroys arithmetic. They are different failures for one source column.

What to do: Find these columns first. There are usually under ten of them, and each needs a human decision, not a default

DATE

What each document says: The Openflow connector pins Oracle DATE to TIMESTAMP_NTZ. SnowConvert emits a bare TIMESTAMP.

Checked against the primary source: Those are the same thing only while a session parameter holds its default. Snowflake documents TIMESTAMP as "a user-specified alias associated with one of the TIMESTAMP_* variations", resolved by TIMESTAMP_TYPE_MAPPING, which defaults to TIMESTAMP_NTZ but is commonly set to TIMESTAMP_LTZ by teams that want local time rendering. Set it that way and a SnowConvert-generated schema and an Openflow-replicated one disagree about your single most common column type.

What to do: Set TIMESTAMP_TYPE_MAPPING deliberately at account level, and declare TIMESTAMP_NTZ explicitly rather than relying on the alias

INTERVAL columns

What each document says: SnowConvert maps both interval types to VARCHAR(20) and rewrites the value into a shorthand such as "1d, 2h, 3m, 4s". The Openflow connector maps them to unbounded TEXT.

Checked against the primary source: Two different string formats for one source column, and one of them carries a length cap. Snowflake has no interval column type to map to, so text is the only honest answer, but a downstream parser written against one format silently produces nulls against the other. The VARCHAR(20) cap is the sharper edge, since a long duration written out in that shorthand runs past twenty characters.

What to do: Store the duration as a NUMBER of seconds plus a unit column and rewrite the arithmetic once, rather than parsing either text format forever

LONG and the LOB family

What each document says: The connector maps LONG, CLOB and NCLOB to TEXT with a default ceiling of 16 MB, and BLOB and LONG RAW to BINARY with a default ceiling of 8 MB. SnowConvert maps LONG to VARCHAR and notes it must be cast through TO_LOB().

Checked against the primary source: Oracle allows 2 GB in a LONG column and far more in a LOB. Snowflake VARCHAR reaches 128 MB. The connector defaults are lower again at 16 MB and 8 MB, raisable but not raised for you. That is three separate ceilings on one column, and the rows that hit the lowest one are your largest documents, which are exactly the rows nobody spot checks after a load.

What to do: Measure MAX(DBMS_LOB.GETLENGTH(col)) on the source and raise the connector limit before the first load, not after

Extended VARCHAR2 and RAW

What each document says: SnowConvert records VARCHAR2 above 4000 bytes, NVARCHAR2 above 4000 bytes and RAW above 2000 bytes as "Not supported in Snowflake", transforms them as ordinary types, and states that the MAX_STRING_SIZE parameter is not recognized.

Checked against the primary source: Oracle documents exactly those thresholds: VARCHAR2 and RAW reach 32767 bytes when MAX_STRING_SIZE is EXTENDED, against 4000 and 2000 bytes when it is STANDARD. Snowflake TEXT holds far more than either, so the target is never the constraint here. The risk is that the converter has stopped reading the setting that told it how wide the source column really is, so any length assertion it generates is based on the standard limit.

What to do: Read MAX_STRING_SIZE from the source database yourself and treat any generated length as unverified

BOOLEAN and JSON

What each document says: The Openflow connector lists BOOLEAN mapping to BOOLEAN and JSON mapping to VARIANT. The SnowConvert built-in type table carries neither row.

Checked against the primary source: Both types are real and recent on the Oracle side, JSON from 21c and BOOLEAN from 23ai, so the gap is a documentation lag rather than a disagreement. It still matters, because SnowConvert states that anything outside its table has no rule, and the connector states that "any Oracle data types not listed in this table are mapped to TEXT by default". Read only the older document and a real boolean arrives as a string.

What to do: Declare BOOLEAN and VARIANT explicitly. These are the two rows where the newer document is the right one

Everything the tables do not list

What each document says: The connector is explicit: "Any Oracle data types not listed in this table are mapped to TEXT by default." SnowConvert simply has no row.

Checked against the primary source: On an Oracle schema of any age that catch-all is doing real work, because Oracle estates accumulate object types, collections, SDO_GEOMETRY, ANYDATA and ROWID columns that no mapping table covers. The load does not fail. Each one arrives as a string, and the failure surfaces months later as a report that cannot aggregate a column everyone assumed was numeric.

What to do: List every non-scalar type in ALL_TAB_COLUMNS before the migration, and decide each one on purpose

NUMBER columns with no declared precision

This is the row where Snowflake contradicts itself, and it is also the most common numeric column in an Oracle schema, because NUMBER with no arguments is what most legacy DDL contains. One Snowflake mapping leaves you 19 digits before the decimal point and the other leaves 20. Neither tells you which side of that line your data sits on.

SELECT owner, table_name, column_name
FROM   all_tab_columns
WHERE  data_type = 'NUMBER'
AND    data_precision IS NULL
AND    owner = 'YOUR_SCHEMA'
ORDER  BY table_name, column_name;

How to read the result: Every row is a column whose target precision is being guessed for you. For the ones that hold real magnitudes, invoices, quantities, identifiers, run SELECT MAX(LENGTH(TRUNC(ABS(col)))) FROM tbl to get the widest integer part actually present. Under 19 and either mapping is safe. At 19 or 20 you have found a column that loads under one Snowflake document and fails under the other.

NUMBER columns with negative scale

Oracle allows scale from -84 to 127 and Snowflake allows 0 to 37. A negative scale is a rounding rule, not a formatting choice: NUMBER(5,-2) holds values rounded to the nearest hundred. SnowConvert removes the negative scale and flags functional inequivalence. The connector may instead store the column as text.

SELECT owner, table_name, column_name,
       data_precision, data_scale
FROM   all_tab_columns
WHERE  data_scale < 0
AND    owner = 'YOUR_SCHEMA'
ORDER  BY table_name;

How to read the result: Most schemas return nothing here and you can move on in a minute. If rows come back, each one needs a decision rather than a default: keep the rounding by writing it into the load with ROUND(col, -2), or accept the extra digits and record that the column now stores a precision the business process never intended.

Scale above 37

Oracle NUMBER accepts scale up to 127. Snowflake stops at 37, and the connector documents that when precision or scale exceeds Snowflake limits "the value is stored as TEXT". A numeric column arriving as text is the failure that reconciles perfectly on row counts and breaks every aggregate built on it.

SELECT owner, table_name, column_name, data_scale
FROM   all_tab_columns
WHERE  data_scale > 37
AND    owner = 'YOUR_SCHEMA'
ORDER  BY data_scale DESC;

How to read the result: Rare but worth thirty seconds, because the failure mode is severe and silent. Any row returned is a column that will not be numeric on the other side unless you intervene. Decide the real precision the business needs, usually far below 37, and declare it explicitly in the target DDL.

DATE columns and the fractional seconds you are about to invent

Oracle is unambiguous that DATE stores "year, month, day, hour, minute, and second" and that "it does not have fractional seconds or a time zone". Snowflake TIMESTAMP_NTZ carries up to nine fractional digits. Nothing is lost, but every DATE column arrives looking more precise than the source ever was, and code that compares timestamps for equality starts behaving differently.

SELECT table_name, column_name
FROM   all_tab_columns
WHERE  data_type = 'DATE'
AND    owner = 'YOUR_SCHEMA'
ORDER  BY table_name;

How to read the result: Usually the longest list this query set produces, and that is the point: Oracle DATE is the default temporal column in most Oracle schemas. Do not try to change all of them. Confirm TIMESTAMP_TYPE_MAPPING at account level so the bare TIMESTAMP alias resolves the way you expect, and truncate to the second in any comparison that used to rely on Oracle having no sub-second component.

CHAR columns and blank padding

Oracle CHAR is a fixed length type and pads values to the declared width. Both Snowflake mappings send it to TEXT, and Snowflake CHAR is only a synonym for VARCHAR, so no padding survives. The stored values keep the spaces they were given, and comparison semantics change under them.

SELECT table_name, column_name, data_length
FROM   all_tab_columns
WHERE  data_type IN ('CHAR','NCHAR')
AND    owner = 'YOUR_SCHEMA'
ORDER  BY table_name;

How to read the result: For each row, run SELECT COUNT(*) FROM tbl WHERE col <> TRIM(col) to see whether padding is actually present in the data rather than only in the type. Where it is, apply RTRIM during the load and compare trimmed values on both sides, or a join on a padded country or product code returns fewer rows in Snowflake with no error anywhere.

LOB columns against the connector default

The Openflow connector defaults to 16 MB for CLOB and NCLOB and 8 MB for BLOB. Oracle LOBs are measured in gigabytes and up. The limits are raisable and they are not raised for you, so the affected rows are your largest documents, images and payloads.

SELECT table_name, column_name, data_type
FROM   all_tab_columns
WHERE  data_type IN ('CLOB','NCLOB','BLOB','LONG','LONG RAW')
AND    owner = 'YOUR_SCHEMA'
ORDER  BY data_type, table_name;

How to read the result: For each row returned, run SELECT MAX(DBMS_LOB.GETLENGTH(col)) FROM tbl. That single number tells you whether the default ceiling is fine or whether you need to raise it before the first load. If any LONG columns appear, note that Oracle itself says "do not create tables with LONG columns" and that they exist only for backward compatibility, so the migration is a good moment to retire them.

Types no mapping table covers

The connector states that any Oracle type not in its table becomes TEXT by default, and nothing is raised when that happens. Older Oracle estates are full of candidates: object types, VARRAYs and nested tables, SDO_GEOMETRY, ANYDATA, XMLTYPE, ROWID.

SELECT data_type, COUNT(*) AS cols
FROM   all_tab_columns
WHERE  owner = 'YOUR_SCHEMA'
AND    data_type NOT IN (
         'NUMBER','VARCHAR2','NVARCHAR2','CHAR','NCHAR',
         'DATE','FLOAT','BINARY_FLOAT','BINARY_DOUBLE',
         'CLOB','NCLOB','BLOB','RAW')
GROUP  BY data_type
ORDER  BY cols DESC;

How to read the result: This is the single most useful query on the page, because it turns an unknown into a short list in one run. Anything here that is not a TIMESTAMP or INTERVAL variant is heading for TEXT unless you decide otherwise. On a typical Oracle schema the answer is a handful of types across a few dozen columns, and those columns are the entire semantic risk of the migration.

Tables the connector cannot replicate at all

Snowflake states that with its Oracle connector "each replicated table must have a primary key, a qualifying unique constraint, a qualifying unique index, or a user-declared logical key". Legacy Oracle schemas routinely contain staging, audit and history tables that have none of those.

SELECT t.table_name
FROM   all_tables t
WHERE  t.owner = 'YOUR_SCHEMA'
AND    NOT EXISTS (
         SELECT 1 FROM all_constraints c
         WHERE  c.owner = t.owner
         AND    c.table_name = t.table_name
         AND    c.constraint_type IN ('P','U'))
ORDER  BY t.table_name;

How to read the result: Every table listed needs either a declared logical key or a different loading strategy, decided during planning rather than discovered during the cutover weekend. This query usually surprises people, because the tables without keys are the append-only ones nobody has touched in a decade, and they are often the largest tables in the schema.