https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
m

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
f

fresh-football-47124

10/04/2023, 4:27 AM
we have some improvements in the query speed coming
but there are ways to do date partitions
h

helpful-application-7107

10/04/2023, 4:04 PM
What data warehouse are you using?
We definitely support scanning only relevant partitions, but in some set ups it might require fine tuning the queries.
f

fast-wire-759

10/04/2023, 4:41 PM
We have a self managed cluster running on AWS
@most-midnight-63385 please follow up @fresh-football-47124 @helpful-application-7107
m

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?
h

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
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?
m

most-midnight-63385

10/04/2023, 5:06 PM
thanks @helpful-application-7107 give me a min
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')
h

helpful-application-7107

10/04/2023, 5:12 PM
And your table is partitioned on a different column than
test_resgistration_at
, right?
m

most-midnight-63385

10/04/2023, 5:13 PM
it’s dt
h

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
m

most-midnight-63385

10/04/2023, 5:20 PM
okay I will try that
15 Views