ACID transactions on data lakes in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a Delta Lake transaction commit

What will be the output count of rows after the following Delta Lake transaction commits?

Apache Spark

from delta.tables import DeltaTable
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('DeltaExample').getOrCreate()

# Create initial DataFrame
initial_data = [(1, 'apple'), (2, 'banana')]
df = spark.createDataFrame(initial_data, ['id', 'fruit'])
df.write.format('delta').mode('overwrite').save('/tmp/delta-table')

deltaTable = DeltaTable.forPath(spark, '/tmp/delta-table')

# Start transaction: update and insert
(deltaTable.alias('t')
 .merge(
     spark.createDataFrame([(2, 'blueberry'), (3, 'cherry')], ['id', 'fruit']).alias('s'),
     't.id = s.id')
 .whenMatchedUpdate(set={'fruit': 's.fruit'})
 .whenNotMatchedInsert(values={'id': 's.id', 'fruit': 's.fruit'})
 .execute())

# Read final table
final_df = spark.read.format('delta').load('/tmp/delta-table')
print(final_df.count())

A 4

B 5

C 2

D 3

💡 Hint

Think about how merge updates existing rows and inserts new ones without duplicates.

The merge updates the row with id=2 and inserts a new row with id=3. The original row with id=1 remains unchanged. So total rows are 3.

🧠 Conceptual

intermediate

Understanding ACID guarantees in Delta Lake

Which of the following best describes the ACID guarantees provided by Delta Lake on data lakes?

A Atomicity ensures all parts of a transaction succeed or fail together, Consistency maintains data integrity, Isolation prevents concurrent transaction interference, Durability guarantees committed data is saved permanently.

B Atomicity allows partial transaction commits, Consistency allows temporary data corruption, Isolation allows transactions to see uncommitted data, Durability means data is saved only in memory.

C Atomicity means transactions are split into smaller parts, Consistency means data is always encrypted, Isolation means transactions run sequentially, Durability means data is backed up daily.

D Atomicity means transactions never fail, Consistency means data never changes, Isolation means no parallel processing, Durability means data is stored on SSDs.

💡 Hint

Recall the meaning of each ACID property in databases.

Delta Lake provides full ACID guarantees: atomic commits, consistent data state, isolation of concurrent transactions, and durable storage of committed data.

🔧 Debug

advanced

Identify the error in Delta Lake transaction code

What error will the following code raise when trying to update a Delta table?

Apache Spark

from delta.tables import DeltaTable
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('DeltaDebug').getOrCreate()

# Create initial Delta table
initial_data = [(1, 'apple')]
df = spark.createDataFrame(initial_data, ['id', 'fruit'])
df.write.format('delta').mode('overwrite').save('/tmp/delta-table')

deltaTable = DeltaTable.forPath(spark, '/tmp/delta-table')

# Attempt to update without condition
(deltaTable.update(set={'fruit': 'orange'}))

A TypeError: update() missing 1 required positional argument: 'condition'

B ValueError: Cannot update without a condition

C No error, updates all rows to 'orange'

D SyntaxError: invalid syntax in update statement

💡 Hint

Check the method signature for update in DeltaTable API.

The update() method requires a condition argument to specify which rows to update. Omitting it causes a TypeError.

❓ data_output

advanced

Resulting data after concurrent Delta Lake writes

Two concurrent transactions write to the same Delta table. Transaction 1 inserts (4, 'date'), Transaction 2 updates id=2 to 'blackberry'. After both commit, what is the content of the table?

Apache Spark

from delta.tables import DeltaTable
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName('DeltaConcurrent').getOrCreate()

# Initial data
initial_data = [(1, 'apple'), (2, 'banana'), (3, 'cherry')]
df = spark.createDataFrame(initial_data, ['id', 'fruit'])
df.write.format('delta').mode('overwrite').save('/tmp/delta-table')

deltaTable = DeltaTable.forPath(spark, '/tmp/delta-table')

# Simulate Transaction 1
(deltaTable.alias('t')
 .merge(
    spark.createDataFrame([(4, 'date')], ['id', 'fruit']).alias('s'),
    't.id = s.id')
 .whenNotMatchedInsertAll()
 .execute())

# Simulate Transaction 2
deltaTable.update(condition="id = 2", set={"fruit": "blackberry"})

final_df = spark.read.format('delta').load('/tmp/delta-table')
final_df.orderBy('id').show()

A [ (1, 'apple'), (2, 'banana'), (3, 'cherry'), (4, 'date') ]

B [ (1, 'apple'), (2, 'blackberry'), (3, 'cherry') ]

C [ (1, 'apple'), (2, 'blackberry'), (3, 'cherry'), (4, 'date') ]

D [ (1, 'apple'), (3, 'cherry'), (4, 'date') ]

💡 Hint

Think about how ACID transactions isolate and commit changes.

Both transactions commit successfully. Transaction 1 inserts a new row with id=4. Transaction 2 updates id=2. The final table has all four rows with updated values.

🚀 Application

expert

Choosing Delta Lake for ACID transactions on a data lake

You manage a large data lake with many users reading and writing data simultaneously. Which feature of Delta Lake most directly helps maintain data correctness and consistency in this environment?

A The integration with Apache Spark for distributed computing.

B The use of optimistic concurrency control with transaction logs to handle simultaneous writes safely.

C The support for schema evolution allowing flexible data structure changes.

D The ability to store data in Parquet format for faster reads.

💡 Hint

Focus on how Delta Lake manages concurrent writes to avoid conflicts.

Delta Lake uses optimistic concurrency control with a transaction log to ensure ACID compliance and prevent write conflicts in concurrent environments.

Practice

1. What does ACID stand for in the context of data lakes?

easy

A. Aggregation, Calculation, Insertion, Deletion

B. Accuracy, Clarity, Integrity, Data

C. Access, Control, Indexing, Distribution

D. Atomicity, Consistency, Isolation, Durability

Solution

1. Step 1: Understand ACID properties

   ACID is a set of properties that guarantee reliable processing of database transactions.

2. Step 2: Match each letter to its meaning

   Atomicity means all or nothing, Consistency means data stays correct, Isolation means transactions don't interfere, Durability means changes are permanent.

3. Final Answer:

   Atomicity, Consistency, Isolation, Durability -> Option D

4. Quick Check:

   ACID = Atomicity, Consistency, Isolation, Durability [OK]

Hint: Remember ACID as key database safety rules [OK]

Common Mistakes:

• Confusing ACID with unrelated terms
• Mixing up the letters' meanings
• Thinking ACID is only for SQL databases

2. Which syntax correctly creates a Delta table with ACID support in Apache Spark?

easy

A. spark.sql("CREATE TABLE delta_table TYPE delta LOCATION '/delta/path'")

B. spark.sql("CREATE DELTA TABLE delta_table AS '/delta/path'")

C. spark.sql("CREATE TABLE delta_table USING delta LOCATION '/delta/path'")

D. spark.sql("MAKE TABLE delta_table USING delta LOCATION '/delta/path'")

Solution

1. Step 1: Recall Delta Lake table creation syntax

   The correct syntax uses CREATE TABLE with USING delta and LOCATION to specify path.

2. Step 2: Identify correct syntax among options

   spark.sql("CREATE TABLE delta_table USING delta LOCATION '/delta/path'") matches the correct syntax exactly; others have wrong keywords or order.

3. Final Answer:

   spark.sql("CREATE TABLE delta_table USING delta LOCATION '/delta/path'") -> Option C

4. Quick Check:

   CREATE TABLE ... USING delta ... = spark.sql("CREATE TABLE delta_table USING delta LOCATION '/delta/path'") [OK]

Hint: Look for 'CREATE TABLE ... USING delta' syntax [OK]

Common Mistakes:

• Using 'MAKE TABLE' instead of 'CREATE TABLE'
• Wrong keyword order or missing USING
• Confusing AS with LOCATION

3. Given the following code snippet, what will be the output count after the update?

from delta.tables import DeltaTable

# Assume delta_table is a DeltaTable object
# Initial data has 3 rows with column 'value' = [10, 20, 30]
delta_table.update("value = 20", {"value": "value + 5"})
count = delta_table.toDF().count()
print(count)

medium

A. 4

B. 3

C. 2

D. 0

Solution

1. Step 1: Understand the update operation

   The update changes rows where value = 20 by adding 5, so that row's value becomes 25.

2. Step 2: Check if row count changes

   Update modifies data in place; it does not add or remove rows, so count remains 3.

3. Final Answer:

   3 -> Option B

4. Quick Check:

   Update changes values, not row count = 3 [OK]

Hint: Update changes data but not number of rows [OK]

Common Mistakes:

• Thinking update adds new rows
• Confusing update with insert
• Assuming count changes after update

4. Identify the error in this Delta Lake transaction code:

delta_table.update("value > 10", {"value": "value + 1"})
delta_table.delete("value < 5")
delta_table.commit()

medium

A. DeltaTable has no commit() method; transactions auto-commit

B. Update condition syntax is incorrect

C. Delete condition should be a function, not a string

D. Missing startTransaction() before update

Solution

1. Step 1: Check DeltaTable API methods

   DeltaTable supports update and delete, but does not have a commit() method; changes auto-commit.

2. Step 2: Verify other method usages

   Update and delete accept string conditions correctly; no need for explicit transaction start.

3. Final Answer:

   DeltaTable has no commit() method; transactions auto-commit -> Option A

4. Quick Check:

   No commit() in DeltaTable API = DeltaTable has no commit() method; transactions auto-commit [OK]

Hint: Delta Lake auto-commits; no commit() needed [OK]

Common Mistakes:

• Trying to call commit() on DeltaTable
• Misunderstanding condition parameter types
• Expecting manual transaction control

5. You want to ensure multiple users can safely update a Delta Lake table without conflicts. Which feature helps achieve this?

hard

A. Delta Lake's optimistic concurrency control with ACID transactions

B. Using Spark's cache() to lock the table

C. Manually locking files in the storage system

D. Running updates sequentially with sleep delays

Solution

1. Step 1: Understand concurrency in Delta Lake

   Delta Lake uses optimistic concurrency control to handle multiple users updating data safely.

2. Step 2: Evaluate other options

   Cache() only speeds reads, manual file locks are error-prone, and sleep delays are unreliable.

3. Final Answer:

   Delta Lake's optimistic concurrency control with ACID transactions -> Option A

4. Quick Check:

   Optimistic concurrency control = safe multi-user updates [OK]

Hint: Trust Delta Lake's built-in concurrency control [OK]

Common Mistakes:

• Thinking cache() locks data
• Trying manual file locks
• Using delays instead of transaction control