Skip to content
PondraPondraPondra

dbt and BI tools

Pondra’s Postgres port answers the catalog queries that dbt and BI tools send: pg_catalog, information_schema, \d in psql, JDBC’s and ODBC’s metadata calls. So these tools connect to a lake as they would to Postgres. Start a node with a Postgres port, and give the tool its address:

Terminal window
pondra serve lake --pg 0.0.0.0:5432 --admin-token "$TOKEN"
Setting Value
Host, port the node’s --pg address, such as host:5432
Database the lake’s name (the folder’s, such as lake), or a database of a folder of lakes
User admin, writer or reader: which token the password is; or a user made with CREATE USER
Password that token, or the user’s password (a node with no tokens or users takes any password)
SSL require when the node has a certificate (TLS); otherwise off

What is tested

Section titled “What is tested”

These tools are run against a node before each release (tools/clients_check.py):

Tool What is checked
dbt (dbt-postgres 1.11, dbt-core 1.12) seeds, a view, a table, incremental models (delete+insert, merge, append), a snapshot, data tests and dbt docs generate, run twice; every model’s rows equal Postgres 16’s
psql \dt, \d (types, NOT NULL, defaults, the primary key), \dv, \dn, \df, \l, \du, \d+ of a view
SQLAlchemy (psycopg 2 and 3) the inspector: schemas, tables, views, columns, primary keys, view definitions; pandas’ to_sql and read_sql
pgjdbc (what DBeaver, DataGrip, Metabase and Tableau’s JDBC connector use) DatabaseMetaData: catalogs, schemas, tables, views, columns, primary keys, functions; prepared queries
psqlODBC (what Tableau, Excel and Power BI’s ODBC connector use) tables, views, columns, primary keys; queries with parameters
Npgsql 4.0 (what Power BI Desktop’s PostgreSQL connector carries) and 8 the types it loads on connecting, its schema of databases, tables, views and columns; queries with parameters, arrays and decimals
ADBC (adbc_driver_postgresql) Arrow results of every type

dbt

Section titled “dbt”

Use the dbt-postgres adapter. A profile:

~/.dbt/profiles.yml
shop:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      user: admin
      password: "{{ env_var('PONDRA_TOKEN') }}"
      dbname: lake
      schema: analytics
      threads: 1

Then run dbt as usual:

Terminal window
pip install dbt-postgres
dbt seed && dbt run && dbt snapshot && dbt test
dbt docs generate
  • Models: views, tables and incremental models, with the delete+insert, merge and append strategies. A second dbt run replaces views and tables the way dbt does on Postgres (it builds a new one and renames it into place, with ALTER … RENAME).
  • Seeds, snapshots and tests work as on Postgres. (Snapshots are tested with the check strategy.)
  • Schemas are Pondra’s schemas: dbt’s analytics is lake.analytics, and other clients read analytics.customer_totals too.
  • Rights: dbt makes schemas and tables, so it needs the admin token (user admin).

What you build with dbt is a lake like any other. Its tables can be published as Delta or Iceberg, queried over HTTP, or kept current with materialized views that dbt doesn’t need to rebuild.

BI tools

Section titled “BI tools”

  1. Start the node with --pg, as above. For a tool on another machine, give the node a token (--read-token is enough for dashboards) and listen on an address the tool can reach.

  2. Add a PostgreSQL connection in the tool, with the settings in the table at the top. Set SSL to require when the node has a certificate, and off, prefer or allow when it hasn’t.

  3. Browse and query. The tool lists the lake’s schemas, tables, views and columns, with Postgres’s type names (bigint, character varying, timestamp with time zone, arrays as integer[]), and a keyed table’s primary key.

Queries run as Pondra runs any query: over the lake’s Parquet files and its newest rows, and spread over the cluster when that is faster. A dashboard on a table that streams in sees each row as soon as its write is acknowledged.

What the catalog holds

Section titled “What the catalog holds”

The port answers from the lake’s own catalog:

  • pg_namespace, pg_class, pg_attribute, pg_type (arrays too), pg_index, pg_constraint, pg_tables, pg_views, pg_matviews, pg_proc, pg_database, pg_roles and the other tables these tools read;
  • information_schema.tables, columns, views, schemata, routines, table_constraints and key_column_usage;
  • functions such as format_type, pg_get_viewdef, pg_get_constraintdef, pg_table_is_visible, current_schema() and version().

Objects get stable ids (oids), so a tool that caches them finds the same table again after a restart.