Account Administration and Metadata

Organizations, account locators, roles, users, network policies, INFORMATION_SCHEMA, SHOW, DESCRIBE, and the DDL lifecycle.

Every chapter so far has lived inside a database — tables, warehouses, roles, queries. This last one climbs the ladder the other way: up past the database, past the schema, to the account itself and the organization above it, and then back down through the layer that makes all of it legible — the metadata. An account you can’t inspect is an account you operate by superstition. So the closing move of this series is to hand you the map and the flashlight: where objects actually live in Snowflake’s hierarchy, and how to ask the catalog what’s really there.

The shape above the database

You’ve been typing three-part names — sales.public.orders — this whole book. Above sales sits the account, and above the account sits the organization. Two identifiers name your account, and they are not interchangeable:

  • The account identifier is orgname-accountname — a hyphenated pair like acmecorp-analytics. This is the modern, human-readable name, and it’s what appears in your Snowsight URL (https://acmecorp-analytics.snowflakecomputing.com) and in every driver connection string. It’s stable and readable, which is exactly why Snowflake nudges you toward it.
  • The account locator is the older form — an opaque string like xy12345 bound to a specific cloud region (xy12345.us-east-1). It still works, it still shows up in legacy scripts, but it leaks the region into the name and reads like a license plate. Prefer the org-account form everywhere you can.

You can always ask which account you’re in:

SELECT CURRENT_ORGANIZATION_NAME(),
       CURRENT_ACCOUNT_NAME(),
       CURRENT_ACCOUNT(),        -- the locator
       CURRENT_REGION();

An account lives in exactly one cloud region — one of AWS, Azure, or GCP, in one geography. That single fact drives a lot: where your data physically sits, which regulations apply, and what “replicate to another region for disaster recovery” even means (we get there at the end).

Organizations and ORGADMIN

An organization is the container Snowflake gives a customer to hold multiple accounts — a production account, a dev account, a separate account in the EU for data-residency, and so on. The role that operates at that level is ORGADMIN, and it does a small, powerful set of things that no account-level role can:

  • Create accounts within the organization (CREATE ACCOUNT ...), each in a region you choose.
  • View organization-wide usage and billing across every account, through the SNOWFLAKE.ORGANIZATION_USAGE schema (more below).
  • Enable replication between accounts, the prerequisite for cross-region and cross-cloud disaster recovery.

ORGADMIN is deliberately narrow. It is not a super-ACCOUNTADMIN — it can’t read your tables or manage your grants. It manages the accounts themselves and the billing envelope around them. In a one-account shop you may never activate it; the day you spin up a second account, it’s the role that does it:

USE ROLE ORGADMIN;
SHOW ACCOUNTS;                    -- every account in the org, with its region and edition
SHOW ORGANIZATION ACCOUNTS;      -- same, with more org-level detail

The mental model that keeps the levels straight: users and roles live inside an account. They do not span accounts. A person who needs access to your prod account and your EU account is two user records, one in each. The account is the security boundary — grants, roles, warehouses, and objects all stop at its edge. The organization is a billing-and-provisioning boundary that sits above that, not a shared login pool.

Two ways to query metadata, and when to use each

Snowflake exposes its own catalog as SQL. This is one of its quietly great features — “who read this table last week?” is a query, not a support ticket. But there are two surfaces with the same-sounding data and very different characteristics, and picking the wrong one is a classic newcomer stumble.

INFORMATION_SCHEMA — per-database, real-time, no history

Every database has its own INFORMATION_SCHEMA — a read-only schema of views and table functions describing that database’s objects. It’s the SQL-standard catalog, it’s real-time (no latency), and it holds no history of dropped objects — it shows you what exists right now.

-- every table in the SALES database, with row counts and byte sizes
SELECT table_schema, table_name, row_count, bytes
FROM sales.information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY bytes DESC;

Alongside the views are table functions — parameterized, so you call them like functions and select from the result. These answer time-bounded operational questions for the current account, live:

-- the last hour of queries, most expensive first
SELECT query_id, user_name, warehouse_name, execution_time, query_text
FROM TABLE(sales.information_schema.query_history(
        end_time_range_start => DATEADD('hour', -1, CURRENT_TIMESTAMP())))
ORDER BY execution_time DESC
LIMIT 20;

Storage detail lives here too, in the TABLE_STORAGE_METRICS view — which is where you’d go to see how many bytes a table is spending on Time Travel and Fail-safe versus active storage:

SELECT table_name, active_bytes, time_travel_bytes, failsafe_bytes
FROM sales.information_schema.table_storage_metrics
WHERE table_schema = 'PUBLIC';

Reach for INFORMATION_SCHEMA when you want the current truth, immediately, for one database.

SNOWFLAKE.ACCOUNT_USAGE — account-wide, historical, latent

The SNOWFLAKE database (a shared, read-only database present in every account) contains the ACCOUNT_USAGE schema, and it’s a different animal:

  • Account-wide — every database, warehouse, user, and role in the whole account, in one place.
  • Historical, up to 365 days — it keeps a rolling year of activity.
  • Includes dropped objects — a DELETED (or DELETED_ON) column marks objects and grants that no longer exist. Filter WHERE deleted IS NULL when you want only what’s live.
  • Latent — data is not real-time. Latency ranges roughly from 45 minutes to 3 hours depending on the view (query history lands fastest, access history slowest). It is an analytics surface, not a live monitor.

Access is gated: by default only ACCOUNTADMIN can read SNOWFLAKE, and you grant the IMPORTED PRIVILEGES on the SNOWFLAKE database to open it to other roles.

-- credits burned per warehouse, last 30 days — a billing question,
-- so it belongs in ACCOUNT_USAGE, not INFORMATION_SCHEMA
SELECT warehouse_name,
       SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY warehouse_name
ORDER BY credits DESC;

Because it keeps a year, ACCOUNT_USAGE is also where you watch things trend — storage creeping up, a warehouse quietly getting more expensive month over month:

-- average daily storage bytes per month, last 6 months
SELECT DATE_TRUNC('month', usage_date) AS mth,
       AVG(storage_bytes + stage_bytes + failsafe_bytes) AS avg_bytes
FROM snowflake.account_usage.storage_usage
WHERE usage_date >= DATEADD('month', -6, CURRENT_DATE())
GROUP BY 1
ORDER BY 1;

The decision rule is clean. Right now, one database → INFORMATION_SCHEMA. Trends, audits, billing, “what happened last month,” or anything about objects that no longer exist → ACCOUNT_USAGE. The same-named QUERY_HISTORY exists in both, and the difference is exactly this: the table function shows the live tail for the current account; the view shows a year of history with a couple of hours’ lag. Ask the same “how much did I query yesterday” question of both and you’ll get slightly different answers for the last hour — that gap is the latency, and knowing which surface you asked is how you stop being surprised by it.

ORGANIZATION_USAGE — the whole org’s bill

One level up again, the SNOWFLAKE database also carries ORGANIZATION_USAGE — the same idea as ACCOUNT_USAGE but aggregated across every account in the organization, readable by ORGADMIN. This is where you answer “what did the entire company spend on Snowflake last quarter,” across prod, dev, and EU accounts at once:

USE ROLE ORGADMIN;
SELECT account_name, usage_date, usage_in_currency
FROM snowflake.organization_usage.usage_in_currency_daily
WHERE usage_date >= DATEADD('month', -1, CURRENT_DATE())
ORDER BY usage_date;

Three surfaces, one hierarchy: INFORMATION_SCHEMA is one database’s live catalog, ACCOUNT_USAGE is one account’s year of history, ORGANIZATION_USAGE is the whole org’s bill.

SHOW, DESCRIBE, and turning their output into a table

Not everything is a view. The commands you’ll type most for quick inspection are SHOW and DESCRIBE, and they behave a little differently from a SELECT.

SHOW WAREHOUSES;                          -- all warehouses, with size, state, credits
SHOW TABLES IN SCHEMA sales.public;       -- tables, with rows, bytes, owner
SHOW GRANTS TO ROLE analyst;              -- what a role can do
DESCRIBE TABLE sales.public.orders;       -- columns, types, nullability, defaults
DESC USER dana;                            -- a user's properties (DESC is the alias)

SHOW is metadata-only and fast — it doesn’t scan data or spin up a warehouse, so it works even with no warehouse running. The catch is that its output isn’t a normal result set you can filter with a WHERE clause. To treat it as a table — to sort it, filter it, join it — pipe it through RESULT_SCAN(LAST_QUERY_ID()), which re-reads the previous query’s result as a queryable relation:

SHOW TABLES IN SCHEMA sales.public;

SELECT "name", "rows", "bytes"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "rows" > 1000000
ORDER BY "bytes" DESC;

Note the double quotes. SHOW output columns are named in lowercase ("name", "rows", "created_on"), and because Snowflake case-folds unquoted identifiers to uppercase, you must quote them or the column won’t be found — one of those case-folding traps we’ve hit all book. LAST_QUERY_ID() grabs the id of the immediately preceding statement; pass an explicit query id if you want an earlier one.

GET_DDL — recover an object’s definition

When you need the actual CREATE statement for something — to copy it to another environment, review it, or check it into version control — GET_DDL reconstructs it from the catalog:

SELECT GET_DDL('table',  'sales.public.orders');
SELECT GET_DDL('view',   'sales.public.order_summary');
SELECT GET_DDL('schema', 'sales.public');   -- DDL for every object in the schema

GET_DDL('schema', ...) is the tidy one: it emits the full CREATE statements for everything in a schema, in dependency order, which is a serviceable way to snapshot a schema’s structure into a file. It’s structure only — no data — but for “what does this object actually look like, exactly,” it beats piecing it together from DESCRIBE.

The DDL lifecycle: change objects without breaking them

Creating objects is the easy half. Operating a warehouse means changing them — renaming, deploying new versions, recovering mistakes — and Snowflake’s DDL verbs for that are deployment tools, not trivia. Here’s the lifecycle as one coherent story.

ALTER is the workhorse for in-place change — rename a column, add a column, change a parameter, none of which re-creates the object:

ALTER TABLE sales.public.orders RENAME COLUMN o_comment TO o_note;
ALTER TABLE sales.public.orders SET DATA_RETENTION_TIME_IN_DAYS = 30;
ALTER WAREHOUSE compute_wh SET AUTO_SUSPEND = 60;

RENAME renames the object itself. Renaming a table is a pure metadata operation — instant, no data movement — but every view, task, or piece of code that referenced the old name now points at nothing, so rename with a search for dependents in hand:

ALTER TABLE sales.public.orders RENAME TO sales.public.orders_v1;

ALTER also reaches up to the account. Account-level parameters set defaults that every session, warehouse, or object inherits unless it overrides them — the Time-Travel retention default, the query timeout, the week-start for date functions. Set once by ACCOUNTADMIN, they ripple everywhere:

USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 7;     -- default Time Travel for new objects
ALTER ACCOUNT SET STATEMENT_TIMEOUT_IN_SECONDS = 3600; -- kill runaway queries after an hour
SHOW PARAMETERS IN ACCOUNT;                            -- see all of them, with their levels

The precedence runs account → warehouse/user → session → object: the most specific setting wins, and SHOW PARAMETERS tells you which level a given value came from.

DROP and UNDROP are the safety net you met with Time Travel. DROP doesn’t erase — it removes the object from the namespace but keeps it recoverable for the object’s Time Travel retention window:

DROP TABLE sales.public.orders;
UNDROP TABLE sales.public.orders;     -- back, within the retention window

UNDROP restores the most recently dropped object of that name. Drop a table, create a new one with the same name, then UNDROP, and you’ll collide — you’d rename the new one out of the way first. This is the everyday “I deleted the wrong thing” recovery, and it’s why the retention window is worth setting deliberately.

SWAP WITH is the one people don’t discover until they need it, and then it’s a revelation. It atomically exchanges the names of two tables — the metadata pointers cross in a single instant, with no window where either name is missing:

-- build the new version fully, off to the side
CREATE OR REPLACE TABLE sales.public.orders_next AS
SELECT ... ;                          -- fresh, validated data

-- then flip them in one atomic move
ALTER TABLE sales.public.orders SWAP WITH sales.public.orders_next;

After the swap, orders is the new data and orders_next holds the old — instant, all-or-nothing, zero downtime. This is blue/green deployment for a table: readers querying orders never see a half-built state, and if the new version is wrong you swap back. It’s the professional alternative to TRUNCATE-then-reload, which leaves the table empty for however long the load takes.

CREATE OR REPLACE vs CREATE IF NOT EXISTS

Two CREATE variants look similar and behave oppositely, and the difference is all about Time Travel and object identity:

  • CREATE OR REPLACE TABLE drops any existing table of that name and creates a brand-new one. It always succeeds and always gives you a clean object — but it’s a new object. The old version is dropped (retained in Time Travel, recoverable by renaming the new one aside and UNDROP-ing), but the new table starts with a new identity: its load history resets, and — importantly — any stream on the old table breaks, because the stream’s offset was tracking an object that no longer exists. Convenient for dev iteration; a footgun in production if something downstream was tracking the table.
  • CREATE TABLE IF NOT EXISTS creates the table only if it’s absent, and is a no-op if it already exists — it does not touch the existing table, its data, or its history. This is the idempotent, safe-to-re-run form you want in deployment scripts.

The rule: OR REPLACE when you genuinely want a fresh object and accept the reset; IF NOT EXISTS when re-running a script must not clobber what’s already there. Reaching for OR REPLACE out of habit is how people accidentally sever a stream or blow away a table’s Time-Travel lineage.

Governance and observability: who did what

Roles decide who can do things; observability tells you what they actually did. The Account Usage views turn that into SQL, and a handful of them are the ones you’ll live in.

QUERY_HISTORY is the audit backbone — every statement, who ran it, on which warehouse, how long it took, how many bytes it scanned:

SELECT user_name, query_type, execution_time, bytes_scanned, query_text
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND query_type = 'DELETE'          -- who deleted data this week?
ORDER BY start_time DESC;

LOGIN_HISTORY answers “who logged in, from where, and did it succeed” — the first stop for a security review or a locked-out user:

SELECT user_name, event_timestamp, client_ip, first_authentication_factor, is_success
FROM snowflake.account_usage.login_history
WHERE event_timestamp >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND is_success = 'NO'              -- failed logins in the last day
ORDER BY event_timestamp DESC;

GRANTS_TO_ROLES and GRANTS_TO_USERS — met in the roles chapter — are the queryable, historical picture of who’s been granted what, deleted grants included. They’re how you answer “who can read the payroll table?” without clicking through the UI.

ACCESS_HISTORY is the sharpest governance tool, and it’s Enterprise Edition and above. Where QUERY_HISTORY tells you a query ran, ACCESS_HISTORY tells you exactly which columns and tables that query read and wrote — the actual data-access lineage, per statement:

SELECT query_id, user_name, direct_objects_accessed, objects_modified
FROM snowflake.account_usage.access_history
WHERE query_start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP());

direct_objects_accessed and objects_modified are semi-structured columns (arrays of objects) — the semi-structured skills from earlier in the book pay off here — and they’re the foundation for real answers to “has anyone ever read this sensitive column,” which is a compliance question you cannot answer from query text alone.

Account-level policies

Two guardrails clamp down at the account level and are worth naming even if you set them once and forget them.

Network policies restrict which IP addresses can even reach the account. You define the allowed (and blocked) ranges once, then attach the policy to the account or to specific users:

CREATE NETWORK POLICY corp_only
  ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.42');

ALTER ACCOUNT SET NETWORK_POLICY = corp_only;

Session policies govern session behavior — most usefully the idle timeout, so an abandoned Snowsight tab doesn’t stay authenticated all day:

CREATE SESSION POLICY tight_sessions
  SESSION_IDLE_TIMEOUT_MINS = 30;

ALTER ACCOUNT SET SESSION POLICY tight_sessions;

Both are account-wide by default and can be scoped to individual users when one group needs different rules.

Replication and failover, at a “what it is” level

Finally, the big-lever BCDR (business continuity and disaster recovery) story, which you should recognize the shape of even before you need it. Because an account lives in a single region, protecting against a whole-region outage means keeping a copy somewhere else — and that’s what replication and failover do.

  • Replication continuously copies databases (and account objects) from a primary account to a secondary account, potentially in another region or even another cloud provider. The secondary is read-only and kept in sync on a schedule.
  • Failover promotes that secondary to primary when disaster strikes, redirecting your workloads to it. Grouped into failover groups, this is available on Business Critical Edition and above.

You won’t stand this up on day one, and this book won’t walk the setup — but the takeaway is the durable one: your disaster-recovery posture is a function of your account and region, ORGADMIN enables the replication that underpins it, and it’s a deliberate design decision, not a checkbox. When someone asks “what happens if AWS us-east-1 goes down,” this is the vocabulary the answer is built from.

Examples in this chapter are docs-checked against the series’ stated versions, but they were not executed in this repository unless a companion project explicitly says so.

Final thoughts

That’s the whole foundation. Eighteen chapters ago Snowflake was a login screen; now you can reason about it from the organization down to a single micro-partition. You’ve stood up warehouses and understood why they cost what they cost, shaped roles so the account stays legible when it’s no longer just you, time-traveled and cloned, loaded and queried semi-structured data, tuned for performance, shared data, and — in this chapter — learned to interrogate the account itself and change its objects without breaking them. The connective thread through all of it is the same one this chapter made explicit: in Snowflake, the system describes itself in SQL. Cost, access, lineage, storage, history — none of it is hidden behind a dashboard you have to trust. It’s a query away, which means operating Snowflake well is mostly a matter of knowing which catalog to ask.

That’s also the honest boundary of “foundations.” You now have the map; the territory is where the real work happens, and it happens by building. This series was always the groundwork for three siblings that put Snowflake to work:

  • Analytics Engineering with dbt — turn the SQL you’ve been writing into a real software project: models, tests, and a DAG, with version control and CI.
  • The Modern Data Stack: Snowflake + dbt + Airflow — the capstone that points dbt at this warehouse with key-pair auth and orchestrates it end to end with Airflow and Cosmos.
  • Dimensional Modeling — what to actually build in the warehouse: star schemas, fact and dimension tables, and slowly changing dimensions, each modeled in dbt on Snowflake.

Foundations were never the destination. They’re the floor you build the warehouse on — and now the floor is solid. Go build something on it.

Comments