Part 12 — Same Query, Every Platform

SQL for Every Data Platform · Part 12

Same Query, Every Platform

A reference comparison of how the same query looks across BigQuery, Snowflake, Databricks SQL, Microsoft Fabric, and Amazon Redshift. Capability-level, not live-tested — the same accuracy guardrail as the site's Learn the Pattern platform-tables.

In 60 seconds

Where the platform-specific syntax lives — all in one place (see Anatomy of a Data Platform for how these five platforms map onto the same six universal layers):

BigQuery's DATE_TRUNC(date, part) reverses the argument order vs. everyone else's DATE_TRUNC('part', date) — the single most common copy-paste bug moving a query between platforms.
Fabric/SQL Server's + silently does numeric addition instead of raising an error if either side isn't already a string.
Redshift is architecturally different, not just syntactically — DISTKEY/SORTKEY encode a distributed-systems decision (which node), not just partitioning/clustering.
MERGE support is the row most likely to be stale by the time you read it — verify current docs.

LIMIT / TOP — What They Are, and Why You'd Use Them

LIMIT/TOP cap how many rows a query returns, after everything else (WHERE, JOIN, GROUP BY, ORDER BY) has already run. The everyday reasons to reach for it: previewing a huge table without pulling millions of rows to your screen, pagination (page 2 = skip the first N, then limit), and "top-N" reports (highest spenders, most recent orders) once you've sorted with ORDER BY. Without an explicit ORDER BY, which rows you get back is not guaranteed to be stable across runs.

SQL · LIMIT vs. TOP, same result

-- ANSI / DuckDB / BigQuery / Snowflake / Databricks / Redshift: LIMIT trails the query
SELECT id, order_date, amount
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Fabric / SQL Server: TOP is positional, right after SELECT -- not a trailing clause
SELECT TOP 5 id, order_date, amount
FROM orders
ORDER BY order_date DESC;

The ANSI Baseline — Run for Real, Then Compared

Every query on this page starts from an ANSI baseline that actually runs in DuckDB, so the comparisons below aren't hypothetical — they're annotated variants of a query that executes. DATE_TRUNC(unit, timestamp) rounds a date/timestamp down to the start of the given unit (month, quarter, year) — the standard way to bucket a timestamp column so GROUP BY can aggregate "per month" instead of per exact instant. DATE_TRUNC's argument order is the single most common copy-paste bug moving a query between BigQuery and any other platform on this list.

SQL · ANSI baseline (executed) + the DATE_TRUNC gotcha

-- Runs for real in DuckDB / PostgreSQL / Snowflake / Redshift as-is:
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM orders
GROUP BY month;

-- BigQuery reverses the argument order -- the #1 copy-paste bug moving queries here:
SELECT DATE_TRUNC(order_date, MONTH) AS month, SUM(amount)
FROM orders
GROUP BY month;

-- CONCAT / || joins strings end to end -- building a display label from parts:
-- works everywhere except Fabric/SQL Server
SELECT first_name || ' ' || last_name AS full_name FROM customers;
-- Fabric/SQL Server: `+` overloads for strings, but silently does numeric addition
-- if either side isn't already a string -- BigQuery/Snowflake's CONCAT() can't do that by accident
SELECT first_name + ' ' + last_name AS full_name FROM customers;

Semi-Structured Data, Four Names (ties to Part 6)

These are types built to hold a whole nested/hierarchical value — an object, an array, a mix — in one column, instead of forcing you to normalize every nested field into its own table before you can query it. STRUCT (BigQuery, Databricks) is a fixed, named record type: a set of sub-fields with known names and types, accessed with dot notation, like a mini-table living inside one column. VARIANT (Snowflake) is the opposite trade-off: a flexible, schema-less container that can hold any JSON-like value (object, array, scalar) without declaring its shape up front, accessed with colon notation. MAP (Databricks) holds an arbitrary number of key-value pairs of the same type, for when the set of keys isn't fixed (e.g. a shopping cart's product IDs). BigQuery STRUCT/ARRAY with dot notation; Snowflake VARIANT with colon notation; Databricks STRUCT/MAP; Redshift SUPER. Unnesting an array also has four names: UNNEST(), LATERAL FLATTEN, EXPLODE() — same concept throughout.

SQL · same field, four extraction syntaxes (reference)

-- This series (SQLite/DuckDB), Part 6:
SELECT json_extract(payload, '$.device') FROM customer_events;

-- BigQuery: dot notation on a native STRUCT column
SELECT payload.device FROM customer_events;

-- Snowflake: colon notation on a VARIANT column
SELECT payload:device FROM customer_events;

-- Fabric Warehouse: JSON functions over NVARCHAR (no first-class nested type yet)
SELECT JSON_VALUE(payload, '$.device') FROM customer_events;

Partitioning & Clustering, Four Names Plus One Different Architecture (ties to Part 9)

PARTITION BY (table DDL, not the OVER (PARTITION BY ...) window-function clause from Part 4 — same keywords, unrelated purpose) physically splits a table's data into separate files/segments by a column's value, almost always a date. A query that filters on that column then only has to read the matching segments instead of the whole table — Part 9's DuckDB demo measures exactly this ("Scanning Files: 1/3"). Clustering sorts the rows within each partition by a second column, so filters on that column are also fast. BigQuery/Databricks/Fabric all separate storage from compute (see Storage vs. Compute), so partitioning/clustering is purely a storage-layout decision. Redshift's DISTKEY controls which node a row lives on — a genuinely different, node-distributed architecture, not just different syntax for the same idea.

SQL · partition + cluster DDL, four platforms (reference)

-- BigQuery
CREATE TABLE orders PARTITION BY DATE(order_date) CLUSTER BY customer_id AS SELECT ...;

-- Snowflake: automatic micro-partitioning, no manual partition DDL -- CLUSTER BY is a hint
ALTER TABLE orders CLUSTER BY (order_date);

-- Databricks (Delta Lake)
CREATE TABLE orders PARTITIONED BY (order_year) AS SELECT ...;
OPTIMIZE orders ZORDER BY (customer_id);

-- Redshift: DISTKEY picks the NODE a row lives on -- a different concept, not just syntax
CREATE TABLE orders (...) DISTKEY(customer_id) SORTKEY(order_date);

Same query, five sets of keywords — details in the table below.

Capability	BigQuery	Snowflake	Databricks SQL	Fabric / SQL Server	Redshift
Row limit	`LIMIT n`	`LIMIT n`	`LIMIT n`	`TOP n` (positional)	`LIMIT n`
Truncate to month	`DATE_TRUNC(d, MONTH)`	`DATE_TRUNC('month', d)`	`DATE_TRUNC('MONTH', d)`	`DATETRUNC(month, d)`	`DATE_TRUNC('month', d)`
String concat	`CONCAT(a,b)`	`a \|\| b`	`CONCAT(a,b)`	`a + b`	`a \|\| b`
Nested/semi-structured	`STRUCT`/`ARRAY`	`VARIANT`/`OBJECT`	`STRUCT`/`MAP`	`JSON_VALUE`/`OPENJSON`	`SUPER`
Partitioning	`PARTITION BY`	automatic micro-partitions	`PARTITIONED BY` (Delta)	Delta partitioning	`DISTKEY` (different concept)
Clustering / sort	`CLUSTER BY`	`CLUSTER BY` (key, not guarantee)	Z-ORDER / Liquid Clustering	evolving – verify docs	`SORTKEY`
`MERGE` support	Yes	Yes	Yes (Delta)	Yes (T-SQL)	No native MERGE – verify current docs

Capability-level only — not live-tested against a real account. Cloud SQL dialects change; verify against current vendor docs before relying on any specific syntax.

The takeaway: Treat every row on this page as "verify before you ship," not a fact to memorize. When migrating a query between platforms, check date functions and semi-structured access first — they have the least syntactic overlap.

Runnable code for this part: sql-for-data-platforms on GitHub — Module 12 of 14.

← Part 11 Procedural SQL: Stored Procedures, Triggers & PL/SQL Part 13 → Choosing Your Database

← Back to Publications