SQL Dialect Migration Reference: Translate Queries Between PostgreSQL, BigQuery, Snowflake, and MySQL
sqldata-migrationpostgresqlbigquerysnowflakemysql

SQL Dialect Migration Reference: Translate Queries Between PostgreSQL, BigQuery, Snowflake, and MySQL

DDataFabric Editorial Team
2026-11-18
6 min read

A practical SQL migration checklist for translating dates, JSON, arrays, pagination, upserts, and types across PostgreSQL, BigQuery, Snowflake, and MySQL.

Moving SQL between PostgreSQL, BigQuery, Snowflake, and MySQL is rarely a matter of changing one keyword. This reference gives you a practical migration checklist for adapting common query patterns, checking semantic differences, and validating results before a translated query reaches production.

Overview

SQL is portable at the level of broad concepts: filtering, grouping, joining, ordering, and window functions exist in all four systems. The details become less portable when a query depends on date arithmetic, identifier quoting, arrays, JSON, type coercion, upserts, or pagination.

Use the examples below as translation patterns rather than universal drop-in replacements. Exact support can vary by engine, edition, compatibility mode, connector, and release. During a migration, record the source query, the intended result, the translated query, and a test case containing representative edge values.

A reliable process separates two questions:

  • Will the query run? Check syntax, functions, data types, and required clauses.
  • Will it mean the same thing? Check time zones, null behavior, ordering, duplicate handling, numeric precision, and semi-structured data semantics.

Checklist by scenario

Dates and timestamps

First identify whether a column represents a calendar date, a timestamp without a time zone, or an instant that must be interpreted across time zones. Do not translate date expressions until that distinction is documented.

TaskPostgreSQLBigQuerySnowflakeMySQL
Current dateCURRENT_DATECURRENT_DATE()CURRENT_DATE()CURRENT_DATE()
Extract yearEXTRACT(YEAR FROM ts)EXTRACT(YEAR FROM ts)EXTRACT(YEAR FROM ts)EXTRACT(YEAR FROM ts)
Add a dayts + INTERVAL '1 day'DATE_ADD(d, INTERVAL 1 DAY)DATEADD(day, 1, ts)DATE_ADD(d, INTERVAL 1 DAY)

Check whether the expression returns a date or timestamp, how month-end values are handled, and which time zone is applied. Functions that appear equivalent may produce different results around daylight-saving transitions or when a timestamp is implicitly converted.

Identifiers and string functions

Unquoted identifiers are commonly folded or compared differently across systems. PostgreSQL and Snowflake typically use double quotes for case-sensitive identifiers; BigQuery also supports backticks for identifiers; MySQL commonly uses backticks. Treat quoted names as a migration decision, not a cosmetic change.

  • Concatenation is commonly written as || in PostgreSQL, BigQuery, and Snowflake, while MySQL commonly uses CONCAT(). Confirm the target engine's operator and null behavior.
  • Substring, position, regular-expression, and case-conversion functions often share names but differ in argument order or pattern syntax.
  • Replace implicit casts in concatenation and comparisons with explicit casts where possible.

Arrays and JSON

Arrays are not interchangeable across these platforms. PostgreSQL has typed array columns and operators; BigQuery has repeated or array values and uses constructs such as UNNEST(); Snowflake commonly represents nested values with ARRAY or VARIANT; MySQL generally uses JSON for array-shaped data.

For every array or JSON expression, document whether you need the entire value, one element, or one row per element. A common translation mistake is to replace a nested-value lookup with a text extraction function, losing type information or confusing a missing path with a JSON null.

  • PostgreSQL: review json/jsonb operators and typed array indexing.
  • BigQuery: review JSON_VALUE, JSON_QUERY, array indexing, and UNNEST().
  • Snowflake: review VARIANT path expressions, casting, and lateral flattening patterns.
  • MySQL: review JSON_EXTRACT(), the -> operators, and JSON path syntax.

Pagination and window functions

LIMIT and OFFSET are familiar, but pagination is not deterministic without a stable ORDER BY. Add a unique tie-breaker to the ordering clause, especially when pages are consumed by an application or comparison test.

For large or changing datasets, consider keyset pagination: filter on the last seen ordering key instead of scanning an increasing offset. Then validate null ordering, timestamp precision, and whether the target system permits the chosen syntax.

Window functions are broadly portable, but check frame defaults. ROWS and RANGE can produce different results when ordering values are duplicated. Specify the frame explicitly for running totals, moving averages, and retention calculations.

Null handling, upserts, and type conversion

COALESCE(), NULLIF(), and CASE are useful portable building blocks, but implicit conversion rules differ. Test comparisons between strings and numbers, empty strings, invalid dates, and division by zero.

Upsert syntax is especially engine-specific:

  • PostgreSQL commonly uses INSERT ... ON CONFLICT ... DO UPDATE.
  • MySQL commonly uses INSERT ... ON DUPLICATE KEY UPDATE.
  • BigQuery and Snowflake commonly use MERGE for matched and unmatched-row logic.

Before translating an upsert, define the conflict key, duplicate source-row behavior, update columns, and delete requirements. Syntax alone does not establish the same atomicity or deduplication behavior.

What to double-check

  1. Data types: Compare integer ranges, decimal precision, floating-point behavior, boolean representation, binary values, and timestamp types.
  2. Identifier rules: Check reserved words, case folding, schema and dataset naming, and the correct quoting character.
  3. Time zones: Test UTC, a named regional zone, daylight-saving boundaries, and timestamps near midnight.
  4. Nulls and missing values: Distinguish SQL NULL, an empty string, a missing JSON key, and a JSON null.
  5. Ordering: Add explicit ordering wherever output order affects pagination, snapshots, tests, or downstream files.
  6. Aggregations: Check grouping rules, duplicate rows introduced by joins, distinct counts, and division by zero.
  7. Performance: Recheck partition filters, clustering or indexing assumptions, joins, scans, and execution plans. A syntactically valid translation may use a very different access path.
  8. Permissions and connectors: Test the query through the same driver, service account, and orchestration path used in production.

For performance-specific follow-up, use a structured SQL query performance tuning checklist rather than assuming that a successful migration is an efficient one.

Common mistakes

  • Replacing functions by name only: Similar names can have different return types, argument order, or error behavior.
  • Relying on implicit casts: A comparison accepted by one engine may fail, truncate, or convert differently in another.
  • Ignoring duplicate rows: Join behavior and source uniqueness assumptions can change aggregate results even when the query runs.
  • Using offset pagination without a tie-breaker: Results can move between pages when rows share the same sort value.
  • Flattening nested data too early: An array or JSON expansion can multiply rows and alter aggregates.
  • Copying identifier quotes: Backticks, double quotes, and unquoted names do not have the same meaning everywhere.
  • Testing only happy-path data: Include nulls, empty values, duplicate keys, malformed input, extreme dates, and boundary numbers.
  • Stopping after compilation: Compare row counts, checksums, aggregates, and sampled records against the source system.

Keep a small dialect test suite in version control. It should contain representative queries and expected outputs, not just parser checks. For API-fed pipelines, pair SQL tests with contract checks using an OpenAPI contract testing workflow when schema changes can affect query inputs.

When to revisit

Revisit this reference before a migration, during seasonal planning cycles, and whenever a workflow or database version changes. A practical maintenance schedule is to review the examples at least every six months, then update sooner when your team adopts a new connector, SQL mode, data type, optimizer behavior, or semi-structured-data feature.

Use this final checklist before approving a translated query:

  1. Identify the source and target engine versions and connection settings.
  2. Classify every date, timestamp, array, JSON, and numeric column involved.
  3. Replace dialect-specific syntax deliberately, documenting assumptions beside the query.
  4. Run syntax checks, unit tests, and edge-case fixtures.
  5. Compare row counts, key uniqueness, null counts, aggregates, and sampled records.
  6. Review the target execution plan and resource behavior.
  7. Record the accepted translation and add a regression test for the failure that prompted it.

When a query is likely to move again, prefer explicit casts, explicit ordering, named columns, and simple intermediate transformations. Those choices make the next PostgreSQL, BigQuery, Snowflake, or MySQL migration easier to review and safer to validate.

Related Topics

#sql#data-migration#postgresql#bigquery#snowflake#mysql
D

DataFabric Editorial Team

Data Engineering Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.