TopolearnEvery query depends on the right context and fully qualified names, and identifier case trips up everyone once.
Objects in Snowflake live in a hierarchy: an account contains databases, a database contains schemas, and a schema contains tables, views, stages, file formats, functions, procedures, streams and tasks. Warehouses, users and roles are account-level objects.
A fully qualified name is database.schema.object, e.g. SALES.PUBLIC.ORDERS. Unqualified names resolve against the session's current database and schema, which you set with USE DATABASE, USE SCHEMA or both at once (USE SCHEMA sales.public). The session also has a current role and current warehouse, and a query needs all of them: a role with privileges and a running warehouse.
Identifiers: unquoted names are stored and resolved in upper case, so orders, Orders and ORDERS are the same table. Double quotes make a name case-sensitive: "orders" is a different table from orders. Tools that quote everything (some ORMs and loaders) can therefore create lowercase tables you then have to quote forever. Stick to unquoted names.
Useful commands: SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES IN SCHEMA x, DESCRIBE TABLE t, and GET_DDL('TABLE', 't') to see the DDL of any object. INFORMATION_SCHEMA views in each database answer the same questions in SQL.
Example
CREATE DATABASE IF NOT EXISTS shop;
CREATE SCHEMA IF NOT EXISTS shop.raw;
USE SCHEMA shop.raw;
CREATE TABLE orders (id INT, amount NUMBER(10, 2));
SELECT * FROM shop.raw.ORDERS; -- same table: unquoted names are upper-cased
CREATE TABLE "orders" (id INT); -- a DIFFERENT, case-sensitive table
SHOW TABLES IN SCHEMA shop.raw;
-- ORDERS
-- orders
SELECT GET_DDL('TABLE', 'ORDERS');
-- create or replace TABLE ORDERS ( ID NUMBER(38,0), AMOUNT NUMBER(10,2) );Remember
- Hierarchy: account → database → schema → objects
- A query needs context: role, warehouse, database and schema
- Unquoted identifiers are upper-cased; quoted ones are case-sensitive
Watch out for
- Creating objects with quoted lowercase names and then failing to find them unquoted.