DPF

How to connect your analytics tools to data managed by DPF.

Overview

On This Page

DPF stores your transformed data as Apache Iceberg tables and exposes them through a standard Iceberg REST Catalog endpoint. Because Iceberg is an open table format with a standardized catalog API, you can query your data with any compatible engine — without moving it, copying it, or setting up proprietary connectors. The platform is API-first throughout, so the same data and catalog access used here is also what powers our MCP server for AI agents.

This guide covers how to connect analytics engines across AWS, Azure, GCP, Databricks, and Snowflake to your tables using catalog federation, shortcuts, and native catalog integrations. Once configured, your tables are discoverable and queryable through familiar SQL interfaces in each ecosystem.

For everything that speaks JDBC or ODBC — BI tools, SQL IDEs like DBeaver, ORMs, and existing database applications — DPF also provides a PostgreSQL wire-protocol gateway at gateway.dpf-it.com:5432. Clients connect with the stock PostgreSQL driver (no custom driver needed), and your own PostgreSQL database can mount DPF as a linked server via postgres_fdw. See PostgreSQL Gateway below.

Read-Only Access via Federation

Catalog federation and shortcuts currently support read-only operations (SELECT, time travel). Write operations (INSERT, UPDATE, DELETE) are not yet supported through federated access. To modify data, use the DPF API, the PostgreSQL Gateway (which supports reads and writes), or connect directly via the REST Catalog with an open engine (Spark, Trino, PyIceberg).

Every path below starts at your analytics tools and ends at your Iceberg tables — what changes per platform is the piece in the middle: AWS, Azure, GCP's BigQuery, Databricks, and Snowflake connect with that vendor's own native driver through a federation/shortcut hop; Direct SQL Access uses the generic, unmodified PostgreSQL driver; and Open Engines connect straight to the REST catalog with the open-source Iceberg ecosystem's own client libraries. Pick a tab to see it.

Your Analytics Tools BI tools · SQL IDEs · ORMs · apps · postgres_fdw

↓

DPF PostgreSQL Gatewaygeneric PostgreSQL JDBC/ODBC/libpq driverRead + Write

Spark · Trino · PyIcebergopen-source Iceberg REST catalog client, directRead + Write

AthenaAWS console or native Athena JDBC/ODBC driver

Redshiftnative Redshift JDBC/ODBC driver or Query Editor

↓

↓

Glue Data Catalog
Read-only

SQL Server 2022 / Azure SQL MInative MSOLEDBSQL/ODBC driver, linked server

Synapse Serverless SQLnative T-SQL engine

↓

↓

Fabric OneLake Shortcut
Read-only

BigQuerynative BigQuery console/client

↓

BigQuery Omni
Read-only

Databricks SQL / Notebooknative Databricks SQL client or Spark session

↓

Unity Catalog Foreign Catalog
Read-only

Snowsight / SnowSQLnative Snowflake JDBC/ODBC driver or client

↓

Catalog Integration
Read-only

↓

DPF Iceberg REST Catalog Endpoint Apache Iceberg REST Specification

↓

Your Iceberg Tables (Managed by DPF)

Connections (Ingestion)

Connecting an AWS Account (S3)

A DPF connection is how DPF authenticates to an external source to pull data in on a schedule (a trigger). This is the opposite direction from the "Query with AWS" section below — that's for querying DPF's tables from your AWS account; this is for DPF pulling files into DPF from an S3 bucket you own.

DPF never asks for or stores long-lived AWS credentials (access keys). Instead it uses the AWS-standard pattern for third-party SaaS access: you create an IAM role in your own account that trusts a stable, dedicated DPF role, gated by a unique ExternalId DPF generates for your connection. DPF then calls sts:AssumeRole on demand to get short-lived credentials — you can revoke access at any time by deleting or editing the role, with no need to contact DPF.

Prerequisites

• An active DPF workspace with full access
• An S3 bucket (in your own AWS account) containing the files you want DPF to pull
• IAM permissions to create a role in your AWS account

Step-by-Step Setup

1. Create the connection Call create-connection with type: "aws_s3" and the ARN of the role you intend to create (it doesn't need to exist yet). DPF generates an externalId and returns a ready-to-use trust policy.

   curl -X POST https://api.dpf-it.com/connections \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "create-connection",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "type": "aws_s3",
       "roleArn": "arn:aws:iam::YOUR_ACCOUNT_ID:role/dpf-ingestion"
     }'
   # Response includes:
   # {
   #   "success": true,
   #   "data": {
   #     "connectionId": "...",
   #     "externalId": "3f9c2e1a-5b6d-4c7e-8f9a-0b1c2d3e4f5a",
   #     "dpfPrincipalArn": "arn:aws:iam::442707444240:role/dpf-aws-connector",
   #     "trustPolicy": { ... },
   #     "message": "..."
   #   }
   # }
   

2. Create the IAM role in your account Use the returned trustPolicy verbatim as the role's trust policy. Notice where the two values from Step 1 go: dpfPrincipalArn is the trust policy's Principal (this is DPF's stable connector role — the same one for every DPF customer), and your connection's externalId goes in the Condition block as sts:ExternalId. That condition is what stops any other DPF customer's connection from assuming your role — without it, anyone who knew the principal ARN (which is not a secret) could try to assume it.

   aws iam create-role \
     --role-name dpf-ingestion \
     --assume-role-policy-document '{
       "Version": "2012-10-17",
       "Statement": [{
         "Effect": "Allow",
         "Principal": {"AWS": "arn:aws:iam::442707444240:role/dpf-aws-connector"},
         "Action": "sts:AssumeRole",
         "Condition": {"StringEquals": {"sts:ExternalId": "3f9c2e1a-5b6d-4c7e-8f9a-0b1c2d3e4f5a"}}
       }]
     }'
   

3. Attach a scoped-down permissions policy The trust policy above only controls who can assume the role — it grants no S3 access by itself. Attach a separate permissions policy that grants only what DPF actually needs: scope Resource to the specific bucket (and prefix, if you're only feeding one subfolder) rather than "arn:aws:s3:::*", and grant only the actions your trigger uses. Read-only is shown below; add s3:DeleteObject if your post-processing rules delete files, or s3:PutObject / s3:DeleteObject together if they archive (copy + delete) files.

   aws iam put-role-policy \
     --role-name dpf-ingestion \
     --policy-name dpf-s3-read \
     --policy-document '{
       "Version": "2012-10-17",
       "Statement": [{
         "Effect": "Allow",
         "Action": ["s3:GetObject", "s3:ListBucket"],
         "Resource": [
           "arn:aws:s3:::YOUR_BUCKET",
           "arn:aws:s3:::YOUR_BUCKET/*"
         ]
       }]
     }'
   # Narrower still: scope Resource to a prefix instead of the whole bucket,
   # e.g. "arn:aws:s3:::YOUR_BUCKET/exports/daily/*", if DPF only needs one
   # subfolder — pair with a ListBucket s3:prefix condition to also keep the
   # bucket-level listing scoped to that same prefix.
   

4. Test the connection DPF assumes your role and confirms it works before the connection can be used in a trigger.

   curl -X POST https://api.dpf-it.com/connections \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "test-connection",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "connectionId": "YOUR_CONNECTION_ID"
     }'
   

5. Create a trigger to pull on a schedule Once the connection has passed test, create a trigger against it with the bucket (and optional prefix) to poll. The referenced spec must already have been analyzed once.

   curl -X POST https://api.dpf-it.com/job-triggers \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "create-trigger",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "type": "aws_s3",
       "specName": "web-logs",
       "connectionId": "YOUR_CONNECTION_ID",
       "s3Bucket": "YOUR_BUCKET",
       "s3Prefix": "exports/daily/",
       "frequency": {"unit": "daily", "hourOfDay": 6},
       "dedupe": true
     }'
   

Revoking Access

Because DPF holds no standing credential, you can cut off access at any time from your own AWS account — delete the IAM role, remove its trust statement, or revoke the permissions policy. The next scheduled run will fail and DPF automatically marks the connection as untested, blocking further use until it passes test-connection again.

Connecting via SFTP

For an SFTP connection, DPF generates an RSA-4096 keypair when the connection is created. The public key is returned so you can install it on your own SFTP server; the private key is retained by DPF and is never returned by any API call.

Prerequisites

• An active DPF workspace with full access
• An SFTP server you control, with a user account DPF will connect as
• Ability to edit that user's ~/.ssh/authorized_keys file on the server

Step-by-Step Setup

1. Create the connection Call create-connection with type: "sftp", the server's hostname, and the username DPF should connect as (defaults to sftpuser if omitted). DPF generates the keypair and returns the public key.

   curl -X POST https://api.dpf-it.com/connections \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "create-connection",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "type": "sftp",
       "hostname": "sftp.example.com",
       "username": "sftpuser"
     }'
   # Response includes:
   # {
   #   "success": true,
   #   "data": {
   #     "connectionId": "...",
   #     "publicKey": "ssh-rsa AAAAB3NzaC1yc2EAAA... dpf-446655440000",
   #     "message": "..."
   #   }
   # }
   

2. If the connection's user doesn't already exist, create it Skip this if username is an existing SFTP account on the server — it already has a .ssh directory. A world- or group-writable .ssh directory is commonly rejected outright by SSH, so permissions matter here.

   sudo useradd -m sftpuser
   sudo -iu sftpuser
   mkdir -p ~/.ssh
   chmod 700 ~/.ssh
   

3. Install the public key on your SFTP server As the connection's username on the server (or an administrator acting on that user's behalf), append the returned publicKey line as-is to that user's authorized_keys file. A world- or group-writable authorized_keys file is commonly rejected outright too.

   # As an administrator, become the connection's user (sftpuser here):
   sudo -iu sftpuser
   echo 'ssh-rsa AAAAB3NzaC1yc2EAAA... dpf-446655440000' >> ~/.ssh/authorized_keys
   chmod 600 ~/.ssh/authorized_keys
   

4. Test the connection DPF connects with the private key it retained and lists the user's home directory. An empty directory still counts as a pass — this step is only verifying authentication works.

   curl -X POST https://api.dpf-it.com/connections \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "test-connection",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "connectionId": "YOUR_CONNECTION_ID"
     }'
   

5. Create a trigger to pull on a schedule Once the connection has passed test, create a trigger against it. The referenced spec must already have been analyzed once.

   curl -X POST https://api.dpf-it.com/job-triggers \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "action": "create-trigger",
       "workspaceId": "YOUR_WORKSPACE_ID",
       "type": "sftp",
       "specName": "web-logs",
       "connectionId": "YOUR_CONNECTION_ID",
       "frequency": {"unit": "daily", "hourOfDay": 6},
       "dedupe": true,
       "preRules": "Process only *.csv files under /outbound",
       "postRules": "Rename each processed file with a .done suffix"
     }'
   

Revoking Access

Remove the corresponding line from that user's authorized_keys file at any time to cut off access — no need to contact DPF. The next scheduled run will fail to authenticate and DPF automatically marks the connection as untested, blocking further use until it passes test-connection again (which requires the key to be reinstalled).

Direct SQL Access

PostgreSQL Gateway (JDBC / ODBC)

DPF operates a PostgreSQL wire-protocol gateway at gateway.dpf-it.com:5432. To any client it looks like a Postgres server, so you connect with the unmodified, official PostgreSQL JDBC or ODBC driver — there is no custom DPF driver to install. This is the integration path for BI tools, SQL IDEs (DBeaver, DataGrip), psql, ORMs, and for mounting DPF as a linked server inside an existing database application.

Every SQL statement you run is forwarded to the DPF query engine and executed directly against your Iceberg tables. Unlike the read-only federation paths above, the gateway supports both reads and writes (SELECT, INSERT, UPDATE, DELETE), with the same per-user authorization and credit metering as the DPF API.

Connection Settings

Field	Value
Host	gateway.dpf-it.com
Port	5432
Database	Your workspace namespace (last 12 characters of your workspaceId, e.g. 633def9656c1)
Schema	Always default
Username	Your DPF account email
Password	Your DPF account password
SSL / TLS	Required (sslmode=require)

JDBC URL:

jdbc:postgresql://gateway.dpf-it.com:5432/<namespace>?sslmode=require

psql (or anything else built on libpq):

psql "host=gateway.dpf-it.com port=5432 dbname=<namespace> user=<email> sslmode=require"

Switching Workspaces

Either reconnect with a different database name, or run USE <namespace>; in an open session. Only namespaces your account is authorized for are allowed.

Connecting Is Free

Driver handshake chatter, SET commands, and catalog/introspection probes are answered by the gateway itself and never metered. Only the real SQL you run against your tables consumes query credits — the same rates as the DPF API.

Per-Statement Commit — No Transactions

The gateway runs in auto-commit mode. BEGIN / COMMIT / ROLLBACK are accepted but are no-ops: each statement commits independently and there is no cross-statement rollback. If a multi-statement batch fails partway, the error reports which statement (1-based) failed; earlier statements have already committed and are yours to undo.

Credential Handling

Authentication uses your DPF email and password, which means the password is stored in your tool's saved connection settings. TLS (sslmode=require, enforced by the gateway) protects it in transit only — treat saved connection files accordingly.

Current Limitations

Feature	Status	Notes
SELECT / INSERT / UPDATE / DELETE	✅	Forwarded to the query engine; per-statement commit
Multi-statement batches	✅	Executed left to right, each statement commits independently
Simple and extended query protocol	✅	Both simple queries and server-side prepared statements (extended protocol) are fully supported
Schema browsing (GUI catalog tree, ODBC SQLTables/SQLColumns)	✅	Tables and columns are served from an emulated pg_catalog backed by your live table metadata
Transactions / rollback	❌	Auto-commit only (see callout above)
Temp tables, COPY, stored procedures, LISTEN/NOTIFY, cursors	❌	Rejected with SQLSTATE 0A000 (feature not supported)

Connecting DBeaver & BI Tools

Any tool with a PostgreSQL connector works with the gateway. DBeaver is shown step-by-step below; DataGrip, Tableau, and ODBC-based tools follow the same pattern with the connection settings from the previous section.

DBeaver Setup

1. Create a new PostgreSQL connection Database → New Database Connection → PostgreSQL. DBeaver will offer to download the official PostgreSQL JDBC driver automatically — accept it (any recent version works).
2. Fill in the Main tab

   Field	Value
   Host	gateway.dpf-it.com
   Port	5432
   Database	Your namespace (e.g. 633def9656c1)
   Username	Your DPF account email
   Password	Your DPF account password

3. Require SSL On the SSL tab, check Use SSL and set SSL mode to require.
4. Test and connect Click Test Connection, then Finish. Open a SQL editor (SQL Editor → New SQL Script) and query your tables directly:

   SELECT * FROM customers LIMIT 100;
   SELECT dpf_filename, COUNT(*) AS row_count
   FROM customers
   GROUP BY dpf_filename
   ORDER BY row_count DESC;
   

Schema Browsing Works

The gateway emulates the pg_catalog and information_schema queries GUI tools issue, so DBeaver's database navigator shows your tables and columns with their types. Metadata is served from the gateway's cached view of your workspace (refreshed every few minutes) and is never billed as a query. Keys, indexes, and constraints show as empty — Iceberg tables don't have them.

Other Tools

• DataGrip / JetBrains IDEs: create a PostgreSQL data source with the same values and set SSL to require. No driver property overrides are needed.
• Tableau / Power BI / other BI tools: use the generic PostgreSQL connector with the same host, port, database, and credentials, with SSL required. Schema browsing and custom SQL both work.
• ODBC (psqlODBC): use the stock PostgreSQL Unicode ODBC driver with SSLmode=require:

  Driver={PostgreSQL Unicode};Server=gateway.dpf-it.com;Port=5432;Database=<namespace>;Uid=<email>;Pwd=<password>;SSLmode=require;
  

  Passthrough SQL and the ODBC catalog functions (SQLTables, SQLColumns) both work — schema-browsing applications see your tables and columns. Keep UseDeclareFetch at its default of 0 (server-side cursors are not supported).

Linked Server from PostgreSQL (postgres_fdw)

Because the gateway speaks the Postgres wire protocol, your own PostgreSQL database can mount DPF as a foreign server using the built-in postgres_fdw extension — the Postgres equivalent of a SQL Server linked server. Your DPF tables then appear as foreign tables inside your existing database, queryable and joinable with your local data in plain SQL.

┌─────────────────────┐ │ Your PostgreSQL │ │ (existing app DB) │ └──────────┬──────────┘ │ postgres_fdw (foreign server) ▼ ┌─────────────────────────────┐ │ DPF PostgreSQL Gateway │ │ gateway.dpf-it.com:5432 │ └──────────────┬──────────────┘ │ DPF Query API ▼ ┌─────────────────────────────┐ │ Your Iceberg Tables │ └─────────────────────────────┘

Setup

1. Enable the extension postgres_fdw ships with PostgreSQL — no third-party install needed.

   CREATE EXTENSION IF NOT EXISTS postgres_fdw;
   

2. Create the foreign server The dbname is your workspace namespace (last 12 characters of your workspaceId).

   CREATE SERVER dpf FOREIGN DATA WRAPPER postgres_fdw
       OPTIONS (host 'gateway.dpf-it.com', port '5432', dbname '633def9656c1');
   

3. Map your local user to your DPF account

   CREATE USER MAPPING FOR CURRENT_USER SERVER dpf
       OPTIONS (user 'you@example.com', password 'your-dpf-password');
   

4. Define foreign tables Declare the columns to match your DPF table (the schema on the DPF side is always default). You can see each table's columns in the DPF workspace UI.

   CREATE FOREIGN TABLE customers (
       customer_id   text,
       name          text,
       email         text,
       dpf_filename  text,
       dpf_job       text,
       dpf_ts        timestamp
   ) SERVER dpf
     OPTIONS (schema_name 'default', table_name 'customers');
   

5. Query DPF data from inside your database

   -- Read DPF data like any local table
   SELECT * FROM customers WHERE dpf_ts >= '2026-06-01' LIMIT 100;
   -- Join DPF data with your application's local tables
   SELECT
       c.customer_id,
       c.name,
       o.order_id,
       o.order_total
   FROM customers c              -- foreign table (DPF)
   JOIN app.orders o             -- local table
     ON o.customer_id = c.customer_id
   WHERE o.order_total > 1000;
   

Writing Through the Link

Foreign tables are writable. INSERTs and fully pushed-down (“direct modify”) UPDATE/DELETE statements are forwarded to the DPF engine and committed per statement:

-- Insert into a DPF table from local data
INSERT INTO customers (customer_id, name, email)
SELECT id, full_name, email FROM app.new_signups;

-- Direct-modify update (whole predicate pushed down)
UPDATE customers SET email = lower(email) WHERE email <> lower(email);

-- Direct-modify delete
DELETE FROM customers WHERE dpf_filename = 'bad_batch.csv';

Keep Write Predicates Pushable

UPDATE and DELETE work only when PostgreSQL can push the entire statement down to DPF (“direct modify”): no joins against local tables in the modify statement, and predicates using common operators/functions. Statements that fall back to postgres_fdw 's row-by-row mode (which relies on Postgres ctid s) are rejected — Iceberg tables have no ctid. Writes commit per statement: a large INSERT ... SELECT fans out into batches that each commit independently, so a mid-batch failure leaves earlier rows committed.

Pushdown Compatibility

postgres_fdw ships filters, joins, and aggregates to the remote side when it considers them safe. The DPF engine is highly Postgres-compatible but not identical, so a pushed-down function it doesn't support will surface as a query error. Keep foreign-table predicates to common operators and functions; if a specific expression errors remotely, rewrite it or apply it locally over the fetched rows.

Coming Soon: IMPORT FOREIGN SCHEMA

Today foreign tables are declared manually with CREATE FOREIGN TABLE. Support for IMPORT FOREIGN SCHEMA "default" FROM SERVER dpf INTO ... — which auto-generates all table definitions from the DPF catalog — is on the roadmap.

Query with AWS

Setting Up the Glue Data Catalog Integration

AWS Glue Data Catalog supports catalog federation for remote Iceberg REST catalogs. This feature connects Glue to the DPF catalog endpoint, synchronizing metadata at query time so that AWS analytics engines can discover your tables without any data movement.

Once configured, Athena and Redshift see your DPF tables as if they were native Glue tables — with Lake Formation providing fine-grained access control on top.

Prerequisites

• An active DPF workspace with at least one completed data load job
• Your DPF REST Catalog endpoint URL: https://api.dpf-it.com/iceberg/v1
• OAuth2 client credentials for catalog authentication (see Step 1 below to generate)
• AWS account with IAM permissions for Glue, Lake Formation, Athena, and Secrets Manager
• An IAM role with read access to the S3 location where DPF stores your data files

One-Time Setup

The following steps only need to be performed once per workspace. After the Glue connection is configured, Athena and Redshift will automatically authenticate using the stored credentials whenever you run a query — no further configuration is needed.

Required: Complete Cross-Account Setup with DPF

Your table data is stored in DPF's AWS account. With Glue catalog federation, Athena and Redshift read those data files directly from Amazon S3 using your own AWS account's credentials — which means your account must be granted cross-account access to your workspace's storage before federated queries can return data. Metadata federation works as soon as you finish the steps below, but data-file reads will fail with an access-denied error until this authorization is in place. After creating your IAM role in Step 3.1 (Create IAM role for Glue federation), contact us with your AWS account ID, workspace ID, your client ID, and the ARN of the IAM role you created in Step 3.1. Our team will authorize that role for your specific workspace. This is a one-time step per workspace.

Connecting directly with an open engine (Spark, Trino, PyIceberg) instead? No cross-account request is needed — the REST catalog vends scoped, short-lived storage credentials to those clients automatically.

Step-by-Step Setup

1. Generate an API credential from the DPF API Create an OAuth2 client credential for your account (Settings → API Credentials in the DPF UI, or the API call below). It's an account-level credential — the same one works across every workspace you have access to, not just this one. This is a one-time operation — save the clientSecret immediately, as it cannot be retrieved again.

   # Generate an API credential (one-time)
   curl -X POST https://api.dpf-it.com/oauth/clients \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "client_name": "Glue Data Catalog Federation"
     }'
   # Response contains clientId and clientSecret (save immediately!)
   # {
   #   "success": true,
   #   "data": {
   #     "clientId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
   #     "clientSecret": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...",
   #     "clientName": "Glue Data Catalog Federation"
   #   }
   # }
   

2. Store your OAuth2 credentials in Secrets Manager Store the client secret from Step 1 in AWS Secrets Manager using the key name USER_MANAGED_CLIENT_APPLICATION_CLIENT_SECRET. Glue uses this key name to retrieve the secret during OAuth2 authentication.

   aws secretsmanager create-secret \
     --name "dpf/api-credentials" \
     --secret-string '{"USER_MANAGED_CLIENT_APPLICATION_CLIENT_SECRET":"YOUR_CLIENT_SECRET"}' \
     --region us-east-1
   

3. Create a federated catalog in Glue First, create an IAM role that Glue can assume to access Secrets Manager and your Iceberg data. Then create the connection and federated catalog.

   # 1. Create IAM role for Glue federation
   aws iam create-role \
     --role-name DPFGlueFederationRole \
     --assume-role-policy-document '{
       "Version": "2012-10-17",
       "Statement": [{
         "Effect": "Allow",
         "Principal": {"Service": "glue.amazonaws.com"},
         "Action": "sts:AssumeRole"
       }]
     }'
   # 2. Attach policy granting access to Secrets Manager and S3 data files
   aws iam put-role-policy \
     --role-name DPFGlueFederationRole \
     --policy-name dpf-federation-access \
     --policy-document '{
       "Version": "2012-10-17",
       "Statement": [
         {
           "Effect": "Allow",
           "Action": [
             "secretsmanager:GetSecretValue",
             "secretsmanager:DescribeSecret",
             "secretsmanager:PutSecretValue"
           ],
           "Resource": ["arn:aws:secretsmanager:us-east-1:ACCOUNT:secret:dpf/api-credentials*"]
         },
         {
           "Effect": "Allow",
           "Action": ["s3:GetObject", "s3:ListBucket"],
           "Resource": [
             "arn:aws:s3:::dpf-storage",
             "arn:aws:s3:::dpf-storage/*"
           ]
         },
         {
           "Effect": "Allow",
           "Action": ["glue:GetDatabase", "glue:GetDatabases", "glue:GetTable", "glue:GetTables"],
           "Resource": ["*"]
         }
       ]
     }'
   # 3. Create the Glue connection (wait ~10s for IAM propagation)
   aws glue create-connection \
     --connection-input '{
       "Name": "dpf-catalog-connection",
       "ConnectionType": "ICEBERGRESTCATALOG",
       "ConnectionProperties": {
         "INSTANCE_URL": "https://api.dpf-it.com/iceberg/v1",
         "ROLE_ARN": "arn:aws:iam::ACCOUNT:role/DPFGlueFederationRole"
       },
       "AuthenticationConfiguration": {
         "AuthenticationType": "OAUTH2",
         "OAuth2Properties": {
           "OAuth2GrantType": "CLIENT_CREDENTIALS",
           "TokenUrl": "https://api.dpf-it.com/oauth/token",
           "OAuth2ClientApplication": {
             "UserManagedClientApplicationClientId": "YOUR_CLIENT_ID"
           }
         },
         "SecretArn": "arn:aws:secretsmanager:us-east-1:ACCOUNT:secret:dpf/api-credentials"
       }
     }' \
     --region us-east-1
   # 4. Create the federated catalog
   aws glue create-catalog \
     --name "dpf-data" \
     --catalog-input '{
       "FederatedCatalog": {
         "ConnectionName": "dpf-catalog-connection",
         "Identifier": "dpf"
       },
       "CreateDatabaseDefaultPermissions": [],
       "CreateTableDefaultPermissions": []
     }' \
     --region us-east-1
   

4. Grant Lake Formation permissions Grant your analytics IAM role access to the federated catalog so Athena and Redshift can query it.

   aws lakeformation grant-permissions \
     --principal '{"DataLakePrincipalIdentifier":"arn:aws:iam::ACCOUNT:role/YourAnalyticsRole"}' \
     --resource '{"Catalog":{"Id":"dpf-data"}}' \
     --permissions "ALL" \
     --region us-east-1
   

5. Verify table discovery List the databases (namespaces) visible through the federated catalog. Each DPF workspace appears as a separate database.

   aws glue get-databases \
     --catalog-id "dpf-data" \
     --region us-east-1
   

Namespace Mapping

DPF uses your workspaceId as the Iceberg namespace. Each workspace's tables appear as a separate database in the federated catalog, providing natural multi-tenant isolation.

Real-Time Metadata

Catalog federation fetches metadata from the DPF REST Catalog at query time. When new data is loaded via DPF, your tables are immediately visible without any sync or refresh steps.

Querying with Amazon Athena

Athena provides serverless, pay-per-query SQL access to your DPF tables through the federated catalog. There is no infrastructure to provision — you pay only for the bytes scanned.

Read-Only via Federation

When querying through a federated catalog, Athena supports read operations only (SELECT, time travel). INSERT, UPDATE, DELETE, and MERGE are not supported on federated tables.

Setup

1. Open the Athena console Navigate to the Athena query editor. Ensure you have a workgroup configured with an S3 results location for query output.
2. Select the federated catalog In the query editor, use the catalog/database selector to choose dpf-data and your workspace database. Alternatively, use three-part naming in your SQL.
3. Run your first query Reference the federated catalog, workspace namespace, and table name:

   SELECT *
   FROM "dpf-data".<workspace_namespace>.<table_name>
   LIMIT 100;
   

Supported Operations

Operation	Supported	Notes
SELECT	✅	Full SQL with joins, aggregations, window functions
Time Travel	✅	Query historical snapshots by timestamp
Metadata Tables	✅	Query $snapshots, $files, $partitions
INSERT INTO	❌	Not supported on federated catalogs
UPDATE	❌	Not supported on federated catalogs
DELETE	❌	Not supported on federated catalogs
MERGE INTO	❌	Not supported on federated catalogs

Examples

Basic query with DPF audit fields:

SELECT
    customer_id,
    name,
    email,
    dpf_filename,
    dpf_job,
    dpf_ts
FROM "dpf-data".my_workspace.customers
LIMIT 100;

Time-travel query — view data as it existed at a specific point in time:

SELECT *
FROM "dpf-data".my_workspace.customers
FOR TIMESTAMP AS OF TIMESTAMP '2026-06-10 12:00:00';

Aggregation by source file:

SELECT
    dpf_filename,
    COUNT(*) AS row_count,
    MIN(dpf_ts) AS earliest_load,
    MAX(dpf_ts) AS latest_load
FROM "dpf-data".my_workspace.customers
GROUP BY dpf_filename
ORDER BY row_count DESC;

Query snapshot history:

SELECT *
FROM "dpf-data".my_workspace."customers$snapshots"
ORDER BY committed_at DESC;

Cost Model

• $5 per TB scanned — only charged for data read by your query
• $0 when idle — no charges when not querying
• Use column projections (select only needed columns) to minimize scanned data
• Partitioned tables automatically prune unneeded data files

Querying with Amazon Redshift

Amazon Redshift Serverless provides a managed SQL analytics engine that can query your DPF tables through the federated catalog. Redshift is ideal for sustained analytical workloads, BI tool integration, and scenarios requiring complex joins across multiple tables.

Read-Only via Federation

Like Athena, Redshift access through a federated catalog is currently read-only. Write operations are not supported on federated tables.

Setup

1. Create a Redshift Serverless workgroup If you don't already have one, create a workgroup and namespace in the Redshift console. Ensure the associated IAM role has Lake Formation permissions on the dpf-data federated catalog.
2. Query using three-part notation Reference the federated catalog directly in your SQL:

   SELECT *
   FROM "dpf-data".<workspace_namespace>.<table_name>
   LIMIT 100;
   

3. Alternatively, create an external schema For simpler two-part notation, create an external schema pointing to the federated database:

   CREATE EXTERNAL SCHEMA dpf_workspace
   FROM DATA CATALOG
   DATABASE '<workspace_namespace>'
   CATALOG_ID 'dpf-data'
   IAM_ROLE 'arn:aws:iam::ACCOUNT:role/RedshiftLakeFormationRole';
   

   Then query with two-part notation:

   SELECT * FROM dpf_workspace.customers LIMIT 100;
   

Supported Operations

Operation	Supported	Notes
SELECT	✅	Full SQL with Redshift optimizations
Joins across tables	✅	Join DPF tables with each other or with Redshift tables
Materialized Views	✅	Cache frequent queries for faster access
Window Functions	✅	Full analytic function support
INSERT / UPDATE / DELETE	❌	Not supported on federated tables
MERGE	❌	Not supported on federated tables

Examples

Aggregation by load job:

SELECT
    dpf_job,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT dpf_filename) AS file_count,
    MIN(dpf_ts) AS job_start,
    MAX(dpf_ts) AS job_end
FROM "dpf-data".my_workspace.customers
GROUP BY dpf_job
ORDER BY job_start DESC;

Join across DPF tables:

SELECT
    c.customer_id,
    c.name,
    a.account_id,
    a.balance
FROM "dpf-data".my_workspace.customers c
JOIN "dpf-data".my_workspace.accounts a
  ON c.customer_id = a.customer_id
WHERE a.balance > 10000;

Create a materialized view for frequent queries:

CREATE MATERIALIZED VIEW mv_customer_summary AS
SELECT
    dpf_job,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT dpf_filename) AS file_count
FROM "dpf-data".my_workspace.customers
GROUP BY dpf_job;

Athena vs. Redshift — When to Use Which

Consideration	Athena	Redshift Serverless
Pricing model	Per-query ($5/TB scanned)	Per-RPU-hour (~$0.375/RPU-hr)
Idle cost	$0	Near-$0 (scales to zero, ~30s cold start)
Best for	Ad-hoc queries, data exploration	Sustained workloads, dashboards, BI tools
Setup complexity	Zero infrastructure	Workgroup + namespace + IAM role
Advanced features	Time travel, metadata queries	Materialized views, cross-database joins
Write support	❌ (via federation)	❌ (via federation)

Recommendation

For most DPF users doing data inspection, validation, and ad-hoc analysis, Athena is the simplest and most cost-effective option. Choose Redshift Serverless when you need sustained concurrent queries, materialized views, or integration with BI tools like QuickSight, Tableau, or Looker.

Need Write Access?

For INSERT, UPDATE, and DELETE operations, connect through the PostgreSQL Gateway (standard JDBC/ODBC, read + write), or connect directly to the DPF Iceberg REST Catalog using an open engine like Apache Spark, Trino, or PyIceberg.

Query with Azure

Setting Up Microsoft Fabric Shortcuts

Microsoft Fabric connects to external Iceberg catalogs through OneLake Shortcuts. A shortcut creates a virtualized reference to your tables, allowing Fabric workloads (SQL Analytics Endpoint, Lakehouse, Notebooks) to query your data as if it were stored locally in OneLake — without copying or moving anything.

Once a shortcut is configured, your tables appear as native Fabric tables and can be queried with T-SQL from SQL Server, Synapse, or any tool connected to the Fabric SQL Analytics Endpoint.

Prerequisites

• An active DPF workspace with at least one completed data load job
• Your DPF REST Catalog endpoint URL: https://api.dpf-it.com/iceberg/v1
• An OAuth2 API credential for your DPF account (generate via POST /oauth/clients)
• A Microsoft Fabric workspace with at least Contributor access
• A Fabric Lakehouse created within the workspace

Step-by-Step Setup

1. Generate an API credential from the DPF API If you haven't already created an API credential for your DPF account, generate one now (Settings → API Credentials in the DPF UI, or the API call below). It's account-level — the same credential used for the AWS integration works here too.

   curl -X POST https://api.dpf-it.com/oauth/clients \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "client_name": "Microsoft Fabric Shortcut"
     }'
   

2. Open your Fabric Lakehouse Navigate to your Microsoft Fabric workspace, then open the Lakehouse where you want to surface your tables. Select the Tables section in the Explorer pane.
3. Create an Iceberg table shortcut Click New shortcut → select Apache Iceberg as the source type. Fill in the connection details:

   Field	Value
   Catalog URL	https://api.dpf-it.com/iceberg/v1
   Authentication	OAuth2 Client Credentials
   Token endpoint	https://api.dpf-it.com/oauth/token
   Client ID	Your DPF client ID from Step 1
   Client Secret	Your DPF client secret from Step 1
   Namespace	Your workspace namespace (last 12 characters of your workspaceId, e.g. 633def9656c1)

4. Select tables to shortcut After connecting, Fabric displays the available tables in your namespace. Select the tables you want to surface (e.g., customers, accounts) and click Create.
5. Verify in the Lakehouse Explorer Your tables now appear in the Tables section of the Lakehouse with a shortcut icon. They are queryable immediately via the SQL Analytics Endpoint.

Namespace Mapping

The Iceberg namespace is derived from your workspaceId — specifically the last 12 characters. For example, if your workspaceId is a9d4d243-d856-4558-84ba-633def9656c1, the namespace is 633def9656c1. You can find this value in your workspace settings or from the DPF API's get-status response.

Automatic Metadata Refresh

Fabric shortcuts fetch Iceberg metadata at query time. When new data is loaded via DPF, your shortcut tables reflect the latest state automatically — no manual refresh required.

Read-Only Access

Iceberg shortcuts in Microsoft Fabric are read-only. You can query, aggregate, and join the data, but INSERT, UPDATE, and DELETE are not supported through shortcuts. To modify data, use the DPF API or connect directly via the REST Catalog.

Querying with SQL Server

SQL Server 2022 and Azure SQL Managed Instance can query your tables through linked servers pointing to a Fabric SQL Analytics Endpoint or Synapse Serverless SQL pool. This enables T-SQL access to your Iceberg data from within your existing SQL Server databases.

Architecture

SQL Server does not connect directly to Iceberg catalogs. Instead, it queries through an intermediary that natively supports Iceberg — either the Fabric SQL Analytics Endpoint or a Synapse Serverless SQL pool — using a linked server connection.

┌────────────────────┐ │ SQL Server 2022 │ │ (or Azure SQL MI)│ └─────────┬──────────┘ │ Linked Server (ODBC / MSOLEDBSQL) ▼ ┌─────────────────────────────┐ │ Fabric SQL Analytics │ │ Endpoint │ │ ─── OR ─── │ │ Synapse Serverless SQL │ └──────────────┬──────────────┘ │ Iceberg shortcut / OPENROWSET ▼ ┌─────────────────────────────┐ │ Your Tables (via shortcut) │ └─────────────────────────────┘

Option A: Via Fabric SQL Analytics Endpoint

Once you've created Iceberg shortcuts in a Fabric Lakehouse, the Lakehouse's SQL Analytics Endpoint exposes them as T-SQL-queryable tables.

1. Get the SQL Analytics Endpoint connection string In your Fabric Lakehouse, click SQL Analytics Endpoint in the top bar. Copy the server name (e.g., xxxxxxxx.datawarehouse.fabric.microsoft.com).
2. Create a linked server in SQL Server

   -- Create linked server to Fabric SQL Analytics Endpoint
   EXEC sp_addlinkedserver
       @server = N'DPF_FABRIC',
       @srvproduct = N'',
       @provider = N'MSOLEDBSQL',
       @datasrc = N'your-endpoint.datawarehouse.fabric.microsoft.com',
       @catalog = N'your_lakehouse';
   -- Configure authentication (Azure AD / Entra ID)
   EXEC sp_addlinkedsrvlogin
       @rmtsrvname = N'DPF_FABRIC',
       @useself = N'FALSE',
       @rmtuser = N'your-azure-ad-user@domain.com',
       @rmtpassword = N'your-password-or-token';
   

3. Query your tables through the linked server

   -- Four-part naming: LinkedServer.Database.Schema.Table
   SELECT *
   FROM DPF_FABRIC.your_lakehouse.dbo.customers
   WHERE dpf_job = '550e8400-e29b-41d4-a716-446655440005';
   -- Aggregation across tables
   SELECT
       dpf_filename,
       COUNT(*) AS row_count,
       MIN(dpf_ts) AS earliest_load,
       MAX(dpf_ts) AS latest_load
   FROM DPF_FABRIC.your_lakehouse.dbo.customers
   GROUP BY dpf_filename;
   

Option B: Via Synapse Serverless SQL

Alternatively, create a linked server pointing to a Synapse Serverless SQL pool that has access to your tables.

-- Create linked server to Synapse Serverless SQL
EXEC sp_addlinkedserver
    @server = N'DPF_SYNAPSE',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'your-synapse-workspace-ondemand.sql.azuresynapse.net',
    @catalog = N'dpf_external';

-- Query your tables through Synapse
SELECT
    customer_id,
    name,
    email,
    dpf_filename,
    dpf_job
FROM DPF_SYNAPSE.dpf_external.dpf.customers
WHERE dpf_ts >= '2026-06-01';

Cross-Database Joins

A key advantage of the linked server approach is that you can join your Iceberg tables with existing SQL Server data in a single query:

-- Join Iceberg data with local SQL Server tables
SELECT
    c.customer_id,
    c.name,
    c.email,
    o.order_id,
    o.order_total
FROM DPF_FABRIC.your_lakehouse.dbo.customers c
INNER JOIN dbo.orders o
    ON c.customer_id = o.customer_id
WHERE c.dpf_job = '550e8400-e29b-41d4-a716-446655440005'
ORDER BY o.order_total DESC;

Performance Tip

When joining remote Iceberg tables with local SQL Server tables, filter the remote side as aggressively as possible. Predicates on dpf_job, dpf_filename, and partition columns are pushed down to the Iceberg layer, minimizing data transfer across the link.

Querying with Azure Synapse Analytics

Azure Synapse Serverless SQL pool can query your Iceberg tables via Fabric Lakehouse shortcuts. This provides pay-per-query T-SQL access without provisioning compute resources.

Read-Only Access

Synapse Serverless SQL supports read-only operations on external Iceberg tables. Write operations must be performed through the DPF API or an open engine.

Setup: Via Fabric Lakehouse Shortcuts

Once you've configured Fabric shortcuts, Synapse can query your tables through the Lakehouse's SQL Analytics Endpoint. Fabric handles catalog authentication and metadata resolution automatically.

1. Connect Synapse to the Fabric workspace In Synapse Studio, add a linked service pointing to your Fabric SQL Analytics Endpoint, or query it directly using a Serverless SQL pool with cross-workspace access.
2. Query your tables

   -- Query through Fabric Lakehouse (three-part name)
   SELECT *
   FROM [your_lakehouse].[dbo].[customers]
   LIMIT 100;
   

Supported Operations

Operation	Supported	Notes
SELECT	✅	Full T-SQL with joins, aggregations, CTEs
Predicate pushdown	✅	Filters pushed to Iceberg for partition pruning
Cross-database joins	✅	Join your Iceberg tables with other Synapse databases
Views / Stored Procedures	✅	Wrap shortcut tables in views for abstraction
INSERT / UPDATE / DELETE	❌	Not supported on shortcut Iceberg tables

Examples

Aggregation by source file and job:

SELECT
    dpf_job,
    dpf_filename,
    COUNT(*) AS row_count,
    MIN(dpf_ts) AS load_start,
    MAX(dpf_ts) AS load_end
FROM [your_lakehouse].[dbo].[customers]
GROUP BY dpf_job, dpf_filename
ORDER BY load_start DESC;

Data lineage query — trace rows back to source:

SELECT
    customer_id,
    name,
    dpf_filename AS source_file,
    dpf_line AS source_row_number,
    dpf_job AS load_job_id,
    dpf_ts AS loaded_at
FROM [your_lakehouse].[dbo].[customers]
WHERE customer_id = 'CUST-12345'
ORDER BY dpf_ts DESC;

Cross-source join — Iceberg data with Azure SQL tables:

-- Join your shortcut table with a local Synapse table
SELECT
    c.customer_id,
    c.name,
    c.email,
    s.subscription_tier,
    s.renewal_date
FROM [your_lakehouse].[dbo].[customers] c
INNER JOIN dbo.subscriptions s
    ON c.customer_id = s.customer_id
WHERE c.dpf_job = '550e8400-e29b-41d4-a716-446655440005';

SQL Server vs. Synapse — When to Use Which

Consideration	SQL Server (Linked Server)	Azure Synapse Serverless
Setup	Linked server to Fabric endpoint	Fabric shortcut via Lakehouse
Best for	Joining with existing SQL Server data, operational queries	Ad-hoc analytics, BI tools, large-scale aggregations
Query pricing	No additional cost (uses existing SQL Server license)	~$5/TB processed (pay-per-query)
Performance	Depends on linked server throughput	Distributed engine, scales with data volume
Materialization	SELECT INTO local tables	SELECT INTO local tables
Write support	❌ (read-only via link)	❌ (read-only via shortcuts)
BI integration	Direct via SSMS, SSRS	Power BI, Tableau, Looker native connectors

Recommendation

If your analytics team lives in the Azure ecosystem, use Fabric shortcuts for the simplest setup and the broadest tool compatibility (Power BI, Synapse, SQL Server).

Need Write Access?

For INSERT, UPDATE, and DELETE operations, connect through the PostgreSQL Gateway (standard JDBC/ODBC, read + write), or connect directly to the DPF Iceberg REST Catalog using an open engine like Apache Spark, Trino, or PyIceberg.

Query with GCP

Setting Up GCP Integration

BigQuery reaches your tables through BigQuery Omni, Google's cross-cloud connector for data sitting in AWS. Because your table data lives in DPF's S3 storage, this requires a one-time cross-account IAM authorization, similar to the Glue integration above.

Prerequisites

• An active DPF workspace with at least one completed data load job
• Your DPF REST Catalog endpoint URL: https://api.dpf-it.com/iceberg/v1
• A BigQuery Omni connection to AWS (aws-us-east-1 region) and an AWS account to authorize for cross-account access

Required: Complete Cross-Account Setup with DPF

BigQuery Omni reads your data files directly from Amazon S3 using an AWS IAM role you authorize. After creating the IAM role in Step 1 below, contact us with your AWS account ID, workspace ID, and the ARN of the IAM role. Our team will authorize that role for your specific workspace — a one-time step per workspace.

Step-by-Step Setup

1. Create an AWS IAM role for BigQuery Omni Follow Google's BigQuery Omni setup to create the role and trust policy, then create the connection in BigQuery:

   bq mk --connection --connection_type=AWS \
     --properties='{"accessRole":{"iamRoleId":"arn:aws:iam::ACCOUNT:role/BigQueryOmniAccessRole"}}' \
     --location=aws-us-east-1 \
     dpf-omni-connection
   

2. Create a BigLake external table pointing at the Iceberg metadata BigQuery Omni reads Iceberg tables from a metadata pointer rather than talking to the REST catalog live, so you supply the current metadata_location for the table (visible in the DPF workspace UI or via the REST catalog's LoadTable response):

   bq mk --table \
     --external_table_definition='@iceberg_def.json' \
     my_dataset.customers
   # iceberg_def.json
   {
     "icebergOptions": {
       "metadataLocation": "s3://dpf-storage/<workspace_namespace>/customers/metadata/00003-xxxx.metadata.json"
     },
     "connectionId": "projects/YOUR_PROJECT/locations/aws-us-east-1/connections/dpf-omni-connection"
   }
   

Manual Metadata Refresh

Unlike the AWS/Azure federation paths, a BigQuery Omni Iceberg external table pins a specific metadata_location snapshot. After new data is loaded into the table via DPF, re-run the bq mk --table command (or a scheduled query) with the latest metadata path to pick up new data.

Querying from GCP

Once the BigLake external table is configured, query it like any other BigQuery table:

SELECT
    customer_id,
    name,
    email,
    dpf_filename,
    dpf_job,
    dpf_ts
FROM \`my_project.my_dataset.customers\`
LIMIT 100;

Need Write Access from BigQuery?

BigQuery Omni external tables are read-only. For writes from a GCP-hosted workload, connect through the PostgreSQL Gateway, or connect directly to the DPF Iceberg REST Catalog using an open engine like Apache Spark, Trino, or PyIceberg.

Query with Databricks

Setting Up Databricks Unity Catalog Integration

Unity Catalog supports Iceberg REST catalog federation: a foreign catalog object that points at an external Iceberg REST endpoint. Once configured, your DPF workspace appears inside Unity Catalog as a foreign catalog, queryable from Databricks SQL warehouses and notebooks alike — on any cloud your Databricks workspace runs on.

Prerequisites

• An active DPF workspace with at least one completed data load job
• Your DPF REST Catalog endpoint URL: https://api.dpf-it.com/iceberg/v1
• OAuth2 client credentials for catalog authentication (see Step 1 below to generate)
• A Databricks workspace with Unity Catalog enabled
• CREATE CONNECTION and CREATE CATALOG privileges on the metastore

Step-by-Step Setup

1. Generate an API credential from the DPF API This is the same account-level credential used for the other integrations on this page (Settings → API Credentials in the DPF UI, or the API call below).

   curl -X POST https://api.dpf-it.com/oauth/clients \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "client_name": "Databricks Unity Catalog"
     }'
   

2. Create the connection In a Databricks SQL editor or notebook, create a connection describing how to reach the DPF REST catalog:

   CREATE CONNECTION dpf_rest_connection TYPE ICEBERG_REST
   OPTIONS (
     uri 'https://api.dpf-it.com/iceberg/v1',
     token_refresh_url 'https://api.dpf-it.com/oauth/token',
     client_id 'YOUR_CLIENT_ID',
     client_secret 'YOUR_CLIENT_SECRET'
   );
   

3. Create the foreign catalog

   CREATE FOREIGN CATALOG dpf_data
   USING CONNECTION dpf_rest_connection
   OPTIONS (catalog 'dpf');
   

4. Verify table discovery Each DPF workspace namespace appears as a schema under the foreign catalog:

   SHOW SCHEMAS IN dpf_data;
   SHOW TABLES IN dpf_data.<workspace_namespace>;
   

Vended Credentials — No Cross-Account Setup

Unity Catalog obtains short-lived S3 credentials directly from the DPF REST Catalog at query time, the same way a direct Spark or Trino connection does. Unlike the AWS Glue integration, no cross-account IAM authorization is needed, regardless of which cloud your Databricks workspace runs on.

Read-Only via Federation

Like the other federation paths in this guide, querying through the foreign catalog currently supports read-only operations (SELECT, time travel). To write, use the PostgreSQL Gateway or connect directly with PySpark's Iceberg REST catalog support.

Querying with Databricks

Once the foreign catalog is set up, query your tables the same way from a Databricks SQL warehouse or a notebook — both share the same Unity Catalog metadata.

Examples

Basic query with DPF audit fields:

SELECT
    customer_id,
    name,
    email,
    dpf_filename,
    dpf_job,
    dpf_ts
FROM dpf_data.my_workspace.customers
LIMIT 100;

Time-travel query:

SELECT *
FROM dpf_data.my_workspace.customers
TIMESTAMP AS OF '2026-06-10 12:00:00';

Join a DPF (foreign) table with a native Delta table:

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.order_total
FROM dpf_data.my_workspace.customers c
JOIN main.sales.orders o
  ON o.customer_id = c.customer_id
WHERE o.order_total > 1000;

Supported Operations

Operation	Supported	Notes
SELECT	✅	Full SQL with joins, aggregations, window functions
Time Travel	✅	Query historical snapshots by timestamp
Cross-catalog joins	✅	Join DPF tables with Delta or other Unity Catalog tables
INSERT / UPDATE / DELETE	❌	Not supported on federated foreign catalogs
MERGE INTO	❌	Not supported on federated foreign catalogs

Need Write Access?

For INSERT, UPDATE, and DELETE, connect through the PostgreSQL Gateway (standard JDBC/ODBC, read + write), or connect directly to the DPF Iceberg REST Catalog from a Databricks notebook using PySpark's Iceberg REST catalog support.

Query with Snowflake

Setting Up the Snowflake Catalog Integration

Snowflake reads external Iceberg tables through a catalog integration of type ICEBERG_REST, paired with an external volume that describes how to reach the underlying storage. Once configured, your DPF workspace's tables can be exposed as Snowflake Iceberg tables and queried with standard SQL.

Prerequisites

• An active DPF workspace with at least one completed data load job
• Your DPF REST Catalog endpoint URL: https://api.dpf-it.com/iceberg/v1
• OAuth2 client credentials for catalog authentication (see Step 1 below to generate)
• A Snowflake account on Enterprise edition or higher (required for Iceberg Tables)
• ACCOUNTADMIN, or a role with CREATE INTEGRATION and CREATE EXTERNAL VOLUME privileges

Step-by-Step Setup

1. Generate an API credential from the DPF API Account-level — Settings → API Credentials in the DPF UI, or the API call below.

   curl -X POST https://api.dpf-it.com/oauth/clients \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "client_name": "Snowflake Catalog Integration"
     }'
   

2. Create the catalog integration

   CREATE CATALOG INTEGRATION dpf_catalog_integration
     CATALOG_SOURCE = ICEBERG_REST
     TABLE_FORMAT = ICEBERG
     REST_CONFIG = (
       CATALOG_URI = 'https://api.dpf-it.com/iceberg/v1'
       CATALOG_NAME = 'dpf'
     )
     REST_AUTHENTICATION = (
       TYPE = OAUTH
       OAUTH_TOKEN_URI = 'https://api.dpf-it.com/oauth/token'
       OAUTH_CLIENT_ID = 'YOUR_CLIENT_ID'
       OAUTH_CLIENT_SECRET = 'YOUR_CLIENT_SECRET'
     )
     ENABLED = TRUE;
   

3. Create an external volume for vended credentials Omit STORAGE_AWS_ROLE_ARN so Snowflake relies on the catalog integration to vend short-lived storage credentials per query, rather than a static IAM role — no cross-account authorization is required.

   CREATE EXTERNAL VOLUME dpf_ext_volume
     STORAGE_LOCATIONS =
     (
       (
         NAME = 'dpf-vended'
         STORAGE_PROVIDER = 'S3'
         STORAGE_BASE_URL = 's3://dpf-storage/'
       )
     );
   

4. Create a catalog-linked database for your workspace This auto-populates a Snowflake database from your DPF workspace namespace — no need to declare each table individually.

   CREATE DATABASE dpf_data
     LINKED_CATALOG = (
       CATALOG = 'dpf_catalog_integration',
       CATALOG_NAMESPACE = '<workspace_namespace>'
     )
     EXTERNAL_VOLUME = 'dpf_ext_volume';
   

5. Verify table discovery

   SHOW ICEBERG TABLES IN DATABASE dpf_data;
   

Namespace Mapping

DPF uses your workspaceId as the Iceberg namespace. Set CATALOG_NAMESPACE to your workspace's namespace to scope the linked database to that workspace's tables.

Read-Only via Federation

Like the other federation paths in this guide, Iceberg tables backed by a REST catalog integration are currently read-only in Snowflake. To write, use the PostgreSQL Gateway or connect directly with an open engine.

Querying with Snowflake

Query the catalog-linked database like any other Snowflake database — your workspace namespace appears as a schema, and each DPF table appears as a Snowflake Iceberg table.

Examples

Basic query with DPF audit fields:

SELECT
    customer_id,
    name,
    email,
    dpf_filename,
    dpf_job,
    dpf_ts
FROM dpf_data.my_workspace.customers
LIMIT 100;

Aggregation by load job:

SELECT
    dpf_job,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT dpf_filename) AS file_count,
    MIN(dpf_ts) AS job_start,
    MAX(dpf_ts) AS job_end
FROM dpf_data.my_workspace.customers
GROUP BY dpf_job
ORDER BY job_start DESC;

Join a DPF Iceberg table with a native Snowflake table:

SELECT
    c.customer_id,
    c.name,
    s.subscription_tier,
    s.renewal_date
FROM dpf_data.my_workspace.customers c
JOIN app_db.public.subscriptions s
  ON s.customer_id = c.customer_id;

Supported Operations

Operation	Supported	Notes
SELECT	✅	Full SQL with joins, aggregations, window functions
Cross-database joins	✅	Join DPF Iceberg tables with native Snowflake tables
Snowpark	✅	Read DPF tables into Snowpark DataFrames like any other table
INSERT / UPDATE / DELETE	❌	Not supported on REST-catalog-backed Iceberg tables

Need Write Access?

For INSERT, UPDATE, and DELETE, connect through the PostgreSQL Gateway (standard JDBC/ODBC, read + write), or connect directly to the DPF Iceberg REST Catalog using an open engine like Apache Spark, Trino, or PyIceberg.

AI Agents (MCP)

DPF publishes a Model Context Protocol (MCP) server so AI agents can discover the DPF API, onboard new users, and run end-to-end data integration workflows — register an account, create a workspace, load a file, build a data spec, and query the results, all through natural-language tool calls. There are two ways to connect it, and which one to use depends entirely on your client.

One Server, No Install

DPF runs a single remote MCP server. VS Code, Cursor, Claude Desktop/Claude.ai, Kiro IDE, and ChatGPT Developer Mode all support real OAuth 2.1 login — point them at the URL below and a browser login popup handles the rest. No npm install, no local Node.js. Clients that don't support that browser login flow (Codex CLI, Kiro CLI) use a DPF API credential instead.

Remote MCP (VS Code, Cursor, Claude, ChatGPT)

Point your client at https://api.dpf-it.com/mcp with no credentials configured. On first connection, the client will open a browser to a real DPF login page — your password is typed there, never inside a chat or tool call — and, if you don't already have an account, the same page's sign-up flow handles registration and email verification before redirecting back. From then on, the client stores its own access + refresh token and reconnects silently; you won't be asked to log in again unless you revoke access or a refresh token itself expires.

VS Code (.vscode/mcp.json, workspace or user-level):

{
  "mcpServers": {
    "dpf": {
      "url": "https://api.dpf-it.com/mcp"
    }
  }
}

Cursor — same shape, in Cursor's MCP settings or its own mcp.json. Click "Add new global MCP server" and paste the URL, or use an "Add to Cursor" style deep link if you've set one up.

Claude Desktop / Claude.ai — Settings → Connectors → Add custom connector → paste https://api.dpf-it.com/mcp as the remote MCP server URL. Claude handles Dynamic Client Registration automatically.

ChatGPT (Developer Mode) — Settings → Apps & Connectors → Advanced → enable Developer Mode, then add a custom connector with the same URL and choose OAuth as the auth type.

No Client-Side Password Storage

The remote server never accepts a password as a tool argument. If a client ever prompts you to type your DPF password directly into a chat message rather than a browser popup, that's not this server working as intended — stop and check the connector URL.

Clients Without OAuth Support (Codex CLI, Kiro CLI)

Codex CLI and Kiro CLI don't implement the MCP OAuth browser-login flow the clients above use, so pointing them at https://api.dpf-it.com/mcp with no credentials configured won't work — there's no browser for them to pop open. Both, however, let you attach a static Authorization header to a remote MCP server's config. DPF's API credentials (the same account-level ones used for catalog federation elsewhere in this guide, created via Settings → API Credentials or POST /oauth/clients) are built for exactly that: a client_id / client_secret pair you exchange for a bearer token yourself, instead of a client doing it interactively on your behalf.

How This Differs From the OAuth Clients Above

VS Code/Cursor/Claude/ChatGPT log in once and then refresh silently forever — the client manages a refresh token behind the scenes and you never think about it again. The client_credentials path here has no refresh token: each exchange mints an access token that's valid for 24 hours, and neither Codex CLI nor Kiro CLI knows how to renew it for you. Budget for re-running the exchange (Step 2 below) roughly daily — the snippet at the end of this section automates that.

Step-by-Step Setup

1. Create an API credential Settings → API Credentials in the DPF UI, or via the API directly:

   curl -X POST https://api.dpf-it.com/oauth/clients \
     -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
       "client_name": "Codex CLI"
     }'
   # Response — save the clientSecret now, it's shown once:
   # { "success": true, "data": { "clientId": "...", "clientSecret": "...", "clientName": "Codex CLI" } }
   

2. Exchange it for an access token This is the step you'll repeat whenever the token expires:

   export DPF_MCP_TOKEN=$(curl -s -X POST https://api.dpf-it.com/oauth/token \
     -H "Content-Type: application/x-www-form-urlencoded" \
     -d "grant_type=client_credentials&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET" \
     | python3 -c "import sys,json; print(json.load(sys.stdin)['access_token'])")
   

3. Point your client at the token via a static header Codex CLI (~/.codex/config.toml) — reads the token from the environment variable set in Step 2, so it never touches the config file itself:

   [mcp_servers.dpf]
   url = "https://api.dpf-it.com/mcp"
   bearer_token_env_var = "DPF_MCP_TOKEN"
   

   Kiro CLI (agent config JSON, e.g. .kiro/agents/*.json) — same idea, via ${VAR} substitution in the header value:

   {
     "mcpServers": {
       "dpf": {
         "url": "https://api.dpf-it.com/mcp",
         "headers": {
           "Authorization": "Bearer ${DPF_MCP_TOKEN}"
         }
       }
     }
   }
   

Re-running Step 2 before each session is enough for occasional use. For daily use, drop it in your shell profile so a new terminal always starts with a live token:

# ~/.zshrc or ~/.bashrc
export DPF_CLIENT_ID="..."
export DPF_CLIENT_SECRET="..."
export DPF_MCP_TOKEN=$(curl -s -X POST https://api.dpf-it.com/oauth/token \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "grant_type=client_credentials&client_id=${DPF_CLIENT_ID}&client_secret=${DPF_CLIENT_SECRET}" \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['access_token'])")

A session already running with an expired token will start getting 401 s from DPF — restart it after refreshing rather than expecting it to recover on its own.

Available Tools

The remote MCP server exposes the following tools once connected:

Tool	What it does
list_my_workspaces / create_workspace	List or create workspaces for the authenticated account
list_data / get_status / delete_data_spec	Inspect and manage data specs and jobs
submit_query	Run SQL against a workspace's Iceberg tables
onboard_data_source → finish_data_source_onboarding	Create a data spec, upload a sample file, and run AI schema inference
update_data_spec → finish_data_spec_update	Change an existing spec's configuration, optionally replacing its sample file
run_data_job → finish_data_job	Process new files through an already-configured spec
manage_connection / manage_trigger / setup_scheduled_pull	Set up and manage scheduled SFTP ingestion
call_dpf_api	Escape hatch for any DPF API action without a dedicated tool

File Uploads Are Two Steps on Remote

onboard_data_source / update_data_spec / run_data_job return a presigned upload URL rather than uploading a file themselves — a remote server has no access to your local disk. If your client has its own file/shell access (e.g. Cursor, VS Code), it uploads the file directly; otherwise, attach the file in the chat when prompted, then call the matching finish_* tool to continue.