Data integration

Data Connection

Sources, read-only queries, CDC, syncs and exports.

Data Connection brings data from external systems into Converge and, where allowed, sends data back out.

Sources

A source is a named connection to an external system. Syncs and exports refer to it by name.

• Connector types. Converge ships connectors for relational databases and warehouses (for example JDBC/PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery, Databricks, Redshift), object and file stores (S3, GCS, HDFS, SFTP/FTP), streams (Kinesis, Pub/Sub), generic HTTP, REST and GraphQL APIs, and SaaS applications. The full catalog is available from GET /api/v2/connectivity/sources/capabilities.
• Capabilities. Each connector advertises what it supports, such as batch sync, incremental sync, CDC, table export, webhooks and exploration.
• Credentials. A source's credentials are encrypted at rest and never logged or returned. Supported authentication includes bearer tokens, basic auth, API keys, OAuth 2.0 (client credentials, authorization code, device and JWT flows) and mutual TLS.
• Egress control. A source may only call its own host or hosts explicitly allowed for it. Anything else is refused.
• Markings. Sources carry markings and roles like any other resource.

Catalog and schema export

External tools can discover the catalog without calling each dataset endpoint individually:

• Catalog JSON. GET /api/v2/catalog/export returns every dataset the caller can see, with its schema and recent builds, plus exportedAt. It needs read permission on datasets. This response is unchanged and stays the default.
• OpenAPI document. GET /api/v2/catalog/export?format=openapi returns an OpenAPI 3 document scoped to the read-only interoperability surface: catalog export, dataset interop, secured object type tables, readTable, the Flight SQL REST bridge and the Iceberg REST catalog. Other formats are refused with 400.
• Iceberg REST catalog. The same managed tables are served through the Iceberg REST catalog at /api/iceberg/v1 (/config, /namespaces, /namespaces/{namespace}/tables, /namespaces/{namespace}/tables/{table}). Engines that speak that protocol — Snowflake, Spark, Trino — load converge.<table> from it. With X-Iceberg-Access-Delegation: vended-credentials the catalog returns short-lived MinIO STS credentials scoped to the table prefix: read-only for readers, plus write actions when the caller may write the table. Tables in a customer-managed bucket refuse credential vending with 406; the platform keys are never returned.
• Iceberg auth. Besides a Bearer token, an OAuth 2.0 client from the Developer Console can be used: POST /api/iceberg/v1/oauth/tokens (HTTP Basic client credentials) returns the access token to send as Authorization: Bearer ….

# Catalog JSON
curl -H "Authorization: Bearer $CONVERGE_TOKEN" $CONVERGE_URL/api/v2/catalog/export
# OpenAPI subset
curl -H "Authorization: Bearer $CONVERGE_TOKEN" \
  "$CONVERGE_URL/api/v2/catalog/export?format=openapi"
# Iceberg REST catalog discovery
curl -H "Authorization: Bearer $CONVERGE_TOKEN" $CONVERGE_URL/api/iceberg/v1/config

Point external BI tools at the OpenAPI document for schema/property discovery, and at the Iceberg REST catalog endpoint for tabular engines that resolve Iceberg tables.

Read-only SQL queries

You can explore a database source with ad hoc SQL:

POST /api/v2/connectivity/sources/{name}/query
Content-Type: application/json

{ "sql": "SELECT id, status FROM orders WHERE status = ?", "params": ["open"] }

Queries are read-only. Converge accepts exactly one SELECT (or WITH … SELECT) statement and rejects statements that could change data. Where the database supports it, the session is also opened read-only. Database drivers are a fixed, pinned set; you cannot upload your own driver.

Because a raw query can be powerful, running one needs write permission on the source in addition to read permission. Queries are audited.

External BI clients

Converge is readable from BI tools two ways: the PostgreSQL connector against the SQL Studio read-only role (the path for Power BI and Tableau, over a private route to Postgres, with one shared login for the deployment), and an opt-in Arrow Flight SQL endpoint for tools and libraries that speak it (ADBC, pyarrow.flight, Flight SQL JDBC/ODBC drivers). Both apply the same read-only limits.

Connection descriptors

GET /api/v2/datasets/{rid}/interop returns the connection facts for one dataset: the JDBC URL (including the dataset-scoped query) and the Tableau PostgreSQL descriptor (server, port, database, read-only user, plus a note), and the S3 prefix. GET /api/v2/datasets/{rid}/interop/credentials returns scoped, read-only credentials for that prefix. The SQL role's password comes from the deployment (CONVERGE_SQL_STUDIO_DB_PASSWORD); it is never returned by the API. A dataset that is not configured for interoperability answers configured: false instead of a descriptor.

The descriptor points at the deployment-internal PostgreSQL service (postgres:5432 in the compose deployment), not at a public endpoint, and Converge publishes no Postgres host port by design. An external Power BI/Tableau installation reaches it only through a route the operator adds: a private network or VPN, private link, a bastion tunnel, or a TLS-terminating proxy in front of Postgres. If no such route exists, use the Arrow Flight SQL endpoint instead — it is authenticated and built for external clients.

Power BI and Tableau

Neither has a native Flight SQL connector. They read a dataset through the connection descriptor (see "Connect Power BI or Tableau" below), over a private route the operator provides. That path signs in with the deployment's one read-only SQL Studio login: it identifies the deployment, not the person, so it has no per-user permissions, audit trail or revocation. Where those matter, use Arrow Flight SQL with a client that speaks it; every call there carries the user's own token.

Arrow Flight SQL

The gRPC listener is off by default: the deployment must set CONVERGE_FLIGHT_SQL_PORT (see the deploy notes for your installation). When enabled it serves the Flight SQL commands drivers expect — catalog/schema/table discovery, SQL info, and prepared statements — plus the pre-Flight-SQL raw SQL command. SQL still runs exactly like SQL Studio: SELECT/WITH only, on the read-only role, in a read-only transaction.

Prepared statements are create / execute / close only: parameter binding is not implemented (there is no DoPut), so a parameterised query must inline its literals (WHERE id = 42) instead of ? placeholders.

ADBC (pip install adbc-driver-flightsql):

from adbc_driver_flightsql import DatabaseOptions
from adbc_driver_flightsql.dbapi import connect

with connect(
    "flightsql://bi.example.com:8815",  # TLS by default; ?transport=tcp for plaintext
    db_kwargs={
        DatabaseOptions.AUTHORIZATION_HEADER.value: "Bearer <token>",
    },
) as conn, conn.cursor() as cur:
    # A backtick dataset reference resolves through the dataset engine.
    cur.execute("SELECT * FROM `Sales/Datasets/orders`")
    print(cur.fetch_arrow_table().to_pandas())

pyarrow.flight (no extra protobuf needed for the raw SQL command):

import pyarrow.flight as flight

client = flight.FlightClient("grpc+tls://bi.example.com:8815")
options = flight.FlightCallOptions(headers=[(b"authorization", b"Bearer <token>")])
info = client.get_flight_info(
    flight.FlightDescriptor.for_command(b"SELECT * FROM `Sales/Datasets/orders`"),
    options)
table = client.do_get(info.endpoints[0].ticket, options).read_all()

Putting it behind TLS

The listener is plaintext gRPC on the deployment network; terminate TLS in front of it. On Dokploy/Traefik a TCP router (dedicated entry point, e.g. 8815) forwards to ontology-api:8815:

tcp:
  routers:
    flightsql:
      entryPoints: [flightsql]
      rule: "HostSNI(`bi.example.com`)"
      service: flightsql
      tls:
        certResolver: letsencrypt
  services:
    flightsql:
      loadBalancer:
        servers:
          - address: "ontology-api:8815"

In Kubernetes the chart publishes the port on the ontology-api Service only when ontologyApi.flightSql.port is set, so a Traefik IngressRouteTCP (or another TCP proxy) can route HostSNI to service port flight-sql:

apiVersion: traefik.io/v1alpha1
kind: IngressRouteTCP
metadata: { name: ontology-api-flight-sql, namespace: <tenant> }
spec:
  entryPoints: [flightsql]
  routes:
    - match: HostSNI(`bi.example.com`)
      services:
        - name: ontology-api
          port: flight-sql
  tls: { certResolver: letsencrypt }

The chart's NetworkPolicy admits only the ingress-controller pods by default (traefik in kube-system); a different in-cluster proxy needs its own peer added to the rendered ontology-api-flight-sql policy.

FlightInfo endpoints name no location, so a client fetches results on the connection it opened; there is no host name to configure, and a client that connected with grpc+tls:// stays on TLS.

Auth, TLS and limits

• Auth. Every Flight SQL call needs a Bearer token in the authorization header (or the equivalent handshake); it is the same OIDC identity and permission model as the REST API, and the caller needs sqlquery:read or sqlquery:write. Metadata discovery, actions and statements are all authenticated.
• TLS. The Flight SQL listener is plaintext gRPC. It is not published as a host port: terminate TLS at your ingress/Traefik TCP router or in-cluster proxy (examples above) and forward to the internal port. In Kubernetes the Service is ClusterIP and the NetworkPolicy admits only the ingress controller unless you add a peer. Do not expose the plaintext port to the internet.
• Read-only. Statements are validated like SQL Studio (SELECT/WITH only, no DDL/DML, fixed table allowlist) and run on the SQL Studio read-only login inside a read-only transaction; queries time out after 20 seconds. Dataset references in backticks get the same read checks (markings, instance ACLs, restricted views) as the app. Prepared statements: create/execute/close only, no parameter binding.
• Row caps. Platform-table queries return at most 10,000 rows; dataset-engine queries are capped at 1,000,000 rows. Aggregate or filter in SQL rather than pulling large extracts.
• Discovery. Discovery lists the platform tables that are queryable; datasets are addressed with a backtick path, not as discovered tables. For a backtick dataset reference the result schema is only known at execution time: get_flight_info reports an empty schema and a prepared statement's dataset_schema is empty, while the do_get stream carries the real columns.

Syncs

A sync pulls data from a source into a dataset on a schedule (a cron expression or an interval) or on demand.

• Incremental syncs compare per-group counts and re-pull only the groups that changed, without needing database log access.
• CDC. Connectors that advertise CDC capture changes continuously, by polling an ordering column. PostgreSQL and Supabase sources can instead use logical replication: the sync reads inserts, updates and deletes from a replication slot, which needs wal_level=logical, a role with REPLICATION that owns the table, and a primary key.
• Syncs can be previewed, run manually, cloned and branched, and each run is recorded.

Bulk export to Parquet

Two endpoints materialize secured rows as one Parquet file in the lakehouse bucket, so external tools (Power BI, Tableau, DuckDB, a notebook) can read a snapshot without a live database connection. The v1 endpoints are synchronous and bounded by a row cap; larger runs will move to a job (follow-up).

POST /api/v2/objectTypes/{apiName}/export
POST /api/v2/datasets/{rid}/export

• Object sets are exported through the same secured page loader as the BI table read and loadObjects. Type permissions, property masking, instance ACLs, restricted views and data markings all apply, and only the properties you may see are written — a masked property cannot reappear as a column.
• Datasets need dataset:read plus visibility and the dataset's data classification. A view holds no rows of its own: export its backing dataset instead.
• Layout. bulk-exports/<ontology>/objects/<apiName>/<run>/part-000.parquet for an object set; bulk-exports/<ontology>/datasets/<rid>/<run>/part-000.parquet for a dataset.
• Lifetime. An export is a temporary copy: the bucket deletes everything under bulk-exports/ after one day. Download the file; do not treat the prefix as storage. One export per user runs at a time (a second request answers 429).
• Row cap. CONVERGE_BULK_EXPORT_MAX_ROWS (default 50000). The response's truncated: true means the cap cut rows; re-run after a filter or wait for the job follow-up.
• Credentials. The response carries temporary, read-only S3 credentials scoped to the export prefix — never the platform keys. Unconfigured MinIO or STS answers 503 with no keys and writes nothing. Every export records a data:export audit event (audit writes are best-effort, like the other export paths).

{
  "exportRid": "ri.converge.export.…",
  "rows": 4120,
  "bytes": 88321,
  "truncated": false,
  "s3": {"endpoint": "…", "bucket": "lakehouse", "prefix": "bulk-exports/converge/objects/orders/…"},
  "credentials": {"accessKeyId": "…", "secretAccessKey": "…", "sessionToken": "…",
                  "expiresAt": "…", "endpoint": "…",
                  "region": "us-east-1", "pathStyle": true}
}

Read the object with the vended credentials (any S3 client; the S3 CLI shown):

curl -s -X POST -H "Authorization: Bearer $TOKEN" \
  "$CONVERGE_URL/api/v2/objectTypes/orders/export"
# copy s3.endpoint, s3.bucket, s3.prefix and credentials from the response
AWS_ACCESS_KEY_ID="$ACCESS_KEY_ID" \
AWS_SECRET_ACCESS_KEY="$SECRET_ACCESS_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
  aws --endpoint-url "$S3_ENDPOINT" s3 cp \
  "s3://$BUCKET/$PREFIX/part-000.parquet" ./orders.parquet

Power BI (Get data > Parquet) and Tableau (Parquet connector) read the file directly; run the export from your own orchestrator on a schedule and point the refresh at the downloaded file for a recurring snapshot (the endpoint itself is a synchronous, one-shot export — there is no built-in scheduler or job for it yet). For live BI access instead of a snapshot, see the dataset interop descriptors in GET /api/v2/datasets/{rid}/interop.

Exports

An export sends dataset rows to an external system over HTTP (POST, PUT or PATCH), as files, or as a stream. Saved export definitions can run on a schedule or, for streaming exports, be started and stopped.

To export a dataset you must be able to read its data, including any markings it inherits.

External BI and S3 export

Tools outside Converge can read a dataset two ways: BI tools connect over PostgreSQL with the dataset's connection descriptor, and object-store clients read the dataset's transaction files with scoped S3 credentials. Both paths are read-only and require read permission on the dataset including the markings that gate visibility. The scoped S3 credentials additionally require the dataset's inherited data classification (403 when unmet), because they reach the raw files; the connection descriptor carries no credentials of its own. Neither path copies or exports data — they vend access details only.

Connection descriptors

GET /api/v2/datasets/{datasetRid}/interop returns the values an external tool needs to connect to one dataset.

GET /api/v2/datasets/{datasetRid}/interop

The response holds up to three descriptors:

{
  "datasetRid": "ri.datasets.main.orders",
  "jdbc": {
    "jdbcUrl": "jdbc:postgresql://<server>:<port>/<database>",
    "driverClass": "org.postgresql.Driver",
    "user": "converge_sql_studio",
    "query": "SELECT * FROM ds_rows WHERE dataset_rid='ri.datasets.main.orders'",
    "scope": "read-only, dataset rows via ds_rows"
  },
  "tableau": {
    "connection": "postgresql",
    "server": "<server>",
    "port": "<port>",
    "database": "<database>",
    "user": "converge_sql_studio",
    "note": "Tableau -> PostgreSQL connector; the same query"
  },
  "s3": {
    "endpoint": "<endpoint>",
    "bucket": "<bucket>",
    "prefix": "datasets/ri.datasets.main.orders/",
    "note": "SigV4; dataset transaction files live under this prefix"
  }
}

The JDBC and Tableau descriptors point at the deployment's read-only SQL Studio login, and jdbc.scope records that the access is read-only. jdbc.query is the statement that selects this dataset's rows from ds_rows; the Tableau descriptor carries connection fields only, so run the same query there. The descriptor names that user but never its password; whoever administers the deployment provisions it separately. A piece that is not configured on the deployment comes back as {"configured": false} in place of its descriptor.

Scoped S3 credentials

GET /api/v2/datasets/{datasetRid}/interop/credentials returns temporary, read-only object-storage credentials for the dataset's prefix.

It is a normal authenticated Converge API call: send your bearer token or use a signed-in session, and the caller needs read permission on the dataset plus its inherited markings and data classification. When the classification check is not met the endpoint answers 403 and vends no credentials.

GET /api/v2/datasets/{datasetRid}/interop/credentials

{
  "datasetRid": "ri.datasets.main.orders",
  "endpoint": "<endpoint>",
  "bucket": "<bucket>",
  "prefix": "datasets/ri.datasets.main.orders/",
  "region": "<region>",
  "accessKeyId": "<accessKeyId>",
  "secretAccessKey": "<secretAccessKey>",
  "sessionToken": "<sessionToken>",
  "expiresAt": "<expiresAt>",
  "readOnly": true
}

• Short-lived. These are temporary MinIO STS sessions; the exact expiry is in expiresAt. Request a new set before it expires instead of reusing an expired one. (The credential request asks for the 900 s minimum, but the MinIO client currently rounds session duration up to its 1-hour default, so expect up to about an hour.)
• Read-only and prefix-scoped. s3:GetObject applies only under datasets/<datasetRid>/ in the dataset bucket, and s3:ListBucket only for that prefix. Nothing else in the bucket is reachable.
• Deployment-configured storage. The bucket, endpoint and region come from the deployment's object-storage configuration; the caller does not choose them. The full path is s3://<bucket>/datasets/<datasetRid>/.
• SigV4 and path-style. Sign requests with Signature Version 4, use path-style addressing, and configure the region returned by the endpoint. The scoped session cannot look the bucket location up, so clients must be told the region rather than discovering it.
• Platform keys are never returned. The endpoint uses the deployment's own credentials to create the session and returns only the scoped session. If object storage or STS is not configured it fails closed with 503 and no credentials.

Connect Power BI or Tableau

Read the descriptor, then fill in the tool's PostgreSQL connection dialog:

• Power BI. Get Data → PostgreSQL database (or another PostgreSQL ODBC connector). Enter the server, port and database from jdbc.jdbcUrl, jdbc.user as the user name, and jdbc.query as the SQL statement the connection runs, so the model loads this dataset's rows.
• Tableau. Connect → PostgreSQL. Enter tableau.server, tableau.port and tableau.database, with tableau.user as the user name. The Tableau descriptor has no query of its own: run jdbc.query as a custom SQL query.

jdbc.query is what scopes a connection to one dataset, so keep the dataset_rid filter in the query when you adapt it.

Copy files with the AWS CLI

The S3 path is the dataset's transaction files in plain object storage: request credentials, export the response fields, and point any S3 client at the endpoint. These endpoints do not materialize a separate tabular export. For example, with the AWS CLI:

export AWS_ACCESS_KEY_ID='<accessKeyId>'
export AWS_SECRET_ACCESS_KEY='<secretAccessKey>'
export AWS_SESSION_TOKEN='<sessionToken>'
export AWS_DEFAULT_REGION='<region>'
export S3_ENDPOINT='<endpoint>'
export S3_BUCKET='<bucket>'
export DATASET_PREFIX='datasets/<datasetRid>/'

# The endpoint requires path-style addressing.
aws configure set default.s3.addressing_style path

aws --endpoint-url "$S3_ENDPOINT" s3 ls "s3://$S3_BUCKET/$DATASET_PREFIX"
aws --endpoint-url "$S3_ENDPOINT" s3 cp --recursive "s3://$S3_BUCKET/$DATASET_PREFIX" ./dataset-copy/

The same fields work from an S3 SDK. In boto3, set the endpoint, the session token and path-style addressing:

import boto3
from botocore.config import Config

s3 = boto3.client(
    "s3",
    endpoint_url="<endpoint>",
    region_name="<region>",
    aws_access_key_id="<accessKeyId>",
    aws_secret_access_key="<secretAccessKey>",
    aws_session_token="<sessionToken>",
    config=Config(signature_version="s3v4", s3={"addressing_style": "path"}),
)

prefix = "datasets/<datasetRid>/"
for item in s3.list_objects_v2(Bucket="<bucket>", Prefix=prefix).get("Contents", []):
    print(item["Key"])