When discussing how to stop Snowflake users from running select * on a table, we got a related question: "If only there was a way to do something similar to enforce a where clause on the date/timestamp column"
So how I can force Snowflake users to use a range of dates when querying a table?
Note: Check a cleaner solution using table UDFS
One way to solve this is to create a view over the table. The first row of this view should be full of invalid computations, and a date/timestamp outside the normal range of dates:
create or replace table mytable3(i number, s string, d date);
insert into mytable3 values (1, 2, '2020-01-01');
create or replace secure view mytable3_view
as
select 'you need to add a date filter'::int i, 'you need to add a date filter'::int s, '1-1-1'::date d
union all
select *
from mytable3
;
Now whenever someone queries that view, it will throw a descriptive error whenever they don't filter out the inappropriate date:
select i, s
from mytable3_view
--where d > '2000-01-01'
;
-- Numeric value 'you need to add a date filter' is not recognized
Note that with a secure view we can manage permissions so analysts can only get data through the view, without giving them access to the underlying table.
Extra credits:
A cleaner alternative (as suggested on reddit): Create a SQL table function requiring the filtering parameters, and then returns the filtered table:
create or replace secure function table_within(since date, until date )
returns table(i number, s string, d date)
as $$
select i, s, d
from mytable3
where d between since and until
$$;
Then you can use it with select * from table(function_name(since, until)):
select *
from table(table_within('2019-01-01'::date, '2021-01-01'::date))
