DP-600 Practice Questions

The scenario has two distinct requirements: preserving existing data and accommodating new schema structure.

1. Preserving Data ("append"): The requirement to "Keep all the existing rows" dictates the use of the append mode. The overwrite mode would replace the table content, violating the requirement.
2. Schema Evolution ("mergeSchema", "true"): The new data introduces two additional columns that do not exist in the target table. By default, Delta Lake enforces a strict schema check and will throw an analysis exception if the source data has columns not present in the target table during an append operation. To resolve this, you must explicitly enable schema evolution by setting the option mergeSchema to true. This instructs the Delta transaction log to update the table's schema to include the new columns before writing the data.

Microsoft Learn. (2024). Update Delta Lake table schema - Automatic schema evolution. Retrieved from https://learn.microsoft.com/en-us/azure/databricks/delta/update-schema#automatic-schema-evolution

Relevance: Confirms that "Automatic schema evolution allows you to add new columns to a table... You can enable schema evolution by adding the .option('mergeSchema', 'true') to your Spark write command."

Delta Lake Documentation. (2024). Table batch reads and writes - Write to a table. Retrieved from https://docs.delta.io/latest/delta-batch.html#write-to-a-table

Relevance: Defines the append mode: "Append: Atomically adds new data to an existing table."

Microsoft Learn (Fabric). (2023). Load data into a Delta Lake table. Retrieved from https://learn.microsoft.com/en-us/fabric/data-engineering/load-data-delta-lake

Relevance: Validates the syntax used in Fabric Notebooks for interacting with Lakehouse tables using Spark.

Import mode is the only storage mode that fully supports calculated tables and complex calculated columns containing aggregate functions (e.g., CALCULATE, SUMX). While Direct Lake is performant, it currently has strict limitations: it does not support calculated tables or calculated columns that use aggregations or context transition. DirectQuery is incorrect because it does not support calculated tables natively and has slower performance, violating the requirement to "minimize page rendering times."

Query Caching must be set to On to meet the requirement of minimizing page rendering times. This feature allows the Fabric/Premium capacity to cache the results of queries; when a user opens a report page, the visual renders instantly from the cache rather than waiting for the engine to process the query.

Microsoft Learn - Direct Lake overview (limitations):

"The following features are not supported in Direct Lake mode: ... Calculated tables ... Calculated columns (that use Calculate or aggregations)..."

Source: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview#known-issues-and-limitations

Microsoft Learn - Query caching in Power BI Premium:

"Query caching instructs the Power BI Premium... to use its local caching service to maintain query results... avoiding having the underlying data source compute those results."

"It allows Power BI to pre-cache the data needed by some report visuals and dashboard tiles so the reports and dashboards open faster."

Source: https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-query-caching

Microsoft Learn - Composite models:

Confirming that calculated tables act as Import tables or require Import mode functionality when complex logic is involved.

Source: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Orchestration pipeline: To ensure the layers are populated daily, a schedule trigger is required to automatically execute the orchestration pipeline at the specified interval.

Bronze layer: The scenario states the Bronze layer uses "Pipelines with Copy activities." To execute an existing child pipeline from a parent orchestration pipeline (minimizing development effort by reusing the existing item), the Invoke pipeline activity (also known as Execute Pipeline) is the correct choice.

Silver layer: The Silver layer uses "Dataflows." To execute a Dataflow Gen2 within a Fabric pipeline, the Dataflow activity is used.

Gold layer: The Gold layer uses "Stored procedures." To execute a stored procedure in a Fabric Warehouse from a pipeline, the Stored procedure activity is the standard method.

Microsoft Learn. (2023). Pipeline activities in Microsoft Fabric. Retrieved from https://learn.microsoft.com/en-us/fabric/data-factory/pipeline-activities

Confirms the availability of specific activities like Dataflow, Stored Procedure, and Invoke Pipeline for orchestration.

Microsoft Learn. (2023). Execute Pipeline activity in Azure Data Factory and Synapse Analytics. Retrieved from https://learn.microsoft.com/en-us/azure/data-factory/control-flow-execute-pipeline-activity

Note: Fabric pipelines share the same core architecture; this document details how the Execute/Invoke Pipeline activity allows a parent pipeline to invoke a child pipeline.

Microsoft Learn. (2023). Dataflow activity in Azure Data Factory and Synapse Analytics. Retrieved from https://learn.microsoft.com/en-us/azure/data-factory/control-flow-execute-data-flow-activity

Describes how the Dataflow activity is used to execute dataflows within a pipeline.

Microsoft Learn. (2023). Stored procedure activity in Azure Data Factory and Synapse Analytics. Retrieved from https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure

Details the use of the Stored Procedure activity to invoke stored procedures in SQL pools/Warehouses.

Microsoft Learn. (2023). Pipeline execution and triggers in Azure Data Factory or Azure Synapse Analytics. Retrieved from https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers

Explains that the "Schedule trigger" is the mechanism used to kick off a pipeline execution on a wall-clock schedule.

Statement 1 (Yes): The partitionBy("year", "month", "day") method explicitly instructs Spark to write data into a directory structure based on the specified columns. This creates a nested hierarchy of folders (e.g., year=2023/month=10/day=25/) rather than a flat file list.

Statement 2 (Yes): Apache Spark is a distributed processing engine. When data is written as partitioned Parquet files, these partitions (files or groups of files) are distributed across storage. During read operations, Spark executors read these partitions simultaneously (in parallel) across multiple nodes in the cluster, maximizing throughput.

Statement 3 (Yes): In Microsoft Fabric and standard Apache Spark configurations, the default compression codec for writing Parquet files is Snappy. Unless explicitly set to "uncompressed" or "none" in the options (which is not done in the provided code snippet), the output files will be compressed.

Apache Spark Documentation, "Parquet Files", Configuration Section. (Confirms spark.sql.parquet.compression.codec defaults to snappy). Available at: spark.apache.org

Microsoft Learn, "Partition data in Azure Synapse Analytics / Fabric". (Describes how partitionBy creates folder hierarchies).

Databricks Documentation, "Partitioning". (Explains directory structures key=value generated by partitionBy).

ProjectPro, "How to save a dataframe as a Parquet file using PySpark?". (Confirms default Snappy compression for write.parquet).

In a Star Schema, a Dimension table (like DimProduct) is designed to provide descriptive context ("the what") for business events. It must contain a unique identifier for the entity and attributes that are used for slicing and filtering data. ProductID serves as the unique key to identify each product, while ProductName and ProductColor are descriptive attributes that allow users to filter sales by name or color. Quantitative measures (like SalesAmount) and transaction-specific keys (like TransactionID or Date) belong in the Fact table or a separate Date dimension, as they do not describe the product itself but rather the sales event.

Option B: Incorrect because SalesAmount is a numeric measure that belongs in a fact table for aggregation, and TransactionID defines the grain of the sales transaction, not the product entity.

Option C: Incorrect because Date is a temporal attribute belonging to a Date dimension or fact table, and TransactionID is specific to the sales event, not a static attribute of a product.

Option D: Incorrect because SalesAmount is a quantitative measure (fact) used for calculations (sum, average), which violates the principle that dimension tables should contain descriptive text and keys, not aggregatable facts.

Understand star schema and the importance for Power BI. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Dimensional modeling in Microsoft Fabric Warehouse: Dimension tables. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables

Fact and dimension tables. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/kusto/concepts/fact-and-dimension-tables?view=microsoft-fabric

To address the performance issues and storage cleanup in the Delta table:

1. Remove the files: The VACUUM command is used in Delta Lake to physically remove data files that are no longer referenced by the transaction log and are older than the retention threshold (default 7 days). This clears out the "data and log files that are no longer used."
2. Combine the files: The OPTIMIZE command compacts small files (bin-packing) into larger ones to improve read performance. The default target file size for OPTIMIZE is 1 GB, which aligns exactly with the requirement. While autoCompact also combines files, it typically targets smaller file sizes (128 MB) and occurs during writes, whereas OPTIMIZE is best run on a schedule to reorganize existing fragmented data into 1 GB files.

Microsoft Learn - Optimize data file layout: "The OPTIMIZE command compacts small files into larger ones... The target file size for the OPTIMIZE operation defaults to 1 GB."

Source: Optimize data file layout - Azure Databricks | Microsoft Learn

Microsoft Learn - Remove unused data files with vacuum: "You can remove files no longer referenced by a Delta table and are older than the retention threshold by running the VACUUM command on the table."

Source: Remove unused data files with vacuum - Azure Databricks | Microsoft Learn

Authentication method: Service principal authentication is the optimal choice for production semantic models in an enterprise environment. It ensures the connection is secure, automated, and not tied to an individual user's lifecycle (preventing refresh failures if a user leaves). It adheres to the "automated and secure access" best practices for the data store.

Mode: DirectQuery is required to meet the specific requirement to "show data as soon as the data is updated in the data store" (real-time). Unlike Import (which has latency) or Direct Lake (which is near real-time but relies on OneLake framing), DirectQuery sends queries directly to the source at execution time. This mode also ensures that table-level security specified in the source is strictly respected by the query engine at the source level.

Microsoft Learn: Service principal support in Fabric "Service principals are the recommended authentication method for production scenarios... they allow for automated access without relying on a user identity." Source: https://learn.microsoft.com/en-us/fabric/security/service-principal-support

Microsoft Learn: DirectQuery model in Power BI "DirectQuery: No data is imported... Power BI sends queries to the underlying data source... This ensures that the data is always current." Source: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Microsoft Learn: Security in Microsoft Fabric Data Warehousing "Granular permissions (table-level security) can be managed via T-SQL (GRANT SELECT) and are enforced when accessed via SQL endpoints or DirectQuery." Source: https://learn.microsoft.com/en-us/fabric/data-warehouse/security-granularity

Microsoft Learn: Row-level security (RLS) with Power BI "For DirectQuery connections... RLS can be implemented at the data source... ensuring the report respects the security logic defined in the database." Source: https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

The goal is to calculate a datetime value (the "first value of each hour") rather than a rank or a sum.

1. row_window_session: This is the only function listed that returns the timestamp of the start of a session window. Other options like row_rank_min or row_cumsum return integers or calculated sums, which would not fit a column named FirstPickupDateTime. The 1h argument specifies the session duration constraint.
2. !=: To partition the calculation by payment_type in a serialized row set, the session must restart whenever the payment_type changes. The expression payment_type != prev(payment_type) returns true when the current value differs from the previous one, triggering the Restart condition of the function.

Microsoft Learn - row_window_session(): Defines the function as calculating the session start values of a column. It confirms the syntax row_window_session(Expr, MaxDistanceFromFirst, MaxDistanceBetweenNeighbors, [Restart]).

Source: row_window_session() (aggregation function) - Azure Data Explorer | Microsoft Learn

Microsoft Learn - Window functions: Describes how serialized row sets allow the use of prev() and restart conditions to simulate partitioning.

Source: Window functions - Azure Data Explorer | Microsoft Learn

To create a shortcut to ADLS Gen2 in a Fabric Lakehouse, you must use the abfss (Azure Blob File System Secure) scheme, which is the secure driver optimized for big data analytics. The shortcut specifically requires the dfs (Distributed File System) endpoint (e.g., dfs.core.windows.net) because it supports the Hierarchical Namespace (HNS). The blob endpoint is used for flat namespace storage and does not support the directory-level operations required for optimized Lakehouse performance.

Microsoft Learn. (2024). Create an Azure Data Lake Storage Gen2 shortcut. Retrieved from https://learn.microsoft.com/en-us/fabric/onelake/create-adls-shortcut

Quote: "Access: ADLS shortcuts must point to the DFS endpoint for the storage account. Example: https://accountname.dfs.core.windows.net/"

Microsoft Learn. (2024). Use the Azure Data Lake Storage URI. Retrieved from https://learn.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-introduction-abfs-uri

Quote: "The Hadoop Filesystem driver that is compatible with Azure Data Lake Storage is known by its scheme identifier abfs... If you add an s at the end (abfss) then the ABFS Hadoop client driver will always use Transport Layer Security (TLS)."

Microsoft Learn. (2024). Connect to OneLake. Retrieved from https://learn.microsoft.com/en-us/fabric/onelake/onelake-access-api

Quote: "As OneLake uses a different endpoint (dfs.fabric.microsoft.com) than ADLS (dfs.core.windows.net), some tools don't recognize the OneLake endpoint..." (Validating the use of the dfs endpoint structure).

Box 1: The delta format should be selected. In Azure Synapse Analytics and Azure Databricks, Delta Lake is the default and preferred format for data preparation and migration tasks that require creating tables. It provides ACID transactions, scalable metadata handling, and unifies streaming and batch data processing, which satisfies standard "data preparation requirements" for modern data lakehouses.

Box 2: The path Tables/research/productline2 must be selected. The question explicitly states the goal is to migrate the "Research division data". Among the available options, Tables/research/productline2 is the only path that includes the specific "research" directory, ensuring the data is stored in the correct hierarchical location for that division.

Microsoft Learn (Delta Lake): "Delta Lake is an open-source storage layer that brings ACID (atomicity, consistency, isolation, and durability) transactions to Apache Spark and big data workloads." – What is Delta Lake?

Source: https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-what-is-delta-lake

Databricks Documentation: "To save a DataFrame as a Delta table, use the format("delta") syntax." – Batch writes.

Source: https://docs.databricks.com/en/delta/tutorial.html#save-as-a-delta-table