Transformation with dbt
Relevant source files
The following files were used as context for generating this wiki page:
- integration/example-library/meltano-run/index.md
- integration/meltano-manifest/expected-manifests/meltano-manifest.json
- integration/meltano-manifest/expected-manifests/meltano-manifest.prod.json
- src/meltano/schemas/meltano.schema.json
- integration/meltano-manifest/expected-manifests/meltano-manifest.cicd.json
- CHANGELOG.md
Meltano utilizes dbt (data build tool) to manage data transformations within a target data warehouse after the initial extraction and loading (EL) phases. In the Meltano ecosystem, dbt is typically implemented as a utility plugin, allowing users to invoke standard dbt commands such as run, test, and seed directly through the Meltano CLI or as part of automated orchestration workflows.
Sources: integration/example-library/meltano-run/index.md:53-56, integration/meltano-manifest/expected-manifests/meltano-manifest.json:1056-1144
Architecture and Integration
The integration of dbt within Meltano is declarative. It is defined in the project manifest or meltano.yml file as a utility that connects to the same database used by the loaders. Meltano manages the environment variables and configuration required for dbt to locate its project files and profiles.
Data Workflow
The transformation process follows a linear progression where raw data is first extracted from a source and loaded into a schema (often named after the extractor), after which dbt models are applied to transform that raw data into analytics-ready tables or views.
The diagram above illustrates the relationship between extraction, loading, and the dbt transformation layer within a Meltano execution block.
Sources: integration/example-library/meltano-run/index.md:53-93, integration/meltano-manifest/expected-manifests/meltano-manifest.json:34-100
Configuration Management
dbt plugins in Meltano (e.g., dbt-postgres, dbt-snowflake) require specific configuration to interact with the target warehouse and the dbt project structure.
Key Configuration Settings
| Setting | Description | Default / Example |
|---|---|---|
project_dir | The directory where the dbt project resides. | $MELTANO_PROJECT_ROOT/transform |
profiles_dir | The directory containing dbt profiles.yml. | $MELTANO_PROJECT_ROOT/transform/profiles |
target_path | Where dbt writes compiled SQL and artifacts. | $MELTANO_PROJECT_ROOT/.meltano/transformers/dbt/target |
skip_pre_invoke | Skips automatic dbt clean and deps calls. | false |
type | The dbt adapter type (e.g., snowflake, postgres). | snowflake |
Sources: integration/meltano-manifest/expected-manifests/meltano-manifest.json:1107-1144, src/meltano/schemas/meltano.schema.json:1062-1070
Environment Variables
Meltano automatically maps plugin configuration to environment variables that dbt consumes. For instance, configuring a Snowflake dbt utility results in variables like DBT_SNOWFLAKE_ACCOUNT, DBT_SNOWFLAKE_USER, and DBT_SNOWFLAKE_PASSWORD.
Sources: integration/meltano-manifest/expected-manifests/meltano-manifest.json:1078-1100
Implementation Workflow
Implementing transformations involves initializing the dbt environment, defining sources that match Meltano loader outputs, and creating transformation models.
1. Initialization
The initialize command creates the necessary project structure, including dbt_project.yml and the models directory.
meltano invoke dbt-postgres:initialize
Sources: integration/example-library/meltano-run/index.md:65-67
2. Source Definition
Transformation models rely on sources defined in yml files. These sources must point to the schema and tables populated by Meltano loaders.
sources:
- name: tap_gitlab
schema: public
tables:
- name: commits
Sources: integration/example-library/meltano-run/index.md:74-80
3. Model Creation
Models are standard SQL files utilizing Jinja templates. Meltano users typically place these in ./transform/models/.
Sources: integration/example-library/meltano-run/index.md:85-93
Operational Commands
The dbt utility supports a wide range of commands that can be invoked via meltano invoke or included as tasks in meltano run.
Standard Tasks
- build: Runs models, tests, snapshots, and seeds in DAG order.
- compile: Generates executable SQL from models.
- run: Executes SQL against the warehouse.
- test: Runs data validation tests.
- docs-generate: Creates project documentation.
Custom Command Examples
Manifests can define shorthand commands for specific model selections:
run_marts:run --select marts.*test_staging:test --select staging.*
Sources: integration/meltano-manifest/expected-manifests/meltano-manifest.json:1008-1055
Orchestration with Jobs and Schedules
Transformations are rarely run in isolation. They are integrated into Meltano jobs alongside extraction and loading tasks.
Job Task Definitions
Jobs define the sequence of plugins and specific commands. For example, a meltanohub_el job might perform the following tasks:
tap-meltanohub target-snowflakedbt-snowflake:test_source_meltanohubdbt-snowflake:run_staging_meltanohub
Sources: integration/meltano-manifest/expected-manifests/meltano-manifest.json:48-54
Summary
Transformation with dbt in Meltano provides a code-first, version-controlled approach to data modeling. By treating dbt as a utility plugin, Meltano ensures that transformation logic is tightly coupled with the data integration pipelines, sharing configuration and orchestration logic through a unified manifest system. This allows for complex workflows, such as environment-specific schema prefixes and automated testing of transformed data, to be executed consistently across local development, staging, and production environments.
Sources: integration/meltano-manifest/expected-manifests/meltano-manifest.prod.json:1078-1120, integration/meltano-manifest/expected-manifests/meltano-manifest.userdev.json:1100-1120