If you've worked in data engineering long enough, you know the drill.
Someone from marketing pings you on a Monday morning: "Hey, can you pull campaign spend by ad set for last week?"
You run the query. Send it. Five minutes later: "Can we also add impressions?"
You run it again.
This loop doesn't end. It just gets longer.
At my previous job, I decided to break it. Here's exactly how I did it — using Microsoft Fabric, PostgreSQL, and Wren AI — and what I learned along the way.
The Stack
Before I get into the how, here's what I was working with:
Source: Meta Ads data in Microsoft Fabric (Delta tables, Gold layer)
Target: Local PostgreSQL (localhost:5432, schema: gold)
Semantic layer: Wren AI
Goal: Let business users ask natural language questions against a proper star schema — no SQL required
Step 1: Getting the Data Out of Fabric
My first instinct was a direct Fabric → Postgres connection via Spark JDBC. Clean, automated, elegant.
Didn't work. Fabric is cloud. My Postgres was localhost. Cloud can't reach your laptop. That's just how networking works.
So I went with the practical option: export as CSV, import locally.
Here's what the Gold layer looked like:
Standard star schema. Clean. Well structured. Ready to go.
Step 2: Loading into PostgreSQL
I created a gold schema in Postgres and defined tables for each CSV — all columns as text for the initial load (you can cast later, don't overthink the first load).
sql
CREATE SCHEMA IF NOT EXISTS gold;
Where I got stuck: the COPY command
My first attempt was server-side COPY:
sql
COPY gold.dim_campaign FROM 'C:/Users/myuser/Downloads/dim_campaign.csv'
WITH (FORMAT csv, HEADER true);
Got a Permission denied error immediately. The PostgreSQL service account can't read files from your Windows user profile folder. Makes sense from a security standpoint — still annoying at 11pm.
The fix: Use pgAdmin's client-side Import/Export Data instead.
Right-click the table → Import/Export Data → Import → select your CSV → Format: csv, Header: Yes, Encoding: UTF8.
Slower than bulk COPY but it works every time.
Another thing that caught me: duplicate primary keys
When I tried adding a primary key to dim_adset, Postgres threw this:
ERROR: could not create unique index "dim_adset_pkey"
DETAIL: Key (adset_sk)=(909) is duplicated.
Fix — deduplicate before adding the constraint:
sql
DELETE FROM gold.dim_adset a
USING gold.dim_adset b
WHERE a.ctid < b.ctid
AND a.adset_sk = b.adset_sk;
Always worth running a quick dedup check before adding PKs on imported data.
Step 3: The Star Schema
Standard stuff if you've built a warehouse before. Same conceptual model as what I had in Fabric/Power BI.
Step 4: Connecting Wren AI
This is where it gets interesting.
Wren AI is an open-source semantic layer with a natural language interface. You connect it to your database, define your data model (relationships, metrics, descriptions), and business users can ask plain English questions and get SQL-backed answers.
Here's the workflow I followed:
Connect data source Point Wren AI at localhost:5432, database postgres, schema gold.
Select tables Chose all 8 core tables from the gold schema.
Define relationships In the "Define relationships" screen, I created Many-to-One links using the _sk and date_key columns — matching the star schema above.
Deploy This publishes your model to the Wren engine. The Deploy button was greyed out initially — that usually means there's a validation error in your relationships. Check for incomplete or circular links.
Ask questions Once deployed, anyone can type a question and get an answer.
The Bug That Wasted My Afternoon
After setting everything up, I asked:
"Show total impressions, clicks, and spend by campaign name"
Result: empty.
No error. No warning. Just nothing.
I went to pgAdmin and ran a quick validation:
sql
SELECT 'fact_ad_performance_daily' t, COUNT() c
FROM gold.fact_ad_performance_daily
UNION ALL
SELECT 'dim_campaign', COUNT() FROM gold.dim_campaign
UNION ALL
SELECT 'dim_ad', COUNT(*) FROM gold.dim_ad;
Output:
fact_ad_performance_daily0
dim_campaign1069
dim_ad2813
I had loaded every single dimension table and completely forgot to import the fact table. 39,000 rows. Sitting in a CSV. Not in the database.
Always validate row counts before you start building the semantic layer. This is the kind of mistake that's obvious in hindsight and invisible in the moment.
Once I imported daily csv with the correct ~39k rows, the same question returned a proper breakdown. Campaign names, spend, impressions, clicks — exactly what the marketing team needed.
What Wren AI Actually Does Well
After going through all of this, here's my honest take:
It respects your existing data model. You're not reinventing your schema for Wren AI. It sits on top of what you already have. The relationships you define mirror what you'd build in any BI tool — it just adds a natural language layer on top.
The semantic layer is engineer-controlled. Business users ask questions, but they're asking against a model you built and you validate. The data engineer still owns the semantics. That matters for accuracy.
It genuinely removes the middleman. Once it's working, a non-technical user can type a question and get a real answer in seconds. That's not hype — it actually works, and it's a meaningful shift in how fast insight moves through a team.
Final Thought
The actual data engineering here wasn't complicated. Star schema, surrogate keys, foreign key relationships — standard stuff. The interesting part was watching what happened after the plumbing was in place.
The marketing team stopped waiting on me. They started getting answers in seconds. The data was finally reaching the people who needed it, at the speed they needed it.
That's the goal, right? The pipeline isn't the point. The pipeline is what makes the point possible.
Have you used Wren AI or a similar semantic layer on top of a warehouse? I'd love to hear how it went drop a comment below.