Learn how to create a Microsoft Fabric Lakehouse from scratch, load CSV files into both the Files and Tables zones, and query your data using the SQL Analytics Endpoint. This hands-on lesson builds the foundational skills you need for every Fabric data engineering project.
Imagine you're a data analyst at a mid-sized retail company. Your team gets daily sales files dropped into a shared folder — CSVs, Excel sheets, the occasional Parquet file from the data engineering team. Right now, those files live in a tangle of SharePoint folders, someone manually imports them into SQL Server, and half the time the Power BI report is querying last week's data because nobody remembered to run the refresh job. Sound familiar?
The Microsoft Fabric Lakehouse is designed to untangle exactly this kind of mess. It gives you a single place to land raw files, transform them into structured tables, and query everything with SQL — without managing a single server, writing any infrastructure code, or convincing IT to provision new databases. By the end of this lesson, you'll have created your own Lakehouse, loaded data into it as both unstructured files and structured Delta tables, and connected to the SQL Analytics Endpoint to write your first query against that data.
This is a foundational skill in Fabric. Almost everything else in the platform — pipelines, Spark notebooks, Direct Lake Power BI reports — flows through the Lakehouse. Understanding what it is and how it works sets you up to build competently on everything that comes next.
What you'll learn:
Before you dive in, you'll need:
Before you click anything, let's build the right mental model.
A Lakehouse is a storage and analytics architecture that combines the flexibility of a data lake (store anything, in any format) with the query power of a data warehouse (structured tables you can query with SQL). The term itself is a blend of the two concepts, and Microsoft Fabric's Lakehouse is one of the cleanest implementations of this idea available today.
Think of it like a warehouse with a loading dock. The loading dock is where trucks pull up and drop raw goods — boxes, pallets, loose items. You don't know the exact contents of every box yet, and that's fine. In Lakehouse terms, this is the Files zone: a general-purpose file storage area where you can land CSVs, Parquet files, JSON files, images, PDFs, or anything else without any schema enforcement. It's a safe place to put data while you figure out what to do with it.
The warehouse floor is organized shelving where goods are catalogued, labeled, and ready to be picked efficiently. In Lakehouse terms, this is the Tables zone: a managed storage area for Delta tables — a special format that adds structure, versioning, and ACID transaction support on top of regular Parquet files. Once data is in a Delta table, Fabric can index it, query it with SQL, and serve it to Power BI at high speed.
Key insight
The Tables zone isn't a separate database — it's still just files stored in OneLake. What makes Delta tables special is a transaction log (a _delta_log folder) that sits alongside the Parquet data files and records every change. This is what lets SQL engines query Delta tables with full reliability, even as data is being written.
Let's build one. Navigate to your Fabric workspace — the hub where all your Fabric items live. If you see items from other projects there, that's fine; a workspace can hold many items.
In the top-left of the workspace view, click New item. A panel will appear showing all the types of items you can create in Fabric. Scroll through the list until you find Lakehouse under the Data Engineering section, and click it.
Fabric will prompt you to name your Lakehouse. Names must be unique within a workspace and can only contain letters, numbers, and underscores — no spaces or hyphens. For this lesson, name it retail_sales_lakehouse and click Create.
After a few seconds, Fabric will open the Lakehouse editor. Take a moment to look at what you're seeing, because the layout tells you everything about how the Lakehouse is structured.
On the left side, you'll see two top-level folders in the file explorer panel: Files and Tables. These are the two zones we discussed. Below the editor, there's a tab switcher that shows Lakehouse and SQL analytics endpoint — we'll come back to that second tab shortly.
Note
When Fabric creates your Lakehouse, it's also automatically provisioning storage in OneLake behind the scenes. Your retail_sales_lakehouse now has a dedicated path inside your organization's OneLake storage. You can learn more about how this storage layer works in OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts.
Now let's put some data in. We'll simulate the retail scenario from the introduction: a CSV file of daily sales transactions.
For this exercise, create a simple CSV file on your local machine with this content and save it as sales_2024_01.csv:
order_id,order_date,product_name,category,quantity,unit_price,store_id
1001,2024-01-03,Running Shoes,Footwear,2,89.99,ST-07
1002,2024-01-03,Water Bottle,Accessories,5,14.99,ST-12
1003,2024-01-04,Yoga Mat,Fitness,1,49.99,ST-07
1004,2024-01-04,Running Shoes,Footwear,1,89.99,ST-03
1005,2024-01-05,Resistance Bands,Fitness,3,24.99,ST-12
1006,2024-01-05,Water Bottle,Accessories,8,14.99,ST-03
1007,2024-01-06,Trail Runners,Footwear,2,119.99,ST-07
1008,2024-01-06,Foam Roller,Fitness,1,34.99,ST-12
Back in the Lakehouse editor, right-click the Files folder in the left panel. You'll see options including New subfolder and Upload. Click Upload, then Upload files. A file picker will open — navigate to your sales_2024_01.csv file and select it.
Once uploaded, you'll see the file appear under the Files folder. Click on it and Fabric will give you a preview showing the raw contents. Notice that at this stage, Fabric is treating this as a plain file — it knows the file exists, but it doesn't know anything about the columns, data types, or how many rows there are. It's sitting on the loading dock.
Tip
You can create subfolders inside the Files zone to organize data logically. For example, you might create a folder structure like Files/raw/sales/2024/01/ to mirror a typical data lake organization. This is especially useful when you're landing data from multiple source systems or time periods.
Let's also create an organizational subfolder. Right-click the Files folder, click New subfolder, and name it raw. Then drag your uploaded CSV into it — or delete the upload and re-upload directly into the raw subfolder. In a real project, you'd always organize files from the start rather than cleaning up afterward.
A CSV in the Files zone is useful for storage, but you can't query it with SQL and you can't build a fast Power BI report on top of it. To unlock those capabilities, you need to promote it into the Tables zone as a Delta table.
There are several ways to do this in Fabric. For this lesson, we'll use the built-in shortcut that appears when you right-click a file.
Right-click your sales_2024_01.csv file in the left panel. Look for the option that says Load to Tables. Click it, then choose New table.
A configuration panel will appear. Fabric has automatically detected that this is a CSV and will show you a preview of the data. You'll see proposed column names and data types — Fabric has inferred these by reading the first few rows of the file. In our case, it should correctly identify order_id as a whole number, unit_price as a decimal, quantity as a whole number, and the remaining columns as text strings.
Give the table a name: sales_jan_2024. Click Load.
Fabric will now run a Spark job to read the CSV, apply the schema, and write the output as a Delta table into your Tables zone. This job may take 30-60 seconds even for a tiny file — that's normal, because Fabric is spinning up a Spark cluster to process it. For larger files in production, this same infrastructure would handle millions of rows without any extra configuration on your part.
Warning
When Fabric infers data types from a CSV, it reads a sample of rows. If your first 100 rows all have clean integers in a column but row 5,000 has a null or a text value, the load will fail. Always inspect your data before loading, and consider explicitly specifying data types when using code-based loading methods like PySpark notebooks.
When the job completes, click the Tables folder in the left panel. You'll see sales_jan_2024 listed there. Click it to see a column preview. You can also click the ellipsis (...) next to the table name and select Preview data to see the actual rows.
At this point, something important has happened under the hood. Fabric has written Delta-format Parquet files into your OneLake storage path and created a _delta_log folder alongside them. The table is registered in Fabric's metadata catalog, which means the SQL engine can discover and query it automatically.
Here's where the Lakehouse gets genuinely powerful. Switch from the Lakehouse tab to the SQL analytics endpoint tab — you'll find this selector at the top center of the Lakehouse editor, or sometimes shown as a tab near the top of the experience.
What you're looking at now is a read-only SQL interface automatically generated from your Lakehouse's Tables zone. You didn't configure this. You didn't write any DDL (Data Definition Language) statements like CREATE TABLE. Fabric saw your Delta table and automatically exposed it as a queryable SQL object.
The SQL Analytics Endpoint is not a separate database — it's a SQL layer that sits on top of your Delta tables in OneLake. Think of it like a SQL lens over your files. This means:
Key insight
The SQL Analytics Endpoint is read-only by design. You cannot run INSERT, UPDATE, or DELETE statements here. All data modifications must happen through the Lakehouse itself — via file uploads, Spark notebooks, or data pipelines. This separation is intentional: it keeps your analytical queries fast and reliable, insulated from concurrent write operations.
In the SQL endpoint view, you'll see a familiar SQL editor on the right and an object explorer panel on the left showing your sales_jan_2024 table under the default schema (usually dbo). Let's write our first query.
Click in the query editor area and type:
SELECT
category,
COUNT(*) AS order_count,
SUM(quantity) AS total_units_sold,
ROUND(SUM(quantity * unit_price), 2) AS total_revenue
FROM dbo.sales_jan_2024
GROUP BY category
ORDER BY total_revenue DESC;
Click the Run button (or press F5). Within a few seconds, you'll see results grouped by product category — Footwear, Fitness, Accessories — with aggregate totals. For our small sample dataset, the numbers will be modest, but the mechanism is exactly what you'd use on a table with 50 million rows.
You can also create Views in the SQL endpoint — virtual tables that encapsulate a query. These are useful for exposing cleaner, pre-filtered datasets to report builders without giving them access to the raw table. To create a view, right-click the Views folder in the object explorer and select New view, or write a standard CREATE VIEW statement in the editor.
Let's zoom out and connect all the pieces into a coherent workflow, because the real power of the Lakehouse comes from understanding how these layers work together.
A typical Lakehouse workflow follows what practitioners call a medallion architecture: data moves through Bronze → Silver → Gold layers, each one more refined than the last.
In Fabric's Lakehouse, this often looks like:
Tip
You don't need to implement the full medallion architecture on day one. Even a simple two-step flow — land the file, load to table, query with SQL — delivers immediate value. Build complexity gradually as your team's needs grow.
The fact that all of this lives in a single Lakehouse item, backed by OneLake, means you have one place to manage security, one storage bill, and one lineage story from raw file to published report.
Now it's your turn to extend what you've built. Complete these tasks using what you've learned in this lesson:
Part 1: Add a second month of data
Create a second CSV file called sales_2024_02.csv with a similar structure but different dates (use February 2024) and at least 8 rows of data. Upload it to Files/raw/ in your Lakehouse. Then load it to a new table called sales_feb_2024 using the Load to Tables option.
Part 2: Query across both tables
Switch to the SQL Analytics Endpoint and write a query that combines both months using UNION ALL, then groups the combined data by store_id to show total revenue per store across January and February:
SELECT
store_id,
COUNT(*) AS total_orders,
ROUND(SUM(quantity * unit_price), 2) AS total_revenue
FROM (
SELECT store_id, quantity, unit_price FROM dbo.sales_jan_2024
UNION ALL
SELECT store_id, quantity, unit_price FROM dbo.sales_feb_2024
) AS combined
GROUP BY store_id
ORDER BY total_revenue DESC;
Part 3: Create a view
Create a SQL view called vw_all_sales_2024 that encapsulates the UNION ALL query from Part 2 (without the outer aggregation — keep all columns so it shows every order). This view will serve as a clean abstraction layer for anyone querying your Lakehouse.
"My table didn't appear in the SQL Analytics Endpoint." After loading a table, the SQL endpoint can take a few minutes to register the new table in its metadata. Try refreshing the object explorer by clicking the Refresh icon. If it still doesn't appear after 5 minutes, check whether the load job actually completed successfully — go back to the Lakehouse tab and verify the table appears under Tables.
"The Load to Tables job failed with a type conversion error." This almost always means Fabric inferred an incorrect data type for one of your columns. Common culprits: date columns that Fabric reads as text, or a numeric column that contains a stray comma or currency symbol. Open the CSV in Excel or a text editor first, clean the problematic column, and try again.
"I uploaded my file but I can't find it." Check which folder you're looking at. If you created subfolders inside Files, the upload dialog defaults to wherever you right-clicked. Click the disclosure triangle next to Files to expand the full folder tree.
"My SQL query returns no rows, but the table preview shows data."
This almost never happens with correct SQL, but double-check your table name spelling and schema prefix. The default schema is dbo, so your table reference should be dbo.sales_jan_2024. If you named your table differently during setup, the schema prefix must match.
Warning
Deleting a table from the Tables zone also deletes the underlying Delta files in OneLake. This is not like dropping a SQL Server table where data might be recoverable — it's a permanent deletion of the physical files. Always confirm you have source data (e.g., the original CSVs in the Files zone) before dropping tables during development.
"I can't write INSERT statements in the SQL endpoint." Correct — that's by design. The SQL Analytics Endpoint is read-only. If you need to write data programmatically, use a Spark notebook (which can write directly to the Tables zone) or a data pipeline with a Copy activity.
You've built your first working Lakehouse. Let's recap what you actually did:
The Lakehouse is the foundation of almost everything in Fabric's data engineering workload. From here, the natural next steps are learning how to automate data loading through pipelines and transformations, and understanding how Fabric's storage layer ties everything together. The OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts article goes deeper on what's actually happening in storage — the Delta log, Parquet files, and how Shortcuts let you reference data from other Lakehouses or cloud storage without copying it.
You're now ready to start treating the Lakehouse as a first-class tool in your data stack — not just a place to dump files, but a structured, queryable foundation for analytics.