After copying data overnight from SQL Server to Snowflake, I wanted to recreate an SSRS report in Data Studio.
Unfortunately the copied over tables all had ‘STG_’ appended to the start of their names (e.g. STG_Customers).
There is nothing like ‘Synonym’ built into Snowflake yet, and I really didn’t want to go through the legacy query adding ‘STG_’ to the beginning of each and every table mentioned.
My solution was to create individual Views as table aliases. Views in Snowflake are blindingly fast. I used an RBAR loop to automate the process.
My final Task was to schedule the script to run every night. This lets it scale by picking up any new tables.
-- schedule
CREATE OR REPLACE TASK reports.task_sync_alias
WAREHOUSE = compute_wh -- default for this demo
SCHEDULE = 'using cron 0 5 * * * UTC' -- every 5am
AS
-- cursor
DECLARE
var_table_name STRING;
var_view_name STRING;
var_cmd STRING;
stg_cursor CURSOR FOR
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name like 'STG_%'
AND table_schema = 'STAGING'
AND table_type = 'BASE TABLE';
-- loop
BEGIN
FOR RECORD IN stg_cursor DO
var_table_name := RECORD.table_name;
var_view_name := SUBSTR(var_table_name, 5);
var_cmd :=
'CREATE OR REPLACE VIEW ' || var_view_name ||
' AS SELECT * FROM STAGING.' || var_table_name;
EXECUTE IMMEDIATE: var_cmd;
END FOR;
END;
-- enable schedule
ALTER TASK reports.task_sync_alias resume;
I have made a short video showing how I built up this script.
Database Consultant 