Memory hook: Data shape → workload → store → processing → insight.
Reviewed 10 October 2026. Read this once, then answer the last-pass checks without looking.
Must remember by domain
| Domain | Rapid revision |
|---|---|
| Core concepts | Structured tables have a defined schema; JSON/XML are semi-structured; audio/images/free text are unstructured. CSV is row text; Parquet is columnar and suited to analytical reads. OLTP performs short operational transactions; OLAP analyzes larger historical collections. Batch is bounded; streaming continuously processes events. |
| Roles and quality | DBA operates database availability/security/performance; data engineer builds ingestion/transformation; analyst models and interprets. Quality includes validity, completeness, uniqueness and freshness. Governance adds ownership, lineage, access and retention. Schema validity does not prove factual correctness. |
| Relational | Tables/keys/relationships support structured integrity. Normalization reduces duplication/update anomalies. ACID = atomicity, consistency, isolation, durability. DDL defines objects; DML changes rows; SELECT queries. WHERE filters rows; HAVING filters aggregate groups. NULL is unknown/missing, not zero. |
| SQL services | SQL Database manages databases; Managed Instance preserves more instance compatibility; SQL Server VMs give OS/engine control with more maintenance. Managed PostgreSQL/MySQL serve those engines. Views store query definitions; procedures encapsulate operations; indexes speed suitable reads while increasing write/storage work. |
| Nonrelational | Blob stores objects; ADLS adds hierarchical namespace; Files exposes shares; Table Storage stores key/attribute records. Cosmos DB supports distributed nonrelational models/APIs. Partition keys affect distribution and locality; RUs measure work. Flexible schema still needs application modeling. |
| Consistency | Strong gives the strongest latest-read guarantee; bounded staleness limits lag; session preserves session guarantees; consistent prefix prevents out-of-order reads; eventual converges without a fixed staleness bound. A consistency choice does not create unlimited cross-partition transactions. |
| Analytics | ETL transforms before loading; ELT transforms after loading. Data Factory orchestrates, Spark transforms, warehouses serve curated analytics, lakes store varied source data, lakehouses add supported table semantics. Event Hubs ingests streams; Stream Analytics evaluates streaming queries. Event time differs from processing time. |
| Visualization | Power BI semantic models define relationships/measures; reports have interactive pages; dashboards combine tiles. Import needs data refresh; DirectQuery depends on the source at query time. Facts measure events, dimensions describe them. Use line charts for trends, bars for categories and scatter for relationships. |
Traps
A file store is not automatically a relational database. A globally replicated database still needs backup and residency decisions. Correlation is not causation. Refreshing a report does not correct wrong source data or duplicated joins.
Last-pass self-check
1. What makes a transaction atomic?
Its changes succeed or fail as one unit within the transaction’s supported boundary.
2. Customer document with changing optional fields: suitable model?
A document model, with validation and a useful partition/access design.
3. Need exact shared-file paths/protocols?
Azure Files rather than assuming Blob is a mounted file server.
4. Why choose Parquet for analytical scans?
Columnar layout allows suitable queries to read selected columns efficiently.
5. Measure versus chart?
A measure defines a model calculation; a chart presents the evaluated result.
Sources
Every topic at a glance
Open any topic to revisit its essential facts, decisions and exam traps. Use the full topic for active recall and supporting references.
01 · Data Concepts and Workloads
Memory hook: Structure describes the data; workload describes how it is used.
Must remember
- Structured data has a defined tabular schema; semi-structured data such as JSON carries flexible structure; unstructured content includes images/audio and free text. CSV, JSON, XML, Parquet and Avro differ in schema, representation and analytical efficiency.
- A database organises data for supported access; a file store holds files/objects. Relational tables, key-value records, documents, graphs and column-family models serve different access patterns. Schema-on-write validates before storage; schema-on-read interprets data during use.
- OLTP handles frequent small transactions with consistency requirements; OLAP analyses large historical datasets. Batch processes bounded collections; streaming processes continuing events. Low arrival latency does not automatically guarantee exactly-once results.
- Data engineers build ingestion/transformation pipelines; database administrators manage database operation/security/performance; analysts model and interpret data for decisions. Responsibilities can overlap, but the role distinction helps select the right activity.
- Data quality includes validity, completeness, uniqueness, consistency and freshness. Governance covers ownership, access, lineage, retention and lawful use. A well-formatted record can still be factually wrong.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Checkout updates several related records | Transactional workload. |
| Analyse years of sales by product | Analytical workload. |
| Continuously evaluate sensor events | Streaming processing. |
Traps
- JSON is not necessarily unstructured.
- Real-time ingestion and real-time dashboards are separate stages.
- A file extension alone does not prove valid contents.
02 · Relational Data and Azure SQL
Memory hook: Keys define relationships; transactions preserve consistency; indexes trade reads against writes.
Must remember
- Tables contain rows/columns with data types. Primary keys identify rows; foreign keys express supported relationships. Normalisation reduces redundancy/update anomalies; deliberate denormalisation can improve selected reads with consistency costs.
- ACID means atomicity, consistency, isolation and durability. A transaction groups changes; isolation controls interaction with concurrent work. An index accelerates suitable lookups but consumes space and adds write/maintenance work.
- DDL defines structures (
CREATE,ALTER); DML changes data (INSERT,UPDATE,DELETE);SELECTqueries it. Views store query definitions; stored procedures package operations; indexes are access structures, not duplicate business tables to edit directly. - SQL Database is managed relational database service; SQL Managed Instance supports more instance-level compatibility; SQL Server on Azure VMs provides greater guest/instance control with more customer maintenance. Azure Database for PostgreSQL/MySQL provide managed open-source engines.
- Read query requirements carefully: joins combine related rows; aggregates summarise;
WHEREfilters rows; null represents missing/unknown rather than zero. Managed SQL does not eliminate customer schema, query, access or recovery-design responsibilities.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need SQL Server OS/instance control | SQL Server on a VM. |
| Need managed per-database relational service | SQL Database. |
| Need repeatable query projection | A view where its semantics fit. |
Traps
- Every added index has a write/storage cost.
- Null and zero mean different things.
- A managed database does not design its schema for you.
03 · Non-Relational Storage and Cosmos DB
Memory hook: Choose object, file, key-value, document or graph by the required access pattern.
Must remember
- Blob Storage stores objects in containers and supports tiers with different cost/access behaviour. Data Lake Storage adds hierarchical namespace capabilities for analytical workloads. Azure Files exposes shared file access; Table Storage provides a key/attribute-style non-relational store.
- Cosmos DB supports distributed database workloads through supported APIs/models. Partition keys determine distribution and query locality; a poor key can create hot partitions or expensive cross-partition work. Request Units measure resource consumption of operations.
- Consistency choices trade read guarantees, latency and availability under the selected deployment. Strong, bounded staleness, session, consistent prefix and eventual describe different guarantees where supported. Session consistency preserves relevant session-level behaviour; it is not globally strong consistency.
- Documents can vary in fields, but applications still need validation and evolution rules. Graphs emphasise relationships/traversal; key-value stores emphasise direct key access. Flexible schema does not mean no data modelling.
- Replication, backup, encryption and access are separate decisions. Global distribution must match residency rules and write/conflict requirements. A non-relational system may support transactions within particular boundaries; never assume every transaction spans all partitions globally.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Store videos with metadata and lifecycle rules | Blob storage plus suitable metadata/indexing. |
| Application needs key/document queries across Regions | Evaluate Cosmos DB with proper partitioning. |
| Existing app expects an SMB share | Azure Files where compatible. |
Traps
- NoSQL does not mean no schema discipline.
- Global replication does not remove latency/consistency trade-offs.
- Request Units are not simply document count.
04 · Analytics, Streaming and Power BI
Memory hook: Ingest, store, transform, model and visualise are separate stages.
Must remember
- ETL transforms before loading; ELT loads first and transforms in the target platform. Data Factory supports orchestration/integration; Spark-based processing supports distributed transformations; Synapse and Fabric provide analytical capabilities with different resource/operating models.
- A warehouse stores curated analytical models; a lake stores diverse source data; a lakehouse combines lake storage with supported table/management capabilities. A star schema connects fact measurements to dimensions; analytical models may denormalise for efficient reporting.
- Event Hubs ingests event streams; Stream Analytics evaluates supported streaming queries; batch processing handles finite datasets. Event time and processing time differ, so late/out-of-order events need a policy.
- Power BI semantic models describe relationships and measures; reports contain interactive pages/visuals; dashboards present selected monitoring views. Import and DirectQuery modes differ in freshness, performance and source dependency. Refreshing a report cannot fix bad source data.
- Select charts by the comparison: trends over time, categories, distributions or relationships. Avoid misleading scales and aggregate-only conclusions. Apply access controls and row-level security where required; exporting data can create another governed copy.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Orchestrate movement and transformation stages | Data Factory or an appropriate integration pipeline. |
| Analyse an unbounded event stream | A streaming ingestion/processing design. |
| Present business metrics interactively | Power BI with a suitable semantic model. |
Traps
- A dashboard is not the database of record.
- Streaming arrival time may differ from event time.
- A fast visual can still display stale or incorrect data.