Prepare and serve data
Choose the store, then the movement pattern
Prepare and serve data in Fabric is mostly about picking the right engine and ingestion method. A lakehouse stores files and Delta tables in OneLake and is transformed with notebooks (PySpark) or Dataflows Gen2 (Power Query). Its SQL analytics endpoint is read-only for data modifications. A warehouse provides full T-SQL DDL/DML, stored procedures, and multi-table transactions. A KQL database serves high-volume, time-series, and real-time queries. Pipelines orchestrate Copy activities and notebooks; dataflows are for low-code transformation; shortcuts virtualize data without copying; mirroring replicates external databases. Organize layers as bronze/silver/gold.
Delta maintenance matters: OPTIMIZE compacts small files, V-Order improves read performance for Fabric engines, Z-ORDER co-locates related data, and VACUUM removes old files. For incremental loads, capture a watermark, land new files, then MERGE into the silver table in a notebook.
Traps: trying to INSERT into a lakehouse table through the SQL endpoint; treating shortcuts as copies (they are references with different lineage and permission behavior); using a dataflow when pipeline orchestration is needed; forgetting that V-Order is a write-time layout, not a query hint; and letting small files accumulate.
Worked example: a nightly pipeline copies CSV files to bronze, a notebook deduplicates and MERGEs into a silver Delta table keyed on order ID, then runs OPTIMIZE with V-Order on gold tables used by Direct Lake.