How to use dbt-greengage adapter

Elena Kostyuchenko

Overview

The dbt-greengage adapter enables dbt to work with Greengage DB (based on Greenplum) and is included in the ADO bundle together with the DBT service.

The dbt-greengage adapter is based on dbt-postgres and adds support for Greengage-specific features, including:

  • Append-Optimized (AO) tables;

  • Column-Oriented (CO) storage;

  • distribution policies;

  • partitioning;

  • external tables;

  • materialized views and indexes.

The adapter provides:

  • compatibility with dbt Core versions 1.3-1.11;

  • support for Greengage-specific functionality;

  • backward compatibility with existing dbt-greenplum projects.

NOTE
The current release supports only Greengage DB 6 (based on PostgreSQL 9). When connecting to a Greengage DB 7 server, table creation operations may fail with a compilation error. Greengage DB 7 (based on PostgreSQL 12) support is planned for future releases.

Configuration

The adapter is included in ADO together with the DBT service. For more information on the DBT service, refer to the DBT service overview article.

To configure DBT for working with a Greengage DB database, create a profiles.yml configuration file that describes properties for the database connection.

Example profiles.yml:

my-greengage-project:
  target: dev
  outputs:
    dev:
      type: greengage
      host: localhost
      port: 5432
      user: gpadmin
      password: <password>
      dbname: warehouse
      schema: dbt_dev
      threads: 4

The required parameters are:

  • host — Greengage DB host;

  • user — database user;

  • password — user password;

  • dbname — target database name;

  • schema — target schema for dbt objects.

The profiles.yml configuration file must be on the host with a DBT component. The path to the configuration file’s directory must be added as a value of the DBT_PROFILES_DIR property in DBT service configuration in ADCM.

Alternatively, you can specify the path in the Profiles path property when running a DBT command.

Optional connection parameters
Parameter Description Default value

port

Database port

5432

threads

Number of parallel dbt threads

1

keepalives_idle

Number of seconds of inactivity before a TCP keepalive check is sent

0

connect_timeout

Connection timeout in seconds

10

search_path

Overrides PostgreSQL search_path

 — 

role

Executes SET ROLE after connection

 — 

sslmode

SSL mode, for example: require, verify-full

 — 

kerberos_service_name

Kerberos service name for GSSAPI authentication

 — 

The full list of connection parameters is identical to dbt-postgres.

DBT commands

The DBT service supports the standard dbt commands for Greengage DB. It provides execution of dbt models, tests, snapshots, documentation generation, and incremental transformations via ADCM actions and Airflow DAGs.

You can run a DBT command using ADCM service actions.

Materializations

dbt-greengage supports multiple materialization types. The adapter automatically selects Greengage-specific SQL generation paths depending on:

  • Greengage DB version;

  • materialization type;

  • storage configuration;

  • partitioning configuration.

The following materializations are supported:

table

The table materialization creates a physical table and fully manages its lifecycle.

Example:

{{ config(materialized='table') }}
NOTE
Greengage DB does not support CREATE OR REPLACE TABLE. Because of this limitation, dbt-greengage implements replacement through explicit drop-and-recreate logic. This is handled transparently via GreengageRelation, which restricts replaceable_relations to views only.

During dbt run --full-refresh, the adapter performs:

  • for regular tables: DROP TABLE IF EXISTS …​ CASCADE → CREATE TABLE;

  • for external tables: DROP EXTERNAL TABLE IF EXISTS …​ CASCADE;

  • for views: CREATE OR REPLACE VIEW (works directly).

Example:

DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users AS
SELECT 1 AS id, 'Alice' AS name;

The CASCADE keyword is required because schemas often contain dependent objects.

The table materialization fully supports Greengage DB storage parameters.

Example:

{{ config(
    materialized='table',
    appendoptimized=true,
    orientation='column',
    compresstype='ZSTD',
    compresslevel=4,
    blocksize=32768
) }}

Generated DDL:

CREATE TABLE users
WITH (
    appendoptimized=true,
    orientation=column,
    compresstype=ZSTD,
    compresslevel=4,
    blocksize=32768
)
AS
SELECT ...

view

The view materialization creates a standard SQL view in Greengage DB. In dbt-greengage, the implementation is mostly inherited from dbt-postgres. However, Greengage DB introduces several MPP-specific behaviors related to object dependencies and relation cleanup, therefore the adapter provides its own relation drop implementation.

Unlike PostgreSQL, Greengage DB environments frequently contain deeply interconnected objects: partition hierarchies, dependent views, external tables, materialized views, and cross-schema analytical objects.

To ensure reliable cleanup and recreation, dbt-greengage overrides the greengage__drop_relation macro. All relation drops use CASCADE semantics where required.

Example lifecycle during model replacement:

DROP VIEW IF EXISTS analytics.products CASCADE;
CREATE VIEW analytics.products AS
SELECT ...

This behavior guarantees that dependent objects are removed correctly before recreation.

Example:

{{ config(
    materialized='view'
) }}

select
    order_id,
    customer_id,
    total_amount,
    created_at
from {{ ref('stg_orders') }}

incremental

The incremental materialization is optimized for large analytical datasets and supports multiple loading strategies.

Supported strategies
Strategy Status Description

append

Inherited

INSERT INTO — works out of the box

delete+insert

Inherited

DELETE WHERE key IN (…​) + INSERT

truncate+insert

Custom

TRUNCATE + INSERT — full reload without CASCADE

microbatch

Custom

DELETE by event_time window + INSERT
NOTE

The adapter does not support the merge strategy because MERGE is unavailable in Greengage DB 6.
TRUNCATE + INSERT example
{{ config(
    materialized='incremental',
    incremental_strategy='truncate+insert'
) }}
select * from {{ ref('source_data') }}

This strategy is safe for heap tables because TRUNCATE removes all rows without affecting the table structure, indexes, permissions, or dependent objects. On partitioned tables, TRUNCATE removes data from all partitions. It is recommended to use delete+insert with incremental_predicates filtered by the partition key.

microbatch example
{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='created_at',
    begin='2024-01-01',
    batch_size='month'
) }}
select * from {{ ref('source_data') }}

The microbatch strategy uses batch configuration and the event_time column as described in dbt microbatch documentation. For each batch, a DELETE is executed over the event_time window, followed by an INSERT from a temporary table.

The unique_key parameter is not required, batch boundaries are defined by the event_time window.

To reference the target table, use DBT_INTERNAL_DEST in incremental_predicates as follows:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='created_at',
    begin='2024-01-01',
    batch_size='month',
    incremental_predicates=["DBT_INTERNAL_DEST.status != 'archived'"]
) }}
select * from {{ ref('source_data') }}

The adapter replaces the alias with the real table name (there is no USING clause in DELETE).

Constraints

The adapter supports dbt model contracts and constraint definitions.

Constraint Supported Enforced

check

Yes

Yes

not_null

Yes

Yes

unique

Yes

No

primary_key

Yes

No

foreign_key

Yes

No
NOTE
When contract.enforced=true is configured, dbt-greengage emits a warning because full contract enforcement is not guaranteed for all constraint types.

Storage configuration (AO/heap)

Greengage DB supports both heap tables and Append-Optimized (AO) tables with columnar and row orientation.

AO-storage parameters are configured directly in model configuration and can be of two types:

  • Row-oriented AO tables are suitable for workloads with frequent row access or mixed OLTP/analytical patterns.

    Example:

    {{ config(
materialized='table',
appendoptimized=true,
orientation='row'
) }}

  • Column-oriented AO tables are recommended for analytical fact tables and aggregation-heavy workloads.

    Example:

    {{ config(
materialized='table',
appendoptimized=true,
orientation='column',
compresstype='ZSTD',
compresslevel=4
) }}
Parameter Description Default value

appendoptimized

Creates an AO table

true

orientation

Storage orientation: column or row

column

compresstype

Compression type

ZSTD

compresslevel

Compression level from 1 to 9

4

blocksize

Block size in bytes

32768

Heap tables

Heap tables disable AO storage and behave similarly to standard PostgreSQL tables.

Example:

{{ config(
materialized='table',
appendoptimized=false
) }}

Distribution

The adapter supports the following distribution policies:

  • DISTRIBUTED BY — distributes rows according to hash values of one or more columns.

    Example:

    {{ config(
materialized='table',
distributed_by='customer_id'
) }}

  • DISTRIBUTED REPLICATED — copies the full table to all segments.

    Example:

    {{ config(
materialized='table',
distributed_replicated=true
) }}

  • DISTRIBUTED RANDOMLY — If no distribution policy is configured, the adapter distributes randomly.

    Example:

    {{ config(materialized='table') }}

Partitioning

DBT Greengage DB adapter supports:

  • raw partition definitions;

  • parameterized RANGE partitions;

  • parameterized LIST partitions.

Greengage DB 6 does not support CREATE TABLE AS together with PARTITION BY. Because of this limitation, dbt-greengage automatically switches to a two-step process:

CREATE TABLE ...
INSERT INTO ...

Partitioned tables therefore require explicit column definitions using the fields_string parameter.

The raw_partition option allows passing a complete partition clause.

Parameter Config Description

fields_string

config.get()

Column definitions for CREATE TABLE ( …​ )

raw_partition

GreengageConfig

Full PARTITION BY …​ clause as a raw SQL string

partition_type

GreengageConfig

RANGE or LIST

partition_column

GreengageConfig

Column to partition by

partition_start

config.get()

Start value for RANGE partition

partition_end

config.get()

End value for RANGE partition

partition_every

config.get()

Interval for RANGE partition, e.g. 1 month

partition_values

config.get()

PARTITION … VALUES (…) clause for LIST partition

default_partition_name

config.get()

Name of the default partition (default: other)
Raw partition definition example
{% set fields_string %}
id int4 null,
event_date timestamp null
{% endset %}

{% set raw_partition %}
PARTITION BY RANGE (event_date)
(
START ('2024-01-01'::timestamp) INCLUSIVE
END ('2025-01-01'::timestamp) EXCLUSIVE
EVERY (INTERVAL '1 month'),
DEFAULT PARTITION other
)
{% endset %}

{{ config(
materialized='table',
distributed_by='id',
fields_string=fields_string,
raw_partition=raw_partition
) }}
RANGE partitioning example
{{ config(
materialized='table',
distributed_by='id',
fields_string=fields_string,
partition_type='RANGE',
partition_column='event_date',
partition_start='2024-01-01',
partition_end='2025-01-01',
partition_every='1 month'
) }}
LIST partitioning example
{{ config(
materialized='table',
distributed_by='id',
fields_string=fields_string,
partition_type='LIST',
partition_column='region',
partition_values="PARTITION eu VALUES ('EU'), PARTITION us VALUES ('US')"
) }}

External tables and data loading

The adapter provides macros for creating and dropping Greengage DB external tables. It supports the gpfdist and PXF protocols.

gpfdist serves flat files from an ETL host (gpfdist://, gpfdists://), supports TEXT and CSV formats, and allows multiple URLs in a single LOCATION clause for parallel loading across segments.

PXF connects to external data sources (HDFS, HBase, Hive, S3, JDBC) via pxf:// URLs. It supports TEXT, CSV, and CUSTOM formats (with a FORMATTER). PXF requires exactly one URL per LOCATION clause, multiple locations are not allowed.

External tables are managed via utility macros and are typically used in:

  • pre-hook;

  • post-hook;

  • dbt run-operation.

Parameter Description Default value

relation

Target external table relation (schema and table name) to create. This parameter is required

 — 

fields_string

Column definitions for the external table, specified in SQL format (for example, id INTEGER, name TEXT). This parameter is required

 — 

location

Source location for the external data. Can be a single URL or a list of URLs. Multiple URLs are supported only for gpfdist sources. This parameter is required

 — 

format

Data format in the external table. Supported values ​​are TEXT, CSV, and CUSTOM

TEXT

formatter

The name of the custom formatter used to process data in the CUSTOM format (e.g. pxfwritable_import)

 — 

delimiter

Field delimiter

,

null_string

String representing NULL

 — 

escape

Escape character (off to disable)

 — 

header

Specifies whether the first line is header

false

encoding

Character encoding

UTF8

on_clause

Determines where data processing is performed: on all segments (ALL) or only on the coordinator (MASTER)

 — 

log_errors

Enables error logging

false

segment_reject_limit

Reject limit value

 — 

segment_reject_limit_type

Unit of measurement for the segment_reject_limit parameter: the number of rows (rows) or the percentage of the total data (percent)

rows

execute

Determines whether the generated SQL query should be executed immediately (true) or just its text should be returned (false)

true
gpfdist CSV with error handling
{% set ext_relation = api.Relation.create(schema='ext_schema', identifier='ext_orders') %}

{{ create_external_table(
    relation=ext_relation,
    fields_string='order_id INTEGER, order_date DATE, amount NUMERIC',
    location="'gpfdist://etl-host:8080/orders*.csv'",
    format='CSV',
    delimiter=',',
    header=true,
    log_errors=true,
    segment_reject_limit=100,
    segment_reject_limit_type='rows'
) }}
Multiple gpfdist locations for parallel loading
{{ create_external_table(
    relation=ext_relation,
    fields_string='id INTEGER, name TEXT, value NUMERIC',
    location=[
        "'gpfdist://host1:8080/data.csv'",
        "'gpfdist://host2:8081/data.csv'"
    ],
    format='CSV',
    header=true
) }}
PXF — HDFS text
{{ create_external_table(
    relation=ext_relation,
    fields_string='event_id INTEGER, payload TEXT',
    location="'pxf://data/events?PROFILE=hdfs:text'",
    format='TEXT',
    delimiter='|'
) }}
PXF — HBase with CUSTOM format
{{ create_external_table(
    relation=ext_relation,
    fields_string='row_key TEXT, cf1_col1 TEXT, cf1_col2 TEXT',
    location="'pxf://hbase_table?PROFILE=HBase'",
    format='CUSTOM',
    formatter='pxfwritable_import'
) }}
Writable external tables

The adapter also supports writable external tables.

Example:

{% set ext_relation = api.Relation.create(schema='ext_schema', identifier='ext_export') %}

{{ create_writable_external_table(
    relation=ext_relation,
    fields_string='id INTEGER, name TEXT',
    location="'gpfdist://etl-host:8080/output.csv'",
    format='CSV',
    distributed_by='id'
) }}

Materialized views

The dbt-greengage adapter provides a custom implementation of materialized_view with built-in index management.

Unlike regular views, materialized views physically store query results on disk. This requires the materialized view to be refreshed when source data changes.

When a materialized view is created, the adapter executes a CREATE MATERIALIZED VIEW …​ AS …​ statement and automatically creates all indexes defined in the model configuration.

During subsequent dbt run executions, the adapter compares the current materialized view definition with the desired configuration. If only index definitions have changed, the adapter performs a targeted update by dropping and recreating only the affected indexes. If the underlying query has changed or the materialized view must be recreated, the adapter replaces the entire object.

Operations such as refresh, rename, drop, describe, and configuration comparison are inherited from dbt-postgres.

Example:

{{ config(
    materialized='materialized_view',
    indexes=[
        {'columns': ['user_id'], 'type': 'btree', 'unique': true},
        {'columns': ['order_count'], 'type': 'bitmap'},
    ]
) }}

select
    user_id,
    count(*) as order_count,
    sum(amount) as total_amount
from {{ ref('orders') }}
group by user_id

Indexes can also be configured in dbt_project.yml or schema.yml:

models:
  - name: user_order_summary
    config:
      materialized: materialized_view
      indexes:
        - columns: ['user_id']
          type: btree
          unique: true
        - columns: ['order_count']
          type: bitmap
Index configuration
Parameter Type Description Default value

columns

list

List of columns included in the index. This parameter is required

 — 

unique

bool

Creates a unique index

false

type

string

Index method to use

btree
NOTE
Greengage DB does not support CREATE OR REPLACE MATERIALIZED VIEW. As a result, materialized views are treated as renameable relations but not replaceable relations. When a full recreation is required, the adapter drops and recreates the materialized view rather than using an in-place replacement operation.

Indexes

The adapter provides native support for index creation through the greengage__get_create_index_sql macro.

When an index definition is supplied in a model configuration, the adapter automatically validates the configuration, generates the appropriate SQL statement, and creates the index as part of the materialization workflow. Index names are generated automatically.

Generated SQL follows the pattern:

CREATE [UNIQUE] INDEX "<generated_name>"
ON <relation>
[USING <index_type>]
(<columns>)

Indexes are used together with materialized views (via the indexes parameter) and are available for calling from macros via get_create_index_sql(relation, index_dict).

Supported index types
Index type Description

btree

Default index type. Recommended for equality predicates, sorting operations, and range queries

bitmap

Greengage-specific index type optimized for columns with low cardinality, such as status flags, categories, and other columns with a limited number of distinct values

hash

Optimized for exact-match lookups using equality operators

gist

Generalized search tree index suitable for geospatial data, full-text search, and other complex data types

spgist

Space-partitioned GiST index designed for IP addresses, phone numbers, and hierarchical data

gin

Generalized inverted index commonly used for arrays, JSONB documents, and full-text search

brin

Block range index designed for very large tables where data is naturally ordered, such as timestamp-based fact tables

Extensions

The adapter provides a set of Jinja macros for managing database extensions in Greengage DB.

These macros can be executed from:

  • dbt models;

  • on-run-start and on-run-end hooks;

  • pre-hook and post-hook;

  • dbt run-operation commands.

An example of creating a database extension:

{{ create_extension(
    extension_name='hstore',
    schema='public',
    version='1.4',
    cascade=true
) }}

Generated SQL:

CREATE EXTENSION "hstore"
WITH SCHEMA "public"
VERSION '1.4'
CASCADE

Create an extension only if it does not already exist:

{{ create_extension_if_not_exists(
    extension_name='postgis'
) }}

Generated SQL:

CREATE EXTENSION IF NOT EXISTS "postgis"

This macro is idempotent and can be safely executed multiple times, making it suitable for automated deployment pipelines.

Remove an existing extension:

{{ drop_extension(
    extension_name='hstore',
    if_exists=true,
    cascade=true
) }}

Generated SQL:

DROP EXTENSION IF EXISTS "hstore" CASCADE
Extension macro parameters
Parameter Type Description Default value

extension_name

string

Name of the extension to create or remove, for example hstore, postgis, or plpython3u. This parameter is required

 — 

schema

string

Schema in which the extension should be installed. Generates the WITH SCHEMA clause

 — 

version

string

Specific extension version to install

 — 

cascade

boolean

Automatically installs or removes dependent objects when supported by the extension

false

if_exists

boolean

Adds the IF EXISTS clause when dropping an extension. Applicable only to drop_extension

true

execute

boolean

Executes the generated SQL immediately. If set to false, the macro returns the SQL statement without executing it

true
Install an extension during DBT initialization

The following example installs the plpython3u extension before model execution begins:

on-run-start:
  - "{{ create_extension_if_not_exists(extension_name='plpython3u') }}"

This ensures that required extensions are available before any models, macros, or user-defined functions that depend on them are executed.

Snapshots

The dbt-greengage adapter supports snapshot-based change tracking for Slowly Changing Dimension (SCD) workloads. Snapshot processing uses the same implementation as dbt-postgres, based on UPDATE …​ FROM and INSERT statements rather than SQL MERGE, which is not available in Greengage DB.

The following snapshot strategies are supported:

  • timestamp — creates a new snapshot version when the value of a specified timestamp column changes (for example, updated_at);

  • check — creates a new snapshot version when the values of one or more monitored columns change.

The adapter also supports the hard_deletes option (available in dbt 1.9 and later), which allows tracking records that have been removed from the source dataset.

Timestamp strategy example
{% snapshot orders_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='order_id',
strategy='timestamp',
updated_at='updated_at'
)
}}
select * from {{ source('raw', 'orders') }}
{% endsnapshot %}
Check strategy example
{% snapshot products_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='product_id',
strategy='check',
check_cols=['name', 'price', 'status']
)
}}
select * from {{ source('raw', 'products') }}
{% endsnapshot %}
hard_deletes example

If hard_deletes is set to new_record, deleted records are captured as a new row with dbt_is_deleted = True.

{% snapshot customers_snapshot %}
{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at',
        hard_deletes='new_record'
    )
}}
select * from {{ source('raw', 'customers') }}
{% endsnapshot %}

Greengage-specific snapshot behavior

Greengage DB 6 is based on PostgreSQL 9 and has specific type resolution behavior for string literals in UNION ALL queries.

To avoid snapshot compilation issues, the adapter automatically injects explicit ::text casts into snapshot helper SQL.

This behavior ensures compatibility with future Greengage DB versions.

Migration

Migration from dbt-greenplum

The dbt-greengage adapter is designed to be largely compatible with existing dbt-greenplum projects. In most cases, migration requires only minimal configuration changes.

  1. Update the adapter type in the profiles.yml configuration file as follows:

    type: greengage

  2. Install the dbt-greengage adapter (either by installing the DBT service or manually) and remove the old adapter package:

    $ pip uninstall dbt-greenplum
$ pip install dbt-greengage

  3. Replace appendonly with appendoptimized in model configurations (recommended). The legacy appendonly parameter remains supported for backward compatibility, so existing projects continue to work without modification. However, using appendoptimized is recommended because it reflects Greengage DB terminology.

    {{ config(
    appendoptimized=true,
    orientation='column'
) }}

  4. Review incremental models and remove the merge strategy if it is configured. Greengage DB does not support SQL MERGE, therefore the merge incremental strategy is not available. If it is configured, the adapter raises a validation error during execution. Use one of the supported alternatives.

  5. Verify custom schema configuration. Unlike some other adapters, dbt-greengage does not automatically prepend target.schema to custom schema names defined through the schema: property. If your project relies on schema name concatenation, review and adjust schema definitions after migration.

The adapter inherits its connection management from dbt-postgres, which means that all standard connection parameters remain unchanged. Existing settings can be reused without modification.

Migration from dbt-postgres

Migrating from dbt-postgres to dbt-greengage is generally straightforward because the dbt-greengage adapter is built on top of dbt-postgres and supports the same connection configuration model.

Update the adapter type in the profiles.yml configuration file as follows:

type: greengage
Found a mistake? Seleсt text and press Ctrl+Enter to report it