Data warehouse support for experiments MVP

[danielbachhuber]

Done is:

• Customer should be able to pick a data warehouse table when selecting a goal for an experiment.
• The data warehouse table should be lazily-joined with the events table.
  • TBD whether the customer needs to do this on their own, or whether we should do it for them
  • Probably should validate that the events table is actually lazily-joined.
• When picking a data warehouse table for an experiment, it's possible to use a HogQL expression for the field.
• We've verified that the expected results are calculated for 'Total count', 'Property value (sum)', and other data warehouse metric aggregations. Any that don't work are hidden.
• Experiments work as expected for Trends queries.
• TBD support for funnels but potentially hide.
• It should be possible to replicate experiment results in an insight. Experiments should link to their corresponding insights.
• Documentation is written/updated.

[danielbachhuber]

Next up:

• Update data warehouse lazy joins to support experiment ASOF LEFT JOIN
• Verify expected results are calculated for 'Property value', etc.
• Explore support for funnels (potentially hide)
• Draft documentation

[danielbachhuber]

Some things I didn't do in #26446 that could be improved:

• Validate the modal fields (error if checkbox is checked but no Source Timestamp Key is specified)
• Lock the "Joining Table Key" to "distinct_id" when the checkbox is checked
• Warn in the experiment goal picker when the data warehouse table doesn't have a join

[danielbachhuber]

Some query notes:

• Need to resolve dots in series configuration: https://posthog.slack.com/archives/C019RAX2XBN/p1733332418425019
• If payments -> persons -> events, then the event property (and join) need to work correctly
• Update tests for payments -> persons -> events
• Uncertain about behavior for multiple distinct ids

[danielbachhuber]

Suggestion from @ivanagas:

Another idea, adding an "experiment joins" section here: posthog-git-experiments-data-warehouse-mvp-post-hog.vercel.app/docs/data-warehouse/join

[danielbachhuber]

Issue: "Unable to resolve field: 'person'" when filtering out internal and test users

[danielbachhuber]

Issue: "Unable to resolve field: 'person'" when filtering out internal and test users

I suppose this is because the query is trying to find person as a column on the data warehouse table, when it's actually located at event.person.

[danielbachhuber]

ClickHouse query finding in Metabase EU:

SELECT
    event_time,
    query_duration_ms,
    query,
    log_comment,
    read_rows,
    formatReadableSize(read_bytes) as read_size,
    result_rows,
    columns,
    query_id,
    exception,
    exception_code
FROM clusterAllReplicas(posthog, system.query_log)
WHERE
    query NOT LIKE '%query_log%'
    AND query LIKE '%ASOF%'
ORDER BY event_time desc
LIMIT 1000

[danielbachhuber]

Potential joins:

events: usage.userid -> events.properties.$user_id
persons: usage.userid -> person_distinct_ids.distinct_id

events: payments.distinct_id -> events.distinct_id
persons: payments.distinct_id -> person_distinct_ids.distinct_id

events: subscriptions.customer_id -> customers.id - customer.email -> persons.email - persons.id -> person_distinct_ids.distinct_id -> events.distinct_id
persons: subscriptions.customer_id -> customers.id - customer.email -> persons.email

[danielbachhuber]

Closing in favor of:

• Easy experiments support for Chargebee or Stripe data warehouse tables #26954
• Support for using a data warehouse table inside of a Funnels experiment #27009
• Fix "Filter internal and test users" when personsOnEventsMode = PersonsOnEventsMode.PERSON_ID_NO_OVERRIDE_PROPERTIES_ON_EVENTS #27132