--- title: "Compile query" method: POST path: "/api/v1/projects/{projectUuid}/explores/{exploreId}/compileQuery" tags: ["Projects"] --- # Compile query `POST /api/v1/projects/{projectUuid}/explores/{exploreId}/compileQuery` Compile a metric query for an explore ## Path parameters - `exploreId` string, required - `projectUuid` string, required ## Request body - object - `metadata` object - `hasADateDimension` PickCompiledDimensionLabelOrNameOrTable, required — From T, pick a set of properties whose keys are in the union K - `name` string, required - `label` string, required - `table` string, required - `pivotDimensions` FieldId[] — Dimension field IDs used as pivot columns (from chart's pivotConfig.columns). Used by row_total() to determine non-pivot dimensions for GROUP BY. - `timezone` string — Timezone for date/time values (e.g., 'America/Los_Angeles', 'UTC') - `dimensionOverrides` DimensionOverrides - `metricOverrides` MetricOverrides - `customDimensions` CustomDimension[] — Custom dimensions defined inline - union - union - FixedNumberBinDimension - `id` string, required — Unique identifier for the custom dimension - `name` string, required — Display name for the custom dimension - `table` string, required — Table this custom dimension belongs to - `type` 'bin', required - `dimensionId` string, required - `binType` 'fixed_number', required - `binNumber` number, double, required - FixedWidthBinDimension - `id` string, required — Unique identifier for the custom dimension - `name` string, required — Display name for the custom dimension - `table` string, required — Table this custom dimension belongs to - `type` 'bin', required - `dimensionId` string, required - `binType` 'fixed_width', required - `binWidth` number, double, required - CustomRangeBinDimension - `id` string, required — Unique identifier for the custom dimension - `name` string, required — Display name for the custom dimension - `table` string, required — Table this custom dimension belongs to - `type` 'bin', required - `dimensionId` string, required - `binType` 'custom_range', required - `customRange` BinRange[], required - `to` number, double — End value for this bin range (undefined for the last range) - `from` number, double — Start value for this bin range (undefined for the first range) - CustomGroupBinDimension - `id` string, required — Unique identifier for the custom dimension - `name` string, required — Display name for the custom dimension - `table` string, required — Table this custom dimension belongs to - `type` 'bin', required - `dimensionId` string, required - `binType` 'custom_group', required - `customGroups` BinGroup[], required - `values` GroupValueRule[], required — Rules that match values into this group - … - `name` string, required — Display name for this group (e.g. "North America") - CustomSqlDimension - `id` string, required — Unique identifier for the custom dimension - `name` string, required — Display name for the custom dimension - `table` string, required — Table this custom dimension belongs to - `type` 'sql', required - `sql` string, required — SQL expression for the custom dimension - `dimensionType` 'string' | 'number' | 'timestamp' | 'date' | 'boolean', required - `additionalMetrics` AdditionalMetric[] — Custom metrics defined inline (ad-hoc metrics not in the dbt model) - `label` string — Display label for the metric - `type` 'percentile' | 'average' | 'count' | 'count_distinct' | 'sum' | 'sum_distinct' | 'average_distinct' | 'min' | 'max' | 'percent_of_previous' | 'percent_of_total' | 'running_total' | 'number' | 'median' | 'string' | 'date' | 'timestamp' | 'boolean', required - `description` string — Description of what the metric measures - `sql` string, required — SQL expression (e.g., ${TABLE}.column_name) - `hidden` boolean — Whether the metric is hidden from users - `round` number, double — Number of decimal places - `compact` union - 'auto' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'kilobytes' | 'megabytes' | 'gigabytes' | 'terabytes' | 'petabytes' | 'kibibytes' | 'mebibytes' | 'gibibytes' | 'tebibytes' | 'pebibytes' - 'K' | 'thousand' | 'M' | 'million' | 'B' | 'billion' | 'T' | 'trillion' | 'KB' | 'kilobyte' | 'MB' | 'megabyte' | 'GB' | 'gigabyte' | 'TB' | 'terabyte' | 'PB' | 'petabyte' | 'KiB' | 'kibibyte' | 'MiB' | 'mebibyte' | 'GiB' | 'gibibyte' | 'TiB' | 'tebibyte' | 'PiB' | 'pebibyte' - `format` union — Format string (legacy format specification) - 'km' | 'mi' | 'si' | 'usd' | 'gbp' | 'eur' | 'jpy' | 'dkk' | 'id' | 'percent' - string - `separator` 'default' | 'commaPeriod' | 'spacePeriod' | 'periodComma' | 'noSeparatorPeriod' | 'apostrophePeriod' - `table` string, required — Table name the metric belongs to - `name` string, required — Internal name of the metric - `index` number, double — Display order index - `filters` MetricFilterRule[] — Filters to apply to this metric - `includeNull` boolean — For the `equals` operator on string fields, also match rows where the field is null (compiles to `field IN (...) OR field IS NULL`). Lets users combine null with selected values in a single "is" rule. - `values` AnyType[] — Values to filter by - unknown - `operator` 'isNull' | 'notNull' | 'equals' | 'notEquals' | 'startsWith' | 'endsWith' | 'include' | 'doesNotInclude' | 'lessThan' | 'lessThanOrEqual' | 'greaterThan' | 'greaterThanOrEqual' | 'inThePast' | 'notInThePast' | 'inTheNext' | 'inTheCurrent' | 'notInTheCurrent' | 'inBetween' | 'notInBetween' | 'inPeriodToDate', required - `id` string, required — Unique identifier for the filter - `target` object, required — Target field for the filter - `fieldRef` string, required — Field reference to filter on (e.g., 'table_name.field_name') - `settings` unknown - `disabled` boolean — Whether this filter is disabled - `required` boolean — Whether this filter is required - `caseSensitive` boolean — Overrides the field/explore case-sensitivity for this rule only. Used by internal features like autocomplete search that must always match case-insensitively regardless of the field's configured setting. - `baseDimensionName` string — Name of the base dimension/column this metric aggregates - `baseMetricName` string — Name of the explore metric this metric was cloned from - `uuid` string, nullable — Unique identifier for the metric - `percentile` number, double — Percentile value for percentile metrics - `distinctKeys` string[] - `formatOptions` CustomFormat - `type` 'default' | 'percent' | 'currency' | 'number' | 'id' | 'date' | 'timestamp' | 'bytes_si' | 'bytes_iec' | 'custom', required - `round` number, double — Number of decimal places - `separator` 'default' | 'commaPeriod' | 'spacePeriod' | 'periodComma' | 'noSeparatorPeriod' | 'apostrophePeriod' - `currency` string — Currency code (e.g., USD, GBP, EUR) - `compact` union - 'auto' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'kilobytes' | 'megabytes' | 'gigabytes' | 'terabytes' | 'petabytes' | 'kibibytes' | 'mebibytes' | 'gibibytes' | 'tebibytes' | 'pebibytes' - 'K' | 'thousand' | 'M' | 'million' | 'B' | 'billion' | 'T' | 'trillion' | 'KB' | 'kilobyte' | 'MB' | 'megabyte' | 'GB' | 'gigabyte' | 'TB' | 'terabyte' | 'PB' | 'petabyte' | 'KiB' | 'kibibyte' | 'MiB' | 'mebibyte' | 'GiB' | 'gibibyte' | 'TiB' | 'tebibyte' | 'PiB' | 'pebibyte' - `prefix` string — Prefix to prepend to formatted values - `suffix` string — Suffix to append to formatted values - `timeInterval` 'RAW' | 'YEAR' | 'QUARTER' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND' | 'DAY_OF_WEEK_INDEX' | 'DAY_OF_MONTH_NUM' | 'DAY_OF_YEAR_NUM' | 'WEEK_NUM' | 'MONTH_NUM' | 'QUARTER_NUM' | 'YEAR_NUM' | 'DAY_OF_WEEK_NAME' | 'MONTH_NAME' | 'QUARTER_NAME' | 'HOUR_OF_DAY_NUM' | 'MINUTE_OF_HOUR_NUM' - `custom` string — Custom format string - `generationType` 'periodOverPeriod' — Optional marker for metrics generated by the system. Currently used for Period-over-Period (PoP) previous-period metrics. - `baseMetricId` string - `timeDimensionId` string - `granularity` 'RAW' | 'YEAR' | 'QUARTER' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND' | 'DAY_OF_WEEK_INDEX' | 'DAY_OF_MONTH_NUM' | 'DAY_OF_YEAR_NUM' | 'WEEK_NUM' | 'MONTH_NUM' | 'QUARTER_NUM' | 'YEAR_NUM' | 'DAY_OF_WEEK_NAME' | 'MONTH_NAME' | 'QUARTER_NAME' | 'HOUR_OF_DAY_NUM' | 'MINUTE_OF_HOUR_NUM' - `periodOffset` number, double — For PoP-generated metrics, the number of periods to offset by (>= 1). - `tableCalculations` TableCalculation[], required — Custom calculations to perform on query results - union - SqlTableCalculation - `totalMode` 'formula' | 'sum_of_rows' | 'none' - `type` 'number' | 'string' | 'date' | 'timestamp' | 'boolean' - `format` CustomFormat - `type` 'default' | 'percent' | 'currency' | 'number' | 'id' | 'date' | 'timestamp' | 'bytes_si' | 'bytes_iec' | 'custom', required - `round` number, double — Number of decimal places - `separator` 'default' | 'commaPeriod' | 'spacePeriod' | 'periodComma' | 'noSeparatorPeriod' | 'apostrophePeriod' - `currency` string — Currency code (e.g., USD, GBP, EUR) - `compact` union - 'auto' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'kilobytes' | 'megabytes' | 'gigabytes' | 'terabytes' | 'petabytes' | 'kibibytes' | 'mebibytes' | 'gibibytes' | 'tebibytes' | 'pebibytes' - 'K' | 'thousand' | 'M' | 'million' | 'B' | 'billion' | 'T' | 'trillion' | 'KB' | 'kilobyte' | 'MB' | 'megabyte' | 'GB' | 'gigabyte' | 'TB' | 'terabyte' | 'PB' | 'petabyte' | 'KiB' | 'kibibyte' | 'MiB' | 'mebibyte' | 'GiB' | 'gibibyte' | 'TiB' | 'tebibyte' | 'PiB' | 'pebibyte' - `prefix` string — Prefix to prepend to formatted values - `suffix` string — Suffix to append to formatted values - `timeInterval` 'RAW' | 'YEAR' | 'QUARTER' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND' | 'DAY_OF_WEEK_INDEX' | 'DAY_OF_MONTH_NUM' | 'DAY_OF_YEAR_NUM' | 'WEEK_NUM' | 'MONTH_NUM' | 'QUARTER_NUM' | 'YEAR_NUM' | 'DAY_OF_WEEK_NAME' | 'MONTH_NAME' | 'QUARTER_NAME' | 'HOUR_OF_DAY_NUM' | 'MINUTE_OF_HOUR_NUM' - `custom` string — Custom format string - `displayName` string, required — Display name shown in the UI - `name` string, required — Internal name of the table calculation - `index` number, double — Display order index - `sql` string, required — SQL expression for the calculation (can reference fields with ${table.field}) - TemplateTableCalculation - `totalMode` 'formula' | 'sum_of_rows' | 'none' - `type` 'number' | 'string' | 'date' | 'timestamp' | 'boolean' - `format` CustomFormat - `type` 'default' | 'percent' | 'currency' | 'number' | 'id' | 'date' | 'timestamp' | 'bytes_si' | 'bytes_iec' | 'custom', required - `round` number, double — Number of decimal places - `separator` 'default' | 'commaPeriod' | 'spacePeriod' | 'periodComma' | 'noSeparatorPeriod' | 'apostrophePeriod' - `currency` string — Currency code (e.g., USD, GBP, EUR) - `compact` union - 'auto' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'kilobytes' | 'megabytes' | 'gigabytes' | 'terabytes' | 'petabytes' | 'kibibytes' | 'mebibytes' | 'gibibytes' | 'tebibytes' | 'pebibytes' - 'K' | 'thousand' | 'M' | 'million' | 'B' | 'billion' | 'T' | 'trillion' | 'KB' | 'kilobyte' | 'MB' | 'megabyte' | 'GB' | 'gigabyte' | 'TB' | 'terabyte' | 'PB' | 'petabyte' | 'KiB' | 'kibibyte' | 'MiB' | 'mebibyte' | 'GiB' | 'gibibyte' | 'TiB' | 'tebibyte' | 'PiB' | 'pebibyte' - `prefix` string — Prefix to prepend to formatted values - `suffix` string — Suffix to append to formatted values - `timeInterval` 'RAW' | 'YEAR' | 'QUARTER' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND' | 'DAY_OF_WEEK_INDEX' | 'DAY_OF_MONTH_NUM' | 'DAY_OF_YEAR_NUM' | 'WEEK_NUM' | 'MONTH_NUM' | 'QUARTER_NUM' | 'YEAR_NUM' | 'DAY_OF_WEEK_NAME' | 'MONTH_NAME' | 'QUARTER_NAME' | 'HOUR_OF_DAY_NUM' | 'MINUTE_OF_HOUR_NUM' - `custom` string — Custom format string - `displayName` string, required — Display name shown in the UI - `name` string, required — Internal name of the table calculation - `index` number, double — Display order index - `template` union, required - object - `partitionBy` string[] - `orderBy` object[], required — Fields to order by for window functions - … - `fieldId` string, required — Field ID to apply the template to - `type` 'percent_change_from_previous', required - object - `partitionBy` string[] - `orderBy` object[], required — Fields to order by for window functions - … - `fieldId` string, required — Field ID to apply the template to - `type` 'percent_of_previous_value', required - object - `partitionBy` string[] — Fields to partition by - `fieldId` string, required — Field ID to apply the template to - `type` 'percent_of_column_total', required - object - `fieldId` string, required — Field ID to apply the template to - `type` 'rank_in_column', required - object - `fieldId` string, required — Field ID to apply the template to - `type` 'running_total', required - object - `frame` FrameClause - … - `partitionBy` string[], required — Fields to partition by for window functions - `orderBy` object[], required — Fields to order by for window functions - … - `fieldId` string, nullable, required — Field ID to apply the template to - `windowFunction` 'row_number' | 'percent_rank' | 'cume_dist' | 'rank' | 'sum' | 'avg' | 'count' | 'min' | 'max', required - `type` 'window_function', required - FormulaTableCalculation - `totalMode` 'formula' | 'sum_of_rows' | 'none' - `type` 'number' | 'string' | 'date' | 'timestamp' | 'boolean' - `format` CustomFormat - `type` 'default' | 'percent' | 'currency' | 'number' | 'id' | 'date' | 'timestamp' | 'bytes_si' | 'bytes_iec' | 'custom', required - `round` number, double — Number of decimal places - `separator` 'default' | 'commaPeriod' | 'spacePeriod' | 'periodComma' | 'noSeparatorPeriod' | 'apostrophePeriod' - `currency` string — Currency code (e.g., USD, GBP, EUR) - `compact` union - 'auto' | 'thousands' | 'millions' | 'billions' | 'trillions' | 'kilobytes' | 'megabytes' | 'gigabytes' | 'terabytes' | 'petabytes' | 'kibibytes' | 'mebibytes' | 'gibibytes' | 'tebibytes' | 'pebibytes' - 'K' | 'thousand' | 'M' | 'million' | 'B' | 'billion' | 'T' | 'trillion' | 'KB' | 'kilobyte' | 'MB' | 'megabyte' | 'GB' | 'gigabyte' | 'TB' | 'terabyte' | 'PB' | 'petabyte' | 'KiB' | 'kibibyte' | 'MiB' | 'mebibyte' | 'GiB' | 'gibibyte' | 'TiB' | 'tebibyte' | 'PiB' | 'pebibyte' - `prefix` string — Prefix to prepend to formatted values - `suffix` string — Suffix to append to formatted values - `timeInterval` 'RAW' | 'YEAR' | 'QUARTER' | 'MONTH' | 'WEEK' | 'DAY' | 'HOUR' | 'MINUTE' | 'SECOND' | 'MILLISECOND' | 'DAY_OF_WEEK_INDEX' | 'DAY_OF_MONTH_NUM' | 'DAY_OF_YEAR_NUM' | 'WEEK_NUM' | 'MONTH_NUM' | 'QUARTER_NUM' | 'YEAR_NUM' | 'DAY_OF_WEEK_NAME' | 'MONTH_NAME' | 'QUARTER_NAME' | 'HOUR_OF_DAY_NUM' | 'MINUTE_OF_HOUR_NUM' - `custom` string — Custom format string - `displayName` string, required — Display name shown in the UI - `name` string, required — Internal name of the table calculation - `index` number, double — Display order index - `formula` string, required — Spreadsheet-like formula compiled to SQL at query time - `limit` number, double, required — Maximum number of rows to return - `sorts` SortField[], required — Sort configuration for query results - `pivotValues` PivotSortAnchor[] — Pins the row-sort anchor to a specific pivot column. Ignored for non-pivoted results. - `value` union, required - string - number, double - boolean - `reference` string, required - `nullsFirst` boolean — Sort null values first - `descending` boolean, required — Sort in descending order - `fieldId` string, required — Field ID to sort by - `filters` Filters, required - `tableCalculations` union - OrFilterGroup - `or` FilterGroupItem[], required — Array of filters or nested groups combined with OR logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - AndFilterGroup - `and` FilterGroupItem[], required — Array of filters or nested groups combined with AND logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - `metrics` union - OrFilterGroup - `or` FilterGroupItem[], required — Array of filters or nested groups combined with OR logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - AndFilterGroup - `and` FilterGroupItem[], required — Array of filters or nested groups combined with AND logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - `dimensions` union - OrFilterGroup - `or` FilterGroupItem[], required — Array of filters or nested groups combined with OR logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - AndFilterGroup - `and` FilterGroupItem[], required — Array of filters or nested groups combined with AND logic - union - FilterGroup — recursive - FilterRule - … - `id` string, required — Unique identifier for the filter group - `metrics` FieldId[], required — List of metric field IDs to include - `dimensions` FieldId[], required — List of dimension field IDs to include - `exploreName` string, required — The name of the explore to query - `usePreAggregateCache` boolean - `pivotConfiguration` PivotConfiguration - `pivotColumnsOrder` GroupByColumn[] — Declared order of the pivot-column dimensions (visible `groupByColumns` plus hidden `sortOnlyDimensions`) as they appear in the chart's `pivotConfig.columns`. `column_ranking` orders columns by this sequence so a hidden sort-only dim sorts at its DECLARED position — hiding a dim then leaves column order identical to when it was visible, instead of hoisting the hidden dim to the front of the ORDER BY. Passthrough (hidden, non-sort) dims are excluded since they don't drive sort. When omitted, ordering falls back to hoisting sort-only sort targets to the front (legacy behavior). - `reference` string, required - `passthroughDimensions` GroupByColumn[] — Hidden pivot-column dimensions that are NOT sort targets but still need their values carried through the SQL pipeline so that other fields' `richText` / `image:` templates can reference them via `row...raw`. They participate in `group_by_query` SELECT and GROUP BY (same as sortOnlyDimensions) but do NOT affect `column_ranking` ORDER BY (they don't drive sort). Without this bucket the dim would be dropped entirely from the query and `row.*.raw` references would silently resolve to undefined. - `reference` string, required - `sortOnlyDimensions` GroupByColumn[] — Dimensions referenced by ORDER BY but NOT spread into pivot columns. Used when a user hides a dim that's part of `pivotConfig.columns` and has a sort entry on it: the dim still ranks column order via the GROUP BY / ORDER BY pipeline, but it doesn't become a pivot column header level. Mirrors `sortOnlyColumns` (which serves the same purpose for metrics). - `reference` string, required - `sortOnlyColumns` ValuesColumn[] — Metrics/table calculations needed for sort anchor CTEs but not for display. These are merged into valuesColumns for SQL generation in PivotQueryBuilder, but excluded from pivotDetails so they don't appear as chart series. - `aggregation` 'sum' | 'count' | 'avg' | 'min' | 'max' | 'any', required - `reference` string, required - `metricsAsRows` boolean — When true, metrics are displayed as rows instead of columns. This affects column limit calculation - when metrics are rows, we don't need to divide the column limit by the number of metrics. Defaults to false for backward compatibility (SQL runner behavior). - `sortBy` VizSortBy[] - `pivotValues` PivotSortAnchor[] — Pins the row-sort anchor to a specific pivot column. - `value` union, required - string - number, double - boolean - `reference` string, required - `nullsFirst` boolean - `direction` 'ASC' | 'DESC', required - `reference` string, required - `groupByColumns` GroupByColumn[] - `reference` string, required - `valuesColumns` ValuesColumn[], required - `aggregation` 'sum' | 'count' | 'avg' | 'min' | 'max' | 'any', required - `reference` string, required - `indexColumn` union - PivotIndexColum - `type` 'time' | 'category', required - `reference` string, required - PivotIndexColum[] - `type` 'time' | 'category', required - `reference` string, required - `parameters` ParametersValuesMap — Construct a type with a set of properties K of type T ## Response `200` Success - object - `results` ApiCompiledQueryResults, required - `compilationErrors` string[] - `parameterReferences` string[], required - `pivotQuery` string - `query` string, required - `status` 'ok', required ## Other responses - `default` — Error --- [API](https://skmtc.dev/lightdash/apis/lightdash-api.md) · [All operations](https://skmtc.dev/lightdash/apis/lightdash-api/llms.txt) · [OpenAPI document](https://skmtc-service-production.skmtc.workers.dev/v1/apis/lightdash/lightdash-api/revisions/40c933755c9a/schema)