Vertica setup

VENDOR-SUPPORTED PLUGIN

If you're interested in contributing, check out the source code for each repository listed below.

• Maintained by: Vertica
• Authors: Vertica (Former authors: Matthew Carter, Andy Regan, Andrew Hedengren)
• GitHub repo: vertica/dbt-vertica
• PyPI package: dbt-vertica
• Slack channel: n/a
• Supported dbt Core version: v1.8.5 and newer
• dbt Cloud support: Not Supported
• Minimum data platform version: Vertica 24.3.0

Installing dbt-vertica

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-vertica

For Vertica-specific configuration, please refer to Vertica configs.

Connecting to Vertica with dbt-vertica

Username / password authentication

Configure your dbt profile for using Vertica:

Vertica connection information

profiles.yml

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]
      autocommit: False
      
      threads: [1 or more]
  target: dev

Description of Profile Fields:

Property	Description	Required?	Default Value	Example
type	The specific adapter to use.	Yes	None	vertica
host	The host name or IP address of any active node in the Vertica Server.	Yes	None	127.0.0.1
port	The port to use, default or custom.	Yes	5433	5433
username	The username to use to connect to the server.	Yes	None	dbadmin
password	The password to use for authenticating to the server.	Yes	None	my_password
database	The name of the database running on the server.	Yes	None	my_db
oauth_access_token	To authenticate via OAuth, provide an OAuth Access Token that authorizes a user to the database.	No	""	Default: ""
schema	The schema to build models into.	No	None	VMart
connection_load_balance	A Boolean value that indicates whether the connection can be redirected to a host in the database other than host.	No	True	True
backup_server_node	List of hosts to connect to if the primary host specified in the connection (host, port) is unreachable. Each item in the list should be either a host string (using default port 5433) or a (host, port) tuple. A host can be a host name or an IP address.	No	None	['123.123.123.123','www.abc.com',('123.123.123.124',5433)]
retries	The retry times after an unsuccessful connection.	No	2	3
threads	The number of threads the dbt project will run on.	No	1	3
label	A session label to identify the connection.	No	An auto-generated label with format of: dbt_username	dbt_dbadmin
autocommit	Boolean value that indicates if the connection can enable or disable auto-commit.	No	True	False

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