What are Schemas in the Microsoft Fabric Lakehouse?

Problem

We’re building our new data platform in Microsoft Fabric. We are using a lakehouse for our bronze and silver layers of the medallion architecture. We’ve loaded many tables from different sources. But in our bronze lakehouse, it’s all becoming very disorganized. Is there a way to organize the tables?

Solution

The Lakehouse in Microsoft Fabric is a collection of files and tables, where the tables use the delta format (with underlying Parquet files). Because the delta format is used, the lakehouse behaves like a regular database and you can query the tables using the familiar T-SQL with the SQL Analytics Endpoint. A new feature has been released in preview that will simplify table management and security.

Since the new feature is in preview at the time of writing, it’s possible some functionality will be different when it becomes generally available, or that some current limitations have been lifted. You can find a list of limitations in the documentation. If you want to follow along with this tip, you need a Fabric-enabled workspace, which can be obtained by getting a free trial, or by buying capacity in Azure.

Schemas in the Fabric Lakehouse

It’s important to know that already existing lakehouses do not support schemas. If you want to work with this new feature, you need to create a new lakehouse in your workspace. For the moment, it’s not possible to migrate a lakehouse that doesn’t use schemas to a schema-based lakehouse.

create new lakehouse

Give the new lakehouse a name and select the option to use Lakehouse schemas.

enable lakehouse schemas

In your new lakehouse, a default schema called dbo will be present, just as in SQL Server.

default dbo schema

Before adding schemas, let’s first add some sample data to the lakehouse. In the canvas window of your lakehouse, you’ll find an option to load sample data.

load sample data into lakehouse

After a while, one table will be created in the dbo schema (publicholidays), and a folder will be added with sample data:

sample data in the lakehouse

Creating a New Schema

Let’s create a new schema. Click the ellipsis next to the Tables folder and select New schema from the context menu.

create new schema using UI

You’ll be asked to provide a name for the new schema.

name the schema

You can add existing tables to the new schema simply by dragging them over in the user interface. From the Files section, you can create a new delta table and add it to your new schema.

create table from file

You’ll be prompted to provide a name for the new table, but also to select the schema:

load new table to schema

The table will then be loaded into the selected schema:

new table is in new schema

You can also create a new schema shortcut. You can use this option if you want to shortcut multiple tables from a schema in another lakehouse, or multiple delta tables in a folder on external storage.

schema shortcut wizard

The shortcuts to the tables will be created in a new schema. Keep in mind that shortcuts are just references to the original tables; the actual data is not copied.

Schemas and the SQL Analytics Endpoint

When we switch to the SQL Analytics Endpoint, we can see our new schemas in the Explorer window:

explorer with sample schema in sql endpoint

You can also create a schema in the SQL Endpoint using the following T-SQL command:

--MSSQLTips.com
CREATE SCHEMA Test;

This schema is visible within the SQL Endpoint:

schema created in SQL Endpoint is visible in its explorer

However, when we go back to the lakehouse itself, we don’t see the new Test schema. Even if we connect to the underlying OneLake storage, we can only find folders for the default dbo schema and the sample schema we created earlier.

schemas in onelake

If we try to create a new table from an existing file (like we did before), the Test schema is not part of the possible destination schemas:

test schema is missing in lakehouse

It seems that there’s a distinction between the schemas created in the lakehouse and those created through the SQL Endpoint. For the moment, it’s not clear if this is intentional or if this is a bug.

Schemas and Spark SQL

You don’t have to use the user interface in the browser to create a new schema. It’s also possible to create one using Spark code. In a notebook, add the schema-enabled lakehouse:

add lakehouse to notebook

Set the language to Spark SQL, either by setting the language for the entire notebook, or for a single cell using the magic command %%sql.

Just like in SQL Server, we can create a new schema with the following statement (keep in mind Spark is case sensitive):

--MSSQLTips.com
CREATE SCHEMA HelloWorld
create schema in notebook

With the following PySpark code, we can create a copy of the publicholidays table in the new schema:

%%pyspark
# mssqltips.com
df = spark.sql("SELECT * FROM mylakehouse_schemas.dbo.publicholidays LIMIT 1000")
df.write.mode("Overwrite").saveAsTable("HelloWorld.publicholidays")

This table will be visible in the lakehouse explorer, but it doesn’t show up in the SQL Endpoint. This might either be a bug, missing functionality, or a problem with the syncing of the metadata.

Why Use Schemas in the Lakehouse?

Like in SQL Server, you can use schemas to organize your tables into logical units. For example, if you want to implement the medallion architecture and have a bronze, silver, and gold layer in your lakehouse, you can now create three different schemas to hold the tables for each layer, instead of creating separate lakehouses.

Another benefit is that you can use schemas to provide role-based access security to a bunch of tables at once since schemas are just folders inside OneLake.

rbac security and schemas in the lakehouse

You can find more information about OneLake Role Based Access Control in the tip Microsoft Fabric OneLake Role Based Access Control (RBAC). At the time of writing, this feature is in preview as well.

Next Steps

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *