Order of Filtering

​

Order of Filtering and Computation

Every query on Honeydew computes needed metrics and applies user filters based on context.

The context of a query includes the domain it operates on the source it came from (SQL interface or a Dynamic Dataset).

The logical order of applying filters:

1. Source data: as defined in source tables, including any custom SQL filters
2. Domain source filters that filter out data from specific source tables, before any computation
3. Entities and their calculated attributes: including calculated attributes that use filtered metrics that add their own filter
4. Domain semantic filters that control accessible data across the semantic layer
5. User query filters:
   • When using the SQL interface, filters in the SQL (WHERE and HAVING)
   • When using Dynamic Datasets, filters in the Dynamic Dataset definition
6. Metrics, including filters of filtered metrics in use

​

Example

Consider:

1. A semantic schema with lineitem, orders and customers entities
2. A domain set up with:

• Source filters orders.order_date >= '1990-01-01' and lineitem.status = 'F'
• Semantic filter customers.c_nationkey = 10

3. A user that asks for orders.count with a query filter of orders.order_date >= '1996-01-01'

The following filters will apply logically:

1. Select from orders with source filter orders.order_date >= '1990-01-01'
2. Select from customers
3. Apply semantic filter customers.c_nationkey = 10:
   • Filter the customers entity and join it to orders
   • Note that orders gets the filter through its INNER join to filtered customers
4. Apply query filter orders.order_date >= '1996-01-01'
5. Calculate orders.count on the resulting filtered data

Note that while logically this is the order, physically the filter in step (5) orders.order_date >= '1996-01-01' will be actually executed at step (1), when selecting from orders.

The reason is that it can be safely pushed down earlier in the query execution without affecting results.