Lakehouse Lookup Activity No Longer Shows T-SQL Query Option

Hi Alaahady​,

I have not seen a Microsoft statement on this specific change, so I cannot say whether the disabled option is intentional. In my understanding, the Lakehouse connection in a Lookup activity reads Delta tables directly, while T-SQL queries run against the SQL analytics endpoint, which is a separate connection. These are the workable routes for returning a single value:

Option 1: connect to the Lakehouse SQL analytics endpoint. Create a connection to the endpoint (server name from the Lakehouse settings) and use it from a Script or Lookup activity with a query:

SELECT MAX(rec_mod_dtz) AS last_update
FROM dbo.fact_survey_detail;

Option 2: Notebook activity. Run the query in Spark and return the value to the pipeline:

last_update = spark.sql("SELECT MAX(rec_mod_dtz) AS last_update FROM fact_survey_detail").first()[0]
notebookutils.notebook.exit(str(last_update))

The pipeline reads it with an expression such as:

@activity('Get last update').output.result.exitValue

One thing to keep in mind with Option 1: the SQL analytics endpoint syncs metadata from the Lakehouse, so a very recent write may show up with a short delay. If the pipeline runs right after a load, Option 2 avoids that.

So my overall approach would be:

Need freshness right after a load -> Notebook activity + exit value
Simple read of committed data    -> SQL analytics endpoint connection
Table-only lookups               -> Lakehouse connection as today

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Hi Alaahady,

jubinsoni already covered the two main routes. Just two small things that trip people up with the SQL analytics endpoint option:

• Create the connection as an Azure SQL Database connection, server = the SQL connection string from the endpoint settings, database = the Lakehouse name. Use Organizational account, basic auth won't work there.
• Always give the aggregate a column alias, otherwise the output is awkward to reference. With First row only ticked you read it as @activity('Lookup1').output.firstRow.<your alias>.

If the value has to be fresh right after a load, the notebook route is safer because of the endpoint sync delay.

Good luck with it.