1. A Fabric data engineer at Contoso is designing a KQL query against an Eventhouse table named NetworkTraffic with columns: EventTime (datetime), SourceIP (string), DestinationIP (string), BytesSent (long), Protocol (string). The engineer must write a query that: (1) filters to only TCP traffic from the last 24 hours, (2) summarizes total BytesSent per SourceIP in 1-hour bins, and (3) returns only SourceIPs where the total BytesSent in any single bin exceeds 1 GB (1,073,741,824 bytes). Which KQL query correctly satisfies all three requirements?
- A. NetworkTraffic | where EventTime >= ago(24h) and Protocol == 'TCP' | summarize TotalBytes = sum(BytesSent) by SourceIP, bin(EventTime, 1h) | where TotalBytes > 1073741824✓ Correct
- B. NetworkTraffic | where EventTime >= ago(24h) and Protocol == 'TCP' | summarize TotalBytes = sum(BytesSent) by SourceIP | where TotalBytes > 1073741824
- C. NetworkTraffic | where EventTime between (ago(24h) .. now()) and Protocol == 'TCP' | summarize TotalBytes = sum(BytesSent) by SourceIP, bin(EventTime, 1h) | where TotalBytes > 1073741824✓ Correct
- D. NetworkTraffic | where EventTime >= ago(24h) | where Protocol == 'TCP' | bin(EventTime, 1h) | summarize TotalBytes = sum(BytesSent) by SourceIP | where TotalBytes > 1073741824
Explanation
Both A and C correctly satisfy all three requirements. A uses ago(24h) for the time filter (a standard KQL idiom), groups by SourceIP and bin(EventTime, 1h) to produce hourly bins, and then filters bins exceeding 1 GB. C is equivalent but uses the between operator with ago(24h)..now() — also a valid KQL time range syntax that produces the same result. — B is wrong because it summarizes total BytesSent across the entire 24-hour window per SourceIP (no hourly binning), so it does not satisfy requirement (2) of binning into 1-hour intervals. D is wrong because bin() is a scalar function used inside summarize or as an expression — it cannot be called as a standalone tabular operator in a pipe chain; this query would produce a syntax error.
2. A Fabric data engineer at Wide World Importers is working in a Fabric Warehouse. They have a staging table named StageProducts (columns: ProductID int, ProductName varchar, CategoryID int, UnitPrice decimal, RowHash varchar) and a target dimension table named DimProducts with the same columns plus IsActive bit and EffectiveDate date. The engineer must implement a Type 1 SCD pattern: update existing rows when any non-key attribute changes (detected via RowHash) and insert new products. They write the following T-SQL:
```sql
MERGE INTO DimProducts AS tgt
USING StageProducts AS src
ON tgt.ProductID = src.ProductID
WHEN MATCHED AND tgt.RowHash <> src.RowHash
THEN UPDATE SET
tgt.ProductName = src.ProductName,
tgt.CategoryID = src.CategoryID,
tgt.UnitPrice = src.UnitPrice,
tgt.RowHash = src.RowHash
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, ProductName, CategoryID, UnitPrice, RowHash, IsActive, EffectiveDate)
VALUES (src.ProductID, src.ProductName, src.CategoryID, src.UnitPrice, src.RowHash, 1, GETDATE());
```
The engineer runs the statement and it executes without error, but they notice that products deleted from the source are not being deactivated (IsActive set to 0) in DimProducts. What should they add to the MERGE statement to fix this?
- A. Add a WHEN NOT MATCHED BY SOURCE THEN UPDATE SET tgt.IsActive = 0 clause to the MERGE statement.✓ Correct
- B. Add a WHEN NOT MATCHED BY SOURCE THEN DELETE clause to the MERGE statement.
- C. Add a WHERE tgt.IsActive = 1 filter to the USING clause on StageProducts to exclude already-inactive rows.
- D. Run a separate UPDATE statement after the MERGE: UPDATE DimProducts SET IsActive = 0 WHERE ProductID NOT IN (SELECT ProductID FROM StageProducts).
Explanation
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.
3. A Fabric data engineer at Litware Inc. needs to connect to a REST API data source from within a Fabric Dataflow Gen2 and load the results into a Lakehouse table. The API requires OAuth 2.0 client credentials authentication. Which steps are required? (Select TWO)
- A. Create a new connection in Dataflow Gen2 using the Web connector, and configure the OAuth 2.0 credentials in the connection settings✓ Correct
- B. Deploy an on-premises data gateway and configure it to proxy all REST API calls from Fabric
- C. Add the REST API URL as a OneLake shortcut and reference it as a Delta table
- D. In the Dataflow Gen2 Power Query editor, use the Web.Contents() function with the appropriate headers for OAuth authentication✓ Correct
- E. Create an Azure API Management instance to wrap the REST API before connecting from Fabric
Explanation
To connect to a REST API in Dataflow Gen2, you configure a Web connector connection with the appropriate authentication (OAuth 2.0 client credentials) in the connection settings (option A), and within the Power Query editor you use Web.Contents() to make the HTTP call, passing required headers or tokens (option D). An on-premises data gateway is only required if the REST API is behind a private network/on-premises firewall — for a public REST API it is not required (option B). A OneLake shortcut points to storage locations (ADLS, S3, etc.) and cannot represent a REST API endpoint (option C). Wrapping the API in Azure API Management is an unnecessary architectural overhead not required by Fabric for REST connectivity (option E).
4. A Fabric data engineer is preparing data in a Fabric Lakehouse using a PySpark notebook. A Delta table named RawSales contains a column `UnitPrice` of type string. Many rows contain the value 'N/A' instead of a numeric price. The engineer must convert `UnitPrice` to a double type and replace all 'N/A' values with NULL before writing the cleaned table.
Which PySpark approach correctly accomplishes this?
- A. Cast the column directly to DoubleType without any pre-processing, relying on Spark to handle 'N/A' automatically
- B. Use `df.dropna(subset=['UnitPrice'])` to remove all rows where UnitPrice is 'N/A', then cast to DoubleType
- C. Replace 'N/A' with None using `df.replace('N/A', None, subset=['UnitPrice'])`, then cast the column to DoubleType✓ Correct
- D. Filter out rows where UnitPrice equals 'N/A' using `df.filter(col('UnitPrice') != 'N/A')`, then cast to DoubleType
Explanation
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.
5. A data engineer at Contoso is building a Fabric solution to analyze website clickstream events. The events arrive in near real-time and analysts need to run time-series queries with millisecond granularity, including sliding-window aggregations and pattern detection over the last 30 days. Which Fabric data store is MOST appropriate for this workload?
- A. A Fabric Lakehouse with Delta tables, because Delta supports streaming ingestion via Structured Streaming.
- B. A Fabric Warehouse, because it supports T-SQL window functions that can perform time-series aggregations.
- C. A Fabric Eventhouse with a KQL database, because it is purpose-built for high-frequency time-series data with native time-window query operators.✓ Correct
- D. A Fabric Lakehouse SQL analytics endpoint, because it exposes the Delta tables via a read-only T-SQL interface optimized for analytics.
Explanation
An Eventhouse KQL database is purpose-built for high-frequency, time-series, and telemetry workloads. KQL includes native operators such as summarize, bin(), sliding_window_counts(), and series_* functions that are ideal for millisecond-granularity time-series analysis and pattern detection. A Lakehouse with Delta tables supports streaming but lacks native time-series operators. A Fabric Warehouse is optimized for relational analytics with T-SQL and does not offer native time-series or KQL capabilities. The SQL analytics endpoint of a Lakehouse is a read-only T-SQL interface over Delta tables and is not optimized for high-frequency time-series queries.