Lookup Routing

The LOOKUP_ROUTING Pre-hook routes queries to warehouses based on a pinned lookup table. Each entry in the lookup table maps a query to the warehouse it should be routed to.

Sundeck intercepts incoming queries and looks for query matches in the lookup table. If a match is found, Sundeck routes the query to the warehouse specified in the table. If no match is found, the query is routed to the configured fallback warehouse.

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

• Whitespaces are ignored
• 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

LOOKUP_ROUTING {
  'lookupTable'  : <table-name>,
  'fallbackWarehouse' : <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

The lookup table is generally created from entries in snowflake’s query_history table and must have the following columns:

• QUERY_TEXT - The query to match incoming queries against
• DATABASE_NAME - The default database for the query
• SCHEMA_NAME - The default schema for the query
• QUERY_SIGNATURE - The Snowflake query signature for the query
• TARGET_WAREHOUSE - The warehouse to route the query to

Snowflake table must be pinned in sundeck before using it in a hook.

If tables in the QUERY_TEXT are not qualified, DATABASE_NAME and SCHEMA_NAME in the pin table are used to qualify them.

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