How to connect Facebook Ads to Databricks, step by step

What you end up with

A Delta table in your own Databricks workspace, facebook_ads_ad_account_insights, with one row per ad per day: the campaign, ad set and ad it belongs to, and the metrics you selected.

The connector calls the Meta Marketing API, writes the result into that table and repeats on a schedule. It does not reshape or model the data. Everything on top of the table, in SQL or in a notebook, is yours to write.

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 from Ads Manager.

In Databricks

A storage of type Databricks connected to your OWOX Data Marts project. The storage guide asks for three values:

• Host: the workspace URL.
• HTTP Path: the path of the SQL warehouse that will run the loads, from that warehouse’s connection details.
• Personal Access Token: generated in your Databricks user settings.

The token’s user needs permission to create schemas and tables in the catalog you choose, and to run queries on that SQL warehouse.

The HTTP path decides which SQL warehouse does the work, and so what the load costs. Turn on auto stop and start small. Databricks pricing explains what a short daily job on a SQL warehouse is billed for.

Step 1: create the Data Mart and choose the connector

Click New Data Mart, give it a title, pick the Databricks storage and click Create Data Mart. On the Data Setup tab, set Definition Type to Connector. The setup opens with the list of sources.

Choose Facebook Ads and click Next.

Step 2: sign in and enter 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. No developer app is needed, and the grant is kept renewed for as long as Meta keeps it valid.

Manually is for teams that want their own Meta app: an access token, an App ID and an App Secret. The token needs the ads_read and ads_management permissions. The connector only reads; Meta requires the second permission for account and ad metadata. The credentials guide covers creating the app.

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

Step 3: choose the endpoint

An endpoint is one kind of data from the API. Each Data Mart loads one endpoint, so a second endpoint means a second Data Mart and a second table.

For reporting on spend and results, choose Ad 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 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.

Step 4: choose the fields

The ad ID and the two date fields are always included, because they are the keys the connector matches rows on. Everything else is your choice.

Select what you will report on and leave the rest. Fields can be added later, and the connector adds the columns to the table.

Step 5: name the catalog, schema and table

The last screen is where Databricks differs from other warehouses: the destination has three levels.

The setup proposes the catalog main, a schema named after the source and a table named after the endpoint. Each is created on the first run if it does not exist. If you load several ad platforms, pointing them all at one schema keeps later queries shorter.

Click Save, then Publish & Run Data Mart. The first run loads data from the first day of the previous month up to today.

Load history and set the schedule

Backfill

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 reach 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 a re-imported day replaces the earlier version and is not duplicated.

What the table looks like in Databricks

A three-level name. With the proposed names the table is main.facebook_ads_owox.facebook_ads_ad_account_insights. No quoting is needed, and names are not case-sensitive.

Keys and comments in the catalog. The connector declares ad_id, date_start and date_stop as the table’s primary key and writes each field’s description as a column comment. In Catalog Explorer the table documents itself.

Metrics are numbers, results are text. impressions, clicks, spend, reach, cpc, cpm and ctr are numeric and date_start is a date. Fields that Meta returns as lists, such as actions, conversions and conversion_values, are stored as JSON text 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 a campaign report needs no join.

Query the data

Both queries use the column names and types the connector creates on Databricks, and were checked on a Databricks SQL warehouse.

Spend, clicks and cost per click 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,
  SUM(spend) / NULLIF(SUM(clicks), 0) AS cpc
FROM main.facebook_ads_owox.facebook_ads_ad_account_insights
WHERE date_start >= date_sub(current_date(), 30)
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 another channel, 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 main.facebook_ads_owox.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 main.ads_owox.tiktok_ads_ad_insights
GROUP BY stat_time_day;

That is the start of a unified spend table. Each platform names its day and its cost differently, and this query is where those differences are settled once.

From table to report

A connector Data Mart is a Data Mart like any other, so it can be reported from directly. On its 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, publish the query you want people to read, such as the channel-by-day one above, as its own SQL Data Mart. Databricks to Google Sheets walks through both the scheduled report and the self-service route for business users.

When a run fails

Open Run History first. It shows the error Meta returned, and most 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 five screens load the other sources into the same workspace: Google Ads, LinkedIn Ads and TikTok Ads.

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

DatabricksWhat is Databricks and how does it work?October 1, 2026→DatabricksDatabricks pricing explained: DBU rates, compute types and what drives your billOctober 1, 2026→DatabricksHow to connect Databricks to Google Sheets, without CSV exportsOctober 1, 2026→

See all articles →