1-- Create a standard permanent database
2CREATE DATABASE prod_db
3DATA_RETENTION_TIME_IN_DAYS = 14
4COMMENT = 'Production analytics database';
5
6-- Create a transient database (no Fail-safe, max 1 day Time Travel)
7-- All schemas and tables within inherit the transient property
8CREATE TRANSIENT DATABASE staging_db
9DATA_RETENTION_TIME_IN_DAYS = 1
10COMMENT = 'ETL staging area — transient, no Fail-safe';
11
12-- Switch the active database for the current session
13USE DATABASE prod_db;
14
15-- Rename a database
16ALTER DATABASE prod_db RENAME TO production_db;
17
18-- Change Time Travel retention on an existing database
19ALTER DATABASE production_db
20SET DATA_RETENTION_TIME_IN_DAYS = 30;
21
22-- Show all databases accessible to the current role
23SHOW DATABASES;
24
25-- Show databases matching a pattern
26SHOW DATABASES LIKE 'prod%';
27
28-- Drop a database (irreversible — Time Travel window starts here)
29DROP DATABASE old_db;
30
31-- Undrop within Time Travel window
32UNDROP DATABASE old_db;

1-- Create a standard schema
2CREATE SCHEMA prod_db.analytics
3DATA_RETENTION_TIME_IN_DAYS = 14
4COMMENT = 'Analytics reporting schema';
5
6-- Create a transient schema (no Fail-safe)
7CREATE TRANSIENT SCHEMA prod_db.staging
8DATA_RETENTION_TIME_IN_DAYS = 1;
9
10-- Switch active schema for the session
11USE SCHEMA prod_db.analytics;
12
13-- Alternatively, use the two-part notation
14USE prod_db.analytics;
15
16-- Rename a schema
17ALTER SCHEMA analytics RENAME TO reporting;
18
19-- Show all schemas in the current database
20SHOW SCHEMAS;
21
22-- Show schemas in a specific database
23SHOW SCHEMAS IN DATABASE prod_db;
24
25-- Drop and undrop schema
26DROP SCHEMA old_schema;
27UNDROP SCHEMA old_schema;

1-- 1. Permanent table (default)
2CREATE TABLE prod_db.analytics.customers (
3customer_id    NUMBER(10, 0)    NOT NULL,
4email          VARCHAR(255)     NOT NULL,
5created_at     TIMESTAMP_NTZ    DEFAULT CURRENT_TIMESTAMP(),
6is_active      BOOLEAN          DEFAULT TRUE
7);
8
9-- 2. Transient table — no Fail-safe, max 1-day Time Travel
10CREATE TRANSIENT TABLE staging.raw_customer_events (
11event_id       VARCHAR(36),
12event_type     VARCHAR(100),
13payload        VARIANT,
14loaded_at      TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
15)
16DATA_RETENTION_TIME_IN_DAYS = 1;
17
18-- 3. Temporary table — dropped when session ends
19CREATE TEMPORARY TABLE temp_dedup_results (
20customer_id    NUMBER,
21row_num        NUMBER
22);
23
24-- 4. External table — data lives in S3
25CREATE EXTERNAL TABLE ext_raw_logs (
26log_date       DATE        AS (TO_DATE($1:log_date::STRING)),
27log_level      VARCHAR     AS ($1:level::STRING),
28message        VARCHAR     AS ($1:message::STRING)
29)
30PARTITION BY (log_date)
31LOCATION = @my_s3_stage/logs/
32FILE_FORMAT = (TYPE = 'JSON')
33AUTO_REFRESH = TRUE;

1-- CREATE TABLE AS SELECT (CTAS) — creates and populates in one step
2CREATE TABLE analytics.customer_summary AS
3SELECT
4customer_id,
5COUNT(*)        AS order_count,
6SUM(amount)     AS total_spend,
7MAX(order_date) AS last_order_date
8FROM analytics.orders
9GROUP BY customer_id;
10
11-- CREATE TABLE LIKE — copies structure only, no data
12CREATE TABLE analytics.customer_summary_backup
13LIKE analytics.customer_summary;
14
15-- CREATE OR REPLACE TABLE — atomically drops and recreates
16-- WARNING: drops all data and resets Time Travel history
17CREATE OR REPLACE TABLE analytics.staging_load (
18id        NUMBER AUTOINCREMENT,
19raw_data  VARIANT
20);
21
22-- ALTER TABLE — add, drop, rename columns
23ALTER TABLE analytics.customers
24ADD COLUMN phone_number VARCHAR(20);
25
26ALTER TABLE analytics.customers
27DROP COLUMN phone_number;
28
29ALTER TABLE analytics.customers
30RENAME COLUMN email TO email_address;
31
32-- Change Time Travel retention at table level
33ALTER TABLE analytics.customers
34SET DATA_RETENTION_TIME_IN_DAYS = 30;
35
36-- SHOW commands for tables
37SHOW TABLES;
38SHOW TABLES IN SCHEMA analytics;
39SHOW TABLES LIKE 'customer%' IN DATABASE prod_db;
40SHOW COLUMNS IN TABLE analytics.customers;

1-- Numeric types
2CREATE TABLE type_examples (
3-- NUMBER(precision, scale) — alias: DECIMAL, NUMERIC
4price           NUMBER(10, 2),       -- up to 9,999,999.99
5quantity        NUMBER(8, 0),        -- integer stored as NUMBER
6rate            FLOAT,               -- 64-bit double precision
7
8-- String types — all aliases for VARCHAR internally
9name            VARCHAR(255),        -- explicit length
10description     STRING,              -- alias, max 16MB
11notes           TEXT,                -- alias, max 16MB
12
13-- Boolean
14is_active       BOOLEAN,             -- TRUE / FALSE / NULL
15
16-- Date and time
17birth_date      DATE,                -- YYYY-MM-DD
18login_time      TIME,                -- HH:MI:SS.FFFFFFFFF
19created_utc     TIMESTAMP_NTZ,       -- no timezone stored
20event_local     TIMESTAMP_LTZ,       -- stores in UTC, displays in session TZ
21recorded_at     TIMESTAMP_TZ,        -- stores offset with value
22
23-- Semi-structured
24raw_json        VARIANT,             -- any JSON, XML, Avro, Parquet (max 16MB)
25tags            ARRAY,               -- ordered list of VARIANT values
26attributes      OBJECT               -- key-value pairs
27);

1-- Insert JSON into a VARIANT column
2INSERT INTO staging.raw_events (payload)
3SELECT PARSE_JSON('{
4"user_id": 12345,
5"event": "purchase",
6"items": [
7  {"sku": "A001", "qty": 2, "price": 29.99},
8  {"sku": "B002", "qty": 1, "price": 149.00}
9],
10"metadata": {
11  "session_id": "abc-123",
12  "ip_address": "192.168.1.1"
13}
14}');
15
16-- Path access using colon (:) notation
17SELECT
18payload:user_id                     AS user_id,       -- returns VARIANT
19payload:event                       AS event_type,
20payload:metadata:session_id         AS session_id,    -- nested path
21payload:items[0]:sku                AS first_sku      -- array index
22FROM staging.raw_events;
23
24-- Type casting with double-colon (::) — explicit CAST
25SELECT
26payload:user_id::NUMBER             AS user_id_num,   -- VARIANT -> NUMBER
27payload:event::VARCHAR              AS event_str,     -- VARIANT -> STRING
28payload:metadata:session_id::STRING AS session_str
29FROM staging.raw_events;
30
31-- Flatten arrays — LATERAL FLATTEN expands array into rows
32SELECT
33f.value:sku::VARCHAR    AS sku,
34f.value:qty::NUMBER     AS quantity,
35f.value:price::FLOAT    AS price
36FROM staging.raw_events,
37   LATERAL FLATTEN(INPUT => payload:items) f;
38
39-- Check if a path exists
40SELECT
41payload:user_id IS NOT NULL AS has_user_id
42FROM staging.raw_events;

1-- INFORMATION_SCHEMA: tables in current database
2SELECT
3table_name,
4table_type,
5row_count,
6bytes,
7retention_time
8FROM information_schema.tables
9WHERE table_schema = 'ANALYTICS'
10ORDER BY bytes DESC;
11
12-- INFORMATION_SCHEMA: column metadata
13SELECT
14table_name,
15column_name,
16data_type,
17character_maximum_length,
18is_nullable
19FROM information_schema.columns
20WHERE table_schema = 'ANALYTICS'
21AND table_name = 'CUSTOMERS';
22
23-- INFORMATION_SCHEMA: load history (last 14 days)
24SELECT
25table_name,
26last_load_time,
27row_count,
28error_count
29FROM information_schema.load_history
30WHERE schema_name = 'ANALYTICS'
31ORDER BY last_load_time DESC;
32
33-- ACCOUNT_USAGE: query history across entire account (ACCOUNTADMIN required)
34SELECT
35query_id,
36user_name,
37warehouse_name,
38execution_time / 1000 AS execution_seconds,
39bytes_scanned,
40query_text
41FROM snowflake.account_usage.query_history
42WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
43AND execution_status = 'SUCCESS'
44ORDER BY execution_time DESC
45LIMIT 100;
46
47-- ACCOUNT_USAGE: storage usage over time
48SELECT
49usage_date,
50database_name,
51average_database_bytes / POWER(1024, 3) AS avg_gb
52FROM snowflake.account_usage.database_storage_usage_history
53WHERE usage_date >= DATEADD('month', -1, CURRENT_DATE())
54ORDER BY usage_date DESC;

1-- Show all databases in the account
2SHOW DATABASES;
3
4-- Show databases matching a pattern
5SHOW DATABASES LIKE 'prod%';
6
7-- Show schemas in the current database
8SHOW SCHEMAS;
9
10-- Show schemas in a specific database
11SHOW SCHEMAS IN DATABASE prod_db;
12
13-- Show all tables in the current schema
14SHOW TABLES;
15
16-- Show tables in a specific schema
17SHOW TABLES IN SCHEMA prod_db.analytics;
18
19-- Show tables matching a pattern
20SHOW TABLES LIKE 'customer%';
21
22-- Show column definitions for a table
23SHOW COLUMNS IN TABLE analytics.customers;
24
25-- Show views in the current schema
26SHOW VIEWS;
27
28-- Show stages accessible to current role
29SHOW STAGES;
30
31-- Show file formats
32SHOW FILE FORMATS;
33
34-- Access SHOW results via result_scan
35SHOW TABLES IN SCHEMA analytics;
36SELECT "name", "kind", "rows", "bytes"
37FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

1-- Constraints can be defined inline
2CREATE TABLE analytics.orders (
3order_id        NUMBER          NOT NULL,         -- informational only
4customer_id     NUMBER          NOT NULL,
5order_date      DATE            NOT NULL,
6total_amount    NUMBER(12, 2),
7status          VARCHAR(20),
8
9-- Primary key — informational, not enforced
10CONSTRAINT pk_orders PRIMARY KEY (order_id),
11
12-- Foreign key — informational, not enforced
13CONSTRAINT fk_orders_customer
14  FOREIGN KEY (customer_id)
15  REFERENCES analytics.customers (customer_id)
16);
17
18-- Add constraint to existing table
19ALTER TABLE analytics.orders
20ADD CONSTRAINT uq_order_customer UNIQUE (order_id, customer_id);
21
22-- Show constraints on a table
23SHOW PRIMARY KEYS IN TABLE analytics.orders;
24SHOW IMPORTED KEYS IN TABLE analytics.orders;
25SHOW UNIQUE KEYS IN TABLE analytics.orders;
26
27-- IMPORTANT: This INSERT succeeds even though it violates NOT NULL
28-- Snowflake does NOT enforce constraints
29INSERT INTO analytics.orders (order_id, customer_id, order_date)
30VALUES (NULL, NULL, NULL);  -- Will succeed! Constraints not enforced.