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

Compilation Guide

This guide shows you how to compile dbt models into Lightdash explores using the ExploreCompiler. For detailed API documentation, see Compiler API.

Overview

The compiler transforms dbt model definitions into executable Lightdash explores:

Input: dbt manifest with model definitions
Process: Resolve references, compile SQL, validate
Output: Compiled explores ready for querying

Quick Start

Basic Compilation

import { ExploreCompiler, type UncompiledExplore, type Explore } from "@lightdash/common";

// Create compiler with warehouse SQL builder
const compiler = new ExploreCompiler(warehouseSqlBuilder);

// Compile an explore
const explore: Explore = compiler.compileExplore(uncompiledExplore);

console.log(`Compiled explore: ${explore.name}`);
console.log(`Tables: ${Object.keys(explore.tables).length}`);
console.log(`Fields: ${getFields(explore).length}`);

Understanding Compilation

What Gets Compiled?

The compiler processes:

Dimension SQL - Field references like ${table.field} → compiled SQL
Metric SQL - Aggregations with references → compiled SQL
Join conditions - Table relationships → compiled SQL
Filters - WHERE clauses with parameters → compiled SQL
Custom dimensions - Runtime dimensions → compiled SQL

Compilation Flow

UncompiledExplore
  ↓
ExploreCompiler.compileExplore()
  ↓
├─ Compile base table
│  ├─ Compile dimensions
│  ├─ Compile metrics
│  └─ Resolve references
├─ Compile joined tables
│  └─ Compile join conditions
└─ Validate parameters
  ↓
CompiledExplore (ready for queries)

Compiling Dimensions

Basic Dimension Compilation

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

const compiler = new ExploreCompiler(warehouseSqlBuilder);

// Compile a dimension
const compiledDimension = compiler.compileDimension(
  dimension,
  tables,
  availableParameters
);

console.log("Original SQL:", dimension.sql);
console.log("Compiled SQL:", compiledDimension.compiledSql);
console.log("References:", compiledDimension.tablesReferences);

Dimension with References

// Dimension definition
const dimension = {
  name: "full_name",
  sql: "${customers.first_name} || ' ' || ${customers.last_name}",
  type: DimensionType.STRING,
};

// After compilation
// compiledSql: "customers.first_name || ' ' || customers.last_name"
// tablesReferences: ["customers"]

Time-Based Dimensions

Create dimensions with specific time granularity:

import {
  createDimensionWithGranularity,
  DateGranularity,
} from "@lightdash/common";

// Create a weekly dimension from a timestamp
const weeklyDimension = createDimensionWithGranularity(
  "created_at_week",
  explore.tables["orders"].dimensions["created_at"],
  explore,
  warehouseSqlBuilder,
  DateGranularity.WEEK,
  availableParameters
);

Compiling Metrics

Basic Metric Compilation

const compiledMetric = compiler.compileMetric(
  metric,
  tables,
  availableParameters
);

console.log("Metric type:", metric.type);
console.log("Compiled SQL:", compiledMetric.compiledSql);

Metric with Dimension Reference

// Metric definition
const metric = {
  name: "total_revenue",
  type: MetricType.SUM,
  sql: "${orders.amount}",
};

// After compilation
// compiledSql: "SUM(orders.amount)"
// tablesReferences: ["orders"]

Custom SQL Metrics

// Complex metric
const metric = {
  name: "average_order_value",
  type: MetricType.NUMBER,
  sql: "SUM(${orders.total_amount}) / COUNT(DISTINCT ${orders.order_id})",
};

// Compiler resolves all references and generates proper SQL

Compiling Joins

Join Compilation

const compiledJoin = compiler.compileJoin(
  join,
  tables,
  availableParameters
);

console.log("Join SQL:", compiledJoin.compiledSqlOn);
console.log("References:", compiledJoin.tablesReferences);

Join Definition

const join = {
  table: "customers",
  sqlOn: "${orders.customer_id} = ${customers.customer_id}",
  type: "left",
};

// After compilation
// compiledSqlOn: "orders.customer_id = customers.customer_id"

Working with Parameters

Parameter References

Extract parameter references from SQL:

import { getParameterReferences, validateParameterReferences } from "@lightdash/common";

const sql = "WHERE date >= ${lightdash.parameters.start_date}";

// Extract parameters
const params = getParameterReferences(sql);
// Returns: ["start_date"]

// Validate parameters exist
validateParameterReferences("orders", params, availableParameters);

Available Parameters

Get all available parameters:

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

const availableParams = getAvailableParameterNames(
  projectParameters,
  exploreParameters
);

console.log("Available parameters:", availableParams);

Custom Dimensions

Compile Custom SQL Dimensions

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

const customDimension: CustomSqlDimension = {
  id: "custom_1",
  name: "order_size",
  type: CustomDimensionType.SQL,
  table: "orders",
  sql: "CASE WHEN ${orders.amount} > 100 THEN 'large' ELSE 'small' END",
  dimensionType: DimensionType.STRING,
};

const compiled = compiler.compileCustomDimension(
  customDimension,
  tables,
  availableParameters
);

console.log("Compiled SQL:", compiled.compiledSql);

Reference Resolution

Parse Field References

import { getParsedReference, getAllReferences, parseAllReferences } from "@lightdash/common";

const sql = "${orders.amount} * ${products.price}";

// Get all reference strings
const refStrings = getAllReferences(sql);
// Returns: ["orders.amount", "products.price"]

// Parse into structured references
const refs = parseAllReferences(sql, "orders");
// Returns: [
//   { refTable: "orders", refName: "amount" },
//   { refTable: "products", refName: "price" }
// ]

// Parse single reference
const ref = getParsedReference("customer_id", "orders");
// Returns: { refTable: "orders", refName: "customer_id" }

Resolve Dimension References

const resolved = compiler.compileDimensionReference(
  "customers.name",
  tables,
  "orders"
);

console.log("Resolved SQL:", resolved.sql);
console.log("Table references:", resolved.tablesReferences);

Error Handling

Handle Compilation Errors

import { ExploreCompiler, isExploreError } from "@lightdash/common";

try {
  const compiler = new ExploreCompiler(warehouseSqlBuilder);
  const explore = compiler.compileExplore(uncompiledExplore);

  console.log("Compilation successful");
} catch (error) {
  if (error instanceof Error) {
    console.error("Compilation failed:", error.message);

    // Check for specific error types
    if (error.message.includes("Circular dependency")) {
      console.error("Found circular dependency in model");
    } else if (error.message.includes("Unknown field reference")) {
      console.error("Invalid field reference in SQL");
    }
  }
}

Validate Parameter References

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

const paramRefs = ["start_date", "end_date", "threshold"];
const available = ["start_date", "end_date"];

try {
  validateParameterReferences("orders", paramRefs, available);
} catch (error) {
  console.error("Missing parameter: threshold");
}

Compilation Reports

Generate Compilation Statistics

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

const report = calculateCompilationReport({ explores });

console.log(`Total explores: ${report.totalExploresCount}`);
console.log(`Successful: ${report.successfulExploresCount}`);
console.log(`Failed: ${report.errorExploresCount}`);
console.log(`Total metrics: ${report.metricsCount}`);
console.log(`Total dimensions: ${report.dimensionsCount}`);

if (report.exploresWithErrors.length > 0) {
  console.log("Compilation errors:");
  report.exploresWithErrors.forEach((error) => {
    console.log(`  ${error.name}: ${error.errors.length} errors`);
  });
}

Complete Compilation Example

End-to-End Compilation

import {
  ExploreCompiler,
  calculateCompilationReport,
  getParameterReferences,
  validateParameterReferences,
  type UncompiledExplore,
} from "@lightdash/common";

async function compileProject(
  explores: UncompiledExplore[],
  warehouseClient: WarehouseClient
) {
  const compiler = new ExploreCompiler(warehouseClient);
  const results = [];

  for (const uncompiledExplore of explores) {
    try {
      // Compile explore
      const explore = compiler.compileExplore(uncompiledExplore);

      // Validate each table
      for (const table of Object.values(explore.tables)) {
        // Validate dimensions
        for (const dimension of Object.values(table.dimensions)) {
          const refs = getParameterReferences(dimension.compiledSql);
          console.log(`Dimension ${dimension.name} uses params:`, refs);
        }

        // Validate metrics
        for (const metric of Object.values(table.metrics)) {
          const refs = getParameterReferences(metric.compiledSql);
          console.log(`Metric ${metric.name} uses params:`, refs);
        }
      }

      results.push(explore);
    } catch (error) {
      console.error(`Failed to compile ${uncompiledExplore.name}:`, error);
      results.push({
        name: uncompiledExplore.name,
        errors: [{ type: "COMPILE_ERROR", message: error.message }],
      });
    }
  }

  // Generate report
  const report = calculateCompilationReport({ explores: results });
  console.log("\nCompilation Report:");
  console.log(`  Success: ${report.successfulExploresCount}/${report.totalExploresCount}`);
  console.log(`  Metrics: ${report.metricsCount}`);
  console.log(`  Dimensions: ${report.dimensionsCount}`);

  return { explores: results, report };
}

Warehouse-Specific Compilation

Adapter Types

Different warehouses use different SQL dialects:

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

// Supported warehouses
SupportedDbtAdapter.BIGQUERY
SupportedDbtAdapter.POSTGRES
SupportedDbtAdapter.SNOWFLAKE
SupportedDbtAdapter.REDSHIFT
SupportedDbtAdapter.DATABRICKS
SupportedDbtAdapter.TRINO

Warehouse SQL Builder

The compiler uses warehouse-specific SQL generation:

const compiler = new ExploreCompiler(warehouseClient);

// Warehouse client provides:
// - Field quote character (` for BigQuery, " for Postgres)
// - String quote character (')
// - SQL dialect specifics
// - Date/time function syntax

Tips and Best Practices

1. Cache Compiled Explores

Compilation is expensive - cache results:

const exploreCache = new Map<string, Explore>();

function getCompiledExplore(name: string): Explore {
  if (exploreCache.has(name)) {
    return exploreCache.get(name)!;
  }

  const explore = compiler.compileExplore(uncompiledExplore);
  exploreCache.set(name, explore);
  return explore;
}

2. Validate Before Compilation

Check for issues before compiling:

// Validate parameter names
import { validateParameterNames } from "@lightdash/common";

const result = validateParameterNames(parameters);
if (result.isInvalid) {
  console.error("Invalid parameters:", result.invalidParameters);
}

3. Handle Circular Dependencies

Avoid circular references in your models:

// Avoid
dimension_a: { sql: "${table.dimension_b}" }
dimension_b: { sql: "${table.dimension_a}" }

// Good
dimension_a: { sql: "${TABLE}.column_a" }
dimension_b: { sql: "${TABLE}.column_b" }

4. Use Explicit Table References

Be explicit about table references:

// Good
sql: "${orders.customer_id} = ${customers.customer_id}"

// Less clear
sql: "${customer_id} = ${customers.customer_id}"

Next Steps

Learn about Building Queries to use compiled explores
See Compiler API for complete reference
Read about DBT Integration for manifest processing
Explore Warehouse Types for adapter specifics

Version

Tile

Files

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

Compilation Guide

Overview

Quick Start

Basic Compilation

Understanding Compilation

What Gets Compiled?

Compilation Flow

Compiling Dimensions

Basic Dimension Compilation

Dimension with References

Time-Based Dimensions

Compiling Metrics

Basic Metric Compilation

Metric with Dimension Reference

Custom SQL Metrics

Compiling Joins

Join Compilation

Join Definition

Working with Parameters

Parameter References

Available Parameters

Custom Dimensions

Compile Custom SQL Dimensions

Reference Resolution

Parse Field References

Resolve Dimension References

Error Handling

Handle Compilation Errors

Validate Parameter References

Compilation Reports

Generate Compilation Statistics

Complete Compilation Example

End-to-End Compilation

Warehouse-Specific Compilation

Adapter Types

Warehouse SQL Builder

Tips and Best Practices

1. Cache Compiled Explores

2. Validate Before Compilation

3. Handle Circular Dependencies

4. Use Explicit Table References

Next Steps

compilation.mddocs/guides/