Batch Ingestion in Fabric: Data Stores, Shortcuts, Mirroring, and Pipelines

Choose the store by workload and by the language your team will query it with: a Lakehouse for file-based and Spark-centric engineering over Delta tables, a Warehouse for full T-SQL analytics with multi-table transactions, an Eventhouse (housing KQL databases) for high-volume telemetry, logs, and time-series queried in near real time, and a semantic model as the BI consumption layer over any of them — not a landing zone for raw data.

Two distinctions decide most exam questions. First, the Lakehouse's SQL analytics endpoint is read-only — if the scenario requires T-SQL INSERT/UPDATE/MERGE or stored procedures writing data, the answer is a Warehouse. Second, everything above stores or exposes data as Delta in OneLake, so choosing one store does not wall data off from the other engines — shortcuts and the shared format keep a single copy queryable everywhere.

Match the tool to the transformation's complexity, the data's scale and shape, and the team's skills. Dataflows Gen2 is Power Query in Fabric: a low-code, visual tool with hundreds of connectors, right for business-analyst-friendly cleansing and mapping of light-to-moderate volumes, with output written to a Lakehouse, Warehouse, or other destinations. Notebooks (PySpark/Spark SQL) are the code-first choice for large-scale, complex transformations — custom logic, reusable functions, unit-testable code, machine-friendly formats like JSON and Parquet — and generally the most capacity-efficient option at scale. T-SQL (stored procedures and views in a Warehouse, orchestrated by pipelines) fits SQL-skilled teams doing set-based dimensional transforms on structured, already-tabular data. KQL transforms live where telemetry lives: shaping, parsing, and aggregating event data inside an Eventhouse, including materializing transformations with update policies as data streams in.

The exam phrases this as persona-plus-requirement scenarios:

• "Citizen developers", "least code", "analysts who know Power Query" → Dataflow Gen2.
• "Hundreds of gigabytes", "complex business logic", "data scientists", "reusable library" → notebook.
• "Team knows T-SQL", "stored procedures", "transform inside the Warehouse" → T-SQL.
• "Transform events as they're ingested into the Eventhouse", "parse log text" → KQL.

Remember the neighbouring skill from Domain 1: a pipeline is not a transformation engine — it copies and orchestrates. A pipeline calling a notebook or stored procedure is the standard pairing: Copy activity for movement, code for logic.

A shortcut is a pointer in OneLake that makes data stored elsewhere appear as if it lived in your Lakehouse, Warehouse, or KQL database — no data is moved or duplicated, and reads always see the current state of the source. Shortcuts are OneLake's answer to data sprawl: instead of every team copying the same dataset into its own workspace, one physical copy is referenced from wherever it's needed.

Shortcuts come in two families:

• Internal (OneLake) shortcuts point at data in other Fabric items — another Lakehouse's tables or files, a Warehouse, a mirrored database, or a KQL database — across workspaces. A gold Lakehouse can shortcut to a silver Lakehouse in another workspace and query its Delta tables in place.
• External shortcuts point at storage outside Fabric, such as Azure Data Lake Storage Gen2, other cloud object stores, or Dataverse. The data stays where it is (and where its lifecycle is managed); Fabric engines read it through the shortcut, with caching available to reduce repeated cross-cloud reads and egress.

Placement matters: a shortcut created under a Lakehouse's Tables section must reference Delta-formatted data to surface as a queryable table, while shortcuts under Files can point at any files and formats. Security follows the shortcut model too — access is evaluated against the target's permissions via the calling user or a delegated connection, so a shortcut is not a way around source authorization.

Choose a shortcut when the source is already analytics-ready (Delta/Parquet files, another team's gold tables) and freshness should be "whatever the source has right now." It is instant to create, costs no storage, and removes an entire copy pipeline from your estate.

Mirroring continuously replicates an operational database into OneLake as Delta tables, giving you a near-real-time, analytics-ready copy with no pipelines to build or schedule. You configure it once against a supported source — Azure SQL Database, Azure Cosmos DB, or Snowflake, among a growing set — and Fabric performs an initial snapshot, then applies the source's ongoing changes (inserts, updates, deletes) to the replica automatically, using the source's change feed.

What you get is a mirrored database item whose tables are ordinary Delta tables in OneLake: query them through the SQL analytics endpoint with T-SQL, shortcut them into a Lakehouse for Spark transformation, and build Direct Lake semantic models on them. The replica is read-only — it is a landing zone for analytics, not a writable store — and because replication is change-based, it offloads analytical queries from the operational system far better than repeated pipeline extracts that re-scan source tables.

Mirroring solves a specific problem: operational databases are not query-friendly analytical sources. Before mirroring, the standard answer was an incremental pipeline with watermarks and merges; mirroring replaces that whole apparatus for supported sources. Exam scenarios signal it with phrases like "replicate an Azure SQL Database into Fabric with minimal effort," "near real time without building pipelines," or "analysts need current Cosmos DB data without impacting the app." If the source is a file store rather than a database, mirroring is the wrong tool — that's shortcut territory; if the data needs heavy reshaping on the way in, mirroring alone isn't enough — you still transform downstream, typically with Spark over shortcuts to the mirrored tables.

The three access patterns answer one question differently: where does the physical data live, and who keeps it current? A shortcut leaves data at the source and reads it live; mirroring maintains a continuously updated replica in OneLake; a pipeline copy takes a point-in-time snapshot you own and schedule.

Scenario. A retailer's sales run in Azure SQL Database, clickstream files land in ADLS Gen2 as Parquet, and a legacy on-premises system exports CSVs nightly. The DP-700 answer: mirror the Azure SQL Database (fresh operational data, zero pipelines), shortcut the ADLS Gen2 clickstream (already analytics-ready files, no copy needed), and run a pipeline copy for the legacy CSVs (no mirroring support, needs shaping into Delta on the way in). One solution, three patterns, each earning its place.

Pipelines are Fabric's batch ingestion and orchestration workhorse: the Copy activity moves data from hundreds of sources into a Lakehouse, Warehouse, or Eventhouse at scale, and control-flow activities (ForEach, Lookup, If, Notebook, Stored procedure, Dataflow) sequence the work around it. A typical ingestion pipeline looks up configuration, iterates a list of source tables with ForEach, runs a parameterized Copy per table into bronze, then invokes a notebook or stored procedure to transform onward — with schedules or event triggers starting the run.

Copy activity choices the exam cares about:

• Destination and format. Landing in Lakehouse Files keeps raw fidelity (any format); landing in Tables writes Delta and is immediately queryable. Bronze-layer designs usually land files or append-only Delta and defer conforming to Spark.
• Full vs incremental copies. A source query filtered on a watermark column (driven by Lookup activities against a control table) turns Copy into an incremental extractor; the simpler Copy job item packages common full and incremental copy patterns without building the pipeline scaffolding yourself.
• Parameterization. One pipeline with parameters for source, schema, and table beats dozens of cloned pipelines — pass values from ForEach over a metadata list.

Keep the division of labor straight: pipelines move and orchestrate but do not transform row internals — that's the job of the notebook, stored procedure, or Dataflow they call. When a scenario says "copy 200 tables nightly and then run transformations," the shape of the answer is a metadata-driven pipeline with ForEach plus Copy, followed by orchestrated transform activities.

The same core transformations — joining, reshaping, aggregating — express naturally in all three languages, and DP-700 expects you to read each. Denormalization means joining normalized tables into wider ones so analytical queries stop paying repeated join costs; it's the standard silver-to-gold step that also feeds dimensional models.

Joining orders to customers, then aggregating revenue by segment:

PySpark:
  wide = orders.join(customers, "customer_id", "left")
  result = (wide.groupBy("segment")
    .agg(sum("amount").alias("revenue"),
         countDistinct("order_id").alias("orders")))

T-SQL:
  SELECT c.segment, SUM(o.amount) AS revenue,
         COUNT(DISTINCT o.order_id) AS orders
  FROM dbo.orders AS o
  LEFT JOIN dbo.customers AS c
    ON o.customer_id = c.customer_id
  GROUP BY c.segment;

KQL:
  orders
  | lookup kind=leftouter customers on customer_id
  | summarize revenue = sum(amount),
              orders = dcount(order_id) by segment

Language-mapping cues worth memorizing: PySpark's groupBy().agg(), T-SQL's GROUP BY, and KQL's summarize ... by are the same operation; KQL's lookup/join replaces SQL joins; KQL pipes read top-to-bottom as a data flow. Choose the language by where the data lives and who maintains the code (the tool-choice rules earlier), not by capability — all three group, join, window, and aggregate. One practical Fabric note: if the transform must write to a Lakehouse table, it runs in Spark, because the Lakehouse's T-SQL surface is read-only; T-SQL DML transforms belong in a Warehouse.

Data-quality handling is a silver-layer responsibility: bronze keeps what arrived, silver makes it trustworthy. The exam tests the idiom in each language plus the design pattern behind each problem.

Duplicates. Exact duplicates fall to PySpark's dropDuplicates(["order_id"]) or KQL's distinct. "Keep the latest version per key" needs ranking: in T-SQL, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY modified_at DESC) and keep rn = 1; in KQL, summarize arg_max(modified_at, *) by order_id; in PySpark, a window with row_number(). The durable fix is an idempotent load — MERGE on the business key — so reruns and overlapping extracts can't create duplicates in the first place.

Missing values. First decide the semantics — is null "unknown," "not applicable," or "bad record"? Then: fill defaults (fillna(), COALESCE, KQL coalesce()), impute from related data, drop rows that fail hard requirements (dropna() on key columns), or quarantine failures to an errors table for review rather than silently discarding them. Missing dimension attributes surface as explicit "Unknown" members so reports don't quietly lose rows on inner joins.

Late-arriving data. Rows can arrive after their period was already processed — a sale dated yesterday landing after last night's run. Batch designs handle this with a lookback window (each run re-extracts the last N days and MERGEs, so stragglers reconcile), event-dated reprocessing of affected partitions, and — when the late arrival is a fact referencing an unseen dimension member — the inferred-member pattern from the loading-patterns lesson. The common thread: because reconciliation means applying the same rows again, every one of these designs only works if the load is idempotent.