โ Best Practices for DDL
Designing and managing database structures efficiently is key to maintaining performance, reliability, and scalability. Below are industry-standard practices and Snowflake-specific recommendations for working with DDL (Data Definition Language).
๐๏ธ DDL Best Practices (Data Definition Language)โ
1. ๐งฑ Use Appropriate Table Typesโ
| Table Type | When to Use |
|---|---|
| Permanent | For long-term data storage with full recovery support |
| Transient | For intermediate or staging data where fail-safe is not required |
| Temporary | For session-level scratchpad or intermediate results |
| External | For querying external cloud storage (e.g., S3, Azure Blob) |
| Iceberg | For open table format and large-scale lakehouse data management |
2. ๐งพ Naming Conventionsโ
- Use meaningful, readable, and standardized names make maintenance easier and reduce errors:
employee_salary,sales_summary_qtrly,dim_customer- Use lowercase and underscore for consistency (snake_case):
- โ
Use
snake_case(e.g.,sales_summary_2024) - โ
Prefix according to purpose:
โฃtmp_for temporary tables
โฃstg_for staging
โฃdim_,fact_for dimensional modeling - โ
Avoid using reserved words or ambiguous names like
data,table,info
3. ๐ Schema Design Principlesโ
- Normalize for transactional systems; denormalize for analytics.
- Group related objects logically in schemas.
- Separate environments using different databases or schemas:
raw,staging,analytics,sandbox
4. ๐ Use CREATE OR REPLACE With Cautionโ
CREATE OR REPLACE TABLE ...
- โ Useful during development for quick iterations.
- โ ๏ธ Be careful: it drops and recreates the object โ all data is lost.
5. ๐งช Validate with SHOW and DESCRIBEโ
-
Use:
SHOW TABLES;
DESCRIBE TABLE my_table;to confirm structure before running destructive DDL.
6. ๐ Leverage Time Travel & Cloningโ
-
Use zero-copy cloning for fast development and testing:
CREATE TABLE my_table_clone CLONE my_table; -
Recover dropped tables or schemas using:
UNDROP TABLE my_table;
๐ซ Sample SQL: Without Best Practicesโ
-- โ Bad Practice: Ambiguous, non-descriptive table and column names
CREATE TABLE data (
col1 DATE, -- โ Unclear: What does col1 represent?
col2 INT, -- โ Unclear: Region? ID? Count?
col3 VARCHAR, -- โ No size specified, vague name
col4 NUMBER, -- โ No precision/scale for currency
col5 STRING -- โ STRING is alias, but VARCHAR is preferred in Snowflake
);
๐ซ What's Wrong in This Example:โ
| Issue | Why It's a Problem |
|---|---|
data as table name | โ Generic name โ unclear purpose |
col1, col2, etc. | โ No meaning โ hard to understand or maintain |
VARCHAR with no size | โ Can lead to inefficient storage (up to 16MB by default) |
NUMBER with no scale | โ Might store unintended precision for amounts |
STRING used | โ Alias for VARCHAR, but inconsistent usage across teams/projects |
| No comments | โ Lacks documentation, making onboarding harder |
๐งฑ Sample SQL: Best Practices for Creating Tables (DDL)โ
-- โ
Best Practice: Use meaningful, snake_case naming convention
-- This table stores sales summary by region and date
CREATE OR REPLACE TABLE sales_summary_by_region (
sales_date DATE, -- โ
Use DATE for date-only values
region_id INT, -- โ
Use INT for numeric identifiers
region_name VARCHAR(100), -- โ
Use size limits for VARCHAR when possible
total_sales_amount NUMBER(12,2), -- โ
Use appropriate precision and scale for currency
is_target_met BOOLEAN -- โ
Use BOOLEAN for true/false flags
)
COMMENT = 'Summary of daily sales totals by region'; -- โ
Add comments for documentation
-- โ
Add clustering key if querying large table by specific columns (advanced)
-- ALTER TABLE sales_summary_by_region CLUSTER BY (sales_date);
๐ก Highlights of Best Practices Shown:โ
- โ
Clear and descriptive table name using
snake_case - โ
Typed columns correctly (
DATE,INT,VARCHAR,BOOLEAN) - โ Column comments (optional, but useful via COMMENT ON or inline annotations)
- โ
Proper precision for
NUMBER(12,2)for monetary values - โ Optional clustering key suggestion for large-scale optimization
๐๏ธ Permanent Table โ Best Practiceโ
CREATE OR REPLACE TABLE sales_data_permanent (
sales_id INT AUTOINCREMENT, -- Unique sales identifier with auto-increment
sales_date DATE NOT NULL, -- Date of the sale
customer_name VARCHAR(100) NOT NULL, -- Customer's full name
amount NUMBER(12, 2) NOT NULL -- Sale amount with precision for currency
)
COMMENT = 'Permanent table to store finalized sales transactions for reporting purposes';
- Clear, descriptive table and column names
- Proper data types with size and precision
- Use of
NOT NULLconstraints where applicable - Inline comments for clarity
- Table-level comment for documentation
๐ Temporary Table โ Best Practiceโ
CREATE OR REPLACE TEMPORARY TABLE tmp_stg_sales_data (
sales_id INT,
sales_date DATE,
customer_name VARCHAR(100),
amount NUMBER(12, 2)
)
COMMENT = 'Temporary staging table for sales data during ETL session';
- Prefix table name with
tmp_for temporary tables - Same attention to data types and sizing
- Table comment to explain purpose
- Used only for session-level, intermediate data
โก Transient Table โ Best Practiceโ
CREATE OR REPLACE TRANSIENT TABLE stg_sales_data_transient (
sales_id INT,
sales_date DATE NOT NULL,
customer_name VARCHAR(100),
amount NUMBER(12, 2)
)
COMMENT = 'Transient staging table for temporary sales data storage during ETL processes';
- Prefix
stg_indicates staging data - Use
TRANSIENTkeyword for temporary but persistent data without fail-safe - Proper data types with
NOT NULLwhere applicable - Table comment explains usage clearly
Notes:
- Transient tables reduce storage costs compared to permanent tables by skipping fail-safe.
- Data persists beyond sessions until dropped, unlike temporary tables.