Free DP-600 practice test

Column-level security on a Fabric Warehouse is implemented using T-SQL GRANT/DENY statements on specific columns (e.g., DENY SELECT ON EmployeeCompensation (SalaryAmount, BonusAmount) TO [FinanceRole]). This restricts column access at the warehouse engine level. Row-level security (Option A) restricts which rows are visible based on a predicate, not which columns — it does not address column visibility. Object-level security (Option C) is a semantic model–layer feature that hides tables or columns from the model perspective; it does not enforce security at the warehouse storage/query engine level, so users with direct warehouse access would bypass it. Workspace role assignment (Option D) controls whether users can access the workspace and its items, but does not restrict specific columns within a table.

To restrict specific columns across all query paths, two complementary controls are needed. First, revoking SELECT permission on CreditCardNumber and SSN columns via the SQL analytics endpoint enforces column-level security for SQL-based queries. Second, defining column restrictions within a OneLake data access role applies the restriction at the OneLake storage layer, covering Spark and other non-SQL query paths. Together, these two controls enforce the requirement across all access methods. A sensitivity label classifies and potentially encrypts the item for export/sharing purposes but does not prevent analysts from reading column values during a live query. Defining CLS on a shared semantic model only protects access through that specific model; analysts querying the lakehouse directly via Spark or SQL would bypass it. Removing and relocating columns is a data architecture change that is destructive and operationally complex—not an access control mechanism. Hiding the entire table via object-level security would deny access to all columns, violating the requirement that other columns remain accessible.

Option C (Member) is correct. The Member role allows users to publish and edit reports and semantic models, and to share content with others — but it does not allow managing workspace membership or changing workspace-level settings, which requires the Admin role. Option A (Viewer) is wrong because Viewers can only read and interact with content; they cannot publish, create, or share items. Option B (Contributor) is wrong because Contributors can publish and edit content but cannot share items with others — sharing requires at least the Member role. Option D (Admin) is wrong because while Admins have all required permissions, it also grants the ability to add/remove members and change workspace settings, which violates the principle of least privilege given the stated requirements.

Sensitivity label inheritance means that when a label is applied to a semantic model, downstream artifacts (reports, dashboards) that are built on it automatically inherit the same label — ensuring consistent classification across the lineage. Additionally, when data is exported from a labeled semantic model (e.g., Analyze in Excel, export to .xlsx), the exported file carries the label and its associated protection policies. Row-level security is a separate feature that must be explicitly configured; sensitivity labels do not activate RLS. Sensitivity labels do not move items to isolated capacities. Labels do not revoke existing workspace permissions; access control and classification are independent mechanisms.

The Power BI Project (.pbip) format saves the report and semantic model as a folder of human-readable text files (JSON, TMDL, etc.), making it ideal for Git-based source control because individual property changes appear as granular diffs. A .pbix file is a binary format; while it can technically be committed to Git, changes are not human-readable, so diffing and reviewing individual changes is not practical. A .pbit template file is also partially binary and does not produce meaningful line-by-line diffs in Git. Using the workspace's built-in version history (Git integration via Fabric workspace settings) is valuable but requires the project to be in .pbip format on disk first; simply publishing a .pbix does not give the developer local, discrete, human-readable file changes.

Impact analysis is the feature in Microsoft Fabric (and Power BI service) that shows all downstream items—such as semantic models, reports, and other dataflows—that depend on a specific item. By opening the dataflow in the Test workspace and selecting 'Impact analysis,' the administrator can see a lineage view of all affected downstream items and notify their owners. The deployment pipeline's 'Compare' view shows differences in item definitions between pipeline stages (e.g., what changed in the dataflow itself), not which downstream items depend on it. The scheduled refresh history shows past refresh run statuses, not dependency relationships. Git commit history shows what code/configuration changed over time but does not reveal downstream consumer relationships.

A is correct. It filters to TCP traffic from the last 24 hours, sums BytesSent per SourceIP in 1-hour bins with summarize ... by SourceIP, bin(EventTime, 1h), and then keeps only the bins whose total exceeds 1 GB. B is wrong: it summarizes per SourceIP over the whole 24-hour window with no hourly binning, so it does not meet requirement (2). C is wrong: it applies the 1 GB filter to individual events before summarizing, so it only counts single events larger than 1 GB and misses SourceIPs whose many smaller events add up to more than 1 GB in an hour. D is wrong: bin() is a scalar function used inside summarize or an expression; it cannot be used as a standalone tabular operator in the pipe chain, so the query fails.

WHEN NOT MATCHED BY SOURCE fires for rows that exist in the target (DimProducts) but not in the source (StageProducts) — exactly the condition for a 'soft delete.' Adding THEN UPDATE SET tgt.IsActive = 0 implements the deactivation without physically deleting rows, which is the correct Type 1 SCD soft-delete pattern. — B is wrong because WHEN NOT MATCHED BY SOURCE THEN DELETE would physically remove the rows from DimProducts, which destroys historical records and is not a Type 1 SCD pattern (and may also violate referential constraints). C is wrong because filtering StageProducts in the USING clause to only active records would prevent new products from being inserted correctly and does not address the deactivation logic. D would work functionally but is not the correct approach when MERGE already supports WHEN NOT MATCHED BY SOURCE; using a separate UPDATE is unnecessary, less atomic, and not the idiomatic T-SQL solution the question asks about.

Option C is correct. Using df.replace('N/A', None, subset=['UnitPrice']) converts the string 'N/A' to a Python None (which becomes NULL in Spark), and then casting to DoubleType will correctly produce NULL for those rows — preserving all rows while fixing the data type. Option A is wrong: Spark does not automatically convert 'N/A' to NULL when casting; the cast will produce NULL but also throws runtime errors or returns NULL for valid numeric strings if the original value was unexpectedly non-numeric — more critically, 'N/A' becomes NULL during cast anyway, but the engineer's intent is explicit replacement before cast for clarity and correctness, and relying on implicit cast behavior for 'N/A' is not a best practice. Option B is wrong: dropna removes rows with actual NULL values, not the string 'N/A' — the rows would not be dropped. Option D is wrong: filtering removes the rows entirely instead of replacing the value with NULL, which loses data that should be retained with a NULL price.

Eventhouse (KQL database) is purpose-built for time-series and log analytics with high-precision timestamps, making it ideal for Workload 1. Fabric Warehouse provides a full T-SQL engine with stored procedures and multi-table joins for structured relational workloads like the product catalogue (Workload 2). Fabric Lakehouse is designed as a multi-format data landing zone accessible by both Spark and SQL via the analytics endpoint (Workload 3). Option A is incorrect because placing a time-series audit log in a Lakehouse misses the native time-series operators and optimised ingestion of Eventhouse, and Eventhouse is not suited for raw multi-format file landing. Option B is incorrect because a Warehouse is not optimised for log/event time-series analytics, and Eventhouse does not support T-SQL stored procedures. Option D is incorrect because Lakehouse SQL analytics endpoint is read-only and does not support stored procedures, making it unsuitable for Workload 2.

Fabric Warehouse is the best fit because it is a fully managed, SQL-first data warehouse that supports complex multi-table joins, ACID transactions at the table level, and fine-grained T-SQL GRANT/DENY role-based security — all requirements listed in the scenario. Lakehouse with SQL analytics endpoint provides read-only SQL access over Delta tables; it does not support DDL/DML write operations via the SQL endpoint and has limited support for T-SQL GRANT-based security at the object level. Eventhouse is optimised for streaming, time-series, and KQL-based analysis; it does not support T-SQL GRANT security or the relational multi-table join patterns expected in traditional data warehousing. Datamart is positioned for small, self-service departmental datasets; it lacks the scalability (2 TB+) and enterprise security controls required by this scenario.

Option A is correct: The OneLake catalog provides an 'Endorsed: Certified' filter that specifically surfaces items that have been certified by the organization's governance team, which is exactly the requirement. Option E is correct: Using the search bar with the keyword 'Finance' will filter catalog items whose names or descriptions match the subject area. Option F is wrong because 'Promoted' endorsement is a lighter-weight endorsement that does not require governance team approval — it does not satisfy the 'certified' requirement. Option D is wrong because the Real-Time hub is used for discovering streaming data sources, not for general catalog discovery with endorsement filters. Option C is wrong because filtering by 'KQL Database' would unnecessarily exclude other item types (like Lakehouse tables or Warehouses) that may be certified Finance assets. Option B is wrong because sensitivity labels indicate data sensitivity for compliance, not governance certification status.

Option F is correct: Partitioning the fact table on a high-cardinality date column (e.g., SalesYear) enables partition pruning, so queries filtering by date only scan relevant partitions rather than the entire 800M-row table. Option B is correct: Z-ordering co-locates related data on disk for non-partition filter columns (like ProductID, CustomerID), reducing the amount of data read during file scans and improving join performance. Option A is correct: Running OPTIMIZE compacts many small Delta files into larger, efficiently readable Parquet files, significantly reducing file listing and I/O overhead on large tables. Option D is wrong because storing everything in a single denormalized flat file eliminates the star schema benefits, makes updates and maintenance difficult, and can actually harm query performance for certain analytical patterns. Option C is wrong because while foreign key constraints are valuable for data integrity, Fabric Lakehouse Delta tables do not enforce foreign key constraints at the engine level — defining them provides no query performance benefit. Option E is wrong because replacing materialized Delta tables with views means dimension data must be recomputed on every query rather than read from stored, optimized Parquet files, which would degrade rather than improve performance.

Option A is correct: it uses valid T-SQL MERGE syntax with MERGE INTO, USING, ON for the match condition, WHEN MATCHED for the update, and WHEN NOT MATCHED BY TARGET for inserts of new rows — this is the correct and complete syntax for an upsert in Fabric Warehouse T-SQL. Option C is incorrect because the UPDATE clause syntax 'UPDATE target SET ...' is invalid in T-SQL MERGE — the correct syntax within the MATCHED clause is 'UPDATE SET target.column = source.column' without repeating the target alias after UPDATE. Option B is incorrect because WHEN NOT MATCHED BY SOURCE means rows in the target that have no match in the source — this would incorrectly apply INSERT logic to target-only rows (which doesn't make sense; inserts from source use WHEN NOT MATCHED BY TARGET). Option D is incorrect because UPSERT is not a valid T-SQL statement — it is a concept, not a T-SQL keyword; ON CONFLICT is PostgreSQL syntax, not valid in Fabric Warehouse.

Option A is correct: it reads the CSV, splits into valid (Revenue >= 0) and invalid (Revenue < 0) sets using clear, complementary filter conditions, and writes both to Delta tables using saveAsTable — which is the correct approach for writing to Lakehouse Delta tables. Option C is incorrect because it quarantines rows where Revenue == None (which is not how you check for NULL in PySpark — you need isNull()), not rows where Revenue < 0; it fails to correctly identify negative revenue rows. Option D is incorrect because dropna(subset=['Revenue']) removes NULL rows, not negative rows — so valid non-null negative rows could still pass to FactRevenue, violating the business rule. Option B is incorrect because it writes the valid data in Parquet format to a path (not as a managed Delta table), which would not register the table properly in the Lakehouse, and it quarantines NULLs rather than negative values as specified.

WINDOW(1, ABS, 0, REL, ALL(DimDate[Date]), ORDERBY(DimDate[Date], ASC)) defines a window that starts at absolute position 1 (the first row in the ordered partition, i.e., the first date of all time) and ends at relative position 0 (the current row). This creates a running total from the very beginning of the data to the current date. Using ALL ensures all dates are considered regardless of filter context, which is appropriate for a cumulative total. Option C uses ALLSELECTED, which would respect slicer filters and could truncate the window start if a date slicer is applied. Option A (DATESYTD) is a valid YTD approach but does not use WINDOW as specified. Option D uses REL for the start position (-1, REL) which means 'one row before the current row,' producing a one-row lag rather than starting from the beginning of the dataset.

Incremental refresh requires two mandatory steps: (1) creating RangeStart and RangeEnd parameters of type Date/Time in Power Query — these are the sentinel parameters that Power BI uses to define partition boundaries; and (2) applying a filter on the date column using those parameters so Power BI can fold the query and create incremental partitions. Large semantic model storage format is optional and not required for incremental refresh to function. Change detection is an optional enhancement to trigger refresh only when data changes, not a requirement. Publishing to Premium/Fabric capacity is necessary to actually use the defined policy, but the question asks about configuring incremental refresh — the policy definition itself (steps 1 and 2) is workspace-agnostic. Converting the date column to text breaks query folding and would prevent incremental refresh from working.

Direct Lake reads data based on the Delta table's transaction log checkpoint (framing). If the checkpoint has not been written recently—because the Delta table uses many small transaction log entries without a consolidated checkpoint—Direct Lake may reference an older data snapshot. The engineer should run OPTIMIZE or ensure the pipeline checkpoints the Delta log so Direct Lake picks up a fresh frame. Exceeding the capacity SKU row limit (A) causes fallback to DirectQuery, which would show current data (not stale data), making this incorrect. V-Order (D) is a write optimization for query speed; its absence does not cause stale data. Automatic page refresh (C) controls how often visuals re-query the model but does not cause the model itself to show stale data from a checkpoint perspective.

Direct Lake reads Delta table data directly from OneLake using VertiPaq in-memory processing, providing near real-time data currency without traditional scheduled refreshes and without the latency of DirectQuery. Import requires scheduled refreshes to stay current and has a data size limit concern at 500 million rows. DirectQuery against the SQL analytics endpoint avoids refresh but sacrifices performance because every query hits the SQL endpoint, risking fallback behavior. Dual mode is only meaningful in composite models to allow a table to act as either Import or DirectQuery depending on the query context; it cannot by itself eliminate DirectQuery fallback for a single table.

ALL(DimProduct) removes all filters from every column of DimProduct, so the denominator calculates total sales across all products regardless of any slicer on ProductName or ProductCategory. This gives the correct 'percentage of all products' result. Option A uses ALLSELECTED, which only removes filters applied by the visual but respects slicers — this would give 'percentage of selected products,' not all products. Option C uses REMOVEFILTERS on only ProductCategory, which still leaves filters on ProductName active, so the denominator would not represent all products. Option D uses ALL(DimProduct[ProductCategory]) which removes the category filter but leaves filters on other DimProduct columns (like ProductName) intact, which could produce incorrect results when ProductName is on a visual or slicer.