From drizzle-cube
Build semantic queries with measures, dimensions, filters, and time dimensions for Drizzle Cube.
npx claudepluginhub cliftonc/drizzle-cube-plugin --plugin drizzle-cubeThis skill uses the workspace's default tool permissions.
This skill helps you construct CubeQuery objects for querying Drizzle Cube's semantic layer.
Answers business questions on analytics, metrics, KPIs by generating/executing SQL via dbt Semantic Layer, compiled SQL mods, or model analysis against data warehouse.
Guides step-by-step through defining business metrics as SQL aggregations on Honeydew entities and pushing via MCP tools.
Generates optimized SQL queries from natural language descriptions. Supports BigQuery, PostgreSQL, MySQL, Snowflake; reads schemas from files or docs. For data exploration, reports, and business analysis.
Share bugs, ideas, or general feedback.
This skill helps you construct CubeQuery objects for querying Drizzle Cube's semantic layer.
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
}
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'
{
measures: ['Employees.count']
}
{
measures: ['Employees.count'],
dimensions: ['Employees.department']
}
{
measures: [
'Sales.totalRevenue',
'Sales.avgOrderValue',
'Orders.count'
],
dimensions: ['Products.category']
}
{
measures: ['Sales.totalRevenue'],
dimensions: ['Customers.name'],
order: {
'Sales.totalRevenue': 'desc'
},
limit: 10
}
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.
{
measures: ['Sales.totalRevenue'],
timeDimensions: [{
dimension: 'Orders.createdAt',
granularity: 'month'
}]
}
{
measures: ['Sales.totalRevenue'],
timeDimensions: [{
dimension: 'Orders.createdAt',
granularity: 'day',
dateRange: ['2024-01-01', '2024-03-31']
}]
}
// 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 | Description |
|---|---|
hour | Hourly aggregation |
day | Daily aggregation |
week | Weekly aggregation |
month | Monthly aggregation |
quarter | Quarterly aggregation |
year | Yearly aggregation |
{
measures: ['Orders.count'],
timeDimensions: [{
dimension: 'Orders.createdAt',
granularity: 'day',
dateRange: ['2024-01-01', '2024-01-31'],
fillMissingDates: true
}],
fillMissingDatesValue: 0 // Fill gaps with 0
}
{
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
]
}]
}
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'
interface Filter {
member: string // Field name
operator: FilterOperator // Comparison operator
values: any[] // Values to compare
dateRange?: string | [string, string] // For date 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'] }
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'] }
]
}
]
}]
Combine multiple queries with merge strategies:
interface MultiQueryConfig {
queries: CubeQuery[]
mergeStrategy: 'concat' | 'merge'
mergeKeys?: string[] // For 'merge' strategy
queryLabels?: string[] // Optional labels
}
Appends results with __queryIndex marker:
{
queries: [
{ measures: ['Sales.revenue'], dimensions: ['Products.category'] },
{ measures: ['Returns.total'], dimensions: ['Products.category'] }
],
mergeStrategy: 'concat',
queryLabels: ['Sales', 'Returns']
}
Aligns results by common dimension:
{
queries: [
{ measures: ['Sales.revenue'], dimensions: ['Products.category'] },
{ measures: ['Returns.total'], dimensions: ['Products.category'] }
],
mergeStrategy: 'merge',
mergeKeys: ['Products.category']
}
{
measures: ['Sales.totalRevenue'],
dimensions: ['Products.name'],
order: { 'Sales.totalRevenue': 'desc' },
limit: 10
}
{
measures: ['Sales.totalRevenue'],
timeDimensions: [{
dimension: 'Sales.createdAt',
granularity: 'month',
dateRange: ['2024-01-01', '2024-12-31'],
compareDateRange: [
['2023-01-01', '2023-12-31']
]
}]
}
{
measures: ['Users.count'],
dimensions: ['Users.signupMonth', 'Users.activityMonth'],
filters: [
{ member: 'Users.signupMonth', operator: 'gte', values: ['2024-01'] }
]
}
{
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: {
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'
}
}
Before executing, validate your query:
// 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)
Get SQL without executing:
const result = await cubeClient.sql(query)
console.log(result.sql)
Get query execution plan:
// POST /cubejs-api/v1/explain
const explain = await fetch('/cubejs-api/v1/explain', {
method: 'POST',
body: JSON.stringify({ query })
})
Cube.field, not just fieldtimeDimensions groups by time (one row per period). Use filters with inDateRange to constrain a date range without grouping. See Time Filtering vs Time Grouping.values must be an array, even for single valuesorder when using limit for consistent results