Skip to main content

SQL Optimisation

Background​

Snowflake is a data warehouse that differs from traditional RDMBS or NoSQL DBs.

It does not support any indexing, but however tackles the problem with columnar micro-partitioning.

Snowflake stores the data in multiple micro-partitions, and computes a set of metadata for each micro-partition.

In order to take advantage of the micro partitioning mechanism whilst keeping identifier data protected, some tweaks to the access pattern is required to access the data in an optimal manner.

warning

*Optimisation only applies to identified-identifiers datafields.

*Non-identifiers are optimised by default

*Optimisation can only be applied for simple queries, and may default to full table scan for complex queries.

Complex queries are defined as queries that rely on runtime values, resolved on query compile time.

In the context of DataHive, this refers to usage of all identified-identifiers (detokenised on runtime).

Some more examples of complex queries includes (but not limited to) statements with:

  1. sub-queries or CTE (Common Table Expressions)
  2. non-static values in query

How to optimise queries on identified identifiers​

  1. encode filter (values) into the tokenised format using the provided table specific UDF
    • UDFs are named: DATA_REQUESTS.SINGLETON.<table>_LOCK, where <table> refers to the table name of the datafield to be tokenised
  2. filter using the tokenised value from (1), against the token column, and project the detokenised column
    • tokenised column are prefixed with _LOCK_, e.g. filter with column _LOCK_UIN on tokenised values, but project corresponds to UIN

Latency​

With optimisation, P95 latency for single record retrieval is under 2 seconds (subject to the status of the warehouse queue).

If there are requirements for latency, please write in to admin@datahive.gov.sg.

note

Aggregations and joins can run with optimisations, but the P95 latency only applies to single record retrievals for a dedicated warehouse.