Tag: Technical

what is

Databricks Delta, now known as Delta Lake, is an open-source storage layer that brings ACID transactions to Apache Spark and big data workloads. It enhances Azure Databricks by providing features like:

• ACID transactions for data reliability and consistency.
• Scalable metadata handling for large tables.
• Time travel for data versioning and historical data analysis.
• Schema enforcement and evolution.
• Improved performance with data skipping and Z-ordering

there are several alternative technologies that provide Delta Lake–style Lakehouse capabilities (ACID + schema enforcement + time travel + scalable storage + SQL engine). such as,

• Apache Iceberg
• Apache Hudi
• Snowflake (Iceberg Tables / Unistore)
• BigQuery + BigLake
• AWS Redshift + Lake Formation + Apache Iceberg
• Microsoft Fabric (OneLake + Delta/DQ/DLTS)

Delta lake tables are tables that store data in the delta format. Delta Lake is an extension to existing data lakes,

1. Bronze Layer (Raw Data): Ingest raw data from various sources into the Bronze layer. This data is stored as-is, without any transformation.
2. Silver Layer (Cleaned Data, as known Enriched layer): Clean and enrich the data from the Bronze layer. Apply transformations, data cleansing, and filtering to create more refined datasets.
3. Gold Layer (Aggregated Data, as known Curated layer): Aggregate and further transform the data from the Silver layer to create high-level business tables or machine learning features. This layer is used for analytics and reporting.

Z-Ordering in Databricks (specifically for Delta Lake tables) is an optimization technique designed to co-locate related information into the same set of data files on disk.

OPTIMIZE mytable
ZORDER BY (col1, col2);

Liquid Clustering is Databricks’ next-generation data layout optimization for Delta Lake.
It replaces (and is far superior to) Z-Order.

ALTER TABLE sales
SET TBLPROPERTIES (
'delta.liquidClustered' = 'true',
'delta.liquidClustered.columns' = 'customer_id, event_date'
);

Dataframe refers to a specified form of tables employed to store the data within Databricks during runtime. In this data structure, the data will be arranged into two-dimensional rows and columns to achieve better accessibility.

RDD, Resilient Distributed Dataset, is a fault-tolerant, immutable collection of elements partitioned across the nodes of a cluster. RDDs are the basic building blocks that power all of Spark’s computations.

Dataset is an extension of the DataFrame API that provides compile-time type safety and object-oriented programming benefits.

A cache is a temporary storage that holds frequently accessed data, aiming to reduce latency and enhance speed. Caching involves the process of storing data in cache memory.

1. Use Azure Key Vault to store and manage secrets securely.
2. Integrate Azure Key Vault with Azure Databricks using Databricks-backed or Azure-backed scopes.
3. Access secrets in notebooks and jobs using the dbutils.secrets API.
   dbutils.secrets.get(scope=”<scope-name>”, key=”<key-name>”)
4. Ensure that secret access policies are strictly controlled and audited.

• Data Classification: Classify data based on sensitivity and compliance requirements.
• Access Controls: Implement role-based access control (RBAC) using Azure Active Directory.
• Data Lineage: Use tools like Databricks Lineage to track data transformations and movement.
• Audit Logs: Enable and monitor audit logs to track access and changes to data.
• Compliance Policies: Implement Azure Policies and Azure Purview for data governance and compliance monitoring.

1. Repartitioning: Repartition the data to balance the workload across nodes and reduce skew.
2. Broadcast Joins: Use broadcast joins for small datasets to avoid shuffle operations.
3. Caching: Cache intermediate results to reduce the need for recomputation.
4. Shuffle Partitions: Increase the number of shuffle partitions to distribute the workload more evenly.
5. Skew Handling: Identify and handle skewed data by adding salt keys or custom partitioning strategies.

• Assessment: Evaluate the existing Hadoop workloads and identify components to be migrated.
• Data Transfer: Use Azure Data Factory or Azure Databricks to transfer data from on-premises HDFS to ADLS.
• Code Migration: Convert Hadoop jobs (e.g., MapReduce, Hive) to Spark jobs and test them in Databricks.
• Optimization: Optimize the Spark jobs for performance and cost-efficiency.
• Validation: Validate the migrated workloads to ensure they produce the same results as on-premises.
• Deployment: Deploy the migrated workloads to production and monitor their performance.

what is

Database locking is the mechanism a database uses to control concurrent access to data so that transactions stay consistent, isolated, and safe.

Locking prevents:

• Dirty reads
• Lost updates
• Write conflicts
• Race conditions

Types of Locks:

1. Shared Lock (S)

• Used when reading data
• Multiple readers allowed
• No writers allowed

2. Exclusive Lock (X)

• Used when updating or inserting
• No one else can read or write the locked item

3. Update Lock (U) (SQL Server specific)

• Prevents deadlocks when upgrading from Shared → Exclusive
• Only one Update lock allowed

4. Intention Locks (IS, IX, SIX)

Used at table or page level to signal a lower-level lock is coming.

5. Row / Page / Table Locks

Based on granularity:

• Row-level: Most common, best concurrency
• Page-level: Several rows together
• Table-level: When scanning or modifying large portions

DB engines automatically escalate:

Row → Page → Table
when there are too many small locks.

A deadlock happens when:

• Transaction A holds Lock 1 and wants Lock 2
• Transaction B holds Lock 2 and wants Lock 1

Both wait on each other → neither can move → database detects → kills one transaction (“deadlock victim”).

Deadlocks usually involve one writer + one writer, but can also involve readers depending on isolation level.

A: In SQL Server: Enable Deadlock Graph Capture

run:
ALTER DATABASE [YourDB] SET DEADLOCK_PRIORITY NORMAL;

use:
DBCC TRACEON (1222, -1);
DBCC TRACEON (1204, -1);

B: Interpret the Deadlock Graph

You will see:

• Processes (T1, T2…)
• Resources (keys, pages, objects)
• Types of locks (X, S, U, IX, etc.)
• Which statement caused the deadlock

Look for:

• Two queries touching the same index/rows in different order
• A scanning query locking too many rows
• Missed indexes
• Query patterns that cause U → X lock upgrades

C. Identify

• The exact tables/images involved
• The order of locking
• The hotspot row or range
• Rows with heavy update/contention

This will tell you what to fix.

• Always update rows in the same order
• Keep transactions short
• Use appropriate indexes
• Use the correct isolation level
• Avoid long reads before writes

Normalization is the process of structuring a relational database to minimize data redundancy (duplicate data) and improve data integrity.

Denormalization is the process of intentionally introducing redundancy into a previously normalized database to improve read performance and simplify complex queries.

• Adding Redundant Columns: Copying a value from one table to another (e.g., copying the CustomerName into the Orders table to avoid joining to the Customer table every time an order is viewed).
• Creating Aggregate/Summary Tables: Storing pre-calculated totals, averages, or counts to avoid running expensive aggregate functions at query time (e.g., a table that stores the daily sales total).
• Merging Tables: Combining two tables that are frequently joined into a single, wider table.