Snowflake

Overview

Snowflake is a managed cloud data platform best known as a data warehouse. It stores data centrally, runs SQL queries on independent pools of compute, and lets teams share data without copying it. Its core idea is the separation of storage from compute: data sits in cheap shared storage, while queries run on virtual warehouses that can be started, resized and stopped on demand.

For people who work with tables, Snowflake is a place where large amounts of tabular data are gathered, cleaned and queried. It also offers programming interfaces for machine learning workloads. Tabular foundation models such as TabPFN style systems typically read samples of such tables, so the warehouse becomes the source of context rows, evaluation sets and scoring inputs.

What it is

Snowflake is a service, not software you install. You create an account, load data and run SQL. The provider manages hardware, upgrades, tuning and availability. It runs on the infrastructure of major public clouds, and the same SQL works whichever one hosts the account.

The three layers

Because the layers are separate, many teams can read the same data at once, each on its own compute. A heavy report does not slow down a dashboard that uses a different warehouse.

What you can do with it

• Store structured and semi-structured data (JSON, Avro, Parquet and similar).
• Run analytic SQL over very large tables.
• Load data in bulk or continuously.
• Share live data with other accounts.
• Build data pipelines with streams, tasks and dynamic tables.
• Run Python, Java or Scala code close to the data using Snowpark.
• Govern data with roles, masking and tagging.

Where it came from

Snowflake was designed in the early 2010s by engineers who had worked on database systems. The aim was to build a warehouse for the cloud from scratch, instead of moving an old design onto rented servers. Earlier warehouses tied storage to compute, so growing one meant growing the other, and sharing capacity between teams was hard.

Key design choices from that time:

• Use cheap, durable object storage as the single source of data.
• Make compute elastic and independent.
• Keep a central metadata service so that cloning and time travel are cheap.
• Support semi-structured data natively, with no separate system.
• Hide tuning knobs wherever possible.

The product became generally available in the middle of the 2010s and grew into a broader data platform. Over time it added data sharing, a marketplace, programmatic interfaces, support for open table formats and machine learning features. For exact feature names and release dates, the official documentation is the right reference.

How it works

The best way to understand Snowflake is to follow a query. The everyday analogy is a restaurant: the pantry holds ingredients, kitchens cook, and a manager takes orders.

Step 1: Data is loaded and divided into micro-partitions

When you load a table, Snowflake splits it into small immutable chunks called micro-partitions. Each holds a modest number of rows stored by column and compressed. Snowflake records metadata for each chunk, such as the minimum and maximum value of every column.

Analogy: the pantry is organized into labeled boxes, and each label says what range of items the box holds.

Step 2: A query arrives at the cloud services layer

The cloud services layer checks permissions, parses the SQL, and builds a plan. It uses the metadata to decide which micro-partitions can possibly contain matching rows.

Analogy: the manager reads the order and decides which boxes to fetch.

Step 3: Pruning skips unneeded data

If the query says WHERE order_date = '2025-03-01', boxes whose date range does not include that day are skipped. This is called partition pruning. Less data read means faster queries and lower cost.

Step 4: A virtual warehouse executes the plan

A virtual warehouse is a cluster of compute. It reads the needed micro-partitions, only the needed columns, caches them locally on its own disks, and runs the operations: filters, joins, aggregations.

Warehouses come in sizes. A larger size has more compute and can finish big jobs sooner. Multi-cluster warehouses can add more clusters automatically when many users arrive at once.

Analogy: a bigger kitchen with more cooks prepares a large order faster, and a second kitchen opens when the line gets long.

Step 5: Results are returned and cached

The result goes back to the client. If the same query is repeated and the data has not changed, the stored result can be returned without running it again.

Step 6: Writes create new versions, not edits

Micro-partitions are never changed in place. An update writes new micro-partitions and records that the old ones are replaced. This design gives:

• Time travel: query or restore data as it was within a retention period.
• Zero-copy cloning: make a copy of a table, schema or database that shares the same storage until changes are made.
• Consistent reads: queries see a stable snapshot while writes continue.

Analogy: instead of erasing a page, the library files a new page and keeps a note of which pages make up each edition.

Step 7: Sharing without copying

A provider can grant another account read access to selected tables. The consumer queries the same underlying data through its own compute. No files are copied or moved.

Step 8: Pipelines inside the platform

• Snowpipe loads files as they arrive in cloud storage.
• Streams track row changes on a table, similar in spirit to change data capture.
• Tasks run SQL on a schedule or when a stream has data.
• Dynamic tables keep a derived table up to date from a query.

Step 9: Code runs near the data

Snowpark lets developers write data frame style code in Python, Java or Scala that is translated to SQL or runs in a secure sandbox on the warehouse. This is how feature engineering and model scoring can happen without exporting data.

Key concepts and terms

Semi-structured data

JSON and similar documents can be loaded into a VARIANT column and queried with path expressions, with no need to define every field first. Then FLATTEN turns arrays into rows. This is handy when data arrives from event streams.

Elasticity and idle time

Warehouses can suspend automatically when idle and resume when a query arrives. Time spent running is what counts for compute usage. Because plans and prices change, check the provider's current documentation for details instead of relying on any fixed numbers.

A worked example

This example creates a warehouse, loads a small table, runs a feature query, uses time travel and clones the table for an experiment. Names are placeholders.

Setting up compute and storage

CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

CREATE DATABASE retail_demo;
CREATE SCHEMA retail_demo.sales;

CREATE TABLE retail_demo.sales.orders (
  order_id    NUMBER,
  customer_id NUMBER,
  order_date  DATE,
  channel     STRING,
  amount      NUMBER(12,2),
  returned    BOOLEAN
);

Loading data from files

CREATE STAGE retail_demo.sales.landing;

COPY INTO retail_demo.sales.orders
FROM @retail_demo.sales.landing/orders/
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
ON_ERROR = 'ABORT_STATEMENT';

The ON_ERROR setting makes the load fail loudly, which is usually better than skipping rows without notice.

An analytical query with a window function

SELECT customer_id,
       order_date,
       amount,
       AVG(amount) OVER (PARTITION BY customer_id
                         ORDER BY order_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_last_3
FROM   retail_demo.sales.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) <= 5;

QUALIFY filters on window function results directly, a convenient feature that avoids a nested query.

Handling JSON

CREATE TABLE retail_demo.sales.events (payload VARIANT);

SELECT payload:user.id::NUMBER     AS user_id,
       payload:action::STRING      AS action,
       f.value:sku::STRING         AS sku
FROM   retail_demo.sales.events,
       LATERAL FLATTEN(input => payload:items) f;

Time travel and recovery

-- Someone deletes rows by mistake
DELETE FROM retail_demo.sales.orders WHERE channel = 'web';

-- Look at the table as it was ten minutes ago
SELECT COUNT(*) FROM retail_demo.sales.orders AT (OFFSET => -600);

-- Restore by cloning from the past
CREATE TABLE retail_demo.sales.orders_restored
  CLONE retail_demo.sales.orders AT (OFFSET => -600);

This works within the configured retention period. It makes a safe experiment easy, because a clone costs almost nothing until data changes.

Taking a sample for modeling

SELECT order_id, customer_id, channel, amount, returned
FROM   retail_demo.sales.orders
SAMPLE (5);        -- roughly five percent of rows

For a repeatable sample, use a seed or a hash of the key.

SELECT *
FROM   retail_demo.sales.orders
WHERE  ABS(HASH(order_id)) % 100 < 5;

The hash method returns the same rows every time, which is important when you want to compare model runs.

Reading the data in Python

import snowflake.connector
import pandas as pd

conn = snowflake.connector.connect(
    account="example-account",
    user="analyst",
    password="placeholder-secret",
    warehouse="analytics_wh",
    database="retail_demo",
    schema="sales",
)

df = pd.read_sql(
    "SELECT order_id, channel, amount, returned FROM orders "
    "WHERE ABS(HASH(order_id)) % 100 < 5",
    conn,
)
print(df.shape)
print(df.isna().mean())

Keeping a derived table fresh

CREATE DYNAMIC TABLE retail_demo.sales.customer_features
  TARGET_LAG = '15 minutes'
  WAREHOUSE = analytics_wh
AS
SELECT customer_id,
       COUNT(*)        AS n_orders,
       SUM(amount)     AS total_amount,
       AVG(IFF(returned, 1, 0)) AS return_rate
FROM   retail_demo.sales.orders
GROUP  BY customer_id;

The dynamic table refreshes itself so that results lag the source by no more than the target. This is a simple way to keep feature tables current.

A cost-aware habit

Before a big job, check how much data a query will scan and whether pruning applies. A query with a filter on a clustered date column may read a small fraction of the table. A query that applies a function to the column in the filter may defeat pruning. Test with a small warehouse first.

Strengths and limitations

Strengths

• Elastic compute: scale up or out for a heavy job, scale down after.
• Workload isolation: separate warehouses for separate teams.
• Simple operations: no hardware, little tuning, automatic upgrades.
• Semi-structured support: JSON handled in the same SQL.
• Time travel and cloning: safe experiments and quick recovery.
• Data sharing: live access for other accounts without copies.
• Strong SQL: window functions, QUALIFY, rich functions.
• Governance features: roles, masking, tags and access history.
• Multi-cloud presence: the same experience on different clouds.

Limitations

• Cost needs attention: idle-looking warehouses, oversized warehouses and repeated heavy queries can add up. Monitor usage.
• Not a transactional database: it targets analytics. Many small single-row updates are not its strength.
• Provider dependence: it is a managed service, so features and limits follow the provider's roadmap.
• Latency floor: individual small queries have some fixed overhead, so it is not meant for millisecond application lookups.
• Egress and copies: moving large data out can be slow or costly, and some use cases are better served by an open lake format.
• Learning curve for governance: roles and grants can become complicated without planning.
• Machine learning is secondary: it is possible, but dedicated ML platforms offer more depth, for example Databricks with MLflow.

How it compares with alternatives

Warehouse versus lakehouse

A warehouse organizes data into managed tables optimized for SQL. A lakehouse keeps data as open files in a lake with a table layer on top, such as Apache Iceberg or Delta Lake. The two ideas have been moving toward each other. Snowflake has added support for open table formats, and lakehouse platforms have added warehouse-like SQL. The choice often depends on team skills and on how much ML and data engineering work sits beside SQL.

Moving data into it

Most organizations fill the warehouse using a mix of approaches: bulk loads, continuous file ingestion, connectors from operational databases using change data capture, and streams through Apache Kafka. See data synchronization for how to keep copies correct.

Transforming data in it

Transformation logic is often written as SQL models using a tool such as dbt, scheduled by an orchestrator such as Apache Airflow or by tasks inside the platform.

Role in tabular foundation models

Tabular foundation models learn from tables with rows, typed columns, missing cells and often anonymous columns. Snowflake is a place where such tables are stored, governed and shared. It is not a tabular foundation model, and no specific integration with any model is claimed here. The points below show how its features relate to the needs of such models.

Row and column structure

Warehouse tables are exactly the row-and-column structure tabular models expect. Because storage is columnar, selecting a subset of columns is cheap. Joins and aggregates in SQL turn several related tables into the single wide table that a model reads. Window functions add history per entity, such as the last three amounts.

Mixed types

Warehouse tables mix numbers, dates, strings, booleans and semi-structured values. Before modeling, flatten VARIANT fields into plain columns, make date parts explicit, and cast numeric strings to numbers. Check exact numeric types: NUMBER columns may hold integers or decimals, and codes stored as numbers are really categories.

Missing values

NULL is the standard marker, but real tables also contain placeholders such as empty strings, -1, 9999 or the text "N/A". Replace placeholders with true NULLs before a model sees them, or at least know that they exist. Tabular foundation models often accept missing cells directly, so keeping NULL honest is more useful than filling it with a guess. When filling is needed, see imputation networks.

No column names

Many warehouse tables have clear names, but some are cryptic or generic. Models that work from the values and their relationships do not depend on names. Humans still benefit from the comments, tags and descriptions that Snowflake can attach to columns, and a data catalog can hold the same information for the wider organization.

Pre-training on synthetic or real tables

Pre-training often uses a large number of generated tables rather than customer data; see synthetic data generation. The warehouse enters at the use stage. A pre-trained model is applied to the organization's real tables without retraining, and the warehouse provides governed access. Data sharing features can give an external party controlled access to a sample, which is helpful for evaluation without handing over full copies.

In-context learning

In-context learning needs a labeled sample of rows passed with the rows to be predicted. In a warehouse this is a SQL step: sample with a hash for repeatability, stratify to keep rare classes, and limit to the row count the model supports. Time travel allows rebuilding the exact context used for a past prediction, which is excellent for audits. Zero-copy clones give experiment copies without cost for storage until changes are made.

Continual learning

Data changes. Streams record row changes, and dynamic tables keep derived feature tables current. These features, together with time travel, make it possible to refresh context rows, compare today's distribution with last month's, and decide when a model or its context should be updated. Drift checks are covered in schema drift detection.

Imputation

A good pattern is to store original columns and imputed columns side by side, with a flag column for whether a value was imputed. A view can then expose either. Never overwrite the source value. Time travel offers a safety net if an imputation job goes wrong.

Anomaly detection

Warehouses hold the history needed to define normal behavior. Simple SQL statistics, such as z-scores per segment, can find obvious outliers. Model-based approaches, as described in anomaly detection, score rows and write results to a flags table. Because queries are fast on columns, scanning many rows for rare events is feasible.

Sector and semantic classification

Warehouses often contain descriptive text and codes, such as product names, merchant types and industry fields. A classification model can label each row, and the label, confidence and model version can be stored in a results table keyed by the source key. Masking policies make sure only authorized roles see sensitive text during the process.

Governance

Roles, masking and tagging help meet privacy rules when data is used for models. Use data tokenization for identifiers, data lineage to track where data goes, and data contract validation to enforce agreements on structure. For model design and results, see the architecture and benchmarks pages.

Practical guidance and best practices

Cost and performance

• Start small. Use a small warehouse and scale up only if a query is truly slow.
• Set auto-suspend. Idle warehouses should stop quickly.
• Separate workloads. Give loading, transformation and BI their own warehouses.
• Filter early. Put selective filters on columns used for pruning, and avoid wrapping them in functions.
• Select only needed columns. Avoid SELECT * on wide tables.
• Use resource limits. Set monitors and alerts on usage.
• Cache wisely. Repeated identical queries can reuse results.

Data modeling

• Keep a raw layer, a cleaned layer and a business layer.
• Keep keys and timestamps on every table.
• Prefer clear column names and add comments.
• Use clustering only for very large tables where pruning is poor.
• Use VARIANT for raw semi-structured data, then flatten into typed columns for modeling.

Loading

• Load in reasonably sized files rather than many tiny ones.
• Use ON_ERROR settings deliberately.
• Validate row counts after each load.
• Use streams and tasks, or an external orchestrator, for incremental processing.
• Plan for schema evolution when upstream columns change.

Security and governance

• Use role-based access with least privilege.
• Apply masking policies to sensitive columns.
• Tag sensitive data and review access history.
• Use network controls and strong authentication.
• Keep secrets in a secret manager, not in code.

For machine learning work

• Make samples repeatable with a hash of the key.
• Record the table version or a timestamp used for each training run, so you can query it again with time travel.
• Build features as of the label date to avoid leakage.
• Keep a separate evaluation set that is never used for context or tuning.
• Write predictions back with model version, time and key.

Common mistakes

1. Leaving large warehouses running. Set auto-suspend and monitor usage.
2. Oversizing for every query. A larger warehouse helps heavy jobs but wastes resources on small ones.
3. Using SELECT * on wide tables. Reading unneeded columns slows queries.
4. Defeating pruning. Applying functions to filter columns or filtering on unrelated columns scans too much.
5. Treating it like a transactional database. Frequent single-row updates are inefficient.
6. Loading many tiny files. This adds overhead and slows ingestion.
7. Ignoring time travel limits. Recovery works only within the retention period.
8. Placeholders left as real values. Strings like "N/A" or numbers like -1 mislead models.
9. Random samples that change every run. Use a deterministic sample for comparisons.
10. Leaking labels into features. A column known only after the outcome ruins evaluation.
11. Overly broad roles. Wide permissions make audits and privacy harder.
12. No lineage. Without it, nobody knows which tables feed which models.

Frequently asked questions

What is Snowflake used for?

Snowflake is used to store large amounts of data and run analytic SQL on it, build reports and dashboards, share data with other organizations and run data pipelines. Teams also use it for feature engineering and, through its programming interfaces, for some machine learning workloads close to the data.

What does separation of storage and compute mean?

It means that the data and the machines that process it are managed independently. Data sits in shared storage, and virtual warehouses are started when needed to run queries. You can add or remove compute without moving data, and several teams can use the same data at the same time on their own compute.

What is a micro-partition?

A micro-partition is a small, immutable chunk of table data that Snowflake stores in compressed columnar form. The platform keeps summary metadata about each one, such as minimum and maximum values, which lets queries skip chunks that cannot match a filter. This skipping is called pruning and is a main reason for speed.

What is time travel?

Time travel lets you query, clone or restore data as it was at an earlier moment within a retention period. It is possible because updates create new versions of data chunks instead of editing old ones. It is useful for recovering from mistakes and for recreating the exact data used in a past analysis.

What is zero-copy cloning?

Zero-copy cloning creates a new table, schema or database that points to the same underlying storage as the original. No data is duplicated at first. Storage grows only as the clone or the original is changed. It is a quick way to create test environments and experiment safely.

Can Snowflake handle JSON?

Yes. JSON and similar formats can be loaded into a column of the VARIANT type and queried with path expressions and functions that expand arrays into rows. For modeling, it is common to flatten the fields you need into regular typed columns first.

How does Snowflake differ from a regular relational database?

It is built for analytics: columnar storage, large scans, elastic compute and a managed service. Regular relational databases such as PostgreSQL or Oracle Database are built for many small transactions and application workloads. Many organizations use a transactional database as the source and Snowflake as the analytical copy.

How does data get into Snowflake from operational systems?

Common ways are bulk file loads from cloud storage, continuous ingestion of arriving files, connectors that stream changes using change data capture, and message streams through tools like Kafka. After loading, validation and reconciliation checks confirm that the copy matches the source.

Can Snowflake be used with machine learning models?

Yes. You can query and sample tables, build features with SQL, run code with Snowpark and write predictions back to tables. Larger or more specialized training workloads are often run on dedicated ML platforms, with Snowflake serving as the governed source of tables.

How does Snowflake relate to tabular foundation models?

It is a common home for the tables these models read. It helps with repeatable sampling, point-in-time context, governance and storing predictions. It does not change how the models work. For background on how they read tables, see the model research page.

Glossary

• Account: the top-level Snowflake container for a customer.
• Clone: a copy that shares storage until changed.
• Cloud services layer: the part that handles metadata, security and planning.
• Clustering: the physical ordering of data to improve pruning.
• Dynamic table: a table maintained from a query with a target freshness.
• Fail-safe: an extra recovery period after time travel ends.
• Micro-partition: the basic immutable storage unit.
• Pruning: skipping micro-partitions that cannot match a filter.
• Share: a way to give another account read access to data.
• Snowpark: libraries for running code on the platform.
• Stage: a location for files used in loading and unloading.
• Stream: an object that tracks changes to a table.
• Task: a scheduled or triggered SQL job.
• Time travel: querying data as of an earlier moment.
• VARIANT: a type for semi-structured data.
• Virtual warehouse: a cluster of compute for queries.

Summary

Snowflake is a managed cloud data warehouse that separates storage from compute, stores data in immutable columnar micro-partitions, and offers time travel, cloning, sharing and in-platform pipelines. It is strong for SQL analytics and shared data, and it requires attention to cost and to its role as an analytical, not transactional, system.

For tabular foundation models, it serves as a governed source of tables, with repeatable sampling, point-in-time recovery, feature tables and a place to store predictions and flags. Read more about related platforms in Databricks and data synchronization, and about the models themselves in the architecture page.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: