Working with Different SQL Dialects Questions

Understand differences between SQL Server, PostgreSQL, BigQuery, Snowflake, and other databases. Know which functions are available in which databases (e.g., GENERATE_DATE_ARRAY in BigQuery vs. recursive CTEs in others) and how to adapt queries accordingly.

MediumTechnical

68 practiced

You maintain queries that were developed against a MySQL source with mixed-case column names and now must run on PostgreSQL. Demonstrate how to rewrite queries to be portable with respect to quoted identifiers and case sensitivity. Give an example of a failing query and the fixed version.

Sample Answer

Approach (brief): MySQL treats unquoted identifiers case-insensitively and preserves column names as written; PostgreSQL folds unquoted identifiers to lower-case and only preserves mixed-case when identifiers were created with double quotes. To make queries portable you must either (A) reference columns in lower-case unquoted form (recommended), or (B) use PostgreSQL double quotes exactly matching the mixed-case identifier (less portable). Best practice for BI: normalize schema to lower_case_with_underscores and use lower-case unquoted identifiers in SQL.

Failing example (works in MySQL, fails in Postgres if table was created without quoted identifiers):

sql

-- MySQL-friendly: mixed-case column names used unquoted
SELECT OrderID, CustomerName
FROM Sales;

Why it fails on Postgres: Postgres interprets OrderID and CustomerName as orderid and customername (lower-case), which don't exist if the table was created with quoted mixed-case names ("OrderID", "CustomerName").

Two fixes

1) Use double quotes to match exact mixed-case identifiers (works if schema has quoted mixed-case names):

sql

SELECT "OrderID", "CustomerName"
FROM "Sales";

2) Preferable portable fix—use lower-case identifiers (recommended long-term): rename columns to lower-case and reference unquoted, or alias them to friendly names:

sql

-- After normalizing schema to lower-case column names
SELECT order_id, customer_name
FROM sales;

-- Or, if table has quoted mixed-case names but you want lowercase aliases in output:
SELECT "OrderID" AS order_id, "CustomerName" AS customer_name
FROM "Sales";

Key points:- Unquoted identifiers -> PostgreSQL lower-cases them.- Quoted identifiers -> case-sensitive and must match exactly.- For BI/reporting portability, normalize schema to lower-case, use consistent naming, and use aliases to produce stable column names for dashboards.

HardTechnical

87 practiced

Compare using CTEs, temporary tables, and persistent derived tables (PDTs) for intermediate transforms in BI queries. For Postgres, BigQuery, and Snowflake, explain performance, resource usage, materialization behavior, and when you would choose each option in an ETL or dashboard query pipeline.

Sample Answer

Brief framing: CTEs, temp tables, and PDTs are all ways to break complex transforms into intermediate steps. Choose based on size, reuse, cost model, and concurrency.

Common trade-offs- CTEs: syntactic, inline (logical) views. In Postgres pre-12 and some engines they can act as optimization fences (materialized), but in modern Postgres, BigQuery, Snowflake they are usually inlined unless explicitly materialized. Good for readability and small, single-query reuse. Low operational overhead; no storage lifetime. Poor for very large intermediates or repeated reuse across queries because work may be repeated.- Temporary tables: session-scoped materialized storage. Materialized once per session, good for large joins/aggregations reused multiple times in the same session. In Postgres they use local temp storage and can reduce repeated compute; in Snowflake you can create transient/temp tables (storage cost applies but can be managed); BigQuery supports temporary tables via scripting or CREATE TABLE statement in temp datasets (storage + cost implications). Requires explicit cleanup; handle concurrency and permissions.- Persistent Derived Tables (PDTs): managed, scheduled materialized tables in BI tooling (Looker, dbt). Persisted to disk, often incremental, can be refreshed on schedule or triggered. Best for heavy, frequently read transforms powering dashboards. Incurs storage cost and pipeline complexity but optimizes query latency and concurrency.

By platform specifics- Postgres: CTEs typically inlined (Postgres 12+), but large CTEs can still increase planning/IO. Temp tables store on disk (or work_mem if small) and reduce re-computation. Use temp tables for complex ETL sessions; use materialized views/PDTs for repeated dashboard queries (but watch refresh cost).- BigQuery: CTEs are inlined and charged per processed bytes each time; repeated work costs more. Temporary tables (or staging tables) persist to storage and can significantly reduce repeated scan costs. Use partitioned/clustering for cost efficiency. PDTs (e.g., dbt/Looker) map to managed tables; incremental builds are critical to control cost.- Snowflake: CTEs are inlined; Snowflake may optimize but large pipelines benefit from transient tables or materialized views. Temporary/transient tables are cheap to create and avoid long-term storage but still use credits on compute during refresh. Snowflake materialized views have maintenance overhead; PDTs (dbt) often implemented as clustered/micro-partitioned tables with incremental models.

When to choose- Use CTEs: simple transforms, one-off queries, readability, when intermediate size small and reuse = 0.- Use temp tables: ad-hoc multi-step ETL in a session, large intermediates reused immediately, or when you need to break memory-heavy queries into stages.- Use PDTs/materialized tables: dashboard backends, frequent reads, expensive transforms where precomputing (full or incremental) reduces cost and latency; schedule refreshes or incremental logic to balance freshness and compute.

Operational notes- Monitor storage and compute costs (BigQuery scans, Snowflake credits, Postgres I/O).- Prefer partitioning/clustering and incremental builds for PDTs.- Test planner behavior: some engines optimize differently; validate whether CTEs are inlined or materialized.

MediumTechnical

76 practiced

You receive event timestamps in user-local time plus a timezone identifier column and must aggregate events into business days in each user's local timezone, then roll up to UTC for central storage. Describe how you would implement this transformation in BigQuery and PostgreSQL considering DST changes and ambiguous local times.

Sample Answer

Approach summary- Interpret each event’s local wall-clock datetime together with its IANA timezone to get an unambiguous absolute instant (UTC). Handle DST/ambiguous times explicitly (see policies below). Group events by the business day defined in the user’s local zone, then roll up/store the business-day window as UTC start/end or UTC-day-key.

Key principles- Always keep the original local datetime, timezone id, and (if available) the original UTC offset for auditing.- Define a clear policy for ambiguous local times (fall-back): either prefer the earlier occurrence, prefer the later occurrence, or require caller to supply offset. Log ambiguous cases to a data-quality table for review.- For non-existent times (spring-forward), choose policy: shift forward to the first valid instant or reject/log.

BigQuery implementation (example)- Use TIMESTAMP(local_datetime_string, timezone) to interpret local wall time as an absolute TIMESTAMP in UTC. BigQuery uses the IANA tzdb and handles DST.- Aggregate by computing the local DATE via DATETIME/TIMESTAMP functions then grouping.

Example:

sql

-- events: user_id, local_dt STRING like '2025-11-02 01:30:00', tz STRING e.g. 'America/Los_Angeles'
WITH parsed AS (
  SELECT
    user_id,
    local_dt,
    tz,
    -- interpret local wall time as UTC instant
    TIMESTAMP(local_dt, tz) AS event_ts_utc,
    -- local date in user's timezone (business day)
    DATE(TIMESTAMP(local_dt, tz), tz) AS business_date_local
  FROM events
)
SELECT
  business_date_local,
  COUNT(*) AS events_local,
  MIN(event_ts_utc) AS first_event_utc,
  MAX(event_ts_utc) AS last_event_utc
FROM parsed
GROUP BY business_date_local;

Notes: BigQuery will error on totally invalid parsed input formats; use SAFE.PARSE_TIMESTAMP or validate first. For ambiguous times BigQuery picks a deterministic rule (consult docs) — surface ambiguous cases by comparing interpretation of local time with applying two offsets is complicated; better to capture original offset if upstream can send it.

PostgreSQL implementation (example)- Store incoming local timestamp as timestamp without time zone plus tz name. Convert using AT TIME ZONE which returns timestamptz (UTC instant).- To get the local date for grouping, convert UTC timestamptz back to that timezone’s date.

Example:

sql

-- events(user_id, local_ts timestamp without time zone, tz text)
WITH converted AS (
  SELECT
    user_id,
    local_ts,
    tz,
    -- interpret local wall time as timestamptz by assuming the given tz
    (local_ts AT TIME ZONE tz) AT TIME ZONE 'UTC' AS event_ts_utc,
    -- get local date (safe way: convert UTC instant back to tz and take date)
    ( (local_ts AT TIME ZONE tz) AT TIME ZONE tz )::date AS business_date_local
  FROM events
)
SELECT business_date_local,
       COUNT(*) AS events_local,
       MIN(event_ts_utc) AS first_event_utc,
       MAX(event_ts_utc) AS last_event_utc
FROM converted
GROUP BY business_date_local;

Notes: In Postgres, the usual pattern is local_ts AT TIME ZONE tz => timestamptz; careful with double AT TIME ZONE patterns to get UTC or local date.

Handling ambiguous and non-existent times- Ambiguous (clock moved back): Postgres and BigQuery will choose a deterministic mapping; but business rules matter. Implement one of: - Require the client to send the UTC offset (e.g., -07:00) alongside local time. Use offset+local time to disambiguate. - Choose policy (prefer earlier or later) and document it; record ambiguous events in a quarantine table for manual review.- Non-existent (clock moved forward): either shift to the next valid instant (policy) or reject/log.

Data-quality and audit- Persist: original local_ts, tz id, optional provided offset, computed UTC instant, business_date_local, and a flag if conversion was ambiguous or non-existent.- Create a small QC pipeline to surface spikes around DST transitions and review policy impact.

Roll-up storage- Store a canonical UTC key for the business day (e.g., business_day_start_utc as TIMESTAMP) computed as the local date at 00:00 in tz converted to UTC: - BigQuery: TIMESTAMP(CONCAT(business_date_local, ' 00:00:00'), tz) - Postgres: (business_date_local::timestamp AT TIME ZONE tz)- Use that UTC key for central joins/BI cubes.

Why this works- Using timezone-aware conversion functions plus explicit policies ensures correct handling across DST boundaries.- Keeping raw inputs and an audit flag preserves traceability and allows correcting policy later without data loss.

HardTechnical

85 practiced

Multiple ETL workers can concurrently MERGE or upsert into a reporting table. Discuss how MERGE semantics vary across Postgres (ON CONFLICT), SQL Server (MERGE), and Snowflake (MERGE), and design an idempotent upsert pattern that avoids race conditions and ensures eventual consistency. Provide pseudocode for a safe upsert workflow.

Sample Answer

Short answer: MERGE/upsert semantics and concurrency differ by engine; safe idempotent upserts require deterministic conflict resolution (e.g., last-write-wins via version/timestamp), single atomic statement where possible, and retry or serialization control to handle races. One robust pattern: write to a staging table, dedupe per key, then perform a guarded atomic MERGE that only applies updates when the incoming version is strictly newer (or the target is absent). Optionally use application-level advisory lock for per-key serialization.

Key differences:- Postgres: ON CONFLICT DO UPDATE is atomic per-statement and honors unique constraints. You can add a WHERE clause on DO UPDATE to implement version checks. Use advisory locks (pg_advisory_xact_lock) or SERIALIZABLE transactions for stronger guarantees.- SQL Server: MERGE exists but historically had bugs and race issues; recommend using separate INSERT ... ON DUPLICATE pattern (MERGE with OUTPUT carefully) or use sp_getapplock for per-key locking or perform atomic UPDATE then INSERT-if-not-exists with transaction/retry.- Snowflake: MERGE is supported and atomic at statement-level; no row-level locks but statement-level serializability; include join condition and WHEN MATCHED AND target.updated_at < source.updated_at THEN UPDATE to avoid overwriting newer rows.

Safe idempotent upsert workflow (pseudocode):

sql

-- 1) Stage: ETL workers write batch to staging_table (id, payload, version_ts)
INSERT INTO staging_table (id, payload, version_ts) VALUES ...

-- 2) Deduplicate per id, keep latest in a staging_dedup view or temp table
WITH latest AS (
  SELECT DISTINCT ON (id) id, payload, version_ts
  FROM staging_table
  ORDER BY id, version_ts DESC
)
-- 3) Atomic MERGE / upsert with version check
MERGE INTO reporting_table AS tgt
USING latest AS src
  ON tgt.id = src.id
WHEN MATCHED AND tgt.version_ts < src.version_ts THEN
  UPDATE SET payload = src.payload, version_ts = src.version_ts
WHEN NOT MATCHED THEN
  INSERT (id, payload, version_ts) VALUES (src.id, src.payload, src.version_ts);

Additional safeguards:- Ensure reporting_table has UNIQUE(id).- Wrap MERGE in retry loop for serialization/statement-failure with exponential backoff.- For Postgres, you can instead: - Use ON CONFLICT (id) DO UPDATE SET ... WHERE reporting_table.version_ts < EXCLUDED.version_ts - Or acquire pg_advisory_xact_lock(hash(id)) for per-key serialization if strict ordering required.- For SQL Server, prefer UPSERT via UPDATE then INSERT-if-zero-rows or use sp_getapplock per key.

Why this works:- Deduping + version check makes operations idempotent (reapplying same batch has no effect).- The atomic MERGE/ON CONFLICT prevents partial state, and the version predicate prevents lost updates from concurrent older writes.- Retries and optional locks handle serialization anomalies and ensure eventual consistency.

MediumTechnical

62 practiced

Approximate aggregation functions (e.g., APPROX_COUNT_DISTINCT) are useful on very large datasets. Explain how APPROX_COUNT_DISTINCT in BigQuery differs from exact COUNT(DISTINCT) in Postgres, when you would accept approximation, and how to validate the error bounds for a given dataset.

Sample Answer

Situation (context): In BigQuery, APPROX_COUNT_DISTINCT is a probabilistic cardinality estimator (HyperLogLog-family/HyperLogLog++ implementation) that returns a fast, memory-efficient estimate of unique counts. In Postgres, COUNT(DISTINCT) computes the exact cardinality by hashing/aggregating all distinct values — accurate but potentially very slow and resource-heavy on large tables.

When to accept approximation:- Interactive dashboards where sub-1–3% relative error is acceptable for trends and KPIs.- Ad-hoc analytics over very large joins where exactness isn’t business-critical.- Pre-aggregation / monitoring (e.g., daily active users) where speed and cost matter more than a few percent.Avoid approximation when legal/financial exact counts, billing, or small-population decisions require exact numbers.

How to validate error bounds (practical steps):1. Stratified sampling: pick representative slices (low, medium, high cardinality).2. Compute exact vs approximate on samples or recent partitions and measure relative error.3. Sweep precision (if API allows) and observe error vs cost tradeoff.4. Ensure robustness across time/windows; error can vary with cardinality distribution.

Example SQL to compare on a sample partition:

sql

-- sample 1% of users, then compare
WITH sample AS (
  SELECT user_id
  FROM `project.dataset.table`
  WHERE RAND() < 0.01
)
SELECT
  COUNT(DISTINCT user_id) AS exact_count,
  APPROX_COUNT_DISTINCT(user_id) AS approx_count,
  SAFE_DIVIDE(ABS(APPROX_COUNT_DISTINCT(user_id) - COUNT(DISTINCT user_id)),
              COUNT(DISTINCT user_id)) AS relative_error
FROM sample;

Interpretation:- Run this across many samples/time windows to estimate mean and max relative error.- If relative error consistently within your tolerance (e.g., <2%), you can adopt APPROX_COUNT_DISTINCT for those reports.- If not, either increase estimator precision (where configurable) or fall back to exact counts for those specific metrics.

Key reasoning: APPROX methods trade deterministic accuracy for speed, lower memory, and cost. For BI use-cases focused on trends and high-cardinality tables, validating empirical error against business tolerance is the right pragmatic approach.

Unlock Full Question Bank

Get access to hundreds of Working with Different SQL Dialects interview questions and detailed answers.

Join thousands of developers preparing for their dream job.