NEW
Font size
WorksheetsDatabricks Certified Data Engineer Quiz part 2
Total questions: 50
Worksheet time: 25mins
A data engineer has realized that the data files associated with a Delta table are incredibly small. They want to compact the small files to form larger files to improve performance. Which of the following keywords can be used to compact the small files?
REDUCE
OPTIMIZE
COMPACTION
REPARTITION
VACUUM
In which of the following file formats is data from Delta Lake tables primarily stored?
Delta
CSV
Parquet
JSON
A proprietary, optimized format specific to Databricks
Which of the following is stored in the Databricks customer's cloud account?
Databricks web application
Cluster management metadata
Repos
Data
Notebooks
Which of the following can be used to simplify and unify siloed data architectures that are specialized for specific use cases?
None of these
Data lake
Data warehouse
All of these
Data lakehouse
A data architect has determined that a table of the following format is necessary: Which of the following code blocks uses SQL DDL commands to create an empty Delta table in the above format regardless of whether a table already exists with this name?
A data engineer has a Python notebook in Databricks, but they need to use SQL to accomplish a specific task within a cell. They still want all of the other cells to use Python without making any changes to those cells. Which of the following describes how the data engineer can use SQL within a cell of their Python notebook?
It is not possible to use SQL in a Python notebook
They can attach the cell to a SQL endpoint rather than a Databricks cluster
They can simply write SQL syntax in the cell
They can add %sql to the first line of the cell
They can change the default language of the notebook to SQL
Which of the following SQL keywords can be used to convert a table from a long format to a wide format?
TRANSFORM
PIVOT
SUM
CONVERT
WHERE
Which of the following describes a benefit of creating an external table from Parquet rather than CSV when using a CREATE TABLE AS SELECT statement?
Parquet files can be partitioned
CREATE TABLE AS SELECT statements cannot be used on files
Parquet files have a well-defined schema
Parquet files have the ability to be optimized
Parquet files will become Delta tables
A data engineer wants to create a relational object by pulling data from two tables. The relational object does not need to be used by other data engineers in other sessions. In order to save on storage costs, the data engineer wants to avoid copying and storing physical data. Which of the following relational objects should the data engineer create?
Spark SQL Table
View
Database
Temporary view
Delta Table
A data analyst has developed a query that runs against Delta table. They want help from the data engineering team to implement a series of tests to ensure the data returned by the query is clean. However, the data engineering team uses Python for its tests rather than SQL. Which of the following operations could the data engineering team use to run the query and operate with the results in PySpark?
SELECT * FROM sales
spark.delta.table
spark.sql
There is no way to share data between PySpark and SQL.
spark.table
Which of the following commands will return the number of null values in the member_id column?
SELECT count(member_id) FROM my_table;
SELECT count(member_id) - count_null(member_id) FROM my_table;
SELECT count_if(member_id IS NULL) FROM my_table;
SELECT null(member_id) FROM my_table;
SELECT count_null(member_id) FROM my_table;
A data engineer needs to apply custom logic to identify employees with more than 5 years of experience in array column employees in table stores. The custom logic should create a new column exp_employees that is an array of all of the employees with more than 5 years of experience for each row. In order to apply this custom logic at scale, the data engineer wants to use the FILTER higher-order function. Which of the following code blocks successfully completes this task?
A data engineer has a Python variable table_name that they would like to use in a SQL query. They want to construct a Python code block that will run the query using table_name. They have the following incomplete code block: ____ (f"SELECT customer_id, spend FROM {table_name}") Which of the following can be used to fill in the blank to successfully complete the task?
spark.delta.sql
spark.delta.table
spark.table
dbutils.sql
spark.sql
A data engineer has created a new database using the following command: CREATE DATABASE IF NOT EXISTS customer360; In which of the following locations will the customer360 database be located?
dbfs:/user/hive/database/customer360
dbfs:/user/hive/warehouse
dbfs:/user/hive/customer360
More information is needed to determine the correct response
dbfs:/user/hive/database
A data engineer is attempting to drop a Spark SQL table my_table and runs the following command: DROP TABLE IF EXISTS my_table; After running this command, the engineer notices that the data files and metadata files have been deleted from the file system. Which of the following describes why all of these files were deleted?
The table was managed
The table's data was smaller than 10 GB
The table's data was larger than 10 GB
The table was external
The table did not have a location
A data engineer that is new to using Python needs to create a Python function to add two integers together and return the sum? Which of the following code blocks can the data engineer use to complete this task?
In which of the following scenarios should a data engineer use the MERGE INTO command instead of the INSERT INTO command?
When the location of the data needs to be changed
When the target table is an external table
When the source table can be deleted
When the target table cannot contain duplicate records
When the source is not a Delta table
A data engineer is working with two tables. Each of these tables is displayed below in its entirety.
(LOOK AT THE PIC.)
Which of the following will be returned by the above query?
A data engineer needs to create a table in Databricks using data from a CSV file at location /path/to/csv. They run the following command:
Which of the following lines of code fills in the above blank to successfully complete the task?
None of these lines of code are needed to successfully complete the task
USING CSV
FROM CSV
USING DELTA
FROM "path/to/csv"
A data engineer has configured a Structured Streaming job to read from a table, manipulate the data, and then perform a streaming write into a new table. The code block used by the data engineer is below:
If the data engineer only wants the query to process all of the available data in as many batches as required, which of the following lines of code should the data engineer use to fill in the blank?
processingTime(1)
trigger(availableNow=True)
trigger(parallelBatch=True)
trigger(processingTime="once")
trigger(continuous="once")
A data engineer has developed a data pipeline to ingest data from a JSON source using Auto Loader, but the engineer has not provided any type inference or schema hints in their pipeline. Upon reviewing the data, the data engineer has noticed that all of the columns in the target table are of the string type despite some of the fields only including float or boolean values. Which of the following describes why Auto Loader inferred all of the columns to be of the string type?
There was a type mismatch between the specific schema and the inferred schema
JSON data is a text-based format
Auto Loader only works with string data
All of the fields had at least one null value
Auto Loader cannot infer the schema of ingested data
A Delta Live Table pipeline includes two datasets defined using STREAMING LIVE TABLE. Three datasets are defined against Delta Lake table sources using LIVE TABLE. The table is configured to run in Development mode using the Continuous Pipeline Mode. Assuming previously unprocessed data exists and all definitions are valid, what is the expected outcome after clicking Start to update the pipeline?
All datasets will be updated once and the pipeline will shut down. The compute resources will be terminated.
All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will persist until the pipeline is shut down.
All datasets will be updated once and the pipeline will persist without any processing. The compute resources will persist but go unused.
All datasets will be updated once and the pipeline will shut down. The compute resources will persist to allow for additional testing.
All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will persist to allow for additional testing.
Which of the following data workloads will utilize a Gold table as its source?
A job that enriches data by parsing its timestamps into a human-readable format
A job that aggregates uncleaned data to create standard summary statistics
A job that cleans data by removing malformatted records
A job that queries aggregated data designed to feed into a dashboard
A job that ingests raw data from a streaming source into the Lakehouse
Which of the following must be specified when creating a new Delta Live Tables pipeline?
A key-value pair configuration
The preferred DBU/hour cost
A path to cloud storage location for the written data
A location of a target database for the written data
At least one notebook library to be executed
A data engineer has joined an existing project and they see the following query in the project repository: CREATE STREAMING LIVE TABLE loyal_customers AS SELECT customer_id - FROM STREAM(LIVE.customers) WHERE loyalty_level = 'high'; Which of the following describes why the STREAM function is included in the query?
The STREAM function is not needed and will cause an error.
The table being created is a live table.
The customers table is a streaming live table.
The customers table is a reference to a Structured Streaming query on a PySpark DataFrame.
The data in the customers table has been updated since its last run.
Which of the following describes the type of workloads that are always compatible with Auto Loader?
Streaming workloads
Machine learning workloads
Serverless workloads
Batch workloads
Dashboard workloads
A data engineer and data analyst are working together on a data pipeline. The data engineer is working on the raw, bronze, and silver layers of the pipeline using Python, and the data analyst is working on the gold layer of the pipeline using SQL. The raw source of the pipeline is a streaming input. They now want to migrate their pipeline to use Delta Live Tables. Which of the following changes will need to be made to the pipeline when migrating to Delta Live Tables?
None of these changes will need to be made
The pipeline will need to stop using the medallion-based multi-hop architecture
The pipeline will need to be written entirely in SQL
The pipeline will need to use a batch source in place of a streaming source
The pipeline will need to be written entirely in Python
A data engineer is using the following code block as part of a batch ingestion pipeline to read from a composable table:
Which of the following changes needs to be made so this code block will work when the transactions table is a stream source?
Replace predict with a stream-friendly prediction function
Replace schema(schema) with option ("maxFilesPerTrigger", 1)
Replace "transactions" with the path to the location of the Delta table
Replace format("delta") with format("stream")
Replace spark.read with spark.readStream
Which of the following queries is performing a streaming hop from raw data to a Bronze table?
A dataset has been defined using Delta Live Tables and includes an expectations clause: CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION FAIL UPDATE What is the expected behavior when a batch of data containing data that violates these constraints is processed?
Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
Records that violate the expectation cause the job to fail.
Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.
Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.
Records that violate the expectation are added to the target dataset and flagged as invalid in a field added to the target dataset.
Which of the following statements regarding the relationship between Silver tables and Bronze tables is always true?
Silver tables contain a less refined, less clean view of data than Bronze data.
Silver tables contain aggregates while Bronze data is unaggregated.
Silver tables contain more data than Bronze tables.
Silver tables contain a more refined and cleaner view of data than Bronze tables.
Silver tables contain less data than Bronze tables.
A data engineering team has noticed that their Databricks SQL queries are running too slowly when they are submitted to a non-running SQL endpoint. The data engineering team wants this issue to be resolved. Which of the following approaches can the team use to reduce the time it takes to return results in this scenario?
They can turn on the Serverless feature for the SQL endpoint and change the Spot Instance Policy to "Reliability Optimized."
They can turn on the Auto Stop feature for the SQL endpoint.
They can increase the cluster size of the SQL endpoint.
They can turn on the Serverless feature for the SQL endpoint.
They can increase the maximum bound of the SQL endpoint's scaling range.
A data engineer has a Job that has a complex run schedule, and they want to transfer that schedule to other Jobs. Rather than manually selecting each value in the scheduling form in Databricks, which of the following tools can the data engineer use to represent and submit the schedule programmatically?
pyspark.sql.types.DateType
datetime
pyspark.sql.types.TimestampType
Cron syntax
There is no way to represent and submit this information programmatically
Which of the following approaches should be used to send the Databricks Job owner an email in the case that the Job fails?
Manually programming in an alert system in each cell of the Notebook
Setting up an Alert in the Job page
Setting up an Alert in the Notebook
There is no way to notify the Job owner in the case of Job failure
MLflow Model Registry Webhooks
An engineering manager uses a Databricks SQL query to monitor ingestion latency for each data source. The manager checks the results of the query every day, but they are manually rerunning the query each day and waiting for the results. Which of the following approaches can the manager use to ensure the results of the query are updated each day?
They can schedule the query to refresh every 1 day from the SQL endpoint's page in Databricks SQL.
They can schedule the query to refresh every 12 hours from the SQL endpoint's page in Databricks SQL.
They can schedule the query to refresh every 1 day from the query's page in Databricks SQL.
They can schedule the query to run every 1 day from the Jobs UI.
They can schedule the query to run every 12 hours from the Jobs UI.
In which of the following scenarios should a data engineer select a Task in the Depends On field of a new Databricks Job Task?
When another task needs to be replaced by the new task
When another task needs to fail before the new task begins
When another task has the same dependency libraries as the new task
When another task needs to use as little compute resources as possible
When another task needs to successfully complete before the new task begins
A data engineer has been using a Databricks SQL dashboard to monitor the cleanliness of the input data to a data analytics dashboard for a retail use case. The job has a Databricks SQL query that returns the number of store-level records where sales is equal to zero. The data engineer wants their entire team to be notified via a messaging webhook whenever this value is greater than 0. Which of the following approaches can the data engineer use to notify their entire team via a messaging webhook whenever the number of stores with $0 in sales is greater than zero?
They can set up an Alert with a custom template.
They can set up an Alert with a new email alert destination.
They can set up an Alert with one-time notifications.
They can set up an Alert with a new webhook alert destination.
They can set up an Alert without notifications.
A data engineer wants to schedule their Databricks SQL dashboard to refresh every hour, but they only want the associated SQL endpoint to be running when it is necessary. The dashboard has multiple queries on multiple datasets associated with it. The data that feeds the dashboard is automatically processed using a Databricks Job. Which of the following approaches can the data engineer use to minimize the total running time of the SQL endpoint used in the refresh schedule of their dashboard?
They can turn on the Auto Stop feature for the SQL endpoint.
They can ensure the dashboard's SQL endpoint is not one of the included query's SQL endpoint.
They can reduce the cluster size of the SQL endpoint.
They can ensure the dashboard's SQL endpoint matches each of the queries' SQL endpoints.
They can set up the dashboard's SQL endpoint to be serverless.
A data engineer needs access to a table new_table, but they do not have the correct permissions. They can ask the table owner for permission, but they do not know who the table owner is. Which of the following approaches can be used to identify the owner of new_table?
Review the Permissions tab in the table's page in Data Explorer
All of these options can be used to identify the owner of the table
Review the Owner field in the table's page in Data Explorer
Review the Owner field in the table's page in the cloud storage solution
There is no way to identify the owner of the table
A new data engineering team has been assigned to an ELT project. The new data engineering team will need full privileges on the table sales to fully manage the project. Which of the following commands can be used to grant full permissions on the database to the new data engineering team?
GRANT ALL PRIVILEGES ON TABLE sales TO team;
GRANT SELECT CREATE MODIFY ON TABLE sales TO team;
GRANT SELECT ON TABLE sales TO team;
GRANT USAGE ON TABLE sales TO team;
GRANT ALL PRIVILEGES ON TABLE team TO sales;
Which data lakehouse feature results in improved data quality over a traditional data lake?
A data lakehouse stores data in open formats.
A data lakehouse allows the use of SQL queries to examine data.
A data lakehouse provides storage solutions for structured and unstructured data.
A data lakehouse supports ACID-compliant transactions.
In which scenario will a data team want to utilize cluster pools?
An automated report needs to be version-controlled across multiple collaborators.
An automated report needs to be runnable by all stakeholders.
An automated report needs to be refreshed as quickly as possible.
An automated report needs to be made reproducible.
What is hosted completely in the control plane of the classic Databricks architecture?
Worker node
Databricks web application
Driver node
Databricks Filesystem
A data engineer needs to determine whether to use the built-in Databricks Notebooks versioning or version their project using Databricks Repos. What is an advantage of using Databricks Repos over the Databricks Notebooks versioning?
Databricks Repos allows users to revert to previous versions of a notebook
Databricks Repos is wholly housed within the Databricks Data Intelligence Platform
Databricks Repos provides the ability to comment on specific changes
Databricks Repos supports the use of multiple branches
What is a benefit of the Databricks Lakehouse Architecture embracing open source technologies?
Avoiding vendor lock-in
Simplified governance
Ability to scale workloads
Cloud-specific integrations
A data engineer needs to use a Delta table as part of a data pipeline, but they do not know if they have the appropriate permissions. In which location can the data engineer review their permissions on the table?
Jobs
Dashboards
Catalog Explorer
Repos
A data engineer is running code in a Databricks Repo that is cloned from a central Git repository. A colleague of the data engineer informs them that changes have been made and synced to the central Git repository. The data engineer now needs to sync their Databricks Repo to get the changes from the central Git repository. Which Git operation does the data engineer need to run to accomplish this task?
Clone
Pull
Merge
Push
Which file format is used for storing Delta Lake Table?
CSV
Parquet
JSON
Delta
A data architect has determined that a table of the following format is necessary: Which code block is used by SQL DDL command to create an empty Delta table in the above format regardless of whether a table already exists with this name?
CREATE OR REPLACE TABLE table_name ( employeeId STRING, startDate DATE, avgRating FLOAT )
CREATE OR REPLACE TABLE table_name WITH COLUMNS ( employeeId STRING, startDate DATE, avgRating FLOAT ) USING DELTA
CREATE TABLE IF NOT EXISTS table_name ( employeeId STRING, startDate DATE, avgRating FLOAT )
CREATE TABLE table_name AS SELECT employeeId STRING, startDate DATE, avgRating FLOAT
A data engineer has been given a new record of data: id STRING = 'a1' rank INTEGER = 6 rating FLOAT = 9.4 Which SQL commands can be used to append the new record to an existing Delta table my_table?
INSERT INTO my_table VALUES ('a1', 6, 9.4)
INSERT VALUES ('a1', 6, 9.4) INTO my_table
UPDATE my_table VALUES ('a1', 6, 9.4)
UPDATE VALUES ('a1', 6, 9.4) my_table
