Databricks Certified Data Engineer Associate Exam Questions Instant Access

Question 1

A data engineer only wants to execute the final block of a Python program if the Python variable day_of_week is equal to 1 and the Python variable review_period is True.

Which of the following control flow statements should the data engineer use to begin this conditionally executed code block?

Aif day_of_week = 1 and review_period:

Bif day_of_week = 1 and review_period = 'True':

Cif day_of_week == 1 and review_period == 'True':

Dif day_of_week == 1 and review_period:

Eif day_of_week = 1 & review_period: = 'True':

Answer : D

In Python, the==operator is used to compare the values of two variables, while the=operator is used to assign a value to a variable. Therefore, option A and E are incorrect, as they use the=operator for comparison. Option B and C are also incorrect, as they compare thereview_periodvariable to a string value'True', which is different from the boolean valueTrue. Option D is the correct answer, as it uses the==operator to compare theday_of_weekvariable to the integer value1, and theandoperator to check if both conditions are true. If both conditions are true, then the final block of the Python program will be executed.Reference: [Python Operators], [Python If ... Else]

Question 2

A data engineer triggers a scheduled job but finds that the new run was not executed. The run history shows that the run was skipped with a concurrency-related queue message.

Which configuration should the engineer investigate?

AThe cluster spot-instance availability in the cloud provider

BThe SQL warehouse permissions for the task

CThe job-level max_concurrent_runs setting and whether queueing is enabled

DThe task-level timeout_seconds configuration

Answer : C

The engineer should inspect the job's maximum concurrent-runs configuration and its queueing setting. max_concurrent_runs controls how many active runs of the same job can execute simultaneously. When that limit has already been reached, Databricks can skip the newly triggered run if queueing is disabled. When queueing is enabled, the additional run waits until capacity becomes available instead of being skipped. Spot-instance availability might delay or prevent compute startup, but it would not normally produce a job-level concurrency message. SQL warehouse permissions affect SQL task execution after a run begins, while timeout_seconds determines how long a task may run before timing out. Therefore, option C directly addresses the reported run state. This candidate was also compared against the existing Word question bank.

Question 3

A data engineer is cleaning a Bronze table. The requirement is to eliminate rows where either the customer_email field or the customer_phone field is NULL. The cleaning must be performed in one operation using a single method call.

Which PySpark approach supports filtering multiple columns for NULL values in one call?

Adf.dropna(subset=['customer_email', 'customer_phone'])

Bdf.where('customer_email IS NOT NULL').where('customer_phone IS NOT NULL')

Cdf.na.drop(how='all')

Ddf.filter(col('customer_email').isNotNull() & col('customer_phone').isNotNull())

Answer : A

Question 4

A data engineer needs to enforce row-level security on main.secure.events(region STRING, event_id STRING). Members of the account group all_regions must see all rows. All other users must see only rows where region = 'EU'.

Which SQL sequence satisfies the requirement?

ACREATE FUNCTION main.secure.region_filter(region STRING)
RETURN IF(is_account_group_member('all_regions'), true, region = 'EU');
ALTER TABLE main.secure.events
SET ROW FILTER main.secure.region_filter ON (region);

BCREATE FUNCTION main.secure.region_filter(region STRING)
RETURN IF(is_account_group_member('all_regions'), true, region = 'EU');
ALTER TABLE main.secure.events
ALTER COLUMN region
SET MASK main.secure.region_filter;

CCREATE FUNCTION main.secure.region_filter(region STRING)
RETURN IF(is_account_group_member('all_regions'), true, region = 'EU');
ALTER VIEW main.secure.events
SET ROW FILTER main.secure.region_filter ON (region);

DCREATE FUNCTION main.secure.region_filter(region STRING)
RETURN IF(is_account_group_member('all_regions'), true, region = 'EU');
ALTER TABLE main.secure.events
SET ROW FILTER main.secure.region_filter USING COLUMNS (region);

Answer : A

A row-filter function must return a Boolean value indicating whether each row is visible to the current user. The function in option A returns true for members of the account-level all_regions group, allowing them to see every row. For other users, it returns the result of region = 'EU', limiting visibility to European records. is_account_group_member checks direct or indirect membership in an account-level group. The ALTER TABLE ... SET ROW FILTER ... ON (region) statement then associates the function with the table and passes the region column to it for every query. A column mask transforms displayed column values rather than removing rows. The object is a table, not a view, and USING COLUMNS is not the correct row-filter attachment syntax. Therefore, option A is correct.

Question 5

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?

AThe STREAM function is not needed and will cause an error.

BThe table being created is a live table.

CThe customers table is a streaming live table.

DThe customers table is a reference to a Structured Streaming query on a PySpark DataFrame.

EThe data in the customers table has been updated since its last run.

Answer : C

The STREAM function is used to process data from a streaming live table or view, which is a table or view that contains data that has been added only since the last pipeline update. Streaming live tables and views are stateful, meaning that they retain the state of the previous pipeline run and only process new data based on the current query. This is useful for incremental processing of streaming or batch data sources. The customers table in the query is a streaming live table, which means that it contains the latest data from the source. The STREAM function enables the query to read the data from the customers table incrementally and create another streaming live table named loyal_customers, which contains the customer IDs of the customers with high loyalty level.Reference:Difference between LIVE TABLE and STREAMING LIVE TABLE,CREATE STREAMING TABLE,Load data using streaming tables in Databricks SQL.

Question 6

Calculate the total sales amount for each region and store the results in a new dataframe called region_sales.

Given the expected result:

Which code will generate the expected result?

Aregion_sales = sales_df.groupBy('region').agg(sum('sales_amountM).alias('total_sales_amount'))

Bregion_sales = sales_df. sum ('salen_aiTiount') . groupBy ('region') .alias ('total_sale3_amount')

Cregion_sales= sales_df.groupBy('category').sum(nsales_amount').alias('t_otal_sales_amounl')

Dregion sales - sales_df.agg(sum('sales_amount').groupBy('region').alias('total sales amount'))

Answer : A

Question 7

A notebook task named check_volume calculates a daily data volume and writes it to a Lakeflow Jobs task value named record_count. If the count is greater than 1 million, the job should run high_volume_pipeline; otherwise, it should run normal_pipeline.

Which control-flow feature should be used?

AAdd an If/else condition task that reads the record_count task value and routes execution to different downstream tasks.

BConfigure additional retries for check_volume until the count exceeds the threshold.

CUse a For each task over record_count and stop when the value is below the threshold.

DConfigure Run if conditions based purely on task status.

Answer : A