REST API Connector
Sync any JSON REST API into Snowflake. One connection holds the base URL and the sign-in; each endpoint becomes a table. Elglide pages through the API, flattens each record into columns, and loads the result the same way it loads a database table or a file set — append, upsert by key, or full replace — with a watermark that only moves after the load has committed.
| You bring | Elglide does |
|---|---|
| The API's base URL and how to sign in | Signs in with one of eight methods, refreshes tokens, and backs off when the API rate-limits. |
| An endpoint: path, where the records are in the response, how it pages | Walks every page, detects the columns, writes Parquet and loads it — all pages or nothing. |
| Optionally a watermark field and a key | Asks only for what changed since the last run and merges it in place. |
By default the connector runs in Elglide's cloud and reaches the API over the public internet. An API that only your network can see is reached by a hybrid agent instead: choose Hybrid agent on the connection and the agent inside your network makes every call. The cloud route refuses a private address and says so by name.
How it fits together
- Connection — the base URL (for example
https://api.example.com/v2), the sign-in method and its secret, and the connection-wide limits: requests per second, retries, request timeout, default headers. - Endpoint — one endpoint produces one target table. A connection can have many. This is where the path, the record path, pagination, the incremental mode and the flattening rules live. Endpoints are listed on the connection's page under Endpoints.
- Pipeline — picks an endpoint the way it would pick a table, and chooses the sync mode.
1. Create the connection
Connections → New → REST API. Enter the base URL and choose how the API wants you to sign in:
| Sign-in | What you enter | What Elglide sends |
|---|---|---|
| No authentication | Nothing | Plain requests — public APIs. |
| API key | The key, its name, and whether it travels in a header, the query string or a cookie; an optional prefix | X-Api-Key: …, ?api_key=… or a cookie, as the API documents. |
| Bearer token | A long-lived token | Authorization: Bearer … |
| Basic | User name and password | Authorization: Basic … |
| OAuth 2.0 client credentials | Token URL, client id and secret, scope | Fetches an access token, caches it, refreshes it before it expires, and retries a request exactly once on a 401. |
| OAuth 2.0 authorization code | Authorization and token URLs, client id (and secret), scope; you approve once in the browser (PKCE) | Keeps the refresh token encrypted on the connection; access tokens stay in memory only. |
| JWT bearer assertion | Private key, issuer, subject, audience, token URL | Signs an RS256 assertion and exchanges it for an access token. |
| AWS Signature V4 | Access key id and secret, region, service | Signs every request the way the AWS SDK does — for API Gateway and other SigV4-protected APIs. |
Test Connection makes one request to the base URL (or to the first endpoint, once you have one) and reports the status it got back — "API answered HTTP 200". Create Connection runs the test itself and saves only when it passes. Secrets are encrypted at rest and never shown again after saving; the form shows only whether one is set.
2. Add an endpoint
Open the connection and choose Endpoints → Add. An endpoint is a saved request plus the rules for reading its answer:
| Field | Meaning |
|---|---|
| Name | The table's name in the pipeline picker — orders,
issues. Unique within the connection. |
| Method and path | GET /v1/orders?status=open, or
POST with a JSON body. The path is joined to the base URL. |
| Record path | Where the records are in the response:
$ when the response is the array itself, $.data[*] or
$.result.items[*] when it is wrapped. A single object becomes one row. |
| Query and headers | Sent with every page, one per line. Values may use the tokens below. |
| Pagination | How to ask for the next page — see the table below — plus the page size and a cap on pages per run. |
| Incremental | Append reads the endpoint as it is;
Watermark names a field (updated_at, id) and asks
only for records past the last value seen; Full replace reads it all,
every run. |
| Flatten | How deep nested objects become columns
(customer.email), and what happens to arrays: stored as JSON, counted,
or the first element. |
Pagination
| Style | Use it when the API… | Tokens |
|---|---|---|
| Single response | returns everything in one answer. | — |
| Page number | takes page=2&per_page=100. |
{{page}}, {{limit}} |
| Offset / limit | takes offset=200&limit=100. |
{{offset}}, {{limit}} |
| Cursor / next token | returns a cursor in the body
($.meta.next_cursor) to send back. |
{{cursor}} |
| Link header | returns Link: <…>; rel="next"
(GitHub, many others). | — |
| Since id | takes the last record's id ($.id) to
continue from. | {{since_id}} |
Paging stops on an empty page, a short page, the stop when rule you set
($.has_more == false: a field that is empty, missing, equal to or below a
value), or the per-run page cap. Two more tokens are always available:
{{watermark}} — the last watermark value, formatted as you choose (as
stored, ISO 8601, epoch seconds or millis) — and {{run_started_at}}. A
query parameter or header whose only content is a token with no value yet, such as
since={{watermark}} on a first run, is simply left out of the request.
3. Detect the schema
Detect Schema on the endpoint fetches a sample — up to three pages or 300 records — flattens it, and proposes a column for every field it saw with a Snowflake type inferred from the values. Review the list on the pipeline's Schema tab: untick what you do not want, rename, or override a type. A field the sample never showed is not a column until you detect again. A pipeline created before the API could be reached detects and saves its map on its first run.
Schema changes
An API's records change shape without notice: a field is added, one is dropped, a number starts arriving as text. Every run samples its first page against the saved Schema Map before it writes anything, and the pipeline's three schema evolution settings decide what happens. With the defaults a new field is added to the destination and the map and the run continues; a field that vanished from every sampled record stops the run by name; a field whose type widened (a number that is now text) moves the destination column to the wider type. The run log says what changed and what was done, and the Schema tab shows the result.
Full or Incremental, and the two sync modes
On the task's Settings → Sync Mode card, first the same choice a database task makes: Full rebuilds the table from the whole endpoint in one transaction on every run (a run that reads no records fails and leaves the table as it was, rather than emptying it; the endpoint's watermark is not used) — for small reference lists and endpoints with no usable watermark. Incremental reads what the endpoint says changed, and its sync mode decides what those records do:
| Mode | Each run… | Use with |
|---|---|---|
| Append | adds every record it reads. A retried run adds a record twice — the fix is a key and Upsert. | Event and log endpoints with a watermark. |
| Upsert | merges on the key you choose (id):
updates rows found, inserts the rest, never deletes. The only mode that needs a key.
When two records in one run share a key, the one with the newer watermark value wins. |
Entities that change: orders, tickets, customers. Pair it with a watermark so each run reads only what changed. |
Sync → Full Sync on any task rebuilds the table once from the whole endpoint, whatever the task is set to.
Every run is all-or-nothing: if page 7 of 10 fails, nothing is loaded and the watermark does not move. The watermark is the largest value seen across every record of the run, and it is stored only after the load has committed — so a failure anywhere leaves the next run asking for the same records again. Because most APIs treat since as inclusive, the last record of one run usually comes back first in the next; with Upsert that is a no-op, with Append it is one duplicate.
Limits and pacing
- Requests per second (connection) — Elglide never sends faster than this, whatever the pipeline schedule says. A run that waited long on the limit says so in its log.
- 429 and 5xx — retried with backoff, honouring
Retry-After, up to the connection's retry count. A 4xx other than 401/429 fails the run by name. - Pages per run (endpoint) — a safety cap. The next run continues from the watermark, so a capped Append or Upsert run loses nothing; a capped Full run would load a partial table, so set the cap above the endpoint's size.
- Request timeout (connection) — per request, not per run.
When a run fails
- "… is a private or internal address". The base URL resolves to an address only your network can reach. Expose the API through a public address or your VPN's public endpoint, or switch the connection to Hybrid agent.
- "API answered HTTP 401" / "HTTP 403". The sign-in was refused. Check the method and the secret; for OAuth, that the scope covers the endpoint. After a 401 Elglide refreshes the token once and retries once — a second 401 is final.
- "HTTP 429 after N retries". The API kept rate-limiting past the connection's retry count. Lower Requests per second, raise Max retries, or schedule the pipeline less often.
- "No records at '…'". The record path does not match the response.
Run Detect Schema and read the raw sample; wrapped responses need the wrapper's key
(
data,results.items). - "… did not fit its column and was written as NULL". A value no longer matches the type chosen at Detect Schema (a number that became text). Widen the type on the Schema tab, or detect again.
- "Endpoint '…' is set to Watermark but names no watermark field". Set the field on the endpoint, or switch it to Append.
- "Full sync read no records … the table was left untouched". The endpoint answered with nothing on a Full task or a Full Sync. Check the request and the record path; deactivate the pipeline if the endpoint is meant to be empty.
- "key column(s) … are not in the task's Schema Map". Upsert needs its key among the loaded columns. Tick it on the Schema tab, or choose another key.