Back to Advanced Data Engineering
Q15Question 15 of 24

Temporary views, global temporary views, and managed vs external tables

Key Concepts
  • A temporary view (createOrReplaceTempView) is session-scoped, invisible to other sessions, and vanishes when the session ends.
  • A global temporary view (createOrReplaceGlobalTempView) is application-scoped, accessed via global_temp.view_name, and visible across sessions.
  • Both views are virtual: they re-execute their plan on each query and store no data.
  • A managed table lets Spark own both data and metadata; DROP TABLE deletes the underlying files.
  • An external table lets Spark own only metadata; DROP TABLE removes the catalog entry but leaves the files intact.
  • In banking, regulated datasets are almost always external tables so an accidental drop cannot irrecoverably delete data.

Definition

Temp views are session-scoped names for DataFrames; global temp views are app-wide via global_temp; managed tables let Spark own data and metadata so drop deletes files, while external tables let Spark own only metadata so drop keeps the files.
  • Temporary view – created with createOrReplaceTempView, it registers a DataFrame under a name for Spark SQL queries within the current session only.
  • Global temporary view – created with createOrReplaceGlobalTempView, it is tied to the application, visible across sessions, and accessed through the global_temp database.
  • Managed table – Spark controls both data and metadata; the data lives in the warehouse directory and DROP TABLE deletes the files.
  • External table – Spark controls only metadata; the data lives at a path you specified and DROP TABLE removes only the catalog entry, leaving the files intact.

At the National Bank of India, a temporary view is like a sticky note naming a report on your own desk – only you during your current shift can refer to it, and it is gone when you leave. A global temporary view is a sticky note pinned to a shared board in the "global" corner, so colleagues in other rooms of the same building can also see it. A managed table is a ledger the bank both owns and stores in its own vault – discard the entry and the pages are shredded. An external table is a ledger entry pointing to documents in an outside archive – discard the entry and the outside documents remain untouched.

Understanding the Concepts

What is a temporary view?

A temporary view is a named, queryable handle on a DataFrame. It is not stored data – it registers the DataFrame's logical plan under a name in the session's catalog.

PYTHON
from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("ViewsDemo").getOrCreate()

txn = spark.read.parquet("/lake/silver/transactions")

# Register as a temp view
txn.createOrReplaceTempView("transactions")

# Query it with SQL
high_value = spark.sql("""
    SELECT account_id, SUM(amount) AS total
    FROM transactions
    WHERE amount > 50000
    GROUP BY account_id
""")
high_value.show()

The view is session-scoped: another session in the same application cannot see it, and it vanishes when the session closes.

What is a global temporary view?

A global temporary view extends visibility to the whole Spark application. It lives in a special global_temp database.

PYTHON
# Register as a global temp view
txn.createOrReplaceGlobalTempView("all_transactions")

# Must qualify with global_temp
result = spark.sql("""
    SELECT branch_code, COUNT(*) AS txn_count
    FROM global_temp.all_transactions
    GROUP BY branch_code
""")
result.show()

# Any other session in the same application can also query it:
# spark2.sql("SELECT * FROM global_temp.all_transactions")

The global_temp qualifier is mandatory. The view disappears when the application ends.

How are views different from tables?

Views are virtual – they store no data and re-execute their plan on every query. Tables persist data to storage.

PYTHON
# View: no data stored, plan re-executed each query
txn.createOrReplaceTempView("txn_view")
spark.sql("SELECT COUNT(*) FROM txn_view").show()  # re-reads parquet
spark.sql("SELECT COUNT(*) FROM txn_view").show()  # re-reads again

# Table: data persisted to disk
txn.write.saveAsTable("txn_table")
spark.sql("SELECT COUNT(*) FROM txn_table").show()  # reads stored files

What is a managed table?

A managed table is one where Spark controls both the metadata (catalog entry) and the data files (stored in the warehouse directory).

PYTHON
# Create a managed table – Spark places files in the warehouse
txn.write.format("delta").saveAsTable("managed_transactions")

# Data lives at: spark-warehouse/managed_transactions/

# DROP deletes BOTH the catalog entry AND the files
spark.sql("DROP TABLE managed_transactions")
# The files are gone – this is the risk

What is an external table?

An external table is one where you specify the data location. Spark controls only the metadata.

PYTHON
# Create an external table pointing at an existing path
txn.write.format("delta").save("/lake/gold/transactions")

spark.sql("""
    CREATE TABLE external_transactions
    USING delta
    LOCATION '/lake/gold/transactions'
""")

# DROP removes only the catalog entry
spark.sql("DROP TABLE external_transactions")
# The files at /lake/gold/transactions/ are still intact

Why does the managed vs external choice matter in banking?

The choice is about data ownership and blast radius. An accidental DROP TABLE on a managed table irrecoverably deletes regulated data.

PYTHON
# Dangerous: regulated data in a managed table
txn.write.saveAsTable("customer_kyc")  # managed
spark.sql("DROP TABLE customer_kyc")   # files deleted – compliance violation

# Safe: regulated data in an external table
spark.sql("""
    CREATE TABLE customer_kyc
    USING delta
    LOCATION '/lake/protected/customer_kyc'
""")
spark.sql("DROP TABLE customer_kyc")   # catalog entry removed, files safe

At the National Bank of India, critical shared datasets are always external tables with the files protected independently. Managed tables are reserved for scratch aggregates whose lifecycle should be bound to the table.

When do you use each?

The decision tree is straightforward.

PYTHON
# Temp view: break a complex pipeline into readable SQL steps
step1 = txn.filter(F.col("txn_date") >= "2026-10-01")
step1.createOrReplaceTempView("october_txns")
result = spark.sql("SELECT account_id, SUM(amount) FROM october_txns GROUP BY account_id")

# Global temp view: share across sessions in one app
step1.createOrReplaceGlobalTempView("october_txns_shared")

# Managed table: scratch data whose lifecycle follows the table
result.write.saveAsTable("scratch_october_totals")

# External table: shared, regulated, or multi-tool data
result.write.format("delta").save("/lake/gold/october_totals")
spark.sql("CREATE TABLE october_totals USING delta LOCATION '/lake/gold/october_totals'")

What should a senior engineer know?

  • Views are cheap (no storage) but recompute on every query. Cached DataFrames or tables avoid recomputation.
  • Managed vs external is fundamentally about blast radius. Critical data should be external.
  • Delta Lake adds time travel, so even a managed Delta table can be recovered within the retention period.
  • In multi-tool environments (Spark, Presto, Hive), external tables with a shared metastore allow all tools to access the same data.
PYTHON
# Senior pattern: combine external table with Delta time travel for safety
spark.sql("""
    SELECT * FROM external_transactions VERSION AS OF 5
""")
# Even after an accidental overwrite, Delta can roll back

What are common interview follow-up questions?

  • Can two sessions see the same temp view? No, temp views are session-scoped. Use global temp views or tables for cross-session access.
  • What happens to a managed table's files when you drop the database? The files are deleted along with the catalog entry.
  • Can you convert a managed table to external? Use ALTER TABLE SET LOCATION to point to an external path.

Visual Overview

DIAGRAM
  View and Table Scoping
  ──────────────────────

  ┌──────────────────────────────────────────┐
  │  Application (SparkContext)              │
  │                                          │
  │  ┌──────────────────────────────┐        │
  │  │ global_temp database          │        │
  │  │ ┌──────────────────────┐     │        │
  │  │ │ global temp views    │     │        │
  │  │ │ (visible to all      │     │        │
  │  │ │  sessions in app)    │     │        │
  │  │ └──────────────────────┘     │        │
  │  └──────────────────────────────┘        │
  │                                          │
  │  ┌────────────┐  ┌────────────┐          │
  │  │ Session A  │  │ Session B  │          │
  │  │ temp views │  │ temp views │          │
  │  │ (private)  │  │ (private)  │          │
  │  └────────────┘  └────────────┘          │
  └──────────────────────────────────────────┘

  Managed vs External Table
  ─────────────────────────
  Managed:   DROP → catalog entry DELETED + files DELETED
  External:  DROP → catalog entry DELETED + files KEPT

Key Takeaway

Temporary views and global temporary views are named handles on DataFrames that differ in scope. A temp view is session-scoped and invisible to other sessions; a global temp view lives in the global_temp database and is visible to all sessions in the application. Both are virtual – they re-execute their plan on each query and store no data.

Managed and external tables differ in data ownership. A managed table lets Spark own both metadata and files, so DROP TABLE deletes everything. An external table lets Spark own only metadata, so DROP TABLE removes the catalog entry but the files stay. The choice is about blast radius: in banking, regulated datasets should be external so an accidental drop cannot irrecoverably delete data.

At the National Bank of India, temp views were used to break reporting jobs into readable SQL steps, global temp views shared filtered datasets across cooperating sessions, and the managed-versus-external choice was strict. All regulated and shared datasets were external tables pointing at protected paths, while managed tables were reserved for scratch aggregates whose existence should genuinely be bound to the table lifecycle.

Remember one sentence: temp views are session-scoped and virtual, global temp views are app-scoped via global_temp, managed tables delete files on drop, and external tables keep files on drop – so always use external for regulated data.

---

Share
Use ← → arrow keys to navigate