Loading...

Azure Data Factory\ Synapse Analytics: Validate File\Folder before using!

Azure Data Factory\ Synapse Analytics:  Validate File\Folder before using!

In real time, every project would deal with Azure storage or Azure SQL Database. It can be blobs, folders/directories, files. It becomes a crucial step to validate the file\folder\table before actually using them.

 

Few usecases:

  1. Suppose we have to load a file named SalesData.csv from a folder that gets created everyday in the format yyyy/MM/dd. Before we use this file in a copy data activity or a data flow activity, we have to first validate, if the folder exists or not. 
  2. If the folder exists, we might want to validate the size of the file. This is important, because, sometimes the files bring no data, i.e. a 0 kb file.
  3. Another usecase would be to validate a table structure or file structure and make sure it is compliant with what we expect. 

In ADF\Synapse, we can use Validation activity and\or getmetadata activity to validate files, folders and tables. 

 

Validation Activity Settings & short description follows:

Screenshot 2024-04-06 at 10.30.31 AM.png

In the above image, we have a referenced a dataset called DelimitedText2, which would point to either a folder or file in azure blob storage or a table in a Azure SQL DB. Timeout property holds the time afterwhich the activity would timeout (note that, it wont fail). For instance, if the validation activity is meant to validate the presence of a file\folder\table, and it doesn't find the corresponding item, after the timeout time, the activity execution stops and timesout. Next, we have the sleep property which makes the validation activity wait for certain number of seconds before revalidation or timeout. Minimum size property is used to mention the minimum size of a file in bytes (not applicable to table based dataset). 

 

When the validation activity completes execution or times out, we can access the output json of the validation activity to know about the validation results.

 

Let us look at the GetMetadata activity and its settings. 

Screenshot 2024-04-06 at 10.42.55 AM.png

Like validation activity, we have few properties that would help us validate the file\folder\table in a GetMetadata activity in ADF\Synapse. Depending on whether the dataset points to a folder\file\table, the properties (field list) would differ.

 

The above image depicts the field list corresponding to a folder in ADLS. When we make the dataset point to a file, it would be as below.

 

Screenshot 2024-04-06 at 10.47.37 AM.png

So, if the dataset points to a file or table, we see couple of additional properties like Column Count, Size, Structure. 

 

Having seen about the individual properties\ field list of both the activities, its time to compare and know the similarities and differences. 

The below table compares the capabilities of Validation activity & Get metadata activity.

  Validation Activity Get metadata activity Property Used
Validate File Yes Yes Exists (returns boolean)
Validate Folder Yes Yes Exists (returns boolean)
Validate File Size Yes Yes

Get Metadata Activity: Use Size property in field list

Validation Activity: Use Minimum size field in Settings tab

Validate File Structure No Yes

Get Metadata:

Use the Field List: Structure.

Then, Use another activity like If condition to validate the structure against the expected.

Validate File Column Count No Yes Get Metadata:

Use the Field List: Column Count.

Then, Use another activity like If condition to validate the count against the expected.

 

In a nutshell, ADF\Synapse comes with a variety of activities, sometimes with similar characteristics or capabilities. When it comes to validation, it is based on the use case, we decide to use either Validation activity or Get Metadata activity or both. 

Published on:

Learn more
Azure Developer Community Blog articles
Azure Developer Community Blog articles

Azure Developer Community Blog articles

Share post:

Related posts

Building Course Registration Project with Azure SQL Database

This semester long project was completed by Master’s students at Cornell University with mentorship from the Azure SQL database product ...

18 hours ago

June patches for Azure DevOps Server

This month, we are releasing fixes that impact our self-hosted product, Azure DevOps Server. The following version of the product has been pat...

20 hours ago

General availability of Azure WAF Bot Manager1.1 Ruleset

Today, we are launching the general availability of Bot Manager1.1 ruleset in Azure WAF integrated with Azure Front Door. Bot Manager1.1 exte...

22 hours ago

Controlling Data Egress in Azure

This article sheds light on the importance of data egress control in Azure networks. Focusing on regulated companies that demand stringent dat...

1 day ago

Mastering Azure Cosmos DB: A Comprehensive Guide from Prototype to Production

Looking to transition your Azure Cosmos DB applications from prototype to production? This video series is the comprehensive guide you've been...

2 days ago

Vector Search using 95% Less Compute | DiskANN with Azure Cosmos DB

Azure Cosmos DB and Microsoft’s DiskANN can help developers achieve an accurate, efficient, and scalable vector search even at massive scale. ...

4 days ago

Getting started with Azure Container Storage

Learn how to get started with Azure Container Storage through this informative video. With containers rapidly becoming the preferred method fo...

6 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy