Can You Use a Lookup Activity in Azure Data Factory to Query a Delta Table?

Discover how to configure Azure Data Factory to query Delta tables stored in ADLS Gen 2 without Databricks. Learn the step-by-step process and important considerations.
---
This video is based on the question https://stackoverflow.com/q/76509486/ asked by the user 'Kingsley Okoro' ( https://stackoverflow.com/u/20260433/ ) and on the answer https://stackoverflow.com/a/76511621/ provided by the user 'Chen Hirsh' ( https://stackoverflow.com/u/21079004/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Azure Data Factory: Is it possible to use a lookup activity or get metadata activity to query a Delta table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Can You Use a Lookup Activity in Azure Data Factory to Query a Delta Table?

Introduction

As businesses increasingly leverage data stored in Delta tables for analytics, one common question arises among Azure Data Factory (ADF) users: Is it possible to use a lookup activity or a get metadata activity to query a Delta table? This query is particularly relevant for those who want to extract data from Delta tables stored in Azure Data Lake Storage (ADLS) Gen 2 while eliminating the need for additional services like Databricks.

Understanding the Problem

Delta tables are a powerful feature of Databricks, enabling users to work with data in a format that supports ACID transactions and schema enforcement. However, when your aim is to connect ADF to a Delta table stored in ADLS, you might find yourself pondering if you could eliminate the dependency on Databricks for simple data retrieval tasks.

The good news is that Delta files are essentially Parquet files, and ADF can work with Parquet formats natively. This opens up a pathway to querying Delta tables without relying on Databricks.

Solution Overview

To successfully query a Delta table using ADF without Databricks, you can utilize the following steps:

Step-by-Step Guide

Create a Linked Service

Set up a linked service of type Azure Data Lake Storage Gen2. This creates a connection between Azure Data Factory and your storage account where the Delta table resides.

Create a Dataset

Generate a dataset of type Azure Data Lake Storage Gen2 and specify the file type as Parquet. You should point this to either the actual file of your Delta table or its folder.

Configure the Lookup Activity

Use the dataset you created in the previous step as the source for your lookup activity. This lets you retrieve data directly from the Delta table by querying it just like you would with regular Parquet files.

Important Considerations

While the above method allows for straightforward retrieval of data, there are a few caveats to keep in mind:

Data Integrity After Modification: If you modify the Delta table — for instance, by deleting or updating records — the Parquet source may not accurately reflect these changes. This is due to the lack of a Delta change log reader in ADF.

Needing Databricks for Advanced Queries: If your use case requires handling complex scenarios involving Delta table operations (like versions or transaction logs), you may need to consider integrating with Databricks or using a Synapse Serverless pool, which has broader support for Delta Lake features.

Conclusion

In summary, while you can indeed use a lookup activity in Azure Data Factory to query Delta tables stored in ADLS Gen 2 without relying on Databricks, it’s essential to be aware of the limitations related to data updates and complexity. By following the steps outlined above, you can effectively set up your ADF pipeline to work with Delta tables, opening up new possibilities in data processing and analytics.

With this guidance, you're now equipped to retrieve data from Delta tables in Azure Data Lake Storage Gen2 efficiently!

Видео Can You Use a Lookup Activity in Azure Data Factory to Query a Delta Table? канала vlogize