Skip to main content

โœ… 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 TypeWhen to Use
PermanentFor long-term data storage with full recovery support
TransientFor intermediate or staging data where fail-safe is not required
TemporaryFor session-level scratchpad or intermediate results
ExternalFor querying external cloud storage (e.g., S3, Azure Blob)
IcebergFor 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:โ€‹

IssueWhy 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 NULL constraints 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 TRANSIENT keyword for temporary but persistent data without fail-safe
  • Proper data types with NOT NULL where 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.