Prepare data

A finance team needs a Fabric data store for their reporting layer. The requirements are: full multi-table transactional (ACID) writes using T-SQL INSERT/UPDATE/DELETE statements, no need to author Spark notebooks, and the team's developers are all experienced SQL professionals who prefer a purely T-SQL development experience. Which Fabric item should you recommend?

You are building a T-SQL view in a Fabric warehouse that calculates total compensation as BaseSalary + Bonus. During testing, several rows return NULL for TotalComp even though BaseSalary has a value, because the Bonus column contains NULLs for employees without bonuses. You must ensure that employees without a bonus show their BaseSalary as the total compensation instead of NULL. Which expression should you use in the view?

You are building a customer dimension in a Fabric warehouse. The source table contains three phone columns: MobilePhone, HomePhone, and WorkPhone. Many rows have NULLs in one or more columns. You must produce a single PrimaryPhone column that returns the first non-null value in the priority order Mobile, then Home, then Work. Which T-SQL expression should you use?

You are loading a fact table into a Fabric warehouse. The source column OrderTimestamp arrives as a VARCHAR value like '2024-03-15T09:42:11'. Analysts need a proper DATE column named OrderDate (no time component) so they can join to a date dimension and group by day. Which T-SQL expression correctly produces the OrderDate value?

You are preparing data in a Fabric warehouse. A staging table named stg_Sales has a column UnitPrice defined as VARCHAR(20) because it was ingested from a CSV file. Some rows contain valid decimal values like '19.99', but a small number contain the literal text 'N/A'. You need to load a clean DECIMAL(10,2) column into the curated fact table and ensure the load does not fail when it encounters 'N/A' values, converting those to NULL instead. Which T-SQL expression should you use in the SELECT that populates the fact table?

An analytics engineer is building a daily sales fact table in a Fabric warehouse. The source data only contains rows for dates on which at least one sale occurred, leaving gaps for days with zero sales. The reporting team needs a continuous row for every calendar day in the reporting period, with sales amounts of 0 for days that had no transactions. Which T-SQL approach best produces this continuous daily result?

You are building a Dataflow Gen2 that ingests a 200-million-row transaction table from an on-premises SQL Server into a Fabric Lakehouse. The downstream report only needs transactions from the last 90 days. You want to minimize the volume of data transferred from the source system during refresh. In Power Query, which approach ensures the row filter is pushed to the source rather than applied after all data is loaded?

You are preparing a customer dimension in a Dataflow Gen2. The source "Country" column contains inconsistent values such as "USA", "U.S.A.", "United States", and "us" that should all resolve to a single standardized value. You want to consolidate these variants to the canonical value "United States" with minimal manual mapping, tolerating minor spelling differences. Which Power Query transformation should you apply?

A Fabric Analytics Engineer is building a Dataflow Gen2 to load customer data into a lakehouse. The source contains a single column named FullName with values formatted as "LastName, FirstName" (for example, "Smith, John"). The target table requires two separate columns, FirstName and LastName. Which transformation should the engineer apply in the Power Query editor to accomplish this most efficiently?

You are building a semantic model on a Fabric warehouse for a retail analytics team. A Sales fact table is joined to a Date dimension. You must create a measure that returns total sales for the same period one year earlier so the team can compute year-over-year growth in a matrix visual sliced by month. Which DAX expression should you use?

You are analyzing a Fabric semantic model using a DAX query in the Fabric portal's DAX query view. You need to return a single-column table containing the distinct list of product category names from the 'Product' table, but only for products where the 'Discontinued' column equals FALSE. Which DAX expression should you use?

You are building a calculated column on the Sales fact table in a Fabric semantic model. Each row in Sales has a ProductKey that links to a Product dimension table via a single active one-to-many relationship (Product → Sales). You need the calculated column to return the CategoryName from the Product table for each sales row, so you can later group sales without physically merging the tables during transformation. Which DAX function should you use in the calculated column?

You are an analytics engineer working in a Fabric semantic model. Business analysts need an ad hoc result set that returns total sales amount grouped by product category and calendar year, but only for rows where the sales amount is greater than zero. You want to write a single DAX query in the DAX query view of the model that produces this grouped, filtered aggregation efficiently. Which DAX function should form the basis of your query?

You are building a DAX query in a Fabric semantic model. The 'Budget' table stores a 'RegionName' column but has no physical relationship to the 'Sales' table, which also contains a 'RegionName' column. You need to write a measure that returns the sum of Sales amounts filtered by the RegionName values currently present in the Budget table's filter context, without creating a physical relationship. Which DAX function should you use to propagate the filter?

You are building a dimensional model in a Fabric warehouse. The source contains three normalized tables: Product, ProductSubcategory, and ProductCategory, linked by foreign keys in a snowflake pattern. Your Power BI report authors complain that navigating three related tables makes measures and filters confusing. You want to optimize the model for query performance and report usability while following star schema best practices. What should you do?

You are building a Dataflow Gen2 transformation in the Visual Query Editor to prepare a customer analytics table. The source table contains one row per order and includes a CustomerID column. You need to produce an output that has exactly one row per CustomerID, keeping the most recent OrderDate for each customer and the total number of orders they placed. Which sequence of Visual Query Editor operations produces this result?

You are building a Fabric Data Factory pipeline to ingest data from an on-premises SQL Server orders table into a Fabric Lakehouse. The source table has a LastModifiedDate column and is updated frequently. You must load only records that changed since the previous pipeline run, while minimizing data movement and load on the source system. Which approach should you implement?

You are analyzing web telemetry stored in an Eventhouse (KQL database). Each row in the PageViews table has RequestBytes and ResponseBytes columns (both long). Analysts need a query result that adds a new column named TotalBytes representing the sum of RequestBytes and ResponseBytes for every row, without changing the existing columns. Which KQL operator should you use?

You maintain an Eventhouse (KQL database) containing a large table named PageViews with millions of rows and a small reference table named PageMetadata that maps PageId to Category. You need to write a KQL query that returns each page view enriched with its Category. The PageMetadata table has only a few hundred rows and is much smaller than PageViews. Which KQL join approach gives the best performance for this scenario?

An analytics engineer is querying a KQL database in Fabric where an IoT telemetry table named DeviceEvents stores a dynamic column called Payload. Each Payload value contains nested JSON such as {"battery": {"level": 87, "charging": false}}. The engineer needs to produce a result set that returns each device's battery level as its own scalar integer column so it can be aggregated. Which KQL expression correctly extracts the battery level?