In Fabric Data Pipeline → Lookup activity:
When you connect Lakehouse and select Table
Fabric does NOT show a “Query” option
👉 Because Lookup with Lakehouse only supports table-level access, not custom SQL queries.
Important Limitation (Key Point)
🔴 Lookup activity in Fabric does NOT support SQL queries on Lakehouse tables
That’s why:
You only see Table
No Query textbox appears
You cannot write SELECT MAX(watermark) directly
---
How to Handle Watermark for Incremental Load (Correct Ways)
✅ Option 1: Use Lakehouse Table with Single Row
Best & simplest approach.
Design your watermark table like this:
watermark_value
2024-12-01 00:00:00
Then in Lookup:
Source: Lakehouse
Table: watermark_table
First row only: ✅ enabled
You can access it using:
@activity('Lookup_Watermark').output.firstRow.watermark_value
✔ No query needed
✔ Fully supported
---
✅ Option 2: Use Notebook activity (Recommended for flexibility)
If you need:
MAX()
filtering
complex logic
Then:
1. Use Notebook activity
2. Run Spark SQL:
SELECT MAX(watermark_value) AS watermark FROM watermark_table
3. Return the value
4. Use it in the pipeline via notebook output
✔ Most flexible
✔ Best practice for complex logic
---
✅ Option 3: Use Warehouse instead of Lakehouse
If your watermark table is in a Fabric Warehouse:
Lookup → Source = Warehouse
You will see “Query”
You can write SQL normally
---
Summary (Quick Answer)
Scenario Query Visible?
Lakehouse + Lookup ❌ No
Warehouse + Lookup ✅ Yes
Notebook + Lakehouse ✅ Yes
---
Recommended Architecture for Incremental Load
✔ Watermark table = 1 row only
✔ Use Lookup (table) OR Notebook
❌ Do not expect SQL Query option in Lakehouse Lookup
How you want to update watermark
I’ll design the exact incremental pipeline flow for you step by step 🚀
