Lookup Routing

The LOOKUP_ROUTING Pre-hook routes queries to warehouses, using a customer-maintained lookup table in Snowflake. Each row in the lookup table maps a query to the target warehouse name it should be routed to. Lookup routing operates on generalized queries; specific literal values, casing, and whitespace are ignored when being compared for routing purposes. If Sundeck is able to match a generalized version on an incoming query to a generalized query in the lookup table, then the query will be routed to the warehouse specified in the table. If no match is found, the query will be routed to the configured fallback warehouse.

When matching an incoming query to an entry in the lookup table, Sundeck uses the following rules:

• Whitespace and comments are ignored
• Literal values in query predicate when used with comparison operators =, !=, <, >, <=, >= are ignored
• Qualification of table names is ignored eg. SELECT * FROM SALES.PUBLIC.TRANSACTIONS is matched to SELECT * FROM TRANSACTIONS if the current schema is SALES.PUBLIC
• Case differences for case-insensitive identifiers are ignored eg. SELECT * FROM TRANSACTIONS is matched to SELECT * FROM transactions
• Case differences for case-insensitive keywords are ignored eg. SELECT * FROM TRANSACTIONS is matched to select * from TRANSACTIONS

The first step in using the LOOKUP_ROUTING pre-hook is to create a lookup table in Snowflake. The lookup table should have the following schema:

CREATE TABLE <lookup_routing_table_name> (
  QUERY_TEXT VARCHAR,  -- The query to match incoming queries against
  DATABASE_NAME VARCHAR,  -- Default database (used if query does not qualify tables with a database name)
  SCHEMA_NAME VARCHAR, -- Default schema (used if query does not qualify tables with a schema name)
  QUERY_SIGNATURE VARCHAR, -- Snowflake's QUERY_PARAMETERIZED_HASH for this query (for analysis purposes)
  TARGET_WAREHOUSE VARCHAR, -- The WAREHOUSE_NAME in Snowflake to route the query to (if matched)
);

The table should be readable by the Sundeck Service Account role in Snowflake. This is the user that the Sundeck Broker uses to connect to Snowflake, and is by default named SUNDECK_SERVICE_ACCOUNT.

-- Grant the necessary permissions to the Sundeck Service Account role
GRANT USAGE ON DATABASE <lookup_routing_table_database> TO ROLE SUNDECK_SERVICE_ACCOUNT;
GRANT USAGE ON SCHEMA <lookup_routing_table_schema> TO ROLE SUNDECK_SERVICE_ACCOUNT;

GRANT SELECT ON <lookup_routing_table_name> TO ROLE SUNDECK_SERVICE_ACCOUNT;

The Lookup Routing table must be pinned in Sundeck before it can be used in a Lookup Routing hook. This is done by running the following SQL command while connected through the Sundeck Broker (note this will not currently work via Snowsight):

  ALTER SUNDECK TABLE <lookup_routing_db>.<lookup_routing_schema>.<lookup_routing_table> 
    PIN=TRUE 
    REFRESH_FREQUENCY_SECONDS=300; -- Refresh the pinned table in Broker memory every 5 minutes

Note that the Lookup Routing table can also be a Snowflake view; if this is the case then ALTER SUNDECK VIEW ... is used to pin the view in Sundeck.

The Lookup Routing table approach provides considerable flexibility in how queries are matched to warehouses; you have complete control over which queries Sundeck will attempt to match, as well as the target warehouse they will be routed to. To get started, most customers will populate the table using an INSERT INTO ... SELECT ... FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY statement, passing a where clause to select queries for a specific pattern or use case.

INSERT INTO <lookup_routing_table_name> 
(QUERY_TEXT, DATABASE_NAME, SCHEMA_NAME, QUERY_SIGNATURE, TARGET_WAREHOUSE)
SELECT 
  QUERY_TEXT, 
  DATABASE_NAME, 
  SCHEMA_NAME, 
  QUERY_PARAMETERIZED_HASH, 
  'DESIRED_WAREHOUSE_NAME'
FROM 
  SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE 
  -- Add a where clause to select queries for a specific pattern or use case
  ROLE_NAME = 'FILTERED_ROLE_NAME'
  AND USER_NAME = 'FILTERED_USER_NAME'
  AND QUERY_TEXT LIKE '%pattern%'
  AND START_TIME >= CURRENT_TIMESTAMP - INTERVAL '1' DAY
  AND ...

Often, customers will move from simple insert/select statements during testing, to much more sophisticated table population mechanisms which examine query history for cost, and then automatically populate the Lookup Routing table on a scheduled basis (using Snowflake tasks, for example). This allows for dynamic routing of queries based on historical patterns, and can be a powerful tool for optimizing Snowflake usage.

LOOKUP_ROUTING {
  'lookupTable'  : <lookup_routing_table_name>,
  'fallbackWarehouse' : <default_warehouse_name>
}

<table-name>

Fully qualified name of the lookup table in snowflake (includes database and schema name)

<warehouse-name>

The warehouse to route to for queries which do not have a match in the lookup table

If a non-existent warehouse name is specified for the fallbackWarehouse property during hook configuration, the configuration will be saved as specified. However, later user queries that get routed to the fallback warehouse will fail, with an error indicating that the warehouse does not exist. The same holds true for any warehouse specified in the TARGET_WAREHOUSE column of the lookup table.

CREATE OR REPLACE SUNDECK FLOW "Route tpc queries based on lookup table"
  PRE HOOK route_tpc_queries_hook
  ALWAYS 
  LOOKUP_ROUTING {'lookupTable': 'TPC_DI.SF10.LOOKUP_TABLE', 'fallbackWarehouse': 'SMALL_WH'}
  ENABLED = TRUE;

When you connect to this flow, the queries will be routed to the warehouse specified in the lookup table. Queries that do not find a match will be routed to the fallback warehouse.

See also:

QUERY_SIGNATURE