General

Question 1

In the UI, where can you go to facilitate granting select access to a user

Answer 1

Data Explorer

Question 2

What is Delta Lake

Answer 2

an open source storage format, like parquet, with additional capabilities that can provide reliability, security, and performance

Question 3

How is the data organized in storage when managing a delta table

Answer 3

All of the data is broken down into one or more parquet files, log files are broken down to one or more json files, and each transaction creates a new data file and log file

Question 4

What is the underlying technology that makes auto loader work

Answer 4

Structured Streaming

Question 5

When should you use AutoLoader vs Copy Into

Answer 5

• You want to load from a location that contains files in the order of millions or higher. Auto Loader can discover files more efficiently
• Auto Loader supports file notification
• Data schema evolves frequently

Question 6

When loading with Auto loader, how do you deal with an evolving schema

Answer 6

mergeSchema will infer the schema across multiple files and merge the schema of each file

Question 7

How can you use merge to deduplicate upon writing

Answer 7

MERGE INTO target USING source
ON target.key = source.key
WHEN NOT MATCHED THEN INSERT *

Question 8

How do you use merge to delete all target rows that have no matches in the source table?

Answer 8

MERGE INTO target USING source
ON target.key = source.key
WHEN NOT MATCHED BY SOURCE THEN DELETE

Question 9

Where can you include timeouts in jobs

Answer 9

in the task

Question 10

How can you automate an alert

Answer 10

By having it on a refresh schedule

Question 11

How do you grant read capability to a table

Answer 11

GRANT SELECT, USAGE ON TABLE customers to some@Email.com

Question 12

What type of constraint keeps the bad records and adds them to the target dataset

Answer 12

CONSTRAINT valid_timestamp EXPECT (timestamp > ‘2020-01-01’)

Question 13

What type of constraint drops bad records

Answer 13

CONSTRAINT valid_timestamp EXPECT (timestamp > ‘2020-01-01’) ON VIOLATION DROP ROW

Question 14

What type of constraint fails when there is a bad record

Answer 14

CONSTRAINT valid_timestamp EXPECT (timestamp > ‘2020-01-01’) ON VIOLATION FAIL UPDATE

Question 15

When creating a db what is the default location of the database

Answer 15

dbfs:/user/hive/warehouse

Question 16

How do you create an external table

Answer 16

Answer is CREATE TABLE transactions (id int, desc string) USING DELTA LOCATION ‘/mnt/delta/transactions’

Question 17

When you drop an external DELTA table using the SQL Command DROP TABLE table_name, how does it impact metadata(delta log, history), and data stored in the storage?

Answer 17

Drops table from metastore, but keeps metadata (delta log, history, and data in storage

Question 18

What is the best way to query external csv files located on DBFS Storage to inspect the data using SQL?

Answer 18

SELECT * FROM CSV. ‘dbfs:/location/csv_files/’

Question 19

How do you create a table from csv with headers, delimiter of | in SQL

Answer 19

CREATE TABLE customer(customerId, customerName)
USING CSV
OPTIONS(header=”true”, delimiter=”|”)

Question 20

How can you do a distinct count that ignores null values

Answer 20

Count(DISTINCT *)

Question 21

What command combines two datasets while eliminating duplicates

Answer 21

UNION

Question 22

What options are not allowed on streaming data

Answer 22

Select *
Multiple streaming aggregations
Limit and take the first N
Distinct
Deduplication
Sorting WITHOUT aggregation

Question 23

What are some examples of options allowed on streaming data

Answer 23

Sum()
Max()
Count()

Question 24

What technique does structured streaming use to ensure recovery of failures during stream processing?

Answer 24

Structured Streaming uses checkpointing and write-ahead logs to record the offset range of data being processed during each trigger interval.

Question 25

What is an example of creating a generated data column

Answer 25

GENERATED ALWAYS AS (CAST(orderTime as DATE))

Question 26

Which plane hosts jobs/pipelines and queries

Answer 26

Control Plane

Question 27

T/F Databricks lakehouse does not support Stored Procedures

Answer 27

True

Question 28

What keyword means you are creating an external table

Answer 28

LOCATION
CREATE TABLE table_name ( column column_data_type…) USING format LOCATION “dbfs:/”

Question 29

What is an example of a UDF

Answer 29

CREATE FUNCTION udf_convert(temp DOUBLE, measure STRING)
RETURNS DOUBLE
RETURN CASE WHEN measure == ‘F’ then (temp * 9/5) + 32
ELSE (temp – 33 ) * 5/9
END

Question 30

What is a command that will create a view on top of a delta stream

Answer 30

Spark.readStream.format(“delta”).table(“sales”).createOrReplaceTempView(“streaming_vw”)

Question 31

How does structured streaming achieve end to end fault tolerance?

Answer 31

First, Structured Streaming uses checkpointing and write-ahead logs to record the offset range of data being processed during each trigger interval.

Next, the streaming sinks are designed to be idempotent

Question 32

What are the three type of exceptions with Delta Live tables

Answer 32

Except - Records that violate the expectation are added to the target dataset along with valid records. (CONSTRAINT valid_timestamp EXPECT (timestamp > ‘2012-01-01’))
Drop - Drop invalid records (CONSTRAINT valid_current_page EXPECT (current_page_id IS NOT NULL and current_page_title IS NOT NULL) ON VIOLATION DROP ROW)
Fail - halt execution immediately when a record fails validation (CONSTRAINT valid_count EXPECT (count > 0) ON VIOLATION FAIL UPDATE)

Question 33

When building a DLT s pipeline you have two options to create a live tables, what is the main difference between CREATE STREAMING LIVE TABLE vs CREATE LIVE TABLE?

Answer 33

CREATE STREAMING LIVE TABLE is used when working with Streaming data sources and Incremental data

Question 34

Where can you see the jobs history in the UI

Answer 34

Under jobs UI select the job you are interested, under runs we can see current active runs and last 60 days historical run

Question 35

What are the different ways you can schedule a job in Databricks workspace?

Answer 35

Cron, on demand

Question 36

On SQL Endpoint(SQL Warehouse) single cluster, how many queries can run at a time

Answer 36

10

Question 37

Which section in the UI can be used to manage permissions and grants to tables?

Answer 37

Data Explorer

Question 38

What are two examples of privileges not available in the unity catalog

Answer 38

DELETE and UPDATE

Question 39

How do you transfer a table’s ownership

Answer 39

ALTER TABLE table_name OWNER to ‘group’