mongo-ai/posthog-data-warehouse-diagnosis

Data warehouse diagnosis workflow

Before you diagnose: fetch live docs

1. Run docs-search("data warehouse troubleshooting {specific symptom}")
2. If source-specific: run docs-search("data warehouse {source type} setup")
3. If potential known bug: run gh search issues --repo PostHog/posthog "data warehouse {symptom}"
4. Use the fetched docs as your primary reference — NOT the steps below

The steps below are a diagnostic FRAMEWORK (what questions to ask, in what order). The docs responses are the CONTENT (current answers, version requirements, config details).

Goal

Determine whether the data warehouse problem is caused by:

• external source sync failure (credentials, permissions, schema change)
• query error (HogQL syntax, missing table, column mismatch)
• join misconfiguration (wrong key, type mismatch)
• stale or missing data (sync not running, incremental sync gap)
• performance issue (query timeout, large table scan)
• S3/GCS specific issues (archived files, path patterns, format mismatch)
• known product bug

Step 1: Classify the symptom

Separate:

• "Sync is failing" → Step 2
• "Query returns wrong results or errors" → Step 3
• "Data is missing or stale" → Step 4
• "Query is slow or timing out" → Step 5
• "Can't set up a new source" → Step 6

Step 2: Sync failures

Run docs-search("data warehouse troubleshooting sync failing")

Key areas to investigate:

• Error message — hover over the error in the Status column on the source page
• Sync logs — expand the failed sync in the Syncs tab for full logs
• Credentials — expired tokens, rotated keys, revoked permissions
• Schema changes — source table altered, columns renamed or removed
• Network — firewall rules, IP allowlisting for PostHog's ingestion IPs
• S3 archived files — if error mentions "archived files", objects may be in Glacier/Deep Archive

Common sync error patterns

| Error pattern | Likely cause | |--------------|-------------| | Authentication/credentials | API key expired, OAuth token revoked, IAM permissions changed | | Permission denied | Source database user lacks SELECT on the table | | Table/schema not found | Table renamed, schema changed, or wrong database selected | | Connection timeout | Firewall blocking PostHog IPs, source database unreachable | | Some files archived | S3 objects in Glacier tier — need to restore or exclude | | Rate limit | Source API throttling — PostHog syncs may need to be spaced out |

Step 3: Query errors

If the complaint is HogQL query errors on warehouse tables:

-- Check what warehouse tables exist
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'warehouse'
LIMIT 50;

Key areas to investigate:

• Table name — warehouse tables use the source name, not the original table name
• Column names — may differ from the source; check the schema in the Data Warehouse UI
• Property access — warehouse tables use direct column access, NOT properties.$name syntax
• Join issues — verify the join key exists in both tables and types match
• Date types — warehouse timestamps may need casting with toDateTime()

Step 4: Missing or stale data

If data exists but is outdated or incomplete:

• Check sync schedule — is the sync set to run frequently enough?
• Check last successful sync timestamp — is it recent?
• Check if incremental sync is configured — full syncs catch everything, incremental may miss backfills
• Check if the source data was recently modified — warehouse syncs on a schedule, not real-time

-- Check when the latest warehouse data was synced
SELECT max(timestamp_column) FROM warehouse_table_name
LIMIT 1;

Step 5: Performance issues

If queries are slow or timing out:

• Run docs-search("data warehouse query performance")
• Check table size — very large external tables may need filtering
• Always include time range filters in WHERE clauses
• Use LIMIT during investigation
• Avoid SELECT * on large warehouse tables
• Consider materializing frequently-used views

Step 6: New source setup issues

Run docs-search("data warehouse setup {source type}")

Key areas to investigate:

• Supported source types (Stripe, Hubspot, Postgres, S3, etc.)
• Required credentials and permissions
• IP allowlisting requirements
• Schema mapping expectations

Step 7: Search known issues

Search GitHub with at least 2 variants:

• gh search issues --repo PostHog/posthog "data warehouse {symptom}"
• gh search issues --repo PostHog/posthog "warehouse sync {source type}"

Common resolution patterns

• Sync failing → credentials expired, permissions changed, or schema altered at source
• Query errors → wrong table/column name, missing join, or syntax issue
• Stale data → sync not running or incremental sync missing recent changes
• Slow queries → missing time range filter or scanning too much data
• S3 issues → archived files, wrong path pattern, or unsupported file format