How to query tables from multiple databases in Snowflake?

Question

I'm using Snowflake with Holistics. Snowflake supports joining tables from 2 different databases in the same cloud instance. But in Holistics, each data source has to link to just one Snowflake database.

How can I run join queries on tables that sit in different Snowflake databases?

Context

Currently when connecting to Snowflake, Holistics requires you to specify one database name. While this works well for a default database, it currently doesn't support selecting multiple databases.

Because of this, when creating data models (table models) in Holistics, you can only choose tables in database A but not database B. All the tables in database B are not visible to the end users.

Because of this, you don't know how to pull data from database B for Holistics modeling.

Solution

Create database views in db1 that refer to tables in db2

This is a workaround that we can implement at the moment. Instead of creating a 'table model' to link to a database table, you can create a Query Model

Model users_db2 {
  type: 'query'
  data_source_name: 'mysnowflake'

  dimension field1 {
    type: 'number'
  }
  ...

  query: @sql
		select * from db2.public.users;
  ;;
}

In the above model, it queries data from database B db2.some_table instead of the current database A.

Once created, just treat the model as per a normal table model and create relationships to link them with other models in database A.

The Proper Solution

(This has not been supported. Please reach out to Support for more info)

The proper solution is to fix the above "Add Model" dialog, so that when adding a table model, you can select tables in other databases.

This way you can bring tables from two different databases into a single modeling layer. These models then can be combined (relationships) or joined together.