Connectors & Integration Troubleshooting Guide

Welcome — purpose and how to use this guide

This guide helps engineers, integrators, analysts, and site operators build reliable connectors and recover them quickly when they fail. It is intentionally practical: checklists you can follow during onboarding, patterns to avoid common failure modes, and runbook steps to diagnose and remediate incidents. Use the checklists for sourcing credentials and security reviews, the patterns to design robust connectors, and the runbooks to reduce downtime and preserve data integrity.

How this guide maps to your hunger

• Primary hunger: shorten time-to-onboard new sources and reduce recurrent ingestion incidents.
• Secondary outcomes: more predictable integrations, fewer duplicated efforts, clearer ownership, better observability, and faster recovery.

1. Authentication & credentials checklist

Before you configure a connector, verify this list. Missing or inconsistent credentials are the most common cause of flaky connectors.

1. Credential type: confirm the exact auth flow required (API key, OAuth2 client credentials, OAuth2 authorization code, HMAC, Basic auth, service account key). Document it in the source card.
2. Least privilege: request scoped credentials that grant only the required permissions (read-only where possible).
3. Rotation & expiry: capture expiry dates and rotation procedure. Add the next-rotation reminder to monitoring/alerts.
4. Environment separation: use separate credentials for dev/test/prod and tag them clearly.
5. Network requirements: confirm IP allowlists, VPN, VPC peering, or proxy settings. Test connectivity from the target runtime environment.
6. Certificates: capture any required client or CA certificates and their validity windows.
7. Test credentials: validate credentials with a minimal smoke request that returns expected schema metadata or a 200 status.

2. Rate limiting and backoff patterns

Respecting source rate limits prevents transient failures and service bans. Use these defensive patterns.

• Exponential backoff with jitter: implement exponential backoff and add randomized jitter to avoid thundering herds. Typical base retries: 3–5 with doubling delays.
• Adaptive throttling: detect 429/503 responses and slow ingestion window dynamically; lower concurrency or switch to chunked reads.
• Client-side rate limits: enforce a client-side QPS limit in addition to reacting to server signals.
• Retry idempotency: design retry-friendly operations. If writes are repeated, ensure idempotent keys or dedup logic.
• Monitoring: create metrics for 429/5xx rates, retry counts, and average latency. Alert when retry rate increases beyond baseline.

3. Schema drift handling

Schemas change. Plan for it instead of firefighting.

1. Contract-first approach: where possible negotiate a semantic contract with the source owner (field names, types, required fields).
2. Loose ingestion + validation layer: ingest into a raw staging table (store JSON) and validate downstream. This avoids breaking pipelines on minor changes.
3. Automated schema detection: run daily diffs of observed schema vs expected schema. Highlight added, removed, or type-changed fields.
4. Transformation rules: keep a mapping layer with explicit field mappings, type coercion rules, and safe fallbacks.
5. Change approval flow: when drift is detected, create a change ticket that includes a sample payload, suggested mapping, and impact analysis.

4. Incremental ingestion tips

Incremental strategies reduce load and make restarts predictable.

• Watermarking: use a stable, monotonic watermark (last_modified timestamp, incremental numeric id, or source change token). Persist watermark durable to resume reliably.
• CDC (Change Data Capture): where supported, prefer CDC streams to full extracts. Ensure ordering guarantees and at-least-once semantics are understood.
• Clock skew handling: if using timestamps, handle skew by overlapping windows on resume (e.g., subtract a safety interval) and deduplicate downstream.
• Windowing & chunk size: tune page sizes and time windows to balance latency, throughput, and rate limits.
• Atomic commits: write incremental batches atomically and mark checkpoints only after successful writes and validation.

5. Backfill strategies

Backfills are inevitable. Plan for them to avoid data duplication and long maintenance windows.

1. Idempotent writes: prefer upserts using unique keys or dedupe logic based on source ids + timestamps.
2. Chunked backfills: break backfills into time or id ranges to keep resource usage predictable and allow partial progress tracking.
3. Isolation: run backfills into separate staging tables, validate, then swap/merge to production.
4. Progress tracking: expose a backfill status with ranges processed, error counts, and estimated time remaining.
5. Reconciliation: after backfill, run reconciliation checks (counts, checksums) before marking pipeline healthy.

6. Sample validation queries (post-ingestion)

Use these to verify basic integrity after ingestion.

• Row count comparison: compare source-reported counts to destination counts by day or partition.
• Null field checks: find unexpected nulls in required fields.
• Checksum / hash comparison: compute a checksum of a key set to detect silent corruption.
• Latency distribution: measure time between source event timestamp and ingestion time.

-- Example: daily row counts by source partition
SELECT source_partition, COUNT(*) AS dest_count
FROM destination_table
WHERE ingestion_date = '{{date}}'
GROUP BY source_partition;

-- Example: find records missing required id
SELECT COUNT(*) FROM destination_table WHERE id IS NULL AND ingestion_date = '{{date}}';

7. Troubleshooting runbook — step-by-step

Use this runbook when an ingestion alert fires.

1. Identify
   • Check ingestion metrics: error rate, retry count, latency, and 4xx/5xx split.
   • Collect recent connector logs and the first failing request payload/response.
2. Classify — decide which bucket the failure fits:
   • Authentication/authorization
   • Rate limit / throttling
   • Schema drift / validation failure
   • Network / DNS / TLS
   • Destination write failures (quota, permission)
   • Transient source errors
3. Isolate
   • Re-run a single failing request with verbose logging to capture headers and raw response.
   • If rate limit, reduce concurrency and validate backoff behavior.
4. Resolve
   • Auth: refresh or replace credentials and confirm with a smoke test.
   • Schema: update mapping or add a transformation stage; run validation on a subset.
   • Rate limits: implement/adjust backoff and reschedule catch-up windows.
   • Network: work with infra to clear firewall, DNS, or TLS certificate issues.
5. Recover
   • Perform targeted replays for missing time ranges using idempotent/merge operations.
   • Run reconciliation queries to compare before/after counts and checksums.
6. Document
   • Create a postmortem entry: root cause, mitigation, permanent fix, monitoring added, and owners assigned.

8. Observability & alerts (what to monitor)

• Connector health: last successful run timestamp, last error timestamp, consecutive failures.
• Throughput and latency: rows/sec, avg request latency, downstream write latency.
• Error breakdown: 4xx/5xx counts, retry counts, backoff triggers.
• Data quality signals: daily row-count delta, schema change events, null-rate spikes on required fields.
• Operational alerts: credential expiry (30/7/1 days), destination quota thresholds.

9. Onboarding checklist (quick)

1. Record contact/owner and SLAs for the source.
2. Complete authentication checklist and verify smoke request.
3. Agree on expected schema and sample payloads.
4. Decide incremental strategy and watermark field.
5. Configure monitoring, alerts, and runbook ownership.
6. Run full end-to-end test and reconciliation on a short historical window.

10. Common pitfalls and quick remedies

• Intermittent 401s: often credential expiry or clock skew — verify token refresh and system clocks.
• Sudden drop in rows: check schema changes, vendor-side filters, or new default limits (page size changes).
• High retry rates: implement backoff with jitter; check for hidden 429s behind generic 5xx responses.

11. Where to take this next (capability opportunities)

To make this guide more actionable inside THE, consider converting the onboarding and troubleshooting checklists into interactive forms that:

• Capture source metadata, credentials, watermarks, and owner contacts when onboarding a connector.
• Store smoke-test results and verification artifacts via the platform's submission capability so teams can track progress and history.
• Attach runbook templates to alerts so responders can follow the exact steps and record outcomes.

12. Quick references

Keep a short list of useful query templates, standard error responses to watch for, and escalation contacts in a connector's documentation card.

End of guide. Use this as a living document: update mappings, thresholds, and runbooks each time you learn from an incident.