Principle:TobikoData Sqlmesh Dbt Model Discovery And Mapping
| Knowledge Sources | |
|---|---|
| Domains | Data_Engineering, Dbt_Migration |
| Last Updated | 2026-02-07 00:00 GMT |
Overview
The systematic process of discovering all dbt project assets and mapping them to equivalent SQLMesh model objects.
Description
Dbt model discovery and mapping is the core translation layer that converts dbt's project structure into SQLMesh's model system. This process extends SQLMesh's base Loader class to understand dbt-specific concepts like materializations, incremental strategies, and package dependencies. The discovery phase inventories all dbt assets (models, seeds, sources) while the mapping phase transforms each asset's configuration into a corresponding SQLMesh model with the appropriate kind and settings.
The mapper handles complex scenarios like dbt's ephemeral models (which become SQLMesh embedded models), incremental models with various strategies (which map to SQLMesh incremental kinds), and seeds (which map to SQLMesh seed models). It also preserves important metadata like model documentation, tags, and custom schemas while adapting them to SQLMesh's structure.
Usage
Use dbt model discovery and mapping when:
- Loading a dbt project into SQLMesh context
- Converting dbt models to SQLMesh models for execution
- Validating that all dbt assets can be represented in SQLMesh
- Analyzing differences between dbt and SQLMesh model kinds
- Executing dbt models through SQLMesh's plan/apply workflow
Theoretical Basis
The discovery and mapping process follows a structured pipeline:
1. Project Loading:
* Initialize DbtLoader with SQLMesh context and project path * Discover all dbt packages (root project plus dependencies) * Load profiles.yml to understand target connections
2. Asset Discovery:
* Scan models/ directory for all SQL and Python files * Locate seeds/ directory for CSV data files * Parse schema.yml files for sources and tests * Track file modification times for caching
3. Configuration Resolution:
* Apply project-level configurations from dbt_project.yml * Merge folder-level configurations from nested schema.yml * Apply model-level configurations from config blocks * Resolve variable substitutions using dbt context
4. Model Mapping:
* Convert dbt materializations to SQLMesh model kinds:
- table → FULL kind
- view → VIEW kind
- incremental → INCREMENTAL_BY_TIME_RANGE kind
- ephemeral → EMBEDDED kind
- snapshot → SCD_TYPE_2 kind
* Map incremental strategies to SQLMesh approaches
* Convert partition_by and cluster_by to SQLMesh equivalents
* Preserve grants, tags, and custom properties
5. Dependency Resolution:
* Parse ref() and source() calls to build dependency graph * Map dbt package references to SQLMesh model names * Handle cross-package dependencies * Validate all references resolve correctly
6. Caching Strategy:
* Cache converted models based on file modification time * Track macro changes that require model recompilation * Invalidate cache when YAML configurations change * Use package-aware caching for multi-project setups
The mapping process preserves dbt's semantics while adapting to SQLMesh's execution model. For example, dbt's incremental models with merge strategy map to SQLMesh's INCREMENTAL_BY_TIME_RANGE with appropriate merge logic, while maintaining the original SQL with minimal modifications.