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

Working with Data

This guide shows you how to format, transform, and manipulate data from Lightdash queries. For detailed API documentation, see Data Utilities and Formatting Utilities.

Formatting Query Results

Format All Rows

Format query results for display in your UI:

import { formatRows, getItemMap, type MetricQueryResponse } from "@lightdash/common";

function displayResults(response: MetricQueryResponse, explore: Explore) {
  // Create item map with all fields
  const itemsMap = getItemMap(
    explore,
    response.metricQuery.additionalMetrics,
    response.metricQuery.tableCalculations,
    response.metricQuery.customDimensions
  );

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

  // Display formatted data
  formattedRows.forEach(row => {
    console.log(row);
  });
}

Format Individual Values

Format specific field values:

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

// Format a currency value
const revenueField = itemsMap["orders_total_revenue"];
const formattedRevenue = formatItemValue(revenueField, 1234.56);
// Returns: "$1,234.56"

// Format a date
const dateField = itemsMap["orders_created_at"];
const formattedDate = formatItemValue(dateField, new Date("2024-01-15"));
// Returns: "2024-01-15"

// Format a percentage
const rateField = itemsMap["conversion_rate"];
const formattedRate = formatItemValue(rateField, 0.156);
// Returns: "15.6%"

Format by Type

Use type-specific formatters:

import { formatDate, formatTimestamp, formatNumberValue } from "@lightdash/common";

// Format dates
const date = new Date("2024-01-15T14:30:00Z");
formatDate(date, TimeFrames.DAY);
// Returns: "2024-01-15"

// Format timestamps
formatTimestamp(date, TimeFrames.HOUR, false);
// Returns: "2024-01-15 14:00:00"

// Format numbers
formatNumberValue(1234567.89, Format.USD);
// Returns: "$1,234,567.89"

Working with Items Maps

Create an Items Map

Combine all queryable items into a single map:

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

const itemsMap: ItemsMap = getItemMap(
  explore,
  additionalMetrics,      // Ad-hoc metrics
  tableCalculations,      // Post-query calculations
  customDimensions        // Runtime dimensions
);

// O(1) field lookup
const field = itemsMap["orders_customer_id"];

Get Specific Item Types

Extract specific types of items:

import {
  getDimensionsFromItemsMap,
  getMetricsFromItemsMap,
  getTableCalculationsFromItemsMap,
} from "@lightdash/common";

// Get only dimensions
const dimensions = getDimensionsFromItemsMap(itemsMap);

// Get only metrics
const metrics = getMetricsFromItemsMap(itemsMap);

// Get only table calculations
const calculations = getTableCalculationsFromItemsMap(itemsMap);

// Get filterable dimensions
const filterableDims = getFilterableDimensionsFromItemsMap(itemsMap);

Identify Items in Query

Find all items referenced in a query:

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

// Get all field IDs used in query
const allItemIds = itemsInMetricQuery(metricQuery);
// Returns: ["orders_customer_id", "orders_count", "orders_total_revenue", ...]

// Check if all items exist
const missingItems = allItemIds.filter(id => !itemsMap[id]);
if (missingItems.length > 0) {
  console.warn("Missing items:", missingItems);
}

Data Transformation

Get Result Value Arrays

Extract values for charting:

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

const result = getResultValueArray(
  rows,
  false,              // preferRaw: use formatted values
  true,               // calculateMinAndMax: compute range
  true                // excludeNulls: skip null values
);

console.log("Values:", result.results);
console.log("Min:", result.minsAndMaxes?.min);
console.log("Max:", result.minsAndMaxes?.max);

Pivot Query Results

Transform rows into pivot table format:

import { pivotQueryResults, type PivotData } from "@lightdash/common";

const pivotData: PivotData = pivotQueryResults({
  rows,
  indexColumnIds: ["orders_month"],           // Row headers
  valuesColumnIds: ["orders_total_revenue"],  // Values to aggregate
  pivotColumnIds: ["orders_region"],          // Column headers
  metricsAsRows: false,
  columnOrder: [],
  itemsMap,
});

Custom Formatting

Apply Custom Format

Use custom format options:

import { applyCustomFormat, CustomFormatType, NumberSeparator } from "@lightdash/common";

const field = {
  ...baseField,
  formatOptions: {
    type: CustomFormatType.CURRENCY,
    currency: "USD",
    round: 2,
    separator: NumberSeparator.COMMA_PERIOD,
  },
};

const formatted = applyCustomFormat(field, 1234567.89, field.formatOptions);
// Returns: "$1,234,567.89"

Format with Compact Notation

Display large numbers compactly:

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

const field = {
  formatOptions: {
    type: CustomFormatType.NUMBER,
    compact: Compact.MILLIONS,
    round: 1,
  },
};

applyCustomFormat(field, 1500000, field.formatOptions);
// Returns: "1.5M"

applyCustomFormat(field, 2340000000, field.formatOptions);
// Returns: "2.3B" (auto-scales)

Custom Format Expression

Use format expressions for advanced formatting:

const field = {
  formatOptions: {
    type: CustomFormatType.CUSTOM,
    custom: "${value} units",  // Custom template
  },
};

applyCustomFormat(field, 42, field.formatOptions);
// Returns: "42 units"

Handling Raw Values

Format Raw Values

Convert raw database values with timezone handling:

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

// Format a timestamp with UTC conversion
const rawTimestamp = "2024-01-15T14:30:00Z";
const formatted = formatRawValue(timestampField, rawTimestamp);
// Returns: Date object in UTC

// Format raw rows
const rawRows = [
  { customer_id: 1, created_at: "2024-01-15T14:30:00Z" },
  { customer_id: 2, created_at: "2024-01-16T10:00:00Z" },
];

const formattedRows = formatRawRows(rawRows, itemsMap);
// Applies formatting to all fields

Data Validation

Validate Field Values

Check if values are valid for a field:

import { isField, isDimension, isMetric } from "@lightdash/common";

// Type guards
if (isField(item)) {
  console.log("Valid field:", item.name);
}

if (isDimension(field)) {
  console.log("Dimension:", field.type);
}

if (isMetric(field)) {
  console.log("Metric:", field.type);
}

Check for Null Values

Handle null and undefined values:

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

// Filter out null values
const validValues = values.filter(isNotNull);

// Type guard narrows type
if (isNotNull(value)) {
  // TypeScript knows value is not null
  console.log(value.toString());
}

String Operations

Generate Field IDs

Convert field references to IDs:

import { getItemId, getItemLabel } from "@lightdash/common";

// Get field ID
const fieldId = getItemId(field);
// Returns: "orders_customer_id"

// Get display label
const label = getItemLabel(field);
// Returns: "Customer ID"

Name Transformations

Transform names and labels:

import { friendlyName, snakeCaseName } from "@lightdash/common";

// Convert to friendly name
friendlyName("customer_id");
// Returns: "Customer Id"

friendlyName("totalRevenue");
// Returns: "Total Revenue"

// Convert to snake_case
snakeCaseName("Customer Name");
// Returns: "customer_name"

Data Utilities

Array Operations

Work with arrays efficiently:

import {
  hasIntersection,
  toggleArrayValue,
  replaceStringInArray,
} from "@lightdash/common";

// Check for common elements
hasIntersection(["a", "b", "c"], ["c", "d", "e"]);
// Returns: true

// Toggle value in array
toggleArrayValue(["a", "b", "c"], "b");
// Returns: ["a", "c"]

toggleArrayValue(["a", "c"], "b");
// Returns: ["a", "c", "b"]

// Replace value in array
replaceStringInArray(["a", "b", "c"], "b", "x");
// Returns: ["a", "x", "c"]

Object Operations

Clean and compare objects:

import { removeEmptyProperties, deepEqual } from "@lightdash/common";

// Remove empty properties
const obj = { a: 1, b: null, c: undefined, d: "" };
removeEmptyProperties(obj);
// Returns: { a: 1, d: "" }

// Deep equality check
const obj1 = { a: 1, b: { c: 2 } };
const obj2 = { a: 1, b: { c: 2 } };
deepEqual(obj1, obj2);
// Returns: true

Color Utilities

Work with Colors

Validate and transform colors:

import {
  isHexCodeColor,
  getInvalidHexColors,
  cleanColorArray,
  hexToRGB,
  getReadableTextColor,
} from "@lightdash/common";

// Validate hex color
isHexCodeColor("#FF0000");  // true
isHexCodeColor("red");      // false

// Find invalid colors
const colors = ["#FF0000", "red", "#00FF00"];
getInvalidHexColors(colors);
// Returns: ["red"]

// Clean color array
cleanColorArray(colors);
// Returns: ["#FF0000", "#00FF00"]

// Convert to RGB
hexToRGB("#FF0000", 0.5);
// Returns: "rgba(255, 0, 0, 0.5)"

// Get readable text color
getReadableTextColor("#FF0000");
// Returns: "white" (for dark backgrounds)

getReadableTextColor("#FFFF00");
// Returns: "black" (for light backgrounds)

Complete Example

Putting it all together:

import {
  getItemMap,
  formatRows,
  getResultValueArray,
  itemsInMetricQuery,
  type MetricQueryResponse,
} from "@lightdash/common";

function processQueryResults(
  response: MetricQueryResponse,
  explore: Explore
) {
  // 1. Create item map
  const itemsMap = getItemMap(
    explore,
    response.metricQuery.additionalMetrics,
    response.metricQuery.tableCalculations,
    response.metricQuery.customDimensions
  );

  // 2. Validate all items exist
  const allItems = itemsInMetricQuery(response.metricQuery);
  const missingItems = allItems.filter(id => !itemsMap[id]);

  if (missingItems.length > 0) {
    console.warn("Missing items:", missingItems);
  }

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

  // 4. Extract values for charting
  const valueArrays = response.metricQuery.metrics.map(metricId => {
    const values = response.rows.map(row => row[metricId]);
    return getResultValueArray(values, false, true, true);
  });

  return {
    formattedRows,
    valueArrays,
    itemsMap,
  };
}

Next Steps

Learn about Building Queries to create queries
See Visualization to chart your data
Read Formatting API for more options
Explore Data Utilities API for complete reference

Version

Tile

Files