default schema for snowflake connection

[raghutej]

Hi ,
I am using snowflake connections to connect to tables.
I have 3 different connections to snowflake for dev, QA and prod.
How to set default databases for these connections?
All dev databases start with Dev_* and QA databases start with QA_* and production databases don't have a prefix.
If i deploy a model from design node to a pre-prod automation node and if in design the connection is DEV_CONNECTION to access a table Dev_test_table, how to automatically change that to QA_test_table when i do a database connection mapping DEV_CONNECTION -> QA_CONNECTION?

[Marlan]

Hi @raghutej,

We use a similar set of three sets of databases for dev, QA, and prod. Our approach has been to use the same connection name across all three nodes (design, QA automation, prod automation). Therefore no connection re-mapping is needed. The connection is defined to point the appropriate database on each node (on design node, points to dev database, on prod automation node, points to prod database).

This has worked nicely for us. Your requirements may be different and thus this may not work for you but if it does I can attest that it works seamlessly.

Marlan

[Turribeach · Accepted solution]

We do not like nor use database connection mappings when deploying to Automation or other environment. This is because a database connection mapping is dangerous since it's invisible. It also doesn't really address issues like database schema names changing between environments. We therefore work in this way:

1. We use generic connection names which are the same in every DSS instance be it Dev, QA or Prod
2. We point those connections to the corresponding matching database for every DSS instance
3. We also use global variables in SQL recipes to handle schema names that change across environments

By doing this we have certainty that the code that run in each environment is exactly the same. And there is no risk of code intended for Dev running on QA or Prod.

[raghutej]

Hi @Turribeach , Thanks for response. We used global variables in SQL recipes to handle schema names that change across environments

[hmjk347]

@raghutej ... can you share sample of code which you have used to handle this in SQL recipe as i have similar requirement for my projects.