Databricks Unity Catalog Managed vs. External Table Migration Strategy Tutorial

This tutorial demonstrates migrating tables between Managed and External table types within Unity Catalog using Databricks SQL. We’ll focus on privilege management and data governance throughout the process.

Scenario

We have a legacy Parquet file (/tmp/legacy_data.parquet) containing sales data. We want to migrate this data to Unity Catalog, initially as an External table, and then convert it to a Managed table with appropriate permissions and data quality constraints. The goal is to control access and maintain data integrity.

Script 1: Creating the External Table

This script creates an External table linked to the legacy Parquet file. It demonstrates the initial creation and grants basic access.

CREATE TABLE external_sales
  TYPE parquet
  LOCATION '/tmp/legacy_data.parquet'
  -- No options are needed for simplicity in this tutorial
  ;
GRANT SELECT ON TABLE external_sales TO ROLE analytics_role;
SELECT COUNT() FROM external_sales;
  

Explanation: This script creates an External table named `external_sales` referencing the location of the legacy Parquet file. We then grant `SELECT` privileges to the `analytics_role` which is crucial for controlling access. The final `SELECT` statement confirms the existence of at least one row in the External table.

 1
    

Script 2: Converting External Table to Managed Table

This script converts the External table to a Managed table, adding a primary key constraint and updating permissions. This demonstrates how Unity Catalog manages the underlying data.

CREATE TABLE managed_sales (
  sale_id INT PRIMARY KEY,
  product_name STRING,
  sale_date DATE,
  sales_amount DECIMAL(10, 2)
)
  TYPE TEMPTABLE
  LOCATION 'rboto://default/sales_data'  -- Use RBoto for Managed Tables
  ON SCHEMA sales_db.sales_schema
  ;

CREATE OR REPLACE TABLE sales_db.sales_schema.managed_sales
  PARTITION BY sale_date
  AS
  SELECT
    sale_id,
    product_name,
    sale_date,
    sales_amount
  FROM external_sales;

ALTER TABLE sales_db.sales_schema.managed_sales
  ADD CONSTRAINT managed_sales_pk PRIMARY KEY (sale_id);

GRANT SELECT ON TABLE sales_db.sales_schema.managed_sales TO ROLE analytics_role;
SELECT COUNT() FROM sales_db.sales_schema.managed_sales;
  

Explanation: We first create a new Managed table named `managed_sales` with a primary key constraint on `sale_id`. The `TYPE TEMPTABLE` clause is crucial for this conversion process. We then copy the data from the `external_sales` External table into the newly created Managed table. After the data copy, we add the primary key constraint to the Managed table. Finally, we adjust the permissions to the Managed table to allow the `analytics_role` to select data.

 1
    

Script 3: Verifying Data and Permissions

This script validates the data in the Managed table and confirms the correct privileges are assigned. It checks data consistency and permissions.

SELECT COUNT() FROM sales_db.sales_schema.managed_sales;
SELECT COUNT() FROM sales_db.sales_schema.managed_sales WHERE sale_id = 1;
GRANT ALTER ON TABLE sales_db.sales_schema.managed_sales TO ROLE data_engineer_role;
SELECT COUNT() FROM sales_db.sales_schema.managed_sales;
SELECT  FROM sales_db.sales_schema.managed_sales LIMIT 10;
  

Explanation: The first `SELECT` statement verifies the total number of rows in the `managed_sales` table. The second `SELECT` confirms the existence of a specific row (using `sale_id = 1`). Next, the `GRANT ALTER` statement gives the `data_engineer_role` the ability to modify the Managed table, which is a key difference from the `analytics_role` which only has `SELECT` privileges. The final `SELECT` statement shows a sample of the data.

 1
    

 1
    

 10