Inspect table metadata

You've built pipelines, transformed data with Spark, and written clean Delta tables. But when someone asks "Where did this column come from?" or "Why is this table so big?", you freeze. That's the pain this lesson solves: inspecting lineage and table metadata in Databricks. Without these skills, you're flying blind in one of the most governed, audited platforms on the market — and you'll miss the very tools designed to make data engineers look like heroes. Let's fix that now.

The problem this lesson solves

Data engineering isn't just about moving data — it's about trust. Your downstream dashboards, ML models, and stakeholder reports all depend on knowing three things:

1. Where the data came from (lineage)
2. What's inside the table (schema, size, partitions)
3. Who can touch it, and how (governance metadata)

In Databricks, each table carries a rich metadata layer — schema, table properties, location, owner, and a full lineage graph showing every upstream source and downstream consumer. But most beginners ignore it, keeping only a mental map of their pipelines. That approach breaks the moment someone asks, "Which of my 50 tables feeds this report?" or "Why did my query slow down?"

The cost of ignoring metadata: - Blind debugging — you can't trace a bad value to its source. - Audit failures — you can't prove where sensitive data originated. - Wasted time — you re-create tables that already exist, because you didn't inspect them first.

Pro tip: In any modern data platform, metadata is the new data. The teams that master inspection ship faster and sleep better.

Core concept / mental model

Think of a table as a person. The metadata is their ID card — name, birthdate (creation time), height in MB, and owner. And lineage is their family tree — parents (source tables) and children (downstream consumers).

In Databricks, metadata and lineage live in Unity Catalog — a governance layer that centralizes access, auditing, and (yes) lineage across all workspaces.

Two key concepts: - Table metadata — structural and descriptive info: column names/types, constraints, table properties, partitioning, size, location (DBSQL), and owner. - Data lineage — a directed graph showing which data went into a table and which tables consume it. At the column level, lineage tracks how a specific column (say revenue) originates and transforms.

A picture in words:

bronze.orders ──► silver.orders_clean ──► gold.revenue_daily
     │                    │                     │
     └── column lineage:  order_id, amount ─────┘

Each arrow is a lineage edge. In Databricks, you can inspect this graph interactively or via SQL/Python APIs.

How it works step by step

Here's the logical flow when you inspect lineage and metadata in Databricks:

1. Locate the table — in your catalog and schema (catalog.schema.table).
2. Inspect metadata — via DESCRIBE EXTENDED, SHOW TBLPROPERTIES, or the Catalog Explorer UI.
3. View lineage — in the Lineage tab of Catalog Explorer, or via the lineage_entries virtual table / system tables.
4. Drill down to columns — expand any column to see exactly which source column contributed to it.
5. Export or automate — use the lineage API or system tables for regular audits.

The cause-and-effect chain matters: understanding how a table is built (lineage) deepens your trust in what it contains (metadata). And both inform who can access it (governance).

Blockquote: Always inspect first, query later. Spend 30 seconds on metadata, save 30 minutes of wrong results.

Hands-on walkthrough

Let's roll. We'll create a tiny ETL flow so you can see lineage appear, then inspect it.

Step 0 — Set up sample tables

from pyspark.sql import functions as F

# Create a source table
spark.sql("""
CREATE OR REPLACE TABLE sales.orders (
  order_id INT,
  customer_id INT,
  amount DECIMAL(10,2)
) USING DELTA
""")

# Write a few rows
spark.createDataFrame([(1, 101, 50.5), (2, 102, 100.0)], ["order_id","customer_id","amount"]).write.mode("append").saveAsTable("sales.orders")

# Create a downstream table
spark.sql("""
CREATE OR REPLACE TABLE sales.orders_clean AS
SELECT order_id, customer_id, amount
FROM sales.orders
WHERE amount > 0
""")

Step 1 — Inspect table metadata

from IPython.display import display, Markdown

display(Markdown("### Table Metadata"))
display(spark.sql("DESCRIBE EXTENDED sales.orders_clean"))

Expected output (excerpt):

col_name      data_type     comment
------------  ------------  -------
order_id      int           null
customer_id   int           null
amount        decimal(10,2) null

# Detailed Table Information
Name                 sales.orders_clean
Type                 MANAGED
Location             /user/hive/warehouse/sales.db/orders_clean
Provider             delta
Table Properties     [delta.minReaderVersion=1,...]

Step 2 — View column-level lineage with Python

import pandas as pd
from databricks.sdk import WorkspaceClient

w = WorkspaceClient()
columns = w.lineage.list_table_columns(
    table_full_name="sales.orders_clean"
)

for col in columns:
    if col.column_name == "amount":
        print("Column: amount")
        for up in col.upstream_cols:
            print(f"  upstream: {up.table_name}.{up.name}")
        for down in col.downstream_cols:
            print(f"  downstream: {down.table_name}.{down.name}")

Expected output:

Column: amount
  upstream: sales.orders.amount
  downstream: (none yet)

Step 3 — Inspect lineage with SQL (Unity Catalog system tables)

-- Requires lineage tracking enabled and system tables accessible
SELECT * FROM system.lineage.table_lineage
WHERE table_name = 'sales.orders_clean';

If your workspace has lineage enabled, you'll see rows with input_table and output_table columns.

Pro tip: Catalog Explorer is your friend. For a quick visual, open a table, click the Lineage tab, and drag the graph to see how columns connect. No code needed.

Compare options / when to choose what

Databricks offers several ways to inspect lineage and table metadata. The right choice depends on your need: a quick look vs. a repeatable audit.

Choose Catalog Explorer when you're investigating a single table. Choose SQL when you want to embed checks like "is this table Delta?" in your regression tests. And choose the SDK when you need to audit lineage across hundreds of tables — for example, before a migration.

Blockquote: Don't over-engineer. Start with DESCRIBE EXTENDED and the UI; graduate to system tables when you hit a repeated audit ask.

Troubleshooting & edge cases

1. "Cannot find table catalog.schema.table"

You might be in the wrong catalog or schema. Check your current context:

spark.sql("SELECT current_catalog() AS catalog, current_database() AS schema").show()

If you're in hive_metastore, sales.orders_clean may live in another catalog. Fix by fully qualifying the name or switching catalog with USE CATALOG.

2. Lineage is empty or missing

• Ensure the table was written after lineage tracking was enabled (check workspace admin setting).
• Column-level lineage only appears for tables written via Spark or SQL, not for external or external-source tables (e.g., CSV directly registered).
• If you used CREATE TABLE AS SELECT from a view, lineage may point to the view, not the underlying table — chase it further.

3. "SHOW TBLPROPERTIES" returns too little

Some properties (like delta.minReaderVersion) are system-generated. If you need custom metadata, add your own:

spark.sql('ALTER TABLE sales.orders_clean SET TBLPROPERTIES (\'quality\' = \'gold\')')

Then they'll appear in SHOW TBLPROPERTIES — perfect for tagging tables.

4. I see duplicate lineage rows

Lineage system tables can record multiple runs. Filter by last_updated timestamp or deduplicate on input_table and output_table.

What you learned & what's next

You now know how to inspect lineage and table metadata — from DESCRIBE EXTENDED to column-level lineage graphs in Catalog Explorer. You can answer the primal questions: where did this come from? and what governs this table? Those skills directly support every downstream task: debugging pipelines, proving data quality, and building trustworthy reports.

In the next lesson, you'll build on this foundation by managing table access controls — putting that metadata to work by deciding who can read and write those tables. You'll move from trusting data to securing it.

Go explore. Open a notebook, create a small Delta table, and inspect its metadata and lineage. It's the fastest way to make this stick.