SQL Query Formatting and Review Workflow: Dialect Differences, Lint Rules, and Safe Refactoring
SQLDeveloper ToolsDatabasesCode ReviewData Engineering

SQL Query Formatting and Review Workflow: Dialect Differences, Lint Rules, and Safe Refactoring

ddetails.cloud Editorial Team
2026-11-19
8 min read

A dialect-aware checklist for formatting, linting, reviewing, and safely refactoring SQL across PostgreSQL, MySQL, SQL Server, and BigQuery.

Consistent SQL formatting makes queries easier to review, but formatting alone cannot prove that a refactor preserves meaning. This workflow combines dialect-aware formatting, linting, join and NULL checks, representative test data, and execution-plan review so teams can change SQL with greater confidence across PostgreSQL, MySQL, SQL Server, and BigQuery.

Overview

A SQL formatter should make structure visible without changing behavior. It can place clauses consistently, align expressions, normalize capitalization, and make nested queries easier to scan. A linter adds a second layer by identifying patterns such as ambiguous column references, inconsistent aliasing, unsafe casts, excessive nesting, or rules your team has chosen to prohibit.

Neither tool understands every business requirement. A query may be valid, well formatted, and still produce the wrong result because a join multiplies rows, a filter changes NULL behavior, or a date expression uses the wrong time zone. Treat formatting and linting as review aids, not semantic verification.

A dependable workflow has five stages:

  1. Identify the target database dialect and the query's intended result.
  2. Format the SQL with a shared, version-controlled style.
  3. Lint the formatted query and resolve meaningful violations.
  4. Compare results on representative data, including edge cases.
  5. Inspect execution plans and document any intentional behavior change.

An online SQL formatter can be useful for a quick review or for turning generated SQL into readable text. Do not paste credentials, customer records, proprietary query logic, or production extracts into a third-party tool unless your organization's data-handling rules allow it. For recurring work, a formatter and linter in the repository provide a more repeatable audit trail.

Dialect differences to identify first

AreaPostgreSQLMySQLSQL ServerBigQuery
Identifier quotingDouble quotes are commonly used for identifiers; unquoted names are folded according to PostgreSQL rules.Backticks are commonly used for identifiers; behavior can depend on SQL mode.Brackets or double quotes may be used, with settings affecting interpretation.Backticks identify table and column paths, especially when project or dataset names are included.
Pagination and row limitingLIMIT and OFFSET are common.LIMIT and OFFSET are common.TOP or OFFSET ... FETCH patterns are common.LIMIT and OFFSET are available, but large scans still require cost and partitioning review.
Boolean conventionsHas a native boolean type.Boolean-like values may be represented through supported numeric or boolean conventions.Often uses BIT for true/false-like values.Has a boolean type.
Upsert or merge patternsCommon approaches include INSERT ... ON CONFLICT.Common approaches include INSERT ... ON DUPLICATE KEY UPDATE.MERGE or separate insert/update logic may be used.MERGE is used for matching source and target rows.
Date and string functionsOften differs from other systems in casting, intervals, and concatenation.Uses its own date, string, and casting syntax.Uses functions and types that differ from both PostgreSQL and MySQL.Includes warehouse-oriented date, array, struct, and safe-casting patterns.

This table is a starting point, not a portability guarantee. A query that formats cleanly in one dialect may fail to parse in another. Keep the dialect setting close to the query and test migration targets with the target engine or a representative parser.

Checklist by scenario

When preparing a new query

  • Write down the expected grain: one row per customer, order, event, account, or another defined entity.
  • Record the intended inputs, time zone, date boundaries, and treatment of missing values.
  • Use explicit column lists instead of SELECT * in durable reports, views, and data contracts.
  • Choose aliases that reveal table roles, such as orders, customers, and latest_events.
  • Format the query before review so indentation exposes nested logic and clause boundaries.
  • Run the linter using the actual target dialect, not a generic default.

When refactoring a working query

  • Save a baseline result with stable ordering and a known test input.
  • Separate cosmetic changes from semantic changes in different commits where practical.
  • Replace repeated expressions with a named common table expression only when it improves clarity and does not obscure optimizer behavior.
  • Check whether moving a predicate from WHERE to ON changes the result of an outer join.
  • Compare row counts, distinct business keys, aggregate totals, and representative records before and after the change.
  • Review the execution plan rather than assuming a shorter query is faster.

When migrating between dialects

  • Change the formatter and linter dialect before editing syntax.
  • Search for reserved words used as identifiers, including names such as user, order, group, and value.
  • Review identifier quoting, case handling, date arithmetic, casts, string concatenation, and regular-expression functions.
  • Check pagination semantics, null-safe comparisons, array or JSON operators, and upsert behavior.
  • Run the query against the destination engine with small, representative fixtures before considering the migration complete.

When reviewing generated SQL

  • Format the generated statement before reading it; long single-line SQL hides joins and filters.
  • Verify that user-controlled values are bound parameters rather than concatenated into SQL.
  • Look for unexpected full-table scans, unbounded date ranges, repeated joins, and accidental cross joins.
  • Confirm that generated aliases and selected columns match the consuming application or report.
  • Test empty inputs, duplicate inputs, missing relationships, and unusually large inputs.

When adding SQL checks to CI

Start with rules that prevent ambiguity and accidental risk. A practical baseline might require a declared dialect, consistent indentation, explicit table aliases, qualified columns in multi-table queries, and a prohibition on accidental cross joins. Add stricter rules gradually so developers can distinguish a meaningful defect from a style preference.

# Illustrative project policy; adapt to the formatter and linter you use
sql_dialect: postgresql
indent_size: 2
require_explicit_aliases: true
require_qualified_columns: true
allow_select_star: false
warn_on_cartesian_join: true
warn_on_ambiguous_null_comparison: true
max_line_length: 100

Configuration names vary between tools, so treat this as a policy template rather than a copy-and-run file. Store the real configuration beside the query library, pin compatible tool versions where your build process requires reproducibility, and document intentional exceptions in code review.

What to double-check

Joins and row multiplication

For every join, state the relationship in plain language and identify its expected cardinality. If a customer should appear once but the customer-to-events relationship is one-to-many, joining directly may duplicate customer-level measures. Check key uniqueness on both sides, then compare the number of rows and distinct business keys after each join.

Be especially careful with LEFT JOIN. A predicate in the WHERE clause can remove unmatched rows and make the result behave like an inner join:

-- Preserves customers without a matching event
FROM customers AS c
LEFT JOIN events AS e
  ON e.customer_id = c.customer_id
 AND e.event_type = 'signup'

Moving e.event_type = 'signup' into WHERE changes which rows survive. That may be correct, but it is a semantic decision, not a formatting change.

NULL behavior

NULL represents an unknown or missing value, not an ordinary value. Expressions such as column = NULL do not test for missingness; use IS NULL or IS NOT NULL. Also inspect comparisons involving nullable join keys, aggregation, ordering, and expressions such as COALESCE. A refactor that replaces a nullable expression with a default can alter grouping and totals.

Dates, time zones, and boundaries

Confirm whether a date range is inclusive or half-open. For timestamps, a pattern such as timestamp >= start_time AND timestamp < end_time often makes adjacent periods easier to reason about than an inclusive end boundary. Verify the source time zone, conversion point, daylight-saving assumptions, and whether the target dialect interprets date literals and intervals as expected.

Plans and resource behavior

Use the database's plan inspection feature appropriate to the environment, and distinguish estimated plans from plans captured during execution. Check access paths, join order, row estimates, partition or index use, sorts, spills, and data scanned. For analytical warehouses, also review partition filters and projected columns. For transactional systems, consider lock duration, selectivity, and whether a new expression prevents useful index access.

Common mistakes

  • Formatting before choosing the dialect: A formatter can normalize syntax that belongs to the wrong engine. Set the dialect first.
  • Using a linter as a correctness oracle: Lint rules identify patterns; they do not know the business definition of an active customer or valid order.
  • Changing style and logic together: Large mixed diffs make review and rollback harder. Separate whitespace-only changes from refactoring when possible.
  • Assuming equivalent functions are interchangeable: Date arithmetic, casts, division, string handling, and regular expressions often differ across engines.
  • Ignoring duplicate rows: Aggregates can look plausible even when a join has multiplied records. Test counts and distinct keys, not only totals.
  • Testing only typical data: Include empty sets, nulls, duplicates, boundary timestamps, unmatched relationships, and unusually large values.
  • Trusting a successful parse: Syntax validation says little about permissions, scan cost, transaction behavior, or result quality.
  • Formatting sensitive SQL in an uncontrolled service: Use local tooling or an approved environment when queries contain confidential logic or data.

When to revisit

Review the workflow before seasonal planning cycles, major reporting changes, database migrations, and query-library reorganizations. Revisit it whenever the database engine, SQL compatibility mode, formatter, linter, generated-SQL framework, or CI pipeline changes. A tool upgrade can introduce new parsing behavior or rules, while a schema change can turn a previously safe query into an ambiguous or expensive one.

At least twice a year, sample important queries and confirm that their formatter configuration, dialect settings, reserved-word list, lint exceptions, test fixtures, and performance checks still match the systems they target. Remove exceptions that no longer have a reason, and add tests for bugs discovered in production or during review.

Before approving your next SQL pull request, use this short final pass:

  1. Confirm the dialect, expected grain, inputs, and time-zone assumptions.
  2. Format the query and inspect the diff for structural changes.
  3. Run lint checks and explain every suppressed rule.
  4. Review joins, NULL handling, filters, casts, date boundaries, and reserved identifiers.
  5. Compare results on representative and adversarial fixtures.
  6. Inspect the execution plan and record any intentional trade-off.
  7. Leave a concise note explaining what changed and why the result remains correct.

This approach keeps an SQL formatter, online or local, in its proper role: making structure and review easier while the team uses tests, domain checks, and execution evidence to establish safety.

Related Topics

#SQL#Developer Tools#Databases#Code Review#Data Engineering
d

details.cloud Editorial Team

Technical Editors

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.