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.

Cross-database queries. A Warehouse can query Lakehouse tables and mirrored-database tables in the same workspace by three-part name (LakehouseName.dbo.TableName) through their SQL analytics endpoints. Those reads are read-only: a Warehouse cannot UPDATE or DELETE a Lakehouse or mirrored table, but it can read one and write the result into its own tables — INSERT INTO dbo.FactSales SELECT ... FROM Bronze.dbo.Sales is the standard way to load a Warehouse from a Lakehouse with T-SQL alone. The endpoints themselves accept views (and other read-only objects) but no CREATE TABLE AS SELECT and no DML; the SQL analytics endpoint of a mirrored database is read-only in exactly the same way.

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.

Two practical notes. Custom Python code shared across notebooks — a team's own wheel (.whl) or modules — reaches notebooks through a Fabric environment: upload the library to the environment, publish it, then attach the environment to the notebooks (or set it as the workspace default) so every session starts with it; an inline %pip install only lasts for one session. And timing decides between Eventstream and KQL: Eventstream transformations act on events in flight, before they land, while data already stored in an Eventhouse is reshaped with KQL — a query written into a new table with .set-or-append, or an update policy that transforms each new ingestion. The streaming lesson covers Eventstream in depth.

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 or KQL database (the item types that can hold shortcuts; a Warehouse can be a shortcut target but cannot contain one) — no data is moved or duplicated, and reads always see the current state of the target, not a snapshot taken when the shortcut was created. 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: Azure Data Lake Storage Gen2, Azure Blob Storage, Amazon S3 and S3-compatible stores, Google Cloud Storage, Dataverse, and on-premises or network stores reached through a data gateway. The data stays where it is (and where its lifecycle is managed); Fabric engines read it through the shortcut using the connection's credentials.

Tables or Files — placement decides discovery. A shortcut under a Lakehouse's Tables section must point at a Delta table; Fabric then discovers it as a table, so it is queryable from Spark SQL, the SQL analytics endpoint and Direct Lake models. A shortcut under Files can point at any files and formats, but gets no table discovery — even if the target folder holds a perfectly good Delta table, it does not appear as a table in Spark SQL or the SQL analytics endpoint; you read it by path (for example spark.read.format("delta").load("Files/...")). If a team needs a shared table to show up as a table, the shortcut belongs in Tables.

Eventhouse data in a Lakehouse. A KQL database's tables become shortcut-able Delta data once you turn on OneLake availability (at database or table level): the Eventhouse then also writes a Delta representation to OneLake, and a Lakehouse Tables shortcut to it lets Spark and the SQL analytics endpoint query the same data without a copy pipeline.

Managing shortcuts: what deletes what.

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.

A shortcut never bypasses authorization: every read is checked at the target. How it is checked depends on the family. An internal shortcut passes the calling user's identity to the target item, so the user must hold permission on the target, not just on the Lakehouse that contains the shortcut. The exception is a delegated path: T-SQL queries in delegated identity mode and Direct Lake over SQL pass the item owner's identity instead, while user identity mode and Direct Lake over OneLake pass the caller's. An external shortcut reads with the credentials stored in its connection (an organizational account, service principal, workspace identity, key or SAS token), so anyone who can use the shortcut reads with that connection's rights.

For internal shortcuts, the level of target permission matters:

• Sharing a Lakehouse with no extra boxes ticked grants only Read — the user can see the item and its metadata, but that alone does not let Spark or OneLake read its files and tables through a shortcut.
• Reading the data through Spark or the OneLake APIs needs Read all Apache Spark data (ReadAll), membership of a OneLake security role that covers the folders or tables, or a workspace role on the target workspace.
• Read all SQL endpoint data covers T-SQL queries through the SQL analytics endpoint, a different path from Spark.

Firewalled storage accounts. When an ADLS Gen2 or Blob storage account blocks public network access, a shortcut reaches it through trusted workspace access. It needs four things: a workspace identity for the Fabric workspace; a resource instance rule on the storage account that trusts that workspace (added with an ARM template or PowerShell, not the portal's network blade); a workspace on an F SKU capacity; and a Storage Blob Data role (Reader, Contributor or Owner) on the storage account for the principal the connection authenticates as. Trusted workspace access serves a documented set of Fabric paths — OneLake shortcuts, pipelines, import-mode semantic models, the T-SQL COPY statement and AzCopy; Spark code that reads the storage account directly, without a shortcut, uses a managed private endpoint instead.

Shortcut caching. Reading the same remote files repeatedly costs cross-cloud egress and latency. Shortcut caching is a workspace-level OneLake setting that keeps copies of files read through Amazon S3, S3-compatible, Google Cloud Storage and on-premises gateway shortcuts. You set a retention period of 1 to 28 days; a cached file is served from OneLake while it is still current at the source, and files larger than 1 GB are not cached. It changes cost and speed only — reads still reflect the source.

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 and Fabric takes an initial snapshot, then applies the source's ongoing inserts, updates and deletes to the replica automatically. Built-in sources include Azure SQL Database, Azure SQL Managed Instance, SQL Server, Azure Database for PostgreSQL, Azure Cosmos DB and Snowflake, with newer sources such as Oracle, Google BigQuery and SAP added over time (some in preview — check the current list).

How changes are captured depends on the source, so there is no single "change feed" rule:

• SQL Server 2016–2022 mirrors through change data capture, and SQL Server 2025 through its change feed. An on-premises instance, or one behind a firewall, is reached through an on-premises data gateway (or a virtual network data gateway when the server sits in an Azure VNet).
• Azure Cosmos DB mirroring uses neither the change feed nor the analytical store. Its prerequisite is continuous backup on the account, and replication does not consume request units — the transactional workload is untouched. That is the key contrast with the alternatives: a Copy job, a pipeline Copy activity or the Spark connector all read the container through its API and spend its RUs on every run.

Two variants extend the idea. Open mirroring covers sources with no built-in connector: your application or a partner tool writes change files (Parquet with a row-marker column saying insert, update, delete or upsert) into the mirrored database's landing zone, and Fabric applies them to Delta tables. Metadata mirroring (for example, an Azure Databricks catalog) mirrors the catalog's structure and reads the tables through shortcuts rather than copying them.

What you get is a mirrored database item whose tables are ordinary Delta tables in OneLake: query them through its SQL analytics endpoint with T-SQL, shortcut them into a Lakehouse for Spark, read them from a Warehouse by three-part name, and build Direct Lake semantic models on them. The replica is one-way and read-only — you cannot write back through it — and it is not a backup of the source: deletes at the source are replicated too.

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 and offloads analytical queries from the operational system. 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 (a file export rather than a database, so it needs a copy and shaping into Delta on the way in, through an on-premises data gateway). 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.
• ForEach concurrency. With Sequential ticked, ForEach runs one iteration at a time. Clear it to run iterations in parallel, and set Batch count to cap how many run at once — the setting to use when a source allows only a few concurrent connections or queries.
• Reaching private sources. An on-premises database or file share needs an on-premises data gateway installed on a machine that can reach it. A virtual network data gateway runs inside an Azure VNet for sources in (or privately linked to) that VNet; it reaches on-premises systems only if the VNet already has VPN or ExpressRoute connectivity to them — without that hybrid link, the on-premises gateway is the answer.

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

Join fan-out. Denormalizing is only safe when the dimension is unique on the join key; otherwise each fact row matches several dimension rows and totals inflate. The classic case is an SCD Type 2 dimension, which keeps several versions of each customer. Choose the join by the question the gold table answers: current attributes → join only the current version (IsCurrent = 1); attributes as they were at the time → a point-in-time join where the fact date falls between ValidFrom and ValidTo. A filter on ValidFrom alone still matches every earlier version and fans out.

Filtering around aggregation. WHERE filters rows before grouping; HAVING filters groups after aggregation ("segments with revenue over 1 million"). In PySpark, that is a filter() before or after the agg(); in KQL, a where before or after summarize.

Pre-aggregating to a reporting grain. Sums and counts are additive — daily totals roll up to monthly totals by adding them. Averages, ratios and distinct counts are not: averaging daily averages gives the wrong monthly average whenever days have different row counts. A gold table pre-aggregated by day should therefore store the sum and the count, and let the report compute average = sum ÷ count at any level.

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"]), T-SQL DISTINCT or KQL's distinct. "Keep the latest version per key" needs ranking, because dropDuplicates keeps an arbitrary row per key — sorting with orderBy first does not guarantee which one survives. Rank instead:

• T-SQL: ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY modified_at DESC), keep rn = 1.
• PySpark: the same with row_number() over Window.partitionBy("order_id").orderBy(col("modified_at").desc()), then filter rn == 1.
• KQL: summarize arg_max(modified_at, *) by order_id.

Idempotent loads with MERGE. The durable fix is to MERGE on the business key so reruns and overlapping extracts can't create duplicates. Reduce the batch to one row per key first: a Delta or T-SQL MERGE with a WHEN MATCHED update or delete fails when several source rows match the same target row. For immutable events that retries may resend, an insert-only MERGE — only WHEN NOT MATCHED THEN INSERT on the event ID — adds new events and silently ignores ones already loaded, without rewriting existing rows. It does not deduplicate within the batch, though: two copies of a new event in the same batch are both inserted, so the batch must still be reduced to one row per key first.

Missing values. First decide the semantics — is null "unknown," "zero," "not applicable," or "bad record"? It matters because SUM, AVG and their PySpark and KQL equivalents ignore nulls: if a null quantity really means zero, leaving it null makes the average higher than it should be. Then act:

• Fill defaults where the meaning is known: fillna() / na.fill(), COALESCE, KQL coalesce().
• Drop rows only for hard requirements, and say which columns: PySpark's na.drop() (or dropna()) defaults to dropping a row when any column is null, so pass subset=["order_id", "customer_id"] to drop only rows missing a mandatory value.
• Quarantine failures to an errors table for review rather than silently discarding them.
• For a legitimately missing foreign key, map it to an Unknown member row in the dimension (for example key -1). An inner join would drop those facts and shrink totals; a left join keeps them but leaves NULL keys that reports group as blank.

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 reprocesses the last N days, so stragglers reconcile. Scope the write to match: Delta's replaceWhere overwrites only the affected date range, whereas a full overwrite rewrites the whole table and a plain append duplicates the reprocessed days. When data can be much later than any fixed window, derive the affected dates from the rows ingested since the last run (their event dates) and reprocess just those partitions. When the late arrival is a fact referencing an unseen dimension member, use the inferred-member pattern from the loading-patterns lesson. The common thread: because reconciliation applies the same rows again, every one of these designs only works if the load is idempotent.