DP-700: how to implement a Data Lakehouse with OneLake and Delta

I will explain how to implement a Data Lakehouse in Microsoft Fabric using OneLake and Delta tables. This skill is relevant for the DP-700 exam because it shows how to organize persistent data for analytics and, in practice, improves reliability, performance and governance of analytics solutions. The Lakehouse approach combines operational simplicity with ACID guarantees that are crucial when you have workloads with millions of rows and multiple consumers (ETL, data science, Power BI).

What you need to know

A Data Lakehouse combines the scalability of a data lake with the structure and guarantees of a data warehouse. In the Fabric context, OneLake is the unified storage and Delta tables (Delta format) bring ACID transactions, snapshots and partitioning. Key concepts:

• OneLake: Fabric’s centralized storage layer — stores Parquet, Delta and other files, accessible by all workloads (Data Engineering, Power BI, Spark, etc.). A single repository simplifies retention and file-level encryption policies.
• Delta table: format that implements ACID transactions, allows updates, deletes, merge and time-travel; it maintains a transaction log that coordinates concurrent operations and allows restoring previous states (for example, recovering a version from 3 days ago).
• Lakehouse layout: typical zones (raw, curated, serving); use access controls and retention policies. For example, an organization may have 10 TB in the raw zone, 2 TB in the curated zone and 200 GB optimized in the serving zone for interactive reports.

Simple example: an ingestion flow places CSV files in the raw zone in OneLake; a pipeline transforms those files to Parquet/Delta in the curated zone; analytical queries run over Delta tables in the serving zone. In environments with 100M–500M records, it’s common to compact files into 100–256 MB blocks to optimize reads.

How it works in practice

Essential steps to implement a Lakehouse with OneLake and Delta in Fabric:

1. Plan the OneLake layout: create folders/containers for raw/, curated/ and serving/ with different access policies (e.g., only the ingestion service has write in raw/; the transformation team has write in curated/; analysts have read in serving/).
2. Initial ingestion: use Dataflows, Synapse pipelines, or Spark to copy files to raw/. For continuous loads, consider using streaming (append) or batch intervals from 15 minutes to 24 hours depending on desired latency.
3. Transform to Delta: convert the data into Delta tables, apply partitioning when relevant and keep schemas versioned. For example, partition by year/month for time-series datasets with tens of millions of rows per month.
4. Expose for consumption: register the Delta tables in the data catalog (metastore) and configure permissions for analytics teams, defining groups and access levels (read, read/write, admin).

Example Spark code (concept) to create a Delta table from CSV data in OneLake. This runs in a Spark Notebook in Fabric:

// montar caminho OneLake
val rawPath = "abfss://container@account.dfs.microsoft.com/raw/sales/"
val curatedPath = "abfss://container@account.dfs.microsoft.com/curated/sales_delta/"

// ler CSV
val df = spark.read.option("header", "true").csv(rawPath)

// limpeza e tipagem
import org.apache.spark.sql.functions._
val dfClean = df.withColumn("amount", col("amount").cast("double"))

// escrever como Delta, particionar por ano
dfClean.write.format("delta")
  .mode("overwrite")
  .partitionBy("year")
  .save(curatedPath)

// registar no metastore
spark.sql(s"CREATE TABLE IF NOT EXISTS curated.sales USING DELTA LOCATION '$curatedPath'")

Notes: in Fabric paths and permissions are managed via OneLake; syntax may vary if you use Python/Scala/SQL in different environments (Spark Notebook, Dataflow, etc.). In production, add schema tests and quality validation (e.g., percentage of nulls) before overwriting a Delta table.

Common mistakes

2–3 frequent pitfalls when implementing a Lakehouse in Fabric:

• Not partitioning correctly: choosing partition columns with too high cardinality (for example, customer_id unique per row) creates thousands/millions of small files; choosing columns with very low cardinality forces reading large amounts of data. Rule of thumb: ideally you have hundreds to a few thousand files per partition, and final files of ~100–250 MB.
• Ignoring the transaction log: manipulating Delta files directly without using the Delta engine (for example, copying/renaming files in OneLake) corrupts state. Always use supported operations (WRITE, MERGE, VACUUM via Delta APIs). The transaction log enables time-travel and is critical for consistency with concurrent workloads.
• Misdefined permissions: giving direct access to the raw zone without control can cause corruption or data leaks. Segregate zones and apply access and data protection policies — for example, only 2 identities with write in raw, a data engineering team with write in curated and analysts with read in serving.

How to practice

To consolidate this skill, practice the following:

• Create a workspace in Fabric (or use a trial subscription) and build a simple flow: place CSV files in raw, transform them to Delta in the curated zone and register the table in the metastore. Do this with a test dataset of 1–10 GB (10M–50M rows) to see real behavior.
• Test Delta operations: INSERT, UPDATE, DELETE and MERGE; verify time-travel (restore a previous version) and use VACUUM to remove old files (note: default retention is 7 days).
• Experiment with optimizations: create partitions, compact files (OPTIMIZE) and measure impact on queries. In many cases OPTIMIZE+ZORDER reduces interactive query times by 30–70% depending on access patterns.
• Measure cost and performance: run queries before and after optimization, record times and I/O read to justify layout changes.

For formal exam preparation, use the OFFICIAL and free Microsoft Practice Assessment and the official study guide (both free). These resources help see the areas measured by the exam without resorting to prohibited materials.

In summary

• OneLake + Delta implement a Lakehouse that brings scalability and ACID guarantees for analytics.
• Organize storage into zones (raw/curated/serving) and apply permissions and retention policies to protect data and facilitate audits.
• Proper partitioning, correct use of the transaction log and supported Delta operations (MERGE/OPTIMIZE/VACUUM) are crucial for integrity and performance on datasets of tens to hundreds of millions of rows.
• Practice in a Fabric workspace: create Delta tables, run MERGE/UPDATE/DELETE, try OPTIMIZE and time-travel, and always measure impact on latency and costs.