PostgreSQL Adapter Integration

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• dbt_ext/dbt_files/profiles/postgres/profiles.yml
• dbt_ext/extension.py
• dbt_ext/main.py
• dbt_ext/dbt_files/bundle/transform/dbt_project.yml

The PostgreSQL adapter integration in the meltano-dbt-ext project provides a standardized way to manage and execute dbt transformations against PostgreSQL databases within a Meltano environment. It utilizes a custom Meltano utility extension that wraps the dbt-postgres adapter, ensuring that environment variables are correctly mapped to dbt profiles and that necessary pre-invocation steps like dependency installation are handled automatically.

Sources: README.md:5-15, dbt_ext/extension.py:27-46

Architecture and Initialization

The integration relies on the dbt class, which inherits from ExtensionBase. During initialization, the extension identifies the adapter type via the DBT_EXT_TYPE environment variable. For PostgreSQL, this value is set to postgres. The extension manages two primary directories: the project directory (defaults to transform) and the profiles directory (defaults to transform/profiles).

The diagram shows the initialization flow where the extension identifies the PostgreSQL type and prepares the project environment.
Sources: dbt_ext/extension.py:38-46, dbt_ext/extension.py:118-155

Initialization Logic

When the initialize command is executed, the extension performs the following steps:

1. Creates the project directory if it does not exist.
2. Bundles standard dbt project files (e.g., dbt_project.yml) into the project directory.
3. Copies the PostgreSQL-specific profiles.yml from the extension's internal resources to the project's profiles directory.

Sources: dbt_ext/extension.py:123-152, dbt_ext/main.py:30-41

Configuration and Profile Mapping

The integration uses a specialized profiles.yml that maps Meltano environment variables to dbt configuration fields. This allows PostgreSQL connection details to be managed through Meltano's configuration system.

PostgreSQL Profile Structure

The profile defines three targets: dev, staging, and prod. The active target is determined by the MELTANO_ENVIRONMENT variable.

Configuration Field	Environment Variable	Description
host	DBT_POSTGRES_HOST	The PostgreSQL host address.
user	DBT_POSTGRES_USER	The database user.
password	DBT_POSTGRES_PASSWORD	The user's password.
port	DBT_POSTGRES_PORT	The connection port (e.g., 5432).
dbname	DBT_POSTGRES_DBNAME	The database name.
schema	DBT_POSTGRES_SCHEMA	The default schema for models.
threads	N/A	Parallelism (Dev: 2, Staging: 4, Prod: 6).

Sources: dbt_ext/dbt_files/profiles/postgres/profiles.yml:6-52

Meltano Plugin Settings

The PostgreSQL extension is configured in meltano.yml with specific settings that map to the environment variables used in the profile.

- name: dbt-postgres
  settings:
  - name: host
    env: DBT_POSTGRES_HOST
  - name: user
    env: DBT_POSTGRES_USER
  - name: port
    kind: integer
    env: DBT_POSTGRES_PORT
  - name: type
    value: postgres
    env: DBT_EXT_TYPE

Sources: README.md:16-72

Execution Flow and Pre-invoke Hooks

The extension implements a pre_invoke hook to automate dbt maintenance tasks. Unless DBT_EXT_SKIP_PRE_INVOKE is set to true, the extension automatically runs dbt clean and dbt deps before executing the primary command.

The sequence diagram illustrates how the extension intercepts the invocation to perform cleanup and dependency updates before the actual dbt command.
Sources: dbt_ext/extension.py:59-86, dbt_ext/extension.py:92-109

Supported Commands

The integration supports standard dbt commands through the dbt_invoker executable, which passes arguments directly to the dbt-postgres adapter:

• clean: Deletes folders in the clean-targets list.
• compile: Generates executable SQL.
• deps: Pulls the most recent version of dependencies.
• run: Compiles and executes SQL against PostgreSQL.
• test: Runs data tests in deployed models.
• build: Runs models, tests, snapshots, and seeds in DAG order.

Sources: README.md:74-118, dbt_ext/dbt_files/bundle/transform/dbt_project.yml:25-28

Conclusion

The PostgreSQL Adapter Integration simplifies dbt operations within Meltano by automating environment setup and maintenance. By bridging Meltano's configuration with dbt's profile system through the dbt-postgres adapter, it provides a consistent and reliable environment for data transformations.