# Design and Develop Batch Processing

> Chapter 5 of the Courseiva DP-203 curriculum — https://courseiva.com/learn/dp-203/design-and-develop-batch-processing

**Official objective:** 2.1 — Design and develop data processing

## Introduction

Without batch processing, every time you checked last week's sales report, it would show only the sales that happened at the exact moment you pressed refresh — an almost useless snapshot. Batch processing is the mechanism that organises, transforms, and summarises huge amounts of historical data into usable business reports, analytics, and machine learning datasets. For the DP-203 exam, you need to understand how to design and build these processing pipelines using Azure services like Azure Synapse Analytics and Azure Data Factory, because this is how most enterprise data workloads actually operate.

## The Industrial Bakery Analogy

A commercial bakery's warehouse floor, five in the morning.

The bakery doesn't bake one croissant at a time when a customer walks in. Instead, it collects all the orders — from hotels, cafes, and airlines — across the entire week. At midnight on Sunday, the production manager reviews the total: 50,000 croissants, 20,000 baguettes, 10,000 Danish pastries. Only then do the massive mixing machines start. The team spends six hours mixing dough, proofing, shaping, baking, and cooling everything in one continuous run. By noon Monday, every single order is boxed and loaded onto delivery trucks. No one bakes a single pastry on demand.

This is exactly how batch processing works. The data — like orders — piles up over time. A scheduler (like the production manager) triggers the processing job at a fixed interval — hourly, daily, weekly. The system then transforms all the accumulated data at once, producing output files or reports. The bakery doesn't stop to make one baguette for a walk-in customer. Similarly, batch processing doesn't respond to real-time events. It waits, collects, and then processes everything in one efficient, scheduled burst. That schedule, the transformation logic, and the output destination are what you design and develop as the batch processing solution.

## Core explanation

Batch processing is a method of data processing where you collect data over a period of time — hours, days, or even weeks — and then process all of it together in one large job. This is the opposite of real-time or stream processing, which processes each piece of data as it arrives.

Think about how a payroll department works. They don't calculate an employee's pay every second. They wait until the end of the month, gather all the hours worked for every person, apply the correct tax rules, and then run one large calculation that produces everyone's payslips at once. That is batch processing. The data (time sheets) is the input. The transformation (calculating gross pay, deductions, and net pay) is the processing job. The output is the payslips and bank transfers.

In Azure, batch processing typically follows a pattern called ETL, which stands for Extract, Transform, Load. First, you extract data from source systems — maybe a database, a set of log files, or an external API. Then you transform that data: clean it, remove duplicates, aggregate it into summaries, or join two datasets together. Finally, you load the transformed data into a destination system, such as a data warehouse like Azure Synapse Analytics, a data lake like Azure Data Lake Storage Gen2, or a reporting database.

The key components you need to design are:

- The schedule: when does the batch run? Fixed intervals like 'every Sunday at 2:00 AM' or triggered by an event like 'after the source file arrives'.
- The compute: what runs the transformation? In Azure, this is often a pipeline in Azure Data Factory, a notebook in Azure Synapse Analytics, or a script in Azure Databricks. Compute resources need to be provisioned, scaled, and shut down after the job finishes to save money.
- The storage: where do you store the input data, the intermediate data, and the final output? Azure Storage (Blob or Data Lake) is typical for raw and staged data, while a dedicated SQL pool or serverless SQL in Synapse stores the final tables.
- The transformation logic: what exactly do you do to the data? This is the SQL queries, Python scripts, or mapping data flows that define how raw data becomes clean, structured data.
- Error handling and retries: what happens if the batch job fails halfway through? You need to design checkpoints and retry logic so you don't lose all the work or corrupt the destination.

Batch processing exists because it is far more cost-effective and easier to manage than processing every single event individually. For large datasets, processing them in one big job uses fewer compute cycles overall because you can optimise the data access patterns. It also creates deterministic, repeatable results — if you run the same batch job on the same input, you always get the same output. This makes it perfect for regulatory reporting, financial reconciliations, and building training datasets for machine learning models.

The DP-203 exam expects you to know how to choose between different batch processing technologies in Azure. Azure Data Factory is the main orchestration tool — it moves data from source to destination and can trigger transformations. Azure Synapse Analytics provides the platform for running large-scale SQL-based transformations on data stored in a data warehouse or data lake. Azure Databricks offers a more code-focused environment using Python, Scala, or SQL for complex transformations on big data. You must understand when to use each one based on the job's complexity, the skill of the team writing the code, and cost constraints.

## Real-world context

Consider a real retailer, GlobalMart, which operates 500 stores across the UK. Every night, each store uploads a CSV file containing every transaction that occurred that day. The file includes store ID, cashier ID, timestamp, product SKU, quantity, and price. GlobalMart's IT team needs to build a batch processing pipeline that turns those 500 raw files into a single, clean, daily sales summary table that the business analysts can query the next morning.

Here is exactly what happens step by step:

1. The engineering team sets up a folder in Azure Data Lake Storage Gen2 called 'raw-transactions'. Each store's IT system uploads its daily file into a subfolder named by store ID and date.
2. At 2:00 AM every day, a scheduled trigger in Azure Data Factory fires. This trigger starts a pipeline named 'Daily Sales Ingestion'.
3. The first activity in the pipeline is a 'Copy Data' step. It uses a wildcard pattern to copy every file from the 'raw-transactions' container into a staging folder, naming the files with a consistent pattern including the store ID and date. This is the Extract phase.
4. The pipeline then executes a 'Data Flow' activity. The Data Flow reads all the staged files, filters out any rows where the price is negative or the quantity is zero (invalid transactions), and aggregates the data by product SKU, summing the quantity and total revenue. This is the Transform phase.
5. The pipeline writes the aggregated output into a table called 'daily_sales_summary' in a dedicated SQL pool in Azure Synapse Analytics. This is the Load phase.
6. After the load completes, the pipeline sends a notification email to the analytics team confirming that the data is ready. If any step fails — for example, a file is corrupt — the pipeline logs the error and retries the entire job up to three times. If it still fails, an alert goes to the engineers.

The business analysts then connect to the Synapse SQL pool using Power BI and refresh their sales dashboard every morning, seeing the complete picture of yesterday's performance. Without batch processing, the analysts would have to manually download and merge 500 CSV files every day — a task that would take hours and be prone to human error. The batch pipeline saves those hours and guarantees consistency.

The team also needs to handle edge cases. What if a store doesn't upload its file? The pipeline can be designed to use a lookup activity to check whether all expected files for the day are present before running. If one is missing, it waits or sends an alert. The concept of 'watermarking' is also used here — the pipeline keeps track of the last successful run time so it only picks up new files, avoiding reprocessing today's data if the pipeline fails and restarts.

## Exam focus

The DP-203 exam tests batch processing heavily. You can expect scenario-based questions that ask you to choose the correct combination of Azure services, scheduling patterns, and transformation methods for a given business requirement. Here are the exact areas you need to master.

Exam topics and traps:

- Choosing between Azure Data Factory (ADF) and Azure Databricks for a transformation job. The trap: if the transformation is simple (like filtering, aggregating, or joining two tables), ADF's Mapping Data Flows are the correct answer. If the transformation requires complex Python code, custom logic, or machine learning libraries, Databricks is preferred. The exam loves testing whether you know that ADF is the orchestration layer, not the heavy-compute layer.
- Understanding the difference between 'tumbling window' triggers and 'schedule' triggers in ADF. A tumbling window trigger is self-healing — if the window is 1 hour and a job fails, it will automatically retry for that exact hour when the window passes. A schedule trigger does not guarantee self-recovery. This is a common exam trap.
- Knowing when to use PolyBase or COPY INTO statement in Azure Synapse versus using ADF for bulk loading. PolyBase and COPY INTO are faster because they use parallel data loading from the storage layer. ADF, when used for loading, goes through the ADF runtime, which can be slower for massive datasets. The exam will ask you to choose the fastest loading method.
- The difference between an Azure Synapse Analytics serverless SQL pool and a dedicated SQL pool. Serverless is for ad-hoc, on-demand queries on data in the data lake (pay per query). Dedicated is for scheduled, consistent, high-performance batch loads (pay for provisioned resources). The exam will give you cost or performance constraints and expect you to pick the correct one.
- Watermarking and incremental loads. The exam expects you to know that for efficient batch processing, you should not reprocess all data every time. Instead, you use a 'high water mark' — a timestamp or a numeric value — to track the last processed record. Only data newer than that watermark is extracted. Questions will ask: 'Which strategy reduces processing time for daily batches?' The answer is incremental loading using a watermark column.
- Error handling patterns. The exam tests the 'upsert' pattern (update existing records and insert new ones) using ADF's built-in 'alter row' transformation within a data flow. The trap is when candidates confuse 'upsert' with 'delete and reload', which is less efficient.

Key definitions to memorise:

- Batch window: the time interval within which a batch job must complete.
- SLA: Service Level Agreement — the promised completion time or uptime.
- Checkpointing: saving the progress of a long-running job so it can resume from the last known good state after a failure.
- Replay: rerunning a batch job to produce historical results (common in financial audits).

The exam will also include multiple-choice questions where you must identify which step of the ETL pipeline a given activity belongs to: Is it Extract, Transform, or Load? Memorise that ADF's 'Copy Data' is always Extract. Synapse Data Flows or SQL transformations are Transform. Writing to a Synapse table or Azure SQL Database is Load.

## Step by step

1. **Source Data Discovery and Profiling** — Identify all source systems and their data formats — CSV, JSON, Parquet, or relational tables. Profile the data to understand its structure, quality, and update frequency. This step determines whether you need to handle delimiters, nested columns, or missing values before the batch job runs.
2. **Define the Batch Schedule and Dependency** — Decide when the batch runs: hourly, daily, weekly, or triggered by an event (like a new file landing in storage). Define dependencies — for example, the batch for today cannot start until yesterday's batch has completed successfully. This step prevents overlapping jobs and data corruption.
3. **Design the Extract Pattern** — Implement the extraction: full load (all data every time) or incremental load (only new or changed data since last run). For incremental loads, you establish a watermark column — a timestamp or an incrementing ID — and store the last run's watermark value. This step is critical for performance optimisation.
4. **Implement the Transformation Logic** — Write the code or configure the data flow that cleans, aggregates, joins, and enriches the data. This can be done via ADF Mapping Data Flows, a Databricks notebook, a Synapse SQL script, or Stored Procedures. The choice depends on complexity and team skills. Always test with a sample dataset before running at full scale.
5. **Define the Load Mechanism and Target Schema** — Specify the destination — a table in Azure Synapse, a set of Parquet files in Data Lake, or a SQL Database. Choose the loading strategy: insert, upsert, or delete-and-reload. For large volumes, use PolyBase or COPY INTO for high-speed parallel loading into Synapse. Validate row counts and data integrity after loading.
6. **Configure Monitoring, Alerts, and Error Handling** — Set up logging of every pipeline run: start time, end time, rows processed, and any errors. Configure alerts in Azure Monitor to notify the team if a batch fails. Implement retry logic (e.g., three retries with exponential backoff) and a dead-letter folder for files that repeatedly fail processing. This step ensures your pipeline is production-grade.

## Comparisons

### Azure Data Factory Mapping Data Flow vs Azure Databricks Notebook

**Azure Data Factory Mapping Data Flow:**
- No-code or low-code configuration using drag-and-drop transformations
- Limited to relatively simple transformations (filters, aggregations, joins) – no custom Python libraries allowed
- Runs on ADF compute – scales but can be slower for very large datasets

**Azure Databricks Notebook:**
- Full code control with Python, Scala, SQL, or R – can import any library

### Azure Synapse Dedicated SQL Pool vs Azure Synapse Serverless SQL Pool

**Azure Synapse Dedicated SQL Pool:**
- Provisioned compute – you pay for allocated resources regardless of usage
- Designed for consistent, predictable high-performance batch loads
- Best for production workloads with fixed schedules and high throughput

**Azure Synapse Serverless SQL Pool:**
- On-demand, serverless compute – you pay only for the data scanned per query
- Ideal for ad-hoc queries and exploratory analysis on data in the data lake
- Not suitable for repeated scheduled batch loads due to lack of reserved resources

### Incremental Load vs Full Load

**Incremental Load:**
- Only extracts new or changed records since the last run using a watermark
- Faster and cheaper because it moves less data per run
- Requires a reliable watermark column (e.g., LastModifiedDate) in the source

**Full Load:**
- Extracts the entire dataset every run
- Simpler to implement – no watermark logic needed – but very expensive and slow
- Only practical for very small datasets or one-time migrations

## Diagram

_Architecture flow of a typical batch processing pipeline from data lake ingestion through transformation options (ADF, Databricks, Synapse) to final reporting._

```mermaid
flowchart TD
    A[Source Files in Data Lake] --> B[Azure Data Factory Pipeline]
    B --> C{Choose Transformation}
    C -->|Simple Transformations| D[ADF Mapping Data Flow]
    C -->|Complex Logic| E[Azure Databricks Notebook]
    C -->|SQL-based| F[Azure Synapse SQL Script]
    D --> G[Transformed Data in Staging]
    E --> G
    F --> G
    G --> H[Load into Synapse Dedicated SQL Pool]
    H --> I[Business Reporting in Power BI]
```

## Common misconceptions

- **Misconception:** Batch processing and stream processing are the same thing — you just run them at different speeds. **Reality:** They are architecturally different. Batch processing operates on bounded, complete datasets collected over time. Stream processing operates on unbounded data as it arrives, one event at a time. The tools (Azure Stream Analytics vs. Azure Synapse pipelines), storage patterns, and error handling are fundamentally different. (The word 'processing' sounds similar, so beginners assume the only difference is the time interval. They don't realise that stream processing requires state management, exactly-once semantics, and different checkpointing mechanisms.)
- **Misconception:** You should always use Azure Databricks for all batch processing because it is the most powerful tool. **Reality:** Azure Databricks is powerful but overkill for many scenarios. If the transformation is a simple SQL aggregation, Azure Data Factory Mapping Data Flows or a Synapse SQL pool is cheaper, easier to maintain, and more appropriate. Databricks is for heavy code-based transformations with machine learning or complex Python logic. (Marketing and online tutorials often show Databricks as the go-to solution. Beginners mistake 'most capable' for 'always correct'. The exam tests your ability to match tool to requirement, not to pick the fanciest option.)
- **Misconception:** ETL (Extract, Transform, Load) is the only pattern for batch processing in Azure. **Reality:** There is also ELT (Extract, Load, Transform), where you load raw data into the destination first and then transform it using the destination's compute power (e.g., Synapse SQL or Databricks). This is often faster for large datasets because you stage the data in the target system before cleaning it. (The term ETL is much more commonly used in older textbooks and job descriptions. Beginners don't realise that modern cloud data warehouses (like Synapse) are powerful enough to do the transform after loading, which avoids moving data twice.)
- **Misconception:** Once a batch pipeline is scheduled, it runs exactly at that time every day, no matter what. **Reality:** Batch pipelines can be skipped, paused, or fail. They also need to account for idle times, public holidays, and upstream data availability. Real pipelines often include dependency checks, 'wait' activities, and conditional logic to decide whether to run. (This comes from thinking of code as deterministic and simple. In the real world, external data sources are unreliable, networks fail, and file formats change. Beginners assume the scheduler is a clock that always ticks, but a scheduled trigger without proper validation logic will produce bad outputs silently.)

## Key takeaways

- Batch processing collects data over a period before processing it all at once, unlike stream processing which handles events individually in real time.
- The ETL pattern (Extract, Transform, Load) is the foundation of most batch processing in Azure, but ELT is also valid when the destination compute can handle transformations.
- Azure Data Factory is the orchestration service that schedules and runs batch pipelines; it does not do heavy transformations itself — those are delegated to Mapping Data Flows, Databricks, or Synapse SQL.
- Incremental loading using a watermark column is essential for efficient batch processing — never reprocess the entire dataset every run.
- Tumbling window triggers in ADF are self-healing and ensure every time window is processed exactly once, even on failure; schedule triggers are not self-healing.
- Choose Azure Synapse dedicated SQL pool for predictable, high-performance batch loads and serverless SQL pool for ad-hoc, cost-per-query scenarios against data lake files.
- Always design batch pipelines with error handling, retries, and notifications — a silent failure in a nightly batch job can corrupt an entire week's business reports.
- PolyBase and COPY INTO load data faster into Synapse than ADF Copy Data because they bypass the ADF runtime and use the storage layer's parallel processing.

## FAQ

**What is the difference between batch processing and stream processing in Azure?**

Batch processes a group of collected data at once on a schedule, like monthly payroll. Stream processes each event as it arrives, like a credit card fraud detection system that flags transactions in real time. In Azure, you use Azure Data Factory or Synapse for batch, and Azure Stream Analytics or Event Hubs with Azure Functions for streaming.

**Why do I need a watermark column in batch processing?**

A watermark column allows you to only extract new or changed records since the last batch run, instead of reprocessing the entire dataset. This dramatically reduces processing time, compute cost, and resource usage. For example, if your source table has a 'LastModifiedDate' column, you can use it as a watermark to fetch only records changed in the last hour.

**Should I use Azure Data Factory or Azure Databricks for batch transformation?**

Use Azure Data Factory when your transformations are simple: filter, aggregate, join, or rename columns. Use Azure Databricks when you need complex logic, Python or Scala scripts, machine learning models, or when data volumes exceed what ADF Mapping Data Flows can handle efficiently. ADF always orchestrates, but Databricks can do the heavy compute.

**What is a tumbling window trigger in Azure Data Factory?**

A tumbling window trigger is a time-based trigger that processes fixed, non-overlapping time intervals — for example, every hour from 12:00 to 13:00, then 13:00 to 14:00. Crucially, if a job fails for a particular window, the trigger automatically retries that exact window. This makes it self-healing, unlike a simple schedule trigger which may skip a failed interval.

**How do I load data fast into Azure Synapse Analytics from a data lake?**

Use PolyBase or the COPY INTO command. These allow Synapse to read data directly from the storage layer using parallel distribution, bypassing the ADF runtime. For example: 'COPY INTO dbo.Sales FROM 'https://mydatalake.blob.core.windows.net/raw/sales/*.csv' WITH (FILE_TYPE = 'CSV');'. This is much faster than using Azure Data Factory's Copy Data activity.

**What happens if my batch pipeline fails halfway through?**

Design your pipeline with checkpoints and a retry policy. In Azure Data Factory, you can set the 'Retry' property on activities (commonly 3 times). You can also use a tumbling window trigger so the pipeline reruns for the exact failed interval. For data integrity, avoid partial loads by using a staging table: load into a temp table first, then switch the production table only after a successful load.

## Check your understanding

1. **What is the primary purpose of a watermark column in an incremental batch load?**

   Answer: It tracks the last processed record (e.g., the 'LastModifiedDate' of the last row) so that on the next run, only records newer than that value are extracted, avoiding reprocessing of the entire dataset.

2. **Which Azure service is best for orchestrating a batch pipeline that involves multiple steps and dependencies?**

   Answer: Azure Data Factory. It provides triggers, pipeline activities, and control flow for sequencing tasks, branching, and error handling across services like Databricks, Synapse, and storage.

3. **What is the difference between a tumbling window trigger and a schedule trigger in Azure Data Factory?**

   Answer: A tumbling window trigger is self-healing – it automatically retries a failed window interval – and guarantees each time window is processed exactly once. A schedule trigger does not retry failed windows and can skip intervals.

4. **In the ETL pattern, which activity in Azure Data Factory corresponds to the 'Transform' step?**

   Answer: The Mapping Data Flow activity or a Notebook/Databricks activity represents the Transform step, where data is cleaned, aggregated, or joined before being loaded into the destination.

5. **When should you use PolyBase or COPY INTO instead of Azure Data Factory's Copy Data activity for loading into Azure Synapse?**

   Answer: When you need the fastest possible load speed for large datasets, because PolyBase and COPY INTO bypass the ADF runtime and use the storage layer's parallel distribution to load data directly into Synapse.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/dp-203/design-and-develop-batch-processing