Databases, Schemas, Tables & Data Types: A Beginner's Guide

When you start working with Snowflake, the first thing you need to understand is how data is organized. Unlike traditional databases where you might just have a flat list of tables, Snowflake uses a hierarchical structure: Account → Database → Schema → Table. This structure helps you manage large amounts of data, enforce security, and organize your work logically.

Imagine you're building a data warehouse for an e-commerce company. You'll have different teams (marketing, sales, inventory) that need their own space. Snowflake's hierarchy allows you to create separate databases for each department, schemas for different projects, and tables for specific datasets. This not only keeps things tidy but also makes it easy to grant permissions at the right level.

In this tutorial, you'll learn how to create databases, schemas, and tables in Snowflake. We'll cover the essential data types you'll use daily, from simple strings and numbers to complex semi-structured data like JSON. You'll also see production-ready examples and common pitfalls to avoid. By the end, you'll be able to set up your Snowflake environment confidently and avoid the mistakes that can lead to costly production incidents.

Understanding Snowflake's Object Hierarchy

Snowflake organizes data in a multi-level hierarchy. At the top is your Snowflake account. Under the account, you can create multiple databases. Each database contains schemas, and each schema contains objects like tables, views, functions, and stored procedures. This structure is similar to a file system: account is the root, databases are folders, schemas are subfolders, and tables are files.

When you first log into Snowflake, you are placed in a default database and schema (usually PUBLIC). It's a common mistake to start creating tables there without realizing that PUBLIC is shared with all users who have access to the database. Always create your own schema for production work.

To see your current context, use:

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

To change context, use:

USE DATABASE mydb; USE SCHEMA mydb.myschema;

Or you can set both at once:

USE SCHEMA mydb.myschema;

Now, let's create our first database and schema.

-- Create a database for e-commerce analytics
CREATE DATABASE IF NOT EXISTS ecommerce_analytics;

-- Create a schema for sales data
CREATE SCHEMA IF NOT EXISTS ecommerce_analytics.sales;

-- Set context to the new schema
USE SCHEMA ecommerce_analytics.sales;

-- Verify current context
SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

Creating Tables in Snowflake

Tables are the core storage objects in Snowflake. They consist of rows and columns, where each column has a name and a data type. Snowflake supports both permanent and transient tables. Permanent tables have a fail-safe period for data recovery, while transient tables do not (but are cheaper). For most production use cases, you'll use permanent tables.

To create a table, you use the CREATE TABLE statement. You must specify the table name, column names, and their data types. You can also define constraints like NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY. However, Snowflake does not enforce constraints except NOT NULL and UNIQUE (if you create a unique constraint, it creates a unique index). Primary keys and foreign keys are metadata only; they are not enforced.

Here's an example of creating a sales orders table:

CREATE TABLE orders ( order_id INTEGER AUTOINCREMENT, customer_id INTEGER NOT NULL, order_date DATE NOT NULL, total_amount DECIMAL(10,2), status VARCHAR(20) DEFAULT 'PENDING', PRIMARY KEY (order_id) );

Notice the AUTOINCREMENT property for order_id – it automatically generates a unique number for each row. The DEFAULT clause sets a default value for status. The PRIMARY KEY is informational; Snowflake will not prevent duplicate order_id values unless you also create a unique constraint.

Let's create a more comprehensive table for our e-commerce schema.

-- Create customers table
CREATE TABLE customers (
    customer_id INTEGER AUTOINCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    signup_date DATE,
    PRIMARY KEY (customer_id)
);

-- Create orders table
CREATE TABLE orders (
    order_id INTEGER AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'PENDING',
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create order_items table
CREATE TABLE order_items (
    order_item_id INTEGER AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Show tables in current schema
SHOW TABLES;

Snowflake Data Types: The Essentials

Snowflake supports a wide range of data types, categorized into: - Numeric: INTEGER, DECIMAL, FLOAT, etc. - String: VARCHAR, CHAR, TEXT, etc. - Date/Time: DATE, TIME, TIMESTAMP, etc. - Semi-structured: VARIANT, OBJECT, ARRAY (for JSON, Avro, Parquet, etc.) - Others: BOOLEAN, GEOGRAPHY, etc.

Choosing the right data type is crucial for performance and storage. For example, use DATE instead of TIMESTAMP if you don't need time, as it uses less space. Use VARCHAR instead of TEXT for shorter strings.

Let's see how to use these types in a table.

-- Create a table with various data types
CREATE TABLE product_reviews (
    review_id INTEGER AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    reviewer_name VARCHAR(100),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    review_text VARCHAR(5000),
    review_date DATE DEFAULT CURRENT_DATE,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    metadata VARIANT
);

-- Insert a sample row with JSON in VARIANT
INSERT INTO product_reviews (product_id, reviewer_name, rating, review_text, metadata)
VALUES (123, 'Alice', 4, 'Great product!', 
        PARSE_JSON('{"source": "web", "helpful_count": 12}'));

-- Query the VARIANT column
SELECT review_id, metadata:source::VARCHAR AS source
FROM product_reviews;

Working with Semi-Structured Data: VARIANT, OBJECT, and ARRAY

Snowflake excels at handling semi-structured data like JSON, Avro, Parquet, and XML. The key data types are: - VARIANT: Can hold any value of any other data type, including OBJECT and ARRAY. - OBJECT: A collection of key-value pairs (like a JSON object). - ARRAY: An ordered list of elements.

You can load JSON data directly into a VARIANT column. Snowflake provides powerful functions to query this data, such as: - : (colon) operator to access object fields. - [] bracket notation for array elements. - FLATTEN function to expand arrays into rows. - PARSE_JSON to convert a string to VARIANT.

Let's see an example with an ARRAY of objects.

-- Create a table to store customer interactions
CREATE TABLE customer_interactions (
    interaction_id INTEGER AUTOINCREMENT,
    customer_id INTEGER,
    interaction_data VARIANT
);

-- Insert JSON data
INSERT INTO customer_interactions (customer_id, interaction_data)
VALUES (1, PARSE_JSON('{
    "type": "purchase",
    "timestamp": "2025-03-15T10:30:00",
    "items": [
        {"product_id": 101, "quantity": 2},
        {"product_id": 102, "quantity": 1}
    ]
}'));

-- Query: get all product IDs from the first interaction
SELECT interaction_id,
       interaction_data:items[0].product_id::INTEGER AS first_product
FROM customer_interactions;

-- Use FLATTEN to expand array
SELECT i.interaction_id,
       f.value:product_id::INTEGER AS product_id,
       f.value:quantity::INTEGER AS quantity
FROM customer_interactions i,
LATERAL FLATTEN(input => i.interaction_data:items) f;

Altering and Dropping Tables

After creating tables, you may need to modify them. Snowflake supports ALTER TABLE to add, drop, or modify columns, rename tables, and change other properties. You can also drop tables when they are no longer needed.

Be cautious when altering tables in production. Dropping a column or changing data types can cause data loss or application errors. Always test in a development environment first.

Let's see some examples.

-- Add a new column 'discount' to orders table
ALTER TABLE orders ADD COLUMN discount DECIMAL(5,2) DEFAULT 0.00;

-- Rename 'total_amount' to 'gross_amount'
ALTER TABLE orders RENAME COLUMN total_amount TO gross_amount;

-- Modify data type of 'discount' to allow larger values
ALTER TABLE orders ALTER COLUMN discount SET DATA TYPE DECIMAL(10,2);

-- Rename table 'order_items' to 'line_items'
ALTER TABLE order_items RENAME TO line_items;

-- Drop the 'line_items' table (be careful!)
-- DROP TABLE line_items;

-- Show table details
DESCRIBE TABLE orders;

Best Practices for Database, Schema, and Table Design

Designing your Snowflake environment properly from the start saves headaches later. Here are some best practices:

1. Use separate databases for different environments (dev, test, prod). This prevents accidental data corruption.
2. Use schemas to organize by domain (e.g., sales, inventory, marketing). This makes permissions easier.
3. Name objects consistently – use lowercase with underscores (e.g., customer_orders). Snowflake converts to uppercase unless quoted, but using lowercase in scripts is more readable.
4. Set appropriate data types – use the smallest type that fits your data. For example, use DATE instead of TIMESTAMP if time is not needed.
5. Use NOT NULL constraints for columns that must have values. This improves data quality.
6. Add comments to tables and columns using COMMENT statement. This helps other developers understand the schema.
7. Plan for growth – use AUTOINCREMENT for surrogate keys, but consider using sequences for high-volume tables.
8. Avoid using reserved words as object names (e.g., TABLE, SELECT). If you must, use double quotes.

Let's see an example of a well-designed schema with comments.

-- Create a database for production
CREATE DATABASE IF NOT EXISTS prod_ecommerce;

-- Create schema for sales
CREATE SCHEMA IF NOT EXISTS prod_ecommerce.sales;

-- Create a table with comments
CREATE TABLE prod_ecommerce.sales.orders (
    order_id INTEGER AUTOINCREMENT COMMENT 'Unique order identifier',
    customer_id INTEGER NOT NULL COMMENT 'Foreign key to customers',
    order_date DATE NOT NULL COMMENT 'Date order was placed',
    total_amount DECIMAL(10,2) COMMENT 'Total order amount before discounts',
    status VARCHAR(20) DEFAULT 'PENDING' COMMENT 'Order status: PENDING, SHIPPED, DELIVERED, CANCELLED',
    PRIMARY KEY (order_id)
) COMMENT = 'Stores customer orders';

-- Add table comment
COMMENT ON TABLE prod_ecommerce.sales.orders IS 'Main orders table for e-commerce platform';

-- Add column comments
ALTER TABLE prod_ecommerce.sales.orders MODIFY COLUMN total_amount COMMENT 'Total amount in USD';

-- View comments
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ORDERS';
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ORDERS';