dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

dbt is the T in ELT. Organize, cleanse, denormalize, filter, rename, and pre-aggregate the raw data in your warehouse so that it's ready for analysis.

dbt-denodo adapter

This repo contains the base code to help you start to build out your denodo adapter plugin, for more information on how to build out the adapter please follow the docs

Getting Started

Prerequisites

To use the dbt-denodo adapter, ensure the following requirements are met:

Denodo Platform version 9.4 or higher Access to the Denodo platform with valid credentials for one of the supported authentication mechanisms: Basic (login/password) or OAuth. Python environment configured to run dbt-core 1.11.11.

Installation from Denodo Support Site

If installed from the package distributed from the Denodo Support Site, dependencies will need to be previously installed, either manually or via pip. See above (or the dialect’s README files) for a list of the specific dependency versions needed.

Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites

Once the package is unzipped, in order to install the dialect just run:

	pip install route/to/dbt_denodo-<version>-py3-none-any.whl

Credentials: configuring your profile

Basic Autehntication

This method uses the standard login mechanism with credentials. Configuration for this method requires the username and password fields, with the auth_type explicitly defined as basic.

denodo:
  target: dev
  outputs:
    dev:
      type: denodo    
      host: localhost    # IP 
      username: admin
      password: admin
      port: 9996          # Port
      database: dbtmaterialized 
      threads: 1
      schema: public

OAuth Authentication

Method A: Direct token injection via password

With this configuration, the adapter treats the provided password string as the active OAuth bearer token.

Method B: Direct token injection via password

To utilize this approach, the following additional parameters must be defined:

token_url: The authentication endpoint responsible for issuing tokens (e.g., the /as/token.oauth2 route on an identity server). client_id: The registered identifier for the OAuth application. client_secret: The private key linked with the client_id. scope_field_name: The specific field within the response payload that designates scopes. scope_field_value: The requested permissions required for the session. grant_type: The specific OAuth flow used for authentication.

denodo:
  target: dev
  outputs:
    dev:
      type: denodo    
      host: localhost    # IP or hostname of the Denodo server
      port: 9996          # Default VDP JDPBC port
      database: dbtmaterialized 
      username: puser
      password: *******
      auth_type: oauth    # Supported types: basic or oauth
      token_url: https://******.******.com:9031/as/token.oauth2
      client_id: test
      client_secret: [your_secret]
      scope_field_name: scope
      scope_field_value: controller openid
      grant_type: password

Usage

Start a project

To initialize a new dbt project for use with the Denodo platform, you must use the standard dbt initialization command from your terminal. This process sets up the necessary directory structure and configuration files required for your analytics engineering workflow.

Run the following command, replacing projectName with your desired project name:

dbt init projectName --profile denodo

Upon execution, dbt will prompt you to select an adapter. This initialization creates a project folder containing sample models and the dbt_project.yml configuration file, which you will then need to align with your Denodo profiles.yml settings.

Create a model

In dbt, a model is a single SELECT statement defined in a .sql file within your project's models/ directory. When you run dbt, the adapter transforms these SELECT statements into Virtual DataPort (VDP) views or tables within the Denodo platform.

To create your first model, navigate to the models/ folder and create a new file, such as my_first_model.sql. Inside this file, write the VQL query that defines your transformation:

SELECT *
FROM actor
WHERE status = 'active'

The dbt-denodo adapter will use Denodo's Virtual Query Language (VQL) to execute these transformations, facilitating the creation of robust datasets directly through the platform. Depending on the materialization strategy, data could be stored in the database configured as a cache, which allows data access from outside the platform

Run a model

After defining your models and configuring the connection, you can execute the models from the terminal. This process triggers the dbt-denodo-connector to communicate with the Denodo VDP server and materialize the specified objects.

To run every model within the project, trigger the following command:

dbt run

For more granular control, you can run a specific model by using the --select flag:

dbt run --select my_first_model

Models

Materialized tables

In this adapter, models materialized as table are created in Denodo VDP as Base Views with Full Cache enabled.

{{ config(
    materialized='table',
    database='admin'
) }}


SELECT 
    1 AS id,
    'Proba Denodo4' AS nome,
   now() AS data_test

Materialized views

In this adapter, models materialized as view are created in Denodo VDP as Base Views.

{{ config(
    materialized='view',
    database='admin'
) }}
-- Use the `ref` function to select from other models

select *
from {{ ref('my_first_dbt_model') }}
where id = 1

Incremental models

Support for incremental models.

In this adapter, models materialized as incremental are created in Denodo VDP as Base Views with Full Cache enabled. Unlike standard dbt adapters, this adapter does not use the is_incremental() macro inside the model's SQL body. Instead, the incremental logic is managed through Denodo's Cache Engine using a specific configuration field.

To define an incremental filter, you must use the incremental_condition parameter within the config block.

These strategies are supported:

append

• append: Insert new records without updating, deleting or overwriting any existing data. There might be duplicate data (e.g. great for log or historical data).

{{ config(materialized='incremental',
 incremental_strategy='append',
 incremental_condition="dbt_updated_at >= '@LAST_REFRESH_DATE'")}}

SELECT  id AS dbt_id, project_name AS dbt_project_name,amount AS dbt_amount,  updated_at AS dbt_updated_at
FROM database.incremental_test
where id < 300

merge

• merge: Conditionally updates, deletes, or inserts rows into an denodo view configured as full cache. Used in combination with unique_key.

{{
   config(
       materialized='incremental',
       incremental_strategy='merge',
       unique_key='id',
       incremental_condition="updated_at >= '@LAST_REFRESH_DATE'"
   )
}}

SELECT *
FROM database.incremental_test

Code of Conduct

Everyone interacting in the dbt project's codebases, issue trackers, chat rooms, and mailing lists is expected to follow the dbt Code of Conduct.