Table Properties

Snowflake · Tables & Data Handling

Theory

Besides columns and data types, a table carries properties you set with CREATE TABLE or ALTER TABLE. CLUSTER BY defines a clustering key so Snowflake keeps rows with similar values together, which improves pruning on very large tables. DATA_RETENTION_TIME_IN_DAYS sets the Time Travel window, from 0 (off) up to 90 days on Enterprise and above. COMMENT and tags document the table in its metadata. CHANGE_TRACKING enables change tracking that streams and CHANGES queries rely on.

For example, ALTER TABLE customers SET DATA_RETENTION_TIME_IN_DAYS = 30 lets you query or restore customers as it was any time in the last 30 days, at the cost of storing the changed data for that long.

Why we use it

These settings let you tune cost, recovery and performance per table instead of for the whole account. A critical table can keep 30 days of history while a large staging table keeps none.

When to use it

Set retention by how valuable and how frequently changed a table is. Add a clustering key only to very large tables with clear filter patterns. Always add comments on shared tables so others know what they contain.

Clustering Keys

Tell Snowflake which columns to use when physically co-locating data, sharpening pruning on very large tables — most useful on a frequently-filtered column like a date.

Comments and Documentation

COMMENT attaches human-readable documentation directly to a table's metadata, so its purpose is discoverable by anyone querying the schema, not buried in a separate wiki.

Retention Settings

DATA_RETENTION_TIME_IN_DAYS controls how far back Time Travel can look for that specific table, from 0 (disabled) up to 90 days on Enterprise edition — set per table to balance recovery needs against storage cost.

How it works

  1. Create or review a table
  2. Add a COMMENT describing purpose and owner
  3. Is the table huge and filtered on a few columns? Yes: Consider CLUSTER BY on those columns. No: Leave natural ordering.
  4. Set DATA_RETENTION_TIME_IN_DAYS to match recovery needs
  5. Check settings with SHOW TABLES or DESCRIBE TABLE

Example

ALTER TABLE customers SET DATA_RETENTION_TIME_IN_DAYS = 30;
ALTER TABLE customers SET COMMENT = 'Main customer master table';
ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(20) DEFAULT 'BRONZE';
ALTER TABLE customers RENAME COLUMN city TO customer_city;
ALTER TABLE customers MODIFY COLUMN phone SET DATA TYPE VARCHAR(30);

DESCRIBE TABLE customers;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CUSTOMERS';

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic