Forum Discussion
Issue with Lookup Activity in Fabric Pipeline – Missing Query Option
Hi Team,
I’m encountering an issue with the Lookup activity in a Fabric Pipeline. When I connect to a Lakehouse and select a table, the “Query” option does not appear.
My goal is to create a watermarking table for incremental loads, but without the Query option, I’m unable to proceed.
Could you please advise on how to enable or use the Query option in this scenario?
Thank you.
Hi Srivallika ,
You’re not missing anything – this is how the Lookup activity behaves with Lakehouse sources right now.
When you select a Lakehouse table in Lookup, Fabric only lets you read the whole table or a single row using “First row only”. The Query option is not available for Lakehouse connections, which is why you don’t see it in the UI. That option only appears for some SQL-based sources like SQL Database or Data Warehouse, not for Lakehouse tables.
Because of that, you can’t write custom SQL in Lookup directly against a Lakehouse table to pull a watermark value.
The usual workaround people use is to either:
Store the watermark in a Fabric Warehouse / SQL Database instead of a Lakehouse and use Lookup with a query there, or
Use a Notebook activity to read the watermark from the Lakehouse with a simple SQL or Spark query and pass the value back to the pipeline as a parameter.
So in short: the Query option can’t be “enabled” for Lakehouse in Lookup today – it’s a current limitation of the activity.
If your design really needs query-based lookups, the cleanest option is to keep the watermark table in a Warehouse or SQL DB. If you’re okay calling a notebook, then reading it from the Lakehouse works fine as well.
– Gopi Krishna
1 Reply
Hi Srivallika ,
You’re not missing anything – this is how the Lookup activity behaves with Lakehouse sources right now.
When you select a Lakehouse table in Lookup, Fabric only lets you read the whole table or a single row using “First row only”. The Query option is not available for Lakehouse connections, which is why you don’t see it in the UI. That option only appears for some SQL-based sources like SQL Database or Data Warehouse, not for Lakehouse tables.
Because of that, you can’t write custom SQL in Lookup directly against a Lakehouse table to pull a watermark value.
The usual workaround people use is to either:
Store the watermark in a Fabric Warehouse / SQL Database instead of a Lakehouse and use Lookup with a query there, or
Use a Notebook activity to read the watermark from the Lakehouse with a simple SQL or Spark query and pass the value back to the pipeline as a parameter.
So in short: the Query option can’t be “enabled” for Lakehouse in Lookup today – it’s a current limitation of the activity.
If your design really needs query-based lookups, the cleanest option is to keep the watermark table in a Warehouse or SQL DB. If you’re okay calling a notebook, then reading it from the Lakehouse works fine as well.
– Gopi Krishna