https://linen.dev logo
Join Slack
Powered by
Good morning, I am trying to add volume_anomalies ...
# community-support
a
Good morning, I am trying to add volume_anomalies test, and before running been wondering:
Copy code
set time_bucket = elementary.get_time_bucket(time_bucket, model_graph_node)
time bucket is calculated as following(taken from elementary git, more in their repo), but my fact tables (incremental model) are clustered by column called 
date
whilst my 
timestamp_column
is not among the clustered columns in my tables. Won’t it be super heavy query to check for example even 1 month back? I want the seasonality to be hour_of_week (at the moment). I thought about adding in the `where_expression`a filter which will take the 
date
according to scanned timestamp.. what would you suggest to do?
✅ 1
s
one option is what I described here
👑 2
but fundamentally you can't have all of the following in one table: • cluster by 
date
• non-clustered 
timestamp_column
• 
hour_of_week
seasonality • optimal query performance because there's no way to avoid grouping by the un-clustered 
timestamp_column
to get 
hour_of_week
seasonality filtering in the 
where_expression
is recommended; you could also consider adding an index (or whatever your data warehouse affords) on 
timestamp_column
a
Thanks for answering! A less sensitive seasonality might cause a better performance isn’t it? I also tried to minimize the detection period to one day but it didn’t help :) I’ll take a deeper look at your answer later as I am out of office at the moment :)
👍 1
s
A less sensitive seasonality might cause a better performance isn’t it?
Probably but hard to say without knowing more about your stack and data
I also tried to minimize the detection period to one day but it didn’t help 🙂
Unfortunately this won't necessarily work. See these 2 feature requests for some more context on why 1 2
a
Thank you :)
Ok, after some time of thinking and influence from you: Just to remind: my source table is partitioned by date/hour, but we do have a timestamp column as well(not partitioned by it of course) a new pre-aggregated table as following: timestamp | value for example: 2023-12-18 08:00 501 2023-12-18 09:00 100 each row will show the timestamp of day(of date) and hour. single timestamp represents 8AM on Sunday for example then hour of week will be able to seek for the exact hour in the previous weeks for example and do anomaly detection
@Samuel Garfield What’s your opinion about my approach? The only thing I’m not sure is the monitor of column_anomalies. you said using 
sum
, right?
s
apologies, been on holiday
✅ 1
this makes sense to me
you would apply a 
sum
column anomaly on the second column in the pre-aggregated table
and use the first column as the timestamp column for the elementary test
a
Yep it’s already working for a while and looks good 🙂
garfieldthumbsup 1
thanks!
37 Views
Open in Slack
PreviousNext
Powered by