Optimizing Complex Searches
Complex EQL queries, especially those involving multiple relationship checks, can sometimes be slow or time out. Computed properties can help by pre-calculating parts of these complex conditions, making the final query much faster.
A Real-World Example
Let's look at a common use case: finding all dashboards that depend on table defined by a dby model but don't depend on any raw tables not defined by dbt.
The Direct Approach
You might try to write this query directly:
type ~ 'dashboard' AND HAS upstream(type="table" and defined_by='dbt') AND NOT HAS upstream(type = 'table' and (defined_by is null or defined_by != "dbt"))
This query needs to:
Find all dashboards
For each dashboard, check all upstream dependencies for dbt models
For each dashboard, check all upstream dependencies for tables
Combine these conditions
With a large data model, this query can easily time out (default timeout is 10 seconds) because it needs to traverse the entire dependency graph multiple times.
The Optimized Approach
Instead, we can break this into two parts:
First, create a computed boolean property that pre-calculates the expensive part of the query
Then use this property in a simpler query
Step 1: Create the Computed Property
Create a new computed boolean property:
Name:
has_table_in_upstream
Description: "Indicates whether this resource has any raw tables in its upstream dependencies"
Type: Computed Boolean
Scope:
type ~ 'dashboard'
Condition:
HAS upstream(type = 'table')
[Screenshot: Creating the computed property]
This property will be calculated once for each dashboard and stored, rather than being computed every time you run a query.
Step 2: Use the Property
Now you can write a much simpler and faster query:
type ~ 'dashboard' AND HAS upstream(type = 'dbt_model') AND has_table_in_upstream IS FALSE
This query is faster because:
The expensive
HAS upstream(type = 'table')
check is pre-computedThe query only needs to check for dbt model dependencies
The boolean property check is a simple lookup
Best Practices for Query Optimization
Identify Expensive Parts
Relationship checks (
HAS upstream
,HAS downstream
) are expensiveMultiple relationship checks compound the performance impact
Negations of relationship checks (
NOT HAS
) are particularly expensive
Create Focused Properties
Use the
scope
field to limit where the property is calculatedThis reduces computation time and storage needs
Makes the property's purpose clear to other users
Document Your Properties
Add clear descriptions explaining what the property represents
Include example queries in the description
Note any assumptions or limitations
Monitor and Maintain
Check if the property is still being used
Review if the property needs to be recalculated when data changes
Consider cleaning up unused properties
When to Use This Pattern
Consider creating a computed property for optimization when:
Your queries frequently time out
You're using the same complex conditions in multiple queries
You need to negate relationship checks (
NOT HAS
)You're combining multiple relationship checks in one query
Example Use Cases
Data Quality Monitoring
# Pre-compute name: has_quality_issues scope: type = 'dbt_model' condition: HAS downstream(type = 'quality_check' AND status = 'failed')
Dependency Analysis
# Pre-compute name: is_isolated_dashboard scope: type = 'dashboard' condition: NOT HAS upstream(TRUE)
Usage Tracking
# Pre-compute name: has_recent_usage scope: type IN ('dashboard', 'look') condition: total_views_30d > 0
Last updated