Delta Tables with Data Flows

In this tutorial, Austin Libal from Pragmatic Works takes us through the process of modifying Delta tables using data flows in Azure Synapse Analytics. If you're interested in working with Delta Lake but don't know PySpark, Scala, or SQL, data flows offer a no-code ETL solution that could be perfect for you.

What is a Data Flow?

Data flows are a serverless data integration service within Azure Synapse Analytics and Data Factory. They enable users to build and manage ETL (Extract, Transform, Load) processes using a visual interface backed by Apache Spark clusters. With data flows, you can ingest, transform, and load data from various sources like Azure Data Lake Storage into different solutions. Data flows simplify the process, making it easier for non-technical users to handle large-scale data processing.

Using Data Flows with Delta Tables

In this tutorial, Austin explains how to use data flows with Delta tables, offering a visual way to work with these powerful data structures without the need for traditional coding. He highlights that inline datasets in data flows can be used to manipulate data from Delta tables directly. This method helps streamline the process of creating, transforming, and loading data from one source to another without requiring complex setup.

Steps to Work with Delta Tables in Data Flows

Here’s a breakdown of the steps Austin covers in the tutorial:

• Create a Data Flow: In Synapse Analytics, Austin begins by creating a new data flow. He names it "Modify Delta" to indicate its purpose—modifying an existing Delta table.
• Add a Source Transformation: He demonstrates how to add a source transformation, selecting the Delta table as the source. This step involves using an inline dataset to reference the Delta table without needing an external data source.
• Configure the Source: Austin goes over the process of linking the data flow to a Delta table by selecting the appropriate folder path within Azure Data Lake Storage. He also explains the option to enable time travel, which allows querying by timestamp or version for more precise data management.
• Data Preview: One powerful feature of data flows is the ability to preview data. Austin shows how to use the data preview to verify the current state of the Delta table before applying any changes.
• Alter Row Transformation: Austin demonstrates how to use the Alter Row transformation to delete specific records from the Delta table. This transformation is useful for upserting, updating, or deleting data within Delta tables using a simple condition.
• Sync Transformation: After making the necessary transformations, Austin sets up a sync transformation, which writes the modified data back into the Delta table. This includes configuring options like allowing deletions and specifying the key column (employee ID in this case) to identify records to be deleted.

Running the Data Flow

Once the data flow is configured, Austin explains how to run it inside a pipeline in Synapse Analytics. He provides step-by-step instructions for adding the data flow to a new pipeline and executing it to see the changes applied to the Delta table.

Verifying Changes

After the data flow execution, Austin checks the results by querying the Delta table using a serverless SQL query. He shows how to confirm that the intended record (employee ID 1) has been deleted from the Delta table. He emphasizes that this process is scalable, allowing users to delete hundreds of records at once if necessary.

Conclusion

Austin concludes by mentioning that data flows in Azure Synapse Analytics offer a powerful way to manipulate Delta tables without needing to write complex code. This method makes it easier for people in an organization who may not be familiar with traditional coding languages to participate in data transformation processes.

For more information on how to use data flows with Delta tables and other features in Azure Synapse, be sure to check out Pragmatic Works' training resources and live boot camps.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Data Flows  and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.