Configuring Databricks to process the K Metadata sync files

Overview

The sync pushes descriptions, glossary terms and tags from K (Kada) into Unity Catalog comments and tags, so K stays the single place metadata is edited. It runs automatically whenever K drops new extract files.

End-to-end flow:

  1. Volume (landing files): K writes daily gzip CSV extracts for tables, columns and glossary terms to Azure Blob, exposed in Databricks as a Unity Catalog external volume.

  2. Auto Loader landing tables: a Lakeflow pipeline uses Auto Loader to load only new files into raw landing tables.

  3. Snapshot tables: an AUTO CDC flow keeps Type 2 SCD history per object, adding a version only when a tracked field changes.

  4. Cleanup views: return the current row per object and flag objects that no longer exist in Unity Catalog.

  5. Comment and tag update: a notebook builds each object's comment and tags, skips unchanged objects and runs the SQL.

  6. Orchestration: a Lakeflow Job with a file arrival trigger runs the pipeline, then the notebook.

Supporting objects: the mapping table (kada_metadata_map) decides which file fields become comments, tags or terms; the location parser function (parse_kada_location) splits K's LOCATION into catalog, schema, table and column; and the applied-state log (applied_state) records what was last pushed.

Prerequisites and object names

Confirm these before starting:

  • Workspace attached to a Unity Catalog metastore, with serverless compute enabled for notebooks, jobs and pipelines

  • CREATE CATALOG on the metastore, or USE CATALOG + CREATE SCHEMA on an existing catalog

  • Storage for the extracts: an Azure storage account with shared-key access enabled, or (optional) an Amazon S3 bucket

  • A Unity Catalog storage credential: an Access Connector for Azure Databricks (Azure) or an IAM role (AWS)

  • Access Connector for Azure Databricks backing a Unity Catalog storage credential

  • A K admin who can add a storage connection, saved searches and alerts

  • A service principal to run the pipeline and job

Every code block on this page uses the names below. If yours differ, find and replace them before running anything. Create one set per environment (dev, test, prod).

Object

Name

Purpose

Sync catalog / schema

governance.kada_sync

Control tables, pipeline tables, views and function

Extract volume

governance.kada_sync.kada_extracts_ext

External volume where K files land: tables/, columns/, terms/

Volume path

/Volumes/governance/kada_sync/kada_extracts_ext

Read by Auto Loader and the file arrival trigger

External location

kada_extracts_loc

Covers the Blob container folder K writes to

Pipeline

kada_metadata_sync

Landing and SCD2 snapshot tables

Notebook

apply_comments_tags

Writes comments and tags

Job

kada_metadata_sync_job

Runs the pipeline, then the notebook

All SQL uses three-part names, so it runs correctly whatever catalog the SQL editor dropdown shows.

Step 1: Storage, catalog, schema and volume

K writes the extracts to cloud storage, and Databricks reads them through an external volume over that location. Azure Blob is the default; Amazon S3 is an optional alternative. Do 1a for Azure or 1a (AWS) for S3, not both.

1a (Azure, default). Azure Blob storage

  1. In the storage account, create (or reuse) a container with a folder for K, e.g. <container>/kada/. Don't use the workspace's managed resource group, root (DBFS) storage, or the metastore's managed storage path.

  2. Grant the Access Connector behind your Unity Catalog storage credential Storage Blob Data Contributor on the account. This is often already in place for an existing external location.

  3. Confirm Allow storage account key access is enabled and copy key1 for the K connection (Step 2).

1a (AWS, optional). Amazon S3 storage

  1. Create (or reuse) an S3 bucket in the same region as the workspace, with a prefix for K, e.g. s3://<bucket>/kada/. Don't use the workspace root bucket or the metastore's managed storage path.

  2. Create an IAM role for Unity Catalog with read/write access to the bucket and a trust policy for the Databricks Unity Catalog role (with your external ID), then register it as a storage credential in Catalog Explorer → External data → Credentials. Skip if an existing storage credential already covers the bucket.

  3. For K, create an IAM user (or the access method your K tenant supports) limited to s3:PutObject and s3:ListBucket on the kada/ prefix, and share its access key with the K admin for Step 2. Confirm with your K admin that S3 delivery is available.

1b. Catalog and schema (SQL editor)

CREATE CATALOG IF NOT EXISTS governance;
CREATE SCHEMA  IF NOT EXISTS governance.kada_sync COMMENT 'K (Kada) metadata sync objects';

If CREATE CATALOG fails with Metastore storage root URL does not exist, add MANAGED LOCATION 'abfss://<container>@<account>.dfs.core.windows.net/governance', or create the schema in an existing catalog and replace governance throughout.

1c. External location and volume

-- Skip if an external location already covers the container
CREATE EXTERNAL LOCATION IF NOT EXISTS kada_extracts_loc
  URL 'abfss://<container>@<account>.dfs.core.windows.net/kada/'
  WITH (STORAGE CREDENTIAL `<credential name>`);

CREATE EXTERNAL VOLUME IF NOT EXISTS governance.kada_sync.kada_extracts_ext
  LOCATION 'abfss://<container>@<account>.dfs.core.windows.net/kada/'
  COMMENT 'K metadata extracts: tables/, columns/, terms/';

LIST '/Volumes/governance/kada_sync/kada_extracts_ext/';

For AWS, use the S3 path in both statements instead:

CREATE EXTERNAL LOCATION IF NOT EXISTS kada_extracts_loc
  URL 's3://<bucket>/kada/'
  WITH (STORAGE CREDENTIAL `<credential name>`);

CREATE EXTERNAL VOLUME IF NOT EXISTS governance.kada_sync.kada_extracts_ext
  LOCATION 's3://<bucket>/kada/'
  COMMENT 'K metadata extracts: tables/, columns/, terms/';

Everything after this step is identical on Azure and AWS, because the pipeline and job read the volume path, not the cloud URL.

Expected: governance.kada_sync shows the volume kada_extracts_ext in Catalog Explorer, and LIST runs without a permission error (it may return no files yet).

Step 2: K configuration

This step is identical to the Snowflake equivalent Configuring the Metadata Sync files. Refer to the link for more in-depth instructions. Refer to this section for suggested naming conventions.

  1. A K admin connects K to the Blob container and schedules four daily extracts, one per file type.

  2. Settings → Integrations → Storage → Add connection → Azure Blob: container, account name and key1 (or, for AWS, Amazon S3: bucket, region and the IAM user's access key) → Test connection → Save.

  3. Create four saved searches filtered to the Databricks source, and an alert on each:

Saved search / alert

Built in K

Columns

Alert path

databricks_<env>_table_sync

Search: Asset = Table

Add Terms, PII data detected; remove Trust

/kada/tables

databricks_<env>_column_tbl_sync

Linked Search → Find Column by Table: Subtype = Table

Column side: add Terms, PII data detected; remove Trust

/kada/columns

databricks_<env>_column_vw_sync

Linked Search → Find Column by Table: Subtype = View

As above

/kada/columns

databricks_<env>_term_sync

Search: Asset = Collection Instance, Collection = glossaries

Description only

/kada/terms

  1. On each alert set file format GZ and frequency daily, then Run now and Save.

The file names matter: the pipeline picks files up by the patterns *_table*, *_column* and *_term*. Scope each saved search to the catalogs you intend to sync.

Step 3: Validate the first K extracts

Check the real files before building on them, because the mapping table and location parser depend on their headers and LOCATION format.

SQL
SELECT * FROM read_files('/Volumes/governance/kada_sync/kada_extracts_ext/tables/',
  format => 'csv', header => true, multiLine => true) LIMIT 20;

SELECT * FROM read_files('/Volumes/governance/kada_sync/kada_extracts_ext/columns/',
  format => 'csv', header => true, multiLine => true) LIMIT 20;

SELECT * FROM read_files('/Volumes/governance/kada_sync/kada_extracts_ext/terms/',
  format => 'csv', header => true, multiLine => true) LIMIT 20;
  • Header names normalise (lower_snake_case) to the names in the mapping table, e.g. collection_domain. Note any differences for Step 4 and Step 5.

  • LOCATION is <source>/<workspace>/<catalog>/<schema> for tables, plus /<table> for columns.

  • The terms separator is a comma, semicolon or new line, and term names match the terms file.

  • Descriptions with line breaks stay on one row (multiLine => true).

  • Owner and steward lists fit within 256 characters (the Unity Catalog tag value limit), or are mapped to a group name in K.

Step 4: Control objects

Three objects drive the sync:

  • Mapping table: which file field becomes a comment, a tag or terms.

  • Applied-state log: what was last pushed, so incremental runs skip unchanged objects.

  • Location parser: turns K's LOCATION into catalog, schema, table and column.

Run in the SQL editor:

SQL
USE CATALOG governance; USE SCHEMA kada_sync;

-- 1. Mapping: file column -> comment / tag / terms, per level
CREATE TABLE IF NOT EXISTS kada_metadata_map (
  file_column_name   STRING,   -- normalised header (lower_snake_case)
  metadata_name      STRING,   -- UC tag key, or description / terms
  metadata_type      STRING,   -- COMMENT | TAG | TERMS
  metadata_obj_level STRING,   -- TBL | COL
  is_active          BOOLEAN
);

INSERT OVERWRITE kada_metadata_map
SELECT f, m, t, l, true FROM VALUES
  ('description','description','COMMENT'),
  ('terms','terms','TERMS'),
  ('collection_category','category','TAG'),
  ('collection_classification','classification','TAG'),
  ('collection_domain','domain','TAG'),
  ('owners','owners','TAG'),
  ('stewards','stewards','TAG'),
  ('collection_pii_data_det','pii_data_det','TAG') AS v(f, m, t)
CROSS JOIN VALUES ('TBL'), ('COL') AS lv(l);

-- 2. Applied-state log
CREATE TABLE IF NOT EXISTS applied_state (
  obj_level    STRING,     -- TBL | COL
  full_name    STRING,     -- catalog.schema.table[.column]
  payload_hash STRING,     -- md5 of the comment + tags pushed
  applied_at   TIMESTAMP,
  status       STRING,     -- OK | ERROR | DRY_RUN
  error_msg    STRING      -- error text, or the generated SQL for DRY_RUN
);

-- 3. Location parser. K LOCATION for a table: <source>/<workspace>/<catalog>/<schema>;
--    for a column: .../<catalog>/<schema>/<table>. Adjust offsets if your extracts differ.
CREATE OR REPLACE FUNCTION parse_kada_location(loc STRING, name STRING, lvl STRING)
RETURNS STRUCT<catalog_name STRING, schema_name STRING, table_name STRING, column_name STRING>
RETURN CASE lvl
  WHEN 'TBL' THEN named_struct(
    'catalog_name', lower(element_at(split(loc,'/'), -2)),
    'schema_name',  lower(element_at(split(loc,'/'), -1)),
    'table_name',   lower(name), 'column_name', CAST(NULL AS STRING))
  ELSE named_struct(
    'catalog_name', lower(element_at(split(loc,'/'), -3)),
    'schema_name',  lower(element_at(split(loc,'/'), -2)),
    'table_name',   lower(element_at(split(loc,'/'), -1)),
    'column_name',  name)
END;

Check:

SQL
SELECT count(*) FROM governance.kada_sync.kada_metadata_map;   -- 16

-- Replace with a real LOCATION value from Step 3
SELECT governance.kada_sync.parse_kada_location('<location from a table row>', '<table name>', 'TBL');
SELECT governance.kada_sync.parse_kada_location('<location from a column row>', '<column name>', 'COL');

The parser should return the correct catalog, schema, table (and column) for real rows. If your headers differed in Step 3, update file_column_name in the mapping table now.

Tags need no pre-creation: Unity Catalog tags are free-form key/value pairs. If your workspace enforces governed tags, create a tag policy for each key (category, classification, domain, owners, stewards, pii_data_det) with its allowed values first.

Step 5: Lakeflow pipeline

The pipeline loads every new file into landing tables with Auto Loader and keeps SCD Type 2 history in snapshot tables. A new snapshot version is written only when a tracked field changes.

5a. Create the pipeline

  1. Sidebar → Jobs & Pipelines → Create → ETL pipeline.

  2. Name it kada_metadata_sync; set default catalog governance and schema kada_sync.

  3. Choose Start with an empty file, language Python. Rename the file in the transformations/ folder to kada_pipeline.py.

  4. Settings (gear): compute Serverless, pipeline mode Triggered, run as the service principal.

5b. Paste the pipeline code into transformations/kada_pipeline.py. Keep TRACK in line with the mapping table.

Python
import re
from pyspark import pipelines as dp   # older runtimes: import dlt
from pyspark.sql import functions as F

BASE = "/Volumes/governance/kada_sync/kada_extracts_ext"
# Fields whose change creates a new snapshot version (keep in line with kada_metadata_map)
TRACK = ["description", "terms", "collection_category", "collection_classification",
         "collection_domain", "owners", "stewards", "collection_pii_data_det"]

def normalise(df):
    for c in df.columns:
        n = re.sub(r"[^0-9a-zA-Z]+", "_", c).strip("_").lower()
        if n != c:
            df = df.withColumnRenamed(c, n)
    return df

def landing(folder, glob):
    df = (spark.readStream.format("cloudFiles")
          .option("cloudFiles.format", "csv")
          .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
          .option("cloudFiles.inferColumnTypes", "false")      # everything as STRING
          .option("header", "true")
          .option("multiLine", "true")                          # descriptions can contain line breaks
          .option("escape", '"')
          .option("pathGlobFilter", glob)
          .load(f"{BASE}/{folder}/"))                           # sub-folders are read too
    return normalise(df).select(
        "*",
        F.col("_metadata.file_name").alias("file_name"),
        F.col("_metadata.file_modification_time").alias("file_last_modified"),
        F.current_timestamp().alias("start_scan_time"))

@dp.table(name="landing_tbl_metadata", comment="K table/view extract (raw)")
def landing_tbl():
    return landing("tables", "*_table*.csv.gz")

@dp.table(name="landing_col_metadata", comment="K column extract, tables + views (raw)")
def landing_col():
    return landing("columns", "*_column*.csv.gz")

@dp.table(name="landing_term_metadata", comment="K glossary terms extract (raw)")
def landing_term():
    return landing("terms", "*_term*.csv.gz")

def prepared(src, lvl):
    df = spark.readStream.table(src)
    for c in TRACK:                                              # tolerate a missing optional field
        if c not in df.columns:
            df = df.withColumn(c, F.lit(None).cast("string"))
    df = df.withColumn("p", F.expr(
        f"governance.kada_sync.parse_kada_location(location, name, '{lvl}')")).select("*", "p.*").drop("p")
    parts = ["catalog_name", "schema_name", "table_name"] + (["column_name"] if lvl == "COL" else [])
    return (df.withColumn("full_name", F.concat_ws(".", *[F.col(p) for p in parts]))
              .withColumn("snap_id", F.lower("full_name"))
              .withColumn("obj_level", F.lit(lvl)))

@dp.view(name="prep_tbl_metadata")
def prep_tbl():
    return prepared("landing_tbl_metadata", "TBL")

@dp.view(name="prep_col_metadata")
def prep_col():
    return prepared("landing_col_metadata", "COL")

for lvl in ("tbl", "col"):
    dp.create_streaming_table(f"snapshot_{lvl}_metadata",
                              comment=f"SCD2 history of K {lvl.upper()} metadata")
    dp.create_auto_cdc_flow(                                     # older runtimes: dlt.apply_changes(...)
        target=f"snapshot_{lvl}_metadata",
        source=f"prep_{lvl}_metadata",
        keys=["snap_id"],
        sequence_by=F.struct("file_last_modified", "file_name"),
        stored_as_scd_type=2,
        track_history_column_list=TRACK)

5c. Run it. Click Dry run first (validates without reading data), then Run pipeline. Don't use Run file, which runs one file in isolation.

Expected: five tables in governance.kada_sync: landing_tbl_metadata, landing_col_metadata and landing_term_metadata with one row per extract row, and snapshot_tbl_metadata and snapshot_col_metadata with one current row per object.

In the snapshot tables, __START_AT and __END_AT are structs (file_last_modified, file_name) because sequence_by is a struct. Rows with __END_AT IS NULL are current.

Step 6: Cleanup views

Two views return the current snapshot row per object, set delete_flag = 'Y' for objects that no longer exist in Unity Catalog, and add the object type so the notebook knows a table from a view. Create them outside the pipeline because they read system.information_schema.

SQL
USE CATALOG governance; USE SCHEMA kada_sync;

CREATE OR REPLACE VIEW current_tbl_metadata AS
SELECT s.*,
       t.table_type,
       CASE WHEN t.table_name IS NULL THEN 'Y' ELSE 'N' END AS delete_flag
FROM snapshot_tbl_metadata s
LEFT JOIN system.information_schema.tables t
  ON  t.table_catalog = s.catalog_name
  AND t.table_schema  = s.schema_name
  AND t.table_name    = s.table_name
WHERE s.__END_AT IS NULL;

CREATE OR REPLACE VIEW current_col_metadata AS
SELECT s.*,
       t.table_type,
       CASE WHEN c.column_name IS NULL THEN 'Y' ELSE 'N' END AS delete_flag
FROM snapshot_col_metadata s
LEFT JOIN system.information_schema.columns c
  ON  c.table_catalog = s.catalog_name
  AND c.table_schema  = s.schema_name
  AND c.table_name    = s.table_name
  AND lower(c.column_name) = lower(s.column_name)
LEFT JOIN system.information_schema.tables t
  ON  t.table_catalog = s.catalog_name
  AND t.table_schema  = s.schema_name
  AND t.table_name    = s.table_name
WHERE s.__END_AT IS NULL;

Check:

SQL
SELECT delete_flag, table_type, count(*)
FROM governance.kada_sync.current_tbl_metadata
GROUP BY ALL ORDER BY ALL;

SELECT delete_flag, count(*)
FROM governance.kada_sync.current_col_metadata
GROUP BY ALL;

Most rows should be N. Many Y rows usually mean the location parser offsets are wrong, not that objects are missing; fix the function in Step 4 and re-run the pipeline with a full refresh.

Step 7: Apply comments and tags notebook

The notebook builds each object's comment (K description, a blank line, then sorted Term - definition lines) and tag set, compares it with the last successful push, and runs the SQL. Tables are processed in parallel; objects of the same table run one after another, because concurrent metadata changes on one table fail (tables) or overwrite each other (views).

7a. Create the notebook outside the pipeline folder

  1. Workspace → the pipeline's root folder → Create → Notebook, named apply_comments_tags, language Python, compute Serverless.

  2. Do not create it inside transformations/: the pipeline runs everything in that folder. The notebook header should show Excluded.

  3. Widget bar → gear → On widget change → Do nothing, so changing a dropdown never triggers a live run.

7b. Paste into cell 1:

import hashlib, json, re
from collections import defaultdict
from concurrent.futures import ThreadPoolExecutor
from pyspark.sql import functions as F, Window

dbutils.widgets.dropdown("obj_level", "TBL", ["TBL", "COL"])
dbutils.widgets.dropdown("load_type", "INC", ["INC", "FULL", "CATCHUP"])
dbutils.widgets.text("catchup_hours", "24")
dbutils.widgets.text("table_list", "ALL")       # ALL or cat.sch.tbl,cat.sch.tbl2
dbutils.widgets.dropdown("dry_run", "true", ["true", "false"])

LVL    = dbutils.widgets.get("obj_level")
LOAD   = dbutils.widgets.get("load_type")
HOURS  = int(dbutils.widgets.get("catchup_hours"))
TABLES = [t.strip().lower() for t in dbutils.widgets.get("table_list").split(",")]
DRY    = dbutils.widgets.get("dry_run") == "true"
SCH    = "governance.kada_sync"
TAG_MAX = 256                                       # UC tag value limit

# ---------- helpers ----------
q     = lambda i: "`" + i.replace("`", "``") + "`"
lit   = lambda s: "'" + s.replace("\\", "\\\\").replace("'", "\\'") + "'"
clean = lambda v: (v or "").strip() or None

# ---------- mapping ----------
map_rows = spark.table(f"{SCH}.kada_metadata_map") \
    .where(f"is_active AND metadata_obj_level = '{LVL}'").collect()
DESC_COL = next(r.file_column_name for r in map_rows if r.metadata_type == "COMMENT")
TERM_COL = next((r.file_column_name for r in map_rows if r.metadata_type == "TERMS"), None)
TAGS     = {r.metadata_name: r.file_column_name for r in map_rows if r.metadata_type == "TAG"}

# ---------- glossary: latest description per term ----------
w = Window.partitionBy(F.lower("name")).orderBy(F.desc("file_last_modified"))
terms = {r.name.strip().lower(): (r.name.strip(), r.description.strip())
         for r in spark.table(f"{SCH}.landing_term_metadata")
             .where("description IS NOT NULL")
             .withColumn("rn", F.row_number().over(w)).where("rn = 1").collect()}

# ---------- current rows in scope ----------
cur = spark.table(f"{SCH}.current_{LVL.lower()}_metadata").where("delete_flag = 'N'") \
    .where("table_type NOT IN ('MATERIALIZED_VIEW', 'STREAMING_TABLE')")   # owned by pipelines
if TABLES != ["all"]:
    cur = cur.where(F.concat_ws(".", "catalog_name", "schema_name", "table_name").isin(TABLES))
if LOAD == "CATCHUP":
    cur = cur.where(f"__START_AT.file_last_modified >= current_timestamp() - INTERVAL {HOURS} HOURS")
rows = cur.collect()

last = {r.full_name: r.payload_hash for r in spark.sql(f"""
    SELECT full_name, max_by(payload_hash, applied_at) AS payload_hash
    FROM {SCH}.applied_state WHERE obj_level = '{LVL}' AND status = 'OK'
    GROUP BY full_name""").collect()}

# ---------- build payload per object ----------
def build(r):
    d = r.asDict()
    desc = clean(d.get(DESC_COL))
    term_lines = []
    if TERM_COL and d.get(TERM_COL):
        for t in re.split(r"[,;\n]", d[TERM_COL]):
            hit = terms.get(t.strip().lower())
            if hit:
                term_lines.append(f"{hit[0]} - {hit[1]}")
    comment = "\n\n".join([p for p in [desc, "\n\n".join(sorted(set(term_lines)))] if p]) or None
    tags = {k: clean(d.get(c))[:TAG_MAX] for k, c in TAGS.items() if clean(d.get(c))}
    h = hashlib.md5(json.dumps([comment, sorted(tags.items())]).encode()).hexdigest()
    return d, comment, tags, h

def statements(d, comment, tags):
    tbl  = ".".join(q(d[p]) for p in ("catalog_name", "schema_name", "table_name"))
    kind = "VIEW" if d["table_type"] == "VIEW" else "TABLE"
    s = []
    if LVL == "TBL":
        s.append(f"COMMENT ON TABLE {tbl} IS {lit(comment) if comment else 'NULL'}")
        for k in TAGS:
            s.append(f"ALTER {kind} {tbl} SET TAGS ({lit(k)} = {lit(tags[k])})" if k in tags
                     else f"ALTER {kind} {tbl} UNSET TAGS ({lit(k)})")
    else:
        col = f"{tbl}.{q(d['column_name'])}"
        s.append(f"COMMENT ON COLUMN {col} IS {lit(comment) if comment else 'NULL'}")
        for k in TAGS:
            if kind == "TABLE":
                s.append(f"ALTER TABLE {tbl} ALTER COLUMN {q(d['column_name'])} "
                         + (f"SET TAGS ({lit(k)} = {lit(tags[k])})" if k in tags else f"UNSET TAGS ({lit(k)})"))
            else:   # view columns: SET TAG ON syntax (DBR 16.1+ / serverless)
                s.append(f"SET TAG ON COLUMN {col} {q(k)} = {q(tags[k])}" if k in tags
                         else f"UNSET TAG ON COLUMN {col} {q(k)}")
    return s

# ---------- group work by table ----------
work = defaultdict(list)          # table -> [(full_name, hash, stmts)]
for r in rows:
    d, comment, tags, h = build(r)
    if LOAD == "INC" and last.get(d["full_name"]) == h:
        continue
    tbl_key = f'{d["catalog_name"]}.{d["schema_name"]}.{d["table_name"]}'
    work[tbl_key].append((d["full_name"], h, statements(d, comment, tags)))
print(f"{LVL} {LOAD}: {len(rows)} in scope, {sum(len(v) for v in work.values())} to apply, dry_run={DRY}")

# ---------- apply ----------
def run(item):
    name, h, stmts = item
    if DRY:
        return (LVL, name, h, "DRY_RUN", "\n".join(stmts))
    errs = []
    for st in stmts:
        try:
            spark.sql(st)
        except Exception as e:
            if "UNSET" not in st:                     # unsetting a missing tag is harmless
                errs.append(f"{st[:120]} :: {str(e)[:300]}")
    return (LVL, name, h, "ERROR" if errs else "OK", "\n".join(errs) or None)

def run_table(items):   # one table's objects in sequence: no concurrent metadata changes on one table
    return [run(i) for i in items]

with ThreadPoolExecutor(max_workers=8) as ex:   # tables in parallel
    results = [r for batch in ex.map(run_table, work.values()) for r in batch]

if results:
    (spark.createDataFrame(results, "obj_level string, full_name string, payload_hash string, status string, error_msg string")
          .withColumn("applied_at", F.current_timestamp())
          .select("obj_level", "full_name", "payload_hash", "applied_at", "status", "error_msg")
          .write.mode("append").saveAsTable(f"{SCH}.applied_state"))

failed = sum(1 for r in results if r[3] == "ERROR")
if failed:
    raise Exception(f"{failed} objects failed - see {SCH}.applied_state")

7c. Add a review cell (cell 2):

SQL
%sql
SELECT full_name, status, error_msg AS detail, applied_at
FROM governance.kada_sync.applied_state
ORDER BY applied_at DESC
LIMIT 50;

7d. First run sequence. Set every widget first, then run cell 1. Text widgets save on Enter or when you click away. Start with one schema in table_list before running ALL.

#

obj_level

load_type

dry_run

Expected

Then

1

TBL

INC

true

TBL INC: n in scope, n to apply, dry_run=True

Review the generated SQL in cell 2 (DRY_RUN rows)

2

TBL

INC

false

Same counts, no error

Step 8 table checks

3

COL

INC

true

COL INC: n in scope, n to apply, dry_run=True

Review the SQL

4

COL

INC

false

Same counts, no error

Step 8 column checks

5

TBL, then COL

INC

false

0 to apply

Confirms incremental runs skip unchanged objects

Objects with delete_flag = 'Y' are excluded from scope. In the dry-run SQL, views use ALTER VIEW and view columns use SET TAG ON COLUMN; an empty field produces UNSET TAGS for that key.

Step 8: Verify in Unity Catalog

After the live table and column runs, every in-scope object should carry its K comment and tags. Replace <catalog> and <schema> with a synced schema.

Tables and views:

SQL
SELECT t.table_name, t.comment, tt.tag_name, tt.tag_value
FROM system.information_schema.tables t
LEFT JOIN system.information_schema.table_tags tt
  ON  tt.catalog_name = t.table_catalog AND tt.schema_name = t.table_schema AND tt.table_name = t.table_name
WHERE t.table_catalog = '<catalog>' AND t.table_schema = '<schema>'
ORDER BY t.table_name, tt.tag_name;

Columns:

SQL
SELECT c.table_name, c.column_name, c.comment, ct.tag_name, ct.tag_value
FROM system.information_schema.columns c
LEFT JOIN system.information_schema.column_tags ct
  ON  ct.catalog_name = c.table_catalog AND ct.schema_name = c.table_schema
  AND ct.table_name   = c.table_name   AND ct.column_name = c.column_name
WHERE c.table_catalog = '<catalog>' AND c.table_schema = '<schema>'
ORDER BY c.table_name, c.column_name, ct.tag_name;
  • Table comments show the K description, then glossary terms as Term - definition lines

  • Tags match K: category, classification, domain, owners, stewards, pii_data_det

  • Columns flagged as PII in K carry pii_data_det = Yes

  • applied_state shows no ERROR rows

In Catalog Explorer, open a synced table → Overview: the description shows the K text and terms, the right panel lists the tags, and the column grid shows each column's comment and tags.

Step 9: Automate with a Lakeflow Job

One job runs the pipeline and then the notebook for tables and columns in parallel, triggered whenever new files land in the volume.

In the UI

  1. Jobs & Pipelines → Create → Job, named kada_metadata_sync_job, run as the service principal.

  2. Task refresh_pipeline: type Pipeline, pipeline kada_metadata_sync.

  3. Task apply_tbl: type Notebook, path apply_comments_tags, compute Serverless, depends on refresh_pipeline. Parameters: obj_level = TBL, load_type = INC, dry_run = false, table_list = ALL, catchup_hours = 24.

  4. Task apply_col: same as apply_tbl with obj_level = COL, also depending on refresh_pipeline.

  5. Schedules & Triggers → Add trigger → File arrival, location /Volumes/governance/kada_sync/kada_extracts_ext/, Advanced → minimum time between triggers 15 minutes (lets all four K files land). Leave the trigger paused.

  6. Job notifications → add a team email on Failure.

  7. Run now. Expected: three green tasks; both notebook tasks print 0 to apply if nothing changed since Step 7.

  8. Unpause the trigger.

As code (Databricks Asset Bundle, resources/kada_metadata_sync_job.yml):

resources:
  jobs:
    kada_metadata_sync_job:
      name: kada_metadata_sync_job_${bundle.target}
      run_as:
        service_principal_name: <service principal application id>
      trigger:
        pause_status: PAUSED
        file_arrival:
          url: /Volumes/governance/kada_sync/kada_extracts_ext/
          min_time_between_triggers_seconds: 900
      max_concurrent_runs: 1
      parameters:
        - name: load_type
          default: INC
      tasks:
        - task_key: refresh_pipeline
          pipeline_task:
            pipeline_id: ${resources.pipelines.kada_metadata_sync.id}
        - task_key: apply_tbl
          depends_on: [{task_key: refresh_pipeline}]
          notebook_task:
            notebook_path: ../src/apply_comments_tags
            base_parameters: {obj_level: TBL, load_type: "{{job.parameters.load_type}}", dry_run: "false", table_list: ALL, catchup_hours: "24"}
        - task_key: apply_col
          depends_on: [{task_key: refresh_pipeline}]
          notebook_task:
            notebook_path: ../src/apply_comments_tags
            base_parameters: {obj_level: COL, load_type: "{{job.parameters.load_type}}", dry_run: "false", table_list: ALL, catchup_hours: "24"}
      email_notifications:
        on_failure: [<team distribution list>]

Operations

The job runs incrementally by default. Use the other modes from Run now with different parameters.

Situation

Action

Normal daily sync

Nothing: the file arrival trigger runs the job with load_type = INC

Re-push after an outage

Run now with load_type = CATCHUP (objects changed in the last catchup_hours)

Full reset, or tables rebuilt by dbt / CREATE OR REPLACE

Run now with load_type = FULL

Test a change safely

Run the notebook manually with dry_run = true and a narrow table_list

Mapping or parser changed

Update TRACK and the mapping table, then run the pipeline with Full refresh all, then a FULL notebook run

Suspend the sync

Pause the file arrival trigger; unpause to resume

Investigate failures

Query applied_state for status = 'ERROR'

Keep extract files in the volume: the pipeline reads only new files, but a full refresh reprocesses all of them.

Production checklist

  • Separate sync schema, volume, pipeline and job per environment (dev, test, prod)

  • Pipeline and job run as a service principal with USE CATALOG, USE SCHEMA, APPLY TAG and MODIFY (or ownership) on synced catalogs, READ VOLUME on the extract volume and write access to the sync schema

  • Governed tags (if used): a tag policy per key, and ASSIGN for the service principal

  • Sync scoped to the intended catalogs via the K saved-search filters

  • K is the only place descriptions are edited; comments typed in Catalog Explorer are overwritten on the next push

  • dbt models: persist_docs off for synced models, or a job task after dbt that runs the notebook with load_type = FULL for rebuilt tables (CREATE OR REPLACE drops tags)

  • Job failure emails go to a team list; applied_state errors reviewed

  • Storage lifecycle rule archives extract files older than about 90 days, keeping enough history for a full refresh

  • Pipeline, notebook and job kept in Git and deployed with Databricks Asset Bundles

  • Trigger unpaused only after a dry run against real extracts

Last updated: