Query-Time Dimension Joins

Query-time joins or more simply, lookup dimensions, provide a powerful way to incorporate reference tables into your dashboard without requiring upstream SQL transformations. This feature enables data enrichment with reference information at query time, eliminating the need for complex ETL processes.

note

The lookup function is dependent on the type of OLAP engine that you are using for your project.

ClickHouse Lookups

ClickHouse provides powerful dictionary functions for lookup operations through dictGet functions. The table needs to be defined as a dictionary.

Using expression

You can use dictGet directly in a dimension's expression:

dimensions:
  - name: user_email
    display_name: "User Email"
    # Query-time lookup in the 'users' dictionary
    expression: dictGet('users', 'email', user_id)
    description: "User email from users dictionary"
    
  - name: product_category
    display_name: "Product Category" 
    expression: dictGet('products', 'category', product_id)
    description: "Product category information"
    
  # With default fallback
  - name: user_email_safe
    display_name: "User Email (Safe)"
    expression: dictGetOrDefault('users', 'email', user_id, 'unknown@example.com')
    description: "User email with fallback for missing users"
    
  # Multiple attributes from same dictionary
  - name: user_full_name
    display_name: "User Full Name"
    expression: concat(dictGet('users', 'first_name', user_id), ' ', dictGet('users', 'last_name', user_id))
    description: "Concatenated full name from user dictionary"

This approach is straightforward but has a performance drawback: when filtering by a lookup dimension, ClickHouse must evaluate dictGet for every row before applying the filter, which prevents it from using indexes on the key column.

Using lookup_* properties

The lookup_* properties provide a declarative way to define lookup dimensions. Rill automatically rewrites filter queries to use an optimized subquery pattern, enabling ClickHouse to leverage indexes on key columns.

dimensions:
  - name: country_name
    display_name: "Country"
    # column in the fact table to match with the dictionary key
    column: country_code
    lookup_table: country_dict
    lookup_key_column: code
    lookup_value_column: name

  # With a default value for missing keys
  - name: region
    display_name: "Region"
    # column in the fact table to match with the dictionary key
    column: country_code
    lookup_table: country_dict
    lookup_key_column: code
    lookup_value_column: region
    lookup_default_expression: "'Unknown'"

The properties are:

• lookup_table — the dictionary name. Use database.dictionary_name if the dictionary is in a non-default database (e.g. mydb.my_dict).
• lookup_key_column — the primary key column in the dictionary that matches the fact table's column.
• lookup_value_column — the attribute column in the dictionary to display.
• lookup_default_expression (optional) — a SQL expression used as a fallback when no match is found (maps to dictGetOrDefault).

How the query optimization works

When you filter on a lookup dimension defined with expression: dictGet(...), the generated query looks like:

SELECT ... FROM fact_table
WHERE dictGet('dict', 'name', publisher_id) IN ('Alice', 'Bob')

ClickHouse must call dictGet on every row to evaluate the filter, making it unable to use the index on publisher_id.

With lookup_* properties, Rill rewrites the filter to a subquery pattern:

SELECT ... FROM fact_table
WHERE publisher_id IN (
  SELECT code FROM dict WHERE name IN ('Alice', 'Bob')
)

This allows ClickHouse to first resolve the small set of matching keys from the dictionary, then use the index on publisher_id to efficiently scan only the relevant rows in the fact table.

Keys missing from the dictionary

Keys in the fact table that are missing from the dictionary are not covered by the subquery. For filters to return correct results for these rows, Rill needs to know what value they resolve to. So when using the lookup_* properties, either:

• Make the lookup_value_column attribute Nullable in the dictionary definition, without a DEFAULT. Missing keys then resolve to NULL.
• Set lookup_default_expression. Missing keys then resolve to that expression, whatever the dictionary's own default is.

CREATE DICTIONARY country_dict (
  code String,
  name Nullable(String)
)
PRIMARY KEY code
...

warning

If the attribute is not Nullable and no lookup_default_expression is set, missing keys resolve to the attribute's default value, such as an empty string or 0. Filters that select that value don't match rows with missing keys. Dictionaries that Rill creates from a model with type: DICTIONARY infer the attribute types from the model's query, which are not Nullable unless the query returns nullable columns (for example using toNullable(name)) or you specify columns yourself.

Filters that match the value of missing keys, such as selecting the null value or excluding other values, are applied to the dictGet expression instead of the subquery and read the whole fact table. With a lookup_default_expression, other filters additionally check the default value, so the index optimization is kept whenever the filter does not match the default.

Grouping by the key first with lookup_group_by_key

When you group by a lookup dimension, the generated query calls dictGet on every row before grouping:

SELECT dictGet('dict', 'name', publisher_id) AS publisher, sum(impressions)
FROM fact_table
GROUP BY publisher

With lookup_group_by_key: true, Rill groups by the key first, resolves the name once per key, and regroups by name. The measures are re-aggregated with ClickHouse's -State and -Merge combinators, so the results are identical, including when several keys share the same name:

dimensions:
  - name: publisher
    column: publisher_id
    lookup_table: dict
    lookup_key_column: id
    lookup_value_column: name
    lookup_group_by_key: true

SELECT dictGet('dict', 'name', t.publisher_id) AS publisher, sumMerge(t.impressions) AS impressions
FROM (
  SELECT publisher_id, sumState(impressions) AS impressions
  FROM fact_table
  GROUP BY publisher_id
) t
GROUP BY publisher

Enable it per dimension for keys with few distinct values compared to the number of rows, such as advertisers, campaigns or properties: the fewer keys, the larger the gain. Leave it off for keys with millions of values, such as users or households, where grouping by key first uses more memory and is no faster.

Distinct counts, such as count(DISTINCT user_id) or uniq(user_id), can sometimes get slower. For example, to count distinct users for 3 categories that cover 2,000 publishers, ClickHouse first builds a list of users per publisher and then merges the 2,000 lists into 3, so a user seen on 500 publishers is stored 500 times instead of once. Compare query times with and without lookup_group_by_key on your data before enabling it.

Keep in mind:

• A lookup_default_expression may only reference constants and the key column.
• Any aggregate function works, including combinators (sumIf) and parametric functions (quantile(0.5)(x)). count(DISTINCT x) is evaluated as uniqExact, ClickHouse's default implementation of it, even on servers that override count_distinct_implementation. Measures with a window, and measures whose expression Rill cannot split into aggregate calls, fall back to the single-pass query for that request.
• Approximate aggregates such as uniq or quantileTDigest can return slightly different values, as they already do between runs.

Optimizing GROUP BY with INJECTIVE

If the key-to-value mapping in your dictionary is 1:1 (e.g. every code maps to exactly one name), you can alternatively mark the attribute columns as INJECTIVE in the ClickHouse dictionary definition. This tells ClickHouse that the mapping preserves uniqueness, allowing it to optimize GROUP BY queries by grouping on the key column instead of evaluating dictGet for every group. Unlike lookup_group_by_key, it does not merge keys that share a value, so only use it for dictionaries that are truly 1:1.

Druid Lookups

For those looking to add ID to name mappings with Druid (as an OLAP engine), you can utilize expressions in your Dimension settings. Simply use the lookup function and provide the name of the lookup and ID, i.e. lookup(city_id, 'cities'). Be sure to include the lookup table name in single quotes.

dimensions:
  - name: city_name
    display_name: "Cities"
    expression: lookup(city_id, 'cities')
    description: "City names from lookup table"

DuckDB/MotherDuck Lookups

Unfortunately, DuckDB does not have a dimension lookup function and instead relies on upstream modeling to join the lookup table and create a one big table for dashboarding. Take a look through DuckDB docs for further information!

SELECT 
    o.*,
    u.user_name
FROM orders o
LEFT JOIN users u ON u.email = o.email;

Alternatively, you can use DuckDB's map function to create a lookup table by mapping values from one column to another. This approach creates an in-memory mapping that can be referenced without using a joining SQL:

 - expression: (SELECT map(list(email), list(user_name)) FROM users_dataset)[email]
    name: user_name

Performance Implications

While joins can kill the performance of OLAP engines, lookups (key-value pairs) are common to reduce data size and improve query speeds. Lookups can be done during ingestion time (a static lookup to enrich the source data) or at query time (dynamic lookups).

Static Lookups

Static lookups are lookups that are ingested at processing time. When a record is being processed, if a match is found between the record and lookup's key, the lookup's corresponding value at that moment in time is extracted and carbon-copied into your OLAP engine for the records it processed.

Static lookups are best suited for:

• Dimensions with values that require a historical record for how they have changed over time
• Values that are never expected to change (leverage Dynamic Lookups if the values are expected to change)
• Extremely large lookups (hundreds of thousands of records or >50MB lookup file) to improve query performance

Customers typically store lookup values in S3 or GCS, and the lookup file is then updated by customers as needed and consumed by ETL logic.

Dynamic Lookups

Since static lookups transform and store the data permanently, any changes to the mapping would require reprocessing the entire dataset to ensure consistency. To address the case when values in a lookup are expected to change with time, we developed dynamic lookups. Dynamic lookups, also known as Query Time Lookups, are lookups that are retrieved at query time, as opposed to being used at ingestion time.

Benefits of dynamic lookups include:

• Historical continuity for dimensions that change frequently without reprocessing the entire dataset
• Time savings, because there is no dataset reprocessing required to complete the update
• Dynamic lookups are kept separate from the dataset. Thus, any human errors introduced in the lookup do not impact the underlying dataset
• Ability for users to create new dimension tables from metadata associated with a dimension table. For example, account ownership can change during the course of a quarter. In such cases, a dynamic lookup ensures that the most current ownership information is always available without requiring data reprocessing.