Remove schedule from unused queries

Hi All,

To get rid of the 600 or so ancient queries that still had schedules punishing our Redshift cluster every day, I ran this on the metadata database:

with old as (
    select object_id::int, max(created_at) as last_viewed from events where object_type='query' and action in ('view', 'api_get') group by 1 having max(created_at) < (current_timestamp - interval '3 months')
)
update queries set schedule = null where id in (
select
    q.id
from
    queries q join old o on o.object_id = q.id
where
    not q.is_archived
    and q.schedule is not null
);

Hopefully others find it helpful too. To be honest, could be a built-in feature as our uses create tens of thousands of queries :frowning:

Chris

2 Likes

Queries that have alerts setup on them can also get removed in this case. You will have to take join with alerts table as well and check if any alerts got triggered in the last 3 months.