How Semantic Model Validation Works in DAT: A Deep Dive into PreBuildValidator

The PreBuildValidator in the DAT framework validates semantic model YAML definitions by executing dry-run SQL queries against the target database, checking syntax, data types, and dimension enum values before any code generation occurs.

When building a Dat project (dat build), the framework must ensure that every semantic model defined in YAML files is both syntactically valid and compatible with the target database schema. This process, known as semantic model validation, is orchestrated by the PreBuildValidator class located in dat-sdk/src/main/java/ai/dat/boot/PreBuildValidator.java. It runs before any code generation begins, providing fast feedback and preventing downstream runtime failures.

What Is Semantic Model Validation?

Semantic model validation is a pre-build safety mechanism that verifies five critical aspects of every semantic model:

1. Model SQL – the raw SQL query that defines the base data.
2. Semantic-Model SQL – the derived SQL after applying dimensions, measures, and filters.
3. Dimension enum values – optional enumerations that restrict dimension values.
4. Data-type correctness – declared data types match actual database column metadata.
5. Automatic data-type completion – fills missing data_type fields when auto-completion is enabled.

If any check fails, the validator aggregates all errors into a comprehensive ValidationException and aborts the build.

The PreBuildValidator Execution Flow

The PreBuildValidator.validate() method follows a strict nine-step pipeline to ensure thorough semantic model validation.

Step 1: Project Loading and Configuration

The validator receives a DatProject, its file system Path, and user-defined template variables. It first validates required and optional configuration options via FactoryUtil.validateFactoryOptions. This ensures that database connection parameters and validation flags are properly set before proceeding.

Step 2: Semantic Model Resolution

All semantic model definitions are read from the in-memory cache (ChangeSemanticModelsCacheUtil). Each model is deserialized using Jackson, and its model field (the raw SQL) is rendered through JinjaTemplateUtil.render to substitute ${variables} with actual values. This produces the executable SQL that will be validated against the database.

Step 3: Database Adapter Initialization

A DatabaseAdapter for the target database (PostgreSQL, Oracle, etc.) is instantiated through ProjectUtil.createDatabaseAdapter. This adapter provides generic JDBC access and database-specific metadata retrieval capabilities required for the subsequent validation steps.

Step 4: Raw SQL Validation

For every model, the validator wraps the raw SQL in a dry-run query:

SELECT 1 FROM (<model>) AS __dat_model WHERE 1=0

The DatabaseAdapter executes this query. Any SQLException indicates a syntax error or invalid table/column reference in the model SQL. Errors are captured and associated with the specific model file.

Step 5: Semantic SQL Validation

The validator builds the semantic SQL using SemanticModelUtil.semanticModelSql, which applies dimensions, measures, and filters. It then executes:

WITH <name> AS (<semantic_sql>) SELECT 1 FROM <name> WHERE 1=0

This validates that the generated semantic SQL is syntactically correct and compatible with the target database dialect. Both SqlParseException (parsing errors) and SQLException (execution errors) are collected.

Step 6: Dimension Enum Value Verification

If the configuration BUILDING_VERIFY_MDL_DIMENSIONS_ENUM_VALUES is true, the validator checks each dimension with enum_values:

• It verifies that COUNT(DISTINCT <column>) does not exceed 1000 values.
• It confirms that all declared enum values exist in the database via SELECT DISTINCT <column>.

This prevents runtime errors caused by invalid enum constraints.

Step 7: Data Type Validation

When BUILDING_VERIFY_MDL_DATA_TYPES is enabled, the validator compares declared data_type fields against actual database column metadata retrieved via DatabaseAdapter.getColumnMetadata. Mismatches raise a ValidationException with specific details about the expected versus actual types.

Step 8: Automatic Data Type Completion

If BUILDING_AUTO_COMPLETE_MDL_DATA_TYPES is true, the validator fills missing data_type fields by querying the database metadata. It uses DatabaseAdapter.getColumnMetadata to determine the correct ANSI-SQL type and injects it into the model definition, ensuring type safety without manual specification.

Step 9: Error Aggregation and Reporting

Each validation stage collects messages in a Map<String, List<ValidationMessage>>. If any map is non-empty, the validator throws a comprehensive ValidationException containing file-relative paths and detailed error descriptions, aborting the build immediately.

Core Components and Helper Classes

The validation pipeline relies on several specialized utilities:

• SemanticModelUtil (dat-core/src/main/java/ai/dat/core/utils/SemanticModelUtil.java): Generates the final semantic SQL by applying dimensions, measures, and filters to the base model.
• DatabaseAdapter (dat-core/src/main/java/ai/dat/core/adapter/DatabaseAdapter.java): Provides generic JDBC access and database-specific metadata retrieval.
• ProjectUtil (dat-boot/src/main/java/ai/dat/boot/utils/ProjectUtil.java): Creates the appropriate DatabaseAdapter instance based on project configuration.
• JinjaTemplateUtil: Renders template variables like ${source_table} in model SQL strings.
• ChangeSemanticModelsCacheUtil (dat-sdk/src/main/java/ai/dat/boot/utils/ChangeSemanticModelsCacheUtil.java): Caches parsed semantic model definitions to avoid redundant file I/O.

Configuration Options for Validation

The validator behavior is controlled by three key configuration flags:

Configuration Key	Default	Description
BUILDING_VERIFY_MDL_DIMENSIONS_ENUM_VALUES	false	Validates that dimension enum values exist in the database and do not exceed 1000 distinct values.
BUILDING_VERIFY_MDL_DATA_TYPES	false	Compares declared data_type fields against actual database column metadata.
BUILDING_AUTO_COMPLETE_MDL_DATA_TYPES	false	Automatically fills missing data_type fields using database metadata.

These options are validated via FactoryUtil.validateFactoryOptions during Step 1 of the execution flow.

Example: Validating a Semantic Model YAML

Consider the following semantic model definition:

semantic_models:
  - name: covid_cases
    model: |
      SELECT
        country,
        date,
        SUM(vaccinations) AS total_vaccinations
      FROM ${source_table}
      GROUP BY country, date
    dimensions:
      - name: country
        type: STRING
        enum_values:
          - value: USA
          - value: CAN
    measures:
      - name: total_vaccinations
        type: NUMBER

When dat build executes, the PreBuildValidator performs the following actions:

1. Template Rendering: Substitutes ${source_table} with public.covid_cases_raw using JinjaTemplateUtil.render.
2. Raw SQL Check: Wraps the query in SELECT 1 FROM (...) AS __dat_model WHERE 1=0 and executes it via DatabaseAdapter.
3. Semantic SQL Check: Generates the semantic SQL using SemanticModelUtil.semanticModelSql and validates it with a CTE wrapper.
4. Enum Verification: If enabled, confirms that USA and CAN exist in the country column and that distinct values do not exceed 1000.
5. Type Checking: If enabled, verifies that total_vaccinations matches the NUMBER declaration in the database metadata.

If any step fails, the build aborts with a detailed ValidationException:

There has exceptions in the semantic model SQL syntax validation of the semantic model,
in the YAML file relative path: src/main/resources/semantic_models.yml
  - covid_cases: Column 'country' not found in the generated SQL.

Summary

• PreBuildValidator (dat-sdk/src/main/java/ai/dat/boot/PreBuildValidator.java) orchestrates all semantic model validation before code generation.
• The validation pipeline executes dry-run SQL queries against the target database to verify both raw model SQL and generated semantic SQL.
• Optional checks include dimension enum value verification, data type consistency, and automatic data type completion, controlled by configuration flags.
• Errors are aggregated into a comprehensive ValidationException that halts the build and provides file-relative paths and specific failure details.
• Core utilities like SemanticModelUtil, DatabaseAdapter, and JinjaTemplateUtil support the validation process by generating SQL, executing queries, and rendering template variables.

Frequently Asked Questions

What happens if a semantic model SQL query is invalid?

If the raw SQL in a semantic model contains syntax errors or references non-existent tables or columns, the validateModelSql() method catches the SQLException when executing the dry-run query SELECT 1 FROM (<model>) AS __dat_model WHERE 1=0. The error is added to a validation messages map, and after all checks complete, a ValidationException is thrown containing the specific file path and error description, aborting the build before any code generation occurs.

How does the validator check dimension enum values?

When the configuration BUILDING_VERIFY_MDL_DIMENSIONS_ENUM_VALUES is enabled, the validateDimensionsEnumValues() method queries the database for each dimension that defines enum_values. It first checks that the count of distinct values in the column does not exceed 1000, then verifies that each declared enum value (such as "USA" or "CAN") actually exists in the database column via a SELECT DISTINCT query. Any mismatches or excessive cardinality are reported as validation errors.

Can the validator automatically fix missing data types?

Yes, when BUILDING_AUTO_COMPLETE_MDL_DATA_TYPES is set to true, the autoCompleteDataTypes() method queries the database metadata using DatabaseAdapter.getColumnMetadata() for every entity, dimension, or measure that lacks a data_type declaration. It retrieves the actual ANSI-SQL type from the database and injects it into the in-memory model definition, ensuring type safety without requiring manual specification in the YAML files.

What is the difference between raw SQL validation and semantic SQL validation?

Raw SQL validation, performed by validateModelSql(), checks the base query defined in the model's model field by wrapping it in a dry-run SELECT statement to verify table and column existence. Semantic SQL validation, performed by validateSemanticModelSql(), generates the final query using SemanticModelUtil.semanticModelSql()—which applies dimensions, measures, and filters—and validates it using a CTE wrapper (WITH <name> AS (...) SELECT 1 FROM <name> WHERE 1=0). The first ensures the base data is accessible; the second ensures the semantic layer transformations are valid.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}