A colleague at Snowflake was working on a white paper recently. In the paper, they stated that querying between different Snowflake databases is no slower or more difficult than querying within a Snowflake database. A non-technical person asked for a source for this, and several of us tried to find one, and could not. This is just such an obvious truth within the Snowflake world that it hardly even seems worth writing down.
Looking back on my past experience, no other platform I have worked with made querying across databases easy, much less performant. It really is a bit of a revolutionary thing. I explored the topic of what a database is in a previous blog entry. There I noted that unlike some past platforms I had dealt with, a database in Snowflake is basically just another logical layer encompassing multiple schemas.
Snowflake hasn’t magically solved problems of geography, the speed of light, or interactivity between cloud providers. A Snowflake account exists in a specific region of a specific cloud provider. A query can span databases, but it cannot span accounts without sharing or replication . With replication, a copy of a database can easily be maintained for you in multiple regions or even across cloud providers. This is a feat that requires a fair amount of expertise, time, and networking kung-fu with most database platforms. There is, of course, some latency expected with replication – we haven’t yet solved the problem of sending data at faster-than-light speeds! But the ease of this replication is incredible to me. Having a replica makes it so we can query and join between databases in different regions and even cloud providers.
Querying Across Databases
Let’s explore quickly what it looks like to query across multiple databases. Here are the databases I have in my current demo account:
I have the two default databases that should exist in snowflake accounts – SNOWFLAKE and SNOWFLAKE_SAMPLE_DATA. I have a third that I created in previous experimentation called ECROOKS_EXP. Let’s create a table in ECROOKS_EXP based on one of the sample data tables so I can demonstrate what it looks like to join tables from two different databases:
use ecrooks_exp.public;
create table orders_by_date as (
select *
from snowflake_sample_data.tpch_sf100.orders
order by o_orderdate
);Now I have a copy of the orders table from the SNOWFLAKE_SAMPLE_DATA database in a different database – ECROOKS_EXP. With IBM Db2, if I wanted to move a copy of a table from one database to another, I would generally need to EXPORT the data and LOAD it in. If I could connect between the databases (a significant if, many databases I worked with were on different networks, and not easy to set up connectivity between), I could do this using a pipe. More often I would use a file of CSV or IXF format, and scp that file between the different database servers. Db2 and most database platforms do provide replication features to keep table copies up to date. Depending on the type of replication used, there are varying levels of complexity to this.
Creating copies of tables in another database might actually be a great way to experiment, assuming the data is relatively small and you’re allowed to move it to an experimentation database. In this case, I could use it to compare the performance of querying an orders table clustered on date to whatever the native clustering state of the table is.
Here’s what it might look like to join two tables in different databases:
select c_custkey
, any_value(c_name)
, sum(o_totalprice)
from snowflake_sample_data.tpch_sf100.customer sd_c
join ecrooks_exp.public.orders_by_date exp_o on sd_c.c_custkey=exp_o.o_custkey
where o_orderdate > to_date('1998-01-01')
group by c_custkey;Note that the table names here are fully qualified – of the format DATABASE.SCHEMA.TABLE.
This query returns results that look like this:
This is the kind of thing we see every day in Snowflake. I am never surprised when working on a customer query to see different databases in one query, to the point where I often don’t even notice. From a performance perspective, I certainly don’t care which database a table is in. When I worked with Db2, joining tables from two different databases was possible, using a feature called federation, but it required cataloging the remote database and defining nicknames for the remote tables. In Db2, the performance from federated queries could be just fine, but also might suffer from the lack of information sharing between the separate databases, and certainly had to wait for the data from the remote server to be transferred across the network.
The reason cross-database querying works so seamlessly in Snowflake is that all we are really talking about are files on S3 or equivalent storage. They don’t reside on or belong to any one server or VM. We therefore don’t have to transfer files around to use them together. If we have role-based permissions on the database objects, it doesn’t much matter which database they belong to.
Summary
The thing that really strikes me here is that something I take for granted as just a part of the Snowflake system would have been a relatively foreign idea to me a few years ago. I don’t have to move data to query and join it, if it is in Snowflake tables in the same account, and I have permissions on it.
