Db note short

19 September 2025 15:52

Q: What is the difference between Control Plane and Data Plane in Databricks?
Answer:
In Databricks, the architecture is divided into Control Plane and Data Plane for security and performance:
• Control Plane:
○ Managed by Databricks.
○ Hosts services like job scheduling, cluster management, notebook workspace, and REST APIs.
○ Stores only metadata (not customer data).
○ Examples: Notebooks, jobs UI, cluster configurations.
• Data Plane:
○ Runs inside the customer’s cloud account (Azure, AWS, or GCP).
○ Actual data processing and storage happen here.
○ Clusters (VMs) are provisioned in the data plane to access and process customer data.
○ Ensures that sensitive data never leaves the customer’s environment.
In short, the Control Plane manages Databricks services, while the Data Plane handles actual data computation and storage securely within the
customer’s environment.

Q: What is Access Connector in Azure Databricks?

Answer:
The Azure Databricks Access Connector is a special managed identity resource that allows Databricks to securely access Azure resources like ADLS
Gen2. Instead of using service principals and secrets, we assign RBAC roles (like Storage Blob Data Contributor) to the Access Connector, and Databricks
clusters use this identity to authenticate via Azure Active Directory (AAD).
In short: It’s the recommended, secure way for Databricks to access ADLS without managing secrets, fully integrated with Unity Catalog and AAD.

Q: What is Unity Catalog?

Answer:
Unity Catalog is Databricks’ centralized governance and data catalog solution. It provides fine-grained access control, centralized
metadata management, auditing, and lineage across all Databricks workspaces. It standardizes how we organize and secure data,
regardless of where it is stored (ADLS, S3, etc.).

Q: Databricks With and Without Unity Catalog

• Without Unity Catalog:
○ Each workspace has its own Hive Metastore.
○ No centralized governance – access control is per workspace.
○ Harder to manage data sharing, lineage, and consistent policies.
• With Unity Catalog:
○ Single, central metastore shared across multiple workspaces.
○ Fine-grained access control at table/column/row level.
○ Data lineage, auditing, and compliance features.
○ Supports three-level namespace ([Link]).
In short: Without UC = siloed governance, With UC = centralized, secure, and consistent governance.

Q: Unity Catalog Object Model

1. Metastore
○ Top-level container in Unity Catalog.
○ Stores metadata and defines governance across multiple workspaces.
○ Each region can have one metastore, linked to multiple workspaces.
2. Catalog
○ A top-level container within a metastore.
○ Used to logically organize schemas and tables (like a database cluster).
○ Example: finance, sales, healthcare.
3. Schema (Database)
○ Inside a catalog, schemas group objects (tables, views, functions).
○ Example: [Link].
4. Table/View
○ Actual data objects stored inside schemas.
○ Example: [Link].

pyspark Page 1
 ○

Q: What is the Three-Level Namespace?

Answer:
Unity Catalog introduces a three-level namespace for identifying objects:

[Link]
• Catalog → Top-level grouping (e.g., finance).
• Schema → Logical database inside a catalog (e.g., transactions).
• Table/View → Actual data object (e.g., payments).
Example:
[Link] → Catalog = finance, Schema = transactions, Table = payments.
This ensures globally unique object names across Databricks.

1. What is a Managed Table?

• A managed table in Databricks (also called an internal table) is a table where Databricks manages both the metadata and the data files.
• When you create a managed table, Databricks stores the data under the workspace’s default location (usually the dbfs:/user/hive/warehouse/ path, or
Unity Catalog-managed storage).
• If you DROP a managed table, both the metadata and the underlying data files are deleted.
• Best for temporary, intermediate, or pipeline-managed datasets where Databricks should control storage.
Example (Managed Table):

CREATE TABLE sales_managed (

id INT,
product STRING,
amount DECIMAL(10,2)
);
Data will be stored inside Databricks’ managed storage location.

2. What is an External Table?

• An external table (also called an unmanaged table) is a table where Databricks manages only the metadata, while the data files remain in an external
location (e.g., ADLS Gen2, Blob, or S3).
• You must specify the LOCATION of the data files when creating the table.
• If you DROP an external table, only the metadata is removed, but the data files remain intact.
• Best for production-grade datasets where you want full control over data storage and don’t want accidental deletion.
Example (External Table):

CREATE TABLE sales_external (

id INT,
product STRING,
amount DECIMAL(10,2)
)
USING DELTA
LOCATION 'abfss://datalake@[Link]/sales/';
Data will stay in ADLS even if the table is dropped.

3. Managed Tables vs External Tables

Feature Managed Table ✅ External Table ✅
Storage Location Databricks-controlled default location (DBFS/UC storage). User-specified external path (ADLS, Blob, S3).
Data Ownership Databricks owns and manages both metadata + data. User owns data, Databricks only manages metadata.
Drop Behavior Deletes both metadata and data files. Deletes only metadata; data files remain safe.
Best Use Case Temporary/Intermediate data pipelines. Production datasets, shared datasets, compliance needs.
Risk of Data Loss High (DROP removes data). Low (data survives DROP).
In short: Managed = easy but risky for production, External = safe and controlled for critical data.

4. Data Files of an External Table

• Data files of an external table are stored outside Databricks, in a location chosen by the user (e.g., ADLS, Blob, or S3).
• They can be directly accessed and modified outside Databricks (using Azure Storage Explorer, CLI, or other tools).
• The table definition (metadata) inside Databricks just points to these files.
• If the files are modified or deleted outside Databricks, the table might break or show corrupted data.

pyspark Page 2
 • If the files are modified or deleted outside Databricks, the table might break or show corrupted data.
This gives flexibility but requires governance and consistency management.

5. What are Views?

• A view in Databricks is a virtual table defined by a SQL query. It does not store data itself but retrieves results from underlying tables or views whenever
queried.
• Types of views:
○ Temporary View: Exists only within the current Spark session (not persistent).
○ Global Temporary View: Available across multiple sessions but tied to the lifetime of the cluster.
○ Persistent View: Saved in the metastore; available across sessions and clusters until explicitly dropped.
• Views are often used for data abstraction, simplifying queries, or applying security filters.
Example (Persistent View):

CREATE VIEW high_value_sales AS

SELECT id, product, amount
FROM sales_external
WHERE amount > 1000;
This creates a logical layer over the external table without duplicating data.

Databricks uses Delta Lake as its default storage layer, which enhances data lakes with ACID transactions, schema enforcement, time travel, and efficient
upserts/deletes. Most data in Databricks is stored and processed in Delta Tables, and governance is provided by Unity Catalog.

Databricks is a unified analytics platform built on Apache Spark, and Delta Lake is its storage layer that brings reliability, ACID transactions, schema
enforcement, and time travel to data lakes.
• Databricks is commonly used with Azure (Azure Databricks) for large-scale ETL pipelines.
• Data in Delta Lake is stored in Parquet format with a _delta_log folder that tracks all operations.
• Delta Tables enable upserts, deletes, merges, and versioning, making them suitable for production-grade pipelines.

2. Delta Tables and its Basics

Delta Table is a table stored in Delta Lake format with these characteristics:
• ACID Transactions: Ensures atomicity, consistency, isolation, and durability.
• Schema Enforcement & Evolution: Prevents bad data ingestion and allows schema changes over time.
• Time Travel: Query historical data versions using VERSION AS OF or TIMESTAMP AS OF.
• Support for DML: UPDATE, DELETE, MERGE operations.
• Storage Format: Parquet + Delta transaction logs.
Example:

CREATE TABLE sales_delta (

id INT,
product STRING,
amount DECIMAL(10,2)
) USING DELTA;

3. List Catalogs in Databricks

• Catalogs are top-level containers in Unity Catalog.
• Each catalog can contain multiple schemas (databases) and tables.
• Command:

SHOW CATALOGS;
• Example Output: main, finance, healthcare

4. List Schemas in Databricks

• Schemas (databases) are containers inside a catalog to organize tables.
• Command:

SHOW SCHEMAS IN finance;

• Helps maintain logical separation of datasets, e.g., transactions, customers.

pyspark Page 3
 • Helps maintain logical separation of datasets, e.g., transactions, customers.

5. List Tables in Databricks

• Lists all tables inside a schema.

SHOW TABLES IN [Link];

• Includes managed tables, external tables, and views.

6. tableExists functionality in PySpark

• Checks if a table exists in a catalog/schema.
• Useful in ETL pipelines to avoid re-creating tables and prevent failures.

[Link]("[Link]")
• Returns True/False.

7. IF NOT EXISTS clause in CREATE command

• Prevents errors if a table already exists.
• Ensures idempotent table creation in pipelines.

CREATE TABLE IF NOT EXISTS sales_delta (

id INT,
product STRING
) USING DELTA;
• Works for Delta Tables, external tables, and views.

8. Temporary Views in Databricks

• Exist only in the current session.
• Not stored in the metastore.
• Useful for intermediate transformations or queries.

[Link]("temp_sales")
• Example: Use a temp view to filter data before inserting into a permanent table.

9. Permanent Views in Databricks

• Persist across sessions and clusters.
• Stored in the metastore, accessible by any authorized user.

CREATE VIEW high_value_sales AS

SELECT * FROM sales_delta WHERE amount > 1000;
• Supports column-level security and consistent business logic.

10. CTAS (Create Table As Select) with Delta Table

• CTAS allows creating a new Delta Table and populating it with data from a query in a single step.
• Example:

CREATE TABLE gold_sales

USING DELTA
AS SELECT * FROM sales_delta WHERE amount > 500;
• Key Points:
○ Only copies result of the query, not the full table or history.
○ Supports transformations and filters.

11. DEEP CLONE of Delta Table

• Deep Clone creates a full copy of a Delta Table, including:
○ All data files
○ Metadata
○ Transaction history
• Useful for backups, test environments, or disaster recovery.

CREATE TABLE sales_clone DEEP CLONE sales_delta;

• Deep Clone is time-consuming and storage-intensive because it copies everything.

12. Difference between CTAS and Deep Clone

Feature CTAS Deep Clone
Data Copied Only query results Entire table + metadata + history
Transaction History Not preserved Fully preserved
Storage Minimal (depends on query result) Full table size
Use Case Create new aggregated/curated tables Backup, isolated test environments

13. SHALLOW CLONE of Delta Table

pyspark Page 4
13. SHALLOW CLONE of Delta Table
• Shallow Clone creates a new table with metadata only.
• Points to same underlying data files as the source table.
• Very fast and storage-efficient.

CREATE TABLE sales_shallow SHALLOW CLONE sales_delta;

• Ideal for sandbox environments or experimentation, but changes to the data affect both tables.

✅ Key Takeaways for Interviews

• Delta Tables = ACID + Time Travel + Schema Management
• CTAS = query-based table creation, Deep Clone = full copy, Shallow Clone = metadata-only clone
• Temporary vs Permanent Views: session-only vs persistent
• IF NOT EXISTS & tableExists = idempotent table creation
• Catalogs → Schemas → Tables = three-level namespace in Unity Catalog

1. What is Upsert or Merge?

• Upsert is a combination of update + insert: it updates existing records if they exist, or inserts new records if they don’t.
• In Delta Lake, this is achieved using the MERGE statement, which allows conditional updates and inserts in a single transaction.
• Benefits:
○ Handles slowly changing dimensions (SCD type 1 or type 2)
○ Prevents duplicate records
○ Maintains ACID compliance
Example Syntax:

MERGE INTO target_table AS t

USING source_table AS s
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (id, product, amount) VALUES ([Link], [Link], [Link]);
✅ This will update existing rows where id matches and insert new rows where no match exists.

2. Merge Data Based on Condition in Delta Table

• Merge can include complex conditions for selective updates, inserts, or deletions.
• Example: Update only if the new amount is higher:

MERGE INTO sales_delta AS t

USING sales_updates AS s
ON [Link] = [Link]
WHEN MATCHED AND [Link] > [Link] THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (id, product, amount) VALUES ([Link], [Link], [Link]);
• Key Points:
○ Condition can use any column or expression
○ Supports multiple WHEN MATCHED/NOT MATCHED clauses
○ Fully ACID-compliant, ensures no partial updates

3. Soft Deletes Using Merge Statements

• Soft delete: Instead of physically deleting data, we mark it as inactive using a flag column (e.g., is_active = false).
• This is preferred for audit, compliance, or historical analysis.
Example Soft Delete:

MERGE INTO sales_delta AS t

USING records_to_delete AS s
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET t.is_active = false;
• Key Points:
○ No data files are deleted
○ Historical data remains in Delta Lake history
○ Compatible with time travel and ACID transactions

pyspark Page 5
1. What are Volumes in Databricks?
• Volumes are storage abstractions in Databricks Unity Catalog that allow you to manage data files directly, similar to tables but for file-based storage.
• They provide fine-grained access control for files and folders, just like managed or external tables.
• Volumes are useful for ETL pipelines, machine learning, and shared file storage, enabling secure, auditable file access.
Key points:
• Volumes are managed by Unity Catalog for governance.
• Can be managed or external.
• You can mount them to Databricks clusters for processing.

2. Types of Volumes in Databricks

Volume Type Description
Managed Databricks manages both metadata and underlying files. Deleting the volume deletes all files.
Volume
External Metadata is managed by Databricks, but the files remain in an external storage location (e.g., ADLS, Blob). Dropping the volume deletes
Volume metadata only, not data.

3. Create External Location for External Volume

• Before creating an external volume, define an external location in Unity Catalog that points to storage in ADLS, Blob, or S3.
Example:

CREATE EXTERNAL LOCATION ext_sales_loc

URL 'abfss://datalake@[Link]/sales'
WITH CREDENTIAL (IDENTITY 'databricks-identity');
• External location defines where the volume’s data files will reside.

4. Create Managed Volume in Databricks

• Managed volumes are fully controlled by Databricks.
Example:

CREATE VOLUME managed_sales_volume;

• Files written to this volume are stored in Databricks-managed storage.
• Deleting the volume deletes both metadata and files.

5. How to Define Volume File System Paths

• File paths inside a volume are hierarchical:

volume_name:/folder/subfolder/file
• Example:

# Write a Parquet file to a managed volume

[Link]("parquet").save("managed_sales_volume:/2025/09/sales_data.parquet")
• Enables organized storage and easy access across pipelines.

6. Create External Volume in Databricks

• Once the external location is defined, create an external volume pointing to it.
Example:

CREATE VOLUME ext_sales_volume

LOCATION ext_sales_loc;
• Now, any data written to this volume goes to the external storage, while metadata is managed in Unity Catalog.

7. Drop a Volume in Databricks

• Managed Volume: Deletes metadata + all files.
• External Volume: Deletes only metadata, files in external storage remain.
Command:

DROP VOLUME managed_sales_volume;

DROP VOLUME ext_sales_volume;

✅ Key Points for Interviews

• Volumes = file-level storage abstraction in Databricks with governance.
• Managed vs External Volumes differ in who owns the data and drop behavior.

pyspark Page 6
 • Managed vs External Volumes differ in who owns the data and drop behavior.
• External Locations define the path in ADLS/S3/Blob for external volumes.
• Path inside volume: volume_name:/folder/file
• Useful for ETL, ML, and pipeline storage, with secure access and auditing.

Databricks Utilities (DBUTILS)

Definition:
• DBUTILS (Databricks Utilities) is a set of utility functions provided by Databricks to perform common tasks such as file operations, secrets
management, notebook workflow control, and interactive widgets.
• These utilities simplify tasks that are outside pure Spark transformations, making pipelines and notebooks more functional and interactive.

1. What all Utilities are Available in Databricks?

Databricks provides several utility modules:
Utility Module Purpose
[Link] File system operations in DBFS or external storage
[Link] Create interactive widgets (dropdown, text input) in notebooks
[Link] Securely store and retrieve secrets (passwords, tokens, keys)
[Link] Manage notebook workflows, call/run other notebooks
[Link] Interact with jobs (get run info, tags)
[Link] Install or manage libraries in notebooks or clusters

2. Databricks File System Utilities ([Link])

• Used to interact with DBFS or mounted external storage.
• Common operations:

# List files
[Link]("/mnt/sales")
# Make a directory
[Link]("/mnt/sales/2025")
# Copy files
[Link]("/mnt/sales/[Link]", "/mnt/sales_backup/[Link]")
# Remove files/folders
[Link]("/mnt/sales/old_data", recurse=True)
# Mount external storage (ADLS/S3)
[Link](
source = "abfss://datalake@[Link]/sales",
mount_point = "/mnt/sales",
extra_configs = {"[Link]": "<access-key>"}
)
• Benefit: Simplifies file operations in notebooks, instead of using Spark APIs directly.

3. Databricks Widgets Utilities ([Link])

• Allows interactive input in notebooks (text boxes, dropdowns, multiselect).
• Useful for parameterizing notebooks in pipelines or jobs.
Example: Create Widgets

# Text widget
[Link]("input_date", "2025-09-22", "Enter Date")

pyspark Page 7
[Link]("input_date", "2025-09-22", "Enter Date")
# Dropdown widget
[Link]("env", "dev", ["dev", "test", "prod"], "Select Environment")
Access Widget Values:

date_val = [Link]("input_date")
env_val = [Link]("env")

4. Use Databricks Widgets in SQL Commands

• Widgets can be used in SQL queries via {{widget_name}} syntax.
Example:

-- Create a dropdown widget first

%python
[Link]("region", "US", ["US", "EU", "APAC"], "Select Region")
-- Use widget in SQL
%sql
SELECT * FROM sales_delta WHERE region = '{{region}}';
• Benefit: Makes notebooks dynamic for different inputs.

5. Databricks Secrets Utilities ([Link])

• Allows secure storage and retrieval of sensitive information.
• Secrets are stored in Databricks Secret Scopes, integrated with Azure Key Vault if needed.
Example:

# List scopes
[Link]()
# Retrieve a secret
[Link](scope="my_scope", key="storage_key")
# Use secret in Spark config
[Link]("[Link]",
[Link](scope="my_scope", key="storage_key"))
• Benefit: Avoids hardcoding passwords/tokens in notebooks.

6. Databricks Notebook Utilities ([Link])

• Allows calling other notebooks and passing parameters.
• Useful for modular notebook design and ETL pipelines.
Example:

# Run another notebook

[Link]("/Users/aaliya/ETL/transform_sales", 3600, {"input_date": "2025-09-22"})
• Features:
○ Returns notebook execution result
○ Can set timeout
○ Pass multiple parameters

✅ Key Points for Interviews

• DBUTILS = toolbox for files, widgets, secrets, notebooks, jobs, libraries
• [Link] = file operations (DBFS, mount, copy, remove)
• [Link] = interactive inputs for parameterized notebooks
• [Link] = secure secret management
• [Link] = modular notebook execution
• [Link] / library = job info & cluster library management

1. What are Databricks Instance Pools?

• Instance Pools are a pre-allocated set of virtual machines (VMs) in Databricks that can be reused across clusters.
• Purpose: reduce cluster startup time and optimize cost.
• When a cluster is created, it borrows instances from the pool instead of provisioning new VMs from Azure, which is faster.
• Helps to manage auto-scaling efficiently and reduce cold start latency for clusters.
Key Benefits:
1. Faster cluster startup (especially for large clusters).
2. Cost optimization – reused instances reduce VM spin-up costs.
3. Predictable performance – pre-warmed instances ready for jobs.

pyspark Page 8
 3. Predictable performance – pre-warmed instances ready for jobs.

2. How Databricks Instance Pools Work

1. You define a pool with a minimum, maximum, and idle instances configuration.
2. When a cluster is started:
○ Databricks checks the pool for available instances.
○ If available, the cluster uses pre-warmed instances.
○ If not, new VMs are provisioned from Azure.
3. When a cluster is terminated, instances return to the pool and remain idle for reuse.

3. How to Create a Warm Instance Pool

A Warm Pool is simply an instance pool that keeps some instances idle (pre-warmed) so clusters can start immediately.
Steps:
1. Navigate in Databricks UI:
○ Go to Compute → Pools → Create Pool
2. Fill Pool Details:
○ Pool Name: warm_sales_pool
○ Min Idle Instances: Number of VMs to keep warm/ready (e.g., 2)
○ Max Capacity: Maximum number of VMs the pool can hold (e.g., 10)
○ VM Type: Select Azure VM type (DS3_v2, etc.)
○ Idle Instance Timeout: How long an instance stays idle before termination
3. Save Pool
○ Pool is now ready. When a new cluster uses this pool, it borrows warm instances and starts faster.
Optional (CLI / API):
• Pools can also be created using Databricks REST API, specifying min_idle_instances, max_capacity, and VM type.

4. Example Use Case

• ETL jobs start every morning at 6 AM.
• By having 2 warm instances in a pool, cluster startup time reduces from 5–10 minutes to <1 minute.
• Reduces idle cluster cost while improving job SLA.

✅ Key Points for Interviews

• Instance Pool = pre-allocated VMs for faster cluster startup.
• Warm Pool = keeps a minimum number of idle pre-warmed VMs ready for immediate use.
• Improves performance, reduces cold start latency, and saves cost.
• Configurable via UI or API with min/max capacity and idle timeout.

Databricks Jobs & Workflow

1. Databricks Jobs UI
• The Jobs UI in Databricks allows you to create, schedule, monitor, and manage jobs and workflows.
• Features include:
○ Job creation: multiple tasks in a single workflow
○ Scheduling: cron-based or periodic execution
○ Dependency management: tasks can run sequentially or in parallel
○ Monitoring & Alerts: track job status, logs, retries, and failures

2. How to Create a Job in Workflow in Databricks

Steps:
1. Navigate to Jobs → Create Job → Workflow
2. Add Tasks:
○ Each task can be a notebook, JAR, Python script, or SQL query
○ Specify cluster (existing or new)
○ Set parameters (if any)
3. Set Task Dependencies (optional):
Define which tasks run after another task

pyspark Page 9
 ○ Define which tasks run after another task
4. Schedule the Job:
○ Cron expression or interval-based
5. Save and Run
Example: ETL pipeline
• Task 1: Extract data (notebook)
• Task 2: Transform data (depends on Task 1)
• Task 3: Load to Delta table (depends on Task 2)

3. How to Pass Values from One Task to Another

• In Databricks Workflows, you can pass output values from one task to another using task().output.
• Example:

# Task 1: Compute result

[Link]("2025-09-22")

# Task 2: Get value from Task 1

date_val = [Link](taskKey="Task1", key="output_date", debugValue="2025-09-22")
• The output can then be used in SQL queries or subsequent notebooks.

4. How to Use If-Else in Databricks Workflow Jobs

• Conditional execution is possible using task dependencies and run_if conditions.
• Example:
○ Task 2 runs only if Task 1 succeeded
○ Task 3 runs if Task 1 failed

"run_if": {
"condition": "TASK_SUCCESS",
"task_key": "Task1"
}
• You can implement complex branching logic with multiple conditional tasks.

5. Re-run Failed Jobs in Databricks Workflows

• Databricks allows rerunning failed tasks without rerunning the entire workflow.
• Options:
○ UI → Job → Rerun Failed
○ API → runs/submit with same parameters
• Reduces compute cost and saves pipeline recovery time.

6. Override Parameters for Job Run

• When starting a job manually or via API, you can override default parameters.
• Example:

# Notebook parameter
[Link]("input_date", "2025-09-22", "Enter Date")
• While running job: set input_date = 2025-09-21 to override default.
• Useful for dynamic job execution with different dates, regions, or environment variables.

7. How to Use For-Each Loop in Databricks Workflow Jobs

• Databricks supports dynamic task creation using for-each loops in workflows.
• Example: Run a notebook for each region:

regions = ["US", "EU", "APAC"]

for region in regions:
[Link]("/ETL/transform_sales", 3600, {"region": region})
• In Jobs UI, you can configure multi-task iteration using task parameters or dynamic task generation.

✅ Key Points for Interviews

Feature Explanation
Jobs UI Create, schedule, monitor, and manage workflows
Task dependencies Run sequential/parallel tasks
Pass values Use task().output or [Link]
If-Else Conditional execution based on task success/failure
Re-run failed tasks Recover without rerunning entire workflow
Parameter override Dynamically pass different input values per run
For Each loop Dynamically execute notebooks/tasks for multiple items

pyspark Page 10
 Databricks ansh notes

what is databricks..?
Databricks is a cloud-based unified data analytics platform built on top of Apache Spark that helps process, analyze, and manage large-scale data. It
combines data engineering, data science, and analytics in one workspace, with key features like Delta Lake for ACID transactions, Unity Catalog for
governance, and Photon Engine for performance, making it ideal for building scalable ETL pipelines and lakehouse architectures.

A Service Principal in Azure is a security identity used by applications, services, or automation tools to access Azure resources securely without using a
user’s credentials. It works like a “username and password” (or certificate/secret) for applications.
• It’s registered in Azure Active Directory (AAD) when you create an App Registration.
• It has an application ID (client ID), client secret/certificate, and a tenant ID.
• Permissions are assigned via Role-Based Access Control (RBAC), so it follows least-privilege principles.
• In Databricks, I use a service principal to authenticate with ADLS/Key Vault/other Azure services, allowing pipelines and jobs to run securely in
production.
In short: A service principal is a non-human identity that enables secure, automated access to Azure resources for applications and services.

Databricks Utilities (DBUtils) is a built-in library in Databricks that provides helper commands for working with data, files, secrets, and notebooks. It’s
mainly used in notebooks and jobs to simplify tasks like file management, parameter passing, and secure credential handling.
Key components:
• [Link] → Manage files in distributed storage (ADLS, S3). Example: [Link]("/mnt/raw") to list files.
• [Link] → Access credentials securely from Azure Key Vault/Databricks Secret Scope. Example: [Link]("scope", "key").
• [Link] → Create input widgets for parameterizing notebooks. Example: [Link]("p_date", "2025-01-01").
• [Link] → Run or chain notebooks and pass parameters/results between them. Example: [Link]("child_notebook", 60,
{"param": "value"}).
• [Link] → Used inside workflows to handle task values, like passing data between tasks.
• [Link] → Manage libraries on clusters (install/uninstall).
In real projects, I’ve used DBUtils to mount ADLS storage, fetch secrets from Key Vault, pass dynamic parameters to pipelines, and chain
multiple notebooks in ADF/Databricks workflows, making pipelines more modular and secure.

Delta Lake is an open-source storage layer that brings ACID transaction capabilities and reliability to data lakes, turning them into a data lakehouse. It is
built on top of cloud object storage like Azure Data Lake Storage (ADLS), Amazon S3, or GCS, and is fully compatible with Apache Spark, including
Databricks. Delta Lake ensures data reliability, consistency, and performance in large-scale ETL and analytics pipelines.
Key Technical Features:
1. ACID Transactions: Delta Lake enables atomic operations for write, update, and delete, so multiple concurrent operations don’t corrupt data. This is
critical for batch and streaming pipelines running in parallel.
2. Schema Enforcement & Evolution: Delta Lake enforces schema-on-write, rejecting invalid records to maintain data quality. Schema evolution allows
adding new columns without breaking downstream pipelines.
3. Time Travel / Data Versioning: Delta Lake maintains a transaction log (_delta_log) that tracks every change. This allows querying previous versions
of data, auditing, and recovering from accidental deletes.
4. Upserts & Deletes: Unlike traditional data lakes, Delta Lake supports MERGE INTO, UPDATE, and DELETE operations efficiently, making slowly
changing dimension (SCD) handling easier.
5. Performance Optimizations:
○ Partitioning: Store data by frequently filtered columns (e.g., date, region) to reduce scan costs.
○ Z-Ordering: Clustering data on columns frequently used in filters to improve predicate pushdown and query performance.
○ File Compaction (OPTIMIZE): Merges small files into larger Parquet files, reducing shuffle and improving read performance.
○ Caching & Delta Caching: Frequently accessed data can be cached locally to reduce repeated cloud storage I/O.
Medallion Architecture in Delta Lake:
• Bronze Layer: Raw ingested data stored as-is for auditing and recovery.

pyspark Page 11
 • Bronze Layer: Raw ingested data stored as-is for auditing and recovery.
• Silver Layer: Cleansed, deduplicated, and standardized data with consistent schema.
• Gold Layer: Aggregated, business-ready data (fact and dimension tables) for analytics, dashboards, and ML models.
Real-World Usage Example: In healthcare RCM pipelines, raw patient and claims data lands in Bronze Delta tables, Silver tables handle cleansing,
deduplication, and joins, and Gold tables provide KPIs like Accounts Receivable and Days in AR. Using Delta Lake features like time travel, schema
enforcement, and Z-Ordering, I ensure reliable, performant, and auditable data pipelines in Databricks.

In Delta Lake, write modes control how data is written to a table:

• Append: Adds new data without affecting existing records; used for incremental loads.
• Overwrite: Replaces existing data; can handle schema changes with overwriteSchema=true.
• ErrorIfExists: Throws an error if the table exists, preventing accidental overwrites.
• Ignore: Skips writing if the table exists, useful for idempotent writes.
All modes are ACID-compliant, ensuring consistent and reliable writes, even with concurrent readers or partitioned tables.

1. Managed Delta Table (Internal Table):

• Databricks owns both the metadata and the data.
• Stored in the default location of the Databricks metastore unless a custom path is provided.
• When you DROP a managed table, both the metadata and the underlying data are deleted.
• Ideal when Databricks is responsible for full lifecycle management of the data.

CREATE TABLE managed_delta_table

USING DELTA
AS SELECT * FROM source_table;
2. External Delta Table:
• Databricks owns only the metadata; the data remains at a user-specified location in ADLS/S3.
• Dropping the table deletes only the metadata, not the underlying files.
• Useful when multiple tools or clusters need access to the same data, or for shared/centralized data lakes.

CREATE TABLE external_delta_table

USING DELTA
LOCATION '/mnt/datalake/events';
Key Differences:
Feature Managed Table External Table
Data Ownership Databricks User-specified location
DROP Behavior Deletes data & metadata Deletes only metadata
Use Case Full control & lifecycle Shared data or multi-tool access
Real-World Use: I use managed tables for curated Gold layer datasets where Databricks handles lifecycle, and external tables for raw Bronze or
shared Silver data in ADLS, ensuring multiple pipelines and tools can access it safely without accidental deletion.

Delta Log (Transaction Log) in Delta Lake is the core mechanism that makes Delta tables reliable, ACID-compliant, and versioned. Every
Delta table has a _delta_log folder stored alongside the table’s data (in ADLS, S3, or GCS). This log records all changes to the table, ensuring
consistency, recoverability, and time travel.
Key Technical Details:
1. ACID Transactions:
○ Delta Log ensures that all write operations (insert, update, delete, merge) are atomic and consistent.
○ Multiple concurrent jobs can read/write without corrupting data, as Delta maintains snapshot isolation.
2. JSON and Checkpoint Files:
○ Each operation generates a JSON transaction file describing the change.
○ Periodically, checkpoint Parquet files summarize the state of the table, improving read performance and avoiding replaying all
JSON logs.
3. Time Travel / Versioning:

pyspark Page 12
 3. Time Travel / Versioning:
○ Each transaction has a version number.
○ You can query previous versions using:

SELECT * FROM delta_table VERSION AS OF 5;

SELECT * FROM delta_table TIMESTAMP AS OF '2025-09-25 10:00:00';
○ This enables auditing, rollback, and reproducing historical analyses.
4. Data Reliability & Recovery:
○ Failed or partial writes don’t corrupt the table.
○ VACUUM can clean up old files while Delta Log maintains version history for recovery.
5. Performance Optimization:
○ The query engine uses the transaction log to prune unnecessary files and skip irrelevant partitions, improving read efficiency.
Real-World Usage: In ETL pipelines, I rely on Delta Log to perform incremental updates, merges, and deletes safely, while also using time
travel to audit changes or recover data. This makes Delta Lake robust, reliable, and production-ready.

Data Versioning in Delta Lake is a core feature that enables tracking changes over time, making your data reliable, auditable, and recoverable. Every
Delta table maintains a _delta_log folder that records all transactions (inserts, updates, deletes, merges) with version numbers, creating a
chronological history of the table.
Key Technical Details:
1. Version Numbers:
○ Each commit in Delta Lake gets a unique incremental version.
○ You can query or restore the table at any previous version.

SELECT * FROM delta_table VERSION AS OF 5;

SELECT * FROM delta_table TIMESTAMP AS OF '2025-09-25 10:00:00';
2. Time Travel / Historical Access:
○ Allows auditing, rollback, and reproducing historical analyses.
○ Useful for debugging, recovering deleted data, or reproducing reports from a specific date.
3. Incremental Updates & Streaming:
○ Combined with Change Data Feed (CDF), you can process only the changed rows between versions, making incremental ETL pipelines
efficient.
4. Integration with ACID Transactions:
○ Versioning works seamlessly with Delta Lake’s ACID guarantees, so even with multiple concurrent writes, each version represents a consistent
snapshot.
Real-World Usage: In production pipelines, I use data versioning to:
• Recover mistakenly deleted or updated data.
• Reproduce dashboards and analytics from a previous date.
• Build incremental ETL pipelines using CDF, reducing full table scans.
Impact: Data versioning ensures reliability, auditability, and operational safety, which is why Delta Lake is production-ready for large-scale data
pipelines.

Delta Table Optimization refers to a set of techniques in Delta Lake to improve query performance, reduce storage overhead, and manage large-scale
datasets efficiently. These optimizations leverage Delta Lake features and Databricks capabilities to make ETL, analytics, and BI workloads faster and more
reliable.
Key Techniques:
1. File Compaction (OPTIMIZE):
○ Delta tables often accumulate many small Parquet files, especially in streaming pipelines.
○ OPTIMIZE merges these files into larger, more efficient files, reducing shuffle, IO, and query latency.

OPTIMIZE delta_table;
2. Z-Ordering / Data Clustering:
○ Reorders data based on frequently filtered columns (e.g., user_id, event_date) to enable data skipping and faster reads.

OPTIMIZE delta_table ZORDER BY (user_id, event_date);

3. Partitioning:
○ Splitting tables by columns like date, region, or category improves predicate pushdown and reduces the amount of scanned data.
○ Dynamic partition overwrite can be used when updating specific partitions.
4. Caching / Delta Caching:

pyspark Page 13
 4. Caching / Delta Caching:
○ Frequently accessed Delta tables can be cached locally on SSDs to avoid repeated cloud storage reads.
○ Useful for dashboards, iterative ML pipelines, or repeated queries.
5. Auto Optimize & Auto Compaction:
○ Enables automatic file sizing and compaction for batch and streaming pipelines.
○ Prevents the small files problem without manual intervention.
6. Adaptive Query Execution (AQE):
○ Works with Delta tables to dynamically coalesce shuffle partitions, handle skewed joins, and optimize join strategies at runtime.
Real-World Usage: In production, I apply these techniques on Delta tables in the Medallion Architecture:
• Bronze tables → compact small streaming files.
• Silver tables → Z-Order and partition for frequent query filters.
• Gold tables → Delta caching and optimized files for BI dashboards and ML models.
Impact: These optimizations reduce query latency, improve cluster efficiency, and ensure scalable and reliable pipelines.

Auto Loader is a Databricks feature designed for efficient, scalable, and incremental ingestion of data from cloud storage (like ADLS, S3) into Delta
Lake tables. It is optimized for streaming and batch pipelines, automatically detecting new files without needing to scan the entire directory every
time.
Key Features & Technical Details:
1. Incremental File Processing:
○ Auto Loader tracks only new files added to a directory using cloud file notifications (or directory listing if notifications aren’t available).
○ Avoids repeatedly scanning all files, making it highly efficient for large datasets.
2. Schema Inference & Evolution:
○ Automatically infers schema of incoming files.
○ Supports schema evolution, so new columns can be handled without breaking pipelines:

[Link]("cloudFiles") \
.option("[Link]", "parquet") \
.option("mergeSchema", "true") \
.load("/mnt/raw-data/")
3. Supports Multiple Formats:
○ Can ingest JSON, CSV, Avro, Parquet, ORC, Delta, and others.
○ Works seamlessly with Delta Lake, enabling incremental updates.
4. Streaming & Batch Support:
○ Can be used in streaming mode (readStream) for real-time ingestion.
○ Can also be used in micro-batch or batch mode for scheduled ETL pipelines.
5. Integration with Delta Lake & Medallion Architecture:
○ Often used to ingest raw files into Bronze Delta tables.
○ Incremental data can then be cleansed and transformed into Silver/Gold layers.
6. Performance & Reliability:
○ Uses checkpointing to maintain state and guarantee exactly-once processing.
○ Scales automatically with cluster resources.
Real-World Usage: In healthcare RCM pipelines, I use Auto Loader to ingest daily patient, claim, and payment files into Bronze Delta tables, then
apply transformations to create Silver/Gold tables. This allows incremental, reliable, and schema-evolving ETL pipelines without manual file
tracking.
Impact: Auto Loader significantly reduces ETL complexity, handles high-volume incremental data efficiently, and ensures data consistency for
downstream analytics and ML workloads.

Streaming Query in Databricks (Structured Streaming):

A streaming query is a long-running Spark job that continuously processes incoming data (like files, Kafka, Event Hub) and writes results to a sink (Delt a,
console, memory, etc.). You define it using readStream + transformations + writeStream.
Example:

query = [Link] \
.format("delta") \
.option("checkpointLocation", "/mnt/checkpoints/") \
.start("/mnt/delta/bronze")

pyspark Page 14
 .start("/mnt/delta/bronze")
• Checkpointing ensures fault tolerance and exactly-once processing.
• Runs until explicitly stopped ([Link]()).

Query Progress Log:

• Spark maintains metadata & progress logs for every streaming query.
• Accessible via:

[Link] # Shows last micro-batch details

[Link] # Shows multiple recent batches
[Link] # Shows current query status
• Logs include:
○ batchId, inputRowsPerSecond, processedRowsPerSecond
○ durationMs (batch time)
○ stateOperators (e.g., aggregations, watermarks)
○ sources & sinks (data read/write details)
These logs are critical for monitoring performance, identifying bottlenecks, and troubleshooting.

✅ In Practice: I use streaming queries with checkpoints to ingest real-time files into Delta Bronze tables. I monitor query progress logs to check
throughput (rows/sec), batch durations, and detect skew or delays. This helps tune resources and optimize the pipeline.

Databricks Workflows is the native orchestration service in Databricks that allows you to schedule, run, and monitor data pipelines, ETL jobs, ML
workflows, and SQL tasks directly inside the platform—without needing external tools like Airflow or ADF.
Key Features & Technical Details:
1. Job Orchestration:
○ You can build multi-task workflows with dependencies between notebooks, SQL queries, JAR/Python scripts, and Delta Live Tables.
○ Supports task chaining, conditional execution (if-else), loops (for-each), and parameter passing between tasks.
2. Scheduling & Triggers:
○ Workflows can be triggered by schedule, manual run, or event-driven triggers (e.g., arrival of new files).
○ Supports retry logic and re-run failed tasks without re-running the whole pipeline.
3. Cluster Management:
○ Jobs can run on all-purpose clusters or job clusters (ephemeral clusters that spin up just for the workflow, reducing cost).
○ Photon and autoscaling can be enabled for performance + efficiency.
4. Monitoring & Alerts:
○ Provides a UI to track job runs, DAG visualizations, and task-level metrics.
○ Supports email/Slack alerts and integration with monitoring tools.
5. Parameterization:
○ You can pass parameters between tasks, override them at runtime, and use them in notebooks or scripts, making workflows highly reusable.
Real-World Usage:
In production, I’ve used Databricks Workflows to orchestrate a Medallion Architecture pipeline:
• Auto Loader ingests raw data into Bronze.
• A second task cleanses and transforms into Silver.
• A third task aggregates into Gold for BI dashboards.
• Downstream tasks refresh ML feature tables and trigger dashboards.
Impact: Databricks Workflows provides a unified, cost-efficient, and reliable orchestration layer directly integrated with Databricks, reducing
dependency on external schedulers and simplifying end-to-end pipeline management.

Control Plane:
The control plane is managed by Databricks and hosts all the management services—like the web UI, REST APIs, job scheduling, cluster metadata,
notebooks, and the Delta transaction log (_delta_log). It does not process your data; it only manages and coordinates.
Compute Plane:
The compute plane is where your data is actually processed. It consists of the clusters (VMs) running in your cloud account (Azure, AWS, GCP). All data
processing, transformations, ML, and queries happen here, and data never leaves your cloud.
Key Difference:
• Control plane → management & orchestration (Databricks-managed).

pyspark Page 15
 • Control plane → management & orchestration (Databricks-managed).
• Compute plane → data execution & storage access (your cloud).
Impact: This separation ensures security (data stays in your cloud), scalability, and efficient management of workloads.

Unity Catalog is Databricks’ centralized governance and data catalog solution that provides fine-grained access control, auditing, and data
discovery across all Databricks workspaces and data assets. It unifies data, ML models, and AI assets under a single governance layer.
Key Technical Points:
1. Centralized Governance:
○ Provides a single control point for permissions across multiple workspaces and clouds.
○ Uses ANSI SQL-based GRANT/REVOKE for managing access at catalog, schema, table, view, and column levels.
2. Three-Level Namespace:
○ Organizes objects in a 3-level hierarchy:
▪ Catalog → Schema → Table
▪ Example: [Link]
○ Helps avoid name conflicts and improves organization.
3. Data Lineage & Auditing:
○ Tracks end-to-end data lineage at the column level.
○ Provides audit logs for all operations (who accessed what and when).
4. Fine-Grained Access Control:
○ Supports row-level filters and column-level masking for sensitive data.
○ Integrates with identity providers like Azure AD for user/service principal authentication.
5. Multi-Cloud & External Data Sources:
○ Can govern data across Azure, AWS, and GCP in a single catalog.
○ Supports external locations (ADLS, S3, GCS) with external volumes and tables.
6. Integration with ML & AI:
○ Extends governance to ML models and AI assets, not just tables.
Real-World Usage:
In my projects, I’ve used Unity Catalog to enforce RBAC (Role-Based Access Control) so that analysts only see aggregated Gold tables, while
engineers can access Bronze/Silver. With column masking, sensitive PII (like patient SSNs) is hidden, ensuring compliance wit h HIPAA/GDPR.
Impact: Unity Catalog ensures secure, compliant, and centralized data governance while simplifying data discovery and collaboration across
teams and environments.

In Azure Databricks, the mount operation is used to connect external storage (like Azure Data Lake Storage or Blob Storage) to Databricks File System
(DBFS), so you can easily access files using normal paths like /mnt/... instead of using long storage URLs or access keys.

✅ Purpose
Mounting allows you to:
• Access Azure storage like a local file system.
• Simplify path references in notebooks and jobs.

pyspark Page 16
• Simplify path references in notebooks and jobs.
• Avoid repeatedly specifying credentials.
✅ Basic Syntax

[Link](
source = "wasbs://<container-name>@<storage-account-name>.[Link]/",
mount_point = "/mnt/<mount-name>",
extra_configs = {"[Link].<storage-account-name>.[Link]": "<access-key>"}
)

✅ After Mounting
You can read or write data easily:

# List files
display([Link]("/mnt/raw"))
# Read data in PySpark
df = [Link]("/mnt/raw/sales_data.csv", header=True, inferSchema=True)
[Link]()

In Azure Databricks, Secret Scopes are secure storage locations used to store sensitive information like access keys, client secrets, passwords, or
tokens.
They help you avoid hardcoding credentials in notebooks or code.

✅ Why Use Secret Scopes

• To securely store and manage secrets (like Azure Storage Keys or Service Principal secrets).
• To avoid exposing credentials in plain text inside Databricks notebooks.
• To manage permissions — only authorized users or jobs can access secrets.

✅ 1. Create a Secret Scope

There are two ways to create secret scopes:
Option 1: Using Databricks CLI

databricks secrets create-scope --scope myscope

Option 2: Using Azure Key Vault
You can link Databricks directly with Azure Key Vault (recommended for production):

databricks secrets create-scope --scope myscope --scope-backend-type AZURE_KEYVAULT --resource-id <azure-keyvault-resource-id> --dns-name
<keyvault-dns-name>
This allows Databricks to read secrets directly from Azure Key Vault.

✅ 2. Add Secrets
Once the scope is created, you can store secrets inside it:

databricks secrets put --scope myscope --key storage-key

It will prompt you to paste the value securely (e.g., your storage account key or client secret).

✅ 3. Access Secrets in a Notebook

You can retrieve stored secrets securely inside your notebook:

storage_key = [Link](scope="myscope", key="storage-key")

print(storage_key) # (value is masked in output)

✅ 4. Use Secrets in Configurations

Use them to authenticate securely — for example, mounting ADLS Gen2:

storage_account = "adlsaccount"
container_name = "raw"
mount_point = "/mnt/raw"
configs = {
"[Link]": "OAuth",
"[Link]": "[Link]",
"[Link]": [Link]("myscope", "client-id"),
"[Link]": [Link]("myscope", "client-secret"),

pyspark Page 17
 "[Link]": [Link]("myscope", "client-secret"),
"[Link]": f"[Link] 'tenant-id')}/oauth2/token"
}
[Link](
source = f"abfss://{container_name}@{storage_account}.[Link]/",
mount_point = mount_point,
extra_configs = configs
)

✅ 5. List Scopes and Secrets

# List all secret scopes

[Link]()
# List all secrets inside a specific scope
[Link]("myscope")

✅ 6. Important Security Points

• Secrets are masked — even if printed, they won’t show actual values.
• Only users with permission can access or manage specific scopes.
• Use Azure Key Vault–backed scopes in production for centralized security and key rotation.

Access ADLS Gen2 (or Blob Storage) from Databricks using an Azure Service Principal (SP) for authentication via OAuth 2.0.

✅ 1. Create a Service Principal (SP)

You can create an SP in the Azure Portal or with Azure CLI.
Using Azure CLI

az ad sp create-for-rbac --name databricks-sp --role "Storage Blob Data Contributor" \

--scopes /subscriptions/<subscription-id>/resourceGroups/<resource-group-name>/providers/[Link]/storageAccounts/<storage-
account-name>

pyspark Page 18
account-name>
This command outputs:

{
"appId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", # client_id
"password": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", # client_secret
"tenant": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" # tenant_id
}

✅ 2. Assign Permissions
In Azure Portal:
1. Go to your Storage Account → Access Control (IAM).
2. Click Add Role Assignment.
3. Assign the role Storage Blob Data Contributor (or Storage Blob Data Reader) to your Service Principal.

✅ 3. Store Credentials Securely

Save your SP credentials (Client ID, Tenant ID, Client Secret) in Databricks Secret Scope.

databricks secrets put --scope myscope --key client-id

databricks secrets put --scope myscope --key tenant-id
databricks secrets put --scope myscope --key client-secret

✅ 4. Configure Spark to Use OAuth Authentication

In your Databricks notebook:

storage_account_name = "adlsaccount"
container_name = "raw"
configs = {
"[Link]": "OAuth",
"[Link]": "[Link]",
"[Link]": [Link]("myscope", "client-id"),
"[Link]": [Link]("myscope", "client-secret"),
"[Link]": f"[Link] 'tenant-id')}/oauth2/token"
}
# Test read (no mount)
df = [Link](
f"abfss://{container_name}@{storage_account_name}.[Link]/sales_data.csv",
header=True, inferSchema=True
)
display(df)

✅ 5. (Optional) Mount the Storage

You can mount the ADLS Gen2 container for easier file access.

[Link](
source = f"abfss://{container_name}@{storage_account_name}.[Link]/",
mount_point = "/mnt/raw",
extra_configs = configs
)
# Verify mount
display([Link]("/mnt/raw"))

✅ 6. Read and Write Data

Now you can read/write using either the mounted path or ABFSS path:

# Read
df = [Link]("/mnt/raw/2025/10/[Link]")
# Write
[Link]("overwrite").parquet("/mnt/raw/processed/sales_cleaned/")

Im ortant Securit otes

• Service Principal is application-based authentication — no user credentials needed.
• Rotate client secrets periodically (Key Vault integration automates this).
• Use Azure Key Vault–backed Secret Scopes for full security compliance.
• Avoid mounting in shared environments if unnecessary — use direct abfss:// paths.

✅ Summary
Step Action
1 Create Service Principal (Client ID, Secret, Tenant ID)
2 Assign "Storage Blob Data Contributor" role

pyspark Page 19
2 Assign "Storage Blob Data Contributor" role
3 Store credentials securely in Databricks Secret Scope
4 Configure Spark with OAuth (ClientCredsTokenProvider)
5 Read/Write data using abfss:// or mount /mnt/... path

pyspark Page 20