Exam DP-203 topic 4 question 1 discussion

correct B

Should be A because it is said that most query are using the Primary Key (SaleKey) to access data. If you access data with your PK, it's row-oriented and not columned-oriented. So no Column Store Clustered Index but regular Clustered Index.

is correct

Correct B

Hash on SaleKey distribution column using Columnstore clustered index; Why? (1) petabyte scale data (2) incoming query on SaleKey therefore, SaleKey will be used in WHERE condition and clustered columnstore index will be efficient.

B is correct

yes, B is correct

correct

Clustered indexes may outperform clustered columnstore tables when a single row needs to be quickly retrieved. For queries where a single or very few row lookup is required to perform with extreme speed, consider a clustered index or nonclustered secondary index. The disadvantage to using a clustered index is that only queries that benefit are the ones that use a highly selective filter on the clustered index column. To improve filter on other columns, a nonclustered index can be added to other columns. However, each index that is added to a table adds both space and processing time to loads.

Clustered columnstore indexes are the most efficient way you can store your data in Azure SQL Data Warehouse. Storing your data in tables that have a clustered columnstore index are the fastest way to query your data. It will give you the greatest data compression and lower your storage costs. Hash-distributed tables work well for large fact tables in a star schema. They can have very large numbers of rows and still achieve high performance. Consider using a hash-distributed table when: The table size on disk is more than 2 GB. The table has frequent insert, update, and delete operations. ANS B

A heap is a table without a clustered index. One or more nonclustered indexes can be created on tables stored as a heap. Data is stored in the heap without specifying an order. Usually data is initially stored in the order in which is the rows are inserted into the table, but the Database Engine can move data around in the heap to store the rows efficiently; so the data order cannot be predicted. To guarantee the order of rows returned from a heap, you must use the ORDER BY clause. To specify a permanent logical order for storing the rows, create a clustered index on the table, so that the table is not a heap. Correct answer: Non clustered

Incoming queries use the primary key SaleKey column to retrieve data as displayed in the following table ..doesn't this mean Salekey will be used in where clause , which makes Salekey not suitable for hashkey distribution . Choosing a distribution column that helps minimize data movement is one of the most important strategies for optimizing performance of your dedicated SQL pool: - Is not used in WHERE clauses. This could narrow the query to not run on all the distributions. with no obvious choice i feel it should be round robin with column clustered index i.e D

I understand that hash distribution mainly for improving the joins and group-by to reduce the data shuffling. In this case, there is no join or group-by mentioned. I think round-robin would be a better option.

If the answer is hash distributed, then what would be the key? If there is no obvious joining key, round-robin should be chosen (https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#round-robin-distributed)