Data roles, OLTP and analytical workloads
Data work is split into roles and into workload types. The exam asks you to match a scenario to the correct one.
The three main roles
| Role | Focus | Typical tasks |
|---|---|---|
| Database administrator | Operate databases | Backup and restore, security, performance, availability |
| Data engineer | Build data platforms | Design stores, build pipelines, clean and integrate data |
| Data analyst | Find insight | Model data, build reports and dashboards in Power BI |
A quick memory trick: the DBA keeps it running, the engineer moves and shapes it, the analyst explains it.
OLTP: transactional workloads
Online Transaction Processing supports day-to-day operations such as orders, payments and bookings.
- Heavy writes and moderate reads, in small, fast operations.
- Normalized data to avoid duplication.
- Schema on write.
- Relies on ACID transactions.
OLAP: analytical workloads
Online Analytical Processing answers questions over large volumes of history: sales by region over five years.
- Light writes (loads), heavy reads of large scans and aggregations.
- Often denormalized (star schema) for fast queries.
- Lives in a data warehouse, lake or lakehouse.
The main purpose of a data warehouse is to answer complex queries over data collected from many sources, not to run the transactional application.
ETL and ELT
Both move data from sources into an analytical store.
- ETL: Extract, Transform, then Load. Transformation happens in a separate engine before loading.
- ELT: Extract, Load, then Transform inside the target. Common with cloud warehouses, Microsoft Fabric and Spark, because raw data is kept and the target has plenty of compute.
Azure Data Factory and Fabric Data Factory pipelines orchestrate both.
Batch and stream
| Style | Data is processed | Example |
|---|---|---|
| Batch | In groups, on a schedule | Nightly sales load |
| Stream | Continuously, as events arrive | IoT telemetry, fraud alerts |
Azure Stream Analytics and Fabric Real-Time Intelligence handle streaming.
Exam traps
- OLTP data is normalized; analytical models are often denormalized. The bank asks about OLTP traits such as heavy writes, normalized data and schema on write.
- Schema on read belongs to analytics and lakes, not OLTP.
- ETL transforms before loading; ELT transforms after. The letters are the order.
- The data engineer builds the analytical stores; the analyst builds the reports.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. Which role is responsible for designing and building data stores and pipelines for analytical workloads?
Show answer
Answer: B.Data engineers build the data stores and pipelines that feed analytics. Analysts explore data and build reports; DBAs manage and secure databases.
2. An order-entry system processes thousands of small inserts and updates per minute and must never lose a committed order. Which workload type is this?
Show answer
Answer: B.Many short, concurrent writes with strong consistency describe OLTP.
3. In which approach is raw data loaded into the destination first and transformed there using the destination's compute?
Show answer
Answer: B.ELT (extract, load, transform) loads first and transforms in the target, such as a lakehouse or warehouse.
4. Sensor readings must be processed within seconds of arrival. Which processing style fits?
Show answer
Answer: B.Continuous low-latency processing of events as they arrive is stream processing.