<\/p>\n\n\n\n
This tutorial will walk you through core Delta Lake functionality in Databricks<\/strong>, including catalogs, schemas, tables, views, CTAS, deep clone, and shallow clone. Each section is backed with SQL and PySpark examples<\/strong> so you can directly try them inside a Databricks notebook.<\/p>\n\n\n\n A Delta Table<\/strong> is a Databricks table format that provides:<\/p>\n\n\n\n \ud83d\udc49 You can think of Delta tables as Parquet tables + transaction log<\/strong> that keeps track of all changes.<\/p>\n\n\n\n Catalogs are the top-level containers<\/strong> in Unity Catalog. \ud83d\udccc Example output:<\/p>\n\n\n\n Schemas (databases) live inside a catalog.<\/p>\n\n\n\n \ud83d\udccc Example:<\/p>\n\n\n\n Tables live inside schemas.<\/p>\n\n\n\n Output shows:<\/p>\n\n\n\n Using Python:<\/p>\n\n\n\n \ud83d\udc49 Returns When creating a table, schema, or catalog, you can avoid errors by using \ud83d\udccc Re-running won\u2019t fail if the table already exists.<\/p>\n\n\n\n Temporary views:<\/p>\n\n\n\n \ud83d\udc49 Will disappear after cluster termination.<\/p>\n\n\n\n Permanent views are stored metadata and persist beyond sessions.<\/p>\n\n\n\n \ud83d\udc49 Stored under schema (like tables, but no data copy).<\/p>\n\n\n\n If you want to update it:<\/p>\n\n\n\n CTAS physically copies data<\/strong> into a new Delta table.<\/p>\n\n\n\n \ud83d\udc49 Creates a new table with its own physical data files.<\/p>\n\n\n\n Deep Clone<\/strong> copies both metadata and data<\/strong> into a new location.<\/p>\n\n\n\n \u2705 Metadata + data are fully duplicated at a new location. Shallow Clone<\/strong> copies only metadata<\/strong>. Data still points to the original table at the specific version.<\/p>\n\n\n\n Example:<\/p>\n\n\n\n Delta tables support versioning.<\/p>\n\n\n\n By now you understand:<\/p>\n\n\n\n This covers the core building blocks of Delta Lake in Databricks<\/strong>. \ud83d\ude80<\/p>\n\n\n\n This tutorial will walk you through core Delta Lake functionality in Databricks, including catalogs, schemas, tables, views, CTAS, deep clone, and shallow clone. Each section is backed… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-781","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/781","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=781"}],"version-history":[{"count":1,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/781\/revisions"}],"predecessor-version":[{"id":782,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/781\/revisions\/782"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=781"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=781"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=781"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\n\n\n1. \ud83d\udd39 Introduction to Delta Tables<\/h2>\n\n\n\n
\n
\n\n\n\n2. \ud83d\udd39 Listing Catalogs<\/h2>\n\n\n\n
You can see which catalogs you have with:<\/p>\n\n\n\n-- List all catalogs\nSHOW CATALOGS;\n\n-- Search catalogs starting with 'dev'\nSHOW CATALOGS LIKE 'dev*';\n\n-- Search catalogs containing 'm'\nSHOW CATALOGS LIKE '*m*';\n<\/code><\/pre>\n\n\n\ndev\ndev_external\nhive_metastore\n<\/code><\/pre>\n\n\n\n
\n\n\n\n3. \ud83d\udd39 Listing Schemas<\/h2>\n\n\n\n
-- Show schemas in a catalog\nSHOW SCHEMAS IN dev;\n\n-- Search schemas starting with 'info'\nSHOW SCHEMAS IN dev LIKE 'info*';\n<\/code><\/pre>\n\n\n\ninformation_schema\nbronze\nsilver\ngold\n<\/code><\/pre>\n\n\n\n
\n\n\n\n4. \ud83d\udd39 Listing Tables<\/h2>\n\n\n\n
-- List tables in schema\nSHOW TABLES IN dev.bronze;\n\n-- Search tables starting with 'sales'\nSHOW TABLES IN dev.bronze LIKE 'sales*';\n<\/code><\/pre>\n\n\n\n\n
\n\n\n\n5. \ud83d\udd39 Check if a Table Exists (PySpark)<\/h2>\n\n\n\n
# Check if table exists\nspark.catalog.tableExists(\"dev.bronze.sales_external\")\n\n# Example: Non-existing table\nspark.catalog.tableExists(\"dev.bronze.sales_external_1\")\n<\/code><\/pre>\n\n\n\nTrue<\/code> or False<\/code>.<\/p>\n\n\n\n
\n\n\n\n6. \ud83d\udd39 Creating Tables with IF NOT EXISTS<\/h2>\n\n\n\n
IF NOT EXISTS<\/code>.<\/p>\n\n\n\n-- Create a table safely\nCREATE TABLE IF NOT EXISTS dev.bronze.emp (\n emp_id INT,\n emp_name STRING,\n dept_code STRING,\n salary DOUBLE\n);\n\n-- Insert some data\nINSERT INTO dev.bronze.emp VALUES\n(1, 'Alice', 'D101', 5000),\n(2, 'Bob', 'D102', 6000),\n(3, 'Charlie', 'D101', 5500),\n(4, 'David', 'D103', 7000);\n\n-- Verify data\nSELECT * FROM dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n7. \ud83d\udd39 Temporary Views<\/h2>\n\n\n\n
\n
-- Create a temporary view\nCREATE OR REPLACE TEMP VIEW emp_tmp_view AS\nSELECT * FROM dev.bronze.emp WHERE dept_code = 'D101';\n\n-- Query it\nSELECT * FROM emp_tmp_view;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n8. \ud83d\udd39 Permanent Views<\/h2>\n\n\n\n
-- Create a permanent view\nCREATE VIEW dev.bronze.emp_view AS\nSELECT * FROM dev.bronze.emp WHERE dept_code = 'D102';\n\n-- Query it\nSELECT * FROM dev.bronze.emp_view;\n<\/code><\/pre>\n\n\n\nCREATE OR REPLACE VIEW dev.bronze.emp_view AS\nSELECT * FROM dev.bronze.emp WHERE dept_code = 'D101';\n<\/code><\/pre>\n\n\n\n
\n\n\n\n9. \ud83d\udd39 CTAS (Create Table As Select)<\/h2>\n\n\n\n
CREATE TABLE dev.bronze.emp_ctas AS\nSELECT * FROM dev.bronze.emp;\n\n-- Verify\nSELECT * FROM dev.bronze.emp_ctas;\n<\/code><\/pre>\n\n\n\n
\n\n\n\n10. \ud83d\udd39 Deep Clone of Delta Table<\/h2>\n\n\n\n
CREATE TABLE dev.bronze.emp_dc\nDEEP CLONE dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n
\u2705 Safer than CTAS because it preserves all metadata<\/strong> (e.g., partitioning, nullability).<\/p>\n\n\n\n
\n\n\n\n11. \ud83d\udd39 Shallow Clone of Delta Table<\/h2>\n\n\n\n
CREATE TABLE dev.bronze.emp_sc\nSHALLOW CLONE dev.bronze.emp;\n<\/code><\/pre>\n\n\n\n\n
\n
emp<\/code>, shallow clone won\u2019t see them (it\u2019s tied to older version).<\/li>\n\n\n\nemp_sc<\/code>, Databricks starts storing new files for the clone.<\/li>\n<\/ul>\n\n\n\n
\n\n\n\n12. \ud83d\udd39 Comparing CTAS, Deep Clone, Shallow Clone<\/h2>\n\n\n\n
Feature<\/th> CTAS<\/th> Deep Clone<\/th> Shallow Clone<\/th><\/tr><\/thead> Metadata copied<\/td> \u274c (some may be lost)<\/td> \u2705 Full metadata<\/td> \u2705 Full metadata<\/td><\/tr> Data copied<\/td> \u2705 Yes<\/td> \u2705 Yes<\/td> \u274c No<\/td><\/tr> Location<\/td> New folder<\/td> New folder<\/td> New folder (points to old)<\/td><\/tr> Use case<\/td> Quick duplicate table<\/td> Reliable full replica<\/td> Lightweight dev\/test copy<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n13. \ud83d\udd39 History & Time Travel<\/h2>\n\n\n\n
-- See history\nDESCRIBE HISTORY dev.bronze.emp;\n\n-- Query old version\nSELECT * FROM dev.bronze.emp VERSION AS OF 0;\n\n-- Or use timestamp\nSELECT * FROM dev.bronze.emp TIMESTAMP AS OF '2023-01-01 00:00:00';\n<\/code><\/pre>\n\n\n\n
\n\n\n\n\u2705 Conclusion<\/h1>\n\n\n\n
\n
IF NOT EXISTS<\/code> to avoid errors<\/li>\n\n\n\n
\n","protected":false},"excerpt":{"rendered":"