Troubleshooting SQL Queries

A SQL query can fail to execute, return no rows, or produce a plausible but incorrect result. Treat those as different problems. Start with the smallest read that proves the table, schema, and time range, then restore the analytical logic one step at a time.

Telemetry uses Apache DataFusion SQL. Syntax copied from PostgreSQL, BigQuery, Snowflake, MySQL, or another engine may need to be rewritten even when the analytical idea is sound.

1. Read the response class

For the synchronous Query API:

• 400 usually means invalid JSON, missing SQL, incompatible SQL, a missing table or field, or a type error
• 401 means the API key is missing or invalid
• 403 means the key lacks read scope
• 429 means the request should wait and use bounded backoff
• 5xx may be retried a limited number of times

Do not retry unchanged invalid SQL. Capture the safe error text, endpoint, and request ID, but never log the key merely because a query failed.

For asynchronous queries, distinguish queued, running, completed, and failed. A completed job still needs an active download_url before the client can retrieve the file. See Exporting Query Results.

2. Prove the table and time range

Replace the original query with a bounded sample:

SELECT *
FROM api_requests
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
ORDER BY timestamp_utc DESC
LIMIT 10;

If this returns no rows:

1. verify the normalized table name
2. widen the UTC time range
3. remove optional filters
4. inspect recent sample rows and the schema in the table view
5. send a synthetic event with a unique identifier

If the synthetic row is also missing, follow Troubleshooting Event Ingestion before editing the SQL further.

3. Compare field names and types

Do not infer a column name from application code. Inspect the table schema, including nested dotted paths and exact types.

Common errors include:

• querying timestamp instead of the generated timestamp_utc
• using duration when the event contract stores duration_ms
• comparing an integer status code with a string
• treating a numeric identifier as text after a producer changed type
• referencing a nested path without the identifier form required by the stored schema
• aggregating a field that is not numeric

Start with the exact field in a simple SELECT. Then add a comparison, function, or aggregate. This identifies whether the problem is column resolution or the operation applied to the column.

4. Reduce the query by layers

For a query with several CTEs, joins, and windows:

1. run the first CTE by itself
2. add the next CTE and inspect its row grain
3. test each join with identifiers and row counts visible
4. add aggregation after the joined rows are correct
5. add window calculations last

Use temporary diagnostic columns such as COUNT(*) and COUNT(DISTINCT account_id) to detect row multiplication. Remove them only after the result grain is understood.

5. Translate unsupported dialect syntax

Use the syntax demonstrated in the DataFusion SQL Reference and tested recipe library. In particular:

• use SUM(CASE WHEN condition THEN 1 ELSE 0 END) for conditional counts
• use approx_percentile_cont(latency_ms, 0.95) for approximate p95
• use date_trunc('hour', timestamp_utc) for time buckets
• use interval arithmetic such as now() - INTERVAL '24 hours'
• protect ratios with NULLIF(denominator, 0)
• use LAG(value) OVER (ORDER BY bucket) for prior-bucket comparisons

Function names and signatures are dialect-specific. A function with the same purpose in another database is not evidence that DataFusion accepts that spelling.

6. Diagnose empty results

An empty result is often a filter interaction, not missing data. Remove predicates one at a time:

SELECT
  environment,
  status,
  COUNT(*) AS events
FROM workflow_events
WHERE timestamp_utc >= now() - INTERVAL '7 days'
GROUP BY environment, status
ORDER BY events DESC;

This exposes the values actually stored. Check case, whitespace, nulls, renamed statuses, environment boundaries, and incomplete recent periods before restoring a narrow predicate.

For funnels and cohorts, confirm that every milestone uses the same identity unit and that the observation window is long enough for later steps to occur.

7. Audit joins before totals

Write down what one row represents on each side. If both sides contain multiple rows per identifier, a direct join may multiply revenue, event counts, or active users.

Pre-aggregate each side to the intended grain:

WITH requests AS (
  SELECT
    request_id,
    MIN(timestamp_utc) AS requested_at
  FROM request_events
  GROUP BY request_id
),
outcomes AS (
  SELECT
    request_id,
    MAX(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS succeeded
  FROM outcome_events
  GROUP BY request_id
)
SELECT
  COUNT(*) AS requests,
  SUM(COALESCE(outcomes.succeeded, 0)) AS succeeded
FROM requests
LEFT JOIN outcomes ON requests.request_id = outcomes.request_id;

Compare distinct identifiers before and after the join. Include unmatched rows deliberately rather than allowing an inner join to erase missing outcomes.

8. Validate rates, percentiles, and windows

A rate requires both numerator and denominator. Return them beside the percentage until the calculation is trusted, and set a minimum meaningful volume before ranking groups.

A percentile should use a numeric field with one documented unit. Compare p50 and p95 to understand whether the whole distribution or only its tail changed.

Rolling windows have fewer observations at the beginning of the series. The newest time bucket may be incomplete. Label or exclude both conditions before alerting on a rolling baseline.

9. Prove the expected result with fixtures

Create a small synthetic fixture whose answer can be calculated by hand:

• one success
• one permanent failure
• one failure that later recovers
• one duplicate identifier
• one null optional field
• one event exactly on a time boundary

Run intermediate CTEs and compare the final rows to that expected answer. Planning proves syntax and types; fixtures help prove interpretation.

Query review checklist

• exact table and schema inspected
• UTC window and bucket completeness confirmed
• field types and units match the operations
• row grain documented before every join
• numerator, denominator, and minimum volume visible
• retries, duplicates, late events, and nulls have explicit behavior
• synthetic boundary cases match expected output
• saved result includes a definition, owner, and response

Telemetry publishes the engine version and automated checks in the SQL testing methodology. Start from a recipe in API reliability, background jobs, product analytics, or data quality when you need a known-compatible pattern.