Data Engineer Associate Task 2.5: Use JDBC/ODBC or REST clients in notebooks to land data into cloud storage or directly into Unity Catalog–governed tables, usually orchestrated and scheduled with Lakeflow Jobs.

Question 1

A notebook reads a PostgreSQL table into a DataFrame and then writes it to a Unity Catalog table:

df = (spark.read.format("jdbc")
  .option("url", jdbc_url)
  .option("dbtable", "public.orders")
  .option("partitionColumn", "order_id")
  .option("lowerBound", "1")
  .option("upperBound", "1000000")
  .option("numPartitions", "8")
  .load())

The table has order_id values from 1 to 3,000,000. Which rows does df contain?

Accepted Answer

Rows with order_id 1 to 3,000,000, split into 8 partitions by stride

Answer

Rows with order_id from 1 to 1,000,000, since the bounds act as a filter

Answer

The first 8 rows, one row from each partition

Answer

No rows; the read fails because values exceed upperBound

Question 2

A notebook reads a PostgreSQL table over JDBC each night. The database password must not appear in the notebook's code, its parameters or its output. How should the notebook obtain it?

Accepted Answer

dbutils.secrets.get on a secret scope that holds the password

Answer

From a notebook widget whose default value is the password

Answer

From the compute's Spark configuration, typed in as plain text

Answer

From a CSV file stored in a Unity Catalog volume

Question 3

A JDBC read from an Oracle table is slow. The table has no numeric, date or timestamp column suitable for partitioning, and profiling shows the time goes to network round trips, because the driver returns 10 rows per trip. Which option addresses this?

Accepted Answer

fetchsize

Answer

partitionColumn with lowerBound, upperBound and numPartitions

Answer

batchsize

Answer

queryTimeout

Question 4

A notebook must read only the last seven days of a large orders table over JDBC, and the filtering should run in the database. The read does not need to be split into partitions. Which option setup is valid?

Accepted Answer

The query option set to a SELECT with the date filter, and no dbtable option

Answer

Both dbtable set to orders and query set to the filtered SELECT

Answer

dbtable set to orders with pushDownPredicate set to false, then a Spark filter

Answer

The query option with the filtered SELECT plus partitionColumn set to order_id

Question 5

A notebook calls a partner's REST API, which returns pages of JSON. The raw responses must be kept as files governed by Unity Catalog and then loaded incrementally into a bronze table by a later step of the same job. Where should the notebook write the responses?

Accepted Answer

To a directory in a Unity Catalog volume

Answer

To /tmp on the driver's local disk

Answer

To the DBFS root of the workspace

Answer

Into task values for the next task to read

Task 2.5: Use JDBC/ODBC or REST clients in notebooks to land data into cloud storage or directly into Unity Catalog–governed tables, usually orchestrated and scheduled with Lakeflow Jobs.

Study it

Sample questions

A notebook reads a PostgreSQL table over JDBC each night. The database password must not appear in the notebook's code, its parameters or its output. How should the notebook obtain it?

A JDBC read from an Oracle table is slow. The table has no numeric, date or timestamp column suitable for partitioning, and profiling shows the time goes to network round trips, because the driver returns 10 rows per trip. Which option addresses this?

A notebook must read only the last seven days of a large orders table over JDBC, and the filtering should run in the database. The read does not need to be split into partitions. Which option setup is valid?

A notebook calls a partner's REST API, which returns pages of JSON. The raw responses must be kept as files governed by Unity Catalog and then loaded incrementally into a bronze table by a later step of the same job. Where should the notebook write the responses?