Summary of meeting with @mpopov:

• Mikhail explained to me the basic model of GrowthBook.
• We went over an example 'fact table' definition, and we discussed how GrowthBook requires a timestamp TIMESTAMP column in order to do its thing.
• We saw examples of GrowthBook injecting into Presto SELECT statements predicates like WHERE timestamp > from_iso8601_timestamp('...').
• We discussed alternatives:
  • Presumably, if we had a source table that is already partitioned by such a TIMESTAMP column, we could accelerate GrowthBook. And since, as of now, Test Kitchen does indeed depend on multiple event streams, a pipeline that UNIONs these multiple event streams into one could help.
  • We also briefly discussed the possibility of just having multiple fact table definitions coming from the multiple event streams. This is interesting because, as mentioned elsewhere, there is draft work that could be finished that could just land the events into an Iceberg table that is already partitioned event time.

Additionally, I've also perused GrowthBook documentation at https://docs.growthbook.io/app/query-optimization#sql-template-variables, which seems to suggest that we could optimize fact table definition by telling GrowthBook how to apply partition predicates to the existing event tables:

If you have a date-partitioned table, you can use template variables within the SQL you enter in GrowthBook to provide better hints to your database. This applies to metrics, fact tables, and experiment assignment queries.

We should experiment with this templating mechanism as this seems like a faster way to get to where we want to be. Here I modify our running example fact table definition from the SDS doc:

SELECT
  experiment.subject_id AS subject_id,
  FROM_ISO8601_TIMESTAMP(
    CONCAT(
      CAST(year AS VARCHAR),
      '-',
      LPAD(CAST(month AS VARCHAR), 2, '0'),
      '-',
      LPAD(CAST(day AS VARCHAR), 2, '0'),
      'T',
      CAST(hour AS VARCHAR),
      ':00:00.000Z'
    )
  ) AS timestamp,
  experiment.enrolled AS experiment_id,
  experiment.assigned AS variation_id,
  meta.domain AS site_domain,
  mediawiki.database AS mediawiki_database,
  IF(performer.is_logged_in, 'Logged-in', 'Logged-out') AS user_auth_status,
  IF(
    agent.client_platform_family = 'desktop_browser',
    'Desktop',
    'Mobile'
  ) AS user_platform,
  mediawiki.skin AS mediawiki_skin
FROM
  event.mediawiki_product_metrics_contributors_experiments
WHERE
  experiment.coordinator = 'xLab'

  -- partition pushdown using GrowthBook SQL templating tuned for Presto:
  AND (year, month, day) >= ({{date startDateISO "yyyy"}}, {{date startDateISO "MM"}}, {{date startDateISO "dd"}})
  AND (year, month, day) <= ({{date endDateISO "yyyy"}}, {{date endDateISO "MM"}}, {{date endDateISO "dd"}})

Thank you so much for investigating that and proposing a short term solution! Once I added the partition pushdown to (1) experiment assignment queries and (2) all the fact tables, I saw huge performance gains – experiment analysis that was previously DNF at 8 minutes finished in under 2 minutes! It also helped with the "obtaining possible values of dimensions from last X days of traffic data" feature.

I also took the opportunity to finally fix timestamp so it's the actual timestamp:

FROM_ISO8601_TIMESTAMP(
    CONCAT(
      CAST(year AS VARCHAR),
      '-',
      LPAD(CAST(month AS VARCHAR), 2, '0'),
      '-',
      LPAD(CAST(day AS VARCHAR), 2, '0'),
      'T',
      CAST(hour AS VARCHAR),
-     ':00:00.000Z'
+     REGEXP_EXTRACT(meta.dt, '\d\d\d\d-\d\d-\d\dT\d\d(:\d\d:\d\d\.\d\d\d)', 1),
+     'Z'
    )
  ) AS timestamp,

The full experiment assignment query for subjects enrolled in Contributors experiments conducted by Editing and Growth teams is:

SELECT
  experiment.subject_id AS subject_id,
  FROM_ISO8601_TIMESTAMP(CONCAT(
    CAST(year AS VARCHAR), '-',
    LPAD(CAST(month AS VARCHAR), 2, '0'), '-',
    LPAD(CAST(day AS VARCHAR), 2, '0'), 'T',
    CAST(hour AS VARCHAR),
    REGEXP_EXTRACT(meta.dt, '\d\d\d\d-\d\d-\d\dT\d\d(:\d\d:\d\d\.\d\d\d)', 1),
    'Z'
  )) AS timestamp,
  experiment.enrolled AS experiment_id,
  experiment.assigned AS variation_id,
  meta.domain AS site_domain,
  mediawiki.database AS mediawiki_database,
  IF(performer.is_logged_in, 'Logged-in', 'Logged-out') AS user_auth_status,
  IF(
    agent.client_platform_family = 'desktop_browser',
    'Desktop',
    'Mobile'
  ) AS user_platform,
  mediawiki.skin AS mediawiki_skin
FROM
  event.mediawiki_product_metrics_contributors_experiments
WHERE
  experiment.coordinator = 'xLab'
  -- partition pushdown using GrowthBook SQL templating tuned for Presto:
  AND (year, month, day) >= ({{date startDateISO "yyyy"}}, {{date startDateISO "MM"}}, {{date startDateISO "dd"}})
  AND (year, month, day) <= ({{date endDateISO "yyyy"}}, {{date endDateISO "MM"}}, {{date endDateISO "dd"}})

The full query for the "Contributors actions" fact table:

SELECT
  experiment.subject_id AS subject_id,
  FROM_ISO8601_TIMESTAMP(CONCAT(
    CAST(year AS VARCHAR), '-',
    LPAD(CAST(month AS VARCHAR), 2, '0'), '-',
    LPAD(CAST(day AS VARCHAR), 2, '0'), 'T',
    CAST(hour AS VARCHAR),
    REGEXP_EXTRACT(meta.dt, '\d\d\d\d-\d\d-\d\dT\d\d(:\d\d:\d\d\.\d\d\d)', 1),
    'Z'
  )) AS timestamp,
  action,
  action_subtype,
  action_source,
  action_context,
  funnel_entry_token AS editing_session_id,
  page.namespace_id AS page_namespace_id,
  page.revision_id AS page_revision_id
FROM
  event.mediawiki_product_metrics_contributors_experiments
WHERE experiment.coordinator = 'xLab'
  -- partition pushdown using GrowthBook SQL templating tuned for Presto:
  AND (year, month, day) >= ({{date startDateISO "yyyy"}}, {{date startDateISO "MM"}}, {{date startDateISO "dd"}})
  AND (year, month, day) <= ({{date endDateISO "yyyy"}}, {{date endDateISO "MM"}}, {{date endDateISO "dd"}})

While this works as a short-term solution, I think from end-user experience – from perspective of Product Analytics who would be maintaining the fact tables – there is still a lot of value in a timestamp-partitioned Iceberg table with hidden partitioning because those are cumbersome queries.

By the way, I did try replacing

+  FROM_ISO8601_TIMESTAMP(meta.dt) AS timestamp,
-  FROM_ISO8601_TIMESTAMP(CONCAT(
-    CAST(year AS VARCHAR), '-',
-    LPAD(CAST(month AS VARCHAR), 2, '0'), '-',
-    LPAD(CAST(day AS VARCHAR), 2, '0'), 'T',
-    CAST(hour AS VARCHAR),
-    REGEXP_EXTRACT(meta.dt, '\d\d\d\d-\d\d-\d\dT\d\d(:\d\d:\d\d\.\d\d\d)', 1),
-    'Z'
-  )) AS timestamp,

while keeping your fantastic performance boosting partition pushdown and that change destroyed performance and the queries Did Not Finish.

@mpopov, I went over the doc and previous meeting. Wanted to summarize my understanding and get some clarifications.

UPDATE: Updated this comment with some corrections after meeting with @mpopov

Summary

• Various experiments stream data into tables that follow these schemas. Each schema corresponds to one or more table. product_metrics_web_base is the base table where ideally all/most experiment streams should land.
• The airflow pipeline resulting from this task would create an iceberg table that combines and saves all stream data in (possibly) a single table after some transformations. There are 2 steps to this:
  1. Optimizing: Have timestamp as a partition to speed up growthbook queries. Filter by experiment.coordinator IN ('default' 'xLab'), and save in a table. This would be a filtered and time-optimized view of the input table and is directly used by growthbook in various places.
  2. Transforming: Crunch the fact table assignment queries in airflow, so growthbook does not have to do heavy lifting. This would use the table saved in step 1 and create another table (e.g. experiment_assigment_table)
• UPDT: The two steps would create two separate tables.
• For the purposes of this task, we would be working on (optimizing, transforming) several tables. If more tables with different schemas pop up in the future, those will have to be appended into this pipeline.
  • Q: Maybe a concrete answer is not possible, how often do we expect to have new schemas and tables that would need to be added to the pipeline? This can help dictate if the tables should be processed together or independently.
    • A: Right now, we have 5 tables with 2 schemas. One schema being a subset of the other. The output table in step 1 would contain 1 extra column that would be null for a lot of rows due to this. Any future schema would have to be an extension of the base schema and will have similar effects of null values in columns. However, the expectation is that all experiments would be combined into just one schema and would land into just the base table in the mid-long term.
• Based on the meeting, there is prioritization in Experiment Platform team to not have multiple schemas/tables for experiment streams and soon only have the base table for growthbook usecase.
  • Q: What does the timeline look like? Is it soon enough for us to only handle the base table for this task? Or, we would be modifying the pipeline later to remove other tables.
    • A: We would have to take multiple table as input for now. We might deprecate all but the base tables later on.
• DE will be provided specific SQL queries that need to be crunched ahead of time in airflow to boost growthbook query time. Growthbook fact table assignment queries change the shape of all incoming data into the same shape.
  • Q: As far as I understand, we can take any data and transform it in any way so growthbook can calculate the relevant metrics for us. Currently we have these streams in some defined schemas, and have fact table assignment queries shape the data. Will these queries and source data schema always remain the same (aside from minor improvements/evolution)?
    • I guess what I am trying to understand is: are the SQL queries so rigid that they can be put in a pipeline? And they are not something dynamic to be modified in every other experiment we perform to fit the needs of those experiments?
    • A: The assignment queries will not change, so yes, they can be put into a pipeline. The fact table and metric queries are the more dynamic queries, and we are not worried about those right now.

Other thoughts

SELECT things
FROM table1

UNION ALL

SELECT things
FROM table2

UNION ALL

...

• Given a few different tables to ingest, we can:
  1. Do what has been done in growthbook so far, UNION ALL the transformed tables, but in the pipeline (the sql statement above ^). Adding new table would mean appending to this big SQL statement. This results in 1 output iceberg table.
  2. Crunch data individually for these tables as separate airflow tasks, and save each table as a separate partition in the table. (e.g. iceberg_table/table1/timestamp, iceberg_table/table2/timestamp). Adding new table would mean adding a task. This also results in 1 iceberg table.
  3. Have separate iceberg tables for each input table. And then UNION_ALL in growthbook.
     • This is better if growthbook wants to pick and choose tables instead of looking at all data all the time. Method <2> also works for this as there would be partitions for each input table.
     • UPDT: Growthbook will look at all data across tables for all experiments. So table specific optimizations are not required. Better to have all data in one table so everything is accessible from one place.
     • Q: @mpopov/@xcollazo: I think this has been discussed a bit before: Can we directly land the streams in a time partitioned iceberg table? So this would inherently handle the 'optimization' part.
       • Q: Also follow up: Do we know the speed up the optimization will give vs the transformation? If we land the data in a timestamp partitioned iceberg table, would we still need pipelines to performs transformations? or would that be handled by growthbook?
       • A: optimization (step 1 ) and transformation (step 2 ) should result in 2 separate tables. Table from 1 is required for sure. Table from 2 is good to have as part of this hypothesis.
• Q: How often should the pipeline run? hourly?
  • A: Yes, hourly.
• We would need jobs to delete data after 90 days as per standard retention policy and prune old iceberg snapshots.

I have a couple of questions if you don't mind!

Various experiments stream data into tables that follow these schemas. Each schema corresponds to one table. product_metrics_web_base is the base table where ideally all/most experiment streams should land.

IIUC, the desire is to have a single Iceberg table with specific partitioning containing the data from all of the various experiment event tables unioned together for Growthbook purposes, is that correct?

Since you'd be unioning the schemas anyway, did you consider just making a single stream with a unioned event JSONSchema? Is that what is meant by:

Based on the meeting, there is prioritization in Experiment Platform team to not have multiple schemas/tables for experiment streams and soon only have the base table for growthbook usecase.

?

I ask, because if so, then perhaps all we would need is for Refine to support Iceberg and custom partitioning: T377600: [refine] Add support for custom Hive Iceberg partitioning. We wouldn't need any TestKitchen or Growthbook specific Airflow pipelines. Just Refine to Iceberg with partition configuration.

@Ottomata

IIUC, the desire is to have a single Iceberg table with specific partitioning containing the data from all of the various experiment event tables unioned together for Growthbook purposes, is that correct?

That is correct. 1 timestamp partitioned iceberg table.

Since you'd be unioning the schemas anyway, did you consider just making a single stream with a unioned event JSONSchema? Is that what is meant by:

Experiment team is aiming for this, yes. To consolidate all experiments streams into one schema and one table.

Summary of requirements for this task:

• 1 iceberg table with timestamp partitioning so growthbook queries don't time out, AND filtering by action
• Possibly running the assignment queries in a pipeline to life load off of growthbook

T377600 would actually solve the biggest problem here: timestamp partitioning. All that would be left is to combine everything in one table (which would be done at the input level in the near future anyways), and assignment query table (which we can decide to do if we see growthbook is still taking too long).

All that would be left is to combine everything in one table (which would be done at the input level in the near future anyways),

This is sort of a question for me still. If the point is to have one table, why not have just one stream? You'd be unioning the schemas anyway, so could we make an uber event JSONSchema that has the schema the event tables will, and use that uber schema for the single event stream? Could we do this right now? I think it would not be difficult?

1. make uber union jsonschema in schemas-event-secondary
2. declare new testkitchen uber stream using uber schema
3. change all TestKitchen clients (hopefully via config) to produce all events to uber stream.

I'm sure I am missing something, but I think this would be less work in the short term than setting up a new custom airflow pipeline to manage a custom table.

Of course, that is all assuming we could quickly-ish enable RefineToIceberg and T377600: [refine] Add support for custom Hive Iceberg partitioning. FWIW, we have been meaning to do this at least since Summer 2024 (see T367057: [SPIKE] Document decision to use a single table per base schema and comments).

Summary from Slack thread:

1. Experiment Platform team is willing to move T408186: Configure experiments with stream, schema and contextual attributes forward, effectively making their current set of event streams go from N streams to 1. AIUI, doing this work also means there is no need to do any fancy unioning of schemas.
2. After (1), we have two choices to move this ticket forward:
   1. We can create a new way to land any stream as an Iceberg table.@Ottomata estimates a focused effort could get this path to work in ~1 month. This effort requires someone with extensive Refine experience.
   2. We could also instead create a now simplified pipeline that would just consume the one event stream from (1) and do the transformations that Experiment Platform wants to see. Such a simplified pipeline could be done in 2 weeks by anyone from DE.
3. As noted in the Slack thread, 2.A has significant long term value, as we would have moved Refine forward with Iceberg integration that had been missing, while doing 2.B only benefits Experiment Platform. Regardless of 2.A or 2.B, doing (1) first makes everyone’s lives easier.

Summarizing the decisions so far:
1/ T408186 will be in Experiment Platform teams sprint commitment for their next sprint, which starts 2026/01/30

2/ Growthbook queries do or require the following:

• require timestamp partitioning
• filter by action
• execute assignment queries (its some sql query)

We don't know the speed up each of these components provide, individually. The ask was to do all of these in dag(s). The main/big problem is still the timestamp. If we only do RefineToIceberg (2.A from previous comment), growthbook can continue to do filtering and assignment query. But whether it would still be too slow, I am not sure (would be nice to benchmark query times for concrete numbers) .

The argument to complete RefineToIceberg is

• to complete a long standing task that will help this and a lot of future requirements
• not have a copy of input tables just for primarily partitioning purposes
• creating additional tables will require maintenance and pruning
• if the filtering and assignment queries do not have a significant speed boost for growthbook, it does not make sense to have additional pipelines for this

If we filtering and assigment queries are hard requirements, then RefineToIceberg is not directly helpful right now, as we need to have growthbook specific dags anyways.

@mpopov is in agreement with doing RefineToIceberg only. This means it will take us longer and would require Refine expertise, but will help long term. After that, if we find the growthbook queries need more speed up and we need to derive any additional tables from it, then we can do that separately as the needs emerge.

Questions remain about timeline and priorities: @Ahoelzl to help identify if the hypothesis can handle the time taken to complete RefineToIceberg and headcount (re-)distribution.

Decision Log (Slack): Due to people capacity constraints, concerns over RefineToIceberg taking over a month, and immediate Growthbook needs we have decided to move forward with dedicated pipelines for now.

After RefineToIcebergis done, we would be decommissioning (parts of, or the full) dedicated growthbook pipeline: T415826: Decomission dedicated growthbook airflow pipeline(s)

Change #1237249 had a related patch set uploaded (by AKhatun; author: AKhatun):

[analytics/refinery@master] Add hql files for new growthbook pipeline

https://gerrit.wikimedia.org/r/1237249

akhatun opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1976

Add growthbook dag for events and assignment queries

Change #1237249 abandoned by AKhatun:

[analytics/refinery@master] Add hql files for new growthbook pipeline

Reason:

Hql files will reside in https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines

https://gerrit.wikimedia.org/r/1237249

akhatun opened https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines/-/merge_requests/46

Add hql files for new growthbook pipeline

akhatun updated https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines/-/merge_requests/46

Add hql files for new growthbook pipeline

bearloga merged https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines/-/merge_requests/46

Add hql files for new growthbook pipeline

akhatun merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1976

Add growthbook dag for events and assignment queries

Done:

• MR for HQL files in product-analytics/data-pipelines repo is merged.
• Airflow dag has been merged into airflow-dags/analytics_product repo following that.

Creating Production Tables:

Running the following commands to create prod tables:

akhatun@stat1008:~$ sudo -u analytics-product bash

analytics-product@stat1008:/srv/home/akhatun$ kerberos-run-command analytics-product spark3-sql

spark-sql (default)> use wmf_experiments;

spark-sql (default)> show tables;
database	tableName	isTemporary
wmf_experiments	experiment_results_v1	false
wmf_experiments	experiment_results_v1_test	false
wmf_experiments	experiments_registry_v1	false
wmf_experiments	metrics_catalog_v1	false
Time taken: 0.387 seconds, Fetched 4 row(s)

Copied create table HQL from here.

spark-sql (default)> CREATE EXTERNAL TABLE IF NOT EXISTS `experiment_event_v1`(
                   >     `_schema`                        string                                                                   COMMENT 'Schema version',
                   >     ... 
Response code
Time taken: 1.681 seconds

Copied create table HQL from here.

spark-sql (default)> CREATE EXTERNAL TABLE IF NOT EXISTS `experiment_assignment_v1`(
                   >     `subject_id`             string    COMMENT 'Experiment subject identifier',
                   >    ... 
Response code
Time taken: 0.134 seconds

Confirming table creation:

spark-sql (default)> show tables;
database	tableName	isTemporary
wmf_experiments	experiment_assignment_v1	false
wmf_experiments	experiment_event_v1	false
wmf_experiments	experiment_results_v1	false
wmf_experiments	experiment_results_v1_test	false
wmf_experiments	experiments_registry_v1	false
wmf_experiments	metrics_catalog_v1	false
Time taken: 0.028 seconds, Fetched 6 row(s)

Checking file path creation and permissions:

akhatun@stat1008:~$ hdfs dfs -ls /wmf/data/wmf_experiments
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8
Found 6 items
drwxr-x---   - analytics-product analytics-privatedata-users          0 2026-02-19 18:30 /wmf/data/wmf_experiments/experiment_assignment_v1
drwxr-x---   - analytics-product analytics-privatedata-users          0 2026-02-19 18:28 /wmf/data/wmf_experiments/experiment_event_v1
drwxr-x---   - analytics-product analytics-privatedata-users          0 2025-04-22 20:22 /wmf/data/wmf_experiments/experiment_results_v1
drwxr-x---   - analytics-product analytics-privatedata-users          0 2025-05-12 18:43 /wmf/data/wmf_experiments/experiment_results_v1_new
drwxr-x---   - analytics-product analytics-privatedata-users          0 2025-04-29 21:22 /wmf/data/wmf_experiments/experiments_registry_v1
drwxr-x---   - analytics-product analytics-privatedata-users          0 2025-04-29 20:54 /wmf/data/wmf_experiments/metrics_catalog_v1

Turning on DAG:

Done. Backfill complete.

Turning on Maintenance DAGs:

Done, Thanks @Snwachukwu.

Backfill complete. We now have data for ~20 days.

Number of files and total size (~3GiB) for event table:

akhatun@stat1008:~$ hdfs dfs -ls /wmf/data/wmf_experiments/experiment_event_v1/data/* | grep parquet | wc -l
373

akhatun@stat1008:~$ hdfs dfs -ls /wmf/data/wmf_experiments/experiment_event_v1/data/* | grep parquet | awk {'print $5'} | awk '{ sum+=$1; }END{print sum;}'
3020793101

Number of files and total size for assignment table (~0.5GiB):

akhatun@stat1008:~$ hdfs dfs -ls /wmf/data/wmf_experiments/experiment_assignment_v1/data/* | grep parquet | wc -l
371

akhatun@stat1008:~$ hdfs dfs -ls /wmf/data/wmf_experiments/experiment_assignment_v1/data/* | grep parquet | awk {'print $5'} | awk '{ sum+=$1; }END{print sum;}'
542158755