Databases and schemas in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Snowflake Database and Schema Hierarchy

In Snowflake, which statement correctly describes the relationship between databases and schemas?

A Databases and schemas are the same and can be used interchangeably.

B A schema contains multiple databases, and each database contains multiple tables.

C Tables contain multiple schemas, and schemas contain multiple databases.

D A database contains multiple schemas, and each schema contains multiple tables.

💡 Hint

Think about how folders and subfolders work on your computer.

In Snowflake, a database is like a main folder that holds multiple schemas, which are like subfolders. Each schema then contains tables.

❓ Configuration

intermediate

Creating a Schema in a Specific Database

Which Snowflake SQL command correctly creates a schema named sales_data inside the database company_db?

A CREATE SCHEMA company_db.sales_data;

B CREATE DATABASE company_db.sales_data;

C CREATE SCHEMA sales_data IN DATABASE company_db;

D CREATE sales_data SCHEMA IN company_db;

💡 Hint

Remember the syntax for creating schemas inside a database.

The correct syntax to create a schema inside a specific database is CREATE SCHEMA schema_name IN DATABASE database_name;

❓ Architecture

advanced

Best Practice for Organizing Data in Snowflake

You have multiple teams working on different projects. Which Snowflake structure best isolates data for security and management?

A Create multiple databases, one per team, each with its own schemas.

B Create one schema with multiple databases inside it, each for a team.

C Store all data in a single schema and use table names to identify teams.

D Create one database with multiple schemas, each schema for a team.

💡 Hint

Think about isolating data for security and easier management.

Using multiple databases per team provides better isolation and control over access and resources compared to using schemas alone.

❓ security

advanced

Granting Access to a Schema

Which SQL command grants the role analyst_role usage rights on the schema marketing in the database company_db?

A GRANT USAGE ON SCHEMA company_db.marketing TO ROLE analyst_role;

B GRANT ALL ON SCHEMA marketing TO ROLE analyst_role;

C GRANT USAGE ON DATABASE company_db TO ROLE analyst_role;

D GRANT SELECT ON DATABASE company_db TO ROLE analyst_role;

💡 Hint

Usage on schema allows access to objects inside it without giving full database rights.

Granting USAGE on a schema allows the role to access objects inside the schema without granting full database privileges.

❓ service_behavior

expert

Effect of Dropping a Schema with Dependent Objects

What happens when you run DROP SCHEMA company_db.sales_data; if the schema contains tables and views?

A The schema and all its objects are dropped immediately without warning.

B The command fails with an error because the schema is not empty.

C The schema is dropped, but tables and views remain in the database.

D The schema is marked for deletion and removed after 7 days automatically.

💡 Hint

Think about safety measures to prevent accidental data loss.

Snowflake requires schemas to be empty before dropping. If objects exist, the drop command fails.