nerdexam
Microsoft

DP-500 · Question #20

You are creating an external table by using an Apache Spark pool in Azure Synapse Analytics. The table will contain more than 20 million rows partitioned by date. The table will be shared with the…

The correct answer is B. Apache Parquet. To minimize query time for a serverless SQL pool querying a large, partitioned external table created via Spark, Apache Parquet is the recommended file format due to its columnar storage and optimization features.

Implement and manage a data analytics environment

Question

You are creating an external table by using an Apache Spark pool in Azure Synapse Analytics. The table will contain more than 20 million rows partitioned by date. The table will be shared with the SQL engines. You need to minimize how long it takes for a serverless SQL pool to execute a query data against the table. In which file format should you recommend storing the table data?

Options

  • AJSON
  • BApache Parquet
  • CCSV
  • DDelta

How the community answered

(41 responses)
  • A
    5% (2)
  • B
    71% (29)
  • C
    7% (3)
  • D
    17% (7)

Why each option

To minimize query time for a serverless SQL pool querying a large, partitioned external table created via Spark, Apache Parquet is the recommended file format due to its columnar storage and optimization features.

AJSON

JSON is a row-based format that is less efficient for analytical queries on large datasets compared to columnar formats, leading to slower query performance.

BApache ParquetCorrect

Apache Parquet is a columnar storage format optimized for analytical queries. It offers efficient data compression, encoding, and predicate pushdown, which significantly speeds up queries, especially for large datasets partitioned by date, making it ideal for serverless SQL pools.

CCSV

CSV is a plain text, row-based format that offers no performance optimizations like columnar storage or indexing, making it inefficient for large-scale analytical queries.

DDelta

Delta Lake stores data as Parquet files but adds transactional overhead; for minimizing serverless SQL pool query time on raw data, directly using Apache Parquet is the most efficient choice.

Concept tested: File formats for performance in Azure Synapse Analytics

Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-parquet-files

Topics

#Azure Synapse Analytics#File Formats#Query Performance#Columnar Storage

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice