or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

guides

authorization.md building-queries.md compilation.md visualization.md working-with-data.md

quick-reference

common-apis.md imports.md types-at-a-glance.md

reference

architecture.md getting-started-full.md

index.md

tile.json

Metric Queries

Metric queries are the core query structure in Lightdash, defining what data to fetch from explores including dimensions, metrics, filters, sorts, and calculations.

Overview

A metric query specifies:

The explore (data model) to query
Dimensions (grouping columns)
Metrics (aggregated values)
Filters (dimension and metric filters)
Sort order
Limit
Additional calculations (table calculations, custom dimensions, ad-hoc metrics)

MetricQuery Type

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

type MetricQuery = {
  exploreName: string;
  dimensions: FieldId[];
  metrics: FieldId[];
  filters: Filters;
  sorts: SortField[];
  limit: number;
  tableCalculations: TableCalculation[];
  additionalMetrics?: AdditionalMetric[];
  customDimensions?: CustomDimension[];
  metricOverrides?: MetricOverrides;
  timezone?: string;
  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.
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.

CompiledMetricQuery Type

The compiled version of a MetricQuery with all calculations, metrics, and dimensions compiled and ready for SQL generation.

type CompiledMetricQuery = Omit<MetricQuery, "customDimensions"> & {
  compiledTableCalculations: CompiledTableCalculation[];
  compiledAdditionalMetrics: CompiledMetric[];
  compiledCustomDimensions: CompiledCustomDimension[];
};

Properties:

Includes all properties from MetricQuery except customDimensions
compiledTableCalculations: Table calculations with SQL compiled and template variables resolved
compiledAdditionalMetrics: Additional metrics with SQL compiled and ready for execution
compiledCustomDimensions: Custom dimensions (bins and SQL dimensions) with SQL compiled. Type is CompiledCustomDimension[] which includes both CompiledDimension and CustomBinDimension

This type is used internally during query execution after the compilation phase transforms the user-defined query into executable SQL.

SortField Interface

interface SortField {
  fieldId: string;
  descending: boolean;
  nullsFirst?: boolean;
}

Specifies sorting for a field.

Example:

const sorts: SortField[] = [
  { fieldId: "customers.revenue", descending: true }, // Highest revenue first
  { fieldId: "customers.name", descending: false }, // Then alphabetical
];

AdditionalMetric Interface

Additional metrics are ad-hoc metrics defined at query time without modifying the explore definition.

interface AdditionalMetric {
  name: string;
  label?: string;
  type: MetricType;
  sql: string;
  table: string;
  hidden?: boolean;
  baseDimensionName?: string;
  percentile?: number;
  formatOptions?: CustomFormat;
  /** @deprecated Use format expression instead */
  compact?: CompactOrAlias;
  /** @deprecated Use format expression instead */
  format?: Format | string;
  /** @deprecated Use format expression instead */
  round?: number;
  filters?: MetricFilterRule[];
  description?: string;
  index?: number;
  uuid?: string | null;
}

Properties

name: Internal name for the metric (auto-generated if not provided)
label: Display label
type: Metric type (COUNT, SUM, AVERAGE, etc.)
sql: SQL expression for the metric (for custom SQL metrics)
table: Table name the metric belongs to
baseDimensionName: Base dimension for aggregation (e.g., for COUNT DISTINCT)
percentile: Percentile value (for PERCENTILE metrics, e.g., 95 for p95)
formatOptions: Custom formatting configuration
filters: Optional filters to apply only to this metric
description: Optional description

Example:

import { MetricType, type AdditionalMetric } from "@lightdash/common";

// Count distinct customers
const uniqueCustomers: AdditionalMetric = {
  label: "Unique Customers",
  type: MetricType.COUNT_DISTINCT,
  table: "orders",
  baseDimensionName: "customer_id",
  formatOptions: {
    type: CustomFormatType.NUMBER,
    separator: NumberSeparator.COMMA_PERIOD,
  },
};

// Custom SQL metric
const averageOrderValue: 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,
  },
};

// High-value orders (with filter)
const highValueOrders: AdditionalMetric = {
  label: "High Value Orders",
  type: MetricType.COUNT_DISTINCT,
  table: "orders",
  baseDimensionName: "order_id",
  filters: [
    {
      id: "high_value",
      target: { fieldId: "orders.total_amount" },
      operator: FilterOperator.GREATER_THAN,
      values: [1000],
    },
  ],
};

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
}

MetricQueryResponse

The response structure returned from executing a metric query.

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

Properties

rows: Array of result rows, each row is a record mapping field IDs to values
metricQuery: The original metric query that was executed
fields: Map of field IDs to their definitions (for formatting and display)
cacheMetadata: Optional cache information

Example:

const response: MetricQueryResponse = {
  rows: [
    {
      "customers.customer_id": 1,
      "customers.name": "John Doe",
      "customers.total_orders": 5,
      "customers.revenue": 1250.5,
    },
    // ... more rows
  ],
  metricQuery: {
    exploreName: "customers",
    dimensions: ["customers.customer_id", "customers.name"],
    metrics: ["customers.total_orders", "customers.revenue"],
    filters: { dimensions: { id: "root", and: [] } },
    sorts: [{ fieldId: "customers.revenue", descending: true }],
    limit: 100,
    tableCalculations: [],
  },
  fields: {
    "customers.customer_id": {
      /* dimension definition */
    },
    "customers.name": {
      /* dimension definition */
    },
    "customers.total_orders": {
      /* metric definition */
    },
    "customers.revenue": {
      /* metric definition */
    },
  },
};

FiltersResponse

Response format for filters in API responses, similar to Filters but with AnyType values.

type FilterGroupResponse =
  | {
      id: string;
      or: AnyType[];
    }
  | {
      id: string;
      and: AnyType[];
    };

type FiltersResponse = {
  dimensions?: FilterGroupResponse;
  metrics?: FilterGroupResponse;
  tableCalculations?: FilterGroupResponse;
};

Used in API responses when filters need to be serialized. The structure matches Filters but uses AnyType for flexibility in serialization.

MetricQueryRequest

API request format for metric queries, simplified for HTTP API usage.

type MetricQueryRequest = {
  exploreName: string;
  dimensions: FieldId[];
  metrics: FieldId[];
  filters: {
    dimensions?: AnyType;
    metrics?: AnyType;
    tableCalculations?: AnyType;
  };
  sorts: SortField[];
  limit: number;
  tableCalculations: TableCalculation[];
  additionalMetrics?: AdditionalMetric[];
  csvLimit?: number;
  customDimensions?: CustomDimension[];
  dateZoom?: DateZoom;
  metadata?: MetricQuery["metadata"];
  timezone?: string;
  metricOverrides?: MetricOverrides;
  periodOverPeriod?: PeriodOverPeriodComparison;
};

Properties:

Similar to MetricQuery but with simplified filter types for API serialization
csvLimit: Optional limit for CSV export operations
dateZoom: Optional date zoom configuration for time-based queries

This type is used for API endpoints and differs from MetricQuery by using AnyType for filters instead of strongly typed filter structures.

QueryWarning

Warning messages returned with query results to inform users about potential issues.

type QueryWarning = {
  message: string;
  fields?: string[];
  tables?: string[];
};

Properties:

message: Warning message in markdown format to be shown to the user
fields: Optional array of field IDs that relate to this warning
tables: Optional array of table names that relate to this warning

Example:

const warning: QueryWarning = {
  message: "Some filters could not be applied due to missing fields",
  fields: ["customers.deleted_field"],
  tables: ["customers"],
};

MetricOverrides

Override format options for metrics in a query.

type MetricOverrides = { [key: string]: Pick<Metric, "formatOptions"> };

A record mapping metric field IDs to format option overrides. Used to temporarily override metric formatting without modifying the explore definition. Each key is a metric field ID (e.g., "orders_total_revenue") and the value contains only the formatOptions property from the Metric type.

Query Building Examples

Basic Query

import { type MetricQuery } from "@lightdash/common";

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: [],
};

Query with Filters

import { type MetricQuery, FilterOperator, UnitOfTime } from "@lightdash/common";

const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders.status"],
  metrics: ["orders.count", "orders.total_amount"],
  filters: {
    dimensions: {
      id: "root",
      and: [
        {
          id: "date_filter",
          target: { fieldId: "orders.created_at" },
          operator: FilterOperator.IN_THE_PAST,
          values: [30],
          settings: { unitOfTime: UnitOfTime.days },
        },
        {
          id: "status_filter",
          target: { fieldId: "orders.status" },
          operator: FilterOperator.EQUALS,
          values: ["completed"],
        },
      ],
    },
  },
  sorts: [{ fieldId: "orders.count", descending: true }],
  limit: 100,
  tableCalculations: [],
};

Query with Additional Metrics

import {
  type MetricQuery,
  type AdditionalMetric,
  MetricType,
  CustomFormatType,
} from "@lightdash/common";

const avgOrderValue: 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: [avgOrderValue],
  sorts: [{ fieldId: "orders.count", descending: true }],
  limit: 50,
  tableCalculations: [],
};

Query with Table Calculations

import {
  type MetricQuery,
  type TableCalculation,
  TableCalculationType,
  WindowFunctionType,
} from "@lightdash/common";

const runningTotal: TableCalculation = {
  name: "running_total",
  displayName: "Running Total",
  calculationType: TableCalculationType.SQL,
  windowFunction: {
    type: WindowFunctionType.SUM,
    field: "orders.total_amount",
    orderBy: {
      field: "orders.order_date",
      ascending: true,
    },
  },
};

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

Query with Custom Dimensions

import {
  type MetricQuery,
  type CustomBinDimension,
  CustomDimensionType,
  BinType,
} from "@lightdash/common";

const orderAmountBins: CustomBinDimension = {
  id: "order_amount_bins",
  name: "order_amount_bins",
  type: CustomDimensionType.BIN,
  table: "orders",
  dimensionId: "orders.total_amount",
  binType: BinType.CUSTOM_RANGE,
  customRange: [
    { from: 0, to: 50, label: "Small (0-50)" },
    { from: 50, to: 200, label: "Medium (50-200)" },
    { from: 200, to: Infinity, label: "Large (200+)" },
  ],
};

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

Complex Query Example

import {
  type MetricQuery,
  type AdditionalMetric,
  type TableCalculation,
  FilterOperator,
  MetricType,
  CustomFormatType,
  TableCalculationType,
} from "@lightdash/common";

// Ad-hoc metric: high-value customer count
const highValueCustomers: AdditionalMetric = {
  label: "High Value Customers",
  type: MetricType.COUNT_DISTINCT,
  table: "orders",
  baseDimensionName: "customer_id",
  filters: [
    {
      id: "high_value",
      target: { fieldId: "orders.total_amount" },
      operator: FilterOperator.GREATER_THAN,
      values: [500],
    },
  ],
};

// Table calculation: percentage of total
const percentOfTotal: TableCalculation = {
  name: "percent_of_total",
  displayName: "% of Total",
  sql: "${orders.count} / SUM(${orders.count}) OVER ()",
  calculationType: TableCalculationType.SQL,
  format: {
    type: CustomFormatType.PERCENT,
    round: 1,
  },
};

const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders.order_date", "orders.status"],
  metrics: ["orders.count", "orders.total_amount"],
  additionalMetrics: [highValueCustomers],
  tableCalculations: [percentOfTotal],
  filters: {
    dimensions: {
      id: "root",
      and: [
        {
          id: "date_filter",
          target: { fieldId: "orders.created_at" },
          operator: FilterOperator.IN_THE_PAST,
          values: [90],
          settings: { unitOfTime: UnitOfTime.days },
        },
      ],
    },
  },
  sorts: [
    { fieldId: "orders.order_date", descending: false },
    { fieldId: "orders.total_amount", descending: true },
  ],
  limit: 500,
  timezone: "America/New_York",
};

Working with Query Results

Formatting Results

import {
  formatItemValue,
  formatRows,
  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
  );

  // Format all rows
  const formattedRows = formatRows(response.rows, itemsMap);

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

  return formattedRows;
}

Extracting Query Items

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

function validateQuery(query: MetricQuery, explore: Explore): string[] {
  // Get all items referenced in query
  const allItemIds = itemsInMetricQuery(query);

  // Build item map
  const itemsMap = getItemMap(
    explore,
    query.additionalMetrics,
    query.tableCalculations,
    query.customDimensions
  );

  // Find missing items
  const missingItems = allItemIds.filter((id) => !itemsMap[id]);

  return missingItems;
}

Period-Over-Period Comparison

Period-over-period (PoP) comparison allows time-based analysis comparing current period metrics to previous periods.

PeriodOverPeriodComparison Type

/**
 * Configuration for period-over-period comparison in metric queries
 */
interface PeriodOverPeriodComparison {
  type: "previousPeriod";
  granularity: TimeFrames;
  periodOffset?: number;
  field: {
    name: string;
    table: string;
  };
}

Properties:

type: Comparison type, currently only 'previousPeriod' is supported
granularity: Time granularity for comparison (day, week, month, quarter, or year). Must be one of the valid period-over-period granularities
periodOffset: Optional number of periods to offset. For example, compare this month to 3 months ago (periodOffset: 3). Defaults to 1 if not specified
field: The time dimension field to use for comparison, specified as an object with name and table properties

Valid Granularities

/**
 * Valid granularities for period-over-period comparison
 */
const validPeriodOverPeriodGranularities: TimeFrames[] = [
  TimeFrames.DAY,
  TimeFrames.WEEK,
  TimeFrames.MONTH,
  TimeFrames.QUARTER,
  TimeFrames.YEAR,
];

/**
 * Check if a granularity is supported for period-over-period comparison
 */
function isSupportedPeriodOverPeriodGranularity(granularity: TimeFrames): boolean;

PoP Field Naming

Period-over-period creates additional columns with a _previous suffix:

/**
 * Suffix used for period-over-period comparison columns
 */
const POP_PREVIOUS_PERIOD_SUFFIX = "_previous";

/**
 * Gets the PoP field ID for a base metric field ID
 * @param baseFieldId - The field ID of the base metric (e.g., "orders_total_revenue")
 * @returns The PoP field ID (e.g., "orders_total_revenue_previous")
 */
function getPopFieldId(baseFieldId: string): string;

/**
 * Gets the base field ID from a PoP field ID
 * @param fieldId - The field ID to check
 * @returns The base field ID if this is a PoP field, null otherwise
 */
function getBaseFieldIdFromPop(fieldId: string): string | null;

Example:

import {
  type MetricQuery,
  type PeriodOverPeriodComparison,
  TimeFrames,
  getPopFieldId,
  getBaseFieldIdFromPop,
} from "@lightdash/common";

// Create a metric query with period-over-period comparison
const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders_created_at"],
  metrics: ["orders_total_revenue", "orders_order_count"],
  filters: { dimensions: undefined, metrics: undefined },
  sorts: [{ fieldId: "orders_created_at", descending: false }],
  limit: 100,
  tableCalculations: [],
  periodOverPeriod: {
    type: "previousPeriod",
    granularity: TimeFrames.MONTH,
    periodOffset: 1, // Compare to previous month
    field: {
      name: "created_at",
      table: "orders",
    },
  },
};

// Result columns will include:
// - orders_total_revenue (current period)
// - orders_total_revenue_previous (previous period)
// - orders_order_count (current period)
// - orders_order_count_previous (previous period)

// Get PoP field IDs
const popRevenueId = getPopFieldId("orders_total_revenue");
// Returns: "orders_total_revenue_previous"

// Check if a field is a PoP field
const baseFieldId = getBaseFieldIdFromPop("orders_total_revenue_previous");
// Returns: "orders_total_revenue"

const notPopField = getBaseFieldIdFromPop("orders_total_revenue");
// Returns: null

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/api/features/queries/

Metric Queries

Overview

MetricQuery Type

Properties

CompiledMetricQuery Type

SortField Interface

AdditionalMetric Interface

Properties

Utility Functions

isAdditionalMetric

hasFormatOptions

getCustomMetricDimensionId

getAdditionalMetricLabel

countCustomDimensionsInMetricQuery

hasCustomBinDimension

MetricQueryResponse

Properties

FiltersResponse

MetricQueryRequest

QueryWarning

MetricOverrides

Query Building Examples

Basic Query

Query with Filters

Query with Additional Metrics

Query with Table Calculations

Query with Custom Dimensions

Complex Query Example

Working with Query Results

Formatting Results

Extracting Query Items

Period-Over-Period Comparison

PeriodOverPeriodComparison Type

Valid Granularities

PoP Field Naming

metric-queries.mddocs/api/features/queries/