Microsoft Microsoft Certifications DP-600 Questions & Answers

• Question 41:

  You have a Fabric tenant that contains two lakehouses.

  You are building a dataflow that will combine data from the lakehouses. The applied steps from one of the queries in the dataflow is shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Folding in Power Query refers to operations that can be translated into source queries. In this case, "some" of the steps can be folded, which means that some transformations will be executed at the data source level. The steps that cannot be folded will be executed within the Power Query engine. Custom steps, especially those that are not standard query operations, are usually executed within Power Query engine rather than being pushed down to the source system. References = Query folding in Power Query Power Query M formula language

• Question 42:

  You have a Fabric workspace named Workspace1 and an Azure Data Lake Storage Gen2 account named storage"!. Workspace1 contains a lakehouse named Lakehouse1.

  You need to create a shortcut to storage! in Lakehouse1.

  Which connection and endpoint should you specify? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  When creating a shortcut to an Azure Data Lake Storage Gen2 account in a lakehouse, you should use the abfss (Azure Blob File System Secure) connection string and the dfs (Data Lake File System) endpoint. The abfss is used for secure

  access to Azure Data Lake Storage, and the dfs endpoint indicates that the Data Lake Storage Gen2 capabilities are to be used.

• Question 43:

  You have a Fabric tenant that contains a lakehouse.

  You are using a Fabric notebook to save a large DataFrame by using the following code.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  The results will form a hierarchy of folders for each partition key. - Yes The resulting file partitions can be read in parallel across multiple nodes. - Yes The resulting file partitions will use file compression. - No

  Partitioning data by columns such as year, month, and day, as shown in the DataFrame write operation, organizes the output into a directory hierarchy that reflects the partitioning structure. This organization can improve the performance of read operations, as queries that filter by the partitioned columns can scan only the relevant directories. Moreover, partitioning facilitates parallelism because each partition can be processed independently across different nodes in a distributed system like Spark. However, the code snippet provided does not explicitly specify that file compression should be used, so we cannot assume that the output will be compressed without additional context. References = DataFrame write partitionBy Apache Spark optimization with partitioning

• Question 44:

  You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a table named Nyctaxi_raw. Nyctaxi_raw contains the following columns.

  

  You create a Fabric notebook and attach it to lakehouse1.

  You need to use PySpark code to transform the data. The solution must meet the following requirements:

  Correct Answer:

  

  Add the pickupDate column: .withColumn("pickupDate",

  df["pickupDateTime"].cast("date"))

  Filter the DataFrame: .filter("fareAmount > 0 AND fareAmount < 100")

  In PySpark, you can add a new column to a DataFrame using the .withColumn method, where the first argument is the new column name and the second argument is the expression to generate the content of the new column. Here, we use

  the .cast("date") function to extract only the date part from a timestamp. To filter the DataFrame, you use the .filter method with a condition that selects rows where fareAmount is greater than 0 and less than 100, thus ensuring only positive

  values less than 100 are included.

• Question 45:

  You have the source data model shown in the following exhibit.

  

  The primary keys of the tables are indicated by a key symbol beside the columns involved in each key.

  You need to create a dimensional data model that will enable the analysis of order items by date, product, and customer.

  What should you include in the solution? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  The relationship between OrderItem and Product must be based on: Both the CompanyID and the ProductID columns The Company entity must be: Denormalized into the Customer and Product entities

  In a dimensional model, the relationships are typically based on foreign key constraints between the fact table (OrderItem) and dimension tables (Product, Customer, Date). Since CompanyID is present in both the OrderItem and Product tables, it acts as a foreign key in the relationship. Similarly, ProductID is a foreign key that relates these two tables. To enable analysis by date, product, and customer, the Company entity would need to be denormalized into the Customer and Product entities to ensure that the relevant company information is available within those dimensions for querying and reporting purposes.

  References = Dimensional modeling Star schema design

• Question 46:

  You have a data warehouse that contains a table named Stage. Customers. Stage-Customers contains all the customer record updates from a customer relationship management (CRM) system. There can be multiple updates per customer

  You need to write a T-SQL query that will return the customer ID, name, postal code, and the last updated time of the most recent row for each customer ID.

  How should you complete the code? To answer, select the appropriate options in the answer area,

  NOTE Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  In the ROW_NUMBER() function, choose OVER (PARTITION BY CustomerID

  ORDER BY LastUpdated DESC).

  In the WHERE clause, choose WHERE X = 1.

  To select the most recent row for each customer ID, you use the ROW_NUMBER() window function partitioned by CustomerID and ordered by LastUpdated in descending order.

  This will assign a row number of 1 to the most recent update for each customer. By selecting rows where the row number (X) is 1, you get the latest update per customer.

  References =

  Use the OVER clause to aggregate data per partition

  Use window functions

• Question 47:

  You have a Fabric tenant that contains a warehouse named Warehouse1. Warehouse1 contains three schemas named schemaA, schemaB. and schemaC.

  You need to ensure that a user named User1 can truncate tables in schemaA only.

  How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  GRANT ALTER ON SCHEMA::schemaA TO User1;

  The ALTER permission allows a user to modify the schema of an object, and granting ALTER on a schema will allow the user to perform operations like TRUNCATE TABLE on any object within that schema. It is the correct permission to

  grant to User1 for truncating tables in schemaA.

  References =

  GRANT Schema Permissions

  Permissions That Can Be Granted on a Schema

• Question 48:

  You have a Fabric tenant.

  You plan to create a Fabric notebook that will use Spark DataFrames to generate Microsoft Power Bl visuals.

  You run the following code.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  The code embeds an existing Power BI report. - No The code creates a Power BI report. - No The code displays a summary of the DataFrame. - Yes

  The code provided seems to be a snippet from a SQL query or script which is neither creating nor embedding a Power BI report directly. It appears to be setting up a DataFrame for use within a larger context, potentially for visualization in Power BI, but the code itself does not perform the creation or embedding of a report. Instead, it's likely part of a data processing step that summarizes data.

  References = Introduction to DataFrames - Spark SQL Power BI and Azure Databricks

  You have a Fabric workspace that uses the default Spark starter pool and runtime version 1,2.

  You plan to read a CSV file named Sales.raw.csv in a lakehouse, select columns, and save the data as a Delta table to the managed area of the lakehouse. Sales_raw.csv contains 12 columns.

  You have the following code.

  

• Question 49:

  For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  The Spark engine will read only the 'SalesOrderNumber', 'OrderDate', 'CustomerName', 'UnitPrice' columns from Sales_raw.csv. - Yes Removing the partition will reduce the execution time of the query. - No Adding inferSchema='true' to the options will increase the execution time of the query. - Yes

  The code specifies the selection of certain columns, which means only those columns will be read into the DataFrame. Partitions in Spark are a way to optimize the execution of queries by organizing the data into parts that can be processed in parallel. Removing the partition could potentially increase the execution time because Spark would no longer be able to process the data in parallel efficiently. The inferSchema option allows Spark to automatically detect the column data types, which can increase the execution time of the initial read operation because it requires Spark to read through the data to infer the schema.

• Question 50:

  You to need assign permissions for the data store in the AnalyticsPOC workspace. The solution must meet the security requirements.

  Which additional permissions should you assign when you share the data store? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Data Engineers: Read All SQL analytics endpoint data

  Data Analysts: Read All Apache Spark

  Data Scientists: Read All SQL analytics endpoint data

  The permissions for the data store in the AnalyticsPOC workspace should align with the principle of least privilege:

  Data Engineers need read and write access but not to datasets or reports.

  Data Analysts require read access specifically to the dimensional model objects and the ability to create Power BI reports.

  Data Scientists need read access via Spark notebooks. These settings ensure each role has the necessary permissions to fulfill their responsibilities without exceeding their required access level.