Reconciling PostHog counts with a Redshift report.
Compare the same event population, grain, timezone, and data cutoff before reconciling PostHog with Redshift. Investigate missing and duplicate event IDs first, then identity and query rules; matching totals alone does not establish that the underlying facts agree.
Inside this article 5 sections +
Reconcile PostHog and Redshift by comparing the same event population at the same reporting grain and observation cutoff. Start with event identifiers and timing, then examine identity rules, exclusions, and query semantics. Matching headline totals is the final check, not the investigation method.
A product dashboard and a warehouse report can disagree while both execute their configured queries correctly. One may count people, another events; one may use occurrence time, another arrival time; one may exclude test accounts that the other retains.
Freeze the question before comparing counts
Write a comparison contract containing the event name and version, date interval, timezone, entity being counted, account filters, and data cutoff. Use a half-open interval such as midnight on one day through, but excluding, midnight on the next. This avoids counting a boundary event in two adjacent daily reports.
State whether the metric counts raw deliveries, unique event IDs, distinct people, distinct accounts, or completed workflows. For an illustrative export report, use unique successful export event IDs for customer production accounts. A browser “export clicked” event is a different population and should not be made to match by adjusting filters.
Save the exact query and dashboard settings with the comparison. A live dashboard that continues receiving events can change while an analyst investigates a warehouse snapshot. An explicit cutoff makes the result reproducible and separates ingestion delay from lost data.
| Comparison stage | PostHog | Warehouse | Interpretation |
|---|---|---|---|
| Initial display | 1,000 | 1,030 | Definitions still unaligned |
| Remove warehouse retries | 1,000 | 1,010 | 20 repeated event IDs |
| Apply same test-account filter | 1,000 | 995 | 15 ineligible warehouse events |
| Apply agreed arrival cutoff | 995 | 995 | 5 later arrivals excluded from comparison |
Compare event sets before aggregates
Carry a stable event ID from the application into each destination. Retrieve the IDs for the agreed slice and identify events present only in PostHog, only in Redshift, and more than once in either source. A full comparison is ideal for a bounded slice; deterministic sampling helps when the volume is larger.
Sample by a repeatable rule on event ID rather than taking the first rows returned. Include a few complete account journeys so the investigation can see related operations. Record the sampling rule and do not treat a clean sample as proof that every event matches.
For each mismatch, inspect the original payload, destination delivery attempts, occurrence time, arrival time, and schema version. This often distinguishes a genuine missing event from a query filter or a duplicate ingestion attempt. Preserve raw evidence until the discrepancy has an explained disposition.
Inspect identity and time as separate dimensions
Person merging can change distinct-person counts without changing event counts. Account switching can misattribute events if browser group state is stale. Current account properties can also change a historical filter. Use event-time account context where the metric requires historical attribution, as described in B2B analytics identity.
Keep occurrence time and received time separate. A late event belongs to its business day when measuring customer behavior, but it belongs to a later arrival interval when monitoring ingestion. Comparing those two clocks produces a discrepancy that no deduplication rule can fix.
Timezone differences are especially visible near midnight. Choose the reporting timezone once and test records immediately before and after the boundary, including a daylight-saving transition if the business report uses a local timezone. UTC storage does not remove the need to define local reporting semantics.
Repair the layer that is wrong
If the warehouse contains repeated event IDs, deduplicate the canonical fact layer according to an explicit rule. Redshift constraints should not be assumed to enforce event uniqueness for ingestion. Prepare the staging data before a merge so multiple source rows do not create ambiguous matches.
If an event is truly missing, repair the destination delivery with the original event ID and timestamp. Keep lifecycle destinations outside a warehouse backfill unless they have a deliberate replay policy. If the event is present but the metric definition differs, change the definition and record the change instead of altering raw history.
Some differences are intentional: privacy deletion, retention settings, or distinct access scopes may make identical historical populations unavailable. Record that boundary and choose an appropriate comparison period. Do not reconstruct deleted personal data simply to make two dashboards agree.
Make reconciliation a small recurring check
Automate a bounded daily comparison with counts of missing IDs, duplicate IDs, late arrivals, and schema failures. Define who investigates and when delayed data becomes an incident. Keep a small known fixture through both destinations so query changes can be checked independently of production variation.
The product event architecture guide establishes destination responsibilities. Software implementation can make reconciliation part of the pipeline, giving product and data teams a shared explanation of the numbers they use.