Optimizing Writes to Hybrid Tables

Overview

Note: This guide focuses on Snowflake-side write optimization. For driver-specific patterns (JDBC, Python, Node.js connection pooling and batch APIs), see Connecting Applications to Hybrid Tables.

What You Will Learn

Prerequisites

Setup

USE ROLE ACCOUNTADMIN;

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

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

CREATE OR REPLACE DATABASE HT_WRITE_QS_DB;
GRANT OWNERSHIP ON DATABASE HT_WRITE_QS_DB TO ROLE HT_WRITE_QS_ROLE;

USE ROLE HT_WRITE_QS_ROLE;
CREATE OR REPLACE SCHEMA HT_WRITE_QS_DB.DATA;
USE WAREHOUSE HT_WRITE_QS_WH;
USE DATABASE HT_WRITE_QS_DB;
USE SCHEMA DATA;

Create the Orders Table

CREATE OR REPLACE HYBRID TABLE orders (
    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,
    total_amount NUMBER(12,2)  NOT NULL,
    PRIMARY KEY (order_id),
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_status_region (status, region)
)
AS SELECT
    SEQ4(),
    UNIFORM(1, 10000, RANDOM())::NUMBER,
    ARRAY_CONSTRUCT('PENDING','SHIPPED','DELIVERED','CANCELLED')[UNIFORM(0,3,RANDOM())]::VARCHAR,
    ARRAY_CONSTRUCT('US-EAST','US-WEST','EU','APAC')[UNIFORM(0,3,RANDOM())]::VARCHAR,
    DATEADD(SECOND, UNIFORM(0,7776000,RANDOM()), DATEADD(DAY,-90,CURRENT_TIMESTAMP()))::TIMESTAMP_NTZ,
    ROUND(UNIFORM(5.00,2500.00,RANDOM()),2)
FROM TABLE(GENERATOR(ROWCOUNT => 100000));

Step 1: Why Plan Compilation Matters

Measure Compilation vs Execution

SET next_id = (SELECT MAX(order_id) + 1 FROM orders);

INSERT INTO orders (order_id, customer_id, status, region, created_at, total_amount)
VALUES ($next_id, 4200, 'PENDING', 'US-EAST', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 99.99);

The Plan Cache

Step 2: Bound Variables and the Parameterized Hash

How the Plan Cache Identifies Reusable Plans

What Shares the Same Hash

Demonstrating Plan Cache Behavior

INSERT INTO orders VALUES (200001, 1001, 'PENDING', 'US-EAST', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 49.99);
INSERT INTO orders VALUES (200002, 2002, 'SHIPPED', 'EU', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 149.99);
INSERT INTO orders VALUES (200003, 3003, 'DELIVERED', 'APAC', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 299.99);

SELECT query_parameterized_hash, query_text, compilation_time, execution_time, total_elapsed_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    DATEADD('minutes', -5, CURRENT_TIMESTAMP()),
    CURRENT_TIMESTAMP()
))
WHERE query_text LIKE 'INSERT INTO orders VALUES%'
ORDER BY start_time DESC
LIMIT 5;

What Breaks the Hash

-- Different table reference (schema-qualified vs unqualified)
INSERT INTO orders VALUES (200004, 4004, 'PENDING', 'US-WEST', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 59.99);
INSERT INTO HT_WRITE_QS_DB.DATA.orders VALUES (200005, 5005, 'PENDING', 'EU', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 79.99);

SELECT query_parameterized_hash, query_text
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    DATEADD('minutes', -5, CURRENT_TIMESTAMP()),
    CURRENT_TIMESTAMP()
))
WHERE query_text LIKE 'INSERT INTO%orders VALUES%'
ORDER BY start_time DESC
LIMIT 5;

Rule: Pick one naming convention (fully-qualified or unqualified) and use it consistently across all application queries. Mixing them splits your plan cache.

Step 3: Schema Qualification Consistency

The Problem

-- Path A (e.g., from a microservice using USE DATABASE/SCHEMA context)
INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?);

-- Path B (e.g., from a stored procedure or different service using fully-qualified names)
INSERT INTO HT_WRITE_QS_DB.DATA.orders VALUES (?, ?, ?, ?, ?, ?);

How to Detect It

SELECT
    query_parameterized_hash,
    COUNT(*) AS execution_count,
    ANY_VALUE(query_text) AS sample_query,
    AVG(compilation_time) AS avg_compile_ms,
    AVG(total_elapsed_time) AS avg_total_ms
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    DATEADD('hours', -1, CURRENT_TIMESTAMP()),
    CURRENT_TIMESTAMP()
))
WHERE query_text ILIKE '%INSERT INTO%orders%'
GROUP BY query_parameterized_hash
ORDER BY execution_count DESC;

The Fix

-- At connection init (once)
USE DATABASE HT_WRITE_QS_DB;
USE SCHEMA DATA;

-- All subsequent queries (unqualified, same hash every time)
INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?);

Step 4: Stored Procedure Overhead

Why Stored Procedures Add Overhead

Demonstration

CREATE OR REPLACE PROCEDURE insert_order(
    p_order_id NUMBER, p_customer_id NUMBER, p_status VARCHAR,
    p_region VARCHAR, p_amount NUMBER
)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
    INSERT INTO orders (order_id, customer_id, status, region, created_at, total_amount)
    VALUES (:p_order_id, :p_customer_id, :p_status, :p_region, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, :p_amount);
    RETURN 'OK';
END;

-- Direct INSERT (faster)
SET direct_id = (SELECT MAX(order_id) + 1 FROM orders);
INSERT INTO orders (order_id, customer_id, status, region, created_at, total_amount)
VALUES ($direct_id, 7777, 'PENDING', 'US-EAST', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 199.99);

-- SP wrapper (slower)
SET sp_id = (SELECT MAX(order_id) + 2 FROM orders);
CALL insert_order($sp_id, 8888, 'PENDING', 'EU', 299.99);

SELECT query_text, total_elapsed_time, compilation_time, execution_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    DATEADD('minutes', -5, CURRENT_TIMESTAMP()),
    CURRENT_TIMESTAMP()
))
WHERE query_text LIKE 'INSERT INTO orders%' OR query_text LIKE 'CALL insert_order%'
ORDER BY start_time DESC
LIMIT 5;

When Stored Procedures Are Appropriate

The Performance Hierarchy

Step 5: COPY INTO Behavior on Hybrid Tables

Key Behaviors

When to Use COPY INTO vs INSERT

Verify the Optimized Path Was Used

Demonstration

-- Create a stage and export sample data as CSV
CREATE OR REPLACE FILE FORMAT csv_fmt TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"';
CREATE OR REPLACE STAGE orders_stage;

COPY INTO @orders_stage/orders_export
FROM (SELECT order_id, customer_id, status, region, created_at, total_amount FROM orders LIMIT 10000)
FILE_FORMAT = csv_fmt
HEADER = TRUE;

-- Create an empty HT and COPY INTO it (uses optimized bulk load path)
CREATE OR REPLACE HYBRID TABLE orders_copy (
    order_id     NUMBER        NOT NULL,
    customer_id  NUMBER        NOT NULL,
    status       VARCHAR(20)   NOT NULL,
    region       VARCHAR(10)   NOT NULL,
    created_at   TIMESTAMP_NTZ NOT NULL,
    total_amount NUMBER(12,2)  NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE OR REPLACE FILE FORMAT csv_load TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1;

COPY INTO orders_copy
FROM @orders_stage/orders_export
FILE_FORMAT = csv_load;

SELECT COUNT(*) FROM orders_copy;
-- Expected: 10000

Step 6: DML Anti-Patterns

Anti-Pattern 1: String Concatenation Instead of Bound Variables

-- BAD: Every unique value creates a new parameterized hash
-- (In application code, this looks like: f"INSERT INTO orders VALUES ({id}, ...)")
INSERT INTO orders VALUES (300001, 1001, 'PENDING', 'US-EAST', '2026-06-16'::TIMESTAMP_NTZ, 49.99);
INSERT INTO orders VALUES (300002, 1001, 'PENDING', 'US-EAST', '2026-06-16'::TIMESTAMP_NTZ, 49.99);

-- GOOD: Use session variables or driver-level bound parameters
SET v_id = 300003;
SET v_cust = 1001;
INSERT INTO orders VALUES ($v_id, $v_cust, 'PENDING', 'US-EAST', CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 49.99);

Anti-Pattern 2: CTAS Repeated Against a Populated Table

-- BAD: CREATE OR REPLACE on a non-empty HT re-creates indexes from scratch
-- every execution. Appropriate for initial load, not for repeated refresh.
CREATE OR REPLACE HYBRID TABLE orders (...) AS SELECT * FROM source_table;

Anti-Pattern 3: Single-Row INSERTs in a Loop

-- BAD: 1000 round-trips, 1000 compilations
-- (In application code: for row in rows: cursor.execute("INSERT ...", row))

Anti-Pattern 4: BI Tool Running Full-Table CTAS on a HT

-- BAD: BI materialization running GROUP BY directly on HT
CREATE OR REPLACE TABLE dashboard_data AS
SELECT region, status, COUNT(*), SUM(total_amount)
FROM orders  -- This is the Hybrid Table
GROUP BY region, status;

Anti-Pattern 5: Inconsistent Schema Qualification (Covered in Step 3)

Step 7: Measuring Write Performance

Use AGGREGATE_QUERY_HISTORY for High-Throughput Workloads

SELECT
    query_parameterized_hash,
    ANY_VALUE(query_text) AS sample_query,
    SUM(calls) AS total_executions,
    AVG(compilation_time:"avg"::FLOAT) AS avg_compile_ms,
    AVG(execution_time:"avg"::FLOAT) AS avg_exec_ms,
    AVG(total_elapsed_time:"avg"::FLOAT) AS avg_total_ms,
    MAX(total_elapsed_time:"p99"::FLOAT) AS p99_total_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.AGGREGATE_QUERY_HISTORY
WHERE interval_start_time > DATEADD(HOUR, -1, CURRENT_TIMESTAMP())
  AND warehouse_name = 'HT_WRITE_QS_WH'
  AND query_text ILIKE '%INSERT%'
GROUP BY query_parameterized_hash
ORDER BY total_executions DESC;

Key Metrics to Track

Quick Diagnostic

Cleanup

USE ROLE ACCOUNTADMIN;
DROP DATABASE IF EXISTS HT_WRITE_QS_DB;
DROP WAREHOUSE IF EXISTS HT_WRITE_QS_WH;
DROP ROLE IF EXISTS HT_WRITE_QS_ROLE;

Conclusion and Resources

Write Optimization Checklist

Related Resources

FAQ