Skip to content

Lesson 6 of 6

Build a small lakehouse pipeline

Combine workspace, cluster, Delta table, and Unity Catalog to ingest a new file, update the table, and query results with restricted access.

7 minDatabricks

After this lesson, you will be able to combine a workspace, cluster, Delta table, and Unity Catalog to ingest a new CSV file, update the table, and query the results with restricted access.

Prepare a new CSV file

In lesson 4, you created a Delta table from a CSV file. Now you will add more data to that table from a second CSV file. Create a new CSV file in your cloud storage with the same columns as the original: name, age, and city. For example, save the following content as new_people.csv in the same location as your original CSV:

name,age,city
David,40,Madrid
Eve,28,Tokyo
Frank,33,New York

This file has three new rows. You will use it to update the Delta table you registered in Unity Catalog in lesson 5.

Ingest the new file into the Delta table

Open the notebook you used in lesson 5, or create a new one and attach it to your cluster. First, read the new CSV file with Spark SQL to check its contents:

SELECT * FROM csv.`<path-to-new-csv>`

Replace <path-to-new-csv> with the actual path to your new CSV file. The result shows the three new rows.

Now you will add these rows to the existing Delta table. Use a MERGE statement to insert rows that do not already exist. The MERGE statement compares the incoming data with the table and applies updates or inserts based on a condition. Here, you will insert only rows whose name is not already in the table:

MERGE INTO my_catalog.my_schema.people_delta AS target
USING csv.`<path-to-new-csv>` AS source
ON target.name = source.name
WHEN NOT MATCHED THEN INSERT (name, age, city) VALUES (source.name, source.age, source.city)

This statement uses the three-part name my_catalog.my_schema.people_delta from lesson 5. If you used different names, adjust them. The MERGE statement reads the CSV file as a source table, matches rows by name, and inserts any source rows that do not have a matching name in the target table.

Run the statement. Then query the table to confirm the new rows were added:

SELECT * FROM my_catalog.my_schema.people_delta

You should see the original rows plus the three new rows.

Check the transaction history

Delta Lake records every change to a table in its transaction log. You saw this in lesson 4 with DESCRIBE HISTORY. Run it again to see the new write operation:

DESCRIBE HISTORY my_catalog.my_schema.people_delta

The result shows a new row with operation equal to MERGE and a higher version number than before. This confirms that the MERGE statement was recorded as a transaction.

Control access to the updated table

In lesson 5, you granted a test user SELECT on the table and then revoked it. Now you will grant SELECT again so the test user can query the updated table. Run these statements:

GRANT USE CATALOG ON CATALOG my_catalog TO `[email protected]`;
GRANT USE SCHEMA ON SCHEMA my_catalog.my_schema TO `[email protected]`;
GRANT SELECT ON TABLE my_catalog.my_schema.people_delta TO `[email protected]`;

Replace [email protected] with the actual user or group you used in lesson 5. These statements give the user permission to use the catalog and schema, and to read the table.

Now, using a different user account or by asking a colleague, run a query as that test user. For example, in a new notebook attached to a cluster, run:

SELECT * FROM my_catalog.my_schema.people_delta

The query should succeed and show all rows, including the new ones. This demonstrates that the test user can see the updated data.

Finally, revoke the SELECT privilege to restrict access again:

REVOKE SELECT ON TABLE my_catalog.my_schema.people_delta FROM `[email protected]`;

Run SHOW GRANTS ON TABLE my_catalog.my_schema.people_delta to confirm that the SELECT privilege is removed. The test user will no longer be able to query the table.

Summary

You have built a small lakehouse pipeline. You ingested a new CSV file, updated a Delta table with a MERGE statement, checked the transaction history, and controlled access with Unity Catalog. This combines the workspace, cluster, Delta table, and Unity Catalog into a working data pipeline.

Key takeaways

  • A MERGE statement can insert new rows from a CSV file into an existing Delta table based on a matching condition.
  • Delta Lake records every write operation, including MERGE, in the transaction history.
  • Unity Catalog privileges must be granted again after revoking them if a user needs access to updated data.
  • The lakehouse pipeline combines cloud storage, Delta tables, and Unity Catalog access control in a Databricks workspace.

Try it

Create a third CSV file with at least two new rows and the same columns as before. Use a MERGE statement to insert only the rows whose name is not already in the table. Run DESCRIBE HISTORY and confirm that a new MERGE operation appears with a higher version. Then grant SELECT to a test user, have them query the table to see all rows, and finally revoke the privilege. Check that SHOW GRANTS on the table no longer shows the test user.

Official documentation

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.