How to load Facebook Ads data into Snowflake with OWOX Data Marts

What you end up with

One table in your own Snowflake account, facebook_ads_ad_account_insights, with one row per ad per day. Each row carries the campaign, ad set and ad it belongs to and the metrics you selected: impressions, clicks, spend, reach and so on.

The connector does three jobs and no more. It calls the Meta Marketing API, writes the result into that table, and repeats on a schedule. It does not reshape or model the data. What you do with the table afterwards is SQL you write, which is the point: the logic stays yours.

Before you start

In Meta. A Facebook user who can open the ad account with the role of Admin, Advertiser or Analyst, and the numeric ad account ID. You find the ID in Ads Manager under Account Overview.

In Snowflake. A storage of type Snowflake connected to your project. The storage guide asks for the account identifier, a warehouse and either a key pair, which it recommends, or a username with a programmatic access token. The role has to be allowed to create a database, a schema and tables, because the connector creates them on the first run if they are missing.

Give the connector a small warehouse of its own with auto-suspend on. A daily load is a short job, and Snowflake pricing explained shows why the warehouse it runs on decides what it costs.

Step 1: create the Data Mart

Click New Data Mart, give it a title such as “Facebook Ads Insights”, pick the Snowflake storage and click Create Data Mart.

On the Data Setup tab, set Definition Type to Connector and choose Facebook Ads.

Step 2: connect the ad account

There are two ways to authenticate.

Continue with Facebook is the short one. Sign in as the user who has access to the ad account and confirm. The connector keeps the grant renewed for as long as Meta keeps it valid.

Manually is for cases where you want your own Meta app: an access token, an App ID and an App Secret. The credentials guide walks through creating the app. The token needs the ads_read and ads_management permissions. The connector only reads; the second permission is what Meta requires for account and ad metadata.

Then enter the Account IDs. Use the numbers only, without the act_ prefix. Several accounts go in one field, separated by commas or semicolons, and the signed-in user needs access to each of them.

Step 3: choose the endpoint and the fields

An endpoint is one kind of data from the API, and each Data Mart loads one endpoint. For reporting on spend and results, that endpoint is Ad Insights (ad-account/insights): daily metrics for every ad.

The others are there when you need them:

• Ad Insights by age and gender, country, region, device platform, publisher platform and position, product ID or link URL asset. The same metrics, split by one more dimension.
• Ad Insights by Ad Set and Ad Insights by Campaign. Metrics aggregated at that level, with reach deduplicated the way Ads Manager shows it.
• Ads and Ad Creatives. Names, statuses and creative details, without metrics.

If you need a breakdown and the plain insights, that is two Data Marts and two tables.

Next, select fields. The ad ID and the two date fields are always included, because they are the keys the connector uses to match rows. Everything else is your choice.

Select what you will report on and leave the rest. You can add fields later, and the connector adds the new columns to the table.

The last screen asks where to write: database, schema and table. The setup proposes a database named after the source, the schema PUBLIC and a table named after the endpoint. If you plan to load several ad platforms, one shared database for all of them keeps later queries shorter. The examples in this guide use ads_owox.

Step 4: publish and run

Click Publish & Run Data Mart. The first run loads data from the first day of the previous month up to today, and can take a few minutes.

When it finishes, the Data Setup tab shows the source, the Snowflake table it writes to, and every column with its type.

Step 5: load history and set the schedule

Two more things turn a first load into a pipeline.

History. Click Manual Run and choose Backfill (custom period). One backfill run covers at most 31 days, so a full month fits in one run. For a year of history, run the months one after another, each after the previous one has finished.

Schedule. Without a trigger, the Data Mart does not run again. Open the Triggers tab, add a trigger of type Connector Run and choose daily, weekly, monthly or an interval, with the time zone it should follow.

Each scheduled run is incremental. It starts from the last date it loaded minus the Reimport Lookback Window, two days by default, and rewrites those days. That is how late-attributed conversions and corrected spend find their way into rows that were already loaded. If your attribution window is longer, raise the lookback in the connector’s advanced settings.

Rows are merged on the keys, so re-importing a day replaces it. It does not duplicate it.

What the table looks like in Snowflake

In this setup the data is in database ads_owox, schema PUBLIC, table facebook_ads_ad_account_insights.

Three things are worth knowing before you query it.

The names are case-sensitive. The connector creates the schema, the table and every column with quoted names, exactly as written. In Snowflake that makes them case-sensitive, and an unquoted name is read as upper case. So this fails with “does not exist or not authorized”:

SELECT campaign_name, spend
FROM ads_owox.public.facebook_ads_ad_account_insights;

and this works:

SELECT "campaign_name", "spend"
FROM ads_owox."PUBLIC"."facebook_ads_ad_account_insights";

The database name is not quoted. Everything below it is.

Metrics are numbers, results are text. impressions, clicks, spend, reach, cpc, cpm and ctr arrive as numeric columns and date_start as a date. Fields that Meta returns as lists, such as actions, conversions and conversion_values, arrive as text holding the list, and need parsing before they can be summed.

The grain is ad and day. date_start and date_stop are the same day for daily data. Campaign and ad set names are repeated on every row, so no join is needed for a campaign report.

Query the data

Both queries below were validated on Snowflake against a table loaded by the connector.

Spend, clicks and impressions by campaign and day, for the last 30 days:

SELECT
  "date_start" AS date,
  "campaign_name" AS campaign,
  SUM("impressions") AS impressions,
  SUM("clicks") AS clicks,
  SUM("spend") AS spend
FROM ads_owox."PUBLIC"."facebook_ads_ad_account_insights"
WHERE "date_start" >=
  DATEADD(day, -30, CURRENT_DATE())
GROUP BY "date_start", "campaign_name"
ORDER BY date, spend DESC;

Sum the base metrics and compute ratios from the sums. Averaging the cpc or ctr column across rows gives every ad the same weight, whatever it spent.

Facebook next to other channels, one row per day and channel:

SELECT
  "date_start" AS date,
  'facebook' AS channel,
  SUM("impressions") AS impressions,
  SUM("clicks") AS clicks,
  SUM("spend") AS spend
FROM ads_owox."PUBLIC"."facebook_ads_ad_account_insights"
GROUP BY "date_start"

UNION ALL

SELECT
  "stat_time_day" AS date,
  'tiktok' AS channel,
  SUM("impressions") AS impressions,
  SUM("clicks") AS clicks,
  SUM("spend") AS spend
FROM ads_owox."PUBLIC"."tiktok_ads_ad_insights"
GROUP BY "stat_time_day";

The second query is the start of a unified spend table. Self-service marketing analytics on Snowflake takes it further, and a view over the connector table is a good place to rename the columns once so nobody downstream has to think about quoting.

From table to report

A connector Data Mart is a Data Mart like any other, so it can be reported from directly. On the Destinations tab you attach reports to Google Sheets, Data Studio, Slack or email, each with its own columns and schedule.

For anything beyond the raw table, write the query you want people to read, such as the channel-by-day one above, and publish that as its own Data Mart. Snowflake reports in Google Sheets covers the delivery step by step.

When a run fails

Open Run History first. It shows the error Meta returned, and most of them fall into a few groups.

• Token errors such as “Session has expired”. Meta invalidated the grant. Reconnect with Facebook in the connector settings.
• Account cannot be loaded. The ID includes act_, or the signed-in user lost access to that account.
• One account of several fails. Run with one ID at a time to find it.
• “Please reduce the amount of data”. The request is too large. Shorten the backfill period, select fewer fields, or lower API Page Limit in the advanced settings.
• Rate limits. Meta throttled the app or the account. Wait and rerun, and schedule less often if it repeats.
• Empty result with no error. Check the same dates in Ads Manager. There may have been no delivery.

Where to go next

The same setup works for the other sources, and every table lands in the same Snowflake account: Google Ads, Microsoft Ads, LinkedIn Ads, TikTok Ads, Reddit Ads and Shopify.

If your warehouse is Databricks, the steps are the same and the naming differs: see Facebook Ads to Databricks. For the platform itself, start with what Snowflake is and how it works.

SnowflakeAutomate Snowflake Reports in Google Sheets with OWOX Data MartsOctober 1, 2026→SnowflakeHow to Load Google Ads Data into Snowflake with OWOX Data MartsOctober 1, 2026→SnowflakeHow to Load LinkedIn Ads Data into Snowflake with OWOX Data MartsOctober 1, 2026→

See all articles →