ETL and Connectors

Classic ETL extracts data, transforms it on an external server, then loads the clean result. Modern warehouses flip the last two steps: ELT extracts and loads raw data first, then transforms inside the warehouse using SQL.

ELT wins because warehouse compute is cheap and elastic. You keep raw data forever, can re-transform when business logic changes, and never lose detail to a premature cleanup step.

A connector is the EL engine: it authenticates to a source's API, pulls records on a schedule, and writes them into warehouse tables matching the source schema. It handles pagination, rate limits, retries, and schema changes so you don't write fragile scripts.

Managed connectors turn a week of API plumbing into a few clicks of OAuth and a sync frequency setting.

Several platforms specialize in pre-built marketing connectors. Fivetran and Airbyte are general EL tools with hundreds of sources. Funnel, Supermetrics, and Adverity focus specifically on marketing and ad data.

The tradeoff: managed tools cost money (often per row or per active source) but save engineering time and self-heal when APIs change. For volatile ad APIs, that reliability is worth a lot.

Most marketing sources authenticate with OAuth 2.0: you grant the connector scoped, revocable access without sharing a password. The connector stores a refresh token and silently renews access.

Watch token expiry and permission scopes. A common failure is a marketer who set up the connection leaving the company, revoking the OAuth grant and silently breaking every sync downstream.

A full load re-pulls every record each sync, simple but expensive and slow at scale. An incremental load fetches only records changed since the last sync, using a cursor like an updated_at timestamp.

Most production pipelines use incremental syncs for volume and periodic full refreshes to catch deletes and corrections. Choosing the cursor correctly is the difference between fresh data and silent gaps.

Source APIs change without warning: a platform renames a field, adds a column, or alters a data type. This is schema drift, and it silently breaks downstream models and dashboards.

Good connectors auto-detect new columns and add them, log type changes, and alert on breaking changes. Still, your transformation layer should be tested so drift surfaces as a failed test, not a wrong number on the CEO's dashboard.

How fresh does marketing data need to be? Daily is fine for spend reporting; intraday matters for pacing live campaigns. More frequent syncs mean higher connector and warehouse cost.

Match cadence to the decision. A budget-pacing dashboard may sync hourly, while an LTV cohort model that updates monthly needs only a nightly load. Don't pay for freshness no one acts on.

Connectors should write to a dedicated raw schema, untouched and append-only. Never let analysts or BI tools query raw tables directly, and never transform in place.

The raw zone is your insurance: if a transform has a bug or business logic changes, you replay from raw without re-pulling from APIs. Treat it as the immutable system of record.

The pipeline can run backward too. Reverse ETL syncs modeled data from the warehouse back into operational tools: pushing a computed LTV segment into Meta for a lookalike audience, or a churn score into Salesforce.

This closes the loop. The warehouse stops being a reporting dead-end and becomes the brain that feeds smarter targeting and personalization back to the channels.

You can write your own Python extractors against each API, full control, no per-row fees. But you then own auth refresh, rate limits, pagination, schema drift, and on-call when an API changes at 2 a.m.

Most teams buy managed connectors for standard sources and build custom only for niche or proprietary systems. Engineering time is usually scarcer than connector subscription budget.

A silent pipeline failure is worse than a loud one, because people keep trusting stale dashboards. Production EL needs monitoring: sync success alerts, freshness checks, and row-count anomaly detection.

Set freshness SLAs (for example, ad spend must be no older than 24 hours) and alert when breached. Trust in data is built on knowing the moment it goes stale, not discovering it in a meeting.

Your warehouse compute is cheap and elastic, and you want to preserve full source detail so you can re-model when business logic changes. Which loading pattern fits, and where should transforms happen?

Connectors are the EL engine that authenticates to sources, handles incremental syncs and schema drift, and lands raw data in an immutable zone. Managed tools like Fivetran, Airbyte, and Supermetrics trade subscription cost for reliability.

Modern stacks favor ELT, transforming inside the warehouse, add reverse ETL to push insights back to channels, and treat pipeline monitoring and freshness SLAs as essential, not optional.