Getting started with Snowflake: warehouses, loading data and time travel

Snowflake is a cloud data platform that separates storage from compute. Your data lives once in cloud storage, and you spin up independent compute clusters, called virtual warehouses, to query it. That one idea explains most of what makes Snowflake pleasant to work with: teams do not slow each other down, and you only pay for compute while it is running.

The object hierarchy

Everything in Snowflake sits inside a simple hierarchy: an account contains databases, which contain schemas, which contain tables, views, stages and other objects. A common layout for analytics is one database per environment and one schema per layer.

CREATE DATABASE IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS analytics.raw;       -- data as it arrives
CREATE SCHEMA IF NOT EXISTS analytics.staging;   -- cleaned and renamed
CREATE SCHEMA IF NOT EXISTS analytics.marts;     -- facts and dimensions for reporting

USE DATABASE analytics;
USE SCHEMA raw;

Virtual warehouses: compute you switch on and off

A warehouse is a cluster of compute resources. Size determines speed and cost; each size up roughly doubles both. For most small and medium workloads, an extra-small warehouse with a short auto-suspend is plenty.

CREATE WAREHOUSE IF NOT EXISTS transform_wh
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60          -- seconds of inactivity before pausing
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE transform_wh;

Separate warehouses for loading, transformation and BI make costs visible and stop a heavy dashboard from slowing down your pipeline.

Loading data: stages and COPY INTO

A stage is a location where files wait to be loaded. It can be internal (managed by Snowflake) or external (your own S3, Azure or Google Cloud bucket). COPY INTO then loads the files into a table, and remembers which files it has already loaded.

CREATE OR REPLACE FILE FORMAT csv_format
    TYPE = CSV
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    NULL_IF = ('', 'NULL');

CREATE OR REPLACE STAGE raw_stage FILE_FORMAT = csv_format;

-- From SnowSQL or the Snowflake CLI, upload a local file:
-- PUT file://orders_2026_09.csv @raw_stage;

CREATE OR REPLACE TABLE raw.orders (
    order_id     NUMBER,
    customer_id  NUMBER,
    order_date   DATE,
    amount       NUMBER(10, 2)
);

COPY INTO raw.orders
FROM @raw_stage
PATTERN = '.*orders_.*[.]csv'
ON_ERROR = 'ABORT_STATEMENT';

Semi-structured data with VARIANT

Snowflake stores JSON natively in a VARIANT column and lets you query it with a path syntax. FLATTEN turns arrays into rows.

CREATE OR REPLACE TABLE raw.events (payload VARIANT);

SELECT
    payload:order_id::NUMBER          AS order_id,
    payload:customer.email::STRING    AS email,
    item.value:sku::STRING            AS sku,
    item.value:qty::NUMBER            AS qty
FROM raw.events,
     LATERAL FLATTEN(input => payload:items) AS item;

Time travel and UNDROP

Snowflake keeps historical versions of your data for a retention period (one day by default, configurable up to 90 days on Enterprise edition). You can query the past, or restore dropped objects.

-- The table as it was 30 minutes ago
SELECT * FROM marts.fct_sales AT(OFFSET => -60 * 30);

-- Recover from an accidental delete by re-inserting yesterday's rows
INSERT INTO marts.fct_sales
SELECT * FROM marts.fct_sales AT(TIMESTAMP => DATEADD(day, -1, CURRENT_TIMESTAMP()))
WHERE order_date = '2026-09-29';

-- Bring back a dropped table
UNDROP TABLE marts.fct_sales;

Zero-copy cloning

Cloning creates a full, writable copy of a table, schema or database instantly, without duplicating storage. Changes are only stored when either copy is modified. It is perfect for development environments and safe testing.

CREATE DATABASE analytics_dev CLONE analytics;
CREATE TABLE marts.fct_sales_backup CLONE marts.fct_sales;

Automating with streams and tasks

A stream tracks changes to a table; a task runs SQL on a schedule. Together they give you simple incremental pipelines without an external scheduler.

CREATE OR REPLACE STREAM raw.orders_stream ON TABLE raw.orders;

CREATE OR REPLACE TASK load_orders
    WAREHOUSE = transform_wh
    SCHEDULE = 'USING CRON 0 6 * * * Europe/London'
WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_stream')
AS
INSERT INTO staging.orders
SELECT order_id, customer_id, order_date, amount
FROM raw.orders_stream
WHERE METADATA$ACTION = 'INSERT';

ALTER TASK load_orders RESUME;   -- tasks are created suspended

Keeping costs under control

• Start every warehouse at XSMALL with AUTO_SUSPEND = 60, and only size up when a real workload needs it.
• Create a resource monitor with a monthly credit quota and alerts.
• Avoid SELECT * on wide tables; Snowflake stores data by column, so reading fewer columns scans less data.
• Check SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for the slowest and most expensive queries each month.

Once you are comfortable here, the natural next step is managing these transformations as code with dbt. The Snowflake cheat sheet keeps all of these commands on one page.

Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.