Direct API ingestion framework

The problem

Marketing and commerce data arrived through third-party connectors. Connectors are fast to adopt but accumulate costs of their own: per-connector licensing, opaque failure modes, rate and field limitations, and a support boundary that sits between you and the API you actually depend on. When a pipeline breaks at 7 a.m., you want the request, the response, and the retry logic in your own warehouse, not in a vendor’s log file.

The constraint was doing this for many sources at once, without each integration becoming a bespoke project: TikTok Shop, The Trade Desk, CM360, SA360, Amazon Ads, and others, each with quirks in pagination, token handling, and payload shape.

The approach

I built a shared ingestion framework in Python and Snowflake rather than a connector per source:

• Shared execution scaffolding: Python procedures on Snowflake handle authentication, pagination, and error handling, with secrets and network rules managed at the platform level instead of per pipeline.
• Request audit log: API requests are recorded - endpoint, timing, outcome - so failures are diagnosed from a table, not a support ticket.
• Append-only landing: raw responses land in append-only VARIANT tables. Nothing is transformed on the way in; retained payloads can be replayed without another request to the source.
• Scheduled orchestration: Snowflake tasks schedule the framework per source, with dbt models building the typed, tested layer above the landing zone.

The framework’s first production uses were TikTok Shop’s Partner API - seven reporting endpoints plus three administrative ones - and The Trade Desk’s direct feed, which also replaced the last vendor hop for that source.

The outcome

Major sources that previously ran through third-party connectors now load directly through the shared framework, and additional integrations can reuse its execution and logging components. The audit log turned connector opaqueness into queryable history, and append-only landing provides retained source payloads for downstream reprocessing. Marketing mix models and CAC reporting consume the resulting tables.

This page describes the platform mechanism; it intentionally omits internal identifiers and business figures.

Tradeoffs and lessons

You inherit the API’s rough edges. Direct ingestion means owning pagination bugs and schema drift yourself. The audit log is what makes that ownership reasonable - every incident has a receipt.

Append-only costs storage and pays it back in replays. Warehouse storage is cheap compared to re-fetching history from rate-limited marketing APIs that may not serve it again.

Framework discipline beats integration speed. The temptation with a new source is to special-case it. The framework only stayed reusable because each integration contributed back shared behavior instead of a fork.