topic Re: About Pipeline Query in Data Engineering

About Pipeline Query

kye1 — Tue, 18 Nov 2025 02:32:40 GMT

Hi Team,

I met a problem that when I use Lookup from Fabric Pipeline, linking lakehouse and I can't find the "Query" even I choose Table.
The case is that I create a table to be the watermark to do increamental load but cant find "Query" when using Lookup in Pipeline.
Please help. Thank you guys.

Best Regards,
Kye

Re: About Pipeline Query

tayloramy — Tue, 18 Nov 2025 14:42:59 GMT

Hi @kye1,

THe Lookup activity does not support queries for lakehouses, only Tables and Files.

I recommend you vote for this idea: Query a lakehouse using LookUP Activity - Microsoft Fabric Community

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

Re: About Pipeline Query

NandanHegde — Tue, 18 Nov 2025 17:21:09 GMT

You can use Azure SQL Db connector and connect to the lakehouse via SQL anlaytics endpoint and then use look up activity and query

Re: About Pipeline Query

kye1crystal — Wed, 19 Nov 2025 01:30:12 GMT

Thanks @tayloramy and @NandanHegde. You guys do help me a lot

Re: About Pipeline Query

NandanHegde — Wed, 19 Nov 2025 02:13:08 GMT

Glad it was helpful 🙂

Re: About Pipeline Query

Anonymous — Fri, 21 Nov 2025 01:42:05 GMT

Hi @kye1 ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by @NandanHegde and @tayloramy . If you still require support, please let us know, we are happy to assist you.

Thank you.

Re: About Pipeline Query

Anonymous — Mon, 01 Dec 2025 05:20:56 GMT

Hi @kye1 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

Re: About Pipeline Query

kye1crystal — Tue, 02 Dec 2025 01:11:09 GMT

Hi @Anonymous ,
The priority of this job seems to lower. So I didn't continue. I will update when I keep finding something. Thanks.

Re: About Pipeline Query

Srisakthi — Wed, 03 Dec 2025 11:36:48 GMT

Hi @kye1 ,

There is an option in Copy activity , are you looking for something like this?

Regards,

Srisakthi

Re: About Pipeline Query

kye1crystal — Thu, 04 Dec 2025 01:23:15 GMT

Hi @Srisakthi ,
Yes , I am looking for this T-SQL Query option in Look-up activity

Re: About Pipeline Query

Anonymous — Tue, 09 Dec 2025 09:58:22 GMT

Hi @kye1crystal ,

Thank you for clarifying. The T-SQL Query option shown in your screenshot is only available in the Copy Data activity when using the Lakehouse SQL analytics endpoint.

For the Lookup activity, this option isn't supported with Lakehouse connections. Lookup can reference only Tables or Files, which is why you don't see the query textbox.

If you want to run a query as part of your Lookup logic, you can use the Azure SQL Database connector and connect it to the Lakehouse SQL analytics endpoint. This way, Lookup will support T-SQL queries.

I hope this explains the behavior.

Thank you.

Re: About Pipeline Query

kye1crystal — Tue, 09 Dec 2025 10:00:57 GMT

Thank you @Anonymous

Re: About Pipeline Query

Haseeb9 — Tue, 30 Dec 2025 13:08:44 GMT

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 🚀