Snowflake Hybrid Table

This class stores result log data in a hybrid Snowflake table.

This storage method requires an active warehouse and it consumes some credits. But warehouse is only started when you have new results to store. If all results are old, this storage class retrieves data from query cache.

The final cost is proportional to frequency of newly reported matched queries. If notifications are relatively rare, the cost should be negligible.

Setup

Snowflake table storage requires you to create a table and to assign a warehouse for SnowKill administration user.

CREATE DATABASE UTILS;
CREATE SCHEMA UTILS.MONITOR;

CREATE HYBRID TABLE UTILS.MONITOR.SNOWKILL_LOG
(
    query_id VARCHAR(16777216),
    check_result_level NUMBER(38,0),
    check_result_name VARCHAR(16777216),
    check_result_description VARCHAR(16777216),
    check_result_time TIMESTAMP_NTZ(6),
    PRIMARY KEY (query_id, check_result_level)
);

CREATE WAREHOUSE ADMIN_MONITOR_WH
WAREHOUSE_SIZE = XSMALL
AUTO_SUSPEND = 60
INITIALLY_SUSPENDED = TRUE;

ALTER USER ADMIN_MONITOR SET
DEFAULT_WAREHOUSE = ADMIN_MONITOR_WH;

Please note the full name of created table. It should be passed to SnowflakeHybridTableStorage constructor.

Example

snowkill_storage = SnowflakeHybridTableStorage(connection, getenv("SNOWFLAKE_TARGET_TABLE"))
check_results = snowkill_storage.store_and_remove_duplicate(check_results)