Dimension Reference Resolver

Build a utility that resolves variable references in dimension SQL expressions for a Business Intelligence system. The utility should handle cross-table field references and validate circular dependencies.

Capabilities

Variable reference resolution

Resolves field references in SQL expressions using the ${table.field} syntax.

• Given SQL "CONCAT(${users.first_name}, ' ', ${users.last_name})" and available fields, returns SQL with resolved table references @test
• Given SQL "${orders.amount} * 1.1" with a numeric field reference, returns SQL with the correct table reference resolved @test
• Given SQL with no variable references "SELECT 1", returns the SQL unchanged @test

Cross-table reference resolution

Resolves references that span multiple tables in a data model.

• Given dimension SQL "${orders.total}" that references a field from the orders table when current table is users, resolves the reference correctly @test
• Given multiple cross-table references in one expression "${orders.amount} + ${payments.amount}", resolves both references correctly @test

Circular reference detection

Prevents infinite loops when dimensions reference each other.

• Given dimension A references dimension B, and dimension B references dimension A, throws an error indicating circular dependency @test
• Given dimension A references B, B references C, and C references A, throws an error indicating circular dependency chain @test

Error handling

Validates references and provides clear error messages.

• Given a reference to a non-existent field "${users.invalid_field}", throws an error indicating the field does not exist @test
• Given a reference to a non-existent table "${invalid_table.field}", throws an error indicating the table does not exist @test

Implementation

@generates

API

interface Field {
  name: string;
  table: string;
  sql?: string;
}

interface DimensionContext {
  fields: Map<string, Field>;
  currentTable: string;
}

/**
 * Resolves variable references in dimension SQL expressions.
 *
 * @param sql - SQL expression containing variable references in ${table.field} format
 * @param context - Context containing available fields and current table
 * @param visitedFields - Set of field references visited (used for circular dependency detection)
 * @returns Resolved SQL expression with variable references replaced
 * @throws Error if circular dependency detected or invalid reference found
 */
export function resolveDimensionReferences(
  sql: string,
  context: DimensionContext,
  visitedFields?: Set<string>
): string;

/**
 * Extracts all variable references from a SQL expression.
 *
 * @param sql - SQL expression to parse
 * @returns Array of references in format "table.field"
 */
export function extractReferences(sql: string): string[];

Dependencies { .dependencies }

@lightdash/common { .dependency }

Provides core types and utilities for the Lightdash BI platform.

@satisfied-by