Explore creating new SQL focused offering as part of Data Factory Experience

[David_Wagstaff]

Within the data factory experience we have Dataflows Gen2, an offering that offers low/no code programming for data transformations. What about the developers who prefer to work within a SQL environment for all of their transformations? Data warehouse allows for queries & views to be created but they are clunky, no way to organize & lineage/reference/dependency tracking is extremely difficult if not impossible within the warehouse let alone across warehouses (or lakehouses). Microsoft should research the creation of a new offering within Data Factory that will allow developers to perform the transformation portion of the workflow as a separate tool from any warehouse/lakehouse. Allow for lineage tracking between SQL, project creation, organization for SQL scripting & integration to version control.

[sean_ms]

Hey David,

I'd like to understand your scenario more. Currently, Data Pipeline offers the Script Activity, which allows you to write custom SQL queries in addition to Script Parameters. One option would be to nest specific SQL transformations into specific Pipelines, create Pipeline Parameters for any Script parameters, then chain these Pipelines together via a Parent Pipeline and the Invoke Pipeline Activity (lineage). This way you can associate your SQL executions with specific Pipeline RunIds for reference/tracking.

Please let me know where the shortcomings are with this approach given your scenario.

Best,

Sean

[nishalit]

Hi! Microsoft Fabric does have an offering for developers that prefer to go the code route. You can read more about notebooks from the following link:

How to use notebooks - Microsoft Fabric | Microsoft Learn