Launch pricing — Azure certification prep for €19,95 €29,95

← All DP-900 topicsDP-900 · Analytics Workloads on Azure

DP-900 Data Warehousing and Lakehouse Analytics Practice Questions

This topic covers large-scale analytics on Azure. Know how data is ingested and transformed with pipelines (ETL versus ELT, Azure Data Factory), and where it lands: a data warehouse, a data lake, or a lakehouse that combines both, often organized as bronze, silver and gold layers.

You also need to tell Azure Synapse Analytics, Azure Databricks and Microsoft Fabric apart, and understand analytical data models: star schemas with fact and dimension tables, measures and hierarchies.

Below are 14 free questions on this topic. Try each one before you open the answer.

Question 1

A data engineering team needs a storage solution that supports fine-grained access control at the file and folder level for big data analytics workloads. Which Azure service should they choose?

  • A.Azure Blob Storage (without hierarchical namespace)
  • B.Azure Data Lake Storage Gen2
  • C.Azure Files
  • D.Azure Table Storage
chevron_rightShow answer and explanation

Correct answer: B. Azure Data Lake Storage Gen2

Azure Data Lake Storage Gen2 (ADLS Gen2) is built on top of Azure Blob Storage and adds a hierarchical namespace, enabling true directory structures and POSIX-compliant ACLs (Access Control Lists) at the file and folder level. This makes it ideal for big data analytics workloads that require granular security and efficient data organization.

Question 2

A data science team needs to run machine learning training jobs and complex data transformations that require distributed in-memory processing across a cluster. Which Azure Synapse Analytics feature is most appropriate?

  • A.Serverless SQL pool
  • B.Dedicated SQL pool
  • C.Apache Spark pool
  • D.Azure Stream Analytics
chevron_rightShow answer and explanation

Correct answer: C. Apache Spark pool

Apache Spark pools in Azure Synapse Analytics provide distributed, in-memory cluster computing using the Apache Spark engine. Spark is well-suited for large-scale data transformations, machine learning (via MLlib or other libraries), and processing semi-structured or unstructured data using Python, Scala, R, or SQL notebooks. SQL pools are optimized for structured SQL queries, not iterative machine learning workloads.

Question 3

Which of the following best describes Azure Databricks?

  • A.A managed relational database service based on Apache Spark
  • B.A collaborative analytics platform based on Apache Spark, optimized for data engineering and machine learning
  • C.A real-time event streaming service for IoT data
  • D.A serverless SQL query engine for Azure Data Lake
chevron_rightShow answer and explanation

Correct answer: B. A collaborative analytics platform based on Apache Spark, optimized for data engineering and machine learning

Azure Databricks is a managed Apache Spark platform that provides a collaborative notebook environment for data engineers, data scientists, and analysts. It is optimized for large-scale data processing, machine learning, and streaming analytics. Key features include Delta Lake (ACID transactions on data lakes), MLflow for ML lifecycle management, and tight integration with Azure storage and security services. It is not a relational database or a simple SQL query engine.

Question 4

In Azure Data Factory, what is the role of a trigger?

  • A.It defines the schema of data read by a pipeline activity
  • B.It determines when and how a pipeline execution is initiated
  • C.It stores connection credentials to external data sources
  • D.It maps column transformations between source and destination
chevron_rightShow answer and explanation

Correct answer: B. It determines when and how a pipeline execution is initiated

In Azure Data Factory, a trigger defines the schedule or event that starts a pipeline run. ADF supports several trigger types: schedule triggers (run at fixed intervals), tumbling window triggers (run over fixed time windows, supporting dependency chaining), storage event triggers (run when a file arrives in Blob Storage), and manual triggers. Linked services handle credentials, datasets define data structure, and data flows handle transformations.

Question 5

What is the primary purpose of Azure Synapse Link?

  • A.To replicate Azure SQL Database tables to on-premises SQL Server in real time
  • B.To enable near real-time analytical queries on operational data without building traditional ETL pipelines
  • C.To replace Azure Data Factory for batch data movement between storage accounts
  • D.To provide a dedicated SQL pool that automatically scales based on query concurrency
chevron_rightShow answer and explanation

Correct answer: B. To enable near real-time analytical queries on operational data without building traditional ETL pipelines

Azure Synapse Link is a cloud-native HTAP (Hybrid Transactional and Analytical Processing) capability. It creates a direct, no-ETL link between operational data stores (such as Azure Cosmos DB, Dataverse, or Azure SQL Database) and Azure Synapse Analytics. This allows analytical queries to run directly against operational data with minimal latency — without impacting the performance of the transactional workload and without manually building or scheduling ETL pipelines.

Question 6

When would you prefer PolyBase over COPY INTO in Azure Synapse Analytics?

  • A.When you want to load data as quickly as possible with minimal T-SQL configuration
  • B.When you need to query external data in Azure Data Lake Storage using T-SQL without physically loading it into the dedicated SQL pool
  • C.When the source file is a JSON document stored in Azure Cosmos DB
  • D.When loading data from on-premises SQL Server using Windows Authentication
chevron_rightShow answer and explanation

Correct answer: B. When you need to query external data in Azure Data Lake Storage using T-SQL without physically loading it into the dedicated SQL pool

The unique advantage of PolyBase is data virtualization: you can create an external table that lets you query files in Data Lake Storage directly using T-SQL, as if the data were already in the SQL pool — without moving or copying it. This is useful for ad-hoc exploration, joining external data with internal tables, or staging patterns where you want to transform data using CTAS (CREATE TABLE AS SELECT). COPY INTO, by contrast, always physically loads data into the SQL pool and does not support querying external data in place.

Question 7

What is an EXTERNAL DATA SOURCE in Azure Synapse Analytics, and why is it needed for PolyBase?

  • A.It is a copy of the data lake stored inside the dedicated SQL pool for fast access
  • B.It is a T-SQL object that defines the connection details (location URL and credentials) for an external storage account, required before creating an external table with PolyBase
  • C.It is an Azure Data Factory linked service that connects Synapse to external databases
  • D.It is a network rule that allows the dedicated SQL pool to access Azure Blob Storage over a private endpoint
chevron_rightShow answer and explanation

Correct answer: B. It is a T-SQL object that defines the connection details (location URL and credentials) for an external storage account, required before creating an external table with PolyBase

In the PolyBase pattern, you must create three objects before you can create an external table: (1) a DATABASE SCOPED CREDENTIAL (or managed identity) for authentication, (2) an EXTERNAL DATA SOURCE that stores the storage account URL and references the credential, and (3) an EXTERNAL FILE FORMAT that describes the structure of the files (delimiter, file type, etc.). The EXTERNAL DATA SOURCE is the object that tells Synapse where the external data lives — for example, "abfss://container@storageaccount.dfs.core.windows.net/".

Question 8

What is the medallion architecture in the context of a data lakehouse?

  • A.A pricing tier model for Azure Data Lake Storage based on hot, cool, and archive access tiers
  • B.A multi-layer data organization pattern where data passes through Bronze (raw), Silver (cleaned), and Gold (aggregated/business-ready) zones
  • C.A security model that grants different levels of access to data based on user roles: read-only, contributor, and owner
  • D.A three-region replication strategy for Azure Data Lake Storage to ensure high availability
chevron_rightShow answer and explanation

Correct answer: B. A multi-layer data organization pattern where data passes through Bronze (raw), Silver (cleaned), and Gold (aggregated/business-ready) zones

The medallion architecture (also called multi-hop architecture) organizes data in a data lake or lakehouse into three quality zones: Bronze (raw ingested data as-is from sources), Silver (validated, deduplicated, and joined data), and Gold (aggregated, business-level data ready for consumption by analysts and BI tools). Data flows progressively through these zones, with each layer adding quality and structure. This pattern is commonly used with Azure Databricks and Delta Lake, and also within Azure Synapse Analytics.

Question 9

Which Azure service is most commonly used to implement a medallion (Bronze/Silver/Gold) architecture with Delta Lake tables on Azure?

  • A.Azure SQL Database with read replicas for each medallion layer
  • B.Azure Stream Analytics with three separate output sinks
  • C.Azure Databricks or Azure Synapse Analytics with Apache Spark and Delta Lake
  • D.Power BI Premium with dataflows configured for each medallion layer
chevron_rightShow answer and explanation

Correct answer: C. Azure Databricks or Azure Synapse Analytics with Apache Spark and Delta Lake

The medallion architecture was popularized by Databricks and is natively supported via Delta Lake — an open-source storage format that adds ACID transactions, schema enforcement, and time travel to data lake files. Both Azure Databricks and Azure Synapse Analytics (Spark pools) can read and write Delta Lake tables, making them the primary tools for implementing Bronze, Silver, and Gold layers. Spark notebooks or pipelines orchestrate the transformations between layers, and Delta Lake ensures data reliability at each zone.

Question 10

What does ELT stand for, and how does it differ from ETL in the context of modern cloud data platforms?

  • A.ELT = Extract, Load, Transform. Data is loaded into the target system first, then transformed using the target system's compute power — typical for cloud data warehouses and lakehouses
  • B.ELT = Encrypt, Load, Transfer. Data is encrypted before being loaded into a data lake
  • C.ELT and ETL are synonyms; both load raw data and then apply transformations in the source system
  • D.ELT = Extract, Link, Transform. It uses Synapse Link to connect operational and analytical stores
chevron_rightShow answer and explanation

Correct answer: A. ELT = Extract, Load, Transform. Data is loaded into the target system first, then transformed using the target system's compute power — typical for cloud data warehouses and lakehouses

In traditional ETL (Extract, Transform, Load), data is transformed in an intermediate staging area (like a local server or integration service) before being loaded. ELT (Extract, Load, Transform) reverses this: raw data is loaded first into the target system (e.g., a Synapse dedicated SQL pool or a data lake), and transformations run inside the target using its native compute (T-SQL, Spark). ELT is preferred for cloud platforms because cloud storage is cheap (so loading raw data is fine) and cloud compute is powerful and scalable (so in-place transformations are efficient). Azure Synapse Analytics and Azure Databricks both support ELT patterns.

Question 11

What is the purpose of distribution in an Azure Synapse Analytics dedicated SQL pool, and what are the three distribution strategies?

  • A.Distribution determines how data is encrypted at rest; strategies are AES-128, AES-256, and TDE
  • B.Distribution determines how data is split across the 60 underlying compute nodes; strategies are hash distribution, round-robin distribution, and replicated distribution
  • C.Distribution controls which Azure region stores the data; strategies are primary region, secondary region, and geo-redundant
  • D.Distribution defines the backup schedule for the dedicated SQL pool; strategies are daily, weekly, and continuous
chevron_rightShow answer and explanation

Correct answer: B. Distribution determines how data is split across the 60 underlying compute nodes; strategies are hash distribution, round-robin distribution, and replicated distribution

A dedicated SQL pool is a massively parallel processing (MPP) system that distributes tables across 60 distributions (compute nodes). The three strategies are: (1) Hash distribution — rows are assigned to a distribution based on a hash of a specified column; optimal for large fact tables queried with JOINs on the hash column. (2) Round-robin — rows are evenly distributed in a rotating fashion; simple but may require data movement during JOINs. (3) Replicated — the entire table is copied to all 60 nodes; best for small dimension tables to eliminate data movement during joins. Choosing the right distribution is key to query performance in Synapse.

Question 12

A bank stores 10 years of transaction history (200 TB) in a data lake. The data science team runs ad hoc exploratory queries over this data using SQL. The BI team runs a fixed set of complex monthly reports. Which combination of Azure services best serves both teams?

  • A.Use Azure Cosmos DB for the data scientists and Azure SQL Database for the BI team
  • B.Use Azure Synapse Analytics serverless SQL pool for ad hoc data science queries and a dedicated SQL pool for the BI team's monthly reports
  • C.Use Azure Databricks for both teams and share the same cluster
  • D.Use Azure Data Factory to load all 200 TB into Azure SQL Database for both teams
chevron_rightShow answer and explanation

Correct answer: B. Use Azure Synapse Analytics serverless SQL pool for ad hoc data science queries and a dedicated SQL pool for the BI team's monthly reports

This is a classic split-workload pattern within Azure Synapse. The serverless SQL pool is ideal for the data science team's exploratory queries — they pay per TB scanned with no upfront provisioning. The dedicated SQL pool provides consistent, high-performance query execution for the BI team's predictable monthly workloads, with columnar storage and result caching. Loading 200 TB into Azure SQL Database is impractical and expensive — SQL DB is optimized for OLTP, not 200 TB analytics. Cosmos DB is not designed for large-scale analytical queries. Sharing a Databricks cluster is operationally simpler but more expensive and less appropriate for SQL-fluent BI users.

Question 13

Determine whether each statement about Microsoft OneLake in Microsoft Fabric is correct.

  1. 1.OneLake is automatically provisioned with every Microsoft Fabric tenant and serves as the central repository for all analytics data.
  2. 2.OneLake requires you to duplicate data into separate storage containers before different analytical engines can use the same dataset.
  3. 3.OneLake is built on top of Azure Data Lake Storage Gen2 and stores data in Delta Parquet format.
chevron_rightShow answer and explanation
  1. Statement 1: Yes
  2. Statement 2: No
  3. Statement 3: Yes

Statement 1 is true: OneLake is provisioned automatically with every Fabric tenant as the organization-wide unified data lake. Statement 2 is false: OneLake allows the same data to be used across multiple analytical engines without data movement or duplication, which is one of its key benefits. Statement 3 is true: OneLake is built on ADLS Gen2 and uses Delta Parquet as its storage format.

Question 14

A snowflake schema extends a star schema by adding tables related to the ___ tables to represent dimensional hierarchies.

  • A.dimension
  • B.fact
  • C.staging
  • D.bridge
chevron_rightShow answer and explanation

Correct answer: A. dimension

In a snowflake schema, dimension tables are further normalised by linking them to additional sub-dimension tables that describe hierarchies (e.g., a Product dimension linked to a Category table). Fact tables (option B) hold numeric measures and are not the ones being extended in a snowflake design. Staging tables (option C) are temporary ingestion zones unrelated to the analytical schema. Bridge tables (option D) are used in specific many-to-many relationship patterns, not for representing dimensional hierarchies.

These are 14 of 63 Data Warehousing and Lakehouse Analytics questions in the full bank

Practice all 400 DP-900 questions in exam format, including drag-and-drop and multi-select questions, with a mock exam and progress tracking.