# DPF — Full Documentation

> This file is the expanded companion to [llms.txt](https://www.dpf-it.com/llms.txt): the complete text of DPF's Integration Guide and Examples pages, concatenated as plain markdown for LLMs and agents that fetch one document instead of crawling multiple pages. It mirrors the live pages; the API reference itself is a separate machine-readable file at [dpf_openapi.yaml](https://www.dpf-it.com/dpf_openapi.yaml).

---

Source: https://www.dpf-it.com/integration-guide.html

# Integration Guide

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

## Overview

[Note]

**On This Page:**

- **Connections:** [AWS S3 (Ingestion)](https://www.dpf-it.com/integration-guide.html#connect-aws-s3)·[SFTP (Ingestion)](https://www.dpf-it.com/integration-guide.html#connect-sftp)
- **Direct SQL Access:** [PostgreSQL Gateway](https://www.dpf-it.com/integration-guide.html#pg-gateway)·[DBeaver & BI Tools](https://www.dpf-it.com/integration-guide.html#gateway-bi-tools)·[postgres_fdw](https://www.dpf-it.com/integration-guide.html#gateway-postgres-fdw)
- **AWS:** [Glue Data Catalog Setup](https://www.dpf-it.com/integration-guide.html#gdc-integration)·[Athena](https://www.dpf-it.com/integration-guide.html#querying-athena)·[Redshift](https://www.dpf-it.com/integration-guide.html#querying-redshift)
- **Azure:** [Fabric Shortcuts Setup](https://www.dpf-it.com/integration-guide.html#fabric-integration)·[SQL Server](https://www.dpf-it.com/integration-guide.html#querying-sql-server)·[Synapse](https://www.dpf-it.com/integration-guide.html#querying-synapse)
- **GCP:** [Setup](https://www.dpf-it.com/integration-guide.html#gcp-integration)·[Querying](https://www.dpf-it.com/integration-guide.html#querying-gcp)
- **Databricks:** [Unity Catalog Setup](https://www.dpf-it.com/integration-guide.html#databricks-integration)·[Querying](https://www.dpf-it.com/integration-guide.html#querying-databricks)
- **Snowflake:** [Catalog Integration Setup](https://www.dpf-it.com/integration-guide.html#snowflake-integration)·[Querying](https://www.dpf-it.com/integration-guide.html#querying-snowflake)
- **AI Agents:** [Remote MCP](https://www.dpf-it.com/integration-guide.html#mcp-remote)·[Codex CLI / Kiro CLI](https://www.dpf-it.com/integration-guide.html#mcp-local)·[Available Tools](https://www.dpf-it.com/integration-guide.html#mcp-tools)

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](https://www.dpf-it.com/integration-guide.html#mcp).

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](https://www.dpf-it.com/integration-guide.html#pg-gateway) below.

[Warning]

**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](https://www.dpf-it.com/integration-guide.html#pg-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 Gateway generic PostgreSQL JDBC/ODBC/libpq driver Read + Write

Spark · Trino · PyIceberg open-source Iceberg REST catalog client, direct Read + Write

Athena AWS console or native Athena JDBC/ODBC driver

Redshift native Redshift JDBC/ODBC driver or Query Editor

↓

↓

Glue Data Catalog
Read-only

SQL Server 2022 / Azure SQL MI native MSOLEDBSQL/ODBC driver, linked server

Synapse Serverless SQL native T-SQL engine

↓

↓

Fabric OneLake Shortcut
Read-only

BigQuery native BigQuery console/client

↓

BigQuery Omni
Read-only

Databricks SQL / Notebook native Databricks SQL client or Spark session

↓

Unity Catalog Foreign Catalog
Read-only

Snowsight / SnowSQL native 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
  }'
```

[Note]

**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"
  }'
```

[Note]

**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.

[Note]

**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.

[Warning]

**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.

[Warning]

**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;
```

[Tip]

**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';
```

[Warning]

**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.

[Note]

**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.

[Note]

**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

[Note]

**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.

[Warning]

**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](https://www.dpf-it.com/integration-guide.html#) 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
```

[Note]

**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.

[Note]

**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.

[Warning]

**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.

[Warning]

**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) |

[Tip]

**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.

[Note]

**Need Write Access?:**
For INSERT, UPDATE, and DELETE operations, connect through the
[PostgreSQL Gateway](https://www.dpf-it.com/integration-guide.html#pg-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.

[Note]

**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.

[Tip]

**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.

[Warning]

**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;
```

[Note]

**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.

[Warning]

**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 |

[Tip]

**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).

[Note]

**Need Write Access?:**
For INSERT, UPDATE, and DELETE operations, connect through the
[PostgreSQL Gateway](https://www.dpf-it.com/integration-guide.html#pg-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

[Warning]

**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](https://www.dpf-it.com/integration-guide.html#) 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"
}
```

[Warning]

**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;
```

[Note]

**Need Write Access from BigQuery?:**
BigQuery Omni external tables are read-only. For writes from a GCP-hosted workload,
connect through the [PostgreSQL Gateway](https://www.dpf-it.com/integration-guide.html#pg-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>;
```

[Tip]

**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.

[Warning]

**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](https://www.dpf-it.com/integration-guide.html#pg-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 |

[Note]

**Need Write Access?:**
For INSERT, UPDATE, and DELETE, connect through the [PostgreSQL
Gateway](https://www.dpf-it.com/integration-guide.html#pg-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;
```

[Note]

**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.

[Warning]

**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](https://www.dpf-it.com/integration-guide.html#pg-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 |

[Note]

**Need Write Access?:**
For INSERT, UPDATE, and DELETE, connect through the [PostgreSQL
Gateway](https://www.dpf-it.com/integration-guide.html#pg-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](https://modelcontextprotocol.io) (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.

[Note]

**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](https://www.dpf-it.com/integration-guide.html#mcp-local) 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.

[Warning]

**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.

[Warning]

**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}"
      }
    }
  }
}
```

[Note]

**Keeping the Token Fresh:**
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 |

[Note]

**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.

---

Source: https://www.dpf-it.com/examples.html

# Examples

Six concrete objectives, each worked end-to-end as a sequence of DPF API calls or MCP prompts.

## Overview

Each objective below is self-contained but builds on the ones before it — together they walk a
single workspace from account creation through a scheduled ingestion pipeline and into
aggregated reporting. Run them in order the first time through — click any step to jump to it:

[1. Register & verify OTPget a JWT and create a workspace](https://www.dpf-it.com/examples.html#obj1-signup)
↓
[2. Load a file into a new tableschema inferred by AI](https://www.dpf-it.com/examples.html#obj2-autoinfer)
↓
`` [3. Load & transform into an existing tablefield mapping + business rules via additionalPrompt](https://www.dpf-it.com/examples.html#obj3-transform)
↓
[4. SFTP connection + daily triggerre-runs the Objective 2 spec's existing transformation code automatically](https://www.dpf-it.com/examples.html#obj4-sftp-trigger)
↓
[5. Query the fully loaded dataSQL over your Iceberg tables](https://www.dpf-it.com/examples.html#obj5-query)
↓
`` [6. Aggregate into a summary tabletable-source spec windowed automatically, fired by a spec_success trigger on Objective 4's load](https://www.dpf-it.com/examples.html#obj6-aggregate)

### Conventions Used Below

- Base URL is `https://api.dpf-it.com`. Every request/response is JSON.
- Success responses are shaped `{ "success": true, "data": {...} }`; errors are
`{ "error": { "code": "...", "message": "..." } }`.
- `YOUR_DPF_JWT_TOKEN` is the `token` from Objective 1's login call. Pass it
as `Authorization: Bearer YOUR_DPF_JWT_TOKEN` on every authenticated request below.
- `YOUR_WORKSPACE_ID` is the `workspaceId` created in Objective 1 —
reused by every other objective on this page.
- Most endpoints (`/data-specs`, `/connections`, `/job-triggers`,
`/workspaces`) are **multi-action**: one URL, with an `action`
field in the JSON body selecting the operation.
- Full request/response schemas are in the [API Docs](https://www.dpf-it.com/api-viewer.html); this page is
the "how do these calls chain together" companion to that reference.

## 1 Register a User & Verify OTP

Create an account, verify it with the one-time code emailed on registration, log in for a JWT,
and create the workspace that every other objective on this page will load data into and query.

API

1. **Register** `termsAccepted` must be `true` or registration is rejected. This sends a
6-digit OTP to the given email.

```
curl -X POST https://api.dpf-it.com/auth/register \
  -H "Content-Type: application/json" \
  -d '{
    "email": "jane@example.com",
    "password": "SecurePassword123",
    "firstName": "Jane",
    "lastName": "Doe",
    "termsAccepted": true
  }'

# 201 Created
# { "success": true, "data": { "userId": "...", "email": "jane@example.com", ... } }
```

2. **Verify the OTP**
Use the 6-digit code from the verification email. If it expired or never arrived, call
`POST /auth/resend-otp` with just `{"email": "..."}`.

```
curl -X POST https://api.dpf-it.com/auth/verify-otp \
  -H "Content-Type: application/json" \
  -d '{
    "email": "jane@example.com",
    "otp": "123456"
  }'

# 200 OK
# { "success": true, "data": { "message": "Email verified successfully" } }
```

3. **Log in**
Returns a 24h-lived access `token` plus a ~90-day `refreshToken`
(exchange it at `POST /auth/refresh` once the access token expires). Save the
`token` — every call below sends it as `Authorization: Bearer ...`.

```
curl -X POST https://api.dpf-it.com/auth/login \
  -H "Content-Type: application/json" \
  -d '{
    "email": "jane@example.com",
    "password": "SecurePassword123"
  }'

# 200 OK
# {
#   "success": true,
#   "data": {
#     "userId": "550e8400-e29b-41d4-a716-446655440000",
#     "token": "eyJhbGciOiJIUzI1NiIs...",
#     "refreshToken": "q8c-BbPEpIF7ai-FrYUZ_a4acEk7finhMGHQ5vUvBvg",
#     ...
#   }
# }
```

4. **Create a workspace**
Creating a workspace also provisions its Iceberg catalog namespace — you'll need that
`workspaceId` for every remaining objective on this page.

```
curl -X POST https://api.dpf-it.com/workspaces \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "create",
    "name": "Sales Analytics",
    "description": "Workspace used throughout the DPF examples"
  }'

# 201 Created
# { "success": true, "data": { "workspaceId": "550e8400-e29b-41d4-a716-446655440099", ... } }
```

MCP

1. **Add DPF's MCP server to Claude**
Settings → Connectors → Add custom connector → paste `https://api.dpf-it.com/mcp`
as the remote server URL. Claude registers itself as a client automatically — there's no
separate credential to create first.

2. **Log in when Claude redirects you**
On first use, Claude opens a browser to DPF's own login page. No account yet? The same
page's sign-up form handles registration and OTP verification before sending you back to
Claude — your password is typed there, never into a chat message.

3. **Nothing to copy or save**
Once you're redirected back, Claude stores its own access and refresh token and attaches it
to every tool call automatically. There's no JWT to paste anywhere, and you won't be asked
to log in again unless you revoke access or the refresh token itself expires.

4. **Create a workspace**
Ask, in plain English: *"create a workspace called Sales Analytics."* Claude calls
`create_workspace` and every other objective on this page reuses it automatically.

[Note]

**The Iceberg Namespace:**
Query and catalog access (Objectives 5–6) address this workspace by **namespace**,
which is just the trailing 12 characters of `workspaceId` — for
`550e8400-e29b-41d4-a716-446655440099` that's `446655440099`. No lookup
call needed; it's a pure string operation on the ID you already have.

## 2 Load a File into a New Table, Schema Inferred

Give DPF a sample file and set `targetOption: "auto-infer"` on the spec: AI infers
the target schema, generates parsing/transform code, and loads the sample — creating a
brand-new Iceberg table in the process. No pre-existing table or schema file needed.

API

1. **Create the spec**
Returns a presigned upload URL for the sample file (valid 1 hour, 100 KB max — a
representative sample is enough, DPF re-runs this same logic against full files later).

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "create-spec",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "Customer Signups",
    "sampleFileName": "customers.csv",
    "targetOption": "auto-infer",
    "computeSize": "small",
    "description": "Load new customer signups into a fresh Iceberg table"
  }'

# 201 Created
# {
#   "success": true,
#   "data": {
#     "specId": "550e8400-e29b-41d4-a716-446655440003",
#     "signedUrls": { "customers.csv": "https://dpf-specs.s3.us-east-1.amazonaws.com/...?X-Amz-Signature=..." },
#     "expiresIn": 3600,
#     ...
#   }
# }
```

2. **Upload the sample file** `customers.csv`:

```
id,name,email,signup_date
1,Acme Corp,ops@acme.test,2026-07-01
2,Beta Inc,hello@beta.test,2026-07-01
3,Globex LLC,contact@globex.test,2026-07-02
```

PUT it straight to the signed URL from step 1 (it's a standard S3 presigned PUT, not a DPF
endpoint — no `Authorization` header):

```
curl -X PUT "SIGNED_URL_FROM_STEP_1" \
  --upload-file customers.csv
```

3. **Start analysis**
Kicks off schema inference + code generation, then (by default) loads the sample and starts
the Glue data-load job. Returns immediately — poll `get-status` for progress.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "start-analysis",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "Customer Signups"
  }'

# 202 Accepted
# { "success": true, "data": { "specId": "...", "status": "processing", ... } }
```

4. **Poll the spec's status**
Poll with `specId` until `status` is `ready` (or
`failed`). Once `ready`, `lastJobId` points at the data-load
run itself.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "get-status",
    "specId": "550e8400-e29b-41d4-a716-446655440003"
  }'

# 200 OK
# { "success": true, "data": { "status": "ready", "lastJobId": "550e8400-e29b-41d4-a716-446655440005", ... } }
```

5. **Poll the load itself**
Poll the same endpoint with `jobId` for the Glue run's own progress and, once
complete, its metrics.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "get-status",
    "jobId": "550e8400-e29b-41d4-a716-446655440005"
  }'

# 200 OK
# {
#   "success": true,
#   "data": {
#     "status": "completed",
#     "metrics": { "recordsRead": 3, "recordsWritten": 3, "filesProcessed": 1 },
#     ...
#   }
# }
```

MCP

Describe the outcome in plain English. Claude picks the tools, uploads the sample file, and
polls the job to completion — below is the real tool-call sequence from one such session.

Prompt Using DPF, load new customer signups into a fresh Iceberg table, use the customers.csv file as an example.

Thought for 6s

Dpf-it [list_my_workspaces]

workspace confirmed 0.2s

Located `customer.csv` in the project folder (150-row sample) and
checked existing specs — `WebLogDaily` and `WebLogs` are
unrelated web-log pipelines, nothing for customer data yet.
Thought for 7s

Dpf-it [list_data] resource: "specs"

2 unrelated specs found 2.6s

Thought for 2s

Dpf-it [onboard_data_source] specName: "CustomerSignups", sourceType: "file", sampleFileName: "customer.csv", targetOption: "auto-infer"

spec created, presigned upload URL returned 56s

Uploaded `customer.csv` to that URL — a plain S3 PUT, not a DPF call — then continued.

Thought for 2s

Dpf-it [finish_data_source_onboarding] specId: "8a4e4fad-...", specName: "CustomerSignups"

processing — schema inference under way

Dpf-it [get_status] specId: "8a4e4fad-..."

processing, 65% — "Generating schema transformation code" 5s

⏱ waited 15s

Dpf-it [get_status] specId: "8a4e4fad-..."

ready — analysis complete, load job started automatically 0.3s

The load job kicked off on its own — polled that next.

Dpf-it [get_status] jobId: "dd14d3ea-..."

processing, 85% — "Starting data processing environment" 0.4s

⏱ waited 15s

Dpf-it [get_status] jobId: "dd14d3ea-..."

complete — data transformation completed successfully 0.4s

Verified the result with SQL, directly over MCP:
Thought for 1s

Dpf-it [submit_query] sql: "SELECT COUNT(*) AS row_count FROM customer"

150 rows 1.1s

Dpf-it [submit_query] sql: "SELECT * FROM customer LIMIT 5"

sample rows returned 0.9s

**Done.** One prompt → spec created, sample uploaded, schema inferred, and
150 rows landed in a brand-new `customer` Iceberg table, confirmed by query.

[Tip]

**What You Now Have:**
A new Iceberg table (named by the AI from the sample — `customers` in this example)
with 4 audit columns appended automatically: `dpf_line`, `dpf_filename`,
`dpf_job`, `dpf_ts`. Confirm the table name with `SHOW TABLES`
in [Objective 5](https://www.dpf-it.com/examples.html#obj5-query) if you're not sure what it inferred.

## 3 Load & Transform into an Existing Table

Same `/data-specs` endpoint as Objective 2, but with
`targetOption: "existing-tables"` instead of auto-infer: the target schema is the
`customers` table created in Objective 2, and the source file has
a different shape entirely — differently-named columns, a different date format, mixed casing.
`additionalPrompt` carries the field-mapping and transformation instructions, and
`merge: true` makes the load an upsert instead of a plain append, so re-running it
with overlapping IDs doesn't create duplicates — that requires the target table to already
have a primary key set (Step 1 below), which is what DPF upserts on.

API

1. **Set a primary key on the target table**
Because we're about to do `merge: true` loads against `customers`,
it needs a primary key for DPF to match rows on — a plain Iceberg table property
(`dpf.primary-keys`), not a `data-specs` call. One-time per table —
skip this if the table already has one (e.g. it was created via auto-infer with
`merge: true`, which sets this automatically; see Objective 2). Without it, the
next step fails with `400 MISSING_PRIMARY_KEY` instead of silently appending
duplicate rows. The easiest way is `ALTER TABLE ... ADD PRIMARY KEY` through the
`/query` endpoint — DuckDB and Iceberg don't support that syntax natively, so DPF
handles it as a special case and sets the property directly:

```
curl -X POST https://api.dpf-it.com/query \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "namespace": "446655440099",
    "query": "ALTER TABLE customers ADD PRIMARY KEY (id)"
  }'

# 200 OK — { "success": true, "data": { "rows": [{ "result": "ALTER TABLE" }], ... } }
```

(`ALTER TABLE customers DROP PRIMARY KEY` removes it the same way.) The same
property can also be set at a lower level directly through the Iceberg REST Catalog (see
[API Docs](https://www.dpf-it.com/api-viewer.html)) if you're already scripting against that endpoint:

```
curl -X POST https://api.dpf-it.com/iceberg/v1/namespaces/446655440099/tables/customers \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "requirements": [],
    "updates": [
      { "action": "set-properties", "updates": { "dpf.primary-keys": "id" } }
    ]
  }'

# 200 OK — table metadata updated
```

2. **Create the spec against the existing table**

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "create-spec",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "Legacy CRM Backfill",
    "sampleFileName": "legacy_crm_export.csv",
    "targetOption": "existing-tables",
    "targetTables": ["customers"],
    "merge": true,
    "computeSize": "small",
    "additionalPrompt": "Source columns are CUST_ID, COMPANY_NAME, EMAIL_ADDR, SIGNUP_MMDDYYYY. Map CUST_ID -> id, COMPANY_NAME -> name, EMAIL_ADDR -> email (lowercased), and parse SIGNUP_MMDDYYYY (MM/DD/YYYY) into signup_date as an ISO 8601 date. Upsert on id.",
    "description": "Backfill customers from the legacy CRM export"
  }'

# 201 Created — same signedUrls / specId shape as Objective 2
```

3. **Upload the sample file** `legacy_crm_export.csv` — note the different column names, casing, and date
format vs. the target `customers` table:

```
CUST_ID,COMPANY_NAME,EMAIL_ADDR,SIGNUP_MMDDYYYY
1,ACME CORP,OPS@ACME.TEST,07/01/2026
4,Initech,billing@initech.test,07/03/2026
```

```
curl -X PUT "SIGNED_URL_FROM_STEP_1" \
  --upload-file legacy_crm_export.csv
```

4. **Start analysis**

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "start-analysis",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "Legacy CRM Backfill"
  }'
```

5. **Poll until ready, then poll the load**
Same two-step polling as Objective 2 (`get-status` by `specId`, then by
the resulting `lastJobId`). Because `id: 1` already exists in
`customers` from Objective 2, that row is **updated in place**
(company casing normalized, email lowercased) — `id: 4` is inserted as new.

MCP

Ask Claude to load the file into the existing table with the field mapping you want. If the
target table doesn't have a primary key set yet for the upsert, Claude sets one via the
Iceberg REST endpoint before continuing — one prompt covers both steps.

Prompt Using the DPF MCP, load legacy_crm_export.csv into the existing customer table. Map CUST_ID -> id, COMPANY_NAME -> name, EMAIL_ADDR -> email (lowercased), and parse SIGNUP_MMDDYYYY into signup_date as an ISO 8601 date. Upsert on id.

Checked existing specs first — the `customer` table from Objective 2
already has `signup_date`/`email` columns, so this is a field
mapping, not a schema change.
Thought for 3s

Dpf-it [list_data] resource: "specs"

customer table schema confirmed 0.5s

Thought for 2s

Dpf-it [onboard_data_source] specName: "LegacyCRMImport", targetOption: "existing-tables", targetTables: ["customer"], merge: true

Blocked: `customer` has no primary key set, so merge/upsert has no key to match rows on — the same requirement as Step 1 in the API column above. The error came back with the exact `call_dpf_api` params to fix it.

Set the primary key through the Iceberg REST endpoint — same operation as the
`curl` example above, via `call_dpf_api` (the MCP escape hatch
for endpoints without a dedicated tool):
Thought for 1s

Dpf-it [call_dpf_api] path: "/iceberg/v1/namespaces/.../tables/customer", params: { updates: [{ action: "set-properties", updates: { "dpf.primary-keys": "id" } }] }

table metadata updated 0.7s

Thought for 2s

Dpf-it [onboard_data_source] specName: "LegacyCRMImport", targetOption: "existing-tables", targetTables: ["customer"], merge: true, additionalPrompt: "Map CUST_ID -> id; COMPANY_NAME -> name; EMAIL_ADDR -> email, lowercased; SIGNUP_MMDDYYYY -> signup_date, parsed MM/DD/YYYY into ISO 8601..."

spec created, presigned upload URL returned 1.1s

Uploaded `legacy_crm_export.csv` to that URL — a plain S3 PUT, not a DPF call — then continued.

Thought for 2s

Dpf-it [finish_data_source_onboarding] specId: "2722d276-...", specName: "LegacyCRMImport"

processing, 45% — "Determining target schema" 11.8s

This run's analysis took longer than the call's polling window — the response is explicit that calling `finish_data_source_onboarding` again would re-attempt starting it (and could trigger a second load job), and to poll `get_status` instead. The agent did exactly that below — no repeat call, no duplicate job.

Dpf-it [get_status] specId: "2722d276-..."

processing, 65% — "Generating schema transformation code" 0.4s

⏱ waited 10s

Dpf-it [get_status] specId: "2722d276-..."

ready — analysis complete (28.6s total), load job started automatically 0.4s

One load job (`b9b085f2-...`) — polled it to completion:

Dpf-it [get_status] jobId: "b9b085f2-..."

processing, 85% — "Starting data processing environment" 0.7s

⏱ waited 15s

Dpf-it [get_status] jobId: "b9b085f2-..."

still processing, 85% 0.8s

⏱ waited 20s

Dpf-it [get_status] jobId: "b9b085f2-..."

complete — data transformation completed successfully ($0.075 charged) 0.4s

Verified the mapping with SQL, directly over MCP:
Thought for 1s

Dpf-it [submit_query] sql: "SELECT id, name, email, signup_date FROM customer WHERE id BETWEEN 1 AND 9 ORDER BY id"

9 rows — e.g. 07132026 → 2026-07-13, emails lowercased 1.3s

**Done.** One primary-key fix, one spec, one upload, one analysis run, one
load job — even though analysis alone ran longer than a single poll window, the agent
never re-triggered it. 9 rows upserted into `customer` with the requested
field mapping, confirmed by query.

[Warning]

**existing-tables vs. auto-infer:** `existing-tables` only determines column *mapping* against a schema that
already exists — it does not create tables. If `customers` didn't already exist,
this call would fail; that's exactly what Objective 2's `auto-infer` approach is for.

## 4 Connect an SFTP Server & Trigger a Daily Load

Automate Objective 2's spec so new files dropped on an SFTP server load themselves every day,
with no manual `create-job`/`start-job` calls. A **connection**
holds how DPF authenticates to the server; a **trigger** pairs that connection with
a spec and a schedule.

API

1. **Create the connection**
DPF generates an RSA-4096 keypair and returns the public half — it never returns the private
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": "test-sftp.dpf-it.com",
    "username": "sftpuser"
  }'

# 201 Created
# { "success": true, "data": { "connectionId": "...", "publicKey": "ssh-rsa AAAA...", ... } }
```

2. **If `sftpuser` doesn't already exist, create it**
Skip this if the connection's `username` is an existing SFTP account on the
server — it already has a `.ssh` directory.

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

3. **Install the public key on the SFTP server**
As the connection's `username` on the server.

```
# As an administrator, become that user first:
sudo -iu sftpuser

echo 'ssh-rsa AAAA... ' >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
```

4. **Test the connection**
A connection must pass this before it 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"
  }'

# 200 OK — { "success": true, "data": { "success": true, ... } }
```

5. **Create the daily trigger**
Points at the **Objective 2** spec (`Customer Signups`) — it must
already have transformation code from a prior `start-analysis`, which it does.
`preRules`/`postRules` are plain English; DPF compiles them into
executable JS and returns it read-only as `preCode`/`postCode`.
`postRules` is optional — omitted here, meaning "do nothing after load"; combined
with `dedupe: false` below, that's a deliberate trade-off (an untouched file
reloads every run) rather than an oversight — see the MCP tab for the reasoning DPF walks
through before making that same choice.

```
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": "Customer Signups",
    "connectionId": "YOUR_CONNECTION_ID",
    "frequency": { "unit": "daily", "hourOfDay": 2 },
    "dedupe": false,
    "preRules": "Only pick up *.csv files"
  }'

# 201 Created — { "success": true, "data": { "triggerId": "...", "preCode": "...", "postCode": "...", ... } }
```

6. **(Optional) Fire it once immediately to test**
Don't want to wait for 02:00 UTC to confirm it works end-to-end:

```
curl -X POST https://api.dpf-it.com/job-triggers \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "run-trigger-now",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "triggerId": "YOUR_TRIGGER_ID"
  }'

# 202 Accepted — the run shows up under list-trigger-runs on /workspaces
```

MCP

Describe the connection and schedule in plain English — server, username, and what "the
customer load" refers to. If a detail is ambiguous, Claude asks before calling any DPF tool
rather than guessing; when it needs the public key installed on the server, install it and
reply to continue.

Prompt Using DPF, help me create a connection to my sftp server "test-sftp.dpf-it.com" connecting as the user "sftpuser" and scheduling a daily trigger for the customer load.

Thought for 12s

Dpf-it [list_my_workspaces]

Dpf-it [list_data] resource: "specs"

workspace confirmed; 2 candidate specs found — `CustomerSignups` and `LegacyCRMImport`, both plausible matches for "the customer load" 1.8s

"The customer load" was ambiguous between two specs, and the request didn't say
what time to run or whether re-pulled files should be skipped — asked all three
before touching any DPF API.
Thought for 7s

Asked

Which data spec is "the customer load" that the SFTP trigger should feed?

CustomerSignups · LegacyCRMImport

What time (UTC) should the daily pull run?

02:00 UTC · 06:00 UTC · 00:00 UTC · Other

Should repeat pulls skip files already loaded (dedupe by file name)? dedupe=true silently skips a same-named file that reappears; dedupe=false reloads an unchanged file every run if it's left on the server.

Yes, dedupe on file name (Recommended) · No, always reload

⏱ waiting on your answer

Answered CustomerSignups · 02:00 UTC · No, always reload

"Always reload" with no post-load action means an untouched file would load
again every day — followed up on what should happen to a file after it loads, plus
the file filter, before creating anything.
Thought for 6s

Asked

After a file is successfully loaded, what should happen to it on the SFTP server? (With dedupe off, an untouched file gets reloaded every day.)

Rename with .done suffix (Recommended) · Leave files untouched · Other

Any restriction on which files to pick up (file type or directory), or pull everything?

Only *.csv files · Pull everything · Other

⏱ waiting on your answer

Answered Leave files untouched · Only *.csv files

Every parameter was now explicit — spec, schedule, dedupe, post-load action,
file filter — so it created the connection and trigger together in one call.
Thought for 4s

Dpf-it [setup_scheduled_pull] specName: "CustomerSignups", hostname: "test-sftp.dpf-it.com", username: "sftpuser", frequency: {unit: "daily", hourOfDay: 2}, preRules: "Only pick up *.csv files", dedupe: false

connection `ba438616-...` created; test failed — public key not yet installed on the server 3.4s

Returned the generated public key and asked me to install it in `sftpuser`'s `authorized_keys` on `test-sftp.dpf-it.com`, then say when ready — it didn't retry or abandon the setup, just paused there with everything configured so far summarized.

⏸ Paused ~53s here while the public key was installed on the server

Me
continue

Thought for 2s

Dpf-it [setup_scheduled_pull] same arguments as above — reuses the existing connection by matching hostname/username

reused connection `ba438616-...`; test succeeded (4,200 files listed); trigger `64c90033-...` created on a daily 02:00 UTC schedule 2.4s

Offered to fire the trigger immediately instead of waiting for tomorrow's
02:00 UTC run, to confirm the whole path works end-to-end.

Me
yes, run now

Thought for 3s

Dpf-it [manage_trigger] action: "run-now", triggerId: "64c90033-..."

run started 0.4s

Dpf-it [manage_trigger] action: "run-history", triggerId: "64c90033-...", pageSize: 5

success — 1 file pulled, job `740706ca-...` started 0.6s

**Done.** Two rounds of clarifying questions up front (spec, schedule,
dedupe, then post-load handling and file filter), a real pause for you to install the
SSH key mid-setup, and a check-in before firing the trigger early — no parameter left
for DPF to silently guess at.

[Note]

**From here on, it's automatic:**
An hourly dispatcher checks every enabled trigger against its `frequency` — no more
manual calls needed for day-to-day loads. Each run stamps its rows with `dpf_ts`,
which is exactly what the table-source spec in [Objective 6](https://www.dpf-it.com/examples.html#obj6-aggregate) uses under
the hood to window each re-run to "what's new."

## 5 Query the Fully Loaded Data

`POST /query` runs SQL directly against your Iceberg tables, scoped to a single
`namespace` — the trailing 12 characters of `workspaceId` (see the
callout in Objective 1). This objective covers reads — `SELECT`,
`DESCRIBE`, and `SHOW TABLES`; Objective 6 covers the write side.

API

1. **Confirm the table name**

```
curl -X POST https://api.dpf-it.com/query \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "namespace": "446655440099",
    "query": "SHOW TABLES"
  }'
```

2. **Select rows**

```
curl -X POST https://api.dpf-it.com/query \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "namespace": "446655440099",
    "query": "SELECT * FROM customers ORDER BY dpf_ts DESC LIMIT 10"
  }'

# 200 OK
# {
#   "success": true,
#   "data": {
#     "columns": ["id", "name", "email", "signup_date", "dpf_line", "dpf_filename", "dpf_job", "dpf_ts"],
#     "rows": [ { "id": "4", "name": "Initech", "email": "billing@initech.test", ... } ],
#     "rowCount": 1
#   }
# }
```

3. **Count and aggregate** `dpf_filename`/`dpf_job` make it easy to see how much each load
contributed:

```
curl -X POST https://api.dpf-it.com/query \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "namespace": "446655440099",
    "query": "SELECT dpf_filename, COUNT(*) AS rows_loaded FROM customers GROUP BY dpf_filename ORDER BY rows_loaded DESC"
  }'
```

MCP

Just ask, in plain English — Claude finds the workspace and writes the SQL, so you never need
to look up a `namespace`. Ask a follow-up and it keeps going from there.

Prompt using dpf, show me the tables in my workspace

Thought for 9s

Dpf-it [submit_query] sql: "SHOW TABLES"

4 tables found — `aws_edge_locations`, `cloudfront_log`, `cloudfront_log_daily`, `customer` 5.0s

Summarized each table's likely contents from its name alone (nothing else queried yet) and offered to show schema or sample rows next.

Me
provide me a sample of the customer table

Thought for 2s

Dpf-it [submit_query] sql: "SELECT * FROM customer LIMIT 10"

10 rows returned 1.5s

Rendered as a table, dropping the free-text `comment` field and the `dpf_line`/`dpf_filename`/`dpf_job`/`dpf_ts` lineage columns for readability — noted they were available on request.

Me
give me a count of records by the filename which loaded it

Thought for 4s

Dpf-it [submit_query] sql: "SELECT dpf_filename, COUNT(*) AS record_count FROM customer GROUP BY dpf_filename ORDER BY record_count DESC"

`customer.csv`: 141 · `legacy_crm_export.csv`: 9 — 150 total 2.9s

**Done.** Three plain-English questions, three `submit_query`
calls — schema explored, sample rows inspected, and per-file load counts confirmed,
without knowing a `namespace` ahead of time.

[Warning]

**Heavier queries:** `POST /query-large` is a drop-in replacement with 4× the memory/CPU and a 120s
timeout (vs. 60s), for large scans or heavy joins/aggregations. It costs 4× the credits per call.

## 6 Aggregate Loaded Data Automatically with a Table-Source Spec

This spec's source is an existing Iceberg table instead of an uploaded file: set
`sourceType: "tables"`, and `start-analysis` generates a SQL query
(grouping/aggregating from
`sourceTables`) plus a deterministic `INSERT`/`MERGE` into
`targetTables` — DPF windows each run to rows added since the spec's *own*
last successful run automatically, so there's no `dpf_ts` bookkeeping to do by hand.

Paired with a **`spec_success` trigger** pointed at Objective 4's spec,
the aggregate re-runs itself the moment that day's load finishes — no external cron, Lambda, or
Airflow task required.

API

1. **Create the table-source spec**
No sample or format file to upload here — `sourceTables` supplies the data
instead. `targetOption: "auto-infer"` has the AI design and create
`customer_signups_daily` from the query's own output shape (pass
`targetTables` instead to pin an existing table's name, same as Objective 3).
`merge: true` makes re-runs upsert existing days instead of double-counting them.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "create-spec",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "daily-signups-aggregate",
    "sourceType": "tables",
    "sourceTables": ["customers"],
    "targetOption": "auto-infer",
    "merge": true,
    "additionalPrompt": "Count signups per day from the customers table, as columns signup_date and total_signups",
    "description": "Roll up customer signups into a daily summary table"
  }'

# 201 Created
# { "success": true, "data": { "specId": "550e8400-e29b-41d4-a716-446655440007", ... } }
# No signedUrls — sourceType: tables never uploads sample/format files.
```

2. **Start analysis**
Generates the aggregation query and the target table (first run), then executes it via the
query engine directly — no Glue job involved. The query is windowed to rows with
`dpf_ts` after the spec's last successful run; on this first run that's the
epoch, so it covers every row Objective 2–4 have loaded so far.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "start-analysis",
    "workspaceId": "YOUR_WORKSPACE_ID",
    "specName": "daily-signups-aggregate"
  }'

# 202 Accepted
# { "success": true, "data": { "specId": "...", "status": "processing", ... } }
```

3. **Poll until ready, then poll the run**
Same two-step polling as every other objective on this page — `get-status` by
`specId`, then by the resulting `lastJobId`.

```
curl -X POST https://api.dpf-it.com/data-specs \
  -H "Authorization: Bearer YOUR_DPF_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "get-status",
    "specId": "550e8400-e29b-41d4-a716-446655440007"
  }'

# 200 OK — { "success": true, "data": { "status": "ready", "lastJobId": "...", "sourceQuerySql": "...", "targetLoadSql": "...", ... } }
```
`sourceQuerySql`/`targetLoadSql` are always visible on the spec — the
AI-generated query, same view-only guarantee as the Python code Objectives 2–3 generate.

4. **Create a `spec_success` trigger on Objective 4's spec**
Fires `daily-signups-aggregate` automatically every time
`Customer Signups` (Objective 4's SFTP-triggered spec) finishes a successful
job — no `frequency`/`connectionId`, since it isn't polling anything
itself.

```
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": "spec_success",
    "specName": "daily-signups-aggregate",
    "upstreamSpecName": "Customer Signups"
  }'

# 201 Created — { "success": true, "data": { "triggerId": "...", "type": "spec_success", "upstreamSpecName": "Customer Signups", ... } }
```

MCP

Describe the rollup you want in plain English. Claude checks existing tables for a naming
convention to follow, then asks how the summary should refresh and what it should contain —
a table-source spec needs both decided explicitly, so answer those before it creates anything.

Prompt using dpf, Roll up the data by signup_date from the customer table into a daily summary table

Thought for 8s

Dpf-it [list_my_workspaces]

Dpf-it [submit_query] sql: "SHOW TABLES"

1 workspace; 4 tables including `customer` and an existing `cloudfront_log_daily` rollup 4.8s

Checked `customer`'s columns, then looked at how the workspace's one
existing daily rollup was shaped, to follow the same convention rather than invent
a new one.
Thought for 2s

Dpf-it [submit_query] sql: "DESCRIBE customer"

14 columns, incl. `signup_date`, `acctbal`, `mktsegment` 1.3s

Thought for 4s

Dpf-it [submit_query] sql: "DESCRIBE cloudfront_log_daily"

Dpf-it [list_data] resource: "specs"

confirmed the day-grain rollup pattern already used in this workspace 1.1s

Two things the pattern here doesn't answer by itself — how the summary stays
current, and what it actually contains — asked before creating anything.
Thought for 41s

Asked

How should the new customer_daily summary table stay up to date?

Manual only · Daily schedule · After signups load

What should each daily row summarize? (count of new signups is a given)

Count + acctbal stats (Recommended) · Count only · Count + acctbal + segment breakdown

⏱ waiting on your answer

Answered After signups load · Count + acctbal + segment breakdown

Both answers map directly onto `onboard_data_source`'s own
parameters — no separate `manage_trigger` call needed afterward.
Thought for 17s

Dpf-it [onboard_data_source] specName: "CustomerDailySummary", sourceType: "tables", sourceTables: ["customer"], targetTables: ["customer_daily"], merge: true, autoRefresh: "spec_success", autoRefreshUpstreamSpecName: "CustomerSignups", additionalPrompt: "Group by signup_date and mktsegment; new_customers = count, total_acctbal = sum(acctbal), derive avg from the stored sum/count rather than overwriting it, since later incremental runs add more rows for the same historical day..."

spec `9d885534-...` created; wired to re-run whenever `CustomerSignups` finishes (trigger `04fa7bfb-...`) 3.1s

Thought for 2s

Dpf-it [finish_data_source_onboarding] specId: "9d885534-...", specName: "CustomerDailySummary"

ready — query generated, initial load job `72b9279b-...` started 11.3s

Dpf-it [get_status] jobId: "72b9279b-..."

ready, not yet started 0.5s

⏱ waited 5s

Dpf-it [get_status] jobId: "72b9279b-..."

processing, 10% — "Running statement 1 of 2" 0.5s

⏱ waited 8s

Dpf-it [get_status] jobId: "72b9279b-..."

complete — 143 row(s) affected 0.5s

Verified the resulting table, then went a step further and read back the
AI-generated `targetLoadSql` itself to confirm re-runs would add to
existing day/segment rows instead of overwriting them.
Thought for 2s

Dpf-it [submit_query] sql: "SELECT * FROM customer_daily ORDER BY signup_date, mktsegment LIMIT 10"

6 columns: signup_date, mktsegment, new_customers, total_acctbal, dpf_ts, dpf_job 6.1s

Thought for 12s

Dpf-it [list_data] resource: "specs", pageSize: 1

confirmed `targetLoadSql` does `t.new_customers + s.new_customers` (additive), not an overwrite 0.4s

**Done.** Built `customer_daily` — grouped by
`signup_date` × `mktsegment`, 143 rows on the initial load, wired
to re-run automatically whenever `CustomerSignups` finishes loading. Average
balance wasn't stored as its own column: the generator derives
`total_acctbal / new_customers` at query time instead, since a stored average
can't be merged additively the way a sum and a count can — confirmed by reading the
generated SQL back, not just assumed.

[Note]

**From here on, it's automatic:**
Every time Objective 4's daily trigger loads new rows into `customers`, this trigger
fires `daily-signups-aggregate` right after — each run's window picks up exactly the
rows the previous run hadn't seen yet. A `schedule` trigger (plain
`frequency`, no upstream spec) is the alternative if you'd rather re-run on a timer
than chain off a specific load.

[Warning]

**Table-source specs only:**
Both `spec_success` and `schedule` triggers require their own
(downstream) `specName` to already be a `sourceType: "tables"` spec —
neither one supplies a file for a file-source spec to load, so `daily-signups-aggregate`
has to exist first. `upstreamSpecName` has no such restriction; it can be any spec,
file- or table-source.