# SnowConvert has moved your Oracle and Teradata SQL to Snowflake. What an FDE still has to check

> The most expensive bugs in a migration don't make scripts fail. The script runs fine and the numbers come out wrong.

Original: https://fdetimes.net/en/guides/snowconvert-snowflake-migration-validation-checks/

You have run SnowConvert AI over the customer's entire SQL estate. The scripts deployed to Snowflake without a single error, the first dashboard is showing numbers, and the customer is asking when they can switch off the old system. This is exactly when the project is most likely to go wrong, because every remaining bug is one that sets off no alarms.

Automated conversion tools handle syntax well. What they cannot guarantee is semantics: whether the same SQL statement returns the same result on both platforms. An FDE's job at this stage is to prove that it does, with data, and to put that evidence in front of the customer before they turn the old system off.

## The tool's warning list is only a starting point

SnowConvert AI records the differences between the two platforms that it cannot resolve on its own, as EWIs (errors, warnings, issues) and FDMs. Snowflake itself recommends reviewing converted code before deploying it. It is tempting to think that clearing that list means the job is done.

The trap is that some differences are ones the tool understood and deliberately left alone, and others only appear when the code runs against real data. Treat the EWI list as a map of where to look, not as a to-do list to tick off. These four areas deserve attention first.

| Trap | Source system | Snowflake | How to check |
|---|---|---|---|
| Empty strings | Oracle treats '' as NULL | '' and NULL are different values | Search for REPLACE and CONCAT with '' |
| String-to-number casts | Teradata reads empty or all-whitespace strings | Prone to runtime errors | Count dirty values in the source column |
| GROUP BY on text | Teradata is case-insensitive by default | 'ABC' and 'abc' become two groups | Compare group counts across both systems |
| Fixed-length CHAR | Teradata matches trailing pad spaces too | CHAR is not padded | Rerun LIKE operations and string functions |

## Oracle: an empty string is not as empty as you think

Oracle's documentation is explicit: a character value with a length of zero is treated as NULL. Snowflake does not do this, so SnowConvert rewrites empty strings as NULL to preserve the old behaviour. In most cases the rewrite is correct.

The exception is the arguments to REPLACE and CONCAT. SnowConvert's documentation says the transformation does not apply here, because turning empty strings into NULL in these calls would change the query results. Consider a query that normalises customers' phone numbers:

```sql
-- Oracle, left unchanged after conversion
SELECT customer_id,
       REPLACE(phone_number, '-', '') AS clean_phone
FROM customers
WHERE notes IS NULL;
```

The REPLACE line was left unchanged on purpose, but you still need to run it on both systems to confirm the results match. The WHERE line is the bigger worry. On Oracle, `IS NULL` also catches "blank" notes, because Oracle never stores empty strings. If loading the data into Snowflake left '' in the `notes` column, those rows will quietly drop out of the results.

## One reconciliation query for both systems

The first check is simple: for each important text column, count NULLs, empty strings and strings containing only whitespace separately. On Snowflake the query looks like this:

```sql
-- Snowflake (works almost verbatim on Teradata)
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN notes IS NULL THEN 1 ELSE 0 END) AS null_count,
  SUM(CASE WHEN notes = '' THEN 1 ELSE 0 END) AS empty_count,
  SUM(CASE WHEN notes <> '' AND TRIM(notes) = ''
           THEN 1 ELSE 0 END) AS whitespace_only_count
FROM customers;
```

On Oracle, the comparison `= ''` is never true because an empty string is already NULL, so `empty_count` is always 0. To count whitespace-only strings there, change the condition to `notes IS NOT NULL AND TRIM(notes) IS NULL`.

When comparing, add Snowflake's `null_count` and `empty_count` together and check the sum against Oracle's `null_count`. If the figures differ, you know to revisit the queries that filter on `IS NULL`.

In the same spirit, for any column about to go into a GROUP BY, compare `COUNT(DISTINCT city)` with `COUNT(DISTINCT UPPER(city))` on Snowflake. If the two numbers differ, the column holds values that differ only in case, and grouped reports will diverge from Teradata as described below.

## Teradata: lenient where you least expect it

With Teradata, the difference is one of tolerance. When casting strings to numbers, Teradata accepts empty or all-whitespace strings; Snowflake is stricter. A `CAST(postal_code AS INTEGER)` that has run smoothly for years can break the first time it meets such rows. The error only shows up at runtime, not during conversion.

GROUP BY is harder still to spot. Teradata compares strings case-insensitively by default (NOT CASESPECIFIC). Snowflake treats values that differ in case as distinct unless you set a collation:

```sql
SELECT city, COUNT(*) AS order_count, SUM(total_amount) AS revenue
FROM orders
GROUP BY city;
```

Suppose the `city` column contains both 'Ha Noi' and 'HA NOI'. On Teradata that is one group. On Snowflake the query above returns two rows, and the revenue-by-city report suddenly gains a "new city". The table-wide total still matches, so a totals check will not catch this bug.

Fixed-length CHAR is the third trap. Teradata pads values with trailing spaces, and some functions match against the whole value, padding included. Snowflake's CHAR is not padded, so LIKE operations and string functions on CHAR columns need to be rerun and compared.

**Key point:** A script that runs only proves the syntax is right. Whether the numbers are right, you only find out by comparing.

## Data validation: three levels, from cheap to expensive

The code review tells you where to be suspicious. The data comparison is the evidence. Snowflake's data validation process has three levels: schema, metrics and row-level. Row-level is off by default because it is the most resource-intensive.

At the schema level, you compare data types, precision and scale. Where precision or scale at the source is larger than in the target Snowflake column, the process reports a difference at each level.

The metrics level compares aggregate measures with a default tolerance of 0.001. Ask the customer whether that tolerance is acceptable for their money columns rather than assuming it is.

The row-level check takes a signature of each partition on the source side, made up of the primary key columns plus an MD5 hash of the whole row. Any partition that differs is then examined down to individual cells. This level is expensive, so enable it for tables that feed financial reports or regulatory filings; for log tables, metrics are enough.

Datafold, a company that builds migration tools, argues that silently mismatched data types are a common cause of post-migration discrepancies, and recommends checking every column rather than just counting rows. The GROUP BY example above shows why: the row count in the base table matches perfectly, and only the grouped results are wrong.

## The order of work on the customer's site

Start with the EWI and FDM list and rank the files by business importance, not by the number of warnings. Then grep all the converted code for the four patterns in the table above: REPLACE and CONCAT with empty strings, casts to numbers, GROUP BY on text columns, and string functions on CHAR columns.

Next, run schema and metrics checks on every table, then turn on row-level checks for a small set of tables that you and the customer have agreed "must not be wrong". Freeze the migrated data while comparing: Snowflake's documentation warns against modifying data during validation, or the validation results will be skewed.

The most common mistake is reporting "row counts match" as if that were proof of completion. The second is fixing discrepancies directly in the target tables without recording the changes, which leaves the next validation run with no baseline to compare against. The third is handling only what the tool flagged and ignoring the places it deliberately left unchanged.

## Turning this into a strength on your CV

If you are aiming for an FDE or solutions engineer role in data, migration experience only counts when you can describe the validation work. On your CV, don't write something vague like "took part in a Snowflake migration".

Describe which tables you validated, at which level, what kinds of semantic differences you found and how you fixed them. Whoever reads the CV will see at once that you took responsibility for the numbers, not just pressed the button that runs the tool.

Customers rarely remember how many thousand lines of SQL a migration moved. They will remember whether the first month's revenue report on Snowflake matched the old system.

**Try this week:**

- Pick a table with text columns in your current project, run the query counting NULLs, empty strings and whitespace-only strings on both the source and Snowflake, and compare the two.
- Find every REPLACE and CONCAT call with an empty-string argument in code converted from Oracle, then run them side by side on both systems and compare the results.
- For a Teradata report that groups by a text column, compare the number of groups and each group's totals across the two systems.

## Sources

- [SnowConvert AI - About](https://docs.snowflake.com/en/migrations/snowconvert-docs/general/about)

- [Oracle Database SQL Language Reference 19c: Nulls](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Nulls.html)

- [SnowConvert AI: Oracle Literals translation reference](https://docs.snowflake.com/en/migrations/snowconvert-docs/translation-references/oracle/basic-elements-of-oracle-sql/literals)

- [Code Conversion - Teradata Issues (SSC-EWI-TD0052)](https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/code-conversion/issues-and-troubleshooting/conversion-issues/teradataEWI)

- [Data validation (Snowflake migration docs)](https://docs.snowflake.com/en/migrations/aim-for-datawarehouses/data-migration-validation/data-validation)

- [Teradata to Snowflake Migration: Challenges, Best Practices & Practical Guide](https://datafold.com/resources/teradata-to-snowflake-migration)