Converting Standard Snowflake Tables to Hybrid Tables

Overview

Note on Production Migrations: Always test migrations on a non-production copy first. Validate row counts, query plans, and application behavior before decommissioning the standard table. Production OLTP workloads should use bound variables (parameterized queries) for plan cache reuse. See Hybrid Tables Best Practices and Performance Testing for Hybrid Tables.

Scenario

What You Will Learn

Prerequisites

Setup

Create Role, Warehouse, and Database

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE ROLE HT_MIGRATION_QS_ROLE;
GRANT ROLE HT_MIGRATION_QS_ROLE TO ROLE ACCOUNTADMIN;

CREATE OR REPLACE WAREHOUSE HT_MIGRATION_QS_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;
GRANT OWNERSHIP ON WAREHOUSE HT_MIGRATION_QS_WH TO ROLE HT_MIGRATION_QS_ROLE;

CREATE OR REPLACE DATABASE HT_MIGRATION_QS_DB;
GRANT OWNERSHIP ON DATABASE HT_MIGRATION_QS_DB TO ROLE HT_MIGRATION_QS_ROLE;

USE ROLE HT_MIGRATION_QS_ROLE;

CREATE OR REPLACE SCHEMA HT_MIGRATION_QS_DB.ORDERS;

USE WAREHOUSE HT_MIGRATION_QS_WH;
USE DATABASE HT_MIGRATION_QS_DB;
USE SCHEMA ORDERS;

Step 1: Create the Standard Table

CREATE OR REPLACE TABLE orders_standard (
    order_id     NUMBER        NOT NULL,
    customer_id  NUMBER        NOT NULL,
    status       VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    region       VARCHAR(10)   NOT NULL,
    created_at   TIMESTAMP_NTZ NOT NULL,
    updated_at   TIMESTAMP_NTZ,
    total_amount NUMBER(12,2)  NOT NULL
);

INSERT INTO orders_standard (order_id, customer_id, status, region, created_at, updated_at, total_amount)
SELECT
    SEQ4()                                                AS order_id,
    UNIFORM(1, 10000, RANDOM())::NUMBER                  AS customer_id,
    ARRAY_CONSTRUCT('PENDING','SHIPPED','DELIVERED','CANCELLED')
        [UNIFORM(0, 3, RANDOM())]::VARCHAR               AS status,
    ARRAY_CONSTRUCT('US-EAST','US-WEST','EU','APAC')
        [UNIFORM(0, 3, RANDOM())]::VARCHAR               AS region,
    DATEADD(SECOND,
        UNIFORM(0, 7776000, RANDOM()),
        DATEADD(DAY, -90, CURRENT_TIMESTAMP()))::TIMESTAMP_NTZ  AS created_at,
    NULL                                                 AS updated_at,
    ROUND(UNIFORM(5.00, 2500.00, RANDOM()), 2)           AS total_amount
FROM TABLE(GENERATOR(ROWCOUNT => 500000));

SELECT COUNT(*) FROM orders_standard;
-- Expected: 500000

Run a Baseline Query

SET SAMPLE_CUSTOMER = (SELECT customer_id FROM orders_standard LIMIT 1);

SELECT order_id, status, created_at, total_amount
FROM orders_standard
WHERE customer_id = $SAMPLE_CUSTOMER
ORDER BY created_at DESC;

Step 2: Assess the Table for Hybrid Table Suitability

Assessment Checklist

Verify Primary Key Uniqueness

SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT order_id) AS distinct_order_ids
FROM orders_standard;
-- Both values should be 500000

Check for Unsupported Downstream Dependencies

Identify Index Candidates

SELECT 'customer_id' AS column_name, APPROX_COUNT_DISTINCT(customer_id) AS distinct_values FROM orders_standard
UNION ALL
SELECT 'status', APPROX_COUNT_DISTINCT(status) FROM orders_standard
UNION ALL
SELECT 'region', APPROX_COUNT_DISTINCT(region) FROM orders_standard;

Step 3: Create the Hybrid Table

CREATE OR REPLACE HYBRID TABLE orders_hybrid (
    order_id     NUMBER        NOT NULL,
    customer_id  NUMBER        NOT NULL,
    status       VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    region       VARCHAR(10)   NOT NULL,
    created_at   TIMESTAMP_NTZ NOT NULL,
    updated_at   TIMESTAMP_NTZ,
    total_amount NUMBER(12,2)  NOT NULL,
    PRIMARY KEY (order_id),
    INDEX idx_orders_customer_id (customer_id),
    INDEX idx_orders_status_region_ts (status, region, created_at)
)
AS SELECT * FROM orders_standard;

Note: CTAS for Hybrid Tables requires you to declare the full column schema explicitly. Unlike standard table CTAS, the schema cannot be inferred from the SELECT. If your table requires FOREIGN KEY constraints, you must create the schema separately and use INSERT INTO ... SELECT to load (CTAS does not support FOREIGN KEY).

Note: You can also add secondary indexes after table creation using CREATE INDEX. This is useful when you discover new access patterns on a live table. For a full exploration of adding indexes to populated tables, composite index column ordering, INCLUDE columns, and anti-patterns, see the Secondary Index Design for Hybrid Tables quickstart.

Validate Row Counts

SELECT 'orders_standard' AS table_name, COUNT(*) AS row_count FROM orders_standard
UNION ALL
SELECT 'orders_hybrid', COUNT(*) FROM orders_hybrid;

Spot-Check Data Integrity

SET SAMPLE_ORDER = (SELECT order_id FROM orders_hybrid LIMIT 1);

SELECT * FROM orders_hybrid   WHERE order_id = $SAMPLE_ORDER;
SELECT * FROM orders_standard WHERE order_id = $SAMPLE_ORDER;

Verify Indexes

SHOW INDEXES IN TABLE orders_hybrid;

Step 4: Validate with Query Profile Comparison

Customer Lookup Comparison

SET SAMPLE_CUSTOMER = (SELECT customer_id FROM orders_hybrid LIMIT 1);

SELECT order_id, status, created_at, total_amount
FROM orders_standard
WHERE customer_id = $SAMPLE_CUSTOMER
ORDER BY created_at DESC;

SELECT order_id, status, created_at, total_amount
FROM orders_hybrid
WHERE customer_id = $SAMPLE_CUSTOMER
ORDER BY created_at DESC;

Primary Key Lookup Comparison

SET SAMPLE_ORDER = (SELECT order_id FROM orders_hybrid LIMIT 1);

SELECT * FROM orders_standard WHERE order_id = $SAMPLE_ORDER;

SELECT * FROM orders_hybrid WHERE order_id = $SAMPLE_ORDER;

Composite Index Validation

SELECT order_id, customer_id, created_at, total_amount
FROM orders_hybrid
WHERE status = 'PENDING'
  AND region = 'US-EAST'
  AND created_at > DATEADD(DAY, -7, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ
ORDER BY created_at DESC;

Note: The ::TIMESTAMP_NTZ cast on CURRENT_TIMESTAMP() is required. Without it, the implicit conversion from TIMESTAMP_LTZ prevents the predicate from being pushed to the index. This applies whenever comparing a TIMESTAMP_NTZ indexed column against CURRENT_TIMESTAMP().

Step 5: Zero-Downtime Swap

The Swap Pattern

ALTER TABLE orders_standard RENAME TO orders_standard_backup;
ALTER TABLE orders_hybrid   RENAME TO orders_standard;

Verify the Swap

SHOW TABLES LIKE 'orders%';

SELECT order_id, status, created_at, total_amount
FROM orders_standard
WHERE customer_id = $SAMPLE_CUSTOMER
ORDER BY created_at DESC;

Rollback (If Needed)

ALTER TABLE orders_standard        RENAME TO orders_hybrid;
ALTER TABLE orders_standard_backup RENAME TO orders_standard;

Decommission the Backup (When Ready)

DROP TABLE orders_standard_backup;

Get Started Faster with Cortex Code

"Assess this Standard Table DDL for Hybrid Table suitability. Identify the primary key candidate, recommended secondary indexes, and any incompatible column types: [paste DDL]."

"Generate the migration SQL to convert this Standard Table to a Hybrid Table, including CREATE HYBRID TABLE, COPY INTO for the initial load, and SWAP: [paste DDL and row count]."

"Write a validation query to confirm my Standard-to-Hybrid migration was successful. Compare row counts, sample row checksums, and verify all constraints are enforced on the new HT."

Cleanup

USE ROLE ACCOUNTADMIN;
DROP DATABASE IF EXISTS HT_MIGRATION_QS_DB;
DROP WAREHOUSE IF EXISTS HT_MIGRATION_QS_WH;
DROP ROLE IF EXISTS HT_MIGRATION_QS_ROLE;

Conclusion and Resources

Migration Decision Summary

Need help with your Hybrid Table architecture? Book a 30-minute session with our specialist team to discuss your use case, review your schema design, or troubleshoot performance: Schedule a session

Related Resources

FAQ and Troubleshooting