Introduction
Traditionally, unifying requires building brittle custom integrations for every single source. Lakeflow Connect resolves this with native, fully managed connectors for a full suite of martech sources, including ad connectors like Google Ads, Meta Ads, and TikTok Ads. Each connector incrementally lands your campaign data directly in Unity Catalog, with no custom API integrations to build or maintain.
In this post, we introduce Ad-Genie, an open-source accelerator that builds on the raw data ingested by Lakeflow Connect’s ad connectors to deliver three things out of the box: a Spark Declarative Pipeline for transforming data from all sources into one single layer,, an AI/BI dashboard for visual analysis and a Genie agent for conversational analytics. Behind the scenes, Ad-Genie converts the bronze tables ingested by Lakelflow Connect’s Google, Meta, and TikTok Ads connectors into unified, query-ready gold tables. On top of this, Ad-Genie also creates a semantic layer of Unity Catalog metric views that define your KPIs. In just three steps, Ad-Genie delivers an analytics-ready foundation that your team can trust and build on.
What you’ll build
Ad-Genie transforms raw outputs from Lakeflow Connect’s Meta Ads, TikTok Ads, and Google Ads connectors through the medallion architecture:
- Bronze: Lakeflow Connect lands raw data from each ad platform into managed tables in Unity Catalog. With fully managed connectors, you can easily set a schedule to incrementally load data from the martech sources of your choice.
- Silver: Ad-Genie normalizes the bronze layer tables via per-platform source adapters into a common schema using a Spark Declarative Pipeline. For example:
- Google's customer_id, Meta's account_id, and TikTok's advertiser_id all become account_id.
- Google reports cost_micros (millionths of a currency unit), which is divided by 1,000,000 into a standard spend column.
- Platform-specific values like Meta's ARCHIVED or TikTok's DELETE are normalized to a shared set (ACTIVE, PAUSED, REMOVED).
- Gold: Ad-Genie unions the normalized silver tables into cross-platform fact and dimension tables: fact tables carry the metrics (fact_campaign_report, fact_adset_report, fact_ad_report), and dimension tables carry the attributes those metrics are sliced by, like campaign, platform, and date.
Beyond the medallion tables, Ad-Genie adds a semantic layer to define your metrics as well as two out-of-the-box interfaces to explore them:
- Semantic layer: Three Unity Catalog metric views sit on top of the gold tables and define your marketing KPIs. These cover measures like Impressions, Clicks, Spend, ROAS, CTR, and CPA across dimensions like Date, Source Platform, Campaign, and Objective. Both the dashboard and Genie query these views, so the metric logic is defined once and stays consistent.
- AI/BI Dashboard and Genie. Ad-Genie optionally deploys two ready-made ways to explore your data: an AI/BI dashboard for visual campaign analysis, and a Genie agent for asking questions in natural language, like "What was our ROAS by campaign last month?" or "Which TikTok ad sets had the highest CPC?"
Getting Started
Before you begin, make sure you have:
- A Databricks workspace with Unity Catalog, serverless compute, and partner-powered AI features enabled.
- A SQL Warehouse (required for the dashboard and Genie)
- At least one of the following Lakeflow Connect ad connectors configured and ingesting on a regular schedule: Google Ads, Meta Ads, or TikTok Ads.
- Databricks CLI and a bash-compatible shell
You can also follow along in this step-by-step video walkthrough.
Ad-Genie is packaged as a Declarative Automation Bundle and deploys in three steps:
1. Clone, authenticate, and configure
Clone the Ad-Genie repository and authenticate with your Databricks workspace:
git clone https://github.com/databricks-industry-solutions/ad-genie.git
cd ad-genie
databricks auth login --host https://<your-workspace-url>All configuration happens in a single file, config/pipeline.yaml:
# What to deploy: full | with_dashboard | with_genie | with_metrics | pipeline_only
deployment_profile: full
# Required if deploying the dashboard or Genie
warehouse_id: abc123def456
# Where Ad-Genie writes silver, gold, and metric-view objects
silver_catalog: marketing_analytics
silver_schema: silver
gold_catalog: marketing_analytics
gold_schema: gold
mv_catalog: marketing_analytics
mv_schema: marketing_semantic_layer
# Enable each source and point it to its Lakeflow Connect bronze tables
sources:
google_ads:
enabled: true
bronze_catalog: lakeflow_google
bronze_schema: google_ads_raw
meta_ads:
enabled: true
bronze_catalog: lakeflow_meta
bronze_schema: meta_ads_raw
tiktok_ads:
enabled: true
bronze_catalog: lakeflow_tiktok
bronze_schema: tiktok_ads_raw2. Deploy
Run the following commands to validate your config/pipeline.yaml and deploy the Ad-Genie bundle:
./scripts/sync_config.sh
databricks bundle validate -t dev
databricks bundle deploy -t devsync_config.sh validates your configuration, updates the bundle definition, and patches the dashboard JSON with your catalog and schema references. bundle deploy then creates your schemas, deploys the pipeline, and registers the dashboard.
3. Run and explore
From your Databricks workspace, navigate to Workflows > Jobs & Pipelines, find ad-genie-setup, and click Run Now. This job runs the pipeline to populate your silver and gold tables, creates the three metric views, and provisions the AI/BI dashboard + Genie agent, depending on your deployment profile.
Once the job completes, you can:
- Open Catalog Explorer to browse your silver tables (per-source standardized data), your gold tables (unified cross-platform facts and dimensions), and the three metric views.
- Open the AI/BI dashboard for a pre-built view of campaign performance across all enabled ad platforms.
- Ask the Genie agent questions in natural language, like "What was our total spend by platform last week?" or "Which campaigns had the highest ROAS in Q1?"
Conclusion
Together, Lakeflow Connect and Ad-Genie give marketing and data teams a governed, cross-channel analytics layer for their ad platforms—all deployed as a single bundle on Databricks! If you're running campaigns on Google Ads, Meta Ads, or TikTok Ads and want to harness insights from your data, Lakeflow Connect and Ad-Genie are both free to try today:
MarTech connector documentation:
Ad-Genie
- GitHub: databricks-industry-solutions/ad-genie · GitHub
- Docs: See the docs/ directory in the repository for configuration, deployment, and extension guides
Since Lakeflow Connect is launching new ingestion connectors rapidly, Ad-Genie is designed to grow with your stack. Adding a new ad platform is straightforward: create a source adapter that maps the platform's schema to the standardized format, register it, enable it in your config, and redeploy! For questions or contributions, open an issue on GitHub or reach out to your Databricks account team.
Take advantage of the Lakeflow Connect Free Tier and deploy Ad-Genie today. Within an afternoon, you can have a governed dashboard and a Genie agent that your marketing team can use to optimize ad spend!
