You can enumerate your catalog from the driver — listTables(), listColumns() — and it'll happily tell you a table's tableType is EXTERNAL. Useful… except the catalog Table object stops exactly one field short of the only thing that makes a table external: the path its data actually lives at.

for t in spark.catalog.listTables("default"):
    print(t.name, t.tableType, t.isTemporary)
# local_external_table EXTERNAL False

Great — it's external. External to what? The Table object doesn't say. dir(t) is name, database, catalog, description, tableType, isTemporary. No location. No provider. No partitioning. The catalog's list API is deliberately lightweight — identity + type, nothing about storage.

Where the location actually lives

The location isn't catalog-list metadata; it's table storage metadata. In the JVM it's CatalogTable.storage.locationUri (a CatalogStorageFormat), reachable through SessionCatalog.getTableMetadata(...). From PySpark you don't get a binding to that off a Table object — the pragmatic way to read it is to ask the table to describe itself:

rows = spark.sql("DESCRIBE FORMATTED default.local_external_table").collect()
for r in rows:
    if r["col_name"].strip() in ("Location", "Provider", "Type"):
        print(r["col_name"].strip(), "=", r["data_type"].strip())
# Type     = EXTERNAL
# Provider = parquet
# Location = file:/tmp/my_local_data

DESCRIBE FORMATTED (or DESCRIBE EXTENDED) returns the extended block as plain col_name/data_type rows. Location, Provider, Type, partitioning, table properties — all there. That's the layer listTables() skips.

Why the location is the whole point

EXTERNAL only means something relative to a path. The defining behavior is DROP:

-- external table over an existing dir
CREATE TABLE local_external_table (name STRING, age INT)
USING parquet LOCATION 'file:///tmp/my_local_data';

DROP TABLE local_external_table;     -- metadata gone, files at /tmp/my_local_data UNTOUCHED

CREATE TABLE local_external_table (name STRING, age INT)
USING parquet LOCATION 'file:///tmp/my_local_data';
SELECT * FROM local_external_table;  -- data's back. it never left.

Drop a MANAGED table and Spark deletes the data dir it owns. Drop an EXTERNAL table and only the catalog entry disappears — the files stay, and a new table at the same LOCATION picks them right back up. That single bit (Type) decides whether DROP TABLE is a metadata op or a destructive one. But you can't reason about it — can't tell which table would nuke which directory, can't spot two tables silently sharing a path — without the location. Which is the field the catalog list won't give you.

Two gotchas worth knowing

• tableType can come back null. Depending on Spark version / how the table was created, listTables().tableType is sometimes empty or generic. DESCRIBE FORMATTED's Type row is the reliable MANAGED/EXTERNAL signal — it's read straight off the table metadata, not the list projection. If you're keying logic on managed-vs-external, prefer describe.
• Location ≠ Provider. Provider (parquet/csv/json) is the format; Location is the path. An external CSV and an external Parquet table can sit in the same directory tree and only Provider tells them apart. Both are in the describe output, neither is in listTables().

Why I'm posting

A while back I added a Catalog tab to Drillbench (my local PySpark playground) because the stock Spark Web UI has none — the catalog is driver-side SessionCatalog, not in the REST API, so the official UI can't draw it [prior post]. It badged tables MANAGED/EXTERNAL/VIEW/TEMP… and then a demo made the gap obvious: the badge said EXTERNAL but the tab couldn't say external to where. So I closed it.

Expand a table row now and you get its Location, its Provider, and a plain-language line on what DROP does — "EXTERNAL — DROP removes only the catalog entry; the data at this path stays" vs "MANAGED — DROP deletes the catalog entry AND the data files." The plumbing matches this post: the catalog probe can't get location off the Table object, so it runs DESCRIBE FORMATTED per persistent table and lifts Location/Provider/Type from the rows (and uses describe's Type to backfill the occasionally-null tableType). It's the same wall as last time — the lightweight list API on one side, the storage metadata on the other — just one field further in.

What I'd like from you

• Anyone been burned by the MANAGED DROP deleting data they thought was safe — or assumed EXTERNAL and pointed two tables at one path?
• How do you inspect a table's location day-to-day — DESCRIBE FORMATTED, SHOW CREATE TABLE, or straight to the metastore?
• Unity Catalog / Glue folks: does the managed-vs-external Location story still hold the same way through an external catalog, or does it change shape?

Code reference

Corresponds to commit b71d253 (per-table Location/Provider + DROP hint: DESCRIBE FORMATTED in the catalog probe in kernel.py, CatalogTable fields in types.ts, renderColumns in SparkUI.tsx):

https://github.com/CoreGems/pyspark-drillbench/commit/b71d2539fa5c556806150c8d0914c3da34417fdc