CREATE API ENDPOINT

Defines a single fetch endpoint under a REST API source. The endpoint knows how to fetch and write raw bytes to a storage zone; format and flatten concerns belong to the downstream external table that reads the landed files.

Category: api-endpointPrivilege: adminDeltaForge extension

Syntax

CREATE API ENDPOINT [IF NOT EXISTS] <zone>.<api>.<name>
  URL '<path>'
  [RESPONSE FORMAT { JSON | XML }]
  [OPTIONS (<key> = '<value>', ...)]

Description

## Overview CREATE API ENDPOINT declares a named fetch endpoint under an existing rest_api CONNECTION. It is the API analogue of CREATE EXTERNAL TABLE: it does not move any data on its own, it only records how to fetch. The three-part name <zone>.<api>.<name> is deliberate: - <zone> anchors where landed files are written (a zone's storage root). - <api> matches the rest_api connection (host, base URL, auth). - <name> is the leaf identifier of this endpoint under that API. ## Behavior - No network I/O at parse or execute time, the statement only writes the definition. Use CALL API ENDPOINT to preview the wire response, or INVOKE API ENDPOINT to run production fetches. - path_param.* values fill {placeholder} slots in URL. Unfilled placeholders cause INVOKE / CALL to fail with a clear error. - query_param.* values become URL query parameters; headers.* values become HTTP request headers. - pagination.* keys select and configure a pagination strategy. The engine currently understands 'offset', 'cursor', and 'link_header'. Strategy-specific sub-keys (cursor_param, page_param, per_page_param, etc.) are documented with the rest_api connector. - watermark_column / watermark_value carry incremental state; INVOKE updates them on success, FULL REFRESH resets them. - The endpoint writes raw response bodies under the zone's storage root in a timestamped per-run folder. A downstream external table with a matching schema reads those files. ## Access Control Requires the admin role. The connection itself must already exist. ## Compatibility DeltaForge extension. No direct analogue in Databricks or Iceberg: REST ingestion is usually externalised to Airflow, Fivetran, or a custom worker in those stacks.

Parameters

Name	Type	Description
`name`		Fully qualified endpoint name: <zone>.<api>.<name>. The <zone>.<api> prefix must match an existing CONNECTION row of TYPE = rest_api; <name> is the leaf, unique within that connection.
`url`		URL path template appended to the connection's base URL (e.g. '/repos/{owner}/{repo}/issues'). Placeholders resolve at INVOKE time from the endpoint's path_param.* options, with USING (...) runtime overrides taking precedence.
`response_format`		Response format: JSON or XML. Drives only the file extension the engine writes (.json / .xml). The downstream external table decides how to parse.
`options`		Endpoint-scoped options. Recognised prefixes: path_param.<k> (URL template variables), query_param.<k> (URL query string), headers.<K> (HTTP request headers), pagination.* (strategy + cursor/offset keys), watermark_column / watermark_value (incremental state), max_pages, write.* (file layout hints), override.* (fallbacks). Keys whose names match the secret-reject list (e.g. authorization, api_key, token) are rejected at parse time, supply auth material via the connection's CREDENTIAL instead.
`if_not_exists`		When true, silently skip if the endpoint already exists.

Examples

-- GitHub issues for a single repo
CREATE API ENDPOINT landing.github.issues
  URL '/repos/{owner}/{repo}/issues'
  RESPONSE FORMAT JSON
  OPTIONS (
    'path_param.owner' = 'anthropics',
    'path_param.repo' = 'claude-code',
    'query_param.state' = 'open',
    'query_param.per_page' = '100',
    'pagination.strategy' = 'link_header',
    'max_pages' = '50'
  );

-- Idempotent bootstrap inside a pipeline
CREATE API ENDPOINT IF NOT EXISTS landing.crm.accounts
  URL '/v2/accounts'
  RESPONSE FORMAT JSON
  OPTIONS (
    'query_param.updated_since' = '2026-01-01',
    'pagination.strategy' = 'cursor',
    'pagination.cursor_param' = 'next_token',
    'watermark_column' = 'updated_at'
  );

-- XML SOAP-style endpoint
CREATE API ENDPOINT landing.partner.orders
  URL '/orders/export'
  RESPONSE FORMAT XML
  OPTIONS ('query_param.format' = 'xml');

Pitfalls

Never place secrets (authorization headers, api keys, bearer tokens) directly in OPTIONS. The secret-reject list blocks the most common names, but the protection is opt-out and the value would otherwise persist in the catalog. Put auth on the rest_api CONNECTION via CREDENTIAL instead.
Format concerns do NOT belong on the endpoint. Only the file extension follows RESPONSE FORMAT. Flattening, JSON pointer paths, element-to-row mapping, and schema inference are all owned by the downstream CREATE EXTERNAL TABLE that reads the landed files.
If URL has a {placeholder} that no path_param.* (and no INVOKE ... USING (...) override) fills, every invocation fails at URL-build time. Use DESCRIBE API ENDPOINT to inspect the resolved template.
<zone>.<api>.<name> is a three-part name, NOT a Delta table reference. Parsing will reject two-part names here, and INVOKE on a non-existent <api> portion points at 'connection not found', not 'endpoint not found'.