About Pipeline Query

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 🚀