How to create a metadata catalog for Delta tables in a Lakehouse

João Barros 14 de August de 2026 4 min read

This tutorial shows how to create a simple metadata catalog to organize Delta tables in the Lakehouse. Having a catalog helps document and discover tables and ensures teams use correct schemas and permissions.

Prerequisites

Microsoft Fabric account with permissions to create Lakehouse and run SQL and scripts.
A Lakehouse with at least one existing Delta table.
Basic knowledge of SQL and some PowerShell (optional for automation).

Step 1: Define the metadata model

Before creating tables, consider which fields are useful: table name, location (path), schema (columns), owner, tags and last update. A simple model prevents divergence and enables search.

-- Exemplo de esquema para a tabela de catálogo
CREATE TABLE IF NOT EXISTS metadata.catalog (
  table_name STRING,
  lakehouse_path STRING,
  schema_json STRING,
  owner STRING,
  tags ARRAY,
  last_updated TIMESTAMP
)
USING DELTA;

Step 2: Create the catalog table in the Lakehouse

Use the Lakehouse SQL endpoint (or SQL notebooks) to create the Delta table that will store the metadata. This centralizes the information and benefits from Delta's ACID properties.

-- Executar no notebook SQL ou no endpoint SQL
CREATE TABLE IF NOT EXISTS metadata.catalog (
  table_name STRING,
  lakehouse_path STRING,
  schema_json STRING,
  owner STRING,
  tags ARRAY,
  last_updated TIMESTAMP
)
USING DELTA;

Step 3: Populate the catalog with a manual entry

Manually add an entry for an existing Delta table. Store the schema in JSON to make it easier for tools that consume the catalog.

INSERT INTO metadata.catalog VALUES (
  'sales_raw',
  '/lakehouse/finance/sales_raw',
  '{"columns":[{"name":"sale_id","type":"INT"},{"name":"amount","type":"DOUBLE"}] }',
  'joana.silva',
  array('finance','raw'),
  current_timestamp()
);

Step 4: Automate schema capture with SQL

To avoid manual inserts, extract the table schema and store it as JSON. This example uses SQL functions to build the schema JSON (generic example — adapt according to the available SQL endpoint).

-- Exemplo: gerar esquema a partir de DESCRIBE TABLE e inserir no catálogo
-- 1) Obter descrição e transformar em JSON (pseudocódigo adaptável)
CREATE OR REPLACE TEMP VIEW v_schema AS
SELECT concat('{"name":"', col_name, '","type":"', data_type, '"}') as item
FROM (DESCRIBE TABLE sales_raw);

-- 2) Agregar em array/json e inserir
INSERT INTO metadata.catalog (table_name,lakehouse_path,schema_json,owner,tags,last_updated)
SELECT
 'sales_raw',
 '/lakehouse/finance/sales_raw',
 concat('[', string_join(collect_list(item), ','), ']'),
 'joana.silva',
 array('finance','raw'),
 current_timestamp()
FROM v_schema;

Step 5: Update entries with MERGE (upsert)

Use MERGE to keep the catalog synchronized when the table is altered or recreated. This avoids duplicates and keeps last_updated correct.

MERGE INTO metadata.catalog AS target
USING (SELECT 'sales_raw' AS table_name, '/lakehouse/finance/sales_raw' AS lakehouse_path,
              '[{"name":"sale_id","type":"INT"},{"name":"amount","type":"DOUBLE"}]' AS schema_json,
              'joana.silva' AS owner, array('finance','raw') AS tags, current_timestamp() AS last_updated) AS src
ON target.table_name = src.table_name
WHEN MATCHED THEN UPDATE SET
  lakehouse_path = src.lakehouse_path,
  schema_json = src.schema_json,
  owner = src.owner,
  tags = src.tags,
  last_updated = src.last_updated
WHEN NOT MATCHED THEN INSERT *;

Step 6: Automate with PowerShell (optional)

To run regular updates, write a PowerShell script that calls the SQL endpoint and updates the catalog — useful for ETL/ELT pipelines.

# PowerShell: exemplo mínimo para chamar um endpoint SQL (pseudocódigo)
$endpointUrl = 'https://sqlendpoint.fabric...'  # adaptar
$query = "MERGE INTO metadata.catalog ..."  # colocar MERGE preparado
Invoke-RestMethod -Method Post -Uri $endpointUrl -Body @{query=$query} -Headers @{Authorization='Bearer TOKEN'}

Verify the result

Confirm that the metadata.catalog table contains entries and that schema_json matches the actual schema. Run simple queries and compare with DESCRIBE TABLE.

SELECT * FROM metadata.catalog WHERE table_name = 'sales_raw';
DESCRIBE TABLE sales_raw;

Conclusion

With a metadata catalog in the Lakehouse it's easier to discover Delta tables, ensure schema consistency and automate documentation. Next steps: extend fields (responsibility lines, SLAs), integrate with Power BI for search or hook alerts when the schema changes. Tip: start with manual entries and automate once the pattern is defined — which metadata do you consider most useful in your organization?