# Snowflake: working with multiple warehouses

**URL:** <https://community.cdata.com/t/snowflake-working-with-multiple-warehouses/764>\
**Category:** CData Virtuality \[Archived\]\
**Tags:** article, cdata-virtuality\
**Created:** [June 10, 2024, 4:16pm UTC](https://community.cdata.com/t/snowflake-working-with-multiple-warehouses/764 "2024-06-10T16:16:31Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![anonymous](https://avatars.discourse-cdn.com/v4/letter/a/8491ac/32.png) [@anonymous](https://community.cdata.com/u/anonymous)\
**Post date:** [June 10, 2024, 4:16pm UTC](https://community.cdata.com/t/snowflake-working-with-multiple-warehouses/764/1 "2024-06-10T16:16:31Z")

</div>

Snowflake is a fantastic database for analytical storage and for reading and writing of data. Data Virtuality’s Snowflake connector supports a single connection to a single database. If you need to connect to multiple databases, it is recommended you create multiple connections, one for each database.

For example, this Snowflake instance has four databases.

 ![mceclip0.png](https://us1.discourse-cdn.com/cdata/original/2X/c/c6ff9295745af607dcbec8465e3775e5d2f08b79.png)

We currently have a single connection to the&nbsp; **test\_db** &nbsp;database.

 ![mceclip1.png](https://us1.discourse-cdn.com/cdata/original/2X/5/519d8bad650b3a7ccdefa42ce58e32b734c82743.png)

```auto
/* Create connection */call SYSADMIN.createConnection(name => 'snowflake_dv', jbossCliTemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host= ************************.snowflakecomputing.com,db=test_db,user-name=qa,check-valid-connection-sql="select 0",warehouse=DEMO_WH', encryptedProperties => 'password=** password**');;/* Create data source */call SYSADMIN.createDatasource(name => 'snowflake_dv', translator => 'snowflake', modelProperties => 'importer.catalog=TEST_DB,importer.tableTypes="TABLE,VIEW",importer.useFullSchemaName=FALSE,importer.schemaPattern=TEST_VZ,importer.defaultSchema=TEST_VZ', translatorProperties => '', encryptedModelProperties => '', encryptedTranslatorProperties => '');;

```

We create an additional connection to the&nbsp; **SNOWFLAKE\_SAMPLE\_DATA** &nbsp;database.

 ![mceclip2.png](https://us1.discourse-cdn.com/cdata/original/2X/b/b2d39dbd3d19b1d2452638e098cf7d34ac10b68c.png)

UNION ALL can be used within views to combine data across multiple data warehouses. In the next example, you will create connections to multiple Snowflake databases.

This will create a new data source called&nbsp; **sf1** &nbsp;connected to warehouse&nbsp; **TEST1**

```auto
-- create Snowflake data source using Warehouse "TEST1"EXEC SYSADMIN.createConnection(name => 'sf1', jbossCLITemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=TEST1') ;;EXEC SYSADMIN.createDataSource(name => 'sf1', translator => 'snowflake', modelProperties => 'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"', translatorProperties => 'supportsNativeQueries=true') ;;

```

This will create a new data source called&nbsp; **sf2** &nbsp;connected to warehouse&nbsp; **TEST2**

```auto
-- create Snowflake data source using Warehouse "TEST2"EXEC SYSADMIN.createConnection(name => 'sf2', jbossCLITemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=TEST2') ;;EXEC SYSADMIN.createDataSource(name => 'sf2', translator => 'snowflake', modelProperties => 'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"', translatorProperties => 'supportsNativeQueries=true') ;;

```

The data from&nbsp; **sf1.tableX** &nbsp;and&nbsp; **sf2.tableX** &nbsp;is combined using the&nbsp; **UNION ALL** &nbsp;statement

```auto
create view views.tableX asSELECT * FROM sf1.tableXUNION ALLSELECT * FROM sf2.tableX;;

```

The following example uses the&nbsp; **hasRole** &nbsp;function to dynamically limit at runtime, which datasource is used based on the roles assigned to the user executing the query.

```auto
create view views.tableX_by_role asSELECT * FROM sf1.tableX where hasRole('data', 'dv-role-1')UNION ALLSELECT * FROM sf2.tableX where hasRole('data', 'dv-role-2');;

```

For example, I do not have the role&nbsp; **‘dv-role-1’** &nbsp;assigned to my account. When I execute the following query it returns&nbsp; **FALSE**.

```auto
select hasRole('data', 'dv-role-1');;

```

For example, I have the role&nbsp; **‘dv-role-2’** &nbsp;assigned to my account. When I execute the following query it returns&nbsp; **TRUE**.

```auto
select hasRole('data', 'dv-role-2');;

```

When I execute the view&nbsp; **select \* from views.tableX\_by\_role** , the&nbsp; **hasRole** &nbsp;function returns&nbsp; **FALSE** &nbsp;for&nbsp; **‘dv-role-1’** &nbsp;and&nbsp; **TRUE** &nbsp;for&nbsp; **‘dv-role-2’** &nbsp;base on the roles assigned to my account. This effectively becomes

```auto
SELECT * FROM sf1.tableX where FALSE -- no data returnedUNION ALLSELECT * FROM sf2.tableX where TRUE -- data is returned

```

This effectively becomes

```auto
SELECT * FROM sf2.tableX 

```