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.

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

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.

Things that translate and still change your 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.

SELECT
  7 / 2 AS slash,
  DIV(7, 2) AS div_fn,
  MOD(7, 2) AS mod_fn,
  SAFE_DIVIDE(7, 0) AS safe

SELECT 1 / 0 AS boom

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.

SELECT
  'xYz' = 'xyz' AS different_case,
  'xyz' = ' xyz' AS leading_space,
  COLLATE('xYz', 'und:ci') = 'xyz' AS case_insensitive

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.

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

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.

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.

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

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.

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

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.

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

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.

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?”).

SELECT TOP 2 amount
FROM UNNEST([30, 10, 20]) AS amount
ORDER BY amount DESC

SELECT
  7 AS total,
  total * 2 AS doubled

SELECT NVL(NULL, 0) AS balance

SELECT *
FROM (SELECT 1 AS id, 'a' AS name) AS customers
NATURAL JOIN (SELECT 1 AS id, 100 AS total) AS orders

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.
• No indexes. CREATE INDEX is not supported. Partitioning, clustering and search indexes are the tools. See partitioning vs clustering.
• No sequences. CREATE SEQUENCE is not supported; see 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 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.
• Triggers and cursors. Google’s guides have sections on triggers (Oracle), cursors and dynamic SQL. Plan on rewriting that logic. See scripting and stored procedures.

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

CREATE TEMP TABLE staging AS SELECT 1 AS id;
DELETE FROM staging;
SELECT COUNT(*) AS n FROM staging

What works that Google’s guides say doesn’t

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.

SELECT name, name LIKE ANY ('Ada%', '%son') AS matches
FROM UNNEST(['Ada Lovelace', 'Alan Turing', 'Ken Thompson']) AS name
ORDER BY name

-- 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

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.

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).
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 in Querylab.io lists them with cost for every job in your projects. See how queries are billed.