dbt-vertica

dbt adapter for Vertica. The adapter uses vertica-python to connect to your Vertica database.

For more information on using dbt with Vertica, consult the Vertica-Setup and Configuration pages.

dbt-vertica Versions Tested

dbt-vertica has been developed using the following software and versions:

• Vertica Server 26.2.0-0
• Python 3.11
• vertica-python client 1.3.1
• dbt-core 1.12.0
• dbt-tests-adapter 1.20.0

Supported Features

dbt Core Features

Below is a table for what features the current Vertica adapter supports for dbt. This is constantly improving and changing as both dbt adds new functionality, as well as the dbt-vertica driver improves. This list is based upon dbt 1.3.0

• Yes - Supported, and tests pass.
• No - Not supported or implemented.
• Untested - May support out of the box, though hasn't been tested.
• Passes Test - The tests have passed, though haven't tested in a production like environment.

dbt Fusion engine / ADBC

The dbt Fusion engine does not load Python adapters: it talks to warehouses through its own Rust drivers over ADBC (Arrow Database Connectivity), and those drivers are developed in the dbt Fusion project itself. dbt-vertica therefore runs on dbt Core (this package); Fusion support requires a Vertica ADBC driver contributed to the Fusion engine and cannot be provided from this repository. The macros in this adapter are kept free of Fusion-incompatible Jinja (dbt Core 1.12's Fusion-conformance deprecation warnings are clean), so projects using dbt-vertica can adopt Fusion-ready project syntax (e.g. require_generic_test_arguments_property) today.

Installation

$ pip install dbt-vertica

You don't need to install dbt separately. Installing dbt-vertica will also install dbt-core and vertica-python.

Sample Profile Configuration

your-profile:
  outputs:
    dev:
      type: vertica # Don't change this!
      host: [hostname]
      port: [port] # or your custom port (optional)
      username: [your username] 
      password: [your password] 
      database: [database name] 
      oauth_access_token: [access token]
      schema: [dbt schema] 
      connection_load_balance: True
      backup_server_node: [list of backup hostnames or IPs]
      retries: [1 or more]
      threads: [1 or more] 
      autocommit: False
  target: dev

Description of Profile Fields:

For more information on Vertica’s connection properties please refer to Vertica-Python Connection Properties.

Changelog

See the changelog

Contributing guidelines

Have a bug or an idea? Please see CONTRIBUTING.md for details

Develop

Run a local Vertica instance like:

docker run -p 5433:5433 \
           -p 5444:5444 \
           -e VERTICA_DB_NAME=docker \
           -e VMART_ETL_SCRIPT="" \
           -e VMART_ETL_SQL="" \
           vertica/vertica-ce

Access the local Vertica instance like:

docker exec -it <docker_image_name> /opt/vertica/bin/vsql

Install the package with its test dependencies:

pip3 install -e ".[test]"

Point the test suite at your Vertica instance (defaults target the CI verticadb-sample host; override with env vars for local runs):

export VERTICA_HOST=localhost VERTICA_PORT=5433 \
       VERTICA_USER=dbadmin VERTICA_PASSWORD='' VERTICA_DATABASE=vdb
# grants tests need three extra users to exist in the database:
export DBT_TEST_USER_1=dbt_test_user_1 DBT_TEST_USER_2=dbt_test_user_2 DBT_TEST_USER_3=dbt_test_user_3

Run tests via:

pytest tests/functional/adapter/
# run an individual test 
pytest tests/functional/adapter/test_basic.py

Run Unit test via:

dbt test --select /{foldername}/{unit_test_file}