Hi team, does growth book support dt/hr partition? We are using the automated generated queries, whi...

most-midnight-63385

10/03/2023, 11:03 PM

Hi team, does growth book support dt/hr partition? We are using the automated generated queries, which turns out slow as they are scanning entire history of test tables

➕ 1

👀 1

fresh-football-47124

10/04/2023, 4:27 AM

we have some improvements in the query speed coming

fresh-football-47124

10/04/2023, 4:27 AM

but there are ways to do date partitions

helpful-application-7107

10/04/2023, 4:04 PM

What data warehouse are you using?

helpful-application-7107

10/04/2023, 4:05 PM

We definitely support scanning only relevant partitions, but in some set ups it might require fine tuning the queries.

fast-wire-759

10/04/2023, 4:41 PM

We have a self managed cluster running on AWS

fast-wire-759

10/04/2023, 4:42 PM

@most-midnight-63385 please follow up @fresh-football-47124 @helpful-application-7107

most-midnight-63385

10/04/2023, 4:53 PM

@fresh-football-47124 @helpful-application-7107 we are using presto and use dt as partition column, but the auto-generated query is using

timestamp

; do you have doc for setting up dt?

helpful-application-7107

10/04/2023, 5:03 PM

Ok, yeah so in this case I think I understand why our auto-generated queries won't hit your partitions. You'll need to modify your experiment assignment and metrics queries from our auto-generated ones. You can use templates in your queries to filter your tables to use the experiment start date and end date: https://docs.growthbook.io/app/metrics#sql-templates

helpful-application-7107

10/04/2023, 5:04 PM

I'm curious though, can you share your default query for the Experiment Assignment Query (on your Datasource page) and can you share what you'd like it to be to hit your parititons?

most-midnight-63385

10/04/2023, 5:06 PM

thanks @helpful-application-7107 give me a min

most-midnight-63385

10/04/2023, 5:09 PM

for instance we are tracking an experiment from 2023-09-13

Copy code

WITH
 __rawExperiment as (
   select
     anonymous_id,
     test_registration_at as timestamp,
     test_name as experiment_id,
     test_bucket as variation_id
   FROM
     {table_name}
 ),
 __experiment as ( -- Viewed Experiment
   SELECT
     e.anonymous_id as anonymous_id,
     cast(e.variation_id as varchar) as variation,
     e.timestamp as timestamp
   FROM
     __rawExperiment e
   WHERE
     e.experiment_id = '{test_name}'
     AND e.timestamp >= from_iso8601_timestamp('2023-09-13T00:00:00.000Z')

helpful-application-7107

10/04/2023, 5:12 PM

And your table is partitioned on a different column than

test_resgistration_at

, right?

most-midnight-63385

10/04/2023, 5:13 PM

it’s dt

helpful-application-7107

10/04/2023, 5:15 PM

Yeah, so in this case we don't auto-detect the partitioned columns and build a filter, but you can edit your Experiment Assignment queries and your Metrics queries to have:

Copy code

select
     anonymous_id,
     test_registration_at as timestamp,
     test_name as experiment_id,
     test_bucket as variation_id
   FROM
     {table_name}
   WHERE 
     dt >= {{ startDate }} AND dt <= {{ endDate }}

using the link above to talk about how you cna use these templates to properly filter your tables

most-midnight-63385

10/04/2023, 5:20 PM

okay I will try that

78 Views

Open in Slack

Previous Next

GrowthBook Users

Open source platform for stress free deployments, measured impact, and smarter decisions.