Snowflake Object Hierarchy & 12 Database Objects
Snowflake stores everything inside a strict containment chain: Organization → Account → Database → Schema → object. The COF-C03 exam guide names twelve specific things that sit inside a schema as database objects, and the exam tests them with multi-select questions. The other half of today is the single most-tested Domain 1 distinction: INFORMATION_SCHEMA versus ACCOUNT_USAGE.
Catalog vocabulary trips people up because the same word means different things in different products. Pin these down before anything else:
| Term | Plain meaning |
|---|---|
| Organization | The biggest container. A company’s footprint across Snowflake, holding one or more accounts. |
| Account | One isolated tenant on Snowflake. Has its own users, warehouses, and databases. |
| Database | A logical container inside an account. Holds schemas. |
| Schema | A folder inside a database. Holds the actual objects (tables, views, etc). |
| Fully-qualified name | The full path to an object: database.schema.object. Like a file path. |
| System catalog | Built-in metadata views Snowflake exposes about your own account. Two main flavors: INFORMATION_SCHEMA and ACCOUNT_USAGE. |
Today’s Concept
Micro-Concept 1: The Containment Hierarchy
Read this top-down, broadest to narrowest. Each level is contained by the level above it.
| Level | What it contains | Example |
|---|---|---|
| Organization | One or more accounts (cross-region, cross-cloud) | ACME_GLOBAL |
| Account | Users, roles, warehouses, databases, resource monitors, network policies | ACME_PROD_AWS_US_EAST_1 |
| Database | Schemas (and account-scoped objects only at this level: itself, plus shares) | SALES_DB |
| Schema | Database objects (the 12, see Micro-Concept 2) | SALES_DB.PUBLIC |
| Object | The actual table, view, function, etc. | SALES_DB.PUBLIC.ORDERS |
Every database is created with two schemas already inside it: PUBLIC and INFORMATION_SCHEMA. PUBLIC is the default landing zone for new objects, and you are allowed to drop it. INFORMATION_SCHEMA is Snowflake’s per-database metadata catalog. You cannot drop it because it is a system schema, not yours to remove.
Every object lives inside exactly one container.
Flow follows containment. An organization holds accounts, an account holds databases, a database holds schemas, and schemas hold every database object.
Fully-qualified naming. The unambiguous form is database.schema.object. If you have already run USE DATABASE sales_db; USE SCHEMA public;, the bare name orders resolves correctly in that session. In production code (stored procedures, scheduled tasks, anything shared with teammates), always write the three-part name. I see this trap in project work: procedures that work in dev fail in another environment. The cause is a different default schema set by the caller’s session.
Micro-Concept 2: The 12 Database Objects
The COF-C03 exam guide names these twelve as the schema-level database objects you are expected to recognize. The list is not negotiable on the exam. Learn the full set.
| # | Object | What it does |
|---|---|---|
| 1 | Tables | Hold rows of data (Permanent / Transient / Temporary / External / Iceberg / Dynamic; Day 11–12) |
| 2 | Views | Saved SELECT statements: Standard, Secure, Materialized (Day 13) |
| 3 | Schemas | Themselves. Schemas live inside databases and contain everything below. |
| 4 | Stages | Landing zones for files (Internal / External; Day 29) |
| 5 | File Formats | Reusable definitions of how to parse files (CSV, JSON, Parquet, etc; Day 30) |
| 6 | Sequences | Auto-increment number generators |
| 7 | UDFs (User-Defined Functions) | Custom SQL / JavaScript / Python / Java / Scala scalar or table functions |
| 8 | Stored Procedures | Procedural code blocks: control flow, looping, transactions (Day 41+) |
| 9 | Pipes | Snowpipe ingestion definitions (Day 32) |
| 10 | Shares | The provider-side handle for outbound Secure Data Sharing (Day 45) |
| 11 | ML Models | First-class object in C03. Snowflake ML / Cortex models stored alongside tables. |
| 12 | Applications | Snowflake Native App instances installed in your account (Day 46) |
Why this list matters. The recurring exam question asks “Which of the following are database objects in Snowflake?” The options mix five or six items. Two or three are real database objects, and the rest are account-level distractors. ML models on this list is the C03 addition most candidates miss. If you have used the Model Registry, they look schema-level. If you skipped the ML chapter, you will not recognize them as a schema object on exam day.
Micro-Concept 3: Account-Level vs Database-Level Objects
Everything below sits above the database in the hierarchy. None of these are database objects. The exam uses them as distractors in multi-select questions.
→ Users, Roles, Warehouses
→ Resource Monitors
→ Network Policies, Authentication Policies, Session Policies
→ Storage Integrations, API Integrations, Notification Integrations
→ Databases themselves, Shares (outbound)
The trap is easy to spot with a name-format check. Three-part names like SALES_DB.PUBLIC.ORDERS are database objects. One-part names like ANALYST_ROLE are account-level. Anything you address without a db.schema. prefix sits above the database.
Micro-Concept 4: First System Catalog (INFORMATION_SCHEMA)
Every database in your account contains its own INFORMATION_SCHEMA. It holds views and table functions describing the objects in that database. A small set of account-wide views like DATABASES and APPLICABLE_ROLES also live there.
→ Real-time: no latency. What you query is what is true right now.
→ Limited history: retention varies from 7 days to 6 months depending on the view.
→ Does NOT include dropped objects. Once a table is dropped, it disappears from INFORMATION_SCHEMA.
→ Use it for: live state, current DDL, recent query history (last 7 days).
Example: SELECT * FROM sales_db.information_schema.tables;
Micro-Concept 5: ACCOUNT_USAGE, the Account-Wide Audit Catalog
Snowflake provisions every account with a read-only shared database named SNOWFLAKE. Inside it is the ACCOUNT_USAGE schema. This is the single audit and metering catalog for the entire account.
→ Latency varies by view. Most views land around 2 hours, with the full range running roughly 45 minutes to 3 hours. Not real-time.
→ 1-year retention (365 days) for historical usage views.
→ INCLUDES dropped objects. The whole point is long-term auditability. Dropped tables remain in the view with a non-null DELETED timestamp.
→ Use it for: long-term audit trails, billing reconciliation, governance reporting, finding what was dropped and when.
Example: SELECT * FROM snowflake.account_usage.tables;
Micro-Concept 6: INFORMATION_SCHEMA vs ACCOUNT_USAGE Comparison
| Property | INFORMATION_SCHEMA | ACCOUNT_USAGE |
|---|---|---|
| Where it lives | Inside every database (one per DB) | In the special SNOWFLAKE database, schema ACCOUNT_USAGE |
| Scope | Mostly the parent database (some account-wide functions) | The entire account |
| Latency | Real-time (no latency) | ~45 min to 3 hours (varies by view; many ~2 hours) |
| Retention | 7 days to 6 months (varies by view) | 365 days (1 year) |
| Includes dropped objects? | ❌ No | ✅ Yes |
| Default access | Available to anyone with USAGE on the database | By default, only ACCOUNTADMIN; can be granted via IMPORTED PRIVILEGES on SNOWFLAKE |
| Best for | Right-now state, recent activity | Long-term audit, billing, dropped-object investigation |
Two traps the exam reuses. First, “ACCOUNT_USAGE has 45-minute latency” sounds right but is wrong. 45 minutes is the floor, not a fixed value. The answer the exam wants is the full 45 min to 3 hr range. Second, “INFORMATION_SCHEMA shows dropped objects” is false. That capability belongs only to ACCOUNT_USAGE. A third one I have seen in practice tests: SYSADMIN queries SNOWFLAKE.ACCOUNT_USAGE.TABLES and gets zero rows. The fix is not “switch to INFORMATION_SCHEMA”. The fix is “ACCOUNTADMIN needs to grant IMPORTED PRIVILEGES on the SNOWFLAKE database.”
Cheat Sheet
| Concept | What to remember |
|---|---|
| Hierarchy | Organization → Account → Database → Schema → Object |
| Fully-qualified name | database.schema.object (three parts) |
| Auto-created schemas | PUBLIC and INFORMATION_SCHEMA in every new database |
| 12 database objects | Tables, Views, Schemas, Stages, File Formats, Sequences, UDFs, Stored Procedures, Pipes, Shares, ML Models, Applications |
| NOT database objects | Users, Roles, Warehouses, Resource Monitors, Network Policies, Integrations |
| INFORMATION_SCHEMA | Per-database, real-time, 7d–6mo history, no dropped objects |
| ACCOUNT_USAGE | Account-wide (in SNOWFLAKE db), 45min–3hr latency, 365d history, includes dropped objects |
| Find dropped tables | Use ACCOUNT_USAGE. It is the only one that retains them. |
| Real-time current state | Use INFORMATION_SCHEMA. No latency. |
The INFORMATION_SCHEMA vs ACCOUNT_USAGE question shows up on virtually every COF-C03 attempt. Two differentiators carry the decision. First, latency (real-time versus up to 3 hours). Second, whether dropped objects are retained (no versus yes). On the exam, the phrase “table I dropped last week” always points to ACCOUNT_USAGE. The phrase “current rowcounts right now” always points to INFORMATION_SCHEMA. The 12-database-objects question runs the same playbook every time. It lists five or six items and asks you to pick the real database objects. The repeat distractors are users, roles, warehouses, resource monitors, and network policies. None of those live inside a schema, so none of them count.
Hands-On Lab
Build the hierarchy. Create a database, then a schema inside it:
CREATE DATABASE IF NOT EXISTS day5_lab;
CREATE SCHEMA IF NOT EXISTS day5_lab.test_schema;
USE day5_lab.test_schema;SHOW SCHEMAS IN DATABASE day5_lab; and you will see all three listed: TEST_SCHEMA, PUBLIC, and INFORMATION_SCHEMA.Create one of each major database object. Five different object types go into the same schema. We will see them all in INFORMATION_SCHEMA together:
-- Table
CREATE TABLE customers (id INT, name STRING);
-- View
CREATE VIEW vip_customers AS SELECT * FROM customers WHERE id < 100;
-- Sequence
CREATE SEQUENCE customer_seq START 1 INCREMENT 1;
-- File format
CREATE FILE FORMAT csv_format TYPE = CSV FIELD_DELIMITER = ',';
-- Stage (internal named stage)
CREATE STAGE my_stage;Inspect via INFORMATION_SCHEMA. Each object type has its own view. Scoped to this database, with zero latency:
SELECT table_schema, table_name, table_type
FROM day5_lab.information_schema.tables
WHERE table_schema = 'TEST_SCHEMA';
SELECT * FROM day5_lab.information_schema.sequences;
SELECT * FROM day5_lab.information_schema.file_formats;
SELECT * FROM day5_lab.information_schema.stages;Confirm context with CURRENT_* functions. These help debug a failing query when you are unsure of the session’s environment:
SELECT
CURRENT_DATABASE() AS db,
CURRENT_SCHEMA() AS sch,
CURRENT_WAREHOUSE() AS wh,
CURRENT_ROLE() AS rl,
CURRENT_USER() AS usr;Compare INFORMATION_SCHEMA vs ACCOUNT_USAGE. Run the same kind of query against both catalogs. The difference in scope and latency is the point:
-- Real-time, scoped to one database
SELECT COUNT(*) AS info_schema_tables
FROM day5_lab.information_schema.tables;
-- Account-wide, latent
-- (May need ACCOUNTADMIN role; if not, skip this query)
USE ROLE ACCOUNTADMIN;
SELECT COUNT(*) AS account_usage_tables
FROM snowflake.account_usage.tables;Prove the dropped-objects difference. This is the step that makes the rule stick. Drop the table and query both catalogs:
DROP TABLE day5_lab.test_schema.customers;
-- Gone from INFORMATION_SCHEMA immediately
SELECT * FROM day5_lab.information_schema.tables
WHERE table_name = 'CUSTOMERS';
-- (zero rows)
-- Still in ACCOUNT_USAGE (even after the drop is processed,
-- the row will be present with a non-null DELETED timestamp)
SELECT table_name, deleted
FROM snowflake.account_usage.tables
WHERE table_name = 'CUSTOMERS'
AND table_schema = 'TEST_SCHEMA';
-- May take an hour+ to update; the row will eventually
-- show DELETED with a timestampDELETED column. That single column is the practical anchor for every dropped-object exam scenario.Cleanup. Drop the entire database in one statement:
DROP DATABASE IF EXISTS day5_lab;
-- This drops the database and every schema/object inside it.
-- (We re-create day5_lab on Day 45 for the share lab —
-- no problem, we'll recreate it then.)Snowflake Documentation
External References
Catalog reference material.
Practice Questions
Options:
A. Stages
B. Resource Monitors
C. Pipes
D. Network Policies
E. ML Models
Why A: Stages are schema-scoped. They live inside a database and schema, and their fully-qualified name is db.schema.stage_name. Definitely a database object.
Why C: Pipes are the Snowpipe ingestion definitions, also schema-scoped. They sit beside the tables they load into.
Why E: ML Models became first-class schema-level database objects in C03. This is the addition most candidates miss. The ML chapter is often the last one studied.
Why not B: Resource monitors are account-level. Create them with CREATE RESOURCE MONITOR at the account scope. They govern credit usage, not schema contents.
Why not D: Network policies are account-level too. They govern who can connect to the account from where, not what is inside a schema.
Options:
A. INFORMATION_SCHEMA includes records for dropped objects
B. ACCOUNT_USAGE retains historical data for 365 days
C. INFORMATION_SCHEMA has up to 3 hours of latency
D. ACCOUNT_USAGE includes records for dropped objects
E. ACCOUNT_USAGE is real-time with no latency
Why B: The historical ACCOUNT_USAGE views retain one year of data (365 days). That long retention window is the reason you reach for it instead of INFORMATION_SCHEMA. Audit questions going back more than a couple of weeks always point to ACCOUNT_USAGE.
Why D: Retaining dropped objects is exactly the design intent. The DELETED column tells you when each one was dropped. That column is what makes long-term reconciliation possible.
Why not A: INFORMATION_SCHEMA only shows currently-existing objects. The moment you drop a table, it leaves the live catalog.
Why not C: INFORMATION_SCHEMA is real-time. The latency claim belongs to ACCOUNT_USAGE. Swapping these two is a common distractor pattern.
Why not E: ACCOUNT_USAGE has 45 minutes to 3 hours of latency, varying by view. Most views land around the 2-hour mark.
Options:
A. FINANCE.INFORMATION_SCHEMA.TABLES
B. SNOWFLAKE.ACCOUNT_USAGE.TABLES
C. SHOW TABLES IN DATABASE FINANCE
D. FINANCE.PUBLIC.TABLES
Why B: ACCOUNT_USAGE.TABLES is the only catalog that retains rows for dropped tables. Each carries a non-null DELETED timestamp marking when it went away. The 365-day retention window comfortably covers the 90-day requirement. Filter WHERE table_catalog = 'FINANCE' AND deleted IS NOT NULL and you have the audit list.
Why not A: INFORMATION_SCHEMA only shows currently-existing objects. Dropped tables are not there to find.
Why not C: SHOW TABLES lists tables that exist right now. It is a metadata query, not a historical one.
Why not D: There is no system view at FINANCE.PUBLIC.TABLES. PUBLIC is the user schema, not a system catalog. This distractor catches candidates who skim the path.
Options:
A. All ACCOUNT_USAGE views have a fixed 45-minute latency
B. Latency varies by view, in the range of approximately 45 minutes to 3 hours
C. ACCOUNT_USAGE has no latency; it is updated synchronously
D. ACCOUNT_USAGE views are refreshed once per day at midnight UTC
Why B: Snowflake’s documentation states latency varies by view. Most views are around 2 hours. The floor is 45 minutes and the ceiling is 3 hours for the slower ones. The phrasing “approximately 45 minutes to 3 hours” is the exact range to anchor on.
Why not A: 45 minutes is the best-case latency for some views, not a universal value. Treating it as fixed is the classic over-simplification trap.
Why not C: Real-time access is the INFORMATION_SCHEMA property. If you see “no latency” attached to ACCOUNT_USAGE, the option is wrong.
Why not D: ACCOUNT_USAGE is continuously updated by Snowflake’s internal metadata processes. It is not a nightly batch job.
Options:
A. SALES_PROD = account, RAW = database, ORDERS_2024 = table
B. SALES_PROD = database, RAW = schema, ORDERS_2024 = table
C. SALES_PROD = schema, RAW = table, ORDERS_2024 = column
D. SALES_PROD = warehouse, RAW = role, ORDERS_2024 = view
Why B: Snowflake’s three-part naming is always database.schema.object, left to right, broadest to narrowest. SALES_PROD is the database, RAW is the schema, ORDERS_2024 is the object. It is typically a table or view.
Why not A: Account names do not appear in fully-qualified object identifiers in queries. Inside a session you are already in an account, so the qualifier starts at the database.
Why not C: The three-part identifier path is database, schema, object. It is not schema, table, column. C swaps the levels and mislabels the parts.
Why not D: Warehouses, roles, and views never appear together in a single dotted path. This option is built to catch candidates who skim the structure rather than reading it.
Today you learned: Snowflake’s hierarchy is Organization → Account → Database → Schema → Object. The COF-C03 exam guide names twelve schema-level database objects. The list: Tables, Views, Schemas, Stages, File Formats, Sequences, UDFs, Stored Procedures, Pipes, Shares, ML Models, and Applications. Users, roles, warehouses, resource monitors, and network policies are account-level. They sit above the database and are not database objects. INFORMATION_SCHEMA is per-database, real-time, and does not retain dropped objects. ACCOUNT_USAGE lives in the SNOWFLAKE database and is account-wide. It has 45 min – 3 hr latency. It retains 365 days of history and includes dropped objects.
Key takeaway: Two memorizations carry the most exam weight here. The 12-object list answers every “which is a database object?” multi-select. The four-row INFORMATION_SCHEMA vs ACCOUNT_USAGE comparison answers every catalog-selection scenario. The four rows are latency, scope, retention, and dropped-objects.
Tomorrow (Day 6): Session, context, and parameter precedence. These are the rules that govern which setting wins when you have set a value at multiple levels. The four levels are Account, User, Session, and Object. The exam loves three-layer scenarios where you predict the effective value. We’ll work the precedence rule from both directions.
