Snowflake Account Setup for Data Engineers: Databases, Schemas, Virtual Warehouses, Roles, RBAC, Users, Resource Monitors, and Building a Production-Ready Environment

Leave a Comment / Data Engineering, Snowflake

Table of Contents

In the previous post, we covered Snowflake’s three-layer architecture, virtual warehouses, micro-partitions, and pricing. Now it is time to get hands-on. This post walks through everything you need to set up a production-ready Snowflake environment: account structure, databases, schemas, warehouse configuration, role-based access control (RBAC), users, and resource monitors.

Analogy — Setting up an office building. Creating a Snowflake account is like leasing an office building. The building (account) gives you floors (databases), rooms on each floor (schemas), and desks in each room (tables). The warehouse is the electricity supply — you pick how much power each department gets, and the meter runs only when someone is actually working. RBAC is the badge access system — you create access levels (roles), assign badges (roles to users), and each badge opens only the doors that person needs. The resource monitor is the electricity budget — it alerts you when a department is using too much power and can shut off the supply if needed.

Snowflake Account Structure

When you sign up for Snowflake, you get an account — the top-level container for everything. Your account has a unique identifier that looks like abc12345.us-east-1.aws (organization-account.region.cloud).

Snowflake hierarchy:

  Account (abc12345.us-east-1.aws)
    |
    ├── Databases
    |     ├── RAW_DB (bronze layer)
    |     |     ├── Schema: PUBLIC
    |     |     ├── Schema: VENDOR_A
    |     |     └── Schema: VENDOR_B
    |     |
    |     ├── ANALYTICS_DB (silver/gold layer)
    |     |     ├── Schema: STAGING
    |     |     ├── Schema: CORE
    |     |     └── Schema: MARTS
    |     |
    |     └── SNOWFLAKE (system database -- usage, billing, query history)
    |
    ├── Warehouses
    |     ├── WH_ETL (Medium, auto-suspend: 5 min)
    |     ├── WH_ANALYTICS (Small, auto-suspend: 2 min)
    |     └── WH_DEV (X-Small, auto-suspend: 1 min)
    |
    ├── Roles
    |     ├── ACCOUNTADMIN (top-level)
    |     ├── SYSADMIN (creates databases, warehouses)
    |     ├── SECURITYADMIN (manages roles, users, grants)
    |     └── Custom roles (DATA_ENGINEER, DATA_ANALYST, etc.)
    |
    └── Users
          ├── naveen (DATA_ENGINEER role)
          ├── sarah (DATA_ANALYST role)
          └── svc_dbt (service account, TRANSFORMER role)

Databases and Schemas

A database is a top-level container for schemas, tables, views, and other objects. A schema is a logical grouping within a database. Together, they form Snowflake’s three-level naming convention: DATABASE.SCHEMA.OBJECT.

Analogy — A filing system. The database is a filing cabinet. Each schema is a drawer in that cabinet. Each table is a folder in the drawer. When you ask for a specific document, you say “Cabinet RAW_DB, Drawer VENDOR_A, Folder ORDERS” — which translates to RAW_DB.VENDOR_A.ORDERS.

Creating Databases

-- Create databases for a medallion architecture
CREATE DATABASE RAW_DB
  COMMENT = 'Bronze layer: raw data from source systems';

CREATE DATABASE ANALYTICS_DB
  COMMENT = 'Silver and gold layers: cleaned, transformed, aggregated data';

CREATE DATABASE DEV_DB
  COMMENT = 'Development sandbox for testing';

-- List all databases
SHOW DATABASES;

-- Switch to a database
USE DATABASE RAW_DB;

Creating Schemas

-- Create schemas within RAW_DB
USE DATABASE RAW_DB;

CREATE SCHEMA VENDOR_A
  COMMENT = 'Raw data from Vendor A (daily CSV uploads)';

CREATE SCHEMA VENDOR_B
  COMMENT = 'Raw data from Vendor B (API ingestion)';

CREATE SCHEMA INTERNAL_SYSTEMS
  COMMENT = 'Raw data from internal databases (ERP, CRM)';

-- Create schemas within ANALYTICS_DB
USE DATABASE ANALYTICS_DB;

CREATE SCHEMA STAGING
  COMMENT = 'Intermediate transformations (silver layer)';

CREATE SCHEMA CORE
  COMMENT = 'Core business entities (silver/gold)';

CREATE SCHEMA MARTS
  COMMENT = 'Business-specific aggregations (gold layer)';

CREATE SCHEMA METADATA
  COMMENT = 'Pipeline configs, audit logs, data quality results';

-- List schemas in current database
SHOW SCHEMAS;

The PUBLIC Schema and Default Behavior

Every database comes with a PUBLIC schema by default. Best practice is to avoid using PUBLIC for production data — create explicit schemas instead. PUBLIC is fine for temporary work, shared utilities, or UDFs.

-- Fully qualified reference (always unambiguous)
SELECT * FROM RAW_DB.VENDOR_A.ORDERS;

-- Using context (database + schema set)
USE DATABASE RAW_DB;
USE SCHEMA VENDOR_A;
SELECT * FROM ORDERS;  -- Snowflake knows the full path

-- Set both in one command
USE RAW_DB.VENDOR_A;

Database Options Worth Knowing

-- Create a transient database (no Fail-safe, cheaper storage)
-- Good for staging/temporary data that does not need 7-day recovery
CREATE TRANSIENT DATABASE STAGING_DB
  DATA_RETENTION_TIME_IN_DAYS = 1
  COMMENT = 'Transient staging -- no fail-safe, 1-day time travel';

-- Clone a database instantly (zero-copy)
CREATE DATABASE DEV_DB CLONE ANALYTICS_DB;
-- DEV_DB is an instant copy -- no data duplicated
-- Costs $0 in storage until data diverges

-- Drop a database (with safety net)
DROP DATABASE DEV_DB;
-- Can be recovered with: UNDROP DATABASE DEV_DB; (within Time Travel window)

Creating and Configuring Virtual Warehouses

A virtual warehouse is a named compute cluster. You define its size, auto-suspend behavior, and scaling policy. Snowflake handles provisioning and deprovisioning the actual compute nodes.

Analogy — Hiring a catering team. Each warehouse is a catering team you hire for an event. You pick the team size (X-Small = 1 cook, Large = 8 cooks). The team arrives the moment an order (query) comes in (auto-resume), and goes home after the kitchen is quiet for a few minutes (auto-suspend). You only pay for hours worked. You can hire multiple teams for different events (ETL team, analytics team, BI team) and they never interfere with each other.

Creating Warehouses

-- ETL warehouse: Medium size, suspends after 5 minutes
CREATE WAREHOUSE WH_ETL
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 300          -- seconds (5 minutes)
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 1
  INITIALLY_SUSPENDED = TRUE  -- do not start until first query
  COMMENT = 'ETL and data loading workloads';

-- Analytics warehouse: Small, fast suspend for ad-hoc queries
CREATE WAREHOUSE WH_ANALYTICS
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND = 120          -- 2 minutes
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Analyst ad-hoc queries';

-- BI warehouse: X-Small with multi-cluster for concurrency (Enterprise edition)
CREATE WAREHOUSE WH_BI
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3       -- scales out to 3 clusters under load
  SCALING_POLICY = 'STANDARD' -- adds clusters when queries queue
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Dashboard and BI queries -- multi-cluster for concurrency';

-- Development warehouse: smallest possible, fast suspend
CREATE WAREHOUSE WH_DEV
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60           -- 1 minute
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Development and testing';

Managing Warehouses

-- Resize a warehouse (takes effect on the next query)
ALTER WAREHOUSE WH_ETL SET WAREHOUSE_SIZE = 'LARGE';

-- Suspend a warehouse immediately
ALTER WAREHOUSE WH_ETL SUSPEND;

-- Resume a warehouse
ALTER WAREHOUSE WH_ETL RESUME;

-- Change auto-suspend timeout
ALTER WAREHOUSE WH_ANALYTICS SET AUTO_SUSPEND = 180;

-- Check warehouse status
SHOW WAREHOUSES;

-- See warehouse usage over time
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE WAREHOUSE_NAME = 'WH_ETL'
  AND START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY START_TIME;

Warehouse Sizing Guidelines

Choosing the right warehouse size:

  X-Small (1 credit/hr):
    - Development and testing
    - Simple SELECT queries
    - Small data loads (< 100 MB)

  Small (2 credits/hr):
    - Ad-hoc analyst queries
    - Small-to-medium transformations
    - Light reporting workloads

  Medium (4 credits/hr):
    - Standard ETL pipelines
    - Medium data volumes (1-50 GB per load)
    - Complex multi-table joins

  Large (8 credits/hr):
    - Heavy transformations (dbt runs)
    - Large data volumes (50+ GB per load)
    - Complex analytics queries

  X-Large+ (16+ credits/hr):
    - Very large data processing
    - Initial bulk loads (hundreds of GB)
    - Data science / ML workloads

  Rule of thumb:
    Start small. Test. Scale up only if queries are slow.
    Scaling up from Small to Medium costs 2x, not a little more.
    A query that runs in 60s on Small might run in 30s on Medium --
    but costs the same in credits (same total compute used).
    Scale up when queries are complex, not just large.

The Role Hierarchy — System-Defined Roles

Snowflake has five built-in system roles that form a hierarchy. Understanding this hierarchy is essential for security.

Analogy — A corporate org chart. ACCOUNTADMIN is the CEO — full power, used rarely, only for critical decisions. SYSADMIN is the CTO — creates infrastructure (databases, warehouses) and manages day-to-day operations. SECURITYADMIN is the CISO — manages who has access to what. USERADMIN is HR — creates and manages user accounts. PUBLIC is the lobby — everyone has access by default.

Role hierarchy (each role inherits the privileges of roles below it):

  ACCOUNTADMIN  (top-level -- use sparingly, always with MFA)
    |
    ├── SYSADMIN  (creates and manages databases, warehouses, schemas)
    |     |
    |     └── (all custom roles should be granted to SYSADMIN)
    |
    ├── SECURITYADMIN  (manages roles, users, grants)
    |     |
    |     └── USERADMIN  (creates and manages users)
    |
    └── PUBLIC  (default role for all users -- minimal privileges)

Rules:
  1. ACCOUNTADMIN should be assigned to 2-3 people maximum (with MFA)
  2. Day-to-day work should NEVER use ACCOUNTADMIN
  3. SYSADMIN should own all databases and warehouses
  4. SECURITYADMIN manages all grants and custom roles
  5. Custom roles should always be granted to SYSADMIN (so SYSADMIN can manage their objects)
-- See all roles in the account
SHOW ROLES;

-- See which roles are granted to a user
SHOW GRANTS TO USER naveen;

-- See which privileges a role has
SHOW GRANTS TO ROLE SYSADMIN;

-- See what a role owns
SHOW GRANTS OF ROLE DATA_ENGINEER;

Building Custom Roles — RBAC in Practice

Production environments need custom roles tailored to your team structure. The best practice is to create functional roles (what people do) that inherit from access roles (what objects they can access).

Analogy — A theme park wristband system. Access roles are the individual ride tickets (this ticket works on the roller coaster, this ticket works on the water ride). Functional roles are the wristband packages (the “Thrill Seeker” wristband includes 5 specific ride tickets; the “Family Fun” wristband includes 3 different ride tickets). You give each visitor (user) a wristband (functional role), and the wristband determines which rides (objects) they can access.

-- Step 1: Create functional roles
USE ROLE SECURITYADMIN;

CREATE ROLE DATA_ENGINEER
  COMMENT = 'Full read/write access to all data layers';

CREATE ROLE DATA_ANALYST
  COMMENT = 'Read-only access to silver and gold layers';

CREATE ROLE DATA_SCIENTIST
  COMMENT = 'Read access to all layers + write to sandbox';

CREATE ROLE TRANSFORMER
  COMMENT = 'Service account role for dbt transformations';

CREATE ROLE DATA_LOADER
  COMMENT = 'Service account role for data ingestion';

-- Step 2: Build the role hierarchy (grant custom roles to SYSADMIN)
GRANT ROLE DATA_ENGINEER TO ROLE SYSADMIN;
GRANT ROLE DATA_ANALYST TO ROLE SYSADMIN;
GRANT ROLE DATA_SCIENTIST TO ROLE SYSADMIN;
GRANT ROLE TRANSFORMER TO ROLE SYSADMIN;
GRANT ROLE DATA_LOADER TO ROLE SYSADMIN;

-- Step 3: Grant analyst role as a subset of engineer
-- (engineers can do everything analysts can do, plus more)
GRANT ROLE DATA_ANALYST TO ROLE DATA_ENGINEER;
Custom role hierarchy:

  SYSADMIN
    |
    ├── DATA_ENGINEER  (read/write all layers)
    |     |
    |     └── DATA_ANALYST  (read-only silver/gold)
    |
    ├── DATA_SCIENTIST  (read all + sandbox write)
    |
    ├── TRANSFORMER  (dbt service account)
    |
    └── DATA_LOADER  (ingestion service account)

Granting Privileges — The Permission Model

Snowflake privileges work at three levels: account-level (CREATE DATABASE), object-level (SELECT on a table), and schema-level (USAGE on a schema). The key rule: a user needs USAGE on the database AND USAGE on the schema AND the specific privilege on the object.

-- Grant warehouse usage to roles
USE ROLE SECURITYADMIN;

-- Engineers get the ETL warehouse
GRANT USAGE ON WAREHOUSE WH_ETL TO ROLE DATA_ENGINEER;
GRANT OPERATE ON WAREHOUSE WH_ETL TO ROLE DATA_ENGINEER;
GRANT MONITOR ON WAREHOUSE WH_ETL TO ROLE DATA_ENGINEER;

-- Analysts get the analytics warehouse
GRANT USAGE ON WAREHOUSE WH_ANALYTICS TO ROLE DATA_ANALYST;

-- BI reports get the BI warehouse
GRANT USAGE ON WAREHOUSE WH_BI TO ROLE DATA_ANALYST;

-- Everyone gets the dev warehouse
GRANT USAGE ON WAREHOUSE WH_DEV TO ROLE DATA_ENGINEER;
GRANT USAGE ON WAREHOUSE WH_DEV TO ROLE DATA_ANALYST;
GRANT USAGE ON WAREHOUSE WH_DEV TO ROLE DATA_SCIENTIST;
-- Grant database and schema access
-- Engineers: full access to RAW_DB and ANALYTICS_DB
GRANT USAGE ON DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE RAW_DB TO ROLE DATA_ENGINEER;

GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;

-- Analysts: read-only on ANALYTICS_DB (silver/gold only)
GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ANALYST;
GRANT USAGE ON SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
GRANT USAGE ON SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST;

Future Grants — The Key to Maintainable Security

Future grants automatically apply privileges to objects that are created later. Without future grants, every new table requires a manual GRANT statement.

-- Without future grants (manual, error-prone):
CREATE TABLE ANALYTICS_DB.CORE.ORDERS (...);
GRANT SELECT ON TABLE ANALYTICS_DB.CORE.ORDERS TO ROLE DATA_ANALYST;
-- Repeat for EVERY new table -- easy to forget

-- With future grants (automatic, set once):
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
-- Every new table in CORE automatically gets SELECT for DATA_ANALYST
-- No manual grants needed -- ever

Users — Creating and Managing Access

-- Create a human user
USE ROLE USERADMIN;

CREATE USER naveen
  PASSWORD = 'TemporaryP@ss123'
  DEFAULT_ROLE = DATA_ENGINEER
  DEFAULT_WAREHOUSE = WH_ETL
  DEFAULT_NAMESPACE = RAW_DB.PUBLIC
  MUST_CHANGE_PASSWORD = TRUE
  COMMENT = 'Data Engineer - Naveen';

-- Grant role to user
USE ROLE SECURITYADMIN;
GRANT ROLE DATA_ENGINEER TO USER naveen;

-- Create a service account (for dbt, Airflow, etc.)
USE ROLE USERADMIN;

CREATE USER svc_dbt
  PASSWORD = 'ServiceP@ss456'
  DEFAULT_ROLE = TRANSFORMER
  DEFAULT_WAREHOUSE = WH_ETL
  COMMENT = 'dbt service account';

USE ROLE SECURITYADMIN;
GRANT ROLE TRANSFORMER TO USER svc_dbt;

-- List all users
SHOW USERS;

-- See a user's grants
SHOW GRANTS TO USER naveen;

Switching Roles

-- Switch your active role (within a session)
USE ROLE DATA_ANALYST;
-- Now you can only do what DATA_ANALYST allows

USE ROLE DATA_ENGINEER;
-- Now you have DATA_ENGINEER privileges (which includes DATA_ANALYST)

USE ROLE ACCOUNTADMIN;
-- Full access -- use only when necessary

-- Check your current role
SELECT CURRENT_ROLE();

Resource Monitors — Controlling Costs

Resource monitors track credit usage and can trigger alerts or suspend warehouses when limits are reached. They are your safety net against runaway costs.

Analogy — A prepaid electricity meter. The resource monitor is a prepaid meter on your electricity (compute). You load $1,000 of credits (set a quota). At 75% usage, the meter flashes a warning (email notification). At 100%, the meter can either warn you again or cut the power (suspend the warehouse). You set the rules — Snowflake enforces them.

-- Create a resource monitor for the entire account
USE ROLE ACCOUNTADMIN;  -- Only ACCOUNTADMIN can create resource monitors

CREATE RESOURCE MONITOR ACCOUNT_MONITOR
  WITH CREDIT_QUOTA = 1000           -- 1000 credits per month
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY           -- Email alert at 75%
    ON 90 PERCENT DO NOTIFY           -- Email alert at 90%
    ON 100 PERCENT DO SUSPEND         -- Suspend warehouses at 100%
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;  -- Force suspend at 110%

-- Apply the monitor to the account
ALTER ACCOUNT SET RESOURCE_MONITOR = ACCOUNT_MONITOR;

-- Create a warehouse-specific monitor
CREATE RESOURCE MONITOR ETL_MONITOR
  WITH CREDIT_QUOTA = 200
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 80 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

-- Apply to a specific warehouse
ALTER WAREHOUSE WH_ETL SET RESOURCE_MONITOR = ETL_MONITOR;

-- View resource monitor status
SHOW RESOURCE MONITORS;

A Complete Production Setup — Putting It All Together

Here is a complete script that sets up a production-ready Snowflake environment from scratch:

-- ============================================================
-- COMPLETE SNOWFLAKE PRODUCTION SETUP
-- Run this once with ACCOUNTADMIN, then switch to custom roles
-- ============================================================

-- Step 1: Create the role hierarchy
USE ROLE SECURITYADMIN;

CREATE ROLE DATA_ENGINEER;
CREATE ROLE DATA_ANALYST;
CREATE ROLE TRANSFORMER;
CREATE ROLE DATA_LOADER;

GRANT ROLE DATA_ANALYST TO ROLE DATA_ENGINEER;
GRANT ROLE DATA_ENGINEER TO ROLE SYSADMIN;
GRANT ROLE TRANSFORMER TO ROLE SYSADMIN;
GRANT ROLE DATA_LOADER TO ROLE SYSADMIN;

-- Step 2: Create databases and schemas
USE ROLE SYSADMIN;

CREATE DATABASE RAW_DB COMMENT = 'Bronze layer';
CREATE DATABASE ANALYTICS_DB COMMENT = 'Silver and gold layers';
CREATE DATABASE DEV_DB COMMENT = 'Development sandbox';

CREATE SCHEMA RAW_DB.VENDOR_A;
CREATE SCHEMA RAW_DB.VENDOR_B;
CREATE SCHEMA ANALYTICS_DB.STAGING;
CREATE SCHEMA ANALYTICS_DB.CORE;
CREATE SCHEMA ANALYTICS_DB.MARTS;

-- Step 3: Create warehouses
CREATE WAREHOUSE WH_ETL
  WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE WH_ANALYTICS
  WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 120 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE WH_DEV
  WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Step 4: Grant warehouse access
USE ROLE SECURITYADMIN;

GRANT USAGE, OPERATE ON WAREHOUSE WH_ETL TO ROLE DATA_ENGINEER;
GRANT USAGE, OPERATE ON WAREHOUSE WH_ETL TO ROLE TRANSFORMER;
GRANT USAGE, OPERATE ON WAREHOUSE WH_ETL TO ROLE DATA_LOADER;
GRANT USAGE ON WAREHOUSE WH_ANALYTICS TO ROLE DATA_ANALYST;
GRANT USAGE ON WAREHOUSE WH_DEV TO ROLE DATA_ENGINEER;
GRANT USAGE ON WAREHOUSE WH_DEV TO ROLE DATA_ANALYST;

-- Step 5: Grant database and schema access
-- Engineers: full access everywhere
GRANT USAGE ON DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON DATABASE DEV_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE RAW_DB TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER;

-- Analysts: read-only on silver/gold
GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ANALYST;
GRANT USAGE ON SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
GRANT USAGE ON SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.CORE TO ROLE DATA_ANALYST;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST;

-- Step 6: Create resource monitor
USE ROLE ACCOUNTADMIN;

CREATE RESOURCE MONITOR MONTHLY_MONITOR
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;

ALTER ACCOUNT SET RESOURCE_MONITOR = MONTHLY_MONITOR;

Snowflake UI — Snowsight Walkthrough

Snowsight is Snowflake’s modern web UI (replaced the classic console in 2023). Here is where to find key features:

Left sidebar navigation:

  Worksheets    -- SQL editor (like SSMS or DBeaver)
                   Create worksheets, run queries, see results
                   Multiple tabs, auto-complete, syntax highlighting

  Dashboards    -- Build simple charts and dashboards from query results

  Data          -- Browse databases, schemas, tables, views
                   Preview data, see column types and statistics
                   Create databases and schemas visually

  Marketplace   -- Browse third-party data sets for free or purchase

  Activity      -- Query History: every query ever run (who, when, how long)
                   Copy History: track COPY INTO operations
                   Task History: monitor scheduled tasks

  Admin         -- Warehouses: create, resize, suspend, resume
                   Resource Monitors: set up cost controls
                   Users & Roles: manage access (if SECURITYADMIN)
                   Cost Management: view credit usage over time

Top-right:
  Role switcher -- quickly switch between roles
  Warehouse selector -- choose which warehouse runs your queries
  Account info -- region, edition, account locator
Useful keyboard shortcuts in Snowsight:

  Cmd/Ctrl + Enter    -- Run current statement (or selected text)
  Cmd/Ctrl + Shift + Enter -- Run all statements in worksheet
  Cmd/Ctrl + /        -- Comment/uncomment lines
  Tab                 -- Auto-complete table/column names

Common Mistakes

  1. Using ACCOUNTADMIN for daily work. ACCOUNTADMIN has unlimited power — one accidental DROP DATABASE or misconfigured grant can cause serious damage. Create custom roles for daily work and use ACCOUNTADMIN only for account-level administration like resource monitors. Enable MFA on ACCOUNTADMIN.

  2. Not granting custom roles to SYSADMIN. If a custom role (DATA_ENGINEER) creates a database but is not granted to SYSADMIN, then SYSADMIN cannot manage that database. Always grant custom roles to SYSADMIN so the admin role can manage all objects.

  3. Forgetting future grants. Without GRANT SELECT ON FUTURE TABLES, every new table requires a manual grant. Analysts will report “I can not see the new table” after every deployment. Set up future grants once per schema and never think about it again.

  4. Leaving auto-suspend at the default (10 minutes). The default is 600 seconds (10 minutes). For development warehouses, 60 seconds is better. For analytics, 120-180 seconds. Every extra minute of idle time costs credits. A Medium warehouse idle for an extra 5 minutes costs about $0.33 per occurrence — which adds up across dozens of daily sessions.

  5. Creating one giant warehouse for everything. When ETL, analytics, and BI share one warehouse, heavy ETL jobs slow down dashboard queries. Create dedicated warehouses per workload type. The per-second billing means idle warehouses cost nothing.

  6. Not setting up resource monitors. Without resource monitors, a runaway query, an infinite loop in a stored procedure, or a misconfigured auto-suspend setting can burn thousands of credits overnight. Always set up at least one account-level resource monitor with a SUSPEND trigger.

  7. Granting privileges directly to users instead of roles. GRANT SELECT ON TABLE orders TO USER john — if John changes teams, you must manually revoke. GRANT SELECT TO ROLE DATA_ANALYST, then GRANT ROLE DATA_ANALYST TO USER john — when John moves teams, you just change his role. Always grant to roles, never directly to users.

  8. Using the PUBLIC schema for production data. Every user has access to PUBLIC by default. Production data in PUBLIC is visible to everyone. Create dedicated schemas (STAGING, CORE, MARTS) with explicit grants.

Interview Questions

Q: What is the object hierarchy in Snowflake? A: Snowflake uses a three-level naming convention: Account, Database, Schema, Object. A table is referenced as DATABASE.SCHEMA.TABLE (e.g., RAW_DB.VENDOR_A.ORDERS). The account is the top-level container. Databases contain schemas. Schemas contain tables, views, stages, streams, tasks, and other objects. This hierarchy enables clean separation of environments (dev/staging/prod) and data layers (bronze/silver/gold).

Q: How do virtual warehouses handle concurrency? A: In Standard edition, a single warehouse processes queries sequentially — if 10 queries arrive, they queue. In Enterprise edition, multi-cluster warehouses can scale out by adding clusters automatically when queries queue. You configure MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. Snowflake adds clusters when load increases and removes them when demand drops. Alternatively, create separate warehouses per team or workload to avoid contention entirely.

Q: Explain the system-defined role hierarchy in Snowflake. A: Snowflake has five system roles in a hierarchy. ACCOUNTADMIN is the top-level super-admin with full control — it should be restricted to 2-3 users with MFA. SYSADMIN creates and manages databases, schemas, and warehouses. SECURITYADMIN manages roles, users, and privilege grants. USERADMIN creates and manages user accounts. PUBLIC is granted to every user by default with minimal privileges. Custom roles should always be granted to SYSADMIN so the admin can manage objects created by those roles.

Q: What are future grants and why are they important? A: Future grants automatically apply privileges to objects created in the future within a specified scope (database or schema). Without future grants, every new table requires a manual GRANT statement, which is error-prone and delays access for users. With GRANT SELECT ON FUTURE TABLES IN SCHEMA core TO ROLE data_analyst, every new table in the core schema automatically gets SELECT access for analysts. Future grants are essential for maintainable, scalable security.

Q: What is a resource monitor and when would you use one? A: A resource monitor tracks credit consumption for the account or specific warehouses and can trigger notifications or suspend warehouses at defined thresholds. You set a credit quota (e.g., 500 credits per month) and trigger actions at percentages (75% = email alert, 100% = suspend warehouses). Resource monitors are the primary mechanism for preventing runaway costs. Every production Snowflake account should have at least one account-level monitor.

Q: How would you design the database and schema structure for a data warehouse project? A: Use separate databases for each data layer: RAW_DB for bronze (raw ingested data), ANALYTICS_DB for silver and gold (transformed data), and DEV_DB for development sandboxes (zero-copy clones of production). Within each database, create schemas by source system (RAW_DB.VENDOR_A, RAW_DB.VENDOR_B) or by business domain (ANALYTICS_DB.CORE, ANALYTICS_DB.MARTS). This structure enables granular access control — analysts can have read-only on ANALYTICS_DB.MARTS without seeing raw data in RAW_DB.

Q: What is the difference between scaling up and scaling out a warehouse? A: Scaling up means increasing the warehouse size (e.g., Small to Large) — this makes individual queries faster by providing more compute nodes per query, but doubles the credit cost. Scaling out means adding more clusters to a multi-cluster warehouse (Enterprise edition) — this handles more concurrent queries without slowing them down. Scale up when single queries are slow. Scale out when many users are queuing for the same warehouse. In practice, start with separate warehouses per workload type before scaling either direction.

Wrapping Up

A well-structured Snowflake environment starts with clear separation: databases by data layer, schemas by source or domain, warehouses by workload type, and roles by job function. Future grants keep security maintainable. Resource monitors keep costs predictable. And the discipline of never using ACCOUNTADMIN for daily work keeps the environment safe.

In the next post, we will get into loading data: stages, file formats, COPY INTO, Snowpipe for continuous ingestion, and the patterns that move data from external sources into your Snowflake warehouse.

Related posts: – Snowflake Overview & Architecture – Azure RBAC Roles Demystified – Databricks Unity Catalog – Fabric Security & Governance

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top