dc-query-building

name	dc-query-building
description	Build semantic queries with measures, dimensions, filters, and time dimensions for Drizzle Cube.

Query Building Skill

This skill helps you construct CubeQuery objects for querying Drizzle Cube's semantic layer.

CubeQuery Structure

interface CubeQuery {
  measures?: string[]                    // Aggregations
  dimensions?: string[]                  // Groupings
  timeDimensions?: TimeDimension[]       // Time-based analysis
  filters?: Filter[]                     // Data filtering
  order?: Record<string, 'asc' | 'desc'> // Sorting
  limit?: number                         // Row limit
  offset?: number                        // Pagination offset
  fillMissingDatesValue?: number | null  // Fill gaps in time series
}

Field Naming Convention

All fields use CubeName.fieldName format:

// Measures
'Sales.totalRevenue'
'Orders.count'
'Employees.avgSalary'

// Dimensions
'Products.category'
'Customers.region'
'Employees.department'

// Time Dimensions
'Orders.createdAt'
'Events.timestamp'

Basic Query Examples

Count Query

{
  measures: ['Employees.count']
}

Count with Dimension

{
  measures: ['Employees.count'],
  dimensions: ['Employees.department']
}

Multiple Measures

{
  measures: [
    'Sales.totalRevenue',
    'Sales.avgOrderValue',
    'Orders.count'
  ],
  dimensions: ['Products.category']
}

With Ordering and Limit

{
  measures: ['Sales.totalRevenue'],
  dimensions: ['Customers.name'],
  order: {
    'Sales.totalRevenue': 'desc'
  },
  limit: 10
}

Time Dimensions

IMPORTANT: timeDimensions groups results BY time (one row per period). If you only want to filter by a date range WITHOUT grouping by time, use filters with inDateRange instead. See Time Filtering vs Time Grouping below.

Basic Time Query

{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'month'
  }]
}

With Date Range

{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'day',
    dateRange: ['2024-01-01', '2024-03-31']
  }]
}

Predefined Date Ranges

// Last 7 days
dateRange: 'last 7 days'

// This month
dateRange: 'this month'

// Last quarter
dateRange: 'last quarter'

// Year to date
dateRange: 'from 1 year ago to now'

Granularity Options

Granularity	Description
hour	Hourly aggregation
day	Daily aggregation
week	Weekly aggregation
month	Monthly aggregation
quarter	Quarterly aggregation
year	Yearly aggregation

Fill Missing Dates

{
  measures: ['Orders.count'],
  timeDimensions: [{
    dimension: 'Orders.createdAt',
    granularity: 'day',
    dateRange: ['2024-01-01', '2024-01-31'],
    fillMissingDates: true
  }],
  fillMissingDatesValue: 0  // Fill gaps with 0
}

Period Comparison

{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Sales.createdAt',
    granularity: 'month',
    dateRange: ['2024-01-01', '2024-03-31'],
    compareDateRange: [
      ['2023-01-01', '2023-03-31']  // Compare to previous year
    ]
  }]
}

Time Filtering vs Time Grouping

This is a common source of errors. Choose the right approach:

Goal	Method	Example
Break down by time periods	timeDimensions with granularity	"Revenue by month"
Aggregate within a time range	filters with inDateRange	"Total revenue for last quarter"
Top N over a time period	filters with inDateRange	"Top 5 employees over past 3 months"

Using timeDimensions (groups by time):

// Returns ONE ROW PER MONTH
{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Sales.createdAt',
    granularity: 'month',
    dateRange: 'last quarter'
  }]
}

Using filters (only constrains, no grouping):

// Returns SINGLE AGGREGATED ROW (or rows by other dimensions)
{
  measures: ['Sales.totalRevenue'],
  filters: [
    { member: 'Sales.createdAt', operator: 'inDateRange', values: ['last quarter'] }
  ]
}

Predefined date ranges for filters: 'last 7 days', 'last month', 'last 3 months', 'last quarter', 'this year', 'from 1 year ago to now'

Filters

Filter Structure

interface Filter {
  member: string              // Field name
  operator: FilterOperator    // Comparison operator
  values: any[]               // Values to compare
  dateRange?: string | [string, string]  // For date operators
}

Filter Operators

Equality:

{ member: 'Products.status', operator: 'equals', values: ['active'] }
{ member: 'Products.status', operator: 'notEquals', values: ['deleted'] }

String Matching:

{ member: 'Products.name', operator: 'contains', values: ['Premium'] }
{ member: 'Products.name', operator: 'notContains', values: ['Test'] }
{ member: 'Products.name', operator: 'startsWith', values: ['Pro'] }
{ member: 'Products.name', operator: 'endsWith', values: ['Edition'] }

Numeric Comparison:

{ member: 'Orders.amount', operator: 'gt', values: [100] }
{ member: 'Orders.amount', operator: 'gte', values: [100] }
{ member: 'Orders.amount', operator: 'lt', values: [1000] }
{ member: 'Orders.amount', operator: 'lte', values: [1000] }
{ member: 'Orders.amount', operator: 'between', values: [100, 500] }

Array Membership:

{ member: 'Products.category', operator: 'in', values: ['Electronics', 'Clothing'] }
{ member: 'Products.category', operator: 'notIn', values: ['Archived', 'Draft'] }

Null Checks:

{ member: 'Customers.email', operator: 'set', values: [] }      // NOT NULL
{ member: 'Customers.email', operator: 'notSet', values: [] }   // IS NULL

Date Operators:

{ member: 'Orders.createdAt', operator: 'inDateRange', values: ['2024-01-01', '2024-12-31'] }
{ member: 'Orders.createdAt', operator: 'beforeDate', values: ['2024-01-01'] }
{ member: 'Orders.createdAt', operator: 'afterDate', values: ['2024-06-01'] }

Grouped Filters (AND/OR)

AND Logic:

filters: [{
  type: 'and',
  filters: [
    { member: 'Products.isActive', operator: 'equals', values: [true] },
    { member: 'Products.stock', operator: 'gt', values: [0] }
  ]
}]

OR Logic:

filters: [{
  type: 'or',
  filters: [
    { member: 'Orders.status', operator: 'equals', values: ['pending'] },
    { member: 'Orders.status', operator: 'equals', values: ['processing'] }
  ]
}]

Complex Nested Logic:

// (isActive = true) AND (category = 'A' OR category = 'B')
filters: [{
  type: 'and',
  filters: [
    { member: 'Products.isActive', operator: 'equals', values: [true] },
    {
      type: 'or',
      filters: [
        { member: 'Products.category', operator: 'equals', values: ['A'] },
        { member: 'Products.category', operator: 'equals', values: ['B'] }
      ]
    }
  ]
}]

MultiQueryConfig

Combine multiple queries with merge strategies:

interface MultiQueryConfig {
  queries: CubeQuery[]
  mergeStrategy: 'concat' | 'merge'
  mergeKeys?: string[]       // For 'merge' strategy
  queryLabels?: string[]     // Optional labels
}

Concat Strategy

Appends results with __queryIndex marker:

{
  queries: [
    { measures: ['Sales.revenue'], dimensions: ['Products.category'] },
    { measures: ['Returns.total'], dimensions: ['Products.category'] }
  ],
  mergeStrategy: 'concat',
  queryLabels: ['Sales', 'Returns']
}

Merge Strategy

Aligns results by common dimension:

{
  queries: [
    { measures: ['Sales.revenue'], dimensions: ['Products.category'] },
    { measures: ['Returns.total'], dimensions: ['Products.category'] }
  ],
  mergeStrategy: 'merge',
  mergeKeys: ['Products.category']
}

Query Patterns

Top N Analysis

{
  measures: ['Sales.totalRevenue'],
  dimensions: ['Products.name'],
  order: { 'Sales.totalRevenue': 'desc' },
  limit: 10
}

Year-over-Year Comparison

{
  measures: ['Sales.totalRevenue'],
  timeDimensions: [{
    dimension: 'Sales.createdAt',
    granularity: 'month',
    dateRange: ['2024-01-01', '2024-12-31'],
    compareDateRange: [
      ['2023-01-01', '2023-12-31']
    ]
  }]
}

Cohort Analysis

{
  measures: ['Users.count'],
  dimensions: ['Users.signupMonth', 'Users.activityMonth'],
  filters: [
    { member: 'Users.signupMonth', operator: 'gte', values: ['2024-01'] }
  ]
}

Funnel Query

{
  funnel: {
    bindingKey: 'Events.userId',
    timeDimension: 'Events.timestamp',
    steps: [
      { name: 'View', cube: 'Events', filter: { member: 'Events.type', operator: 'equals', values: ['pageview'] } },
      { name: 'Cart', cube: 'Events', filter: { member: 'Events.type', operator: 'equals', values: ['add_to_cart'] } },
      { name: 'Purchase', cube: 'Events', filter: { member: 'Events.type', operator: 'equals', values: ['purchase'] } }
    ],
    includeTimeMetrics: true
  }
}

Flow Query

{
  flow: {
    bindingKey: 'Events.userId',
    timeDimension: 'Events.timestamp',
    eventDimension: 'Events.eventType',
    startingStep: {
      name: 'Purchase',
      filter: { member: 'Events.type', operator: 'equals', values: ['purchase'] }
    },
    stepsBefore: 3,
    stepsAfter: 3,
    joinStrategy: 'auto'
  }
}

Query Validation

Before executing, validate your query:

1. Field existence - All measures/dimensions must exist in registered cubes
2. Cube consistency - Multi-cube queries need proper joins defined
3. Filter validity - Filter operators must match field types
4. Date format - Use ISO 8601 format (YYYY-MM-DD)

API Execution

// Using CubeClient directly
const result = await cubeClient.load(query)
const data = result.rawData()

// Using React hook
const { rawData, isLoading, error } = useCubeLoadQuery(query)

// SQL preview (dry run)
const { sql } = await cubeClient.sql(query)

Debugging Queries

Dry Run

Get SQL without executing:

const result = await cubeClient.sql(query)
console.log(result.sql)

Explain

Get query execution plan:

// POST /cubejs-api/v1/explain
const explain = await fetch('/cubejs-api/v1/explain', {
  method: 'POST',
  body: JSON.stringify({ query })
})

Common Mistakes

1. Wrong field format - Use Cube.field, not just field
2. Using timeDimensions when you want filters - timeDimensions groups by time (one row per period). Use filters with inDateRange to constrain a date range without grouping. See Time Filtering vs Time Grouping.
3. Filter value type - values must be an array, even for single values
4. Limit without order - Add order when using limit for consistent results
5. Mixing time dimensions - Use same granularity when combining time series