What is the difference between a schema & a database in Snowflake?

MYK 0
Viewed 31

Is there a good reason to start a new project in a fresh Snowflake schema vs. a fresh Snowflake database?

I know this sounds like an opinion based question, but I'm trying to get to the technical limitations of one vs. the other.

As far as I can tell, databases & schemas are just like folders and sub-folders. They seem to have no bearing on cost or capability.

I can do:

SELECT * 
FROM database1.schemaA.tableX x
JOIN database2.schemaB.tableY y ON y.row_id = x.row_id

So is it all purely syntax and table organization? Or am I missing something?

snowflake-cloud-data-platform
1 Answers

For simple use cases, you can treat databases and schemas as folders and subfolders. How you set them up is determined on how you want to organise your data and how you want to manage access control.

Access control: the more granular you want to make your access control the more complicated it is to implement and maintain. It's relatively simple to give users access to everything in a database, it's more complicated to give users access to specific schemas within a database and it can get very complicated to give users access to a subset of tables within a schema. Therefore if you have sets of tables that should be accessible to different set of users it is easier if you keep them in different schemas (or databases).

Replication: if you are going to need to replicate data to another Snowflake account (presumably in another region or otherwise you would probably use Sharing not Replication) then bear in mind that replication happens at the database level i.e. you can't replicate specific schemas (or tables or views), the whole database gets replicated. This may influence how you segregate your data between databases

userncjV0
Related