Exam DP-203 topic 1 question 97 discussion

from this link : https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop Partition elimination is available only in the partitioned tables created on Parquet or CSV formats that are synchronized from Apache Spark pools. You might create external tables on Parquet partitioned folders, but the partitioning columns are inaccessible and ignored, while the partition elimination won't be applied. Don't create external tables on Delta Lake folders because they aren't supported. Use Delta partitioned views if you need to query partitioned Delta Lake data.

D is correct the keyword here is: "Delta Lake table named SparkTable1" here is how partitioned view works: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-views#partitioned-views And this link shows that dedicated pool does not talk to delta table: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

Partition elimination is available only in the partitioned tables created on Parquet or CSV formats that are synchronized from Apache Spark pools."https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop "

Only partitioned Table can support partition elimination, so the answer should be A

The folder partition elimination is available in the native external tables that are synchronized from the Synapse Spark pools. If you have partitioned data set and you would like to leverage the partition elimination with the external tables that you create, use the partitioned views instead of the external tables.

D is correct. The solution is achieved by storing the delta lake table in sql spool ,so that transact-SQL query can be used on it..The choice of sql pool to use is determined by the format of the data .which is Delta.This format (Delta) is only supported by severless sql pool,hence it will be chosen for the job.Yes,it is important to state thay it can use the transct sql query through the openrowset function. The next case in the solution is the partitioning part. As a precaution from azure documentation ,external tables in severless sql pool with delta format should not be partitioned .If a partition must be used for query optimization,then use , a partition view .This view also contain partition folders or rather support partition folder elimination which is need for query optimization

Everything is mentioned here: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

D is correct. "The OPENROWSET function is not supported in dedicated SQL pools in Azure Synapse." so it eliminates A,B and C. Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16 Only the partitioned view in the serverless sql pool is correct since "External tables in serverless SQL pools do not support partitioning on Delta Lake format. Use Delta partitioned views instead of tables if you have partitioned Delta Lake data sets." Ref: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables#delta-tables-on-partitioned-folders