fabric-lakehouse

When to Use This Skill

Use this skill when you need to:

• Generate a document or explanation that includes definition and context about Fabric Lakehouse and its capabilities.
• Design, build, and optimize Lakehouse solutions using best practices.
• Understand the core concepts and components of a Lakehouse in Microsoft Fabric.
• Learn how to manage tabular and non-tabular data within a Lakehouse.

Fabric Lakehouse

Core Concepts

What is a Lakehouse?

Lakehouse in Microsoft Fabric is an item that gives users a place to store their tabular data (like tables) and non-tabular data (like files). It combines the flexibility of a data lake with the management capabilities of a data warehouse. It provides:

• Unified storage in OneLake for structured and unstructured data
• Delta Lake format for ACID transactions, versioning, and time travel
• SQL analytics endpoint for T-SQL queries
• Semantic model for Power BI integration
• Support for other table formats like CSV, Parquet
• Support for any file formats
• Tools for table optimization and data management

Key Components

• Delta Tables: Managed tables with ACID compliance and schema enforcement
• Files: Unstructured/semi-structured data in the Files section
• SQL Endpoint: Auto-generated read-only SQL interface for querying
• Shortcuts: Virtual links to external/internal data without copying
• Fabric Materialized Views: Pre-computed tables for fast query performance

Tabular data in a Lakehouse

Tabular data in a form of tables are stored under "Tables" folder. Main format for tables in Lakehouse is Delta. Lakehouse can store tabular data in other formats like CSV or Parquet, these formats are only available for Spark querying. Tables can be internal, when data is stored under "Tables" folder, or external, when only reference to a table is stored under "Tables" folder but the data itself is stored in a referenced location. Tables are referenced through Shortcuts, which can be internal (pointing to another location in Fabric) or external (pointing to data stored outside of Fabric).

Schemas for tables in a Lakehouse

When creating a lakehouse, users can choose to enable schemas. Schemas are used to organize Lakehouse tables. Schemas are implemented as folders under the "Tables" folder and store tables inside of those folders. The default schema is "dbo" and it can't be deleted or renamed. All other schemas are optional and can be created, renamed, or deleted. Users can reference a schema located in another lakehouse using a Schema Shortcut, thereby referencing all tables in the destination schema with a single shortcut.

Files in a Lakehouse

Files are stored under "Files" folder. Users can create folders and subfolders to organize their files. Any file format can be stored in Lakehouse.

Fabric Materialized Views

Set of pre-computed tables that are automatically updated based on a schedule. They provide fast query performance for complex aggregations and joins. Materialized views are defined using PySpark or Spark SQL and stored in an associated Notebook.

Spark Views

Logical tables defined by a SQL query. They do not store data but provide a virtual layer for querying. Views are defined using Spark SQL and stored in Lakehouse next to Tables.

Security

Item access or control plane security

Users can have workspace roles (Admin, Member, Contributor, Viewer) that provide different levels of access to Lakehouse and its contents. Users can also get access permission using sharing capabilities of Lakehouse.

Data access or OneLake Security

For data access use OneLake security model, which is based on Microsoft Entra ID (formerly Azure Active Directory) and role-based access control (RBAC). Lakehouse data is stored in OneLake, so access to data is controlled through OneLake permissions. In addition to object-level permissions, Lakehouse also supports column-level and row-level security for tables, allowing fine-grained control over who can see specific columns or rows in a table.

Lakehouse Shortcuts

Shortcuts create virtual links to data without copying:

Types of Shortcuts

• Internal: Link to other Fabric Lakehouses/tables, cross-workspace data sharing
• ADLS Gen2: Link to ADLS Gen2 containers in Azure
• Amazon S3: AWS S3 buckets, cross-cloud data access
• Dataverse: Microsoft Dataverse, business application data
• Google Cloud Storage: GCS buckets, cross-cloud data access

Performance Optimization

V-Order Optimization

For faster data read with semantic model enable V-Order optimization on Delta tables. This presorts data in a way that improves query performance for common access patterns.

Table Optimization

Tables can also be optimized using the OPTIMIZE command, which compacts small files into larger ones and can also apply Z-ordering to improve query performance on specific columns. Regular optimization helps maintain performance as data is ingested and updated over time. The Vacuum command can be used to clean up old files and free up storage space, especially after updates and deletes.

Lineage

The Lakehouse item supports lineage, which allows users to track the origin and transformations of data. Lineage information is automatically captured for tables and files in Lakehouse, showing how data flows from source to destination. This helps with debugging, auditing, and understanding data dependencies.

PySpark Code Examples

The following examples demonstrate common PySpark operations in a Fabric Lakehouse environment.

Read a Delta table

df = spark.read.format("delta").load("Tables/my_table")
df.show()

Write a Delta table

df.write.format("delta").mode("overwrite").save("Tables/my_table")

Append data to a Delta table

df.write.format("delta").mode("append").save("Tables/my_table")

Merge (upsert) into a Delta table

from delta.tables import DeltaTable

delta_table = DeltaTable.forPath(spark, "Tables/my_table")
delta_table.alias("target").merge(
    df.alias("source"),
    "target.id = source.id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

Read a CSV file from the Files section

df = spark.read.format("csv").option("header", "true").load("Files/my_folder/my_file.csv")
df.show()

Read a Parquet file

df = spark.read.format("parquet").load("Files/my_folder/my_file.parquet")
df.show()

Write data as Parquet

df.write.format("parquet").mode("overwrite").save("Files/my_folder/output")

Create a managed Delta table via SQL

spark.sql("""
    CREATE TABLE IF NOT EXISTS my_schema.my_table (
        id BIGINT,
        name STRING,
        created_date DATE
    )
    USING DELTA
""")

Run OPTIMIZE on a Delta table

spark.sql("OPTIMIZE my_schema.my_table ZORDER BY (id)")

Run VACUUM to remove old files

spark.sql("VACUUM my_schema.my_table RETAIN 168 HOURS")

Enable V-Order on write

spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
df.write.format("delta").mode("overwrite").save("Tables/my_table")

Time travel — query a previous version

df = spark.read.format("delta").option("versionAsOf", 0).load("Tables/my_table")
df.show()

Time travel — query by timestamp

df = spark.read.format("delta").option("timestampAsOf", "2024-01-01").load("Tables/my_table")
df.show()

Getting data into Lakehouse

The following describes supported methods for ingesting data into a Fabric Lakehouse.

Upload files via the Lakehouse UI

Small files can be uploaded directly through the Fabric portal. Navigate to the Lakehouse item, open the Files section, and use the Upload option to add individual files or folders.

Copy data with Data Factory pipelines

Use a Fabric Data Factory pipeline with the Copy Data activity to move data from supported sources (Azure SQL Database, Azure Blob Storage, ADLS Gen2, Amazon S3, REST APIs, and many others) into the Lakehouse Files or Tables section. The pipeline can be scheduled or triggered on demand.

Dataflow Gen2

Dataflow Gen2 provides a low-code/no-code Power Query-based experience. Connect to a source, apply transformations, and set the destination to a Lakehouse table. Dataflows support incremental refresh and can be scheduled.

Shortcuts to external data

Create a Shortcut in the Lakehouse to reference data stored in ADLS Gen2, Amazon S3, Google Cloud Storage, or another Fabric Lakehouse without copying it. Shortcuts make the external data queryable in place via Spark or the SQL endpoint.

Spark notebooks (PySpark / Spark SQL)

Write data into a Lakehouse table or file path directly from a Spark notebook:

# Write a DataFrame to a managed Delta table
df.write.format("delta").mode("overwrite").saveAsTable("my_schema.my_table")

# Write a DataFrame to the Files section
df.write.format("parquet").mode("overwrite").save("Files/landing/my_data")

COPY INTO (T-SQL via SQL endpoint)

From the SQL analytics endpoint, use COPY INTO to load files from Azure storage into a Delta table:

COPY INTO my_schema.my_table
FROM 'https://<storage_account>.blob.core.windows.net/<container>/my_data/'
WITH (
    FILE_TYPE = 'PARQUET'
);

Eventstream (real-time ingestion)

Use Fabric Eventstream to capture, transform, and route real-time events from sources such as Azure Event Hubs or IoT Hub into a Lakehouse table for low-latency streaming ingestion.

Apache Spark Structured Streaming

Stream data into a Lakehouse table using PySpark Structured Streaming:

stream_df = spark.readStream.format("cloudFiles") \
    .option("cloudFiles.format", "json") \
    .load("Files/landing/")

stream_df.writeStream \
    .format("delta") \
    .option("checkpointLocation", "Files/checkpoints/my_table") \
    .outputMode("append") \
    .toTable("my_schema.my_table")