DP-900 Describe an analytics workload on Azure Practice Question
A company uses Azure Synapse Analytics to run a data warehouse. They need to load 500 GB of historical data from Azure Blob Storage into a staging table. They want the fastest load performance with minimal administrative overhead. Which method should they use?
⚠ Common exam trap
Watch out — candidates often assume Azure Data Factory is always the fastest for data movement because of its visual interface and parallelization, but they overlook that PolyBase's direct integration with Synapse's MPP engine provides superior performance for warehouse loading without intermediate data routing.
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
✓
Use PolyBase with the COPY INTO statement
PolyBase with the COPY INTO statement is the fastest method for loading large volumes of data into Azure Synapse Analytics because it leverages the Massively Parallel Processing (MPP) architecture to read data directly from Azure Blob Storage in parallel across all compute nodes, bypassing any single-node bottleneck. It also requires minimal administrative overhead as it is a native T-SQL command with automatic schema inference and no external tools or orchestration to manage.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use SQL Server Integration Services (SSIS)
Why it's wrong here
SQL Server Integration Services (SSIS) is a legacy ETL tool that requires an Azure-SSIS Integration Runtime in Synapse, adding significant management overhead and cost. While SSIS can load data, it executes through a single orchestration engine and does not natively leverage Synapse's Massively Parallel Processing (MPP) architecture for direct, high-throughput ingestion. The result is a slower, more complex pipeline compared to modern parallel loading options.
- ✓
Use PolyBase with the COPY INTO statement
Why this is correct
PolyBase with the COPY INTO statement is the optimal choice because it loads data directly from Azure Data Lake Storage or Azure Blob Storage without requiring staging tables. COPY INTO leverages Synapse's MPP compute nodes in parallel, reading source files concurrently to maximize throughput, and provides built-in error handling and flexible file format support (e.g., Parquet, CSV). This minimizes management effort while delivering the fastest, most reliable bulk load path into a dedicated SQL pool.
- ✗
Use Azure Data Factory with Copy activity
Why it's wrong here
Azure Data Factory (ADF) Copy activity is excellent for orchestration, scheduling, and data movement across diverse sources, but when used alone for loading into Synapse, it acts as an intermediary, moving data through an integration runtime. This network-level copy adds overhead and does not inherently exploit Synapse's parallel native loading capabilities. In practice, ADF should be configured to use PolyBase (or the COPY command) inside the sink to achieve comparable performance; otherwise, it is slower than a direct PolyBase load.
- ✗
Use the bcp utility
Why it's wrong here
The bcp utility is a command-line bulk copy tool that writes rows into Synapse through the SQL client interface, typically requiring a local staging file and issuing row-by-row or batched inserts. It bypasses the distributed MPP engine's best path, resulting in significantly lower throughput for large data volumes. bcp also lacks native error-resolution and file-format flexibility, making it impractical for cloud-scale data warehousing workloads.
Quick reference
Azure Blob Storage Tier Comparison
| Tier | Storage Cost | Retrieval Cost | Latency | Use Case |
|---|---|---|---|---|
| Hot | Highest | Lowest | Immediate | Active data, frequent reads |
| Cool | Lower | Higher | Immediate | Data accessed < once / month |
| Cold | Lower still | Higher | Immediate | Data accessed < once / quarter |
| Archive | Lowest | Highest + rehydration delay | Hours | Long-term compliance retention |
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Data warehouse
A data warehouse is a central repository that stores large amounts of structured data from multiple sources, optimized for querying and analysis rather than day-to-day transactions.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.