Ingest and transform data

A data engineering team is building a new analytics solution in Microsoft Fabric. The bronze and silver layers require heavy transformation of large volumes of semi-structured JSON and Parquet files using PySpark. The gold layer will serve a set of downstream analysts and BI developers who are only comfortable writing T-SQL and require full multi-table INSERT/UPDATE/DELETE transactional support with multi-statement (ACID) transactions. The team wants to minimize the number of engines they manage while satisfying both groups. Which storage design should they choose?

A retail company has a Fabric lakehouse containing raw sales files in deeply nested JSON. The data engineering team must flatten and enrich this data on a nightly schedule. The transformation logic is complex, involving multiple joins, custom Python functions for currency conversion, and reuse of libraries already published to a Spark environment. The team consists of experienced Python developers who need full control over the transformation code and expect data volumes to grow to terabytes. Which transformation approach best fits these requirements?

A data engineer must load 200 large CSV files from an Azure Data Lake Storage Gen2 container into a Fabric Lakehouse Delta table on a nightly schedule. The files require no transformation—they must be landed exactly as-is into the table with the highest possible throughput and lowest compute overhead. Which approach should the engineer choose within a Fabric data pipeline?

A retail analytics team needs to combine a monthly Excel workbook, a SharePoint-hosted CSV, and a small on-premises SQL Server table into a single cleansed table in a Fabric Lakehouse. The transformation involves column renaming, type conversion, and merging the three sources. The team consists of business analysts who are comfortable with Power Query but have no experience writing PySpark or SQL code, and the data volumes are small (under 100,000 rows total). Which ingestion and transformation approach best fits this team and workload?

A data engineer is preparing data in a Fabric lakehouse to feed a star-schema dimensional model. The source is a set of highly normalized operational tables (Customers, Addresses, Regions, and SalesReps) that must be combined into a single wide Customer dimension table with columns from all four source tables. The transformation involves multiple joins and needs to run nightly over hundreds of millions of rows. Which approach best prepares this denormalized dimension for the dimensional model?

A retail company runs an Azure SQL Database that records order transactions. The data engineering team wants to continuously stream row-level inserts, updates, and deletes from this operational database into a Fabric Eventstream so that downstream analytics reflect changes with minimal latency, without periodically re-scanning the entire table. Which Eventstream source type should the team configure?

A retail company ingests point-of-sale transactions into a Fabric Eventstream from an Azure Event Hubs source. The data engineering team must route the events so that transactions with an amount greater than 5000 are sent to a KQL database for real-time fraud analysis, while all transactions (regardless of amount) are landed into a lakehouse table for batch reporting. They want to accomplish this entirely within the Eventstream without writing Spark structured streaming code. What is the most appropriate way to implement this?

A retail company streams clickstream events from an Azure Event Hub into Microsoft Fabric using an Eventstream. Analysts need to run sub-second, ad-hoc analytical queries over the last 90 days of raw events, including time-series aggregations and pattern-matching functions, with data available for query within seconds of arrival. Which Eventstream destination should you configure to best meet these requirements?

A telemetry team ingests IoT sensor readings through an Eventstream into an Eventhouse. The analysts need a KQL query that reports the average temperature over the last 5 minutes, but the result must be recalculated and emitted every 1 minute so dashboards show a smooth, continuously updating rolling average. Which windowing approach should you configure?

A retail company streams point-of-sale transactions into a Fabric Eventstream. Analysts need to run sub-second KQL queries against the incoming data with full indexing and the lowest possible query latency, while also making the same data available in the lakehouse for periodic Spark batch reporting without duplicating storage. Which approach should you implement for the Eventhouse destination?

A retail analytics team ingests clickstream events into a Fabric Eventhouse using an Eventstream. Analysts run frequent, low-latency KQL queries that filter and aggregate the last 24 hours of events. The team wants the fastest possible query performance and full indexing/caching benefits on this hot data. When configuring the Eventstream destination into the Eventhouse, which approach should they choose?

A retail analytics team already lands IoT sensor telemetry into a Delta table in a Fabric lakehouse using Spark Structured Streaming. The KQL analysts now need to run interactive, low-latency queries over the same telemetry from an Eventhouse (KQL database) without physically duplicating the data or maintaining a second ingestion pipeline. Data volume is high and the analysts are comfortable with slightly higher query latency than a native KQL table would provide. Which approach best meets these requirements?

A retail company streams point-of-sale transactions into a Microsoft Fabric Eventstream. The business wants only per-store total sales amounts, computed in 5-minute non-overlapping intervals, written to an Eventhouse for near real-time dashboards. Raw individual transactions must NOT be persisted to reduce storage. Which approach should you implement inside the Eventstream?

A retail company ingests point-of-sale transactions into a Fabric Eventstream. The analytics team needs a per-store total sales figure computed for every fixed 5-minute interval, with no overlap between intervals. Due to intermittent network issues at some stores, a small percentage of events arrive up to 90 seconds after their event-time window has closed. The team wants these late events to still be included in the correct interval's aggregate rather than dropped. Which combination of windowing configuration should you use in the Eventstream aggregation?

A logistics company needs a solution in Microsoft Fabric to process GPS pings from thousands of delivery trucks. The data arrives continuously, and dispatchers need a live dashboard showing each truck's most recent location with under 10 seconds of latency. The engineering team also wants to route the same incoming pings to a Lakehouse for later historical analysis. Which approach best satisfies both the low-latency dashboard and the historical archive requirements with the least custom code?

A retail analytics team ingests clickstream events into an Eventhouse via an Eventstream. Raw JSON events land in a table named RawEvents. The team needs every incoming row to be automatically parsed, filtered to remove bot traffic, and written to a curated table with a flattened schema — with the transformation applied at ingestion time inside the KQL database, requiring no external orchestration and minimal added latency. Which approach should they use?

A retail analytics team ingests clickstream data that arrives continuously from a web application into an Azure Event Hub. Business users need dashboards refreshed with a maximum data latency of a few seconds, and the incoming events must be filtered and enriched before landing in an Eventhouse for interactive KQL queries. The team wants a low-code approach that avoids managing Spark clusters. Which ingestion and transformation approach best fits these requirements?

A retail company needs to process a continuous feed of point-of-sale transactions arriving through Azure Event Hubs. The requirement is to land the raw events, apply lightweight filtering and column mapping, and route the results to both a KQL database for real-time dashboards and a lakehouse Delta table for later batch analytics. The team wants a low-code solution that avoids writing and maintaining custom code, and they have no complex stateful transformation needs. Which Fabric feature should they use to implement this pipeline?

A data engineering team ingests a small vendor reference table (about 5,000 rows) into a Fabric Lakehouse Delta table each night. The source system does not expose any reliable modified-date, version, or change-tracking metadata, and vendor records can be silently updated or deleted between runs. The team needs the Lakehouse table to always exactly match the current source state after each nightly run, while keeping the pipeline logic as simple as possible. Which loading pattern should they implement?

A retail company ingests daily order files into a Fabric lakehouse Delta table. Due to an upstream system retry mechanism, some order records are delivered multiple times across different daily files, and occasionally an order file arrives a day late containing corrected versions of orders already loaded earlier. Each order has a unique OrderId and a LastModifiedTimestamp. The engineering team needs the Bronze-to-Silver PySpark transformation to keep only the most recent version of each order and eliminate duplicates. Which approach best meets this requirement?