or run

npx @tessl/cli init

Metric Queries

Core query structure for fetching data from explores.

MetricQuery Type

The core query type in Lightdash for defining what data to fetch from an explore.

type MetricQuery = {
  exploreName: string;                    // Must match an explore defined in your dbt project
  dimensions: FieldId[];                  // Use underscore format: "tableName_fieldName"
  metrics: FieldId[];                     // Use underscore format: "tableName_metricName"
  filters: Filters;                       // Required property
  sorts: SortField[];                     // Required property (can be empty array)
  limit: number;                          // Required. Use 500/5000 for "unlimited"
  tableCalculations: TableCalculation[];  // Required (can be empty array)
  additionalMetrics?: AdditionalMetric[]; // Compiled and included in query execution
  customDimensions?: CustomDimension[];   // Bins, custom SQL dimensions
  metricOverrides?: Record<string, Pick<Metric, 'formatOptions'>>;
  timezone?: string;                      // e.g., "America/New_York", "UTC"
  parameters?: Record<string, unknown>;   // SQL parameter values
  metadata?: {
    hasADateDimension: Pick<CompiledDimension, 'label' | 'name' | 'table'>;
  };
  periodOverPeriod?: PeriodOverPeriodComparison;
};

Properties

exploreName: Name of the explore (data model) to query (e.g., "customers", "orders"). Must match an explore defined in your dbt project.
dimensions: Array of dimension field IDs to group by. Field IDs use underscore format: "tableName_fieldName" (e.g., ["customers_customer_id", "customers_name"])
metrics: Array of metric field IDs to aggregate. Field IDs use underscore format: "tableName_metricName" (e.g., ["customers_total_orders", "customers_revenue"])
filters: Filter configuration for dimensions, metrics, and table calculations. Required property containing nested filter groups with AND/OR logic.
sorts: Array of SortField configurations specifying sort order. Required property (can be empty array for no sorting).
limit: Maximum number of rows to return from the query. Required property. Use a large number like 500 or 5000 for "unlimited" results.
tableCalculations: Array of TableCalculation definitions for post-query calculations. Required property (can be empty array if no calculations needed).
additionalMetrics: Optional array of AdditionalMetric definitions for ad-hoc metrics created at query time without modifying the explore. These metrics are compiled and included in the query execution.
customDimensions: Optional array of CustomDimension definitions including binned dimensions and custom SQL dimensions created at query time.
metricOverrides: Optional record mapping metric field IDs to format option overrides. Allows temporarily changing metric formatting without modifying the explore definition. Type: Record<string, Pick<Metric, 'formatOptions'>>
timezone: Optional timezone string for date/time operations (e.g., "America/New_York", "UTC"). Used to convert timestamps to the specified local timezone.
parameters: Optional record of SQL parameter values for parameterized queries.
metadata: Optional metadata object containing query information. Currently supports hasADateDimension property with the date dimension's label, name, and table.
periodOverPeriod: Optional period-over-period comparison configuration for time-based analysis comparing current period to previous period.

Building Queries

Basic Query

const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders.order_date', 'orders.status'],
  metrics: ['orders.count', 'orders.total_amount'],
  filters: { dimensions: { id: 'root', and: [] } },
  sorts: [{ fieldId: 'orders.order_date', descending: false }],
  limit: 1000,
  tableCalculations: [],
};

With Filters

const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders.status'],
  metrics: ['orders.count'],
  filters: {
    dimensions: {
      id: 'root',
      and: [
        {
          id: 'date',
          target: { fieldId: 'orders.created_at' },
          operator: FilterOperator.IN_THE_PAST,
          values: [30],
          settings: { unitOfTime: UnitOfTime.DAYS },
        },
      ],
    },
  },
  sorts: [{ fieldId: 'orders.count', descending: true }],
  limit: 100,
  tableCalculations: [],
};

With Additional Metrics

const avgValue: AdditionalMetric = {
  label: 'Average Order Value',
  type: MetricType.AVERAGE,
  table: 'orders',
  baseDimensionName: 'total_amount',
  formatOptions: { type: CustomFormatType.CURRENCY, currency: 'USD', round: 2 },
};

const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders.customer_id'],
  metrics: ['orders.count'],
  filters: { dimensions: { id: 'root', and: [] } },
  additionalMetrics: [avgValue],
  sorts: [{ fieldId: 'orders.count', descending: true }],
  limit: 50,
  tableCalculations: [],
};

With Custom Dimensions (Bins)

const bins: CustomBinDimension = {
  id: 'amount_bins',
  name: 'amount_bins',
  type: CustomDimensionType.BIN,
  table: 'orders',
  dimensionId: 'orders.total_amount',
  binType: BinType.CUSTOM_RANGE,
  customRange: [
    { from: 0, to: 50, label: 'Small' },
    { from: 50, to: 200, label: 'Medium' },
    { from: 200, to: Infinity, label: 'Large' },
  ],
};

const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['amount_bins'],
  metrics: ['orders.count'],
  filters: { dimensions: { id: 'root', and: [] } },
  customDimensions: [bins],
  sorts: [{ fieldId: 'amount_bins', descending: false }],
  limit: 100,
  tableCalculations: [],
};

SortField

interface SortField {
  fieldId: string;      // Field to sort by
  descending: boolean;  // Sort direction
  nullsFirst?: boolean; // Null placement
}

AdditionalMetric

Ad-hoc metrics created at query time.

interface AdditionalMetric {
  name?: string;                  // Auto-generated if not provided
  label?: string;                 // Display label
  type: MetricType;               // COUNT, SUM, AVERAGE, etc.
  sql?: string;                   // Custom SQL (for custom metrics)
  table: string;                  // Table name
  baseDimensionName?: string;     // Base dimension (for COUNT_DISTINCT, etc.)
  percentile?: number;            // Percentile value (for PERCENTILE)
  formatOptions?: CustomFormat;   // Formatting
  /** @deprecated Use formatOptions instead */
  compact?: CompactOrAlias;
  /** @deprecated Use formatOptions instead */
  format?: Format | string;
  /** @deprecated Use formatOptions instead */
  round?: number;
  filters?: MetricFilterRule[];   // Metric-specific filters
  description?: string;
}

Examples

// Count distinct
const uniqueCustomers: AdditionalMetric = {
  label: 'Unique Customers',
  type: MetricType.COUNT_DISTINCT,
  table: 'orders',
  baseDimensionName: 'customer_id',
};

// Custom SQL
const avgOrderValue: AdditionalMetric = {
  label: 'Average Order Value',
  type: MetricType.NUMBER,
  sql: 'SUM(${orders.total_amount}) / COUNT(DISTINCT ${orders.order_id})',
  table: 'orders',
  formatOptions: { type: CustomFormatType.CURRENCY, currency: 'USD', round: 2 },
};

// With filter
const highValueOrders: AdditionalMetric = {
  label: 'High Value Orders',
  type: MetricType.COUNT_DISTINCT,
  table: 'orders',
  baseDimensionName: 'order_id',
  filters: [{
    id: 'high_value',
    target: { fieldRef: 'orders.total_amount' },
    operator: FilterOperator.GREATER_THAN,
    values: [1000],
  }],
};

MetricQueryResponse

Response from query execution.

interface MetricQueryResponse {
  rows: Record<string, unknown>[];
  metricQuery: MetricQuery;
  fields: Record<string, Field | TableCalculation | AdditionalMetric | CustomDimension>;
  cacheMetadata?: CacheMetadata;
}

Period-Over-Period Comparison

Compare current period to previous periods.

interface PeriodOverPeriodComparison {
  type: 'previousPeriod';
  granularity: TimeFrames;  // DAY, WEEK, MONTH, QUARTER, YEAR
  periodOffset?: number;    // Offset (default: 1)
  field: { name: string; table: string };
}

const POP_PREVIOUS_PERIOD_SUFFIX = '_previous';

function getPopFieldId(baseFieldId: string): string;
function getBaseFieldIdFromPop(fieldId: string): string | null;
function isSupportedPeriodOverPeriodGranularity(granularity: TimeFrames): boolean;

Example

const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders_created_at'],
  metrics: ['orders_total_revenue'],
  filters: { dimensions: { id: 'root', and: [] } },
  sorts: [{ fieldId: 'orders_created_at', descending: false }],
  limit: 100,
  tableCalculations: [],
  periodOverPeriod: {
    type: 'previousPeriod',
    granularity: TimeFrames.MONTH,
    periodOffset: 1,
    field: { name: 'created_at', table: 'orders' }
  }
};
// Result columns: orders_total_revenue, orders_total_revenue_previous

Utility Functions

isAdditionalMetric

Type guard to check if a value is an AdditionalMetric.

function isAdditionalMetric(value: any): value is AdditionalMetric;

Example:

import { isAdditionalMetric } from '@lightdash/common';

if (isAdditionalMetric(item)) {
  console.log(`Ad-hoc metric: ${item.label}`);
}

hasFormatOptions

Checks if a metric has custom format options.

function hasFormatOptions(
  metric: Metric | AdditionalMetric
): boolean;

Example:

import { hasFormatOptions } from '@lightdash/common';

if (hasFormatOptions(metric)) {
  // Apply custom formatting
  const formatted = applyCustomFormat(metric, value, metric.formatOptions);
}

getCustomMetricDimensionId

Gets the dimension ID for a custom metric (used for metrics that aggregate a dimension).

function getCustomMetricDimensionId(
  additionalMetric: AdditionalMetric
): string;

Returns a field ID like "orders.customer_id" for the dimension being aggregated.

Example:

import { getCustomMetricDimensionId } from '@lightdash/common';

const dimensionId = getCustomMetricDimensionId(additionalMetric);
// For COUNT DISTINCT on orders.customer_id, returns "orders.customer_id"

getAdditionalMetricLabel

Gets the display label for an additional metric.

function getAdditionalMetricLabel(
  additionalMetric: AdditionalMetric
): string;

Returns the metric's label, or generates one from the metric configuration if no label is provided.

Example:

import { getAdditionalMetricLabel } from '@lightdash/common';

const label = getAdditionalMetricLabel(additionalMetric);
// Returns: "Unique Customers" or "Count distinct of Customer ID"

countCustomDimensionsInMetricQuery

Counts the number of custom dimensions in a metric query.

function countCustomDimensionsInMetricQuery(
  metricQuery: MetricQuery
): {
  numFixedWidthBinCustomDimensions: number;
  numFixedBinsBinCustomDimensions: number;
  numCustomRangeBinCustomDimensions: number;
  numCustomSqlDimensions: number;
};

Example:

import { countCustomDimensionsInMetricQuery } from '@lightdash/common';

const counts = countCustomDimensionsInMetricQuery(metricQuery);
console.log(`Fixed width bins: ${counts.numFixedWidthBinCustomDimensions}`);
console.log(`Fixed bins: ${counts.numFixedBinsBinCustomDimensions}`);
console.log(`Custom range bins: ${counts.numCustomRangeBinCustomDimensions}`);
console.log(`Custom SQL dimensions: ${counts.numCustomSqlDimensions}`);

hasCustomBinDimension

Checks if a metric query contains any custom bin dimensions.

function hasCustomBinDimension(
  metricQuery?: MetricQuery
): boolean;

Example:

import { hasCustomBinDimension } from '@lightdash/common';

if (hasCustomBinDimension(metricQuery)) {
  // Special handling for binned data
}

Working with Query Results

import { formatItemValue, getItemMap, type MetricQueryResponse } from '@lightdash/common';

function displayResults(response: MetricQueryResponse, explore: Explore) {
  const itemsMap = getItemMap(
    explore,
    response.metricQuery.additionalMetrics,
    response.metricQuery.tableCalculations,
    response.metricQuery.customDimensions
  );

  response.rows.forEach(row => {
    Object.entries(row).forEach(([fieldId, value]) => {
      const formatted = formatItemValue(itemsMap[fieldId], value);
      console.log(`${fieldId}: ${formatted}`);
    });
  });
}

Common Patterns

Query Builder with Validation

import { type MetricQuery, getItemMap, itemsInMetricQuery } from '@lightdash/common';

function buildValidatedQuery(
  exploreName: string,
  dimensions: string[],
  metrics: string[],
  explore: Explore
): MetricQuery | Error {
  // Build query
  const query: MetricQuery = {
    exploreName,
    dimensions,
    metrics,
    filters: { dimensions: { id: 'root', and: [] } },
    sorts: [],
    limit: 500,
    tableCalculations: [],
  };

  // Validate all fields exist
  const itemsMap = getItemMap(explore);
  const allFields = itemsInMetricQuery(query);
  const missing = allFields.filter(id => !itemsMap[id]);

  if (missing.length > 0) {
    return new Error(`Missing fields: ${missing.join(', ')}`);
  }

  return query;
}

Dynamic Filter Construction

import { FilterOperator, UnitOfTime, type FilterGroup } from '@lightdash/common';

function buildDateRangeFilter(
  fieldId: string,
  days: number,
  includeStatus?: string[]
): FilterGroup {
  const rules: (FilterRule | FilterGroup)[] = [
    {
      id: 'date_range',
      target: { fieldId },
      operator: FilterOperator.IN_THE_PAST,
      values: [days],
      settings: { unitOfTime: UnitOfTime.DAYS, completed: true },
    },
  ];

  // Add optional status filter
  if (includeStatus && includeStatus.length > 0) {
    rules.push({
      id: 'status_group',
      or: includeStatus.map((status, idx) => ({
        id: `status_${idx}`,
        target: { fieldId: 'orders.status' },
        operator: FilterOperator.EQUALS,
        values: [status],
      })),
    });
  }

  return { id: 'root', and: rules };
}

// Usage
const filters = buildDateRangeFilter('orders.created_at', 30, ['completed', 'shipped']);

Creating Ad-Hoc Metrics

import { type AdditionalMetric, MetricType, FilterOperator, CustomFormatType } from '@lightdash/common';

// Revenue from high-value orders only
function createHighValueRevenueMetric(threshold: number): AdditionalMetric {
  return {
    label: `Revenue (Orders > $${threshold})`,
    type: MetricType.SUM,
    table: 'orders',
    baseDimensionName: 'total_amount',
    filters: [{
      id: 'high_value',
      target: { fieldRef: 'orders.total_amount' },  // Note: fieldRef not fieldId
      operator: FilterOperator.GREATER_THAN,
      values: [threshold],
    }],
    formatOptions: {
      type: CustomFormatType.CURRENCY,
      currency: 'USD',
      round: 0,
    },
  };
}

// Usage in query
const query: MetricQuery = {
  exploreName: 'orders',
  dimensions: ['orders.created_at_month'],
  metrics: ['orders.count'],
  additionalMetrics: [
    createHighValueRevenueMetric(1000),
    createHighValueRevenueMetric(5000),
  ],
  filters: { dimensions: { id: 'root', and: [] } },
  sorts: [{ fieldId: 'orders.created_at_month', descending: false }],
  limit: 12,
  tableCalculations: [],
};

Query Composition and Reusability

import { type MetricQuery, type Filters, addFiltersToMetricQuery } from '@lightdash/common';

// Base query template
const baseOrdersQuery: Omit<MetricQuery, 'filters'> = {
  exploreName: 'orders',
  dimensions: ['orders.status'],
  metrics: ['orders.count', 'orders.total_amount'],
  sorts: [{ fieldId: 'orders.count', descending: true }],
  limit: 100,
  tableCalculations: [],
};

// Reusable filter builders
function createDateRangeFilters(days: number): Filters {
  return {
    dimensions: {
      id: 'root',
      and: [{
        id: 'date',
        target: { fieldId: 'orders.created_at' },
        operator: FilterOperator.IN_THE_PAST,
        values: [days],
        settings: { unitOfTime: UnitOfTime.DAYS },
      }],
    },
  };
}

// Compose queries
const last30DaysQuery: MetricQuery = {
  ...baseOrdersQuery,
  filters: createDateRangeFilters(30),
};

const last90DaysQuery: MetricQuery = {
  ...baseOrdersQuery,
  filters: createDateRangeFilters(90),
};

// Add additional filters to existing query
const queryWithRegionFilter = addFiltersToMetricQuery(last30DaysQuery, {
  dimensions: {
    id: 'region',
    and: [{
      id: 'r1',
      target: { fieldId: 'customers.region' },
      operator: FilterOperator.EQUALS,
      values: ['North America'],
    }],
  },
});

Edge Cases

Scenario	Behavior	Solution
Required fields missing	TypeScript error or runtime failure	Always provide `filters`, `sorts`, `tableCalculations` (can be empty)
Field ID format wrong	Query fails, fields not found	Use underscore format: `orders_total` not `orders.total`
Empty filters object	Invalid	Use `{ dimensions: { id: 'root', and: [] } }` for no filters
Limit too high	May cause performance issues or warehouse errors	Use `500`-`5000` for most queries, `2147483647` for "unlimited"
AdditionalMetric missing name	Auto-generated name	Provide explicit `name` for consistent field IDs
AdditionalMetric filter format	Uses `fieldRef` (dot notation) not `fieldId`	In metric filters: `{ fieldRef: 'orders.amount' }`
Timezone not set	Uses server/warehouse default	Explicitly set `timezone: 'America/New_York'` for consistent results
Date dimension without suffix	Returns raw timestamp	Append `_day`/`_month` etc. for time grouping

Version

Tile

Files

metric-queries.mddocs/

Metric Queries

MetricQuery Type

Properties

Building Queries

Basic Query

With Filters

With Additional Metrics

With Custom Dimensions (Bins)

SortField

AdditionalMetric

Examples

MetricQueryResponse

Period-Over-Period Comparison

Example

Utility Functions

isAdditionalMetric

hasFormatOptions

getCustomMetricDimensionId

getAdditionalMetricLabel

countCustomDimensionsInMetricQuery

hasCustomBinDimension

Working with Query Results

Common Patterns

Query Builder with Validation

Dynamic Filter Construction

Creating Ad-Hoc Metrics

Query Composition and Reusability

Edge Cases

Version

Tile

Files

metric-queries.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Metric Queries

MetricQuery Type

Properties

Building Queries

Basic Query

With Filters

With Additional Metrics

With Custom Dimensions (Bins)

SortField

AdditionalMetric

Examples

MetricQueryResponse

Period-Over-Period Comparison

Example

Utility Functions

isAdditionalMetric

hasFormatOptions

getCustomMetricDimensionId

getAdditionalMetricLabel

countCustomDimensionsInMetricQuery

hasCustomBinDimension

Working with Query Results

Common Patterns

Query Builder with Validation

Dynamic Filter Construction

Creating Ad-Hoc Metrics

Query Composition and Reusability

Edge Cases

metric-queries.mddocs/