DBT Integration

/**
 * Validates DBT manifests and models against expected schemas
 */
class ManifestValidator {
  /**
   * Create a validator for a specific DBT manifest version
   * @param manifestVersion - DBT manifest version (v7, v8, v9, v10, v11, v12, v20)
   */
  constructor(manifestVersion: DbtManifestVersion);

  /**
   * Validate a DBT model node against the Lightdash schema
   * @param model - Raw DBT model node to validate
   * @returns Tuple of [isValid, errorMessage] where errorMessage is undefined if valid
   */
  isModelValid(model: DbtRawModelNode): [true, undefined] | [false, string];

  /**
   * Validate a DBT metric against the DBT schema
   * @param metric - DBT metric to validate
   * @returns Tuple of [isValid, errorMessage] where errorMessage is undefined if valid
   */
  isDbtMetricValid(metric: DbtMetric): [true, undefined] | [false, string];

  /**
   * Validate data against a compiled AJV validator
   * @param validator - AJV validator function
   * @param data - Data to validate
   * @returns Tuple of [isValid, errorMessage] where errorMessage is undefined if valid
   */
  static isValid(
    validator: ValidateFunction<AnyType>,
    data: AnyType
  ): [true, undefined] | [false, string];

  /**
   * Format AJV validation errors into a readable string
   * @param validator - AJV validator function with errors
   * @returns Formatted error message
   */
  static formatAjvErrors(validator: AnyValidateFunction): string;

  /**
   * Get a compiled AJV validator for a schema reference
   * @param schemaRef - Schema reference URL
   * @returns AJV validator function
   * @throws ParseError if schema not found
   */
  static getValidator<T>(schemaRef: string): ValidateFunction<T>;
}

import { ManifestValidator } from '@lightdash/common';

// Create a validator for a specific DBT version
const validator = new ManifestValidator('v10');

// Validate a model
const [isValid, error] = validator.isModelValid(dbtModel);
if (!isValid) {
  console.error('Model validation failed:', error);
}

// Validate a metric
const [isMetricValid, metricError] = validator.isDbtMetricValid(dbtMetric);
if (!isMetricValid) {
  console.error('Metric validation failed:', metricError);
}

// Use static methods for custom validation
const customValidator = ManifestValidator.getValidator<DbtModelNode>(
  'https://schemas.lightdash.com/lightdash/v10.json#/definitions/LightdashCompiledModelNode'
);

const [customValid, customError] = ManifestValidator.isValid(
  customValidator,
  myCustomModel
);

if (!customValid) {
  console.error('Custom validation failed:', customError);
}

/**
 * Class to edit DBT schema files (YAML) programmatically
 * Methods can be chained and the final schema can be obtained as a string
 */
class DbtSchemaEditor {
  /**
   * Create a new schema editor
   * @param doc - YAML document string to parse
   * @param filename - Filename for error messages
   * @param dbtVersion - DBT version for version-specific behavior
   */
  constructor(
    doc?: string,
    filename?: string,
    dbtVersion?: SupportedDbtVersions
  );

  /**
   * Check if DBT version is 1.10 or higher
   * @returns true if version is 1.10+
   */
  isDbtVersion110OrHigher(): boolean;

  /**
   * Find a model by name (returns undefined if not found)
   * @param name - Model name
   * @returns YAML map node or undefined
   */
  findModelByName(name: string): YAMLMap<unknown, unknown> | undefined;

  /**
   * Get a model by name (throws if not found)
   * @param name - Model name
   * @returns YAML map node
   * @throws Error if model not found
   */
  getModelByName(name: string): YAMLMap<unknown, unknown>;

  /**
   * Find a column by name in a model (returns undefined if not found)
   * @param modelName - Model name
   * @param columnName - Column name
   * @returns YAML map node or undefined
   */
  findColumnByName(
    modelName: string,
    columnName: string
  ): YAMLMap<unknown, unknown> | undefined;

  /**
   * Get a column by name in a model (throws if not found)
   * @param modelName - Model name
   * @param columnName - Column name
   * @returns YAML map node
   * @throws Error if column not found
   */
  getColumnByName(
    modelName: string,
    columnName: string
  ): YAMLMap<unknown, unknown>;

  /**
   * Check if schema has any models
   * @returns true if models exist
   */
  hasModels(): boolean;

  /**
   * Get all columns for a model
   * @param modelName - Model name
   * @returns Array of column objects or undefined
   */
  getModelColumns(modelName: string): YamlColumn[] | undefined;

  /**
   * Add a new model to the schema
   * @param model - Model configuration to add
   * @returns this for method chaining
   */
  addModel(model: YamlModel): DbtSchemaEditor;

  /**
   * Add a new column to a model
   * @param modelName - Model name
   * @param column - Column configuration to add
   * @returns this for method chaining
   */
  addColumn(modelName: string, column: YamlColumn): DbtSchemaEditor;

  /**
   * Remove columns from a model
   * @param modelName - Model name
   * @param columnNames - Array of column names to remove
   * @returns this for method chaining
   */
  removeColumns(modelName: string, columnNames: string[]): DbtSchemaEditor;

  /**
   * Add custom metrics to a model
   * @param customMetricsToAdd - Array of custom metrics to add
   * @returns this for method chaining
   */
  addCustomMetrics(customMetricsToAdd: AdditionalMetric[]): DbtSchemaEditor;

  /**
   * Add custom dimensions to a model
   * @param customDimensionsToAdd - Array of custom dimensions to add
   * @param warehouseClient - Warehouse client for SQL generation
   * @returns this for method chaining
   */
  addCustomDimensions(
    customDimensionsToAdd: CustomDimension[],
    warehouseClient: WarehouseClient
  ): DbtSchemaEditor;

  /**
   * Add a single custom dimension
   * @param customDimension - Custom dimension to add
   * @param warehouseClient - Warehouse client for SQL generation
   * @returns this for method chaining
   */
  addCustomDimension(
    customDimension: CustomDimension,
    warehouseClient: WarehouseClient
  ): DbtSchemaEditor;

  /**
   * Update a column's properties
   * @param options - Update options with model, column, and properties
   * @returns this for method chaining
   */
  updateColumn(options: {
    modelName: string;
    columnName: string;
    properties?: Record<string, unknown>;
  }): DbtSchemaEditor;

  /**
   * Convert the document to a YAML string
   * @param options - Formatting options
   * @returns YAML string
   */
  toString(options?: { quoteChar?: "'" | '"' }): string;

  /**
   * Convert the document to a JavaScript object
   * @returns JSON representation as string
   */
  toJS(): string;
}

import { DbtSchemaEditor } from '@lightdash/common';

// Create an editor from existing YAML
const yamlContent = `
version: 2
models:
  - name: customers
    columns:
      - name: customer_id
        description: Primary key
`;

const editor = new DbtSchemaEditor(yamlContent, 'schema.yml', 'v1.5.0');

// Add custom metrics
const customMetrics: AdditionalMetric[] = [
  {
    name: 'total_revenue',
    label: 'Total Revenue',
    type: MetricType.SUM,
    sql: '${TABLE}.revenue',
    table: 'customers',
  },
];

// Add custom dimensions
const customDimensions: CustomDimension[] = [
  {
    id: 'revenue_bucket',
    name: 'revenue_bucket',
    table: 'customers',
    type: CustomDimensionType.BIN,
    dimensionId: 'customers.revenue',
    binType: BinType.FIXED_NUMBER,
    binNumber: 5,
  },
];

// Chain methods to edit the schema
const updatedYaml = editor
  .addCustomMetrics(customMetrics)
  .addCustomDimensions(customDimensions, warehouseClient)
  .addColumn('customers', {
    name: 'created_at',
    description: 'Customer creation timestamp',
  })
  .updateColumn({
    modelName: 'customers',
    columnName: 'customer_id',
    properties: {
      meta: {
        lightdash: {
          dimension: {
            hidden: false,
          },
        },
      },
    },
  })
  .toString();

console.log(updatedYaml);

enum DbtManifestVersion {
  V7 = 'v7',
  V8 = 'v8',
  V9 = 'v9',
  V10 = 'v10',
  V11 = 'v11',
  V12 = 'v12',
  V20 = 'v20',
}

interface DbtRawModelNode {
  unique_id: string;
  name: string;
  resource_type: 'model' | 'source' | 'seed' | 'snapshot';
  package_name: string;
  path: string;
  original_file_path: string;
  database: string;
  schema: string;
  alias?: string;
  columns: Record<string, DbtModelColumn>;
  meta: Record<string, unknown>;
  config?: DbtModelConfig;
  tags?: string[];
  description?: string;
  compiled?: boolean;
  compiled_sql?: string;
  raw_sql?: string;
  depends_on?: {
    nodes?: string[];
    macros?: string[];
  };
}

interface DbtModelColumn {
  name: string;
  description?: string;
  meta?: Record<string, unknown>;
  data_type?: string;
  tags?: string[];
  tests?: unknown[];
}

interface DbtModelConfig {
  enabled?: boolean;
  materialized?: 'table' | 'view' | 'incremental' | 'ephemeral';
  tags?: string[];
  pre_hook?: unknown[];
  post_hook?: unknown[];
  [key: string]: unknown;
}

interface DbtModelNode {
  uniqueId: string;
  name: string;
  resourceType: 'model' | 'source' | 'seed' | 'snapshot';
  packageName: string;
  path: string;
  originalFilePath: string;
  database: string;
  schema: string;
  alias?: string;
  columns: Record<string, DbtModelColumn>;
  meta: Record<string, unknown>;
  config?: DbtModelConfig;
  tags?: string[];
  description?: string;
}

interface DbtMetric {
  unique_id: string;
  name: string;
  label?: string;
  description?: string;
  type: DbtMetricType;
  sql?: string;
  timestamp?: string;
  time_grains?: string[];
  dimensions?: string[];
  filters?: DbtMetricFilter[];
  meta?: Record<string, unknown>;
  tags?: string[];
  model?: string;
}

type DbtMetricType =
  | 'count'
  | 'count_distinct'
  | 'sum'
  | 'average'
  | 'min'
  | 'max'
  | 'derived';

interface DbtMetricFilter {
  field: string;
  operator: string;
  value: string | number;
}

enum SupportedDbtAdapter {
  BIGQUERY = 'bigquery',
  DATABRICKS = 'databricks',
  SNOWFLAKE = 'snowflake',
  REDSHIFT = 'redshift',
  POSTGRES = 'postgres',
  TRINO = 'trino',
  PRESTO = 'presto',
  ATHENA = 'athena',
  CLICKHOUSE = 'clickhouse',
  DREMIO = 'dremio',
  SYNAPSE = 'synapse',
  MOTHERDUCK = 'motherduck',
  DUCKDB = 'duckdb',
  MYSQL = 'mysql',
  ORACLE = 'oracle',
  MSSQL = 'mssql',
  VERTICA = 'vertica',
  SPARK = 'spark',
  HIVE = 'hive',
  IMPALA = 'impala',
  EXASOL = 'exasol',
  FIREBOLT = 'firebolt',
  SINGLESTORE = 'singlestore',
}

// From utils/dbt.ts
function parseDbtManifestVersion(manifest: unknown): DbtManifestVersion;
function isDbtVersion110OrHigher(version: SupportedDbtVersions): boolean;
function getLatestSupportDbtVersion(): SupportedDbtVersions;

import {
  parseDbtManifestVersion,
  isDbtVersion110OrHigher,
  SupportedDbtVersions,
} from '@lightdash/common';

// Parse manifest version
const version = parseDbtManifestVersion(manifest);
console.log(`DBT manifest version: ${version}`);

// Check version
if (isDbtVersion110OrHigher(SupportedDbtVersions.V1_10)) {
  // Use v1.10+ features
}

// From utils/convertCustomDimensionsToYaml.ts
function convertCustomDimensionsToYaml(
  customDimensions: CustomDimension[]
): string;

// From utils/convertCustomMetricsToYaml.ts
function convertCustomMetricsToYaml(
  additionalMetrics: AdditionalMetric[]
): string;

import {
  convertCustomDimensionsToYaml,
  convertCustomMetricsToYaml,
} from '@lightdash/common';

// Convert custom dimensions to YAML for dbt file
const dimensionsYaml = convertCustomDimensionsToYaml(customDimensions);

// Convert additional metrics to YAML
const metricsYaml = convertCustomMetricsToYaml(additionalMetrics);

// Write to dbt YAML file
const dbtYaml = `
version: 2
models:
  - name: my_model
    meta:
      lightdash:
        dimensions:
${dimensionsYaml}
        metrics:
${metricsYaml}
`;

import {
  ManifestValidator,
  type DbtRawModelNode,
  type DbtMetric,
  parseDbtManifestVersion,
} from '@lightdash/common';

async function validateDbtProject(manifest: unknown) {
  // Parse manifest version
  const version = parseDbtManifestVersion(manifest);
  console.log(`DBT version: ${version}`);

  // Create validator
  const validator = new ManifestValidator(version);

  // Validate all models
  const models = Object.values(manifest.nodes || {})
    .filter((node: any) => node.resource_type === 'model');

  const validationResults = models.map((model: DbtRawModelNode) => {
    const [isValid, error] = validator.isModelValid(model);
    return {
      model: model.name,
      valid: isValid,
      error,
    };
  });

  // Report validation results
  const invalidModels = validationResults.filter(r => !r.valid);
  if (invalidModels.length > 0) {
    console.error('Invalid models found:');
    invalidModels.forEach(({ model, error }) => {
      console.error(`  ${model}: ${error}`);
    });
  } else {
    console.log('All models valid');
  }

  // Validate metrics
  const metrics = Object.values(manifest.metrics || {});
  const metricResults = metrics.map((metric: DbtMetric) => {
    const [isValid, error] = validator.isDbtMetricValid(metric);
    return {
      metric: metric.name,
      valid: isValid,
      error,
    };
  });

  const invalidMetrics = metricResults.filter(r => !r.valid);
  if (invalidMetrics.length > 0) {
    console.error('Invalid metrics found:');
    invalidMetrics.forEach(({ metric, error }) => {
      console.error(`  ${metric}: ${error}`);
    });
  }

  return {
    modelsValid: invalidModels.length === 0,
    metricsValid: invalidMetrics.length === 0,
    errors: [...invalidModels, ...invalidMetrics],
  };
}

/**
 * Import JSON schemas for validation
 */
import {
  chartAsCodeSchema,
  dashboardAsCodeSchema,
  lightdashDbtYamlSchema,
  lightdashProjectConfigSchema,
  modelAsCodeSchema,
} from '@lightdash/common';

// External dependency - Install separately: npm install ajv
import Ajv from 'ajv';
import { chartAsCodeSchema } from '@lightdash/common';

const ajv = new Ajv();
const validate = ajv.compile(chartAsCodeSchema);

const chartConfig = {
  version: '1.0',
  name: 'My Chart',
  tableName: 'customers',
  metricQuery: {
    dimensions: ['customers.name'],
    metrics: ['customers.total_revenue'],
  },
  chartConfig: {
    type: 'cartesian',
    config: {
      layout: {
        xField: 'customers.name',
        yField: ['customers.total_revenue'],
      },
    },
  },
};

const isValid = validate(chartConfig);
if (!isValid) {
  console.error('Validation errors:', validate.errors);
}

version: 2
models:
  - name: customers
    meta:
      lightdash:
        joins:
          - join: orders
            sql_on: ${customers.customer_id} = ${orders.customer_id}
            relationship: one_to_many
    columns:
      - name: customer_id
        meta:
          lightdash:
            dimension:
              hidden: false
              label: Customer ID
              format: id
      - name: lifetime_value
        meta:
          lightdash:
            metric:
              type: sum
              format:
                type: currency
                currency: USD
              round: 2

/**
 * Validates that a dbt selector conforms to the expected format
 * @param selector - The dbt selector string to validate
 * @returns true if the selector is valid, false otherwise
 */
function validateDbtSelector(selector: string): boolean;

import { validateDbtSelector } from '@lightdash/common';

// Valid selectors
validateDbtSelector('tag:daily'); // true
validateDbtSelector('path:models/staging'); // true
validateDbtSelector('customers'); // true
validateDbtSelector('+orders'); // true
validateDbtSelector('orders+'); // true
validateDbtSelector('@source:jaffle_shop'); // true

// Invalid selectors (contain invalid characters)
validateDbtSelector('tag:daily!'); // false
validateDbtSelector('path|models'); // false
validateDbtSelector(''); // false