Fivetran Meta Ads Pipeline Setup: Schema, Sync, and Modeling in 2026

TL;DR: The Fivetran Meta Ads connector pulls campaign, ad set, ad, and daily insights data into your warehouse automatically. The three decisions that determine whether your pipeline is trustworthy are: attribution window alignment, lookback window for late conversions, and permission scope on your system user. Get those right at setup and the rest is dbt.

Meta's Ads Manager is a reporting UI, not a data warehouse. It is designed for human navigation, not programmatic querying. Export CSVs break on date ranges over 90 days. The Graph API requires app review for certain scopes, enforces per-account rate limits, and returns paginated cursors that timeout on large accounts. If you have more than two ad accounts or need historical data beyond 90 days, you need a pipeline.

Fivetran's Meta Ads connector solves the extraction layer. It handles OAuth token refresh, API pagination, rate-limit backoff, and incremental re-syncs for late-attributed conversions. What it does not do is model your data — that is your job, and the schema choices you make at setup directly affect whether every downstream dashboard and ML feature is correct.

This guide covers the complete fivetran meta ads pipeline setup end-to-end. Whether you are configuring a fivetran meta ads pipeline for the first time or debugging an existing one, prerequisites, connector configuration, schema anatomy, incremental sync mechanics, dbt modeling patterns, and a practical layer for enriching the pipeline with competitive creative intelligence.

Why Native Meta Exports Break at Scale

Before configuring anything, it helps to understand what you are replacing. Meta's native data access options are:

1. Ads Manager UI exports — manual CSV downloads, 90-day max range, no API, no automation.
2. Meta Marketing API — the /insights endpoint returns ad performance data but requires managing tokens, pagination, rate limits, and incremental sync logic yourself.
3. Meta Business Suite exports — monthly PDF-style reports, not queryable.

For a team running five or more ad accounts, building a custom Marketing API integration is a meaningful engineering investment. You need to handle attribution window configuration, deleted-object tombstoning, breakdown dimension fan-out (age/gender/region/placement each return separate rows), and the 28-day lookback window for late conversions.

Fivetran handles all of that. The tradeoff is cost and schema opacity — the connector creates tables with Fivetran-specific naming conventions and transformation logic you need to understand before modeling on top of them.

Prerequisites Before You Configure the Connector

Getting the permissions right before touching Fivetran saves hours of debugging later.

Meta Business Manager access

You need Business Admin or Advertiser access on every ad account you plan to sync. The cleanest approach is creating a system user in Meta Business Manager — not a human account. System users do not have 60-day token expiry and do not get logged out when a human changes their password.

Required permissions for the system user:

• ads_read — for campaign structure and ad set data
• ads_management — required by Fivetran even for read-only sync
• business_management — for account-level metadata
• read_insights — for the ad performance tracking data in ad_insights

Fivetran destination

Fivetran supports BigQuery, Snowflake, Redshift, Databricks, and several others. The SQL syntax in your dbt models will differ by destination, but the Fivetran schema is identical. For BigQuery, ensure your service account has bigquery.dataEditor on the target dataset.

Access token generation

Generate a long-lived User Access Token (60-day) or — better — a System User Access Token (never expires). In Meta Business Manager go to Business Settings > System Users > [your system user] > Generate New Token. Select your app, tick the three permission scopes above, and copy the token. You will paste this into Fivetran.

Step-by-Step: Configuring the Fivetran Meta Ads Connector

With your system user token ready, here are the steps to configure your fivetran meta ads pipeline:

Step 1 — Add the connector. In your Fivetran dashboard, click Connectors > Add Connector. Search for "Meta Ads" (previously "Facebook Ads" — the connector was renamed in late 2023). Select it.

Step 2 — Authenticate. Paste your system user access token. Fivetran will validate it against the Meta API. If validation fails, confirm the token has not expired and that the app is in Live mode (Development mode tokens cannot access real ad account data).

Step 3 — Choose ad accounts. Fivetran lists all ad accounts accessible by your token. Select the ones you want to sync. You can add more accounts later without resetting the connector.

Step 4 — Set the destination schema. Choose your target database/dataset and schema name. Default is meta_ads. If you are syncing multiple business units, use separate schemas per entity (e.g., meta_ads_brand_a, meta_ads_brand_b).

Step 5 — Configure attribution window. This is the most important decision in the entire setup. Fivetran lets you choose the click-through and view-through attribution windows. Your Fivetran setting must exactly match what you use in Ads Manager reporting. The most common mismatch is Fivetran at 1-day click / 1-day view versus Ads Manager at 7-day click / 1-day view. That mismatch will make your pipeline numbers permanently lower than Ads Manager, and no amount of dbt modeling will fix it.

Step 6 — Set sync frequency. 15 minutes is the minimum on Business plans. For most reporting use cases, hourly is sufficient. For real-time bidding optimization use cases, use 15-minute sync but be aware you will consume significant Meta API quota.

Step 7 — Trigger initial sync. Click Save and Test. The initial sync pulls full historical data — this can take 2-4 hours for large accounts with years of history. Fivetran handles the pagination internally.

Fivetran Meta Ads Schema Anatomy

After your fivetran meta ads pipeline completes its initial sync, your destination schema contains these core tables:

campaigns — One row per campaign. Columns include campaign_id, name, objective, status, daily_budget, lifetime_budget, start_time, stop_time. Status values are ACTIVE, PAUSED, ARCHIVED, DELETED — Fivetran keeps deleted rows (tombstoned) so historical join keys never break.

ad_sets — One row per ad set. Includes ad_set_id, campaign_id (FK), name, targeting, bid_amount, billing_event, optimization_goal, status, start_time, stop_time. The targeting column is a JSON blob — you will need to parse it in dbt for audience analysis.

ads — One row per ad. Includes ad_id, ad_set_id (FK), campaign_id (FK), name, status, creative_id. This is the join key to ad_creatives.

ad_creatives — One row per creative. Columns include creative_id, name, title, body, image_url, video_id, call_to_action_type, object_story_spec. This is the table most analytics engineers underuse — it contains the raw ad detail view data that makes creative performance analysis possible.

ad_insights — The primary performance table. One row per ad per day. Key columns:

• ad_id, ad_set_id, campaign_id — hierarchy keys
• date_start, date_stop — always one-day windows in daily sync mode
• spend — in account currency (not always USD)
• impressions, clicks, reach — see reach, impression, CTR
• actions — a JSON array of {action_type, value} pairs. Purchase events live here.
• action_values — a parallel JSON array with revenue values per action type
• cost_per_action_type — pre-computed CPA by action type

ad_insights_age_and_gender, ad_insights_region, ad_insights_placement — breakdown tables. Same schema as ad_insights plus a dimension column. Warning: these tables do not sum to ad_insights totals because Meta suppresses small-count breakdowns for privacy. Do not add them up expecting a reconciliation.

Incremental Sync and Attribution Window Reality

Fivetran's Meta Ads connector uses a lookback window on every incremental sync. By default, it re-syncs the last 7 days of ad_insights data. Why? Because Meta's attribution model applies conversions retroactively.

Here is the concrete scenario: a user clicks your ad on Monday, adds to cart on Tuesday, and purchases on Wednesday. If your attribution window is 7-day click, that purchase appears in Monday's ad_insights row — but it does not exist until Wednesday. Without a lookback window, Monday's row in your warehouse would be permanently understated.

With a 7-day lookback, every sync re-fetches the last 7 days from the Meta API and upserts into your warehouse. Fivetran uses the _fivetran_id composite key for upserts, so rows are updated in place rather than duplicated.

You can extend the lookback to 28 days for maximum accuracy, at the cost of higher API usage per sync. The right setting depends on your attribution window — if you use 28-day click attribution, you need a 28-day lookback to capture all conversions.

This is the mechanism that makes Meta ads attribution tracking work correctly in a warehouse context. Without it, your MoM comparisons will show phantom drops whenever a pipeline gap coincides with a high-conversion week.

See the Meta Marketing API documentation for the full list of supported attribution windows and breakdown combinations.

Rate Limits and What They Mean for Your Pipeline

Meta's Marketing API enforces rate limits at the app level per ad account. The limit is roughly 200 API calls per hour per ad account (the exact formula is documented in Meta's rate limiting guide).

For a portfolio of 10 ad accounts with 3 years of history, the initial sync will make thousands of API calls. Fivetran handles exponential backoff automatically. But once you add breakdown tables — especially ad_insights_age_and_gender and ad_insights_region — the call count multiplies. Each breakdown dimension requires separate API requests.

Practical implications:

• Do not enable all breakdown tables by default. Only enable the breakdowns you actively query in BI. Disabling unused breakdown tables in Fivetran settings immediately reduces sync time and API consumption.
• Stagger your sync times if you have multiple Meta Ads connectors in Fivetran. Running them simultaneously against the same app can compound rate limit pressure.
• 15-minute syncs on 20+ ad accounts will push API limits. Test with hourly first and only drop to 15 minutes after validating your app-level rate headroom.

For Facebook ads data analysis at high scale — hundreds of accounts — consider using the Meta Ads Reporting API in async mode and building a custom connector rather than relying on Fivetran's synchronous approach.

dbt Modeling Patterns for Meta Ads Data

Fivetran provides an open-source dbt package that generates staging and mart models. It is a reasonable starting point, but it makes opinionated decisions you may need to override:

Staging layer

The staging model should:

1. Cast spend to NUMERIC (Fivetran stores it as a string in some destinations)
2. Parse actions JSON into separate columns — one per action type you care about (purchase, add_to_cart, initiate_checkout, lead)
3. Cast date_start to DATE type
4. Join ad_id to ads to ad_sets to campaigns for the full hierarchy in a single wide row

Actions JSON parsing is destination-specific. In BigQuery:

SELECT
  ad_id,
  date_start,
  spend,
  impressions,
  clicks,
  COALESCE((
    SELECT CAST(value AS NUMERIC)
    FROM UNNEST(JSON_EXTRACT_ARRAY(actions)) AS action
    WHERE JSON_EXTRACT_SCALAR(action, '$.action_type') = 'purchase'
  ), 0) AS purchases,
  COALESCE((
    SELECT CAST(value AS NUMERIC)
    FROM UNNEST(JSON_EXTRACT_ARRAY(action_values)) AS av
    WHERE JSON_EXTRACT_SCALAR(av, '$.action_type') = 'purchase'
  ), 0) AS purchase_value
FROM meta_ads.ad_insights

In Snowflake, use PARSE_JSON and LATERAL FLATTEN instead.

Mart layer

The mart model should produce a single row per ad per day with all hierarchy columns, all action columns, and computed metrics:

• cpm = (spend / impressions) * 1000 — compare to CPM
• cpc = spend / clicks — see cost per click
• ctr = clicks / impressions — full definition at CTR
• cpa = spend / purchases — see CPA
• roas = purchase_value / spend — see ROAS

Round all metrics to 4 decimal places and handle zero-division with NULLIF.

Deduplicate before joining. The campaigns, ad_sets, and ads tables contain duplicates if a campaign was edited — Fivetran inserts a new row rather than updating in place for structural tables. Use a ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY _fivetran_synced DESC) pattern to take the latest version.

Enriching the Pipeline with AdLibrary Creative Data

Fivetran gives you performance data. It does not give you creative intelligence.

The ad_creatives table contains your own creatives' raw fields — image URL, body copy, headline, CTA. What it does not contain:

• Whether competitors are running similar formats
• How long competing ads have been active (a signal for ad timeline analysis)
• Cross-platform creative patterns from TikTok, YouTube, or LinkedIn
• AI-enriched format classification — static vs carousel vs video, direct vs native, short-form vs long-form

Meta's free Ad Library API gives you basic public creative data for political and social ads. It does not cover commercial ads at the depth you need for competitive ad research.

The AdLibrary API is what you reach for when Meta's API stops being enough. It returns richer fields than Meta's own Marketing API — creative metadata, performance signals, enrichment — covers Facebook, Instagram, TikTok, YouTube, Snapchat, Pinterest, LinkedIn, and Google in a single call, and does not require the app review and business verification friction that Meta's Marketing API demands for higher-tier scopes.

A practical architecture: pull Fivetran performance data into your fct_meta_ads_daily mart, then JOIN on creative_id to an adlibrary_creatives staging model fed by the AdLibrary API access feature. The result is a single analytics layer that answers "which creatives drove the best ROAS this week AND how do they compare to what competitors are running?" — without switching tools.

For automation workflows and AI agent pipelines that query both your own performance and competitive data in the same request, the Business tier (€329/mo) on AdLibrary is the right access level. See pricing and API details.

If you are building a cross-platform media mix modeler that needs ad creative features as inputs, AdLibrary's multi-platform coverage is what makes the feature set viable — one API, eight platforms, format-normalized. The ad spend estimator can also help you forecast API credit usage before committing to a tier.

Common Failure Modes and How to Fix Them

Token expiry mid-sync. If you used a User Access Token (60-day), it will expire. The connector will pause and alert you. Fix: switch to a System User Access Token (non-expiring) and reconnect.

Ad account permission errors. If a human account that granted permissions is deactivated or removed from the Business Manager, the token becomes invalid even for accounts the system user could access. Fix: always authenticate Fivetran via a dedicated system user, not a human user account.

Schema drift after Meta API updates. Meta occasionally adds new action types or modifies field names in the Marketing API. Fivetran handles new columns by adding them to existing tables, but your dbt models may need updates. Track Fivetran's Meta Ads connector changelog and Meta's Marketing API changelog in your team's dependency watch list.

Breakdown tables not reconciling. As noted above, ad_insights_age_and_gender rows will not sum to ad_insights totals due to Meta's privacy suppression. Do not model them as children of ad_insights — model them as separate, independent fact tables with their own audience segmentation grain.

Data lag during Meta outages. During Meta API degradation events, Fivetran syncs will queue and eventually catch up. Your downstream dashboards will show stale data during the window. Build data-freshness tests in dbt using dbt_utils.recency to alert when ad_insights has not been updated in over 2 hours.

Currency inconsistency. If you run ad accounts in multiple currencies, spend values in ad_insights are in the account's native currency. Join to account_info to get the currency code, then apply exchange rate conversion in a separate dimension table before aggregating across accounts.

Validating Your Pipeline Against Ads Manager

Before you publish any dashboards from your fivetran meta ads pipeline, run a 4-point validation:

1. Spend match. Sum spend in fct_meta_ads_daily for last 30 days by account. Compare to the same date range in Ads Manager. Acceptable variance: < 1%. Higher variance means attribution window mismatch.
2. Impression match. Same check for impressions. Impressions are not attribution-dependent, so this should be exact. Any discrepancy indicates a sync coverage gap.
3. Campaign count. Count distinct campaign_id in Fivetran vs the total in Ads Manager (including paused and archived). Fivetran should show equal or higher count due to tombstoned deleted campaigns.
4. Action type coverage. Verify that the action types you parse from JSON (purchase, lead, add_to_cart) are actually present in your raw ad_insights.actions column. If a campaign objective does not fire purchase events, the column will be empty — not null — which breaks CAST operations without a COALESCE.

For ongoing monitoring, the ROAS calculator and CPA calculator can serve as quick-check tools when you want to sanity-check aggregate numbers without running SQL. The ad budget planner is useful for projecting how much warehouse data you will accumulate as spend scales.

Frequently Asked Questions

How often does the Fivetran Meta Ads connector sync data?

The minimum sync interval is 15 minutes on Fivetran Business plans. Standard plans default to hourly or daily. Meta's Marketing API enforces rate limits per app per ad account, so high-frequency syncs across many accounts can hit throttling — Fivetran handles backoff automatically, but latency can stretch to 30-60 minutes during peak API load. For most reporting dashboard use cases, hourly sync is the right default.

Why does my Fivetran Meta Ads data differ from Ads Manager?

The most common cause is an attribution window mismatch. Fivetran defaults to a 1-day click / 1-day view window; Ads Manager often shows 7-day click / 1-day view. Set the connector's attribution window to match exactly what you report in Ads Manager. A secondary cause is deleted ads — Fivetran tombstones deleted objects but Ads Manager may aggregate them differently. A third cause is currency: if your warehouse aggregates across accounts in different currencies without conversion, totals will not match.

What tables does the Fivetran Meta Ads connector create?

Core tables are: campaigns, ad_sets, ads, ad_creatives, ad_insights (daily), ad_insights_age_and_gender, ad_insights_region, ad_insights_placement, and account_info. The ad_insights table is the primary performance table — one row per ad per day with spend, impressions, clicks, actions, and action_values broken out by action type. See the Fivetran Meta Ads schema documentation for the complete column list.

Does Fivetran support incremental syncs for Meta Ads?

Yes. After the initial full sync, Fivetran uses a lookback window to capture late-attributed conversions. By default, it re-syncs the last 7 days on each run to pick up attribution updates. You can extend this lookback in connector settings, at the cost of higher API usage. The mechanism is an upsert keyed on Fivetran's composite _fivetran_id, so rows are updated in place. For Facebook ads attribution tracking, this lookback behavior is critical for accurate conversion rate and CPA reporting.

Can I enrich Fivetran Meta Ads data with creative intelligence?

Yes. Fivetran pulls performance metrics and basic creative fields — ad_creative_id, image_url, body, title. For richer creative metadata — competitor context, format classification, copy angle tagging — you can query the AdLibrary API. It returns enriched fields that Meta's Marketing API does not expose, covering TikTok, YouTube, and other platforms in the same call. This gives your warehouse a cross-platform creative layer that Meta's free Ad Library cannot provide at the data depth required for serious competitive research workflows. See AdLibrary API access for integration details.

Conclusion

A fivetran meta ads pipeline is three decisions and a lot of dbt.

Decision one: attribution window. Match it exactly to your Ads Manager reporting window before you run the first sync — it cannot be changed without a full re-sync.

Decision two: lookback window. Seven days is the default; extend to 28 if you use 28-day click attribution. Getting this wrong means every historical data point is systematically understated.

Decision three: breakdown tables. Enable only what you actively query. Unused breakdown tables multiply API call count without adding value.

Once those are right, the schema is predictable. The ad_insights table is your grain. Parse the actions JSON, join the hierarchy, compute your ROAS and CPA in a mart model, and validate spend totals before publishing any dashboard.

For the creative layer — understanding not just how your ads performed but how they compare to competitor formats across Meta, TikTok, and LinkedIn — Fivetran cannot help you. That data lives in AdLibrary's API, and for teams running serious cross-platform ad strategy, it is the enrichment layer that makes the warehouse useful rather than just correct.

Start with AdLibrary's Business plan if your workflow is API-first. The 1,000+ monthly credits and direct API access are what pipeline-driven teams need.