Microsoft Updated DP-600 Exam Questions and Answers by milan

You need to configure OneLake security for two users with the following requirements:

User1: Read all the Spark data

User2: Read all the SQL endpoint data

The available permissions are: Read, ReadAll, and ReadData.

Understanding the permissions:

Read → Grants access to metadata but not the data itself.

ReadData → Grants access to query data through SQL endpoints (SQL-based access).

ReadAll → Grants access to read data across all engines (including Spark and SQL endpoints).

Applying least privilege principle:

User1 (Spark access): Needs to read all the Spark data → Assign ReadAll because Spark requires this permission to access data in OneLake.

User2 (SQL endpoint access): Needs to read SQL endpoint data only → Assign ReadData, as this grants access to SQL endpoints without over-provisioning Spark access.

Final Answer:

User1: ReadAll

User2: ReadData

The correct query to use in order to return data for the year 2023 that displays ProductID, ProductName, and has a summarized Amount greater than 10,000 is Option B. The reason is that it uses the GROUP BY clause to organize the data by ProductID and ProductName and then filters the result using the HAVING clause to only include groups where the sum of Amount is greater than 10,000. Additionally, the DATEPART(YEAR, SaleDate) = '2023' part of the HAVING clause ensures that only records from the year 2023 are included. References = For more information, please visit the official documentation on T-SQL queries and the GROUP BY clause at T-SQL GROUP BY.

You should use CREATE VIEW to make the pricing group logic available for T-SQL queries.

The CASE statement should be used to determine the pricing group based on the list price.

The T-SQL statement should create a view that classifies products into pricing groups based on the list price. The CASE statement is the correct conditional logic to assign each product to the appropriate pricing group. This view will standardize the pricing group logic across different databases and semantic models.

For the semantic model design required for the customer satisfaction report, the choices for data source authentication method and mode should be made based on security and performance considerations as per the case study provided.

Authentication method: The data should be accessed securely, and given that row-level security (RLS) is required for users executing T-SQL queries, you should use an authentication method that supports RLS. Service principal authentication is suitable for automated and secure access to the data, especially when the access needs to be controlled programmatically and is not tied to a specific user's credentials.

Mode: The report needs to show data as soon as it is updated in the data store, and it should only contain data from the current and previous year. DirectQuery mode allows for real-time reporting without importing data into the model, thus meeting the need for up-to-date data. It also allows for RLS to be implemented and enforced at the data source level, providing the necessary security measures.

Based on these considerations, the selections should be:

Authentication method: Service principal authentication

Mode: DirectQuery