# How to Build Data Infrastructure on Azure for Real-Time Analytics

> Build real-time data infrastructure on Azure. Learn to architect a five-layer pipeline with Azure Data Factory, Data Lake Storage, Databricks, Synapse Analytics, and Power BI for powerful analytics.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-09

---

**To build real-time data infrastructure on Azure, architect a five-layer pipeline using Azure Data Factory for ingestion, Azure Data Lake Storage Gen2 for raw storage, Azure Databricks for Spark-based transformation, Azure Synapse Analytics for warehousing, and Power BI for visualization.**

This comprehensive pattern enables near-real-time analytics with end-to-end security and scalability. According to the `DataExpert-io/data-engineer-handbook` source code, specifically the projects outlined in [`projects.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/projects.md), this architecture supports low-latency dashboards while maintaining ACID compliance and cost-efficient storage tiers.

## Architecture Overview

The end-to-end flow connects on-premise sources to cloud analytics through a modular pipeline. Data moves continuously from source systems into a data lake, undergoes Spark-based refinement, lands in an analytics-ready warehouse, and surfaces in Power BI dashboards.

```

On-prem SQL Server ──► Azure Data Factory ──► Azure Data Lake (raw) 
   │                                                   │
   ▼                                                   ▼
Azure Databricks (Spark) ──► Delta Lake (processed) ──► Azure Synapse 
                                                             │
                                                             ▼
                                                    Power BI (real-time dashboards)

```

This design achieves **near-real-time latency** (seconds to minutes) by leveraging Delta Lake for incremental processing and DirectQuery for live dashboard updates.

## Step-by-Step Implementation

### Data Ingestion with Azure Data Factory

**Azure Data Factory (ADF)** handles the initial extraction from on-premise systems. Configure a pipeline that connects to an on-premise SQL Server using a **Self-Hosted Integration Runtime** to bridge network boundaries.

Store all connection credentials in **Azure Key Vault** and reference them dynamically in your linked services. The pipeline copies tables continuously—triggered by schedule or change-data-capture—into Azure Data Lake Storage Gen2 as raw Parquet files.

```json
{
  "name": "CopySqlToADLS",
  "properties": {
    "activities": [
      {
        "name": "CopyActivity",
        "type": "Copy",
        "inputs": [{ "referenceName": "SqlSourceDS", "type": "DatasetReference" }],
        "outputs": [{ "referenceName": "AdlsRawDS", "type": "DatasetReference" }],
        "typeProperties": {
          "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT * FROM dbo.Orders" },
          "sink": { "type": "ParquetSink" }
        }
      }
    ],
    "annotations": []
  }
}

```

The dataset `SqlSourceDS` points to the on-prem SQL Server via the self-hosted IR, while `AdlsRawDS` writes to `raw/orders/` in ADLS Gen2.

### Raw Storage in Azure Data Lake Storage Gen2

**Azure Data Lake Storage Gen2** provides the hierarchical namespace, high scalability, and hot/cold tiering required for cost-effective data lakes. Organize data using a partitioned folder structure to support incremental processing:

```

raw/<source>/<entity>/yyyy/mm/dd/

```

This convention enables Spark to read only new partitions during transformation jobs, reducing compute costs and processing time.

### Transformation with Azure Databricks and Delta Lake

Spin up an **Azure Databricks** cluster to run Spark jobs that read raw Parquet files from ADLS Gen2. Apply cleansing, enrichment, and business logic—including windowed aggregations and joins—before writing to the processed layer.

Use **Delta Lake** format for the output to enable ACID transactions, schema enforcement, and time-travel capabilities. This is critical for maintaining data quality in real-time pipelines.

```python

# Read raw data

raw_df = spark.read.format("parquet") \
    .load("abfss://raw@mydatalake.dfs.core.windows.net/orders/")

# Simple cleansing

clean_df = (raw_df
            .filter(col("order_date").isNotNull())
            .withColumn("order_amt_usd", col("order_amt") * 1.0))

# Write as Delta Lake

clean_df.write.format("delta") \
    .mode("overwrite") \
    .partitionBy("order_date") \
    .save("abfss://processed@mydatalake.dfs.core.windows.net/orders/")

```

### Analytics Store with Azure Synapse Analytics

**Azure Synapse Analytics** serves as the enterprise data warehouse. Connect to the Delta Lake tables via **Synapse Serverless SQL pools** or PolyBase to query processed data without moving it.

Materialize the most frequently queried fact tables into **dedicated SQL pools** for low-latency access. Build star schemas (facts and dimensions) that optimize Power BI query performance.

```sql
CREATE EXTERNAL DATA SOURCE AzureDataLake
WITH ( LOCATION = 'abfss://processed@mydatalake.dfs.core.windows.net/' );

CREATE EXTERNAL TABLE dbo.Orders
(
    order_id        INT,
    customer_id     INT,
    order_date      DATE,
    order_amt_usd   FLOAT
)
WITH
(
    LOCATION = 'orders/',
    DATA_SOURCE = AzureDataLake,
    FILE_FORMAT = ParquetFileFormat
);

```

### Real-Time Visualization with Power BI

In **Power BI Desktop**, connect directly to Azure Synapse Analytics using **DirectQuery** mode. This ensures dashboards reflect new data immediately rather than waiting for scheduled refreshes.

Import the fact tables from Synapse, build your visualizations, and publish to the Power BI service. Enable **Auto Refresh** intervals as low as one minute for near-real-time dashboard updates, or use push datasets for true real-time streaming scenarios.

1. In Power BI Desktop → **Get Data** → **Azure → Azure Synapse Analytics**
2. Choose **DirectQuery** mode and select the `dbo.Orders` table
3. Configure **Auto Refresh** to 1 minute for live updates

## Security and Governance

Implement **Azure Active Directory (AAD)** authentication across all services to maintain single sign-on and centralized identity management. Store all secrets, connection strings, and certificates in **Azure Key Vault**, allowing Databricks and ADF to retrieve them at runtime via managed identities.

Apply **role-based access control (RBAC)** on ADLS Gen2 containers, Synapse workspaces, and Power BI workspaces to enforce least-privilege access. Monitor pipeline executions and data access through **Azure Monitor** and **Log Analytics** to maintain audit trails for compliance.

## Summary

- **Azure Data Factory** with Self-Hosted Integration Runtime extracts data from on-premise SQL Server into ADLS Gen2 as raw Parquet files.
- **Azure Data Lake Storage Gen2** organizes data hierarchically using date-partitioned paths for efficient incremental processing.
- **Azure Databricks** transforms raw data using Spark and writes ACID-compliant Delta Lake tables to the processed layer.
- **Azure Synapse Analytics** queries Delta Lake via serverless SQL or dedicated pools, serving star schemas to Power BI.
- **Power BI DirectQuery** enables real-time dashboards that refresh within minutes of new data landing.
- **Azure Key Vault** and **Azure Active Directory** provide end-to-end security and secret management across the entire stack.

## Frequently Asked Questions

### What is the latency of this Azure real-time analytics architecture?

The architecture supports **near-real-time latency** ranging from seconds to minutes. Azure Data Factory pipelines can trigger every few minutes, Databricks jobs process incremental data quickly, and Power BI DirectQuery refreshes dashboards within one-minute intervals. For true sub-second latency, replace batch processing with Spark Structured Streaming in Databricks and use Power BI push datasets.

### Why use Delta Lake instead of standard Parquet for the processed layer?

**Delta Lake** adds ACID transaction guarantees, schema enforcement, and time-travel capabilities to Parquet files. This prevents data corruption during concurrent writes, enables rollback to previous data versions, and supports merge operations for slowly changing dimensions. According to the `DataExpert-io/data-engineer-handbook` implementation, Delta Lake is essential for maintaining data quality in production pipelines.

### How do I secure connections between on-premise SQL Server and Azure?

Deploy an **Azure Data Factory Self-Hosted Integration Runtime** on a VM within your on-premise network. This runtime initiates outbound HTTPS connections to Azure, eliminating the need to open inbound firewall ports. Store SQL Server credentials in **Azure Key Vault** and reference them using Key Vault-linked services in ADF, ensuring no secrets reside in pipeline JSON definitions.

### Can I query Delta Lake tables directly from Azure Synapse without copying data?

Yes. **Azure Synapse Serverless SQL pools** can query Delta Lake files directly using the `OPENROWSET` syntax or external tables pointing to the `abfss://` paths. This approach eliminates data duplication and ensures Synapse always reads the latest version of your processed data. For better performance on large fact tables, consider materializing critical datasets into dedicated SQL pool tables using CETAS (CREATE EXTERNAL TABLE AS SELECT).