Data Platforms in Azure, Part 2: Working with Azure Data Lake

In Part 1 of this series, we explored Azure Cosmos DB and why you might reach for it when building low-latency, globally distributed applications. Now let’s switch gears and look at a platform designed for big data analytics and storage at scale: Azure Data Lake.

---

What is Azure Data Lake?

At its core, Azure Data Lake is a massively scalable data storage and analytics service built on top of Azure Blob Storage. Think of it as Blob Storage with extra capabilities for analytics:

• Hierarchical namespace (folders/directories you can manage like a file system).
• Security at the file and folder level with Azure AD integration.
• Optimized performance for big data engines (Spark, Databricks, Synapse).

There are two flavors:

• Azure Data Lake Storage Gen1: The original, mostly replaced now.
• Azure Data Lake Storage Gen2: Built on Blob Storage, so you get analytics features plus standard blob features (tiered storage, lifecycle management, etc.).

Reference: Introduction to Azure Data Lake Storage Gen2

---

Why use Data Lake instead of Blob Storage?

• Analytics integration: Tools like Azure Synapse Analytics, HDInsight, and Databricks read directly from Data Lake using the hierarchical namespace.
• Fine-grained permissions: You can secure data at the folder or file level, not just by storage account keys.
• Schema-on-read: You don’t define a schema when storing data. Tools like Synapse can infer or apply schemas later.
• Cost-effective staging: Store raw data cheaply, then transform it downstream.

---

Setting Up Azure Data Lake (Gen2)

Step 1: Create a storage account with Data Lake enabled

Portal steps:

1. Create a resource → Storage account.
2. On the Advanced tab, check Enable hierarchical namespace.
3. Finish creating the account (e.g., datalakestorageacct).

CLI example:

az storage account create \
  -n datalakestorageacct \
  -g rg-datalake-demo \
  -l westus3 \
  --sku Standard_LRS \
  --kind StorageV2 \
  --hierarchical-namespace true

Step 2: Create a container (root folder)

az storage fs file upload \
  -s ./sales_2025.csv \
  -p sales/2025/09/sales_20250901.csv \
  -f rawdata \
  --account-name datalakestorageacct

Working with Data

Uploading files

You can upload from your machine into the Data Lake container:

az storage fs file upload \
  -s ./sales_2025.csv \
  -p sales/2025/09/sales_20250901.csv \
  -f rawdata \
  --account-name datalakestorageacct

Here we’re simulating a folder structure: /sales/2025/09/.

Querying files directly

Tools like Synapse Analytics serverless SQL pools can query CSV, Parquet, or JSON directly without moving it.

Example (querying a CSV file from Synapse):

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://datalakestorageacct.dfs.core.windows.net/rawdata/sales/2025/09/sales_20250901.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) AS [result];

Organizing Your Lake

A Data Lake usually has zones to separate raw and processed data:

• Raw (landing) zone: Direct ingests from source systems, often untouched CSV/JSON.
• Curated zone: Cleaned, standardized data (e.g., Parquet with proper types).
• Presentation zone: Data shaped for reporting, BI, or machine learning.

Folder example:

/raw/sales/2025/...
/curated/sales/2025/...
/presentation/sales/...

Security and Access

• Authentication: Use Azure Active Directory. Assign RBAC roles at the container or folder level.
• Authorization: ACLs (Access Control Lists) at the directory/file level.
• Network: Private endpoints and firewalls restrict access to your VNet.

👉 Reference: Access control in Azure Data Lake Storage Gen2

---

When to Use Azure Data Lake

Choose Data Lake when you need:

• A central data lakehouse for raw + curated data.
• To integrate with big data engines (Spark, Synapse, Databricks).
• A schema-on-read approach, not fixed relational schemas.
• Secure, large-scale, low-cost storage for analytics pipelines.

Avoid Data Lake for:

• Small, transactional applications (use SQL DB, MI, or Cosmos instead).
• Storing sensitive data without setting proper ACLs and network controls.

---

Wrap-Up

Azure Data Lake is your backbone for analytics workloads in Azure. It’s not just a place to dump files—it’s a structured, secure environment where raw and processed data can coexist. With hierarchical namespaces, ACLs, and seamless integration with Synapse and Databricks, it’s the right fit for building data lakes and lakehouses.

In Part 3 of this series, we’ll shift gears to relational open-source databases in Azure, starting with Azure Database for MySQL.

---

References

• Data Lake Storage Gen2 introduction
• Access control with Azure AD and ACLs
• Querying Data Lake files with Synapse serverless SQL

Workshop: Building Your First Data Pipeline with Azure Data Lake and Synapse

Objective:
Learn how to load, organize, and query data from Azure Data Lake Storage Gen2 using Azure Synapse Analytics serverless SQL pools.

---

Step 1: Prepare the Environment

1. Create a Storage Account with hierarchical namespace enabled.
2. Create a container called rawdata.
3. Upload a sample file (e.g., sales_2025.csv) containing columns such as Date, Product, Quantity, Amount.

---

Step 2: Create a Synapse Workspace

az synapse workspace create \
  --name synapse-datalake-demo \
  --resource-group rg-datalake-demo \
  --storage-account datalakestorageacct \
  --file-system rawdata \
  --location westus3

Open the workspace in the Synapse Studio portal.

---

Step 3: Create an External Table

In Synapse Studio’s Develop tab, run the following query to link the file:

CREATE EXTERNAL DATA SOURCE SalesDataLake
WITH (
    LOCATION = 'https://datalakestorageacct.dfs.core.windows.net/rawdata',
    TYPE = HADOOP
);

CREATE EXTERNAL FILE FORMAT CsvFormat
WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = ',', STRING_DELIMITER = '"'));

CREATE EXTERNAL TABLE dbo.Sales2025 (
    [Date] DATE,
    [Product] NVARCHAR(100),
    [Quantity] INT,
    [Amount] DECIMAL(10,2)
)
WITH (
    LOCATION = '/sales/2025/',
    DATA_SOURCE = SalesDataLake,
    FILE_FORMAT = CsvFormat
);

Step 4: Query the Data

SELECT Product, SUM(Amount) AS TotalSales
FROM dbo.Sales2025
GROUP BY Product
ORDER BY TotalSales DESC;

This runs directly against files in your Data Lake—no ETL needed.

---

Step 5: Secure and Monitor

1. Assign Storage Blob Data Contributor to your Synapse managed identity.
2. Enable firewall rules and private endpoints.
3. Use Azure Monitor Logs to track access and performance.

---

Step 6: Visualize in Power BI

• Connect Power BI Desktop to Synapse Serverless SQL endpoint.
• Load your Sales2025 table.
• Create visuals such as Total Sales by Product and Monthly Trend charts.

---

Outcome:
By completing this workshop, you’ve built a small-scale lakehouse pipeline—data is ingested into Azure Data Lake, queried using Synapse SQL, and visualized in Power BI. This workflow scales directly to enterprise analytics using Data Factory, Databricks, and Synapse pipelines.

---

References

• Azure Data Lake Storage Gen2 Introduction
• Access Control in Azure Data Lake Storage Gen2
• Querying Data Lake with Synapse Serverless SQL
• Create a Data Lake in Azure Portal
• Power BI Integration with Synapse