Domain 2 of 3 · Chapter 2 of 3

Ingest and transform batch data

Unlock the complete study guide.

Bundled into the existing Implementing Data Engineering Solutions Using Microsoft Fabric premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Four decisions behind every batch load
  • Choosing the batch data store
  • OneLake shortcuts as pointers, not copies
  • Mirroring a source database into OneLake
  • Moving data with pipelines and Copy activity
  • Choosing the transformation engine
  • Denormalizing for the analytic model
  • Grouping and aggregating at the requested grain
  • Deduplicating with a deterministic winner
  • Quarantine before late-arrival repair
  • Exam patterns and the traps behind them

Fabric batch data store choice

Decision criterionLakehouseWarehouseSQL database in FabricEventhouseCosmos DB in Fabric
Primary workloadSpark and open-file processingGoverned relational analyticsOperational transactional appsTelemetry and event dataNoSQL, AI data, vector search
Transformation languagePySpark and Spark SQLT-SQLT-SQLKQLNoSQL and vector APIs
T-SQL DML against its tablesNo: SQL endpoint is read-onlyYesYesNot a T-SQL engineNot a T-SQL engine
Multi-table ACID transactionsNot supportedFull supportFull supportNot a relational engineNot a relational engine
Direct unstructured file accessYes, via the OneLake Files areaLoad files with COPY INTONoNoNo

Decision tree

Must the data be copied into Fabric? No OneLake shortcut Target stays authoritative Yes Continuous change capture from a supported database? Yes Database mirroring Managed replica in OneLake No Does the same pass also reshape with Power Query? Yes Dataflow Gen2 Copies and reshapes No Copy activity Bulk movement, no reshape A pipeline sequences these mechanisms It orchestrates activities; it does not transform rows
Cheat sheet

Cheat sheet

  • Choose a lakehouse for Spark-first processing and open file data
  • Choose a warehouse for governed relational analytics with T-SQL writes
  • Choose SQL database in Fabric for operational transactional workloads
  • Use a shortcut when existing data should remain in place
  • Choose a warehouse when writes require multi-table atomicity
  • Choose Cosmos DB in Fabric for NoSQL and vector workloads
  • Choose Dataflow Gen2 for visual low-code Power Query transformations
  • Choose notebooks for advanced distributed transformations and custom libraries
  • Choose T-SQL for set-based transformation inside a warehouse
  • Choose KQL for telemetry and event-shaped transformations
  • Use pipelines to orchestrate rather than replace transformation engines
  • Use warehouse-native bulk and set-based loading operations
  • Treat a shortcut as an independent pointer to its target
  • Place structured Delta shortcuts at the lakehouse Tables root
  • Authorize internal shortcut access at the target location
  • Distinguish deleting a shortcut from deleting through it
  • Use workspace shortcut caching to reduce supported remote egress
  • Bind external shortcuts only through an authorized cloud connection
  • Scope Eventhouse database shortcuts to the required subitems
  • Use database mirroring for managed near-real-time replication
  • Scope mirroring to the required database tables
  • Use the mirrored SQL analytics endpoint for reads, not source writes
  • Match database, metadata, or open mirroring to the movement model
  • Keep Fabric capacity running for replication to continue
  • Use Copy activity for scalable batch movement across supported stores
  • Use a data gateway for network-restricted pipeline sources
  • Configure activity retries for transient ingestion failures
  • Tune Copy activity parallelism before multiplying pipelines
  • Filter at the source to minimize transferred batch data
  • Run batch transformation in the engine that owns the target workload
  • Denormalize dimensions to simplify analytic joins
  • Group and aggregate at the requested dimensional grain
  • Deduplicate with a business key and deterministic survivor rule
  • Quarantine invalid data before applying late-arrival logic

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. Activity overview in Data Factory for Microsoft Fabric
  2. Microsoft Fabric decision guide: choose a data store
  3. What is a lakehouse in Microsoft Fabric?
  4. Cosmos DB in Microsoft Fabric overview
  5. OneLake shortcuts
  6. OneLake shortcut security
  7. What is Mirroring in Microsoft Fabric?
  8. Mirroring Azure SQL Database in Microsoft Fabric
  9. Configure Lakehouse in a Copy activity
  10. Access on-premises data sources in Data Factory for Microsoft Fabric
  11. Copy activity performance and scalability guide
  12. Dataflow Gen2 overview in Data Factory for Microsoft Fabric
  13. Fabric notebook selection guide
  14. Dimensional modeling in Fabric Data Warehouse: load tables
  15. Dimensional modeling in Fabric Data Warehouse: dimension tables
  16. Dimensional modeling in Fabric Data Warehouse: fact tables
  17. Table.Distinct (Power Query M function reference)
  18. Troubleshoot data ingestion errors in Fabric Data Warehouse