Convert to Native Query from ODBC with joins to different Snowflake databases

Define "multiple databases". Are they all on the same server and do you have equal access to each of them?

Yes, it's possible to write a native SQL query that joins tables from multiple Snowflake databases directly within the same query. Snowflake allows you to reference tables from different databases in a single SQL statement, provided you have the necessary permissions to access those databases.

### Here’s how you can do it:

#### 1. **Qualified Table Names**
You can join tables from different databases by qualifying the table names with the database and schema. The syntax generally looks like this:

```sql
SELECT *
FROM database1.schema1.table1 AS t1
JOIN database2.schema2.table2 AS t2 ON t1.id = t2.id
```

#### 2. **Ensure Permissions**
Make sure that your user account has the appropriate permissions to access both databases and the schemas within them.

#### 3. **Performance Considerations**
When working with joins across databases, be mindful of potential performance implications, especially with large datasets. Consider the size of the tables and the complexity of the joins.

#### 4. **Convert to Native Query**
If you're using Power BI or another tool that allows you to write native queries, you can directly input your SQL code with the necessary joins. Here’s a basic structure:

```sql
SELECT t1.column1, t2.column2
FROM database1.schema1.table1 AS t1
JOIN database2.schema2.table2 AS t2 ON t1.common_column = t2.common_column
WHERE t1.some_condition = 'value'
```

### Alternatives if Needed

If for any reason you cannot get the joins to work or if you encounter limitations:

- **Separate Queries**: You can indeed break the queries into separate queries, load them into Power BI, and create relationships in the model. You would then use DAX to create measures as needed.

- **Materialized Views**: If the joins are complex and used frequently, consider creating a materialized view in Snowflake that combines the data from the necessary tables. Then, query this view from Power BI.

### Conclusion

You can definitely write a native query that joins tables from different Snowflake databases. Just ensure that you use the fully qualified names for your tables and check permissions. If needed, separating the queries and establishing relationships in Power BI is also a valid approach. If you have any more specific questions or examples, feel free to ask!