A database selector in Snowflake with help of IDENTIFIER() function

Introduction

I've to build a new solution against a couple of databases and they are organized in a couple of environments (Development, Preproduction and Production) and I want to run my code environment agnostic. So I decided to build views for every environment with the same name but with another reference to one of the environments. Now I could easily deploy my code trough my own environments, but one thing that bothered me was that I had to maintain three scripts : for every environment one. Can we bring this one step further? Yes, we can.

The example

In this example we have two databases as a source and two as a target. We need to deploy the code to the different databases from one script in Visual Studio Code.

We can do this with the IDENTIFIER function in Snowflake. let's try this out. 

Lab example

I've created a small example for the example as described above.

First a simple setup of two databases : SourceA and SourceB with the same tablename : SourceTable and in order to test the workings I inserted a value of  'A' in the SourceA Database and a 'B' in the SourceB Database. And, I create two Target databases A and B.

CREATE DATABASE IF NOT EXISTS SOURCEA;
CREATE OR REPLACE TABLE SOURCETABLE (Field String);
INSERT INTO SOURCETABLE VALUES ('A');

CREATE DATABASE IF NOT EXISTS SOURCEB;
CREATE OR REPLACE TABLE SOURCETABLE (Field String);
INSERT INTO SOURCETABLE VALUES ('B');

CREATE DATABASE IF NOT EXISTS TARGETA;
CREATE DATABASE IF NOT EXISTS TARGETB;

Next step is deploy a stored procedure in every target database. Based on the parameter 'CurrentDB' , this stored procedure determines which database it should return : SourceA for TargetA and SourceB for Target Database.

USE DATABASE TARGETA;
--install here 

USE DATABASE TARGETB;
--Install here too

CREATE OR REPLACE PROCEDURE GETDATABASE (CurrentDB STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
    CASE (CurrentDB)
        WHEN 'TARGETA' THEN RETURN('SOURCEA');
        WHEN 'TARGETB' THEN RETURN('SOURCEB');
    END;
END;

And now create the view 'VW_SOURCETABLE' in the TargetA and in the TargetB Database. You can easily execute this code in the VSC (and automate this in a deployment script). 

DECLARE CurrentDB STRING := CURRENT_DATABASE();
BEGIN
    LET CalledDB STRING;
    CALL GETDATABASE(CURRENT_DATABASE()) INTO :CalledDB;
    LET TableName STRING := :CalledDB || '.Public.SOURCETABLE';

    CREATE OR REPLACE VIEW VW_SOURCETABLE AS SELECT * FROM IDENTIFIER(:TableName);

    RETURN (:TableName);
END;

Let's run this in Target A database :

And run this in Target B database :

If we execute this code for TargetA database :

USE DATABASE TARGETA;
SELECT * FROM VW_SOURCETABLE;

Resulting in a table with a value of A : 

Let's check the same for TargetB database :

USE DATABASE TARGETB;
SELECT * FROM VW_SOURCETABLE;

Resulting in a row with a value of B for column Fieldname : 

And if we look at the defintion of the VIEW for the Target A Database, We can see the following :

create or replace view TARGETA.PUBLIC.VW_SOURCETABLE(
	FIELDA
) as SELECT * FROM IDENTIFIER('SOURCEA.Public.SOURCETABLE');

And for the Target B database :

create or replace view TARGETB.PUBLIC.VW_SOURCETABLE(
	FIELDA
) as SELECT * FROM IDENTIFIER('SOURCEB.Public.SOURCETABLE');

Final thoughts

In SQL Server it is not possible to dynamically change the FROM clause or you have to build Dynamic SQL, but in Snowflake you can do this quite easily. 

Hennie