Design and implement data storage →easyMultiple Choice

DP-203 Design and implement data storage Practice Question

Exhibit

Refer to the exhibit.

```sql
CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    LOCATION = 'abfss://data@storagedatalake.dfs.core.windows.net',
    TYPE = HADOOP,
    CREDENTIAL = MyCredential
);

CREATE EXTERNAL FILE FORMAT MyFileFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

CREATE EXTERNAL TABLE dbo.Orders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2)
)
WITH (
    LOCATION = '/orders/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
);
```

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

⚠ Common exam trap

Test-takers frequently assume external tables require an explicit import step before querying, but in reality, PolyBase allows direct querying of external data without any data movement into the dedicated SQL pool.

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

✓

You can query the external table using standard T-SQL SELECT statements.

An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Querying the external table automatically imports data into a round-robin distribution.
Why it's wrong here
Querying an external table reads data in place from external storage; it does not load rows into a round-robin distributed table. It is tempting because round-robin is the default distribution for new dedicated SQL pool tables, and automatic import would apply to CTAS or INSERT SELECT operations.
✗
The table cannot be queried until the data is imported into the dedicated SQL pool.
Why it's wrong here
External tables are queried directly against files in the data lake, so no import into the dedicated SQL pool is needed first. It tempts because dedicated SQL pool tables do require loading before querying, and that import step would be correct for a regular internal table.
✓
You can query the external table using standard T-SQL SELECT statements.
Why this is correct
Dedicated SQL pools expose external tables through the PolyBase engine, so standard T-SQL SELECT statements work against them exactly as with internal tables. The external table definition supplies the schema and the LOCATION pointing to the data in Azure Data Lake Storage, satisfying the requirement to query without loading.
✗
You must first create a PolyBase external table before querying.
Why it's wrong here
The external table already exists, so creating a PolyBase external table beforehand is redundant; PolyBase is the underlying connector, not a prerequisite object. It tempts because PolyBase is genuinely required to query external data, and creating one would be correct if no external data source or table had been defined.

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

DP-203 Design and implement data storage Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Azure Blob Storage Tier Comparison

Related to this question