Partitions

Define data availability constraints for query routing.

Overview

Partitions tell the planner about data availability constraints. This helps route queries to the correct tables when multiple tables can answer the same question.

Supported Predicates

The predicates that rank a table are between, greater_than, greater_than_or_equal_to, less_than and less_than_or_equal_to (number or date dimensions) and in_list (any dimension). Any other predicate is accepted with a warning and does not influence routing. Write them under partitions: as a list; the singular partition: key is also accepted. A partition takes dimension, predicate, filter_value and, for between, filter_value_end. A description is accepted and ignored by the loader; it documents the constraint for readers.

between

Date range partition.

partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 24m  # 24 months ago
    filter_value_end: 1d  # yesterday
    description: "Rolling 2 year window"

in_list

List of values partition.

partitions:
  - dimension: Region
    predicate: in_list
    filter_value: us-east, us-west, europe
    description: "Table only has 3 regions of 5"

Date Range Partitions

Most common use case: rolling window tables.

name: Recent Sales
physical_name: recent_sales
datasource: warehouse
cost: 10

partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 24m  # 24 months ago
    filter_value_end: 1d  # yesterday
    description: "Table only contains last 24 months"

Time formats:

  • 24m - 24 months
  • 1y - 1 year
  • 30d - 30 days
  • 1d - 1 day

List Partitions

Partition by specific values.

partitions:
  - dimension: Region
    predicate: in_list
    filter_value: us-east, us-west, europe
    description: "Table only has 3 regions"

Multiple Partitions

Define multiple partition constraints:

partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 12m
    filter_value_end: 1d
    description: "Last 12 months"

  - dimension: Region
    predicate: in_list
    filter_value: us-east, us-west
    description: "US regions only"

How It Works

When a spec arrives:

  1. Planner checks partition constraints
  2. If query matches partition, uses this table
  3. If query doesn’t match, uses alternative table (if available)
  4. Routes query to appropriate table based on constraints

Use Cases

Rolling Window Table

name: Recent Orders
partitions:
  - dimension: Order Date
    predicate: between
    filter_value: 12m
    filter_value_end: 1d
    description: "Last 12 months of data"

Regional Partition

name: US Sales
partitions:
  - dimension: Region
    predicate: in_list
    filter_value: us-east, us-west, us-central
    description: "US regions only"

Best Practices

  1. Document partitions - Add descriptions explaining constraints
  2. Use appropriate dimensions - Name a dimension by name; it does not have to live on this table, but it must exist in the branch
  3. Set appropriate costs - Partitioned tables often have lower cost
  4. Test query routing - Verify planner routes queries correctly

Next Steps