Creating and Managing Database Objects
1. Creating and Managing Database Objects
Welcome to Chapter 2. In the last chapter, we covered how Snowflake organizes objects into a hierarchy. Now let's get hands on — creating databases and schemas, running queries, and understanding how Snowflake manages access to all of it.2. Creating a Database and Schema
Creating a database in Snowflake is a single line of SQL. You execute the statement: `CREATE DATABASE` followed by the database name. Similarly, to create a schema, you execute `CREATE SCHEMA` and either qualify it with the database name, as in `CREATE SCHEMA snowy_peak_db.raw`, or provide only the schema name — in that case the schema is created in the database of your current context. Once created, both show up immediately in the object browser in Snowsight. From here, the next step is telling Snowflake where you want to work, and that is where context comes in.3. Setting Context
Before you run any query, remember to set your context like we covered in the last video. All four of these lines matter: role, warehouse, database, and schema. If you set only part of the path, a fully qualified `USE SCHEMA my_db.my_schema` (or a single `USE SCHEMA` after `USE DATABASE`) but the important part is that database and schema are unambiguous before you run. Skip the warehouse and you will get an error (for example, "No active warehouse selected"). The fix is always the same: set your warehouse, then re-run.4. Querying Your Data
Once your context is set, querying works as you'd expect. One Snowflake-specific addition worth knowing is `EXCLUDE`. Instead of listing every column you want, you select all columns and exclude the few you do not need. This is often easier than a long select list for wide tables. `LIMIT` caps the number of rows returned, which is a good habit when exploring, but it does not guarantee a cheaper scan by itself, Snowflake may still need to read relevant micro-partitions depending on the query. Use it together with a sensible column list for exploration.5. Understanding Your Tables
Every Snowflake database comes with a built-in schema called INFORMATION_SCHEMA. It's a read-only set of views that gives you metadata about everything in your database. If you're handed a new database and want to understand what's in it before writing a single business query, this is your starting point.6. Understanding Your Columns
INFORMATION_SCHEMA.COLUMNS goes one layer deeper — giving you the structure of a specific table. Column names, data types, whether nulls are allowed. This is useful when you're joining tables and need to confirm types match, or when you're building a pipeline and need to know which fields can be empty. Between TABLES and COLUMNS, you can understand a database's structure without opening a single table directly.7. RBAC: Role-Based Access Control
Access control in Snowflake follows a role-based model: RBAC, or Role-Based Access Control. Every object has an owner: whichever role created it. Common privileges include `SELECT` to read data, `INSERT`, `UPDATE`, and `DELETE` to write it, and `USAGE` to access a database so you can use objects inside it, a schema, or to use a warehouse. Dropping or altering the object structure typically requires `OWNERSHIP` or equivalent DDL privileges.8. RBAC: Granting Privileges in Practice
`USAGE` on the database is required for a role to work with objects in that database: without it, you may be unable to query tables even with `USAGE` on the schema and `SELECT` on the table. In the example, we first grant `USAGE` on the database, then the schema, then `SELECT` on the table. Users do not get privileges directly: they are assigned roles, and the role carries the privileges. The principle of least privilege is the guiding rule. For example, an analyst who only reads data should not get DDL permission on production objects.9. Let's practice!
Let’s put your knowledge to the test.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.