How to create a Metadata Catalog in Data Governance: step by step

This tutorial shows how to create a simple Metadata Catalog in Data Governance to document datasets, provenance and owners — useful to improve discovery, control and compliance. The approach uses a centralized table and simple ETL processes to keep metadata up to date.

Prerequisites

• Access to a SQL server (for example Azure SQL or SQL Server) with permissions to create tables and stored procedures.
• Tool to run SQL (SSMS, Azure Data Studio or the sqlcmd command line).
• Basic SQL knowledge and an initial source of metadata (csv, ETL catalog or existing tables).

Step 1: Concept and schema of the Metadata Catalog

Before creating, we explain what to store: dataset name, description, owner, sensitivity, provenance (source), date of last update and tags. This schema allows answering common questions like "who is responsible?" or "when was it updated?".

Step 2: Create the Metadata Catalog table

We create a central SQL table to store the metadata. Keep relevant columns and keys to ensure uniqueness per dataset.

CREATE TABLE MetadataCatalog (
  DatasetId VARCHAR(200) PRIMARY KEY,
  DatasetName VARCHAR(300) NOT NULL,
  Description VARCHAR(2000),
  Owner VARCHAR(200),
  SensitivityLabel VARCHAR(100),
  SourceSystem VARCHAR(200),
  LastUpdated DATETIME2,
  Tags VARCHAR(500)
);

Step 3: Populate the catalog from a CSV file (simple ETL example)

A practical method is to import a CSV file with metadata rows. Example using BULK INSERT (adjust paths/permissions). This step shows how to automate the initial import.

-- Example: file C:\temp\metadata.csv with header
-- DatasetId,DatasetName,Description,Owner,SensitivityLabel,SourceSystem,LastUpdated,Tags
BULK INSERT MetadataCatalog
FROM 'C:\temp\metadata.csv'
WITH (
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n',
  TABLOCK
);

Step 4: Create a stored procedure for upsert (insert/update)

To keep the catalog updated by ETL processes, we create a stored procedure that performs an upsert by DatasetId. This avoids duplicates and allows automating updates.

CREATE PROCEDURE UpsertMetadata
  @DatasetId VARCHAR(200),
  @DatasetName VARCHAR(300),
  @Description VARCHAR(2000),
  @Owner VARCHAR(200),
  @SensitivityLabel VARCHAR(100),
  @SourceSystem VARCHAR(200),
  @LastUpdated DATETIME2,
  @Tags VARCHAR(500)
AS
BEGIN
  SET NOCOUNT ON;
  MERGE MetadataCatalog AS T
  USING (SELECT @DatasetId AS DatasetId) AS S
  ON T.DatasetId = S.DatasetId
  WHEN MATCHED THEN
    UPDATE SET DatasetName = @DatasetName,
               Description = @Description,
               Owner = @Owner,
               SensitivityLabel = @SensitivityLabel,
               SourceSystem = @SourceSystem,
               LastUpdated = @LastUpdated,
               Tags = @Tags
  WHEN NOT MATCHED THEN
    INSERT (DatasetId, DatasetName, Description, Owner, SensitivityLabel, SourceSystem, LastUpdated, Tags)
    VALUES (@DatasetId, @DatasetName, @Description, @Owner, @SensitivityLabel, @SourceSystem, @LastUpdated, @Tags);
END;

Step 5: Integrate with ETL/CI process (example with simplified script)

Call the stored procedure during the ETL pipeline when a dataset is created or changed. Here is a T-SQL example that updates two datasets; in real scenarios this is generated by the ETL (Data Factory, Azure Databricks, SSIS).

EXEC UpsertMetadata 'sales.orders', 'Orders', 'Registos de encomendas de vendas', 'data-ops@empresa.com', 'Confidencial', 'OLTP_Sales', GETDATE(), 'finance,orders';
EXEC UpsertMetadata 'crm.customers', 'Customers', 'Dados de clientes CRM', 'crm-owner@empresa.com', 'Pessoal', 'CRM_System', GETDATE(), 'crm,customers';

Step 6: Control access and auditing

Define minimal permissions: create a read-only role for users that query metadata and restrict who can execute UpsertMetadata. Log changes with triggers or SQL Audit for auditing.

-- Create role and grant SELECT
CREATE ROLE metadata_read;
GRANT SELECT ON MetadataCatalog TO metadata_read;
-- Grant execution of the procedure only to catalog administrators
GRANT EXECUTE ON UpsertMetadata TO db_owner; -- adjust according to policy

Verify the result

Confirm by querying the table and testing the upsert. Also check permissions and audit logs.

-- View metadata list
SELECT * FROM MetadataCatalog ORDER BY DatasetName;

-- Test read permission (as a user with metadata_read)
-- EXECUTE assumes appropriate context

Conclusion

With a central table, an upsert routine and access rules you have a functional Metadata Catalog that improves discovery, accountability and compliance. Next steps: integrate with Microsoft Purview or APIs to sync classifications, add versioning and automate updates from the ETL pipeline. Tip: start with minimal fields and expand as business needs evolve — what is the next dataset to document?