LinkedIn Ads data into PostgreSQL: four API limits

CREATE TABLE li_ads_raw (
  pulled_at     timestamptz NOT NULL DEFAULT now(),
  api_version   char(6) NOT NULL,          -- Linkedin-Version header
  request       jsonb   NOT NULL,
  response      jsonb   NOT NULL,
  element_count int GENERATED ALWAYS AS
                (jsonb_array_length(response->'elements')) STORED
);

CREATE TABLE li_ads_fact (
  pivot_type   text    NOT NULL,           -- CAMPAIGN, CREATIVE, MEMBER_JOB_TITLE
  pivot_urn    text    NOT NULL,
  period_start date    NOT NULL,
  grain        text    NOT NULL,           -- DAILY or MONTHLY, never ALL
  api_version  char(6) NOT NULL,
  metrics      jsonb   NOT NULL,
  loaded_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (pivot_type, pivot_urn, period_start, grain, api_version)
);

CREATE TABLE li_urn_lookup (urn text PRIMARY KEY, display_name text, resolved_at timestamptz);

INSERT INTO li_ads_fact (pivot_type, pivot_urn, period_start, grain, api_version, metrics)
SELECT DISTINCT ON (pivot_type, pivot_urn, period_start)   -- single-pivot example
       'CAMPAIGN' AS pivot_type, e->'pivotValues'->>0 AS pivot_urn,
       make_date((e->'dateRange'->'start'->>'year')::int,
                 (e->'dateRange'->'start'->>'month')::int,
                 (e->'dateRange'->'start'->>'day')::int) AS period_start,
       'DAILY', r.api_version, e - 'pivotValues' - 'dateRange'
FROM li_ads_raw r, jsonb_array_elements(r.response->'elements') e
WHERE r.pulled_at >= now() - interval '1 hour'
ORDER BY pivot_type, pivot_urn, period_start, r.pulled_at DESC
ON CONFLICT (pivot_type, pivot_urn, period_start, grain, api_version)
DO UPDATE SET metrics = EXCLUDED.metrics, loaded_at = now();

HEADROOM, WINDOW_VALUES = 12_000, 45_000_000

def plan(urns, days, fields, version="202610"):   # days are offsets from dateRange.start
    fields = fields[:20]
    options = [(max(1, HEADROOM // days), min(days, HEADROOM)),             # campaign batches
               (min(len(urns), HEADROOM), max(1, HEADROOM // len(urns)))]   # date slices
    per_call, slice_days = min(options, key=lambda o: -(-len(urns) // o[0]) * -(-days // o[1]))
    for i in range(0, len(urns), per_call):
        for d in range(0, days, slice_days):
            batch, span = urns[i:i + per_call], min(slice_days, days - d)
            assert len(batch) * span <= HEADROOM
            yield {"q": "analytics", "pivot": "CAMPAIGN", "timeGranularity": "DAILY",
                   "campaigns": batch, "fields": ",".join(fields), "dateRange": (d, d + span - 1),
                   "headers": {"Linkedin-Version": version, "X-Restli-Protocol-Version": "2.0.0"},
                   "cost": len(batch) * span * len(fields)}
# Keep sum(cost) per 5 minutes under WINDOW_VALUES; on 429 wait, resend.
# Assert len(response["elements"]) < 15_000 before writing li_ads_raw.

Long URN lists push the query string past 4 KB and LinkedIn answers 414. Use query tunneling: POST with X-HTTP-Method-Override: GET and the query string in a form-encoded body.