Microsoft DP-600 시험

Question No : 1

DRAG DROP
You are implementing two dimension tables named Customers and Products in a Fabric warehouse.
You need to use slowly changing dimension (SCD) to manage the versioning of data.
The solution must meet the requirements shown in the following table.



Which type of SCD should you use for each table? To answer, drag the appropriate SCD types to the correct tables. Each SCD type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: Type 2
There are 6 types of Slowly Changing Dimension that are commonly used, they are as follows:
Type 0 C Fixed Dimension
No changes allowed, dimension never changes
Type 1 C No History
Update record directly, there is no record of historical values, only current state
Type 2 C Row Versioning
Track changes as version records with current flag & active dates and other metadata
Type 3 C Previous Value column
Track change to a specific attribute, add a column to show the previous value, which is updated as further changes occur
Etc.
Box 2: Type 1
Reference: https://adatis.co.uk/introduction-to-slowly-changing-dimensions-scd-types/

Question No : 2

You have a Fabric tenant that contains a lakehouse.
You plan to use a visual query to merge two tables.
You need to ensure that the query returns all the rows in both tables.
Which type of join should you use?

• A.inner
• B.full outer
• C.left outer
• D.right anti
• E.right outer
• F.left anti

답을 확인하기

정답:
Explanation:
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.



Reference: https://www.w3schools.com/sql/sql_join_full.asp

Question No : 3

HOTSPOT
You have a Fabric tenant that contains lakehouse named Lakehouse1. Lakehouse1 contains a Delta table with eight columns.
You receive new data that contains the same eight columns and two additional columns.
You create a Spark DataFrame and assign the DataFrame to a variable named df. The DataFrame contains the new data.
You need to add the new data to the Delta table to meet the following requirements:
- Keep all the existing rows.
- Ensure that all the new data is added to the table.
How should you complete the code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: append
Mode "append" atomically adds new data to an existing Delta table and "overwrite" atomically replaces all of the data in a table.
Box 2: overwriteSchema false
Explicitly update schema to change column type or name
You can change a column’s type or name or drop a column by rewriting the table. To do this, use the overwriteSchema option.
The following example shows changing a column type:
(spark.read.table(...)
.withColumn("birthDate", col("birthDate").cast("date"))
.write
.mode("overwrite")
.option("overwriteSchema", "true")
.saveAsTable(...)
), when performing an Overwrite, the data will be deleted before writing out the new data.
Reference: https://learn.microsoft.com/en-us/azure/databricks/delta/update-schema

Question No : 4

You have a Fabric workspace named Workspace1 that contains a lakehouse named Lakehouse1.
In Workspace1, you create a data pipeline named Pipeline1.
You have CSV files stored in an Azure Storage account.
You need to add an activity to Pipeline1 that will copy data from the CSV files to Lakehouse1. The activity must support Power Query M formula language expressions.
Which type of activity should you add?

• A.Dataflow
• B.Notebook
• C.Script
• D.Copy data

답을 확인하기

정답:
Explanation:
Power Query activity in Azure Data Factory
The Power Query activity allows you to build and execute Power Query mash-ups to execute data wrangling at scale in a Data Factory pipeline. You can create a new Power Query mash-up from the New resources menu option or by adding a Power Activity to your pipeline.
Translation to data flow script
To achieve scale with your Power Query activity, Azure Data Factory translates your M script into a data flow script so that you can execute your Power Query at scale using the Azure Data Factory data flow Spark environment.
Example:



Reference: https://learn.microsoft.com/en-us/azure/data-factory/control-flow-power-query-activity

Question No : 5

HOTSPOT
You have an Azure Data Lake Storage Gen2 account named storage1 that contains a Parquet file named sales.parquet.
You have a Fabric tenant that contains a workspace named Workspace1.
Using a notebook in Workspace1, you need to load the content of the file to the default lakehouse. The solution must ensure that the content will display automatically as a table named Sales in Lakehouse explorer.
How should you complete the code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: delta
Use a notebook to load data into your Lakehouse
Saving data in the Lakehouse using capabilities such as Load to Tables or methods described in Options to get data into the Fabric Lakehouse, all data is saved in Delta format.
# Keep it if you want to save dataframe as a delta lake, parquet table to Tables section of the default Lakehouse
df.write.mode("overwrite").format("delta").saveAsTable(delta_table_name)
# Keep it if you want to save the dataframe as a delta lake, appending the data to an existing table df.write.mode("append").format("delta").saveAsTable(delta_table_name)
QUESTION NO: NO: The solution must ensure that the content will display automatically as a table named Sales in Lakehouse explorer.
Box 2: files/sales
Reference:
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-notebook-load-data
https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-and-delta-tables

Question No : 6

DRAG DROP
You are building a solution by using a Fabric notebook.
You have a Spark DataFrame assigned to a variable named df. The DataFrame returns four columns.
You need to change the data type of a string column named Age to integer. The solution must return a DataFrame that includes all the columns.
How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: withColumn
In PySpark, we can use the cast method to change the data type.
from pyspark.sql.types import IntegerType
from pyspark.sql import functions as F
# first method
df = df.withColumn("Age", df.age.cast("int"))
# second method
df = df.withColumn("Age", df.age.cast(IntegerType()))
# third method <-- This one
df = df.withColumn("Age", F.col("Age").cast(IntegerType()))
Box 2: col
Box 3: cast
Reference: https://www.aporia.com/resources/how-to/change-column-data-types-in-dataframe/

Question No : 7

DRAG DROP
You are implementing a medallion architecture in a single Fabric workspace.
You have a lakehouse that contains the Bronze and Silver layers and a warehouse that contains the Gold layer.
You create the items required to populate the layers as shown in the following table.



You need to ensure that the layers are populated daily in sequential order such that Silver is populated only after Bronze is complete, and Gold is populated only after Silver is complete. The solution must minimize development effort and complexity.
What should you use to execute each set of items? To answer, drag the appropriate options to the correct items. Each option may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: A schedule
Orchestration pipeline
Run and schedule the data pipeline
Box 2: A pipeline Copy activity
Bronze layer, pipelines with Copy activities (Lakehouse)
Configure Lakehouse in a copy activity
Use the copy activity in a data pipeline to copy data from and to the Fabric Lakehouse.
Box 3: A pipeline Dataflow activity
Silver layer, dataflows (Lakehouse)
Microsoft Fabric, Data Factory, Use a dataflow in a pipeline
A dataflow is a reusable data transformation that can be used in a pipeline.
Box 4: A pipeline Stored procedure actvitity
Gold layer, stored procedures (warehouse)
Azure Data Factory, Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory or Synapse Analytics
Reference:
https://learn.microsoft.com/en-us/fabric/data-factory/connector-lakehouse-copy-activity
https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-dataflows-gen2-pipeline-activity
https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure

Question No : 8

HOTSPOT
You have a Fabric tenant.
You need to configure OneLake security for users shown in the following table.



The solution must follow the principle of least privilege.
Which permission should you assign to each user? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: ReadAll
User1, Read all the Spark data
If the “Read all Apache Spark” box is checked, users will be given ReadAll. This permission allows users to access data in OneLake. This could be through direct OneLake access, Apache Spark queries, or the lakehouse UX.
Box 2: ReadData
User2, Read all the SQL endpoint data
If the “Read all SQL endpoint data” is checked, users will be given the ReadData permission. ReadData gives access to all Tables in the item when accessing through the SQL Endpoint. Users will not be able to access OneLake directly.
Reference: https://support.fabric.microsoft.com/en-us/blog/building-common-data-architectures-with-onelake-in-microsoft-fabric

Question No : 9

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



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:
- Add a column named pickupDate that will contain only the date portion of pickupDateTime.
- Filter the DataFrame to include only rows where fareAmount is a positive number that is less than 100.
How should you complete the code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: df.withColumnRenamed
Add a column named pickupDate that will contain only the date portion of pickupDateTime.
withColumnRenamed(existing, new)[source]
Returns a new DataFrame by renaming an existing column. This is a no-op if schema doesn’t contain the given column name.
Parameters:
existing C string, name of the existing column to rename.
col C string, new name of the column.
>>> df.withColumnRenamed('age', 'age2').collect() [Row(age2=2, name='Alice'), Row(age2=5, name='Bob')]
Incorrect:
* df.withColumn withColumn(colName, col)[source]
Returns a new DataFrame by adding a column or replacing the existing column that has the same name.
The column expression must be an expression over this DataFrame; attempting to add a column from some other dataframe will raise an error.
Parameters:
colName C string, name of the new column.
col C a Column expression for the new column.
>>> df.withColumn('age2', df.age + 2).collect()
[Row(age=2, name='Alice', age2=4), Row(age=5, name='Bob', age2=7)]
Box 2: cast('date')
cast(dataType)[source]
Convert the column into type dataType.
>>> df.select(df.age.cast("string").alias('ages')).collect() [Row(ages='2'), Row(ages='5')]
>>> df.select(df.age.cast(StringType()).alias('ages')).collect() [Row(ages='2'), Row(ages='5')]
Box 3: .filter("fareAmount > 0 AND fareAmount < 100"
Filter the DataFrame to include only rows where fareAmount is a positive number that is less than 100.
filter(condition)[source]
Filters rows using the given condition.
where() is an alias for filter().
>>> df.filter(df.age > 3).collect() [Row(age=5, name='Bob')]
>>> df.where(df.age == 2).collect() [Row(age=2, name='Alice')]
>>> df.filter("age > 3").collect() [Row(age=5, name='Bob')]
>>> df.where("age = 2").collect() [Row(age=2, name='Alice')]
Incorrect:
*.where
Isin will not give the desired result.
Note: In Apache Spark, the where() function can be used to filter rows in a DataFrame based on a given condition. The condition is specified as a string that is evaluated for each row in the DataFrame. Rows for which the condition evaluates to True are retained, while those for which it evaluates to False are removed.
isin(*cols)[source]
A boolean expression that is evaluated to true if the value of this expression is contained by the evaluated values of the arguments.
>>> df[df.name.isin("Bob", "Mike")].collect() [Row(age=5, name='Bob')]
>>> df[df.age.isin([1, 2, 3])].collect() [Row(age=2, name='Alice')]
Reference: https://spark.apache.org/docs/2.3.0/api/python/pyspark.sql.html

Question No : 10

DRAG DROP
You are creating a data flow in Fabric to ingest data from an Azure SQL database by using a T-SQL statement.
You need to ensure that any foldable Power Query transformation steps are processed by the Microsoft SQL Server engine.
How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

답을 확인하기

정답:


Explanation:
Box 1: Value
Query folding on native queries
Use Value.NativeQuery function
The goal of this process is to execute the following SQL code, and to apply more transformations with
Power Query that can be folded back to the source.
SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'
The first step was to define the correct target, which in this case is the database where the SQL code will be run. Once a step has the correct target, you can select that step―in this case, Source in Applied Steps ―and then select the fx button in the formula bar to add a custom step. In this example, replace the Source formula with the following formula:
Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'
Box 2: NativeQuery
Box 3: EnableFolding
The most important component of this formula is the use of the optional record for the forth parameter of the function that has the EnableFolding record field set to true.



Reference: https://learn.microsoft.com/en-us/power-query/native-query-folding

Question No : 11

You have a Fabric tenant that contains a data pipeline.
You need to ensure that the pipeline runs every four hours on Mondays and Fridays.
To what should you set Repeat for the schedule?

• A.Daily
• B.By the minute
• C.Weekly
• D.Hourly

답을 확인하기

정답:

Question No : 12

HOTSPOT
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.
df.write.partitionBy(“year”, “month”, “day”).mode(“overwrite”).parquet(“Files/ SalesOrder”)
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:

답을 확인하기

정답:


Explanation:
Box 1: Yes
PartitionBy segregates data into folders.
Note: PySpark partitionBy() is a function of pyspark.sql.DataFrameWriter class which is used to partition the large dataset (DataFrame) into smaller files based on one or multiple columns while writing to disk-
Box 2: Yes
Box 3: No
Reference: https://sparkbyexamples.com/pyspark/pyspark-partitionby-example/

Question No : 13

You have source data in a folder on a local computer.
You need to create a solution that will use Fabric to populate a data store.
The solution must meet the following requirements:
- Support the use of dataflows to load and append data to the data store.
- Ensure that Delta tables are V-Order optimized and compacted automatically.
Which two types of data stores should you use? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• A.a lakehouse
• B.an Azure SQL database
• C.a warehouse
• D.a KQL database

답을 확인하기

정답:
Explanation:
Delta Lake table format interoperability
In Microsoft Fabric, the Delta Lake table format is the standard for analytics. Delta Lake is an open-source storage layer that brings ACID (Atomicity, Consistency, Isolation, Durability) transactions to big data and analytics workloads.
All Fabric experiences generate and consume Delta Lake tables, driving interoperability and a unified product experience. Delta Lake tables produced by one compute engine, such as *Synapse Data warehouse* or Synapse Spark, can be consumed by any other engine, such as Power BI. When you ingest data into Fabric, Fabric stores it as Delta tables by default. You can easily integrate external data containing Delta Lake tables by using OneLake shortcuts.
The following matrix shows key Delta Lake features and their support on each Fabric capability.



Etc.
Reference: https://learn.microsoft.com/en-us/fabric/get-started/delta-lake-interoperability

Question No : 14

You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains an unpartitioned table named Table1.
You plan to copy data to Table1 and partition the table based on a date column in the source data.
You create a Copy activity to copy the data to Table1.
You need to specify the partition column in the Destination settings of the Copy activity.
What should you do first?

• A.From the Destination tab, set Mode to Append.
• B.From the Destination tab, select the partition column.
• C.From the Source tab, select Enable partition discovery.
• D.From the Destination tabs, set Mode to Overwrite.

답을 확인하기

정답:
Explanation:
The following properties are supported for Lakehouse under the Destination tab of a copy activity.
* Under Advanced, you can specify the following fields:
- Table actions: Specify the operation against the selected table.
-- Overwrite: Overwrite the existing data and schema in the table using the new values. If this operation is selected, you can enable partition on your target table:
--- Enable Partition: This selection allows you to create partitions in a folder structure based on one or multiple columns. Each distinct column value (pair) is a new partition. For example, "year=2000/month=01/ file". This selection supports insert-only mode and requires an empty directory in the destination.
----Partition column name: Select from the destination columns in schemas mapping. Supported data types are string, integer, boolean, and datetime. Format respects type conversion settings under the Mapping tab.
Incorrect:
Not A:
* Append: Append new values to existing table.
* Etc.
Not C: The following tables contain more information about a copy activity in Lakehouse.
Source information
* Enable partition discovery
Whether to parse the partitions from the file path and add them as extra source columns.
* Etc.
Reference: https://learn.microsoft.com/en-us/fabric/data-factory/connector-lakehouse-copy-activity

Question No : 15

You have a Fabric tenant that contains a lakehouse named Lakehouse1. Lakehouse1 contains a subfolder named Subfolder1 that contains CSV files.
You need to convert the CSV files into the delta format that has V-Order optimization enabled.
What should you do from Lakehouse explorer?

• A.Use the Load to Tables feature.
• B.Create a new shortcut in the Files section.
• C.Create a new shortcut in the Tables section.
• D.Use the Optimize feature.

답을 확인하기

정답:
Explanation:
Load to Delta Lake table
The Lakehouse in Microsoft Fabric provides a feature to efficiently load common file types to an optimized Delta table ready for analytics. The Load to Table feature allows users to load a single file or a folder of files to a table. This feature increases productivity for data engineers by allowing them to quickly use a right-click action to enable table loading on files and folders. Loading to the table is also a no-code experience, which lowers the entry bar for all personas.
Reference: https://learn.microsoft.com/en-us/fabric/data-engineering/load-to-tables