Dialect differences.
WHAT IS STANDARD
Core query structure, joins, aggregation, and much else.
WHAT DIFFERS
Date and time functions, considerably String functions Handling of nulls in sorting Window frame defaults Type names and casting Limit and offset syntax Upsert behaviour
WHY THAT MATTERS
Queries written for one engine frequently fail or, worse, behave differently on another.
WHAT TO BE ESPECIALLY CAREFUL WITH
Integer division, which truncates in some engines String concatenation with nulls Case sensitivity of identifiers and comparisons
WHAT ANALYTICAL ENGINES ADD
Approximate aggregation Array and structured types Sampling Query result caching
WHAT TRANSACTIONAL ENGINES PROVIDE THAT ANALYTICAL ONES MAY NOT
Full transactional guarantees Enforced constraints Efficient single-row operations
WHAT THAT MEANS
Do not assume constraints exist in a warehouse.
WHY
Many analytical engines do not enforce them, so uniqueness must be verified rather than assumed.
WHAT TO DO WHEN TARGETING SEVERAL ENGINES
Use a transformation framework abstracting the differences, or accept maintaining variants.
WHAT TO TEST
On the engine you actually run.
