How to create a Spark SQL View in a Lakehouse on Microsoft Fabric

João Barros 29 de August de 2026 6 min read

This tutorial shows how to create a Spark SQL View in a Lakehouse on Microsoft Fabric, useful to encapsulate transformation logic, reuse queries in Notebooks and Warehouses, and control data access. I will explain the reasons for each step and show how to do it, with practical examples, plausible numbers (e.g.: counts, approximate sizes) and tips to avoid common errors that typically arise in corporate data environments.

Prerequisites

Account with access to a Microsoft Fabric workspace and permissions to edit a Lakehouse.
Lakehouse with at least one table or file loaded (for example a table named sales_raw) containing, for example, 100k–5M rows and 10–20 columns.
Basic knowledge of Spark SQL and access to Notebooks or the Query editor in Fabric.

Step 1: Choose the location and prepare the data

Decide in which Lakehouse to create the View (for example sales_lakehouse). The goal is for the View to be close to the base tables to avoid remote reads. Verify that the base table (sales_raw) exists and has clean columns. Do an initial validation: count rows, check types and identify nulls or outliers. This helps estimate performance impact (e.g.: a scan of 1M rows can take 10–30s depending on the Warehouse).

-- Exemplo: verificar colunas e algumas linhas
SELECT * FROM sales_lakehouse.catalog.sales_raw LIMIT 10;
-- Contar linhas para estimativa
SELECT COUNT(*) AS total_rows FROM sales_lakehouse.catalog.sales_raw;

If you find 20% of amounts null, for example, handle or filter them before building the View to avoid surprising results and to optimize scans.

Step 2: Create a temporary View (optional) to test the logic

Before creating a persistent View, test the logic with a temp view in a Notebook. This avoids creating permanent objects that you later need to remove and lets you iterate quickly. Use limited data (LIMIT 1k) to test performance locally.

# Em PySpark (Notebook)
spark.sql("CREATE OR REPLACE TEMP VIEW tmp_sales AS
SELECT customer_id, order_date, amount, region
FROM sales_lakehouse.catalog.sales_raw
WHERE amount > 0 LIMIT 1000")

# Verificar
spark.sql("SELECT * FROM tmp_sales LIMIT 5").show()

Check samples (5–10 rows) and basic statistics: MIN/MAX/AVG for numeric columns, count of NULLs. This prevents errors like invalid casts when promoting columns to DATE or NUMERIC.

Step 3: Define the View logic in Spark SQL

Write the definitive query that encapsulates the transformation. Keep it simple and documented: descriptive column names, clear filters and inline comments. If it is a complex transformation, split it into named subqueries. This facilitates maintenance, testing and optimization — for example, reducing the dataset before performing heavy joins.

-- Exemplo de lógica para uma View
CREATE OR REPLACE VIEW sales_lakehouse.catalog.vw_sales_clean AS
SELECT
  customer_id,
  CAST(order_date AS DATE) AS order_date,
  amount,
  UPPER(region) AS region
FROM sales_lakehouse.catalog.sales_raw
WHERE amount IS NOT NULL AND amount > 0;

Note: if the table has 2M rows and the filter reduces it to 1.2M, the View works logically and reflects changes in the base table without copying data. For very large loads, consider partitioning the base table by order_date to improve scans.

Step 4: Run the View creation in the correct environment

Open the Lakehouse Query editor or a Notebook with the Lakehouse context and execute the CREATE VIEW. Make sure the context (catalog/schema) is correct to avoid creating the View in the wrong space. If you prefer, you can use T-SQL in a Warehouse connected to the Lakehouse, but here we use Spark SQL for compatibility with Notebooks and programmatic automation.

-- No Notebook ou Query editor (Spark SQL)
spark.sql(open('create_view.sql').read())
-- ou executar directamente a instrução SQL do passo anterior

After running, validate with a verification SELECT. If there is an error, check messages: common issues include insufficient permissions, incompatible types in CAST, or incorrect table/catalog names.

Step 5: Control permissions and document

After creating the View, adjust permissions in the Lakehouse so that only authorized teams can read or alter the View. In Fabric, typically grant SELECT to a group of analysts and CONTROL or OWNERSHIP only to administrators. Document the purpose, logic version and dependencies (base tables) in a README file in the same Lakehouse or in the associated Git repository.

-- Exemplo: verificar permissões (comandos dependem da UI do Fabric)
-- No UI: Lakehouse -> Security -> Grant SELECT a grupo-analistas

Also include notes about expected SLAs (e.g.: queries to this View should respond in < 5s for samples of 100 rows) and when to materialize the View if compute cost is high.

Verify the result

To confirm that the View was created and works: run simple queries, check execution plans (EXPLAIN) and use the View in a Notebook or Warehouse. Also verify that changes in the base table are reflected in the View (because it is logical, not a copy). Run performance tests with typical filters (e.g.: WHERE region = 'EUROPE') and measure runs — if the average time is greater than 30s for common filters, consider materializing or reviewing partitioning.

-- Testes rápidos
SELECT COUNT(*) FROM sales_lakehouse.catalog.vw_sales_clean;
SELECT * FROM sales_lakehouse.catalog.vw_sales_clean LIMIT 10;
-- Ver plano de execução para identificar scans pesados
EXPLAIN SELECT * FROM sales_lakehouse.catalog.vw_sales_clean WHERE region = 'EUROPE';

Conclusion

Creating a Spark SQL View in a Lakehouse on Microsoft Fabric helps to reuse transformation logic, simplify queries and manage access. Practical next steps: create parameterized views, convert the View to a materialized table when performance is required, and include the View creation in version control (Git) with change comments. Final tip: if queries are slow, check partitioning and statistics of the base table or materialize results that are expensive to compute repeatedly.