Hi team, does growth book support dt/hr partition?...
# ask-questionsm
most-midnight-63385
10/03/2023, 11:03 PMHi 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
fresh-football-47124
10/04/2023, 4:27 AM
but there are ways to do date partitions
h
helpful-application-7107
10/04/2023, 4:04 PMWhat data warehouse are you using?
helpful-application-7107
10/04/2023, 4:05 PMWe 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 PMWe 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
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
timestamph
helpful-application-7107
10/04/2023, 5:03 PMOk, 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 PMI'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 PMthanks @helpful-application-7107 give me a min
most-midnight-63385
10/04/2023, 5:09 PMfor 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 PMAnd your table is partitioned on a different column than
test_resgistration_atm
most-midnight-63385
10/04/2023, 5:13 PMit’s dt
h
helpful-application-7107
10/04/2023, 5:15 PMYeah, 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 }}m
most-midnight-63385
10/04/2023, 5:20 PMokay I will try that
67 Views