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

Building Queries

This guide shows you how to build metric queries to fetch data from Lightdash explores. For detailed API documentation, see Metric Queries API.

Overview

A metric query specifies:

What data model to query (explore)
What dimensions to group by (e.g., customer name, order date)
What metrics to calculate (e.g., count, sum, average)
What filters to apply
How to sort the results
How many rows to return

Basic Query Structure

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

const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders_customer_id", "orders_status"],
  metrics: ["orders_count", "orders_total_amount"],
  filters: { dimensions: { id: "root", and: [] } },
  sorts: [{ fieldId: "orders_created_at", descending: false }],
  limit: 1000,
  tableCalculations: [],
};

Step-by-Step: Building a Query

1. Inspect Available Fields

First, discover what fields are available in your explore:

import { getFields, getDimensions, getMetrics, type Explore } from "@lightdash/common";

const explore: Explore = /* ... get explore ... */;

// Get all fields
const allFields = getFields(explore);
console.log(`Total fields: ${allFields.length}`);

// Get dimensions (grouping columns)
const dimensions = getDimensions(explore);
console.log(`Dimensions: ${dimensions.length}`);
dimensions.forEach(d => console.log(`  - ${d.name}: ${d.label}`));

// Get metrics (aggregations)
const metrics = getMetrics(explore);
console.log(`Metrics: ${metrics.length}`);
metrics.forEach(m => console.log(`  - ${m.name}: ${m.label}`));

2. Select Dimensions and Metrics

Choose which dimensions and metrics to include:

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

const query: MetricQuery = {
  exploreName: "orders",
  // Select first 2 dimensions
  dimensions: dimensions.slice(0, 2).map(d => d.name),
  // Select first 3 metrics
  metrics: metrics.slice(0, 3).map(m => m.name),
  filters: { dimensions: { id: "root", and: [] } },
  sorts: [],
  limit: 100,
  tableCalculations: [],
};

3. Add Filters

Filter the data using various operators:

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

// Date filter: last 30 days
const dateFilter: FilterRule = {
  id: "date_filter",
  target: { fieldId: "orders_created_at" },
  operator: FilterOperator.IN_THE_PAST,
  values: [30],
  settings: { unitOfTime: UnitOfTime.days },
};

// String filter: specific status
const statusFilter: FilterRule = {
  id: "status_filter",
  target: { fieldId: "orders_status" },
  operator: FilterOperator.EQUALS,
  values: ["completed"],
};

// Add to query
query.filters = {
  dimensions: {
    id: "root",
    and: [dateFilter, statusFilter],
  },
};

4. Add Sorting

Sort results by one or more fields:

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

query.sorts = [
  // Primary sort: by metric descending
  { fieldId: "orders_total_amount", descending: true },
  // Secondary sort: by dimension ascending
  { fieldId: "orders_customer_id", descending: false },
];

5. Set Limit

Control how many rows to return:

query.limit = 500; // Return up to 500 rows

Common Query Patterns

Pattern: Recent Data

Get data from the last N days:

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

const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders_order_date"],
  metrics: ["orders_count"],
  filters: {
    dimensions: {
      id: "root",
      and: [{
        id: "recent",
        target: { fieldId: "orders_created_at" },
        operator: FilterOperator.IN_THE_PAST,
        values: [7],
        settings: { unitOfTime: UnitOfTime.days },
      }],
    },
  },
  sorts: [{ fieldId: "orders_order_date", descending: false }],
  limit: 1000,
  tableCalculations: [],
};

Pattern: Top N by Metric

Get top 10 customers by revenue:

const query: MetricQuery = {
  exploreName: "customers",
  dimensions: ["customers_customer_id", "customers_name"],
  metrics: ["customers_total_revenue"],
  filters: { dimensions: { id: "root", and: [] } },
  sorts: [{ fieldId: "customers_total_revenue", descending: true }],
  limit: 10,
  tableCalculations: [],
};

Pattern: Multiple Filters with OR Logic

Orders that are either "pending" OR "processing":

import { FilterOperator } from "@lightdash/common";

const query: MetricQuery = {
  exploreName: "orders",
  dimensions: ["orders_status"],
  metrics: ["orders_count"],
  filters: {
    dimensions: {
      id: "root",
      or: [
        {
          id: "pending",
          target: { fieldId: "orders_status" },
          operator: FilterOperator.EQUALS,
          values: ["pending"],
        },
        {
          id: "processing",
          target: { fieldId: "orders_status" },
          operator: FilterOperator.EQUALS,
          values: ["processing"],
        },
      ],
    },
  },
  sorts: [],
  limit: 100,
  tableCalculations: [],
};

Pattern: Range Filter

Orders between $100 and $500:

import { FilterOperator } from "@lightdash/common";

const rangeFilter = {
  id: "amount_range",
  target: { fieldId: "orders_total_amount" },
  operator: FilterOperator.IN_BETWEEN,
  values: [100, 500],
};

query.filters = {
  dimensions: {
    id: "root",
    and: [rangeFilter],
  },
};

Advanced Features

Ad-hoc Metrics

Create metrics at query time without modifying the explore:

import { MetricType, CustomFormatType, type AdditionalMetric } 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,
  },
};

query.additionalMetrics = [avgOrderValue];

Table Calculations

Add post-query calculations:

import { type TableCalculation, CustomFormatType } from "@lightdash/common";

const percentOfTotal: TableCalculation = {
  name: "percent_of_total",
  displayName: "% of Total",
  sql: "${orders_count} / SUM(${orders_count}) OVER ()",
  format: {
    type: CustomFormatType.PERCENT,
    round: 1,
  },
};

query.tableCalculations = [percentOfTotal];

Custom Dimensions

Create binned dimensions at query time:

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

const amountBins: CustomBinDimension = {
  id: "amount_bins",
  name: "amount_bins",
  type: CustomDimensionType.BIN,
  table: "orders",
  dimensionId: "orders_total_amount",
  binType: BinType.CUSTOM_RANGE,
  customRange: [
    { from: undefined, to: 50 },    // Less than 50
    { from: 50, to: 200 },           // 50-200
    { from: 200, to: undefined },    // 200+
  ],
};

query.customDimensions = [amountBins];
query.dimensions = ["amount_bins"]; // Use the binned dimension

Filter Operators Reference

String Operators

EQUALS - Exact match
NOT_EQUALS - Not equal
STARTS_WITH - Begins with
ENDS_WITH - Ends with
INCLUDE - Contains substring
NOT_INCLUDE - Does not contain

Number Operators

EQUALS - Equal to
NOT_EQUALS - Not equal to
GREATER_THAN - Greater than
GREATER_THAN_OR_EQUAL - Greater than or equal
LESS_THAN - Less than
LESS_THAN_OR_EQUAL - Less than or equal
IN_BETWEEN - Between two values

Date Operators

EQUALS - Exact date
IN_THE_PAST - Last N days/weeks/months/years
IN_THE_NEXT - Next N days/weeks/months/years
IN_THE_CURRENT - Current day/week/month/year
IN_BETWEEN - Between two dates

Null Operators

NULL - Is null
NOT_NULL - Is not null

Tips and Best Practices

1. Use Field Maps for Efficiency

Create field maps once and reuse them:

import { getFieldMap } from "@lightdash/common";

// Create map once
const fieldMap = getFieldMap(explore);

// O(1) lookups
const field = fieldMap["orders_customer_id"];

2. Validate Field Existence

Check fields exist before adding to query:

const requestedDimensions = ["orders_customer_id", "orders_status"];
const validDimensions = requestedDimensions.filter(
  fieldId => fieldMap[fieldId] && fieldMap[fieldId].fieldType === "dimension"
);

query.dimensions = validDimensions;

3. Handle Empty Filters

Always provide a filter structure, even if empty:

// Good
filters: { dimensions: { id: "root", and: [] } }

// Avoid
filters: {} // Missing required structure

4. Consider Limit Values

Choose appropriate limits:

// For UI display
query.limit = 500;

// For exports
query.limit = 5000;

// For sampling
query.limit = 100;

Next Steps

Learn about Working with Data to format query results
See Visualization to create charts from queries
Read Filters API for advanced filtering
Explore Metric Queries API for complete reference

Version

Tile

Files