Snowflake Cloud data warehouse - Database best practice limitations

Question

Is there a limitation on how many schemas and security objects you can have in one Snowflake database and will there be performance degradation with thousands of these objects?

Will splitting the data into multiple databases help performance?

Answer 1

There is no hard limit to my knowledge. Our experience is that there are dire performance problems when you are having larger thousands of security objects (users, roles, schemas). We have a couple of support cases open about this. So far we've been able to work out some points which affect the performance:

• The performance problems are worse when the security objects are changing a lot. There is some sort of cache which helps a lot if the security objects do not change often.
• The performance problems are worse when the security graph is complicated. The sheer number of users/roles is less important then their relations (e.g. user A has grant to 1000 roles, each of these has grant to 100 other roles)
• Using multiple databases has no effect (apart from the previous point).
• Scaling the warehouse has limited effect in that you can offset the "lag" in DCL/DDL queries by faster executing the DQL/DML queries.
• The performance of the cloud security functions varies a lot - You can have avg 200ms on one query on one database for a couple of days and then it raises to avg 500ms out of the blue and stays there for a couple of days.

Interestingly we're constantly told by support that we're the only customer experiencing these kind of problems.