Cloud Data Warehouses Compared: Snowflake vs BigQuery vs Redshift

Most comparisons of Snowflake vs BigQuery vs Redshift focus on architecture and billing, which the BigQuery vs Snowflake vs Redshift comparison covers. For the people who write the queries, the SQL dialects matter as much. They are close enough that queries look portable, and different enough to fail, or worse, to run and return different answers.

To see the differences concretely, six everyday queries were written in Snowflake SQL and translated into BigQuery and Redshift SQL with sqlglot, an open-source SQL parser and transpiler that understands dozens of dialects (sqlglot). The translations below are sqlglot's output, not runs on the warehouses; where behaviour is described, it comes from each vendor's documentation.

import sqlglot
from sqlglot.errors import UnsupportedError

# The same intent written once (in Snowflake SQL), translated by sqlglot into BigQuery and Redshift SQL
queries = {
    "Q1 latest order per customer (QUALIFY)":
        "SELECT customer_id, order_id, amount FROM orders "
        "QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1",
    "Q2 case-insensitive match (ILIKE)":
        "SELECT name FROM products WHERE name ILIKE '%bike%'",
    "Q3 date difference in days":
        "SELECT DATEDIFF(day, ordered_at, shipped_at) AS days_to_ship FROM orders",
    "Q4 truncate to month and count":
        "SELECT DATE_TRUNC('month', ordered_at) AS month, COUNT(*) FROM orders GROUP BY 1",
    "Q5 null handling with IFF and NVL":
        "SELECT IFF(amount > 100, 'large', 'small') AS size, NVL(coupon, 'none') AS coupon FROM orders",
    "Q6 top 10 rows":
        "SELECT * FROM orders ORDER BY amount DESC LIMIT 10",
}

for title, sql in queries.items():
    print(title)
    print(f"   snowflake: {sql}")
    for target in ("bigquery", "redshift"):
        try:
            out = sqlglot.transpile(sql, read="snowflake", write=target, unsupported_level=sqlglot.ErrorLevel.RAISE)[0]
        except UnsupportedError as err:
            out = f"NOT TRANSLATED: {str(err).splitlines()[0]}"
        print(f"   {target + ':':<10} {out}")
    print()

print("sqlglot version", sqlglot.__version__)

Q1 latest order per customer (QUALIFY)
   snowflake: SELECT customer_id, order_id, amount FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1
   bigquery:  SELECT customer_id, order_id, amount FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC NULLS FIRST) = 1
   redshift:  SELECT customer_id, order_id, amount FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1

Q2 case-insensitive match (ILIKE)
   snowflake: SELECT name FROM products WHERE name ILIKE '%bike%'
   bigquery:  SELECT name FROM products WHERE LOWER(name) LIKE LOWER('%bike%')
   redshift:  SELECT name FROM products WHERE name ILIKE '%bike%'

Q3 date difference in days
   snowflake: SELECT DATEDIFF(day, ordered_at, shipped_at) AS days_to_ship FROM orders
   bigquery:  SELECT DATE_DIFF(shipped_at, ordered_at, DAY) AS days_to_ship FROM orders
   redshift:  SELECT DATEDIFF(DAY, ordered_at, shipped_at) AS days_to_ship FROM orders

Q4 truncate to month and count
   snowflake: SELECT DATE_TRUNC('month', ordered_at) AS month, COUNT(*) FROM orders GROUP BY 1
   bigquery:  SELECT TIMESTAMP_TRUNC(ordered_at, MONTH) AS month, COUNT(*) FROM orders GROUP BY 1
   redshift:  SELECT DATE_TRUNC('MONTH', ordered_at) AS month, COUNT(*) FROM orders GROUP BY 1

Q5 null handling with IFF and NVL
   snowflake: SELECT IFF(amount > 100, 'large', 'small') AS size, NVL(coupon, 'none') AS coupon FROM orders
   bigquery:  SELECT IF(amount > 100, 'large', 'small') AS size, COALESCE(coupon, 'none') AS coupon FROM orders
   redshift:  SELECT CASE WHEN amount > 100 THEN 'large' ELSE 'small' END AS size, COALESCE(coupon, 'none') AS coupon FROM orders

Q6 top 10 rows
   snowflake: SELECT * FROM orders ORDER BY amount DESC LIMIT 10
   bigquery:  SELECT * FROM orders ORDER BY amount DESC NULLS FIRST LIMIT 10
   redshift:  SELECT * FROM orders ORDER BY amount DESC LIMIT 10

sqlglot version 30.21.0

The silent one: where NULLs sort

Look at the first and last queries. Translating to BigQuery, sqlglot added NULLS FIRST to every descending sort. That is not decoration. The three warehouses disagree on where NULLs go by default:

• Snowflake: with the default setting, NULLs count as higher than any value, so they come last when sorting ascending and first when sorting descending (Snowflake: ORDER BY).
• Redshift: the same, last in ascending and first in descending order (Redshift: ORDER BY).
• BigQuery: the opposite, NULLs first in ascending and last in descending order (BigQuery: query syntax).

Now consider the first query, "latest order per customer". If an order has a missing ordered_at, Snowflake and Redshift sort it first in a descending order, so ROW_NUMBER() = 1 picks the order with no date as the "latest". BigQuery puts it last and picks the most recent dated order. The same query text returns different rows, with no error. sqlglot preserved Snowflake's behaviour by spelling out NULLS FIRST; a hand translation that copied the query unchanged would not.

The habit: always write NULLS FIRST or NULLS LAST in ordering that decides which row wins, and filter or handle NULLs explicitly before ranking.

The other differences

QUALIFY (Q1). All three accept it in these translations; Redshift documents a QUALIFY clause for filtering window function results (Redshift: QUALIFY). Older SQL code often uses a subquery instead, which is portable everywhere.

ILIKE (Q2). Snowflake and Redshift support case-insensitive ILIKE; for BigQuery, sqlglot rewrote it as LOWER(name) LIKE LOWER('%bike%').

Date differences (Q3). Snowflake and Redshift use DATEDIFF(part, start, end); BigQuery uses DATE_DIFF(end, start, part) with the arguments in a different order. Swapping start and end silently flips the sign, a classic migration bug.

Truncation (Q4). DATE_TRUNC('month', ...) in Snowflake and Redshift becomes TIMESTAMP_TRUNC(..., MONTH) in BigQuery for a timestamp column; BigQuery chooses the function by data type.

Conditionals (Q5). Snowflake's IFF and NVL became IF and COALESCE in BigQuery and a CASE expression with COALESCE in Redshift. COALESCE and CASE are the portable choices.

Limits (Q6). LIMIT works in all three, but the sort before it carries the NULL-ordering difference above.

Habits for portable SQL

• Prefer standard constructs: CASE, COALESCE, subqueries, explicit JOIN syntax.
• Spell out NULLS FIRST or NULLS LAST whenever the order matters.
• Name date-function arguments carefully and add a test with known dates.
• Keep dialect-specific SQL in a few clearly marked models, for example in dbt, rather than scattered.
• When migrating, translate with a tool, then compare row counts and key aggregates on a sample before switching.

Running a dialect migration in practice

Moving a few hundred queries or models between warehouses goes more smoothly in stages. First, inventory the SQL and group it by the features it uses: window functions, date logic, semi-structured data, user-defined functions. Translate the simplest group first to build confidence in the tooling, and keep a list of every manual correction you make, because the same patterns will repeat. For each translated model, run it on both warehouses against the same snapshot of data and compare row counts, key totals and a sample of rows ordered by a stable key. Differences usually cluster around the issues above: NULL ordering, date arithmetic and implicit type conversion. Only switch consumers over once the comparisons match, and keep the old version running in parallel for a short period so that any surprise can be traced quickly.

Using a transpiler safely

Tools like sqlglot save a great deal of manual work, but treat their output as a draft. Read the translated queries, especially ordering, date and type conversions, and run both versions against the same sample data where you can. The NULL-ordering example shows why: the translation was correct only because the tool knew a default that most people would not think to check.

For warehouse design questions, see data warehouse interview questions, and for performance, SQL optimisation for data engineers.

Warehouse SQL and migration practice are part of the Data Engineering course.

Sources

• sqlglot (GitHub) — sqlglot project
• ORDER BY (Snowflake documentation) — Snowflake
• Query syntax (BigQuery documentation) — Google Cloud
• ORDER BY clause (Amazon Redshift documentation) — Amazon Web Services
• QUALIFY clause (Amazon Redshift documentation) — Amazon Web Services