# Introduction to Azure Data Engineering

> Chapter 1 of the Courseiva DP-203 curriculum — https://courseiva.com/learn/dp-203/introduction-to-azure-data-engineering

**Official objective:** 1.1 — Design and implement data storage

## Introduction

Data engineering solves the problem of turning messy, raw data from many sources into clean, organised, and usable information. For someone studying for the DP-203 exam, understanding the core purpose of Azure Data Engineering is the foundation every other concept builds upon, from storage to processing to analytics. This chapter bridges the gap between a complete beginner's understanding and the professional role of designing how data gets captured, stored, and prepared for analysis in the real world.

## The Apartment Building Mail Room Analogy

A mail room is the central hub for all incoming and outgoing parcels in a large apartment building. Without it, every resident would have to deal directly with every courier service, every package would be a negotiation between the sender and the specific individual, and the building would be chaotic.

In this analogy, the apartment building is a company. The individual residents are the company's various applications—sales, finance, customer service—each needing data to function. The mail room itself is the cloud storage infrastructure in Azure. The courier services (FedEx, UPS, postal service) are the different data sources: a website generating user clicks, a mobile app sending location data, a legacy database storing customer records.

The mail room manager works like an Azure Data Engineer. They don't just receive parcels; they sort them (categorise data by type—sales, logs, user profiles), log each parcel in a ledger (create metadata about the data, like when it arrived and where it came from), and place them in the correct PO box (the right storage location—a hot folder for immediate use, a cooler basement for historical archives). If a resident requests a package from three months ago, the manager knows where to find it. If a new resident moves in, the manager sets up a new PO box. The manager ensures that no single resident can just dump trash in the hallways (raw, unsorted data) and that the mail room has rules about who can pick up which packages (data access permissions). The manager also scales the operation: during the holidays when millions of packages arrive (a massive data ingestion event), they hire temporary staff and rent extra storage space (auto-scaling compute and storage resources).

## Core explanation

Azure Data Engineering is the discipline of designing, building, and maintaining the systems that handle an organisation's data from its point of origin to the point where it can be analysed. A beginner often confuses a data engineer with a data analyst or data scientist. The engineer builds the pipeline—the plumbing and infrastructure—while the analyst and scientist use the water that comes out of the tap.

At its heart, data engineering in Azure is about managing the lifecycle of data. This lifecycle begins with ingestion, the process of bringing data into the Azure cloud. Data can arrive in many forms: structured data from a sales database (think rows and columns, like a perfect spreadsheet), semi-structured data from a web server log (JSON or XML files that have some structure but allow for flexibility), or unstructured data from images, videos, and social media posts (no predefined format).

Once ingested, the data must be stored. Azure provides several options, each designed for a specific purpose:

- Azure Blob Storage is like a giant cloud-based hard drive for any kind of file—videos, backups, text files. It is object storage, meaning it stores each piece of data as a blob (Binary Large Object) with metadata.
- Azure Data Lake Storage Gen2 builds on Blob Storage but adds a file system hierarchy. It is designed for big data analytics, making it easier for tools like Apache Spark to read massive datasets.
- Azure Cosmos DB is a NoSQL database. It is ideal for data that changes frequently, like product catalogs or gaming leaderboards, because it offers low-latency access from anywhere in the world.
- Azure Synapse Analytics (formerly SQL Data Warehouse) is a big data analytics service that combines data warehousing, which is a central repository for structured, cleaned data optimised for reporting, with big data processing. This is where an analyst would run complex queries to find trends.

After storage comes processing, also known as ETL (Extract, Transform, Load). The 'transform' stage is the most critical. Raw data is rarely clean enough for analysis. It might have missing values, inconsistent date formats, or duplicate records. An Azure Data Engineer uses services like Azure Data Factory (a cloud-based ETL service for orchestrating data movement) or Azure Databricks (a platform for running Spark jobs that cleanses and transforms data through code) to perform these operations.

The concept of a 'data lake' is central. A data lake stores all data in its native, raw format until it is needed. This is different from a traditional 'data warehouse', which requires you to structure the data before you store it (schema-on-write versus schema-on-read). An Azure Data Engineer designs how these two systems interact, often creating a one-way flow from a landing zone in the data lake, through a curated zone where data is cleaned, and into a warehouse for structured reporting.

A key thing to understand is that data engineering is not just about moving files. It is about ensuring data quality, security, and governance. Azure provides tools like Microsoft Purview (for data discovery and governance—knowing what data you have, where it came from, and who can use it) and Azure Policy (for enforcing rules, like 'all storage accounts must be encrypted'). The data engineer configures these to ensure compliance with regulations like GDPR. The exam tests these concepts not as abstract ideas, but as practical choices you must make based on a scenario's specific requirements for latency, cost, scale, and security.

## Real-world context

A retail company, 'GlobalMart', wants to improve its inventory forecasting. Currently, the process is manual: a manager looks at a spreadsheet at the end of each week and guesses how much stock to order next month. This results in frequent overstock (wasted money) or stockouts (lost sales). The company decides to hire Azure Data Engineers to build a modern data solution.

What does the engineer actually do? They do not write the forecasting algorithm themselves. Instead, they create the infrastructure that collects, stores, and prepares the data the forecasting algorithm needs.

First, the engineer identifies the data sources. GlobalMart has:
- A point-of-sale system that generates transaction logs (every time a pair of jeans is sold, a JSON record is created).
- A website that tracks user clicks and cart additions, stored in a NoSQL database.
- Supplier delivery schedules, emailed as CSV attachments.
- Weather data from an external API, because temperature affects coat sales.

The engineer uses Azure Data Factory to build a pipeline. Step one is ingestion: every 15 minutes, a Data Factory pipeline runs and calls the API for weather data, pulls the CSV from an email attachment via a connector, and reads the new transaction logs from the on-premises server. Each of these is copied into Azure Data Lake Storage Gen2. The data lands in a 'raw' folder, exactly as it arrived—no changes.

Next, the engineer builds another pipeline that runs nightly. This pipeline uses Azure Databricks to perform the transformation. The Python code in Databricks:
- Removes duplicate transaction records (e.g., if a sale was recorded twice due to a network error).
- Standardises date formats across all sources (US format MM/DD/YYYY vs European DD/MM/YYYY into ISO 8601 YYYY-MM-DD).
- Joins the sales data with the product catalogue to add product categories.
- Handles missing weather data by filling in the average temperature for that region.

The cleaned data is then written into a 'curated' folder in the Data Lake. From there, it is loaded into Azure Synapse Analytics, specifically into a star-schema design (a central fact table with dimension tables) that is optimised for the forecasting model.

The engineer then configures access control. The finance team can see aggregated cost data in Power BI, but not the raw individual transaction records. The data scientist building the forecast model gets a dedicated schema with full read access. This is managed using Role-Based Access Control (RBAC) at both the storage account and the Synapse workspace level.

The final step is monitoring. The engineer sets up Azure Monitor alerts to notify the team if a pipeline fails, if data volume drops unexpectedly (suggesting a broken source), or if processing time exceeds a threshold. A dashboard in the Azure portal shows the status of every pipeline, the health of each storage system, and query performance in Synapse.

## Exam focus

The DP-203 exam (Designing and Implementing a Data Engineering Solution on Azure) tests your ability to choose the correct Azure service for a given scenario. The questions are rarely 'What is a data lake?' but rather 'You need to store raw video files for batch processing. Which service should you use?' The answer is almost always Azure Data Lake Storage Gen2 or Blob Storage, not Azure SQL Database.

Key concepts that appear repeatedly:
- **PolyBase and COPY INTO**: These are commands used in Azure Synapse Analytics to quickly load data from external storage (like Blob Storage) without moving it first. The exam loves to test when to use these versus using Data Factory.
- **Partitioning strategies**: In Synapse or Databricks, partitioning divides data into smaller segments (e.g., by date). The exam tests the impact of over-partitioning (too many small files) and under-partitioning (huge unmanageable files).
- **Star schema vs. snowflake schema**: You must know the difference and when to use each for reporting. Star schema is denormalised (fewer joins, faster reads); snowflake is normalised (less storage, more joins).

Common traps the exam sets:
- **Confusing storage layers**: They will describe a scenario where you need low-latency access to a small dataset (Azure Cosmos DB is correct) versus large-scale analytical queries (Synapse is correct). The trap is that both use the word 'database', but their purposes are completely different.
- **Ignoring the 'hot/cold' tiers**: Blob Storage access tiers (Hot, Cool, Archive) are a frequent trick. If the scenario says 'access data monthly for 10 years', the answer is often Cool tier, not Archive, because Archive takes hours to retrieve.
- **Security misconfigurations**: The exam often presents a scenario where a managed identity (an automatically managed identity in Microsoft Entra ID for an Azure service) should be used instead of access keys or shared access signatures (SAS). If you see a connection string hardcoded in a scenario, that is almost always the wrong pattern.

Patterns for correct answers:
- When data needs transformation before loading, the answer is rarely 'copy data directly to Synapse'. Instead, use Data Factory with a Data Flow, or use Azure Databricks.
- When the question involves handling streaming data (real-time events), the answer involves Azure Event Hubs (to capture the stream) and Azure Stream Analytics (to process it on the fly).
- When the question talks about data lineage or cataloguing, the answer is Microsoft Purview.

You must also memorise the strict definitions of 'batch processing' (process large volumes at once, e.g., nightly) versus 'stream processing' (process each event as it arrives, e.g., fraud detection). The exam will explicitly test your ability to select the processing paradigm based on latency requirements.

## Step by step

1. **Identify Data Sources** — The first step is cataloguing every system that produces data your business needs. This includes transactional databases, application logs, IoT devices, external APIs, and flat files. You document the data's format (structured, semi-structured, unstructured), volume, and velocity (how fast it arrives). This step matters because the choice of ingestion tool (Azure Data Factory, Event Hubs, or Azure Synapse Pipelines) depends entirely on the source type.
2. **Design the Ingestion Strategy** — You decide how to bring the data into Azure. For batch data (nightly sales reports), you use Azure Data Factory with a schedule trigger. For streaming data (real-time IoT sensor readings), you use Azure Event Hubs to capture the stream. You also choose the landing storage: typically Azure Data Lake Storage Gen2 for raw data. This matters because a poorly designed ingestion strategy leads to data loss (e.g., not capturing a stream correctly) or overwhelming the system with too many tiny files.
3. **Build the Transformation Pipeline** — Raw data is rarely usable. You use Azure Databricks (for code-intensive transforms) or Azure Data Factory Mapping Data Flows (for visual transforms) to clean and structure the data. Common operations include removing duplicates, validating data types, handling missing values, and joining datasets. This step is the most time-consuming part of the job because data quality directly impacts the reliability of downstream reports and machine learning models.
4. **Load into the Serving Layer** — The cleaned data is loaded into a system optimised for consumption. For structured analytics and reporting, you load into Azure Synapse Analytics (data warehouse). For real-time dashboards, you might load into Azure Cosmos DB. The data is often organised into a star schema. Getting this step wrong means the analysts and data scientists cannot efficiently query the data, negating the value of the pipeline.
5. **Implement Security and Monitoring** — You configure RBAC roles (e.g., 'Storage Blob Data Reader' for analysts who need read-only access) and networking rules (e.g., allow only your corporate VPN to reach the data lake). You set up Azure Monitor alerts for pipeline failures, query latency issues, and resource utilisation. You also configure Microsoft Purview to scan the data lake and catalogue its contents. This is the step that ensures the data infrastructure is both trusted by the business and stays compliant with regulations like GDPR.

## Comparisons

### Azure Data Lake Storage Gen2 vs Azure Blob Storage

**Azure Data Lake Storage Gen2:**
- Designed for big data analytics with a hierarchical file system
- Supports POSIX-like access permissions for fine-grained control
- Integrated with Azure Synapse Analytics and Databricks for direct querying

**Azure Blob Storage:**
- Flat namespace for general object storage (videos, backups)
- Access control via container-level permissions only
- Used for archival and general-purpose file storage, not optimised for Spark

### Azure Synapse Analytics (Dedicated SQL Pool) vs Azure Cosmos DB

**Azure Synapse Analytics (Dedicated SQL Pool):**
- Relational database optimised for large-scale analytical queries (OLAP)
- Uses schema-on-write with predefined table structures (star schema)
- Ideal for complex aggregations and reporting over massive datasets

**Azure Cosmos DB:**
- NoSQL database optimised for high-throughput transactional workloads (OLTP)
- Schema-agnostic with automatic indexing for low-latency reads/writes
- Ideal for real-time applications and globally distributed data access

### Batch Processing vs Stream Processing

**Batch Processing:**
- Processes data in large, scheduled chunks (e.g., nightly runs)
- Higher latency (minutes to hours) but optimised for low-cost operations
- Uses Azure Data Factory or Azure Databricks with scheduled triggers

**Stream Processing:**
- Processes each event as it arrives in real-time
- Sub-second latency for live dashboards and alerts
- Uses Azure Event Hubs and Azure Stream Analytics

### Schema-on-Read (Data Lake) vs Schema-on-Write (Data Warehouse)

**Schema-on-Read (Data Lake):**
- Data is stored in native format without transformation first
- Schema applied only when data is read for analysis
- Highly flexible for unplanned use cases; stores raw JSON, CSV, Parquet

**Schema-on-Write (Data Warehouse):**
- Data must conform to a predefined schema before storage
- ETL process enforces structure upfront (data quality at entry)
- Optimised for fast, predictable query performance on structured data

## Diagram

_A typical end-to-end Azure data engineering pipeline showing the flow from raw data sources through ingestion, storage, transformation, serving, and final consumption._

```mermaid
flowchart TD
    A[Raw Data Sources: SQL DB, Logs, IoT, APIs] --> B[Ingestion Layer: Azure Data Factory or Event Hubs]
    B --> C[Storage Layer: Azure Data Lake Storage Gen2]
    C --> D[Transform Layer: Azure Databricks or ADF Data Flows]
    D --> E[Serving Layer: Azure Synapse Analytics or Cosmos DB]
    E --> F[Consumption: Power BI, ML Models, Apps]
    style A fill:#e1f5fe
    style B fill:#fff3e0
    style C fill:#e8f5e9
    style D fill:#ffe0b2
    style E fill:#f3e5f5
    style F fill:#ffccbc
```

## Common misconceptions

- **Misconception:** Data engineering is the same as database administration (DBA). **Reality:** A DBA manages a single database system (like SQL Server), tuning queries and backups. A data engineer designs entire pipelines that often move data between multiple systems (from a SQL database to a data lake to a data warehouse) and also manages the code that transforms the data. (Both roles handle data, and a beginner may see a DBA as the only 'data job' in IT. The DP-203 exam focuses entirely on the pipeline and orchestration side, not on transactional database tuning.)
- **Misconception:** You always need to transform data before storing it. **Reality:** In a modern data lake architecture (schema-on-read), you store raw data exactly as it arrives. Transformation happens later when someone reads it for analysis. This gives you flexibility to use the data for purposes you hadn't anticipated. (Older systems required schema-on-write (like a data warehouse), so beginners assume that is the only way. The exam tests both approaches and expects you to know that a data lake stores the raw original.)
- **Misconception:** Azure Blob Storage and Azure Data Lake Storage Gen2 are completely different services. **Reality:** ADLS Gen2 is built on top of Blob Storage. It adds a hierarchical namespace and POSIX-like permissions. They share the same underlying infrastructure, but ADLS Gen2 is optimised for analytics engines like Spark. (Microsoft markets them as separate services, so beginners think they are unrelated. The exam tests the fact that they are compatible but ADLS Gen2 has extra features for big data.)
- **Misconception:** If data needs to be secure, you should always encrypt it manually using your own keys. **Reality:** Azure encrypts data at rest by default using Microsoft-managed keys. The decision for you is whether to use a customer-managed key (CMK) for extra control. Manual encryption of individual fields is a specialised technique used only for highly sensitive data like credit card numbers. (Security paranoia leads beginners to think default encryption isn't enough. The exam expects you to know the standard approach is automatic server-side encryption, and CMK is the exception, not the rule.)
- **Misconception:** You need to write all ETL code in SQL to be a real data engineer. **Reality:** Modern Azure data engineering heavily uses Python, Scala, and .NET for transformation logic, especially in Azure Databricks and Azure Synapse Notebooks. SQL is used for querying the warehouse, but the heavy lifting in the pipeline is typically code-based. (Older ETL tools were SQL-based (SSIS), so beginners associate the role with SQL. The DP-203 exam includes scenarios that require coding in PySpark or Scala, not just SQL.)

## Key takeaways

- Data engineering builds the infrastructure that ingests, stores, transforms, and secures data, separate from the analysis or modelling that happens afterwards.
- Azure Data Lake Storage Gen2 is the recommended storage service for big data analytics because it combines object storage with a file system hierarchy and POSIX permissions.
- The ETL process (Extract, Transform, Load) is the core job of a data engineer, with the 'transform' stage being the most critical for cleaning and preparing raw data.
- Schema-on-read (store raw, process later) is the modern approach for data lakes, while schema-on-write (structure before storing) is used for traditional data warehouses.
- Microsoft Purview is the tool for data governance and lineage, allowing organisations to document what data exists and where it came from.
- Security for data storage in Azure relies on default encryption at rest, managed identities for authentication, and RBAC for authorisation, not manual hardcoded keys.
- Batch processing handles large volumes of data on a schedule (e.g., nightly), while stream processing handles events as they happen (e.g., fraud alerts), and each has different Azure services optimised for it.
- Azure Data Factory is the orchestration tool for building pipelines that move and transform data across the entire Azure data platform.

## FAQ

**Do I need to know Python to pass the DP-203 exam?**

Yes, you need a basic understanding of Python, especially PySpark syntax for Azure Databricks. The exam includes questions about code snippets that perform transformations, and you must recognise patterns like reading a DataFrame and applying a filter.

**What is the difference between Azure Data Lake Storage and Azure Blob Storage?**

Blob Storage is general-purpose object storage for any file type. Azure Data Lake Storage Gen2 is built on Blob Storage but adds a hierarchical file system and POSIX permissions, making it optimised for big data analytics tools like Spark. Use ADLS Gen2 for analytics workloads.

**What is the most important service for ETL in Azure?**

Azure Data Factory is the primary ETL orchestration service. It allows you to create pipelines that copy data from source to destination and perform transformations using Mapping Data Flows or by calling external compute like Azure Databricks.

**Do I need to build a data warehouse for every data engineering project?**

No. If your use case requires only raw data storage for exploratory analysis, a data lake (Azure Data Lake Storage Gen2) may be sufficient. A data warehouse (Azure Synapse Analytics) is needed when you have structured data that must be queried rapidly for reporting and dashboards.

**How does data security work in Azure data storage?**

Azure encrypts all data at rest by default using server-side encryption. You control access using Microsoft Entra ID authentication and RBAC roles. For additional security, you can use customer-managed keys or private endpoints to keep traffic within the Azure network.

**What is a managed identity in Azure?**

A managed identity is an automatically managed service principal in Microsoft Entra ID that allows an Azure resource (like a Data Factory) to authenticate to other Azure services (like Blob Storage) without storing any credentials in code or configuration files.

## Check your understanding

1. **Which Azure storage service should you use to store raw streaming data from IoT sensors before processing it in a batch pipeline?**

   Answer: Azure Data Lake Storage Gen2. It is the standard landing zone for raw data in big data architectures because it stores any file format at scale and integrates with analytics tools.

2. **What is the primary difference between a data lake and a data warehouse in Azure?**

   Answer: A data lake stores raw data in its native format using schema-on-read, while a data warehouse (Azure Synapse Dedicated SQL Pool) requires structured data loaded via schema-on-write for fast analytical queries.

3. **What Azure service would you use to orchestrate a nightly ETL pipeline that copies data from an on-premises SQL Server to Azure Blob Storage?**

   Answer: Azure Data Factory. It provides built-in connectors for on-premises and cloud sources, allows you to schedule triggers, and can orchestrate multiple activities in a single pipeline.

4. **How do you authenticate an Azure Data Factory pipeline to connect to Azure Data Lake Storage without storing a password?**

   Answer: Use a managed identity. Assign a system-assigned or user-assigned managed identity to the Data Factory, and grant that identity the appropriate RBAC role on the Data Lake Storage account.

5. **You need to provide a data scientist with access to explore raw data in a data lake, but she should not be able to delete files. What type of access control should you use?**

   Answer: Use Role-Based Access Control (RBAC) by assigning the 'Storage Blob Data Reader' role to the data scientist's identity (user or service principal) at the storage account or container scope.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-203/introduction-to-azure-data-engineering