Connecting to multiple Snowflake databases in a single SQL query

[GBilger]

I'm having challenges passing a single SQL query to snowflake in Power BI that references multiple databases within the same server/warehouse where the role/credentials/authentication for both are all the same.  Although it says the database name isn't required when setting up the snowflake connection, Power Query forces you to enter a name.  So I keep getting an error even with entering the full database.table for each table.  I know I can pull each database individually and then join/merge them in power query.  However thats not what I want to do.  I want to create a single query.  Is this possible?  If so, how?

[SacheeTh]

We have done the same senario by adding separate connection tables 

If you open i the Advaned editor of one of the connection table here,

let
    Source = Snowflake.Databases(#"Datawarehouse-Server",#"Datawarehouse-Warehouse"),
    _Database = Source{[Name=#"Datawarehouse-Database" ,Kind="Database"]}[Data],
    _Schema = _Database{[Name=#"Datawarehouse-Schema-Dim" ,Kind="Schema"]}[Data]
in
    _Schema

Same way using deferent parameters try to get the tables in and then use an Append to combine tables. 

Here is a step by step guide:Steps:

1. Create Separate Connections:

   • Use Power Query to create a connection for each database and schema you need to reference.

   • For example, in the Advanced Editor, you can define a connection like this:

     let
         Source = Snowflake.Databases(#"Datawarehouse-Server", #"Datawarehouse-Warehouse"),
         _Database = Source{[Name=#"Datawarehouse-Database", Kind="Database"]}[Data],
         _Schema = _Database{[Name=#"Datawarehouse-Schema-Dim", Kind="Schema"]}[Data]
     in
         _Schema

   • Repeat the process for other databases and schemas using the appropriate parameters for each.

2. Combine Tables Using Append:

   • Once you have separate connection tables for each database and schema, load the required tables into Power Query.
   • Use the Append Queries option to combine the data from these connections into a single dataset.

3. Handle Column Alignment:

   • Ensure that the structure (column names and data types) of the tables being combined is consistent to avoid errors.

4. Publish and Test:

   • After appending the tables, load the combined dataset into your Power BI report, and test the output.

This method works because each Snowflake connection uses the same server/warehouse/credentials. Power Query allows you to manage each connection individually, then merge them programmatically, avoiding the limitations of entering a single SQL query across multiple databases.

Let me know if you need further details! 😊

[SacheeTh]

We have done the same senario by adding separate connection tables 

If you open i the Advaned editor of one of the connection table here,

let
    Source = Snowflake.Databases(#"Datawarehouse-Server",#"Datawarehouse-Warehouse"),
    _Database = Source{[Name=#"Datawarehouse-Database" ,Kind="Database"]}[Data],
    _Schema = _Database{[Name=#"Datawarehouse-Schema-Dim" ,Kind="Schema"]}[Data]
in
    _Schema

Same way using deferent parameters try to get the tables in and then use an Append to combine tables. 

[lbendlin]

Run a SQL statement for that

[GBilger]

I've tried this...you have to provide a database name though in the field above.  It isn't actually optional.  Specifying database1 there prevents me from querying against database2.  How can I get around that?

[lbendlin]

Initially provide a single database Name and a query against it. Then in advanced editor modify the query to include the other databases.