DP-900 Describe an analytics workload on Azure Practice Question
A data engineer needs to query data stored in CSV files in Azure Data Lake Storage Gen2 using T-SQL in Azure Synapse Analytics, without loading the data into the database. Which feature should they use?
⚠ Common exam trap
Candidates often confuse external tables with materialized views or indexed views, thinking any database object can query external files, but only external tables provide the PolyBase-based bridge to read data directly from storage without loading.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
External tables
External tables in Azure Synapse Analytics allow you to query data stored in files (such as CSV in Azure Data Lake Storage Gen2) using T-SQL without loading the data into the database. They use the PolyBase engine to read the files directly, enabling on-the-fly querying of external data sources.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
External tables
Why this is correct
An external table in Azure Synapse SQL defines a schema over files in Azure Data Lake Storage Gen2 using an external data source and external file format. PolyBase enables T-SQL queries to read the CSV or Parquet files directly from the storage account without loading or moving the data into a database. This is exactly the capability needed to query CSV data in ADLS Gen2.
- ✗
Materialized views
Why it's wrong here
Materialized views in a dedicated SQL pool physically persist the results of a SELECT query as a database object, requiring the source data to already be loaded into the pool. They optimize query performance by pre-computing aggregates or joins locally, but they cannot read or reference files in Azure Data Lake Storage Gen2 directly. Thus, they are not a mechanism for querying external CSV files.
- ✗
Stored procedures
Why it's wrong here
Stored procedures are encapsulated T-SQL batches saved in a database that can contain business logic, control flow, and error handling. While you could write a stored procedure that issues a SELECT against an external table or OPENROWSET, the procedure itself is not the query engine or connector for external files. Without such internal statements, a stored procedure does not inherently query CSV data in ADLS Gen2, making it an indirect and non-specific solution.
- ✗
Indexed views
Why it's wrong here
An indexed view is a view that has been materialized with a unique clustered index in the database, physically storing the view's output. Like materialized views, indexed views operate only on data that resides in the database pool; they cannot index or access data stored in external CSV files without first importing it. Therefore, indexed views are irrelevant for directly querying data in a data lake.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data Lake Storage Gen2
Data Lake Storage Gen2 is a cloud-based storage service that combines a scalable data lake with enterprise-grade file system capabilities for big data analytics.
Key term
Data lake
A data lake is a centralized storage repository that holds vast amounts of raw data in its native format until it is needed for analysis.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.