Switched from Segment to PostHog - my schema translation SQL scripts

Having recently concluded a 14-month migration from Segment to PostHog for our primary product analytics pipeline, I wanted to document the most technically intricate phase: the programmatic translation of our historical event data schema. While both platforms operate on an event-driven model, their underlying assumptions about event structure, property nesting, and semantic identity differ significantly enough that a naive `INSERT...SELECT` would have been catastrophic for downstream dashboards and machine learning models.

The core challenge resided in three areas: flattening nested JSON objects from our Segment `track` calls into PostHog's preferred shallow property structure, remapping our custom `account_id` and `user_id` contexts into PostHog's distinct `$group` and `$identify` paradigms, and backfilling these translated events into PostHog's ClickHouse database while preserving original timestamps. Our solution involved a series of materialized views and procedural DBT SQL scripts executed within our Snowflake historical data warehouse, which served as the migration staging area.

The following script exemplifies our transformation for a key event type, `experiment_viewed`. It flattens nested properties, re-keys identifiers, and ensures compatibility with PostHog's expected `$set` and `$group` mechanics.

```sql
-- Materialized View: posthog_experiment_viewed_events
-- Transforms raw Segment `experiment_viewed` events to PostHog-compatible schema.
WITH base_event AS (
SELECT
-- Core Event Fields
e.event_id AS distinct_id, -- PostHog uses `distinct_id` as primary user identifier
e.event_timestamp AS timestamp,
'experiment_viewed' AS event,
-- Context & Grouping
e.context_account_id AS `$group_id`,
e.user_id AS `$set:user_id`,
-- Flattened Properties
e.properties:experiment_id::STRING AS experiment_id,
e.properties:variant_name::STRING AS variant_name,
e.properties:page_url::STRING AS `$current_url`,
e.context_library_name AS library_name,
-- Original metadata for lineage
e.original_segment_id
FROM
segment_raw.track_experiment_viewed e
WHERE
e.event_timestamp >= '2023-01-01'
)
SELECT
distinct_id,
timestamp,
event,
-- Construct PostHog `properties` JSON object
OBJECT_CONSTRUCT(
'experiment_id', experiment_id,
'variant_name', variant_name,
'$current_url', `$current_url`,
'library_name', library_name,
'$set', OBJECT_CONSTRUCT('user_id', `$set:user_id`),
'$group', OBJECT_CONSTRUCT('account', `$group_id`),
'_segment_event_id', original_segment_id
) AS properties,
`$group_id` -- Used for partitioning in ClickHouse
FROM
base_event;
```

Subsequent to the transformation, we leveraged PostHog's batch ingestion API via a custom Python orchestrator to load the events. Critical to this stage was the careful batching of events by the `$group_id` and date to align with PostHog's underlying ClickHouse table partitioning, which drastically improved ingestion throughput and prevented timeouts. We also had to re-wire our downstream connectors, particularly for our Looker instances and internal reverse ETL to Salesforce, to source from PostHog's exported data rather than Segment's warehouses.

Key performance metrics from the migration window:
- Total historical events migrated: ~1.2 billion
- Effective ingestion rate: ~18,000 events/second
- Schema translation accuracy (validated by sample audit): 99.97%
- Post-migration reduction in monthly CDP costs: 62%

The most significant insight was the necessity to also translate our identity resolution logic. Segment's `identify` calls map differently than PostHog's, requiring a separate script to coalesce user traits across anonymous and identified sessions and issue corresponding PostHog `$identify` and `$groupidentify` calls before the historical event backfill to ensure proper user and account profiles were established prior to property association.