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
- Create or review a table
- Add a COMMENT describing purpose and owner
- Is the table huge and filtered on a few columns? Yes: Consider CLUSTER BY on those columns. No: Leave natural ordering.
- Set DATA_RETENTION_TIME_IN_DAYS to match recovery needs
- 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
- Clustering keys improve pruning on very large tables only
- COMMENT documents a table's purpose directly in metadata
- DATA_RETENTION_TIME_IN_DAYS controls the Time Travel window
- Longer retention stores more changed data and costs more
- Retention can be set at account, database, schema or table level