Microsoft Azure Learning Journey

OLTP, OLAP, ETL, ELT: Foundations for Azure Data Engineering

Choose a data shape, storage design, and processing path from the decisions a product or analyst needs to make.

5 min read · 885 words
Microsoft AzureData EngineeringAnalyticsData GovernanceCloud Computing
OLTP, OLAP, ETL, ELT: Foundations for Azure Data Engineering cover image

A database that serves an application’s transactions has different needs from a platform that scans years of history for business trends. Moving operational data into analytics without a clear contract can create stale dashboards, broken pipelines, duplicated records, and an expensive lake nobody trusts. Data engineering makes the path from source to useful, governed data repeatable.

What you’ll learn

Prerequisites

Match the workload to the data system

OLTP systems serve short, concurrent transactions such as creating an order or updating an account. They prioritize correctness, low-latency reads and writes, and transactional consistency. OLAP systems analyze larger sets of historical or aggregated data. They prioritize efficient scans, joins, and aggregations across many records.

Operational databases are not automatically the right place for dashboards and long-running analytical queries. A separate analytical path can isolate workload contention, preserve historical snapshots, and combine sources. That path needs a freshness target and a reconciliation process so the analytical copy does not silently diverge.

Pattern Where transformation happens Typical fit
ETL Before loading into the target Curated outputs, restricted target schemas, or transformation environments with the needed controls
ELT After landing data in the target platform Lake or warehouse platforms with scalable compute and a need to retain source-shaped data
Batch On a schedule or event-triggered window Periodic reports, daily snapshots, and large bounded files
Streaming Continuously as events arrive Low-latency operational analytics and event-driven processing

These are design choices, not mutually exclusive architectures. A platform can ingest streaming events into a lake and also run a daily batch reconciliation. ETL does not imply “secure,” and ELT does not imply “raw data is safe to query.” Both need access control, validation, and lineage.

Pick a storage model

Azure data platforms can combine ADLS Gen2, Azure Data Factory, Azure Databricks, Synapse, and Microsoft Fabric. Choose each service for a specific need, not because every architecture diagram has one of each.

Layer data and define its contract

A common medallion model separates:

  1. Bronze/raw: source-aligned records with arrival metadata and minimal destructive transformation.
  2. Silver/validated: typed, deduplicated, conformed records with quality checks and documented corrections.
  3. Gold/serving: purpose-built datasets for a report, product feature, or analytical consumer.

Layer names are conventions, not a guarantee of quality or governance. State who may write and read each layer, how long data remains, and how corrections are represented. Retain source identifiers, event times, ingestion times, and pipeline versions so operators can reconcile a derived dataset.

A data contract records the schema and meaning a producer promises and consumers rely on: field names and types, nullability, units, keys, event-time semantics, change compatibility, sensitivity classification, and freshness target. Version changes, test backward compatibility, and agree on how breaking changes are announced.

Engineer for reruns and change

Every pipeline should identify its input window or watermark, output partition, and retry behavior. Make writes idempotent where possible: rerunning the same input should not create a second logical copy. Track source and output counts, rejected records, schema version, and execution ID. Quarantine invalid rows with a reason instead of silently dropping them.

Schema evolution is a product decision. Additive nullable columns may be compatible for some consumers; renames, type changes, and changed business definitions often are not. Validate data before promoting a new schema, publish lineage, and alert on freshness and completeness—not just whether a job exited with code zero.

Make the tradeoffs explicit

Define query latency and freshness requirements, data sensitivity, recovery needs, concurrency, expected volume, and budget. A streaming path is not automatically better if a five-minute batch meets the business need. Retaining every raw event indefinitely may help reprocessing, but increases storage, access, and privacy obligations. Measure total cost across ingestion, compute, storage transactions, scans, egress, and support.

✅ Checkpoint: Sketch one source-to-consumer path. Label its OLTP/OLAP boundary, ETL/ELT step, storage layers, freshness objective, quality gate, owner, and safe rerun key.

Next: Build a data lake with ADLS Gen2.

Series navigation: Previous: Phase 8 operations · Current: Phase 9, Part 1 · Next: ADLS Gen2

References

Share this article

LinkedIn post draft