Serving Low-Latency Data with Hybrid Tables

Overview

Note on Production Workloads: Production serving workloads should use bound variables (parameterized queries), private key authentication, and connection pooling. See Hybrid Tables Best Practices and Connecting Applications to Hybrid Tables.

Why Not Just Use a Standard Table?

What You Will Learn

Prerequisites

Setup

USE ROLE ACCOUNTADMIN;

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

CREATE OR REPLACE WAREHOUSE HT_SERVE_QS_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD';
GRANT OWNERSHIP ON WAREHOUSE HT_SERVE_QS_WH TO ROLE HT_SERVE_QS_ROLE;

CREATE OR REPLACE DATABASE HT_SERVE_QS_DB;
GRANT OWNERSHIP ON DATABASE HT_SERVE_QS_DB TO ROLE HT_SERVE_QS_ROLE;

USE ROLE HT_SERVE_QS_ROLE;
CREATE OR REPLACE SCHEMA HT_SERVE_QS_DB.DATA;
USE WAREHOUSE HT_SERVE_QS_WH;
USE DATABASE HT_SERVE_QS_DB;
USE SCHEMA DATA;

Note: The warehouse uses MAX_CLUSTER_COUNT = 3 and SCALING_POLICY = 'STANDARD'. For serving workloads with high concurrency, multi-cluster warehouses scale horizontally to handle burst traffic without queuing.

Step 1: The Reverse ETL Problem

[Snowflake Standard Table]
    → ETL/Transform (Python, dbt, Airflow)
    → Export (COPY INTO S3 / API push)
    → External Cache (Redis, DynamoDB, S3 JSON)
    → Application reads from cache

[Snowflake Standard Table]
    → Task (CTAS+SWAP into Hybrid Table)
    → Application reads directly from Snowflake

Scenario 1: Email/Marketing Personalization

The Use Case

Design the Serving Table

CREATE OR REPLACE HYBRID TABLE user_recommendations (
    user_id         NUMBER       NOT NULL,
    content_id      NUMBER       NOT NULL,
    rank            NUMBER       NOT NULL,
    score           FLOAT        NOT NULL,
    content_title   VARCHAR(500),
    content_url     VARCHAR(2000),
    computed_at     TIMESTAMP_NTZ NOT NULL,
    PRIMARY KEY (user_id, content_id)
);

Create the Source Table (Simulates Scoring Pipeline Output)

CREATE OR REPLACE TABLE recommendations_source AS
SELECT
    user_id,
    content_id,
    rank,
    score,
    content_title,
    content_url,
    CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS computed_at
FROM (
    SELECT
        u.user_id,
        c.content_id,
        ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY RANDOM()) AS rank,
        ROUND(UNIFORM(0.01, 1.00, RANDOM()), 4) AS score,
        'Article ' || c.content_id::VARCHAR AS content_title,
        'https://example.com/content/' || c.content_id::VARCHAR AS content_url
    FROM
        (SELECT SEQ4() + 1 AS user_id FROM TABLE(GENERATOR(ROWCOUNT => 10000))) u
    CROSS JOIN
        (SELECT SEQ4() + 1 AS content_id FROM TABLE(GENERATOR(ROWCOUNT => 20))) c
)
WHERE rank <= 10;

SELECT COUNT(*) FROM recommendations_source;
-- Expected: 100000 (10,000 users x 10 recommendations each)

Initial Load via CTAS

CREATE OR REPLACE HYBRID TABLE user_recommendations (
    user_id         NUMBER       NOT NULL,
    content_id      NUMBER       NOT NULL,
    rank            NUMBER       NOT NULL,
    score           FLOAT        NOT NULL,
    content_title   VARCHAR(500),
    content_url     VARCHAR(2000),
    computed_at     TIMESTAMP_NTZ NOT NULL,
    PRIMARY KEY (user_id, content_id)
)
AS SELECT * FROM recommendations_source;

Serve: Lookup Recommendations for a User

SET TARGET_USER = (SELECT user_id FROM user_recommendations LIMIT 1);

SELECT content_id, rank, score, content_title, content_url
FROM user_recommendations
WHERE user_id = $TARGET_USER
ORDER BY rank;

Refresh: The CTAS+SWAP Pattern

-- Step 1: Build the new version (CTAS into a temporary name)
CREATE OR REPLACE HYBRID TABLE user_recommendations_new (
    user_id         NUMBER       NOT NULL,
    content_id      NUMBER       NOT NULL,
    rank            NUMBER       NOT NULL,
    score           FLOAT        NOT NULL,
    content_title   VARCHAR(500),
    content_url     VARCHAR(2000),
    computed_at     TIMESTAMP_NTZ NOT NULL,
    PRIMARY KEY (user_id, content_id)
)
AS SELECT * FROM recommendations_source;

-- Step 2: Atomic swap (applications see no interruption)
ALTER TABLE user_recommendations SWAP WITH user_recommendations_new;

-- Step 3: Drop the old version (now in the _new name)
DROP TABLE user_recommendations_new;

Why SWAP instead of RENAME? ALTER TABLE ... SWAP WITH exchanges the contents of two tables atomically in a single metadata operation. Applications querying user_recommendations see the new data immediately after the swap with zero downtime. RENAME requires two statements (rename old, rename new) which creates a brief window where the table name does not exist.

Refresh Task (Scheduled)

CREATE OR REPLACE TASK refresh_user_recommendations
  WAREHOUSE = HT_SERVE_QS_WH
  SCHEDULE = 'USING CRON 0 */4 * * * UTC'
AS
BEGIN
  CREATE OR REPLACE HYBRID TABLE user_recommendations_new (
    user_id NUMBER NOT NULL, content_id NUMBER NOT NULL,
    rank NUMBER NOT NULL, score FLOAT NOT NULL,
    content_title VARCHAR(500), content_url VARCHAR(2000),
    computed_at TIMESTAMP_NTZ NOT NULL,
    PRIMARY KEY (user_id, content_id)
  )
  AS SELECT * FROM recommendations_source;

  ALTER TABLE user_recommendations SWAP WITH user_recommendations_new;
  DROP TABLE user_recommendations_new;
END;

Scenario 2: API Backend (Entitlements/Session State)

The Use Case

Design the Serving Table

CREATE OR REPLACE HYBRID TABLE user_entitlements (
    user_id         VARCHAR(100)  NOT NULL,
    resource_type   VARCHAR(50)   NOT NULL,
    resource_id     VARCHAR(200)  NOT NULL,
    access_level    VARCHAR(20)   NOT NULL,
    granted_at      TIMESTAMP_NTZ NOT NULL,
    expires_at      TIMESTAMP_NTZ,
    PRIMARY KEY (user_id, resource_type, resource_id)
);

Load Sample Data

INSERT INTO user_entitlements
SELECT
    'user_' || u.id::VARCHAR AS user_id,
    ARRAY_CONSTRUCT('PORTFOLIO','REPORT','DASHBOARD','API_ENDPOINT')
        [UNIFORM(0,3,RANDOM())]::VARCHAR AS resource_type,
    'resource_' || UNIFORM(1, 500, RANDOM())::VARCHAR AS resource_id,
    ARRAY_CONSTRUCT('READ','WRITE','ADMIN')[UNIFORM(0,2,RANDOM())]::VARCHAR AS access_level,
    DATEADD(DAY, -UNIFORM(1,365,RANDOM()), CURRENT_TIMESTAMP())::TIMESTAMP_NTZ AS granted_at,
    DATEADD(DAY, UNIFORM(30,365,RANDOM()), CURRENT_TIMESTAMP())::TIMESTAMP_NTZ AS expires_at
FROM (SELECT SEQ4() + 1 AS id FROM TABLE(GENERATOR(ROWCOUNT => 50000))) u;

API Query Patterns

SET API_USER = 'user_42';
SET API_RESOURCE_TYPE = 'PORTFOLIO';
SET API_RESOURCE_ID = 'resource_100';

SELECT access_level
FROM user_entitlements
WHERE user_id = $API_USER
  AND resource_type = $API_RESOURCE_TYPE
  AND resource_id = $API_RESOURCE_ID
  AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP()::TIMESTAMP_NTZ);

SELECT resource_type, resource_id, access_level, expires_at
FROM user_entitlements
WHERE user_id = $API_USER
  AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP()::TIMESTAMP_NTZ)
ORDER BY resource_type;

Incremental Updates (No CTAS+SWAP Needed)

-- Grant new access
INSERT INTO user_entitlements VALUES (
    'user_42', 'DASHBOARD', 'resource_999', 'READ',
    CURRENT_TIMESTAMP()::TIMESTAMP_NTZ,
    DATEADD(DAY, 90, CURRENT_TIMESTAMP())::TIMESTAMP_NTZ
);

-- Revoke access (delete)
DELETE FROM user_entitlements
WHERE user_id = 'user_42' AND resource_type = 'DASHBOARD' AND resource_id = 'resource_999';

-- Bulk sync from source system (MERGE)
MERGE INTO user_entitlements AS tgt
USING entitlements_source AS src
ON tgt.user_id = src.user_id
  AND tgt.resource_type = src.resource_type
  AND tgt.resource_id = src.resource_id
WHEN MATCHED THEN UPDATE SET
  tgt.access_level = src.access_level,
  tgt.expires_at = src.expires_at
WHEN NOT MATCHED THEN INSERT VALUES (
  src.user_id, src.resource_type, src.resource_id,
  src.access_level, src.granted_at, src.expires_at
);

Step 2: Managing the Compaction Window

Why Latency Spikes After a Refresh

Mitigation Strategies

-- Refresh at 2 AM when traffic is low
CREATE OR REPLACE TASK refresh_user_recommendations
  WAREHOUSE = HT_SERVE_QS_WH
  SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
BEGIN
  CREATE OR REPLACE HYBRID TABLE user_recommendations_new (...)
  AS SELECT * FROM recommendations_source;
  ALTER TABLE user_recommendations SWAP WITH user_recommendations_new;
  DROP TABLE user_recommendations_new;
END;

-- Add to the refresh task after the SWAP
SELECT COUNT(*) FROM user_recommendations WHERE user_id = 1;

Step 3: Warehouse Sizing for High-Concurrency Serving

Recommended Configuration

-- For serving workloads: small size, multiple clusters, standard scaling
ALTER WAREHOUSE HT_SERVE_QS_WH SET
  WAREHOUSE_SIZE = 'XSMALL'
  MAX_CLUSTER_COUNT = 5
  MIN_CLUSTER_COUNT = 1
  SCALING_POLICY = 'STANDARD';

Why XSMALL?

When to Go Bigger

Serving Design Checklist

Cleanup

ALTER TASK IF EXISTS refresh_user_recommendations SUSPEND;
USE ROLE ACCOUNTADMIN;
DROP DATABASE IF EXISTS HT_SERVE_QS_DB;
DROP WAREHOUSE IF EXISTS HT_SERVE_QS_WH;
DROP ROLE IF EXISTS HT_SERVE_QS_ROLE;

Conclusion and Resources

When to Use This Pattern vs External Cache

Related Resources

FAQ