# Migrating to BigQuery from Snowflake, Redshift, Teradata and Oracle > The BigQuery Migration Service assesses a warehouse, translates SQL from about twenty dialects into GoogleSQL, and moves data. What it translates well, the dialect differences a migrating team hits first, each checked against real BigQuery, and where Google's own migration guides are out of date. Source: https://docs.querylab.io/bigquery/migrating-to-bigquery/ Updated: 2026-10-14 BigQuery Migration Service is a set of free tools in the console (Migration, Services) with four jobs: **assess** your current warehouse, **translate** its SQL into GoogleSQL, **transfer** the data, and validate the result. The SQL translator is the part people use first. It handles most syntax mechanically, and what it can't convert shows up in an issues report. The rest of the work is differences in behavior that no translator can fix, which is most of this article. Every BigQuery-side statement below was run against real BigQuery. What we say about Snowflake, Redshift, Teradata and Oracle comes from Google's migration guides for them, not from our own tests of those systems. ## The pieces of the Migration Service | Tool | What it does | Notes | | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Migration assessment | Reads metadata and query logs you extract from the old warehouse, then produces a report on storage cost, workload cost optimization, and a migration plan | Needs an extraction step per source (Teradata, Redshift, Oracle, Snowflake, Hive and more). For Teradata it supports version 15 or later, and on-premises Vantage only | | Interactive SQL translator | Paste SQL in the console, get GoogleSQL | Good for ad hoc queries. Supports translation rules and customization with Gemini (Preview) | | Batch SQL translator | Translates folders of files in Cloud Storage, writes SQL and an issues log back | Max 10 active translation tasks per project. Google suggests under 1,000 input files per job | | Translation API | Same engine as a REST API, in batch or interactive mode | For CI pipelines and your own tooling | | Data transfer | Moves tables with the [Data Transfer Service](https://docs.querylab.io/bigquery/data-transfer-service/) connectors (Redshift, Teradata, Snowflake and others) | Separate from SQL translation | | Data validation tool | Open source tool comparing source and destination tables after the move | | Google says there is no charge for the Migration API or the batch translator. You pay normal storage for input and output files in Cloud Storage. Supported source dialects in general availability are Redshift, HiveQL, Netezza, Snowflake and Teradata (including BTEQ and TPT). In preview: Impala, Spark SQL, Azure Synapse T-SQL, Greenplum, DB2, MySQL, Oracle (SQL, PL/SQL, Exadata), PostgreSQL, Trino and Presto, SQL Server T-SQL, SQLite and Vertica. Google lists "Snowflake SQL is supported in preview" on its Snowflake guide even though the dialect list now shows it as generally available, so check the current status. Google publishes dialect guides for Snowflake, Redshift, Teradata, Oracle, Netezza and Hive. It has no SQL Server translation guide in the BigQuery docs, so for T-SQL you are reading the translator's output without a mapping reference. When a source function has no GoogleSQL equivalent, translated SQL can call a helper UDF from the public `bqutil` project, for example `bqutil.fn.cw_count()`. Google says not to depend on `bqutil` in production: its definitions change over time. Copy the functions into your own project. ## Translations that change results The translator rewrites syntax. These are behaviors, so you need to check them in your results. **Integer division.** `7 / 2` is `3.5` in BigQuery, because `/` always returns a floating-point or numeric result. For integer division use `DIV`. Division by zero is an error, not `NULL`; `SAFE_DIVIDE` returns `NULL`. **slash gives 3.5, DIV gives 3, SAFE_DIVIDE returns NULL** ```sql SELECT 7 / 2 AS slash, DIV(7, 2) AS div_fn, MOD(7, 2) AS mod_fn, SAFE_DIVIDE(7, 0) AS safe ``` Result in BigQuery (1 row): | slash | div_fn | mod_fn | safe | | --- | --- | --- | --- | | 3.5 | 3 | 1 | NULL | **division by zero fails the query** ```sql SELECT 1 / 0 AS boom ``` BigQuery rejects this query: ```text division by zero: 1 / 0 ``` **String comparison is exact.** Equality is case-sensitive and trailing or leading spaces count. Google's Teradata guide says Teradata can ignore trailing spaces and, for `NOT CASESPECIFIC` columns, case, so `'xYz' = 'xyz'` is true there. In BigQuery both comparisons are false. You can get case-insensitive comparison with the `COLLATE` function and the `und:ci` collation, which is cleaner than wrapping both sides in `UPPER`. See [strings and collation](https://docs.querylab.io/bigquery/strings-and-collation/). **exact comparison, and COLLATE for case-insensitive** ```sql SELECT 'xYz' = 'xyz' AS different_case, 'xyz' = ' xyz' AS leading_space, COLLATE('xYz', 'und:ci') = 'xyz' AS case_insensitive ``` Result in BigQuery (1 row): | different_case | leading_space | case_insensitive | | --- | --- | --- | | false | false | true | **Rounding.** `ROUND` rounds halves away from zero: `ROUND(2.5)` is `3`. Google's Teradata guide says Teradata uses banker's rounding. BigQuery has it behind a third argument, `ROUND(x, 0, 'ROUND_HALF_EVEN')`, and only for `NUMERIC` values: with a `FLOAT64` the call fails with No matching signature. **ROUND(2.5) is 3, ROUND_HALF_EVEN gives 2** ```sql SELECT ROUND(2.5) AS plain_float, ROUND(CAST(2.5 AS NUMERIC)) AS plain_numeric, ROUND(CAST(2.5 AS NUMERIC), 0, 'ROUND_HALF_EVEN') AS half_even, ROUND(CAST(3.5 AS NUMERIC), 0, 'ROUND_HALF_EVEN') AS half_even_3_5 ``` Result in BigQuery (1 row): | plain_float | plain_numeric | half_even | half_even_3_5 | | --- | --- | --- | --- | | 3.0 | 3 | 2 | 4 | **NULL ordering and NULL comparisons.** `NULL` sorts first in ascending order (we ran `ORDER BY` over `[2, NULL, 1]`). Comparing to a literal `NULL` is an error: `WHERE x = NULL` fails with "Operands of = cannot be literal NULL". See [NULL handling](https://docs.querylab.io/bigquery/null-handling/). **Implicit casts are narrow.** Google's Redshift guide says to convert most implicit conversions into explicit casts, with a short list that BigQuery does itself: `INT64` to `FLOAT64` or `NUMERIC`, `NUMERIC` to `FLOAT64`, and string literals to `DATE`, `TIMESTAMP`, `DATETIME` and `TIME`. We confirmed the first and the date literal. We also found that `1 + '1'` fails with a confusing message: "Could not cast literal "1" to type DATE", because `+` has a date overload. The `::` cast syntax doesn't exist ("CAST operators are not supported"). `CAST(x AS VARCHAR(3))` fails with "Type not found: VARCHAR", and `CAST(x AS STRING(3))` fails with "Parameterized types are not allowed in CAST expressions": there is one `STRING` type with no length. **what converts silently, and what doesn't** ```sql SELECT 1 + 1.5 AS int_plus_float, CAST('1' AS INT64) + 1 AS string_to_int, SAFE_CAST('x' AS INT64) AS bad_cast, DATE '2024-1-5' AS short_date ``` Result in BigQuery (1 row): | int_plus_float | string_to_int | bad_cast | short_date | | --- | --- | --- | --- | | 2.5 | 2 | NULL | 2024-01-05 | **Booleans.** Google's Redshift guide notes that Redshift accepts `t`, `yes` and `1` as true. In BigQuery only the strings `true` and `false` (any case) cast to `BOOL`; `CAST('t' AS BOOL)` fails with "Bad bool value: t", and `SAFE_CAST` returns `NULL`. A `BOOL` can also be `NULL`. **only true and false strings cast to BOOL** ```sql SELECT SAFE_CAST('true' AS BOOL) AS true_lower, SAFE_CAST('TRUE' AS BOOL) AS true_upper, SAFE_CAST('t' AS BOOL) AS t, SAFE_CAST('yes' AS BOOL) AS yes, SAFE_CAST('1' AS BOOL) AS one, CAST(NULL AS BOOL) AS null_bool ``` Result in BigQuery (1 row): | true_lower | true_upper | t | yes | one | null_bool | | --- | --- | --- | --- | --- | --- | | true | true | NULL | NULL | NULL | NULL | **Dates and time zones.** `DATE '01/02/2024'` is an invalid literal; only `YYYY-[M]M-[D]D` is accepted, so use `PARSE_DATE` with a format. A `TIMESTAMP` literal with an abbreviation like `PDT` fails ("Invalid TIMESTAMP literal"); a numeric offset or an IANA name works, and a literal with no zone is read as UTC. Google's Redshift guide says the same about abbreviations. Format with `FORMAT_DATE`, not `TO_CHAR`. `TIME` has microsecond precision: `12:00:00.123456` parses and a seventh digit doesn't. **date functions that replace DATEADD, DATEDIFF and TO_CHAR** ```sql SELECT DATE_ADD(DATE '2024-01-31', INTERVAL 1 MONTH) AS plus_month, DATE_DIFF(DATE '2024-03-01', DATE '2024-01-31', DAY) AS days_between, FORMAT_DATE('%d/%m/%Y', DATE '2024-01-31') AS formatted, PARSE_DATE('%d/%m/%Y', '31/01/2024') AS parsed ``` Result in BigQuery (1 row): | plus_month | days_between | formatted | parsed | | --- | --- | --- | --- | | 2024-02-29 | 30 | 31/01/2024 | 2024-01-31 | **Integers and decimals.** All the integer type names (`SMALLINT`, `BIGINT`, `TINYINT`, `INTEGER`) are aliases for `INT64`, and `DECIMAL` is `NUMERIC`. Google's Redshift and Oracle guides point out that the source column may be narrower (`SMALLINT` is 2 bytes in Redshift, 8 in BigQuery), and that Oracle `NUMBER(x)` rounds on insert while an `INT64` column rejects a fraction. ## Syntax that fails outright We ran each one. The translator rewrites these, and SQL you write by hand can't use them. | You write | BigQuery says | Use instead | | --------------------------------------------------------------- | -------------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | `SELECT TOP 2 x ...` | syntax error | `ORDER BY ... LIMIT 2` | | `SEL`, `MINUS`, `FETCH FIRST n ROWS ONLY`, `SAMPLE n` | syntax error | `SELECT`, `EXCEPT DISTINCT`, `LIMIT`, `TABLESAMPLE` | | `SELECT 1 AS a, a + 1` | Unrecognized name: a | a `WITH` clause or subquery | | `NVL`, `NVL2`, `DECODE`, `ISNULL`, `IFF`, `IIF`, `ZEROIFNULL` | Function not found | `IFNULL` or `COALESCE`, `IF`, `CASE` | | `GETDATE()`, `SYSDATE`, `ROWNUM` | Function not found, Unrecognized name | `CURRENT_TIMESTAMP()`, `ROW_NUMBER()` | | `DATEADD`, `DATEDIFF`, `TO_CHAR`, `TO_DATE`, `TRY_CAST` | Function not found | `DATE_ADD`, `DATE_DIFF`, `FORMAT_*`, `PARSE_*`, `SAFE_CAST` | | `LISTAGG`, `LEN`, `CHARINDEX`, `SPLIT_PART`, `CONVERT_TIMEZONE` | Function not found | `STRING_AGG`, `LENGTH`, `STRPOS`, `SPLIT(...)[SAFE_OFFSET(n)]`, `DATETIME`/`TIMESTAMP` with a zone | | `x ILIKE 'a%'`, `x::INT64` | syntax error, CAST operators are not supported | `LOWER(x) LIKE`, `CAST` | | `SELECT 1 FROM DUAL`, `SELECT 1 UNION SELECT 2` | Table "DUAL" must be qualified, expected ALL or DISTINCT | `SELECT 1`, `UNION ALL` or `UNION DISTINCT` | | `COUNT(DISTINCT a, b)` | No matching signature | `COUNT(*)` over a `SELECT DISTINCT a, b` subquery | | `NATURAL JOIN` | Natural join not supported | `JOIN ... USING (...)` | | `CREATE INDEX`, `CREATE SEQUENCE`, `SHOW TABLES` | CREATE INDEX is not supported, Statement not supported | none; see below | `SYSDATE` as a call and `SYSDATE` as a bare name both fail. For several of these, the error suggests the replacement ("Did you mean ifnull?"). **TOP n is a syntax error** ```sql SELECT TOP 2 amount FROM UNNEST([30, 10, 20]) AS amount ORDER BY amount DESC ``` BigQuery rejects this query: ```text Syntax error: Expected end of input but got integer literal "2" at [1:12] ``` **a SELECT list can't reference its own aliases** ```sql SELECT 7 AS total, total * 2 AS doubled ``` BigQuery rejects this query: ```text Unrecognized name: total at [3:3] ``` **NVL does not exist** ```sql SELECT NVL(NULL, 0) AS balance ``` BigQuery rejects this query: ```text Function not found: NVL at [1:8] ``` **NATURAL JOIN is not supported** ```sql SELECT * FROM (SELECT 1 AS id, 'a' AS name) AS customers NATURAL JOIN (SELECT 1 AS id, 100 AS total) AS orders ``` BigQuery rejects this query: ```text Natural join not supported at [3:1] ``` Google's Redshift guide lists the alias reuse and `TOP` differences, and its Teradata guide lists `SEL` and `TOP`. The Redshift and Teradata guides also cover joins: a comma between tables is an inner join in Teradata, according to Google, while in BigQuery `FROM a, b` is a cross join. We confirmed the BigQuery side by counting rows from two `UNNEST` calls (6 rows from 2 by 3). Teradata also lets a join predicate contain a subquery, which BigQuery rejects, so the guide says to join to a derived table instead. ## What doesn't carry over - Constraints are not enforced. BigQuery accepts `PRIMARY KEY ... NOT ENFORCED` and foreign keys as metadata for the optimizer. We inserted the same key twice into a table declared with one, and both rows went in. Deduplicate in the pipeline. See [primary and foreign keys](https://docs.querylab.io/bigquery/primary-and-foreign-keys/). - No indexes. `CREATE INDEX` is not supported. Partitioning, clustering and [search indexes](https://docs.querylab.io/bigquery/search/) are the tools. See [partitioning vs clustering](https://docs.querylab.io/bigquery/partitioning-vs-clustering/). - No sequences. `CREATE SEQUENCE` is not supported; see [default values and identity columns](https://docs.querylab.io/bigquery/default-values-and-identity-columns/). - `DELETE` needs a `WHERE`. `DELETE FROM t` fails with "DELETE must have a WHERE clause". Use `WHERE TRUE` or `TRUNCATE TABLE`. - Row-by-row DML is slow and rationed. BigQuery is built for set-based statements; see [DML](https://docs.querylab.io/bigquery/dml/) for the quotas. - Transactions exist, with different rules. Google's Teradata guide describes BigQuery as optimistic concurrency with snapshot isolation (first to commit wins), switching to queued, retried execution when several `UPDATE` statements hit the same table. See [transactions](https://docs.querylab.io/bigquery/transactions/). - Triggers and cursors. Google's guides have sections on triggers (Oracle), cursors and dynamic SQL. Plan on rewriting that logic. See [scripting](https://docs.querylab.io/bigquery/scripting/) and [stored procedures](https://docs.querylab.io/bigquery/stored-procedures/). **a NOT ENFORCED primary key accepts duplicates** ```sql CREATE TEMP TABLE customers ( id INT64, PRIMARY KEY (id) NOT ENFORCED ); INSERT INTO customers VALUES (1), (1); SELECT id, COUNT(*) AS copies FROM customers GROUP BY id ``` Result in BigQuery (1 row): | id | copies | | --- | --- | | 1 | 2 | **DELETE without WHERE is rejected** ```sql CREATE TEMP TABLE staging AS SELECT 1 AS id; DELETE FROM staging; SELECT COUNT(*) AS n FROM staging ``` BigQuery rejects this query: ```text Query error: DELETE must have a WHERE clause at [2:1] ``` ## Where Google's guides are out of date Some of Google's per-dialect guides are out of date. We tested each of these against real BigQuery. If you read the guide and plan work around one of these, you can skip it. | Guide says | What we found | | --------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------- | | Teradata: BigQuery doesn't have the `ANY` predicate for `LIKE ANY (...)` | `'a' LIKE ANY ('a', 'b')` works | | Teradata: express `QUALIFY` as a `WHERE` on a subquery | `QUALIFY` works directly | | Oracle: hierarchical or recursive queries "are not used in BigQuery" | `WITH RECURSIVE` works | | Snowflake: a BigQuery SQL UDF can't return a table; "on the roadmap" | `CREATE TABLE FUNCTION ... RETURNS TABLE<...>` works | | Snowflake: comments or descriptions on UDFs aren't supported | `OPTIONS (description = '...')` works and is stored in the routine's DDL | | Snowflake: BigQuery `BOOL` can only hold `TRUE`/`FALSE`, unlike Snowflake which allows `NULL` | `BOOL` can be `NULL` | | Snowflake: `TIME` supports 0 to 6 "nanoseconds" | 0 to 6 digits of fractional seconds, which is microseconds | | Oracle: `NVL2(a, b, c)` maps to `IF(a, b, c)` | `IF` needs a boolean, so `IF(5, 'a', 'b')` fails. Use `IF(a IS NOT NULL, b, c)` | | Oracle: `NANVL` maps to `IFNULL` | `IFNULL` replaces `NULL`, not `NaN`: `IFNULL(CAST('NaN' AS FLOAT64), 0)` returns `NaN` | | Oracle: `INT64` allows "up to 18 digits" | `INT64` holds 19-digit values up to 9223372036854775807; adding 1 overflows | **LIKE ANY works** ```sql SELECT name, name LIKE ANY ('Ada%', '%son') AS matches FROM UNNEST(['Ada Lovelace', 'Alan Turing', 'Ken Thompson']) AS name ORDER BY name ``` Result in BigQuery (3 rows): | name | matches | | --- | --- | | Ada Lovelace | true | | Alan Turing | false | | Ken Thompson | true | **WITH RECURSIVE and QUALIFY in one query** ```sql -- Both of these are things older migration guides say BigQuery lacks. WITH RECURSIVE counter AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM counter WHERE n < 3 ) SELECT n FROM counter QUALIFY ROW_NUMBER() OVER (ORDER BY n DESC) = 1 ``` Result in BigQuery (1 row): | n | | --- | | 3 | The Snowflake guide also contradicts itself on integers: its table maps `INT` to `BIGNUMERIC`, while a note says BigQuery converts `INTEGER` to `INT64` by default. In the guide's Snowflake data type table, `VARIANT` and `OBJECT` map to `JSON`, and `TIMESTAMP` (which Snowflake defaults to `TIMESTAMP_NTZ`) maps to `DATETIME`. Querying a `JSON` column uses `JSON_VALUE` and dot access, not Snowflake's colon path (`JSON '{"a": 1}':a` is a syntax error). See [JSON](https://docs.querylab.io/bigquery/json/). ## A plan that works 1. Run the assessment. It tells you how many queries, tables and users you have before you commit to a timeline. 2. Translate in batch, read the issues log first, and sort it by how many files each issue touches. 3. Run old and new queries side by side on a sample and compare outputs with the validation tool. Pay attention to the behaviors above: integer division, string comparison, rounding, `NULL` ordering, time zones. Once both versions exist in BigQuery, Querylab.io's Compare tab diffs two tables or two query results by key columns, and two schemas (see [Data compare](https://docs.querylab.io/data-management/data-compare/)). 4. Replace loops and row-at-a-time logic with set-based SQL before tuning anything. 5. Re-check cost after cutover. BigQuery bills for the columns a query scans, so check the bytes per query once real traffic arrives; [Query history](https://docs.querylab.io/writing-sql/query-history/) in Querylab.io lists them with cost for every job in your projects. See [how queries are billed](https://docs.querylab.io/bigquery/how-queries-are-billed/). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Introduction to BigQuery migration (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration-intro) - [Enable SQL translations, supported dialects (Last updated 2026-10-06)](https://cloud.google.com/bigquery/docs/enable-sql-translations) - [Batch SQL translator (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/batch-sql-translator) - [Interactive SQL translator (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/interactive-sql-translator) - [Translation API (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/api-sql-translator) - [Migration assessment (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration-assessment) - [Snowflake SQL translation guide (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration/snowflake-sql) - [Amazon Redshift SQL translation guide (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration/redshift-sql) - [Teradata SQL translation guide (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration/teradata-sql) - [Oracle SQL translation guide (Last updated 2026-10-05)](https://cloud.google.com/bigquery/docs/migration/oracle-sql)