I85PREP
⌘ K
Azure DP-900 study materials
Azure DP-900 study resources

Azure DP-900 practice questions

One question at a time. Build your Azure DP-900 understanding.

First 50 of 197 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Describe core data concepts

Which term describes data that conforms to a fixed schema and is stored in tables with rows and columns?

  1. Streaming data
  2. Structured data Correct answer.
  3. Unstructured data
  4. Semi-structured data

Explanation

Structured data has a predefined schema and is organized into tables of rows and columns, which makes it easy to query with SQL. Semi-structured data has some organization but not a fixed table schema, unstructured data has no predefined model, and streaming data refers to how data is delivered rather than its structure.

Question 2Describe core data concepts

In a relational database, what is a primary key?

  1. A column that references a row in another table
  2. A column that may contain duplicate values
  3. A column used only to sort query results
  4. A column or set of columns that uniquely identifies each row Correct answer.

Explanation

A primary key is a column or combination of columns whose values uniquely identify every row in a table, and it cannot contain nulls. A foreign key references another table, and a sort column does not enforce uniqueness.

Question 3Describe core data concepts

Which workload is characterized by many small, short transactions that insert, update, or delete individual records?

  1. Batch reporting workload
  2. Data warehousing workload
  3. Analytical workload
  4. Transactional workload Correct answer.

Explanation

Transactional (OLTP) workloads consist of many short, concurrent read and write operations on individual records. Analytical workloads scan large volumes for reporting, and batch reporting runs on a schedule rather than as per-record transactions.

Question 4Describe core data concepts

Which type of data store is designed to hold documents, key-value pairs, or graphs without requiring a fixed relational schema?

  1. Data warehouse
  2. Relational database
  3. Star schema
  4. NoSQL database Correct answer.

Explanation

NoSQL databases include document, key-value, column-family, and graph stores, which do not require a fixed relational schema. Relational databases and data warehouses use schemas and tables, and a star schema is a modeling pattern rather than a store type.

Question 5Describe core data concepts

Which role is primarily responsible for building and maintaining pipelines that move and transform data?

  1. Data scientist
  2. Database administrator
  3. Data engineer Correct answer.
  4. Data analyst

Explanation

Data engineers build and maintain the pipelines and infrastructure that ingest, transform, and move data between systems. Analysts focus on reporting, scientists on modeling and experimentation, and database administrators on database availability and performance.

Question 6Describe core data concepts

Which file format stores data as comma-separated values in plain text?

  1. CSV Correct answer.
  2. JSON
  3. Parquet
  4. Avro

Explanation

CSV (comma-separated values) is a plain-text format in which fields are separated by commas and rows by line breaks. JSON is a text format with key-value structure, while Parquet and Avro are binary formats.

Question 7Describe core data concepts

A data warehouse is optimized for which type of workload?

  1. Real-time device telemetry ingestion
  2. High-volume transaction processing
  3. Managing session state for web applications
  4. Complex analytical queries over large historical datasets Correct answer.

Explanation

A data warehouse is optimized for analytical (OLAP) workloads, which involve complex queries that aggregate and scan large historical datasets. Transactional systems are optimized for many small writes, and telemetry ingestion is a streaming workload.

Question 8Describe core data concepts

Which characteristic best describes semi-structured data?

  1. It contains tags or markers that separate semantic elements but does not follow a rigid table schema. Correct answer.
  2. It must be stored in a relational table with a fixed schema.
  3. It is always stored as binary large objects.
  4. It cannot contain nested elements.

Explanation

Semi-structured data uses tags or markers, such as keys in JSON or elements in XML, to separate semantic elements, but it does not require a rigid table schema. Structured data fits tables, and unstructured data lacks a predefined model.

Question 9Describe core data concepts

What is the main purpose of normalizing a relational database?

  1. To increase query speed by duplicating data
  2. To convert structured data into unstructured data
  3. To eliminate the need for primary keys
  4. To reduce data duplication and update anomalies Correct answer.

Explanation

Normalization organizes tables to minimize redundancy and dependency, which reduces duplicate data and avoids update, insert, and delete anomalies. Denormalization intentionally duplicates data to improve read performance.

Question 10Describe core data concepts

In an ETL process, what happens during the Transform step?

  1. Data is loaded into the destination warehouse.
  2. Data is extracted from source systems.
  3. Data is deleted from the source system.
  4. Data is cleansed, validated, and converted into a target format. Correct answer.

Explanation

The Transform step cleanses, validates, standardizes, and converts extracted data into the structure required by the target. Extract retrieves data from sources, and Load writes the result to the destination.

Question 11Describe core data concepts

Which statement describes a data lake?

  1. It enforces strict schema-on-write for all data.
  2. It stores large volumes of raw data in its native format until it is needed. Correct answer.
  3. It stores only highly structured data in relational tables.
  4. It is limited to data smaller than 1 GB.

Explanation

A data lake stores raw data in its native format, including structured, semi-structured, and unstructured data, until it is needed, often using schema-on-read. Data warehouses typically enforce schema-on-write and store structured data.

Question 12Describe core data concepts

Which processing approach handles data continuously as it arrives, with very low latency?

  1. Batch processing
  2. Stream processing Correct answer.
  3. ETL scheduled nightly
  4. OLTP

Explanation

Stream processing analyzes data continuously as it arrives, which supports near-real-time scenarios. Batch processing handles data in scheduled chunks, and nightly ETL is a batch pattern.

Question 13Describe core data concepts

A foreign key in a relational table is used to do what?

  1. Encrypt sensitive columns
  2. Uniquely identify each row in the same table
  3. Sort rows alphabetically
  4. Establish a link to a primary key in another table Correct answer.

Explanation

A foreign key is a column or set of columns whose values must match a primary key or unique key in another table, creating a referential link between the tables. A primary key enforces uniqueness within its own table.

Question 14Describe core data concepts

Which property of a transaction ensures that it is completed entirely or not at all?

  1. Isolation
  2. Atomicity Correct answer.
  3. Consistency
  4. Durability

Explanation

Atomicity guarantees that all operations in a transaction succeed or the entire transaction is rolled back. Durability persists committed changes, consistency enforces rules, and isolation prevents interference between concurrent transactions.

Question 15Describe core data concepts

In a relational model, what does cardinality describe?

  1. The number of rows in a table or the relationship between entities Correct answer.
  2. The physical size of a database file
  3. The number of columns in a table
  4. The data type of a column

Explanation

Cardinality refers to the number of rows in a relation or to the numeric relationship between entity instances, such as one-to-many. It does not describe column counts, data types, or file size.

Question 16Describe core data concepts

Which of the following is an example of unstructured data?

  1. A table of customer orders
  2. A JSON document with defined fields
  3. A collection of audio recordings Correct answer.
  4. A CSV file of sales figures

Explanation

Audio recordings have no predefined data model or schema, so they are unstructured. Tables, CSV files, and JSON documents all have some defined structure or organization.

Question 17Describe core data concepts

Which task is most typical for a data analyst?

  1. Writing low-level device drivers
  2. Creating reports and visualizations from existing data Correct answer.
  3. Managing network firewalls
  4. Designing storage engines for databases

Explanation

Data analysts explore and interpret existing data to produce reports, dashboards, and visualizations for decision-makers. They generally do not design storage engines or manage network infrastructure.

Question 18Describe core data concepts

What is a database view?

  1. A backup of the transaction log
  2. A physical copy of a table stored on disk
  3. A type of index used for full-text search
  4. A virtual table defined by a stored query Correct answer.

Explanation

A view is a virtual table whose contents are defined by a stored query, and it does not store data separately unless it is a materialized view. It is not an index, a log backup, or a physical copy.

Question 19Describe core data concepts

Which characteristic distinguishes OLAP systems from OLTP systems?

  1. OLAP systems are normalized to third normal form.
  2. OLAP systems use denormalized schemas and read-heavy queries. Correct answer.
  3. OLAP systems typically process many short write transactions.
  4. OLAP systems store only current-day data.

Explanation

OLAP systems typically use denormalized star or snowflake schemas and are read-heavy, scanning large volumes for aggregation. OLTP systems handle many short write transactions and are usually highly normalized.

Question 20Describe core data concepts

What is a data lakehouse?

  1. A data warehouse that cannot store unstructured data
  2. A relational database used only for OLTP
  3. A platform that combines data lake storage with data warehouse management features Correct answer.
  4. A file system that does not support transactions

Explanation

A data lakehouse combines low-cost data lake storage with data warehouse features such as schema enforcement, transactions, and performance optimizations. It is not limited to OLTP or structured-only data.

Question 21Describe core data concepts

Why is Parquet often preferred over CSV for analytical workloads?

  1. It stores data as plain text for easy editing.
  2. It can store only a single column per file.
  3. It requires no schema.
  4. It is a columnar format that supports compression and efficient column reads. Correct answer.

Explanation

Parquet is a columnar binary format that compresses well and lets a query read only the columns it needs, which improves analytical scan performance. CSV is row-based plain text and generally requires reading entire rows.

Question 22Describe core data concepts

What does sharding a database involve?

  1. Partitioning data across multiple servers based on a key Correct answer.
  2. Converting all tables to unstructured files
  3. Creating a duplicate copy of the entire database for backup
  4. Removing all indexes to speed up writes

Explanation

Sharding horizontally partitions data across multiple servers, often by a shard key, so that each server holds a subset of the data. Replication creates copies, which is a different technique.

Question 23Describe core data concepts

What does data lineage describe?

  1. The encryption algorithm used for a column
  2. The physical location of backup tapes
  3. The number of users who can query a table
  4. The origin and movement of data through a system over time Correct answer.

Explanation

Data lineage tracks where data originated and how it moves and transforms through systems over time, which supports auditing and impact analysis. It does not describe backups, encryption algorithms, or access counts.

Question 24Describe core data concepts

How does a snowflake schema differ from a star schema?

  1. Its dimension tables are normalized into multiple related tables. Correct answer.
  2. It contains no fact table.
  3. It stores only unstructured data.
  4. It cannot be used in a data warehouse.

Explanation

In a snowflake schema, dimension tables are normalized into multiple related tables, whereas a star schema keeps dimensions denormalized in single tables. Both contain fact and dimension tables and are used in warehouses.

Question 25Describe core data concepts

Which statement best describes the difference between a database and a data warehouse?

  1. A data warehouse cannot store historical data.
  2. A database cannot store structured data.
  3. A database is optimized for day-to-day transactions, while a data warehouse is optimized for analytical reporting. Correct answer.
  4. A data warehouse is used only for OLTP.

Explanation

An operational database is optimized for day-to-day transactional processing, while a data warehouse is designed for analytical reporting over consolidated historical data. Warehouses commonly store historical data and are not used for OLTP.

Question 26Describe core data concepts

Which term describes data that is organized into a predefined schema of tables, rows, and columns?

  1. Metadata
  2. Semi-structured data
  3. Structured data Correct answer.
  4. Unstructured data

Explanation

Structured data conforms to a predefined schema, so every row has the same columns and each column has a defined type. This makes it easy to query with SQL and to enforce constraints. Semi-structured and unstructured data do not follow a fixed table schema, and metadata describes data rather than being the data itself.

Question 27Describe core data concepts

Which data type is designed to store a calendar date with no time component?

  1. Datetime
  2. Boolean
  3. Date Correct answer.
  4. Time

Explanation

A date type stores a year, month, and day only, so it is the right choice when no clock time is needed. Datetime types add a time component, a time type stores only a clock value, and Boolean stores true or false.

Question 28Describe core data concepts

How is a data mart best described?

  1. A copy of every source system in the organization
  2. A cache that speeds up transactional queries
  3. A subset of a data warehouse focused on one business area Correct answer.
  4. A staging area that holds raw files before transformation

Explanation

A data mart is a smaller, focused slice of analytical data serving a single department or subject area. It can be sourced from a warehouse or built independently. A full copy of all sources is closer to a warehouse, raw files belong in a landing zone, and a cache is a performance tier.

Question 29Describe core data concepts

Which role is primarily responsible for building and maintaining the pipelines that move and transform data?

  1. Data engineer Correct answer.
  2. Data scientist
  3. Business analyst
  4. Database administrator

Explanation

Data engineers design, build, and operate the pipelines and storage that move and transform data between systems. Data scientists build models, database administrators manage database availability and security, and business analysts gather requirements and interpret results.

Question 30Describe core data concepts

Which SQL statement is used to retrieve rows from a table without changing the data?

  1. UPDATE
  2. SELECT Correct answer.
  3. INSERT
  4. DELETE

Explanation

SELECT is the data manipulation statement that queries and returns rows. INSERT adds rows, UPDATE modifies existing rows, and DELETE removes rows, so all three change stored data.

Question 31Describe core data concepts

What is the main purpose of creating an index on a relational table?

  1. To define relationships between tables
  2. To speed up retrieval of rows that match a search condition Correct answer.
  3. To compress all table data on disk
  4. To enforce uniqueness across every column of a table

Explanation

An index is an auxiliary structure keyed on one or more columns that points to the matching rows, so lookups and joins can avoid scanning the whole table. Uniqueness is enforced only when the index is declared unique and only on its own columns. Compression is a storage option, and relationships are defined by foreign keys.

Question 32Describe core data concepts

A data cleansing process finds several records that describe the same customer. Which task addresses this problem?

  1. Deduplication Correct answer.
  2. Validation
  3. Normalization
  4. Aggregation

Explanation

Deduplication detects repeated records and either removes them or merges them into a single trusted record. Normalization restructures tables to reduce redundancy, validation checks values against rules, and aggregation summarizes detail into totals.

Question 33Describe core data concepts

Which format represents data as a collection of name-value pairs and supports nested objects and arrays?

  1. JSON Correct answer.
  2. Fixed-width text
  3. CSV
  4. Parquet

Explanation

JSON encodes data as name-value pairs and allows objects and arrays to be nested inside one another, which makes it common for semi-structured data exchange. CSV and fixed-width text use positional columns, and Parquet is a binary columnar format for analytics.

Question 34Describe core data concepts

A table has a primary key formed by combining the OrderID and ProductID columns. What is this key called?

  1. Surrogate key
  2. Foreign key
  3. Alternate key
  4. Composite key Correct answer.

Explanation

A composite key uses two or more columns together to uniquely identify a row. A foreign key references a key in another table, a surrogate key is system-generated, and an alternate key is a candidate key that was not chosen as the primary key.

Question 35Describe core data concepts

Which ACID property guarantees that once a transaction commits, its changes survive a power failure?

  1. Durability Correct answer.
  2. Consistency
  3. Isolation
  4. Atomicity

Explanation

Durability means committed changes are written to durable storage and remain available after a crash or restart. Atomicity makes a transaction all-or-nothing, isolation controls visibility between concurrent transactions, and consistency keeps the database within its rules.

Question 36Describe core data concepts

In an ELT process, how does the load step differ from a traditional ETL process?

  1. Data is loaded only after a person approves each batch
  2. Data is loaded in raw form and transformed inside the target system Correct answer.
  3. Data is never transformed at any stage
  4. Data is transformed before it is loaded into the target

Explanation

ELT loads raw data into the target platform first and then uses that platform's compute to transform it, which suits scalable cloud warehouses. ETL transforms data before loading it. Manual approval is a workflow choice rather than the defining difference.

Question 37Describe core data concepts

Many rows in a customer table have an empty Phone column. Which data quality dimension is most directly affected?

  1. Uniqueness
  2. Accuracy
  3. Completeness Correct answer.
  4. Timeliness

Explanation

Completeness measures whether all required values are present, so missing phone numbers reduce completeness. Timeliness concerns availability when needed, uniqueness concerns duplicate records, and accuracy concerns whether values reflect reality.

Question 38Describe core data concepts

Which data model is the highest-level model and shows only business entities and the relationships between them?

  1. Physical data model
  2. Conceptual data model Correct answer.
  3. Dimensional model
  4. Logical data model

Explanation

A conceptual model captures business entities and their relationships for communication with stakeholders and omits technical detail. A logical model adds attributes and keys, a physical model adds tables, indexes, and platform details, and a dimensional model is an analytical modeling style.

Question 39Describe core data concepts

Which type of NoSQL store is best suited for data whose value comes from the relationships among items, such as a social network?

  1. Column-family store
  2. Document store
  3. Key-value store
  4. Graph database Correct answer.

Explanation

A graph database stores nodes and edges and can traverse relationships efficiently, which suits connected data. Key-value and document stores optimize simple lookups and document retrieval, and column-family stores handle wide sparse rows.

Question 40Describe core data concepts

In a dimensional model, what does a fact table primarily contain?

  1. Descriptive attributes of business entities
  2. Numeric measures and keys that reference dimension tables Correct answer.
  3. A copy of every source table
  4. Metadata about the warehouse

Explanation

A fact table stores measurable events such as sales amounts together with foreign keys to the dimension tables that describe them. Descriptive attributes belong in dimension tables, and warehouse metadata belongs in a catalog.

Question 41Describe core data concepts

What is the primary purpose of a data catalog?

  1. To enforce row-level security policies inside a database
  2. To store the physical files that make up a data lake
  3. To provide a searchable inventory of data assets Correct answer.
  4. To schedule and monitor nightly ETL jobs across systems

Explanation

A data catalog records metadata such as descriptions, owners, locations, and classifications so users can find and trust data assets. File storage, access control, and job scheduling are handled by other components.

Question 42Describe core data concepts

Data governance is a set of policies and processes that primarily does what?

  1. Defines who can act on which data and how quality is maintained Correct answer.
  2. Automatically encrypts every database in the organization
  3. Converts unstructured data into relational tables
  4. Replaces the need for a data warehouse

Explanation

Data governance assigns decision rights and accountability, sets policies for access and quality, and defines how data is managed across the organization. Encryption is one possible control, and governance does not replace storage platforms or change data formats.

Question 43Describe core data concepts

Which characteristic is typical of batch processing?

  1. It requires a graph database
  2. Large volumes are processed together on a schedule Correct answer.
  3. Records are handled one at a time as they arrive
  4. Latency is measured in milliseconds

Explanation

Batch processing collects a large set of records and processes them together at planned intervals, such as a nightly job. Handling records individually as they arrive with millisecond latency describes stream processing, and the data store choice is independent.

Question 44Describe core data concepts

In a commonly used data lake organization, what is the purpose of the raw landing zone?

  1. To archive deleted records for legal hold
  2. To serve dashboards directly to business users
  3. To hold source data exactly as received before transformation Correct answer.
  4. To store only aggregated reporting tables

Explanation

The raw or landing zone keeps source data in its original form so it can be reprocessed later if transformation logic changes. Aggregated reporting tables and dashboards are served from curated or serving layers, and legal archives are a separate retention concern.

Question 45Describe core data concepts

Which ACID property prevents one transaction from seeing uncommitted changes made by another?

  1. Atomicity
  2. Consistency
  3. Durability
  4. Isolation Correct answer.

Explanation

Isolation controls how concurrent transactions interact and prevents them from reading each other's uncommitted work. Durability protects committed changes, atomicity makes a transaction all-or-nothing, and consistency preserves database rules.

Question 46Describe core data concepts

A customer dimension must keep a full history of address changes. Which technique should be used?

  1. Store the address only in the fact table
  2. Overwrite the old value with the new value and keep no history
  3. Add a new row with validity dates and keep the old row Correct answer.
  4. Truncate the dimension and reload it every night

Explanation

Keeping history calls for a type 2 slowly changing dimension, which inserts a new version of the row with effective dates and flags the previous row as expired. Overwriting values is a type 1 change that loses history, and reloading or moving attributes to the fact table does not preserve versions.

Question 47Describe core data concepts

A system-generated integer key with no business meaning is added to a dimension table. What is this key called?

  1. Composite key
  2. Foreign key
  3. Surrogate key Correct answer.
  4. Natural key

Explanation

A surrogate key is generated by the system and carries no business meaning, which keeps history stable when business identifiers change. A natural key comes from business data, a composite key combines columns, and a foreign key references another table.

Question 48Describe core data concepts

What does granularity mean for a fact table?

  1. The number of dimension tables joined to the fact table
  2. The level of detail represented by each row Correct answer.
  3. The number of partitions used for storage
  4. The physical size of each row on disk

Explanation

Granularity, or grain, defines what one fact row represents, such as one line item versus one daily total. It determines which dimensions can be used and how detailed the analysis can be. Row size, partitioning, and dimension count are separate design concerns.

Question 49Describe core data concepts

Master data management is primarily concerned with which goal?

  1. Maximizing the compression ratio of archived files
  2. Recording the lineage of every transformation step
  3. Converting transactional workloads into batch workloads
  4. Providing a single consistent view of core entities Correct answer.

Explanation

Master data management creates and maintains trusted, shared definitions of core entities such as customers, products, and suppliers across systems. Compression, lineage tracking, and workload conversion are separate technical or governance activities.

Question 50Describe core data concepts

A data pipeline has tasks that must run in a specific order, with retries when a step fails. Which capability provides this?

  1. Sharding
  2. Pipeline orchestration Correct answer.
  3. Data masking
  4. Data virtualization

Explanation

Orchestration coordinates tasks, enforces dependencies and ordering, and handles retries and monitoring across a pipeline. Virtualization queries data without copying it, masking hides sensitive values, and sharding distributes data across partitions.