Add Support for OUTPUT Statement in Fabric Data Warehouse

[esatterfield]

The OUTPUT statement is a commonly-used function in the world of databases and T-SQL and is integral to a variety of data operations. Not being able to return the OUTPUT of a script severely hampers the ability to perform numerous data operations that have dependencies on consuming newly created keys and adds a barrier to Fabric adoption.

EXAMPLE:

You are loading multiple files from multiple clients. You want to ensure data quality and be able to clean up the data warehouse if bad data gets loaded. You use a standard DB practice of creating a logging table and inserting a record so you have a unique ID that you can then assign to all upserted records. Your normal process would be:

1. Insert log record.
2. Output new log record key.
3. Include log record key in upsert scripts for tables.

Without an OUTPUT statement in Fabric, you can't perform item #2 as there could be numerous jobs inserting to the same table concurrently.

MS Document Link: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16

[paul_dibenedett]

I have the same use case for our medallion architecture. Would love to have OUTPUT to record a simple CDC log.