Data Management and Operations Patterns for Hybrid Tables

Overview

Note on Production Workloads: The SQL in this quickstart uses string literals for clarity. Production OLTP workloads should use bound variables (parameterized queries). See Hybrid Tables Best Practices.

Patterns in This Guide

Other Guides in This Series

Prerequisites

Setup

USE ROLE ACCOUNTADMIN;

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

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

CREATE OR REPLACE DATABASE HT_OPS_QS_DB;
GRANT OWNERSHIP ON DATABASE HT_OPS_QS_DB TO ROLE HT_OPS_QS_ROLE;

-- Every pattern in this guide is driven by a Task. Owning a task is not enough
-- to run one: the owner role needs the EXECUTE TASK privilege, or each run fails
-- with "Cannot execute task, EXECUTE TASK privilege must be granted to owner role".
GRANT EXECUTE TASK ON ACCOUNT TO ROLE HT_OPS_QS_ROLE;

USE ROLE HT_OPS_QS_ROLE;
CREATE OR REPLACE SCHEMA HT_OPS_QS_DB.DATA;
USE WAREHOUSE HT_OPS_QS_WH;
USE DATABASE HT_OPS_QS_DB;
USE SCHEMA DATA;

Create Sample Hybrid Tables

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),
    CONSTRAINT chk_order_status
      CHECK (status IN ('PENDING','SHIPPED','DELIVERED','CANCELLED'))
)
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 => 500000));

CREATE OR REPLACE HYBRID TABLE customers (
    customer_id   NUMBER NOT NULL,
    customer_name VARCHAR NOT NULL,
    tier          VARCHAR(20) NOT NULL,
    region        VARCHAR(10) NOT NULL,
    PRIMARY KEY (customer_id),
    CONSTRAINT chk_customer_tier
      CHECK (tier IN ('BRONZE','SILVER','GOLD','PLATINUM'))
)
AS SELECT SEQ4() + 1, 'customer_' || (SEQ4() + 1)::VARCHAR,
    ARRAY_CONSTRUCT('BRONZE','SILVER','GOLD','PLATINUM')[UNIFORM(0,3,RANDOM())]::VARCHAR,
    ARRAY_CONSTRUCT('US-EAST','US-WEST','EU','APAC')[UNIFORM(0,3,RANDOM())]::VARCHAR
FROM TABLE(GENERATOR(ROWCOUNT => 10000));

SELECT MIN(customer_id) AS c_min, MAX(customer_id) AS c_max FROM customers;
-- Expected: 1, 10000

SELECT COUNT(*) AS orphan_orders
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
-- Expected: 0

INSERT INTO orders VALUES (9999999, 1, 'REFUNDED', 'EU',
    CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 10.00);

001185 (23514): Operation on table ORDERS failed because CHECK constraint
CHK_ORDER_STATUS, which requires that status IN
('PENDING','SHIPPED','DELIVERED','CANCELLED'), was violated

Add the Foreign Key After Loading

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

The DDL Result Does Not Mean It Worked

SHOW INDEXES IN TABLE orders;

Note: Treat SHOW INDEXES as a required step whenever you add a foreign key to a table that already holds data. A successful DDL result tells you the constraint was accepted, not that the existing rows passed validation, and nothing raises an error to tell you otherwise.

When Validation Fails

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

INSERT INTO orders VALUES (9999998, 999999, 'PENDING', 'EU',
    CURRENT_TIMESTAMP()::TIMESTAMP_NTZ, 10.00);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

DELETE FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM customers);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

What the Constraint Changes for the Patterns Below

DELETE FROM customers WHERE customer_id = 1;

200008 (22000): Foreign keys that reference key values still exist.

Step 1: Fan-In Aggregation

Full Refresh Fan-In (CTAS)

CREATE OR REPLACE TASK refresh_consolidated_analytics
  WAREHOUSE = HT_OPS_QS_WH
  SCHEDULE = '15 MINUTES'
AS
  CREATE OR REPLACE TABLE consolidated_orders AS
  SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.tier          AS customer_tier,
    o.status,
    o.region,
    o.created_at,
    o.total_amount
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.customer_id;

ALTER TASK refresh_consolidated_analytics RESUME;

EXECUTE TASK refresh_consolidated_analytics;

SELECT NAME, STATE, SCHEDULED_TIME, COMPLETED_TIME, ERROR_MESSAGE
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
    TASK_NAME => 'REFRESH_CONSOLIDATED_ANALYTICS'))
WHERE COMPLETED_TIME IS NOT NULL
ORDER BY COMPLETED_TIME DESC
LIMIT 5;
-- Wait for the most recent row to show STATE = SUCCEEDED before continuing

SELECT COUNT(*) AS consolidated_rows FROM consolidated_orders;
-- Expected: 500000

SELECT customer_tier, COUNT(*), SUM(total_amount) FROM consolidated_orders GROUP BY customer_tier;

ALTER TASK refresh_consolidated_analytics SUSPEND;

SELECT COUNT(*) AS rows_missing_customer
FROM consolidated_orders WHERE customer_name IS NULL;
-- Expected: 0

Note: Keep this aggregation in a Task, not an Inline Stored Procedure. It reads a hybrid table and writes a standard table, and an Inline Stored Procedure can only touch hybrid tables in a single database. It also uses DDL, which an Inline Stored Procedure does not allow. CREATE OR REPLACE TABLE is already atomic, so there is nothing to gain by wrapping it.

Incremental Fan-In (MERGE)

-- Run as separate tasks or combine into a single Task with scripting
MERGE INTO consolidated_orders AS tgt
USING (
    SELECT o.order_id, o.customer_id, c.customer_name, c.tier AS customer_tier,
           o.status, o.region, o.created_at, o.total_amount
    FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.created_at > DATEADD(MINUTE, -20, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ
) AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND tgt.status != src.status THEN UPDATE SET tgt.status = src.status, tgt.customer_tier = src.customer_tier
WHEN NOT MATCHED THEN INSERT VALUES (src.order_id, src.customer_id, src.customer_name, src.customer_tier, src.status, src.region, src.created_at, src.total_amount);

When to Use Full Refresh vs Incremental

Step 2: Hot/Cold Data Tiering

Create the Archive Table

CREATE OR REPLACE TABLE orders_archive (
    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,
    archived_at  TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
CLUSTER BY (created_at);

Age-Off Task

CREATE OR REPLACE TASK archive_old_orders
  WAREHOUSE = HT_OPS_QS_WH
  SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
BEGIN
  INSERT INTO orders_archive (order_id, customer_id, status, region, created_at, total_amount)
    SELECT order_id, customer_id, status, region, created_at, total_amount
    FROM orders
    WHERE created_at < DATEADD(DAY, -90, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ;

  DELETE FROM orders
    WHERE created_at < DATEADD(DAY, -90, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ;
END;

INSERT INTO orders_archive (order_id, customer_id, status, region, created_at, total_amount)
  SELECT order_id, customer_id, status, region, created_at, total_amount
  FROM orders
  WHERE created_at < DATEADD(DAY, -60, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ;

SELECT COUNT(*) AS archived_rows FROM orders_archive;

DELETE FROM orders
  WHERE created_at < DATEADD(DAY, -60, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ;

SELECT COUNT(*) AS remaining_in_ht FROM orders;

Query Across Hot and Cold

CREATE OR REPLACE VIEW orders_all AS
  SELECT order_id, customer_id, status, region, created_at, total_amount FROM orders
  UNION ALL
  SELECT order_id, customer_id, status, region, created_at, total_amount FROM orders_archive;

SELECT COUNT(*) FROM orders_all;

Storage Sizing Notes

Why This Stays Outside an Inline Stored Procedure

Step 3: Alert-Based Monitoring

Query Top Workloads

SELECT
    query_parameterized_hash,
    ANY_VALUE(query_text)              AS sample_query,
    SUM(calls)                         AS total_executions,
    AVG(total_elapsed_time:"avg"::FLOAT) AS avg_latency_ms,
    MAX(total_elapsed_time:"p99"::FLOAT) AS p99_latency_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.AGGREGATE_QUERY_HISTORY
WHERE interval_start_time > DATEADD(DAY, -1, CURRENT_TIMESTAMP())
  AND warehouse_name = 'HT_OPS_QS_WH'
GROUP BY query_parameterized_hash
ORDER BY total_executions DESC
LIMIT 10;

Monitor Throttling

SELECT
    interval_start_time,
    SUM(calls)                                  AS total_calls,
    SUM(hybrid_table_requests_throttled_count)  AS throttled_requests
FROM SNOWFLAKE.ACCOUNT_USAGE.AGGREGATE_QUERY_HISTORY
WHERE interval_start_time > DATEADD(HOUR, -1, CURRENT_TIMESTAMP())
  AND hybrid_table_requests_throttled_count > 0
GROUP BY interval_start_time
ORDER BY interval_start_time DESC;

Create an Alert Log Table

CREATE OR REPLACE TABLE alert_log (
    alert_ts    TIMESTAMP_NTZ,
    alert_type  VARCHAR,
    message     VARCHAR
);

Create a Throttling Alert

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE ALERT ht_throttle_alert
  WAREHOUSE = HT_OPS_QS_WH
  SCHEDULE = '5 MINUTE'
  IF( EXISTS(
    SELECT 1
    FROM SNOWFLAKE.ACCOUNT_USAGE.AGGREGATE_QUERY_HISTORY
    WHERE hybrid_table_requests_throttled_count > 50
      AND interval_start_time > DATEADD(MINUTE, -10, CURRENT_TIMESTAMP())
  ))
  THEN
    INSERT INTO HT_OPS_QS_DB.DATA.alert_log
    VALUES (CURRENT_TIMESTAMP(), 'THROTTLE', 'More than 50 throttled HT requests in the last 10 minutes');

ALTER ALERT ht_throttle_alert RESUME;
USE ROLE HT_OPS_QS_ROLE;

SHOW ALERTS;
SELECT * FROM alert_log ORDER BY alert_ts DESC;

-- Suspend the alert after this quickstart
USE ROLE ACCOUNTADMIN;
ALTER ALERT ht_throttle_alert SUSPEND;
USE ROLE HT_OPS_QS_ROLE;

AGGREGATE_QUERY_HISTORY Notes

Get Started Faster with Cortex Code

"Assess my Hybrid Table for hot/cold data tiering opportunities. My schema is: [paste DDL]. Recommend a tiering strategy and generate the Task SQL to move cold rows to a Standard Table."

"Design a fan-in aggregation pipeline for my Hybrid Table. I have [N] source tables writing to a single HT. Generate the MERGE task that prevents lock contention."

"Generate Snowflake Alert SQL to notify me when my Hybrid Table p99 latency exceeds 100ms or my error rate exceeds 1% over the past hour."

Cleanup

ALTER TASK IF EXISTS refresh_consolidated_analytics SUSPEND;
ALTER TASK IF EXISTS archive_old_orders SUSPEND;
USE ROLE ACCOUNTADMIN;
ALTER ALERT IF EXISTS ht_throttle_alert SUSPEND;
DROP ALERT IF EXISTS ht_throttle_alert;
DROP DATABASE IF EXISTS HT_OPS_QS_DB;
DROP WAREHOUSE IF EXISTS HT_OPS_QS_WH;
DROP ROLE IF EXISTS HT_OPS_QS_ROLE;

Conclusion and Resources

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