Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformDatabases & Schemas

Databases & Schemas

Part 6

Database Objects & Platform Features

The building blocks: tables, views, and the objects that turn Snowflake from a warehouse into a full application platform, streams, tasks, dynamic tables, stored procedures, UDFs, and semi-structured data. This closes out the COF-C03 domain map.

These topics belong to Domains 1 and 4 rather than forming their own block. Most of them come down to sorting: schema-level versus account-level objects, table types by cost and persistence, scalar versus tabular versus procedure. Semantics traps do the rest: a SELECT that doesn't consume a stream, a dynamic table you can't INSERT into, LAST_QUERY_ID(2) versus (-2). When an option names a command you've never seen in this guide, it probably doesn't exist.

Databases, Schemas & Tables

The object hierarchy every privilege, every grant, and every query path in Snowflake sits inside: account → database → schema → object.

Databases & Schemas

Definition

A database is a logical container for schemas; a schema is a logical container for tables, views, and every other object type. Every database ships with two schemas by default: PUBLIC, the default working schema, and INFORMATION_SCHEMA, a read-only, auto-populated set of metadata views and table functions describing the objects inside that specific database.

The object hierarchy, drawn precisely

Every Snowflake object lives at exactly one level of a strict containment hierarchy, and knowing each object's level is most of the skill. Warehouses feel like they should live inside a database. They don't. Pipes feel account-wide. They aren't. Learn the map, not the intuition.

Databaseschemas and database roles
PUBLIC schema INFORMATION_SCHEMA Database roles Your schemas ↓
Schemaschema-level objects
Tables Views Named stages Pipes File formats UDFs Stored procedures Streams Tasks Sequences Masking & row access policies Network rules
Figure 6.1 Every object lives at exactly one level. Account objects are addressed by bare name from anywhere; a database holds schemas and database roles; schema objects take a three-part name, database.schema.object. Read top to bottom: each level contains the next.

A useful drill: take one object from each level and sort them. Pipes and file formats are schema objects, even though pipes drive account-wide ingestion. Resource monitors, warehouses, and storage integrations are account objects, even though you use them from inside a database context every day. And a fully qualified name always has exactly three parts for a schema object (database.schema.object), which is itself a giveaway: three parts means a schema object, two parts (db.schema or db.database_role) means a database-level object, and a bare name means an account object.

Exam trap

Sort these five: resource monitor, file format, virtual warehouse, pipe, storage integration. Only file format and pipe are schema objects. The other three are account-level, and every one of them is placed there deliberately because it either governs compute (warehouses, monitors) or holds credentials (integrations), things Snowflake refuses to scatter across databases.

CREATE DATABASE analytics_db;
CREATE SCHEMA analytics_db.staging;
CREATE SCHEMA analytics_db.marts WITH MANAGED ACCESS;   -- only the schema owner (or MANAGE GRANTS) grants on objects here

SELECT * FROM analytics_db.information_schema.tables;
SELECT * FROM TABLE(analytics_db.information_schema.query_history()) LIMIT 10;   -- a table function, last 7 days

Information Schema vs Account Usage, precisely

Information SchemaAccount Usage
ScopeViews: one database. Table functions such as QUERY_HISTORY: account-wide historyEntire account
Includes dropped objectsNoYes
Retention7 days to 6 months, view-dependentUp to 1 year on most views
LatencyNear real-time45 minutes to 3 hours, depending on the view (about 2 hours for most)
Where it livesInside each database (INFORMATION_SCHEMA schema)The shared SNOWFLAKE database
Two ways to read metadata in one account with three databases: SALES_DB, HR_DB and OLD_DB, which was dropped yesterday. SALES_DB.INFORMATION_SCHEMA sees only SALES_DB, only objects that exist now, and only what your role may see, with no delay. SNOWFLAKE.ACCOUNT_USAGE sees all three databases, dropped objects included, keeps up to one year of history and lags 45 minutes to 3 hours.ACCOUNTSCHEMA INSIDE SALES_DBSALES_DB.INFORMATION_SCHEMAviews cover this database onlyobjects that exist now · filtered by your roleDATABASESALES_DBDATABASEHR_DBDATABASEOLD_DBdropped yesterdaySCHEMA IN THE SHARED SNOWFLAKE DATABASESNOWFLAKE.ACCOUNT_USAGEevery database, dropped objects included · up to 1 year of historylatency 45 minutes to 3 hourssees all threesees

SCHEMA INSIDE SALES_DBSALES_DB.INFORMATION_SCHEMAviews cover this database onlyobjects that exist now · filtered by your role

sees

ACCOUNT

DATABASESALES_DB

DATABASEHR_DB

DATABASEOLD_DBdropped yesterday

sees all three

SCHEMA IN THE SHARED SNOWFLAKE DATABASESNOWFLAKE.ACCOUNT_USAGEevery database, dropped objects included · up to 1 year of historylatency 45 minutes to 3 hours

Figure 6.2 INFORMATION_SCHEMA answers for one database, now; ACCOUNT_USAGE answers for the whole account, including what was dropped, with a delay. Follow the arrows: the schema inside SALES_DB reaches only SALES_DB, while the shared SNOWFLAKE database reaches every database, even OLD_DB. Querying either one with SELECT needs a warehouse.

The INFORMATION_SCHEMA of any given database exposes two kinds of objects: views for all objects contained in that specific database, and table functions for account-level, historical, or usage data (query history, login history, task history) that draw information beyond that one database's boundary.