Databricks

Question 1

What are the 2 main components of Databricks?

Answer 1

The control plan: stores notebooks commands, workspace configurations.
The data plane: hosts compute resources
(clusters)

Question 2

What are the 3 different Databricks service?

Answer 2

Data science and engineering workspace
SQL
Machine learning

Question 3

What is a cluster?

Answer 3

A set of compute resources on which you run data engineer, data science workloads, which are run as a set of commands on notebook or as a job.

Question 4

What are the 2 cluster types?

Answer 4

All purpose clusters: use interactive notebooks.
Job clusters: run automated jobs

Question 5

. How long does Databricks retain cluster configuration information?

Answer 5

30 days
Pin cluster to keep all purpose cluster after 30 days.

Question 6

1.6. What are the three cluster modes?

Answer 6

Standard clusters: large amounts of data with Apache Spark.
Single Node clusters: jobs that use small amounts of data or non-distributed
workloads such as single-node machine learning libraries.
High Concurrency clusters: groups of users who need to share resources or run ad-hoc
jobs. Administrators usually create High Concurrency clusters.
Databricks recommends enabling
autoscaling for High Concurrency clusters.

Question 7

To ensure that all data at rest is
encrypted for all storage types, including shuffle data that is stored temporarily on your cluster’s local
disks:

Answer 7

You can enable local disk encryption.

Question 8

To reduce cluster start time

Answer 8

you can attach a cluster to a predefined pool of idle instances, for the driver
and worker nodes

Question 9

Which magic command do you use to run a notebook from
another notebook?

Answer 9

%run ../Includes/Classroom-Setup-1.2

Question 10

What is Databricks utilities and how can you use it to list out
directories of files from Python cells?

Answer 10

display(dbutils.fs.ls(“/databricks-datasets”))

Question 11

What function should you use when you have tabular data
returned by a Python cell?

Answer 11

display()

Question 12

What is Databricks Repos?

Answer 12

provides repository-level
integration with Git providers, allowing you to work in an environment that is backed by revision control
using Git

Question 13

What is the definition of a Delta Lake?

Answer 13

technology at the heart of the Databricks Lakehouse platform. It is an open source
technology that enables building a data lakehouse on top of existing storage systems.

Question 14

. How does Delta Lake address the data lake pain points to
ensure reliable, ready-to-go data?

Answer 14

ACID Transactions – Delta Lake adds ACID transactions to data lakes. ACID stands for atomicity,
consistency, isolation, and durability

Question 15

Describe how Delta Lake brings ACID transactions to object
storage

Answer 15

Difficult to append data
Difficult to modify existing data
Jobs failing mid way
Real time operations are not easy
Costly to keep historical data versions

Question 16

Is Delta Lake the default for all tables created in Databricks?

Answer 16

Yes, Delta Lakes is the default for all tables created in Databricks

Question 17

What data objects are in the Databricks Lakehouse?

Answer 17

Catalog, database, table, view, function

Question 18

What is a metastore?

Answer 18

contains all of the metadata that defines data objects in the lakehouse:
Including:
Unity catalog
Hive metastore
external metastore

Question 19

What is a catalog?

Answer 19

the highest abstraction (or coarsest grain) in the Databricks Lakehouse relational model.
catalog_name.database_name.table_name

Question 20

What is a Delta Lake table?

Answer 20

stores data as a directory of files on
cloud object storage and registers table metadata to the metastore within a catalog and schema

Question 21

What is the syntax to create a Delta Table?

Answer 21

CREATE TABLE students
(id INT, name STRING, value DOUBLE);
CREATE TABLE IF NOT EXISTS students
(id INT, name STRING, value DOUBLE)

Question 22

What is the syntax to insert data?

Answer 22

INSERT INTO students
VALUES
(4, “Ted”, 4.7),
(5, “Tiffany”, 5.5),
(6, “Vini”, 6.3)

Question 23

What is the syntax to update particular records of a table?

Answer 23

UPDATE students
SET value = value + 1
WHERE name LIKE “T%”

Question 24

What is the syntax to delete particular records of a table?

Answer 24

DELETE FROM students
WHERE value > 6

Question 25

What is the syntax for merge and what are the benefits of
using merge?

Answer 25

MERGE INTO table_a a
USING table_b b
ON a.col_name=b.col_name
WHEN MATCHED AND b.col = X
THEN UPDATE SET *
WHEN MATCHED AND a.col = Y
THEN DELETE
WHEN NOT MATCHED AND b.col = Z
THEN INSERT *

Question 26

Deduplication case (python syntax)

Answer 26

(deltaTable
.alias(“t”)
.merge(historicalUpdates.alias(“s”), “t.loan_id = s.loan_id”)
.whenNotMatchedInsertAll()
.execute())

Question 27

What is the syntax to delete a table?

Answer 27

DROP TABLE students

Question 28

What is Hive?

Answer 28

is built on top of Apache Hadoop, which is an open-source framework used to efficiently store and
process large datasets

Question 29

What are the two commands to see metadata about a table?

Answer 29

DESCRIBE EXTENDED students
DESCRIBE DETAIL students

Question 30

What is the syntax to display the Delta Lake files?

Answer 30

%python
display(dbutils.fs.ls(f”{DA.paths.user_db}/students”))

DESCRIBE DETAIL students

Question 31

Describe the Delta Lake files, their format and directory
structure

Answer 31

%python
display(dbutils.fs.ls(f”{DA.paths.user_db}/students/_delta_log”))

Question 32

What does the query engine do using the transaction logs
when we query a Delta Lake table?

Answer 32

resolve all the files that are valid in the current
version, and ignores all other data files.

You can look at a particular transaction log and see if records were inserted / updated / deleted.
%python
display(spark.sql(f”SELECT * FROM
json.{DA.paths.user_db}/students/_delta_log/00000000000000000007.json”))

Question 33

What commands do you use to compact small files and index
tables?

Answer 33

OPTIMIZE command allows you to combine files toward an optimal size:

OPTIMIZE events
OPTIMIZE events WHERE date >= ‘2017-01-01’
OPTIMIZE events
WHERE date >= current_timestamp() - INTERVAL 1 day
ZORDER BY (eventType)

Question 34

How do you review a history of table transactions?

Answer 34

Because all changes to the Delta Lake table are stored in the transaction log:
DESCRIBE HISTORY students

Question 35

. How do you query and roll back to previous table version?

Answer 35

SELECT *
FROM students VERSION AS OF 3

Question 36

How to roll back after deletion?

Answer 36

RESTORE TABLE students TO VERSION AS OF 8

Question 37

What command do you use to clean up stale data files and
what are the consequences of using this command?

Answer 37

SET spark.databricks.delta.retentionDurationCheck.enabled = false;
SET spark.databricks.delta.vacuum.logging.enabled = true;
VACUUM students RETAIN 0 HOURS DRY RUN

Question 38

What is the advantage of using Delta cache?

Answer 38

optimize performance

Question 39

What is the syntax to create a database with default location
(no location specified)?

Answer 39

CREATE DATABASE IF NOT EXISTS db_name_default_location;

Question 40

What is the syntax to create a database with specified
location?

Answer 40

CREATE DATABASE IF NOT EXISTS db_name_custom_location LOCATION
‘path/db_name_custom_location.db’;

Question 41

How do you get metadata information of a database? Where
are the databases located (difference between default vs custom
location)

Answer 41

DESCRIBE DATABASE EXTENDED db_name;

Question 42

What’s the best practice when creating databases?

Answer 42

declare a location for a given database

Question 43

What is the syntax for creating a table in a database with default location and inserting data?

Answer 43

USE db_name_default_location;

CREATE OR REPLACE TABLE managed_table_in_db_with_default_location (width INT, length
INT, height INT);
INSERT INTO managed_table_in_db_with_default_location
VALUES (3, 2, 1);
SELECT * FROM managed_table_in_db_with_default_location;

Question 44

What is the syntax for a table in a database with custom location?

Answer 44

USE db_name_custom_location;

CREATE OR REPLACE TABLE managed_table_in_db_with_custom_location (width INT, length
INT, height INT);
INSERT INTO managed_table_in_db_with_custom_location
VALUES (3, 2, 1);
SELECT * FROM managed_table_in_db_with_custom_location;

Question 45

Where are managed tables located in a database?

Answer 45

in the LOCATION of the database it is registered to

Question 46

How can you find their location?

Answer 46

DESCRIBE EXTENDED managed_table_in_db;

Question 47

What is the syntax to create an external table?

Answer 47

CREATE OR REPLACE TABLE external_table LOCATION ‘path/external_table’ AS
SELECT * FROM temp_delays;
SELECT * FROM external_table;

Python:
df.write.option(“path”, “/path/to/empty/directory”).saveAsTable(“table_name”)

Question 48

What happens when you drop tables ?(Managed tables)

Answer 48

the table’s directory and its log and data files will be deleted,
only the database directory remains.
Managed tables:
DROP TABLE managed_table_in_db_with_default_location;
DROP TABLE managed_table_in_db_with_custom_location;

Question 49

What happens when you drop tables a unmanaged table?

Answer 49

The table definition no longer exists in the metastore, but the underlying data
remain intact.
DROP TABLE external_table;

We still have access to the underlying data files:
%python
tbl_path = f”{DA.paths.working_dir}/external_table”
files = dbutils.fs.ls(tbl_path)
display(files)

Question 50

What is the command to drop the database and its underlying tables and views?

Answer 50

Default location:
DROP DATABASE db_name_default_location CASCADE;
Customer location:
DROP DATABASE db_name_custom_location CASCADE;

Question 51

How can you show a list of tables and views?

Answer 51

SHOW TABLES;

Question 52

What is the difference between Views, Temp Views & Global Temp Views?

Answer 52

View: Persisted across multiple sessions, just like a table. No process or write data.
Temp View: not persisted across multiple sessions, is scoped to the query level
Global temp view: are scoped to the cluster level, registered to a separate database. Use views with appropriate table ACLs instead of global temporary views.

Question 53

What is the syntax for view?

Answer 53

CREATE VIEW view_delays_abq_lax AS
SELECT *
FROM external_table
WHERE origin = ‘ABQ’ AND destination = ‘LAX’;
SELECT * FROM view_delays_abq_lax;

Question 54

What is the syntax for temp view?

Answer 54

CREATE TEMPORARY VIEW temp_view_delays_gt_120
AS SELECT * FROM external_table WHERE delay > 120 ORDER BY delay ASC;
SELECT * FROM temp_view_delays_gt_120;

Question 55

What is syntax for global temp view?

Answer 55

CREATE GLOBAL TEMPORARY VIEW global_temp_view_dist_gt_1000
AS SELECT * FROM external_table WHERE distance > 1000;
SELECT * FROM global_temp.global_temp_view_dist_gt_1000;

Show tables for global temp views
SHOW TABLES IN global_temp;

Question 56

What is the syntax to select from global temp views?

Answer 56

SELECT * FROM global_temp.name_of_the_global_temp_view;

Question 57

What is the syntax for defining a CTE in a subquery?

Answer 57

SELECT
max(total_delay) AS Longest Delay (in minutes)
FROM
(WITH delayed_flights(total_delay) AS (
SELECT
delay
FROM external_table)
SELECT
*
FROM
delayed_flights
);

Question 58

What is the syntax for defining a CTE in a subquery expression?

Answer 58

SELECT
(
WITH distinct_origins AS (
SELECT DISTINCT origin FROM external_table
)
SELECT
count(origin) AS Number of Distinct Origins
FROM distinct_origins)
AS Number of Different Origin Airports;

Question 59

What is the syntax for defining a CTE in a CREATE VIEW statement?

Answer 59

CREATE OR REPLACE VIEW BOS_LAX
AS WITH origin_destination(origin_airport, destination_airport)
AS (SELECT origin, destination FROM external_table)
SELECT * FROM origin_destination
WHERE origin_airport = ‘BOS’ AND destination_airport = ‘LAX’;
SELECT count(origin_airport) AS Number of Delayed Flights from BOS to LAX FROM
BOS_LAX;

Question 60

How do you query data from a single file?

Answer 60

SELECT * FROM file_format./path/to/file
SELECT * FROM json.${da.paths.datasets}/raw/events-kafka/001.json

Question 61

. How do you query a directory of files?

Answer 61

SELECT * FROM json.${da.paths.datasets}/raw/events-kafka

Question 62

How do you create references to files?

Answer 62

CREATE OR REPLACE TEMP VIEW events_temp_view
AS SELECT * FROM json.${da.paths.datasets}/raw/events-kafka/;
SELECT * FROM events_temp_view

Question 63

How do you extract text files as raw strings?

Answer 63

SELECT * FROM text.${da.paths.datasets}/raw/events-kafka/

Question 64

How do you extract the raw bytes and metadata of a file?

Answer 64

SELECT * FROM binaryFile.${da.paths.datasets}/raw/events-kafka/

Question 65

Explain why executing a direct query against CSV files rarely returns the desired result.

Answer 65

the header row can be extracted as a table row, all columns can be loaded as a single column, and the column can contain nested data that is being truncated.
SELECT * FROM csv.${da.paths.working_dir}/sales-csv

Question 66

Describe the syntax required to extract data from most formats against external sources.

Answer 66

CREATE TABLE table_identifier (col_name1 col_type1, …)
USING data_source
OPTIONS (key1 = “val1”, key2 = “val2”, …)
LOCATION = path

Question 67

Using Spark SQL DDL to create a table against an external CSV source

Answer 67

CREATE TABLE sales_csv
(order_id LONG, email STRING, transactions_timestamp LONG, total_item_quantity
INTEGER, purchase_revenue_in_usd DOUBLE, unique_items INTEGER, items STRING)
USING CSV
OPTIONS (
header = “true”, delimiter = “|”
)
LOCATION “${da.paths.working_dir}/sales-csv”

Question 68

What happens to the data, metadata and options during table declaration for these external sources?

Answer 68

All the metadata and options passed during table declaration will be persisted to the metastore.

Question 69

What is the syntax to show all of the metadata associated with the table definition?

Answer 69

DESCRIBE EXTENDED sales_csv

Question 70

How can you manually refresh the cache of your data?

Answer 70

REFRESH TABLE sales_csv

Question 71

What is the syntax to extract data from SQL Databases?

Answer 71

CREATE TABLE
USING JDBC
OPTIONS (
url = “jdbc:{databaseServerType}://{jdbcHostname}:{jdbcPort}”,
dbtable = “{jdbcDatabase}.table”, user = “{jdbcUsername}”,
password = “{jdbcPassword}”
)
DROP TABLE IF EXISTS users_jdbc;
CREATE TABLE users_jdbc
USING JDBC
OPTIONS (
url = “jdbc:sqlite:/${da.username}_ecommerce.db”,
dbtable = “users”
)
SELECT * FROM users_jdbc

Question 72

Explain the two basic approaches that Spark uses to interact with external SQL databases and their limits.

Answer 72

You can move the entire source table(s) to Databricks and then executing logic on the currently active cluster. BUT significant overhead because of network transfer latency.
You can push down the query to the external SQL database and only transfer the results back to Databricks. But significant overhead because the execution of query logic in source systems not optimized for big data queries.

Question 73

What is a CTAS statement and what is the syntax?

Answer 73

CREATE OR REPLACE TABLE sales AS
SELECT * FROM parquet.${da.paths.datasets}/raw/sales-historical/;
DESCRIBE EXTENDED sales;

Question 74

Do CTAS support manual schema declaration?

Answer 74

No

Question 75

What is the syntax to overcome the limitation when trying to ingest data from CSV files?

Answer 75

CREATE OR REPLACE TEMP VIEW sales_tmp_vw
(order_id LONG, email STRING, transactions_timestamp LONG, total_item_quantity
INTEGER, purchase_revenue_in_usd DOUBLE, unique_items INTEGER, items STRING)
USING CSV
OPTIONS (
path = “${da.paths.datasets}/raw/sales-csv”,
header = “true”,
delimiter = “|”
);
CREATE TABLE sales_delta AS
SELECT * FROM sales_tmp_vw;
SELECT * FROM sales_delta

Question 76

How do you filter and rename columns from existing tables during table creation?

Answer 76

CREATE OR REPLACE TABLE purchases AS
SELECT order_id AS id, transaction_timestamp, purchase_revenue_in_usd AS price
FROM sales;
SELECT * FROM purchases

Question 77

What is a generated column and how do you declare schemas with generated columns?

Answer 77

CREATE OR REPLACE TABLE purchase_dates (
id STRING,
transaction_timestamp STRING,
price STRING,
date DATE GENERATED ALWAYS AS (
cast(cast(transaction_timestamp/1e6 AS TIMESTAMP) AS DATE))
COMMENT “generated based on transactions_timestamp column”)

Question 78

Which built-in Spark SQL commands are useful for file ingestion (for the select clause)?

Answer 78

current_timestamp() records the timestamp when the logic is executed;
input_file_name() records the source data file for each record in the table

Question 79

What are the three options when creating tables?

Answer 79

A COMMENT is added to allow for easier discovery of table contents
A LOCATION is specified, which will result in an external (rather than managed) table
The table is PARTITIONED BY a date column;

CREATE OR REPLACE TABLE users_pii
COMMENT “Contains PII”
LOCATION “${da.paths.working_dir}/tmp/users_pii”
PARTITIONED BY (first_touch_date)
AS
SELECT *,
cast(cast(user_first_touch_timestamp/1e6 AS TIMESTAMP) AS DATE) first_touch_date,
current_timestamp() updated,
input_file_name() source_file
FROM parquet.${da.paths.datasets}/raw/users-historical/;
SELECT * FROM users_pii;

Question 80

As a best practice, should you default to partitioned tables for most use cases when working with Delta Lake?

Answer 80

you should default to nonpartitioned tables for most use cases when working with Delta Lake

Question 81

What are the two options to copy Delta Lake tables and what are the use cases?

Answer 81

DEEP CLONE
CREATE OR REPLACE TABLE purchases_clone
DEEP CLONE purchases

SHALLOW CLONE
CREATE OR REPLACE TABLE purchases_shallow_clone
SHALLOW CLONE purchases

Question 82

What are the multiple benefits of overwriting tables instead of deleting and recreating tables?

Answer 82

Much faster
The old version of the table still exists and can be easily retrieved using Time Travel;
Concurrent queries can still read the table while you are deleting the table.
if overwriting the table fails, the table will be in its previous state.

Question 83

What are the two easy methods to accomplish complete overwrites?

Answer 83

CREATE OR REPLACE TABLE: completely redefine the contents of our target table,
CREATE OR REPLACE TABLE events AS
SELECT * FROM parquet.${da.paths.datasets}/raw/events-historical

INSERT OVERWRITE: only overwrite an existing table. Will fail if we try to
change our schema.
INSERT OVERWRITE sales
SELECT * FROM parquet.`${da.paths.datasets}/raw/sales-historical/

Question 84

What is the syntax to atomically append new rows to an existing Delta table? Is the command idempotent?

Answer 84

INSERT INTO sales
SELECT * FROM parquet.${da.paths.datasets}/raw/sales-30m

Question 85

What is the syntax for the the MERGE SQL operation and the benefits of using merge?

Answer 85

MERGE INTO target a
USING source b
ON {merge_condition}
WHEN MATCHED THEN {matched_action}
WHEN NOT MATCHED THEN {not_matched_action}

Benfits: : 1. updates, inserts, and deletes are completed as a single transaction;
2. multiple conditions can be added in addition to matching fields;
3. it provides extensive options for
implementing custom logic

Question 86

How can you use merge for deduplication?

Answer 86

MERGE INTO events a
USING events_update b
ON a.user_id = b.user_id AND a.event_timestamp = b.event_timestamp
WHEN NOT MATCHED AND b.traffic_source = ‘email’ THEN
INSERT *

Question 87

What is the syntax to have an idempotent option to incrementally ingest data from external systems?

Answer 87

COPY INTO sales
FROM “${da.paths.datasets}/raw/sales-30m”
FILEFORMAT = PARQUET

Question 88

How is COPY INTO different than Auto Loader?

Answer 88

COPY INTO focused on a SQL analyst doing a batch execution.
Auto Loader requires Structured Streaming

Question 89

What is the syntax to count null values?

Answer 89

SELECT * FROM table_name WHERE col_name IS NULL
SELECT count_if(col_name IS NULL) AS new_col_name FROM table_name

Question 90

What is the syntax to count for distinct values in a table for a specific column?

Answer 90

SELECT COUNT(DISTINCT(col_1, col_2)) FROM table_name WHERE col_1 IS NOT NULL

Question 91

What is the syntax to deal with binary-encoded JSON values in a human readable format?

Answer 91

CREATE OR REPLACE TEMP VIEW events_strings AS
SELECT string(key), string(value)
FROM events_raw;
SELECT * FROM events_strings

Question 92

What is the syntax to parse JSON
objects into struct types with Spark SQL?

Answer 92

CREATE OR REPLACE TEMP VIEW parsed_events AS
SELECT from_json(value, schema_of_json(‘{insert_example_schema_here}’)) AS json
FROM events_strings;
SELECT * FROM parsed_events

Question 93

Once a JSON string is unpacked to a struct type, what is the syntax to flatten the fields into columns?

Answer 93

CREATE OR REPLACE TEMP VIEW new_events_final AS
SELECT json.*
FROM parsed_events;
SELECT * FROM new_events_final

Question 94

What is the syntax for exploding arrays of structs?

Answer 94

SELECT user_id, event_timestamp, event_name, explode(items) AS item
FROM events

Explode function lets us put each element in an array on its own row

Question 95

What is the syntax to collect arrays?

Answer 95

SELECT user_id,
collect_set(event_name) AS event_history,
array_distinct(flatten(collect_set(items.item_id))) AS cart_history
FROM events
GROUP BY user_id

The collect_set function can collect unique values for a field, including fields within arrays.
The flatten function allows multiple arrays to be combined into a single array.
The array_distinct function removes duplicate elements from an array.

Question 96

What is the syntax for a semi-join?

Answer 96

SELECT columns
FROM table_1
WHERE EXISTS (
SELECT values
FROM table_2
WHERE table_2.column = table_1.column);

Question 97

What is the syntax for FILTER ?

Answer 97

FILTER (items, i -> i.item_id LIKE “%K”) AS king_items

FILTER : the name of the higher-order function
items : the name of our input array
i : the name of the iterator variable. You choose this name and then use it in the lambda function. It iterates
over the array, cycling each value into the function one at a time.
-> : Indicates the start of a function
i.item_id LIKE “%K” : This is the function. Each value is checked to see if it ends with the capital letter
K. If it is, it gets filtered into the new column, king_items

Question 98

What is the syntax for EXIST ?

Answer 98

EXISTS (categories, c -> c = “Company Blog”) companyFlag

Let’s say we want to flag all
blog posts with “Company Blog” in the categories field. I can use the EXISTS function to mark which
entries include that category.

Question 99

What is the syntax for TRANSFORM ?

Answer 99

TRANSFORM(king_items, k -> CAST(k.item_revenue_in_usd * 100 AS INT)) AS item_revenues
we extract the item’s revenue value, multiply it by 100, and cast the result to integer

Question 100

What is the syntax for REDUCE ?

Answer 100

REDUCE(co2_level, 0, (c, acc) -> c + acc, acc ->(acc div size(co2_level)))

Question 101

What is the syntax to define and register SQL UDFs? How do
you then apply that function to the data?

Answer 101

CREATE OR REPLACE FUNCTION function_name(param TYPE)
RETURNS type_to_be_returned RETURN function_itself

Question 102

What are SQL UDFs governed by?

Answer 102

by the same Access Control Lists (ACLs)
as databases, tables, or views

Question 103

What permissions must a user have on the function to use a SQL UDF? Describe their scoping.

Answer 103

The user must have USAGE and SELECT permissions on the function to use it.

Question 104

What is the benefit of using SQL UDFs?

Answer 104

allow a handful of users to define the complex logic needed for
common reporting and analytic queries

Question 105

What is the syntax to turn SQL queries into Python strings?

Answer 105

print(“””
SELECT *
FROM table_name
“””)

Question 106

What is the syntax to execute SQL from a Python cell?

Answer 106

spark.sql(“SELECT * FROM table_name”)

Question 107

What function in python do you call to render a query the way it would appear in a normal SQL notebook?

Answer 107

display(spark.sql(“SELECT * FROM table_name”))

Question 108

What is the syntax to define a function in Python?

Answer 108

def return_new_string(string_arg):
return “The string passed to this function was “ + string_arg

Question 109

What is the syntax for f-strings?

Answer 109

f”I can substitute {my_string} here”

Question 110

How can f-strings be used for SQL queries?

Answer 110

table_name = “users”
filter_clause = “WHERE state = ‘CA’”
query = f”””
SELECT *
FROM {table_name}
{filter_clause}
“””
print(query)

Question 111

What is the syntax for if / else clauses wrapped in a function?

Answer 111

def foods_i_like(food):
if food == “beans”:
print(f”I love {food}”)
elif food == “potatoes”:
print(f”My favorite vegetable is {food}”)
elif food != “beef”:
print(f”Do you have any good recipes for {food}?”)
else:
print(f”I don’t eat {food}”)

Question 112

What are assert statements and what is the syntax?

Answer 112

Example asserting that the number 2 is an integer:
assert type(2) == int

Question 113

Why do we use try / except statements and what is the syntax?

Answer 113

try / except provides robust error handling. When a nonnumeric string is passed, an informative message is printed out.
def try_int(num_string):
try:
int(num_string)
result = f”{num_string} is a number.”
except:
result = f”{num_string} is not a number!”
print(result)

Question 114

What is the downside of using try / except statements?

Answer 114

an error will not be raised when an error occurs. Implementing logic that
suppresses errors can lead to logic silently failing.

Question 115

What is the syntax for try / except statements where you return an informative error message?

Answer 115

def three_times(number):
try:
return int(number) * 3
except ValueError as e:
print(f”You passed the string variable ‘{number}’.\n”)
print(f”Try passing an integer instead.”)
return None

Question 116

. How do you apply these concepts to execute SQL logic on Databricks, for example to avoid SQL injection attack?

Answer 116

Using a simple if clause with a function allows us to execute arbitrary SQL queries, optionally displaying
the results, and always returning the resultant DataFrame.
def simple_query_function(query, preview=True):
query_result = spark.sql(query)
if preview:
display(query_result)
return query_result
result = simple_query_function(query)

Question 117

What is the purpose of Auto Loader?

Answer 117

Provides a way for
data teams to load raw data from cloud object stores at lower costs and latencies,

Allows you to continuously ingest data into Delta Lake2,

Use as general best practice when ingesting data from cloud object storage.

Question 118

What are the 4 arguments using Auto Loader with automatic schema inference and evolution?

Answer 118

data_source : Auto Loader will detect new files as they arrive in this location and queue them for ingestion; passed to the .load() method.

source_format: While the format for all Auto Loader queries will be cloudFiles , the format of the source data should always be specified for the cloudFiles.format option

table_name: Spark Structured Streaming supports writing directly to Delta Lake tables
by passing a table name as a string to the .table() method. Note that
table you can either append to an existing table or create a new table.

checkpoint_directory: This argument is passed to the checkpointLocation and
for storing cloudFiles.schemaLocation options. Checkpoints keep track of
streaming progress, while the schema location tracks updates to the fields
in the source dataset.

Question 119

What is the benefit of Auto Loader compared to structured streaming?

Answer 119

With cloudFiles.schemaLocation , Auto Loader will infer schema wheareas traditional structured streaming will not,
Auto Loader will scan the first gigabytes of data and infer the schema for you.

Question 120

What keyword indicates that you’re using Auto Loader rather than a traditional stream for ingesting?

Answer 120

cloudFiles

Question 121

What is the _rescued_data column?

Answer 121

to capture any data that might be
malformed and not fit into the table otherwise

Question 122

What is the data type encoded by Auto Loader for fields in a text-based file format?

Answer 122

STRING type

Question 123

Historically, what were the two inefficient ways to land new data?

Answer 123

Reprocess all records in a source directory to
calculate current results.
Implement custom logic to identify new data that’s arrived since the last time a table was updated.

Question 124

How do you track the ingestion progress?

Answer 124

%sql
DESCRIBE HISTORY target_table

Question 125

What is Spark Structured Streaming?

Answer 125

Extends the functionality of Apache Spark to allow for simplified configuration
and bookkeeping when processing incremental datasets.

Allows users to interact with ever-growing data sources as if they were just a static table of records, by treating infinite data as a table.

Question 126

What is the syntax to read a stream?

Answer 126

(spark.readStream
.table(“bronze”)
.createOrReplaceTempView(“streaming_tmp_vw”))

Question 127

How can you transform streaming data?

Answer 127

%sql
SELECT device_id, count(device_id) AS total_recordings
FROM streaming_tmp_vw
GROUP BY device_id

Question 128

What are the 3 most important settings when writing a stream to Delta Lake tables?

Answer 128

Checkpointing with checkpointLocation
.outputMode(“append”).outputMode(“complete”).
Trigger Intervals

Question 129

What is the syntax to load data from a streaming temp view back to a DataFrame, and then query the table that we wrote out to?

Answer 129

(spark.table(“device_counts_tmp_vw”)
.writeStream
.option(“checkpointLocation”, f”{DA.paths.checkpoints}/silver”)
.outputMode(“complete”)
.trigger(availableNow=True)
.table(“device_counts”)
.awaitTermination() # This optional method blocks execution of the next cell until
the incremental batch write has succeeded)

Question 130

what is a bronze table?

Answer 130

contains raw data ingested from various sources (JSON files, RDBMS data, IoT data, to
name a few examples). Bronze makes sure that data is appended incrementally and grows over time.
We’re interested in retaining the full unprocessed history of each dataset in an efficient storage format which will provide us with the ability to recreate any state of a given data system.

Question 131

What is a silver table?

Answer 131

provides a more refined view of our data.
We can join fields from various bronze tables to enrich streaming records, or update account statuses based on recent activity. The silver layer might contain many pipelines and silver tables. Various different views for a given dataset. The goal is that this silver layer becomes that validated single source of truth for our data. This is the dream that the data lake could have been: Correct schema, deduplicated records, but no aggregations for our business users yet.

Question 132

What is a gold table?

Answer 132

highly refined and aggregated data. Data that has been transformed to knowledge. Updates to these tables will be completed as part of regularly scheduled production workloads, which helps control costs and allows SLAs for data freshness to be established. Gold tables provide business level aggregates often used for reporting and dashboarding. This would include aggregations such as daily active website users, weekly sales per store, or gross revenue per quarter by department. The end outputs are actionable insights, dashboards and reports of business metrics. Gold tables will often be stored in a separate storage container to help avoid cloud limits on data requests. In general, because aggregations, joins and filtering are being handled before data is written to the golden layer, query performance on data in the gold tables should be exceptional.

Question 133

Bronze: what additional metadata could you add for enhanced discoverability?

Answer 133

Examples: source file names that are being ingested, recording of the time where that data was originally processed.

Question 134

how you can configure a read on a raw JSON source using Auto Loader with schema inference?

Answer 134

(spark.readStream
.format(“cloudFiles”)
.option(“cloudFiles.format”, “json”)
.option(“cloudFiles.schemaHints”, “time DOUBLE”)
.option(“cloudFiles.schemaLocation”, f”{DA.paths.checkpoints}/bronze”)
.load(DA.paths.data_landing_location)
.createOrReplaceTempView(“recordings_raw_temp”))

Question 135

What happens with the ACID guarantees that Delta Lake brings to your data when you choose to merge this data with other data sources?

Answer 135

ensuring that only fully successfully commits are reflected in your tables. If you choose to merge these data with other data sources, be aware of how those sources version data and what sort of consistency guarantees they have.

Question 136

Describe what happens at the silver level, when we enrich our data.

Answer 136

We join the recordings data with the PII to
add patient names, the time for the recordings we parse the time for the recordings to the format ‘yyyy-MM-dd
HH:mm:ss’ to be human-readable, and we perform a quality check by excluding heart rates that are <= 0.

(spark.readStream
.table(“bronze”)
.createOrReplaceTempView(“bronze_tmp”))

%sql
CREATE OR REPLACE TEMPORARY VIEW recordings_w_pii AS (
SELECT device_id, a.mrn, b.name, cast(from_unixtime(time, ‘yyyy-MM-dd HH:mm:ss’) AS
timestamp) time, heartrate
FROM bronze_tmp a
INNER JOIN pii b
ON a.mrn = b.mrn
WHERE heartrate > 0)
(spark.table(“recordings_w_pii”)
.writeStream
.format(“delta”)
.option(“checkpointLocation”, f”{DA.paths.checkpoints}/recordings_enriched”)
.outputMode(“append”)
.table(“recordings_enriched”))
%sql
SELECT COUNT(*) FROM recordings_enriched

Question 137

Describe what happens at the Gold level.

Answer 137

We read a stream of data from recordings_enriched and write another stream to create an aggregate
gold table of daily averages for each patient.
(spark.readStream
.table(“recordings_enriched”)
.createOrReplaceTempView(“recordings_enriched_temp”))

%sql
CREATE OR REPLACE TEMP VIEW patient_avg AS (
SELECT mrn, name, mean(heartrate) avg_heartrate, date_trunc(“DD”, time) date
FROM recordings_enriched_temp
GROUP BY mrn, name, date_trunc(“DD”, time))

Question 138

What is .trigger(availableNow=True) and when is it used?

Answer 138

provides us the ability to continue to use the strengths of structured streaming while trigger this job one-time to process all available data in micro-batches.

(spark.table(“patient_avg”)
.writeStream
.format(“delta”)
.outputMode(“complete”)
.option(“checkpointLocation”, f”{DA.paths.checkpoints}/daily_avg”)
.trigger(availableNow=True) # you want the benefits of streaming but as a single
batch
.table(“daily_patient_avg”))

Question 139

What are the important considerations for complete output mode with Delta?

Answer 139

When using complete output mode, we rewrite the entire state of our table each time our logic runs.
While this is ideal for calculating aggregates, we cannot read a stream from this directory, as Structured Streaming assumes data is only being appended.

Question 140

What is the syntax for declaring a bronze layer table using Auto Loader and DLT?

Answer 140

CREATE OR REFRESH STREAMING LIVE TABLE sales_orders_raw
COMMENT “The raw sales orders, ingested from /databricks-datasets.”
AS SELECT * FROM cloud_files(“/databricks-datasets/retail-org/sales_orders/”, “json”,
map(“cloudFiles.inferColumnTypes”, “true”))

Question 141

When scheduling a Job, what are the two options to configure the cluster where the task runs?

Answer 141

New Job Cluster
Existing All-Purpose Clusters

Question 142

How can you view runs for a Job and the details of the runs?

Answer 142

To view details of the run, including the start time, duration, and status, hover over the bar in the Job Runs row.
To view details of each task, including the start time, duration, cluster, and status, hover over the cell for that task

Question 143

What are the recommendations for cluster configuration for specific job types?

Answer 143

make sure to use shared job clusters.
choose the correct cluster type for your job.

Question 144

What is the data explorer, how do you access it and what does it allow you to do?

Answer 144

allows users and admins to navigate databases, tables, and views; explore data schema,metadata, and history; set and modify permissions of relational entities

Question 145

What is Unity catalog?

Answer 145

shows where data came from, who created it and when, how it has been
modified over time, how it’s being used, and more.

Question 146

What are the four key functional areas for data governance?

Answer 146

Data Access Control: Who has access to what?

Data Access Audit: Understand who accessed what and when? What did they do? Compliance aspect.

Data Lineage: Which data objects feed downstream data objects - if you make a change to an upstream
table, how does that affect downstream and vice versa.

Data Discovery: Important to find your data and see what actually exists.

Question 147

What are the default permissions for users and admins in DBSQL?

Answer 147

Admins will have the ability to view all objects registered to the metastore and will be able to control permissions for other users in the workspace.
Users will have no permissions on anything registered to the metastore, other than objects that they create in DBSQL;
before users can create any databases, tables, or views, they must have create and usage privileges specifically granted to them.
Permissions will be set using Groups that have been configured by an administrator.
Access Control Lists (ACLs) are used to control permissions.

Question 148

What are the 6 objects for which Databricks allows you to configure permissions?

Answer 148

CATALOG controls access to the entire data catalog.

DATABASE controls access to a database.

TABLE controls access to a managed or external table.

VIEW controls access to SQL views.

FUNCTION controls access to a named function.

ANY FILE controls access to the underlying filesystem. Users granted access to ANY FILE can bypass the restrictions put on the catalog, databases, tables, and views by reading from
the file system directly.

Question 149

For each object owner, describe what they can grant privileges for.

Answer 149

Databricks administrator: All objects in the catalog and the underlying filesystem.

Catalog owner: All objects in the catalog.

Database owner: All objects in the database.

Table owner: Only the table (similar options for views and functions).

Question 150

Describe all the privileges that can be configured in Data Explorer.

Answer 150

ALL PRIVILEGES: gives all privileges (is translated into all the below privileges).

SELECT: gives read access to an object.

MODIFY: gives ability to add, delete, and modify data to or from an object.

READ_METADATA: gives ability to view an object and its metadata.

USAGE: does not give any abilities, but is an additional requirement to perform any action
on a database object.

CREATE: gives ability to create an object (for example, a table in a database)

Question 151

What is the command to generate a new database and grant permissions to all users in the DBSQL query editor?

Answer 151

To enable the ability to create databases and tables in the default catalog using Databricks SQL:
GRANT usage, create ON CATALOG hive_metastore TO users

To confirm this has run successfully:
SHOW GRANT ON CATALOG hive_metastore

Question 152

In pyspark dataframe API, what should we use to load data from from temp view back to dataframe?

Answer 152

spark.table