Category: Business Intelligence

Aliasing table names in Snowflake

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.

Updating a table from SSRS

It’s relatively straight forward to write to a table from an SSRS report once you realise that every time a report is refreshed it tries to run any code it finds in its datasets. This gives us the opportunity to go “off campus”.

Consider a report with a drop-down list called “Change”, containing three choices “View” (the default), “Add”, and “Remove”. And a dataset containing … 

    IF @Change = 'ADD'
        INSERT INTO [dbo].[SomeTable]
        VALUES (@Param1, @Param2);

    IF @Change = 'REMOVE'
        DELETE FROM [dbo].[SomeTable]
        WHERE Column1 = @Param1
		AND Column2 = @Param2;

	SELECT * FROM [dbo].[SomeTable];

You could open the report. Paste some values into Param1 and Param2. Choose “Add” from the drop-down. Then click “Refresh Report”.

And maybe limit access 🙂