Database Monitoring, Troubleshooting, and Diagnostics Questions

I would treat the investigation like a production read-only incident.

My approach:

• Start with a SELECT only query and verify I am connected to the correct environment and schema.
• Add tight filters first, such as a single date partition or a small key range, instead of scanning the full table.
• Use LIMIT, sampled subsets, or partition predicates to reduce load.
• Check the query plan before running anything expensive.
• Avoid UPDATE, DELETE, MERGE, or DDL statements unless I am in a controlled fix workflow.
• Run in a read replica or analytics copy if one exists.

Safety checks:

• Confirm transaction mode is read-only if supported.
• Set statement timeouts so a bad query does not run forever.
• Query metadata tables first for row counts, freshness, and partition coverage.

For a billion-row table, I would usually compare aggregates by day or partition before drilling into raw rows. That lets me validate whether the dashboard issue is a data freshness problem, a filter issue, or a metric logic problem without putting unnecessary pressure on the database.

1. Confirm column type and literal format

• Check schema: is event_date DATE, DATETIME/TIMESTAMP, or string?
• Quick queries:

-- PostgreSQL / BigQuery
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name='events' AND column_name='event_date';

If TIMESTAMP, equality to '2024-06-01' may not match.

2. Inspect a sample of values and timezones

SELECT DISTINCT CAST(event_date AS STRING) FROM events LIMIT 50;
SELECT MIN(event_date), MAX(event_date) FROM events;

Use psql/BigQuery console or Spark SQL to reveal time components/timezones.

3. Check partitions and query pruning behavior

• If table is partitioned by event_date, ensure partition exists and your query hits it.
• For Hive/BigQuery: list partitions (SHOW PARTITIONS events) or in BigQuery: SELECT partition_id FROM ...INFORMATION_SCHEMA.PARTITIONS.

4. Test range and cast-based queries

-- Match any time on that date
SELECT COUNT(*) FROM events
WHERE event_date >= '2024-06-01' AND event_date < '2024-06-02';
-- Or cast to date
SELECT COUNT(*) FROM events WHERE DATE(event_date) = '2024-06-01';

5. Verify data ingestion / freshness and lineage

• Check upstream pipeline runs (Airflow/GCP Composer, AWS Glue, Databricks jobs), last successful run and logs.
• Query raw/staging table to ensure rows made it to production.

6. Permissions, visibility and row-level filters

• Confirm your user/schema sees the table and no row-level security (RLS) hides records. Check grants and RLS policies (Postgres: pg_policy; BigQuery: IAM).

7. Check query planner/optimizer and server timezone/config

• Review query plan (EXPLAIN/EXPLAIN ANALYZE) for pushes to partitions.
• Confirm DB timezone settings and session timezone (SHOW TIME ZONE;) which can affect comparisons on TIMESTAMP columns.

Result: run the above incrementally, document findings, and fix: cast comparisons, correct partition filter, reload missing data, or adjust query to use date-range or DATE() cast.

Start with hypothesis-driven, reproducible checks and narrow scope (day, table, metric). Keep versions of every query you run.

1. Reproduce baseline

• Run the dashboard’s exact configured query/time-range (if available) or export its SQL/JSON.
• Run your raw report query for the same time window and granularity.

2. Check aggregation/pre-aggregation

• Compare raw base table vs pre-aggregated table.

SELECT COUNT(*) AS raw_rows, SUM(metric) AS raw_sum
FROM events
WHERE event_time BETWEEN '2025-11-01' AND '2025-11-30';

SELECT COUNT(*) AS agg_rows, SUM(metric) AS agg_sum
FROM agg_events_month
WHERE month = '2025-11';

• Check join keys and GROUP BY used when building aggregates; sample small partition and compare.

3. Check filter conditions

• Diff filters: list rows present in raw but excluded by dashboard filters:

SELECT * FROM events e
LEFT JOIN dashboard_filtered df ON e.id = df.id
WHERE df.id IS NULL AND e.event_time BETWEEN ...
LIMIT 100;

• Validate null handling, WHERE vs JOIN vs HAVING differences.

4. Timezone / truncation issues

• Compare event_time cast to dashboard timezone:

SELECT COUNT(*) FROM events
WHERE (event_time AT TIME ZONE 'UTC')::date = '2025-11-05';

• Check boundary days for timezone shifts; run counts by hour in both zones.

5. Deduplication

• Identify duplicates by dedup key(s):

SELECT key, COUNT(*) cnt
FROM events
WHERE event_time BETWEEN ...
GROUP BY key HAVING COUNT(*) > 1
LIMIT 50;

• Compare dedup logic used by dashboard (ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) = 1).

6. Narrow to examples and trace

• Pick 10 sample IDs where values differ; trace through raw -> ETL -> agg -> dashboard queries.
• Check pipeline run times and late-arriving data.

7. Quantify and fix

• For each cause compute its contribution to delta (e.g., missing pre-agg rows = X%).
• Implement fixes (adjust filter, timezone conversion, dedupe rules, re-run aggregation) and re-compare.

Document every query and result; produce reproducible notebook/dashboard for reviewers.

High-level goal: systematically isolate whether regressions come from missing/incorrect statistics, planner differences, or schema/partitioning/type changes. Work top-down: reproduce, collect evidence, run targeted experiments, and fix/mitigate.

1. Reproduce & baseline

• Pick representative failing queries (slowest regressions + some unchanged ones).
• Capture before/after EXPLAIN/EXPLAIN ANALYZE and runtime metrics (rows, time, memory).
  Example:
  sql

  EXPLAIN ANALYZE SELECT event_date, COUNT(*) FROM events WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY event_date;
  

2. Check schema differences

• Compare column types/nullability/defaults:
  sql

  SELECT column_name, data_type, is_nullable FROM information_schema.columns
  WHERE table_name='events' ORDER BY ordinal_position;
  

• Check partitioning definitions and partition pruning behavior:
  sql

  -- show partitions
  SELECT partition_name, partition_expression FROM information_schema.partitions WHERE table_name='events';
  

• Experiment: Cast types to previous types to see performance change:
  sql

  SELECT event_date::date, COUNT(*) ...;
  

3. Check statistics & cardinality

• Show table/column stats:
  sql

  -- Postgres example
  SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename='events';
  

• Run quick cardinality scans to compare with planner estimates from EXPLAIN:
  sql

  SELECT COUNT(*) FROM events WHERE event_date BETWEEN ...;
  SELECT event_date, COUNT(*) FROM events GROUP BY event_date LIMIT 10;
  

• If stats missing or stale, collect them and re-run:
  sql

  ANALYZE events;
  -- or sampling
  ANALYZE events WITH (SAMPLE_PERCENT = 10);
  

4. Isolate planner behavior

• Compare plans: look for different join order, lack of index/partition usage, wrong aggregation strategy (hash vs sort).
• Force alternative plans/hints to see impact:
  sql

  -- Example hint (engine-specific)
  SELECT /*+ USE_HASH_AGG() */ ...
  

• Run same query with planner disabled options (disable parallel, disable hashagg) to see sensitivity.

5. Targeted experiments

• Small-sample execution: run queries on a subset (WHERE event_date = single day) to see scaling.
• Run with statistics artificially set (where supported) or with ANALYZE to see improvement.
• Create a copy of table with schema exactly matched to old warehouse (types, nullability, partitions) and run queries for comparison.

6. Metrics & thresholds

• Record planner estimates vs actual rows and compute estimation error ratios.
• Track wall time, cpu, peak memory, and I/O.

7. Actions based on findings

• If stats: schedule regular ANALYZE, adjust sampling, add histograms or NDV estimates, maintain incremental stats.
• If planner: file bug/config change, add sensible hints or rewrite queries (e.g., pre-aggregate, use window functions), tune planner config (join_collapse_limit, enable_hashagg).
• If schema/partitioning: align schemas (types, nullability), re-partition or add partitioning columns/indices, update ETL to produce consistent layouts.

Summary: collect EXPLAIN/ANALYZE, compare cardinalities & stats, run schema diffs and cast experiments, force planner alternatives, then remediate (stats collection, planner tuning, or schema alignment).

I decide based on ownership, reproducibility, and whether the fix changes the business meaning of the data.

I can usually fix it with SQL when:

• It is a transformation bug in my pipeline, like a bad join, filter, or timezone conversion.
• The source data is correct, but my logic is wrong.
• I can validate the issue with a small reproducible query and patch it safely.

I escalate when:

• The source system is producing missing, late, or contradictory records.
• I see schema changes, upstream null spikes, or broken business rules I do not control.
• The issue requires permission changes, network fixes, or DB tuning.
• The analytics team’s metric definition is unclear or needs business sign-off.

Signals it is outside my scope include repeated load failures from the source, inconsistent row counts before my transformation starts, or evidence that the source-of-truth itself is wrong. In those cases, I document what I observed, share sample queries and timestamps, and hand it to the right owner with clear evidence instead of guessing.