Hello Jack17, I took a look at both internal and external documentation and here is what I found.
Divya_Pathak's flow (pull with requests, flatten, write to Lakebase, schedule it as a job) is the right skeleton, and DoTA's cautions are the ones that bite: the OAuth token expires after an hour, there's no documented payload or rate limit on Data API POSTs, and a service principal is the identity to run under. One question decides the rest of the design, and the post doesn't say: where will the code that pulls from the external API run?
If it runs outside Databricks (a small service, a serverless function, or the source system pushing to you), the Data API fits. It's PostgREST-compatible, so POST a JSON array to /public/<table> and you get one row per element in a single request. Add the header Prefer: resolution=merge-duplicates (optionally with on_conflict=<key_column>) and re-pulling overlapping data becomes insert-or-update instead of a duplicate-key error. The API URL is on the API tab in the Lakebase App; the token comes from the OAuth flow on the authentication page, not from that tab. Setup gotchas from the docs: it needs an Autoscaling project; don't use the project owner identity, because authenticator can't assume it; create the role with SELECT databricks_create_role(...) in the SQL editor rather than the Roles & Databases UI, or GRANT "<identity>" TO authenticator fails with SQLSTATE 42501; and click Refresh schema cache after you create or alter the table.
If it runs as a Databricks notebook or job, skip the HTTP hop and use a Postgres driver. Mint the same token with the SDK, connect with psycopg over SSL, and use executemany or COPY. If the JSON is nested, store the raw payload in a jsonb column and flatten it with SQL later:
import psycopg, requests
from databricks.sdk import WorkspaceClient
from psycopg.types.json import Jsonb
records = requests.get("https://api.example.com/items", timeout=60).json()
cred = WorkspaceClient().postgres.generate_database_credential(
endpoint="projects/<project>/branches/<branch>/endpoints/<endpoint>")
with psycopg.connect(host="<host>", dbname="databricks_postgres",
user="<identity>", password=cred.token, sslmode="require") as conn:
conn.cursor().executemany(
"""INSERT INTO public.api_data (id, payload) VALUES (%s, %s)
ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload""",
[(r["id"], Jsonb(r)) for r in records])
Generate a fresh credential each time you open a connection, keep the service principal secret in a Databricks secret scope, and give that principal INSERT on the target table.
If the JSON also belongs in the lakehouse, there's a third route: write it to a Delta table first, then create a synced table from Unity Catalog into your Lakebase project. The Postgres copy is read-only, which is fine if the app only reads it, and you keep the Delta history.
Short version: Data API when the writer lives outside Databricks, a Postgres driver when it lives inside, synced tables when the data needs to exist in both places anyway.
References:
Regards,
Louis.
