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:
-
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.
-
Auto Loader landing tables: a Lakeflow pipeline uses Auto Loader to load only new files into raw landing tables.
-
Snapshot tables: an AUTO CDC flow keeps Type 2 SCD history per object, adding a version only when a tracked field changes.
-
Cleanup views: return the current row per object and flag objects that no longer exist in Unity Catalog.
-
Comment and tag update: a notebook builds each object's comment and tags, skips unchanged objects and runs the SQL.
-
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 CATALOGon the metastore, orUSE CATALOG+CREATE SCHEMAon 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 |
|
Control tables, pipeline tables, views and function |
|
Extract volume |
|
External volume where K files land: |
|
Volume path |
|
Read by Auto Loader and the file arrival trigger |
|
External location |
|
Covers the Blob container folder K writes to |
|
Pipeline |
|
Landing and SCD2 snapshot tables |
|
Notebook |
|
Writes comments and tags |
|
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
-
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. -
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.
-
Confirm Allow storage account key access is enabled and copy key1 for the K connection (Step 2).
1a (AWS, optional). Amazon S3 storage
-
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. -
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.
-
For K, create an IAM user (or the access method your K tenant supports) limited to
s3:PutObjectands3:ListBucketon thekada/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.
-
A K admin connects K to the Blob container and schedules four daily extracts, one per file type.
-
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.
-
Create four saved searches filtered to the Databricks source, and an alert on each:
|
Saved search / alert |
Built in K |
Columns |
Alert path |
|---|---|---|---|
|
|
Search: Asset = Table |
Add Terms, PII data detected; remove Trust |
|
|
|
Linked Search → Find Column by Table: Subtype = Table |
Column side: add Terms, PII data detected; remove Trust |
|
|
|
Linked Search → Find Column by Table: Subtype = View |
As above |
|
|
|
Search: Asset = Collection Instance, Collection = glossaries |
Description only |
|
-
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.
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:
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:
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
-
Sidebar → Jobs & Pipelines → Create → ETL pipeline.
-
Name it
kada_metadata_sync; set default cataloggovernanceand schemakada_sync. -
Choose Start with an empty file, language Python. Rename the file in the
transformations/folder tokada_pipeline.py. -
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.
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.
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:
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
-
Workspace → the pipeline's root folder → Create → Notebook, named
apply_comments_tags, language Python, compute Serverless. -
Do not create it inside
transformations/: the pipeline runs everything in that folder. The notebook header should show Excluded. -
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
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 |
|
Review the generated SQL in cell 2 ( |
|
2 |
TBL |
INC |
false |
Same counts, no error |
Step 8 table checks |
|
3 |
COL |
INC |
true |
|
Review the SQL |
|
4 |
COL |
INC |
false |
Same counts, no error |
Step 8 column checks |
|
5 |
TBL, then COL |
INC |
false |
|
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:
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:
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 - definitionlines -
Tags match K: category, classification, domain, owners, stewards, pii_data_det
-
Columns flagged as PII in K carry
pii_data_det = Yes -
applied_stateshows noERRORrows
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
-
Jobs & Pipelines → Create → Job, named
kada_metadata_sync_job, run as the service principal. -
Task
refresh_pipeline: type Pipeline, pipelinekada_metadata_sync. -
Task
apply_tbl: type Notebook, pathapply_comments_tags, compute Serverless, depends onrefresh_pipeline. Parameters:obj_level = TBL,load_type = INC,dry_run = false,table_list = ALL,catchup_hours = 24. -
Task
apply_col: same asapply_tblwithobj_level = COL, also depending onrefresh_pipeline. -
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. -
Job notifications → add a team email on Failure.
-
Run now. Expected: three green tasks; both notebook tasks print
0 to applyif nothing changed since Step 7. -
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 |
|
Re-push after an outage |
Run now with |
|
Full reset, or tables rebuilt by dbt / |
Run now with |
|
Test a change safely |
Run the notebook manually with |
|
Mapping or parser changed |
Update |
|
Suspend the sync |
Pause the file arrival trigger; unpause to resume |
|
Investigate failures |
Query |
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 TAGandMODIFY(or ownership) on synced catalogs,READ VOLUMEon the extract volume and write access to the sync schema -
Governed tags (if used): a tag policy per key, and
ASSIGNfor 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_docsoff for synced models, or a job task after dbt that runs the notebook withload_type = FULLfor rebuilt tables (CREATE OR REPLACEdrops tags) -
Job failure emails go to a team list;
applied_stateerrors 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