View Full Databricks Certified Data Engineer Associate Exam Dumps and Practice Test Dumps.

Question 361

Which SQL command can be used to view the properties currently configured for a Delta table?

1. SHOW TBLPROPERTIES
2. SHOW DATABASES
3. SHOW FUNCTIONS
4. SHOW COLUMNS

Correct Answer: 1

Explanation

The SHOW TBLPROPERTIES command displays the properties associated with a table, including properties configured for Delta Lake behavior. Table properties can control or describe specific table settings and are useful when troubleshooting or verifying configuration. SHOW DATABASES lists catalogs or databases, SHOW FUNCTIONS lists available functions, and SHOW COLUMNS provides column information. Data engineers can use SHOW TBLPROPERTIES when they need to inspect configuration values without modifying the underlying table.

Question 362

A data engineer wants to enable a specific Delta Lake feature through a table property. Which command is appropriate for modifying an existing table’s properties?

1. ALTER TABLE table_name ADD COLUMN property_name
2. ALTER TABLE table_name SET TBLPROPERTIES (‘property_name’ = ‘value’)
3. UPDATE table_name SET TBLPROPERTIES = ‘value’
4. MODIFY TABLE table_name PROPERTY property_name = ‘value’

Correct Answer: 2

Explanation

ALTER TABLE … SET TBLPROPERTIES is used to add or modify properties associated with an existing Delta table. The property is specified as a key-value pair, allowing administrators or data engineers to configure supported Delta behavior. The other commands either modify table data or use syntax that is not valid for setting Delta table properties. Because table properties can affect table behavior, they should be changed deliberately and tested appropriately before being applied to production workloads.

Question 363

A Delta table contains an array column named items. Each row may contain multiple product identifiers. The engineer needs one output row for every item in the array. Which function is most appropriate?

1. concat()
2. collect_list()
3. explode()
4. flatten()

Correct Answer: 3

Explanation

The explode() function converts each element of an array or map into a separate output row. For example, if one record contains three product identifiers in an array, explode(items) can produce three rows associated with that original record. This is useful when nested data needs to be transformed into a relational structure for filtering, joining, or aggregation. collect_list() performs the opposite type of aggregation by collecting values into an array, while concat() combines values.

Question 364

A JSON column contains customer information such as name, city, and membership level. The engineer needs to convert the JSON string into structured columns that can be queried individually. Which Spark SQL function should be used?

1. to_json()
2. explode()
3. concat()
4. from_json()

Correct Answer: 4

Explanation

The from_json() function parses a JSON-formatted string into a structured value using a supplied schema. This allows individual fields within the JSON document to be referenced as columns or nested fields in Spark transformations. For example, customer name and membership level can be extracted after parsing the JSON string. to_json() performs the reverse operation by converting structured data into JSON text. explode() expands arrays or maps, while concat() combines strings or other compatible values.

Question 365

A data engineer wants to create a Delta table directly from the results of a SQL query. Which approach is appropriate?

1. CREATE TABLE new_table AS SELECT …
2. CREATE TABLE new_table USING JSON AS SELECT …
3. CREATE VIEW new_table AS TABLE …
4. INSERT TABLE new_table FROM SELECT …

Correct Answer: 1

Explanation

CREATE TABLE … AS SELECT, commonly called CTAS, creates a table using the results returned by a query. In Databricks, the resulting table can be created as a Delta table when Delta is the configured or specified format. CTAS is useful when an engineer wants to create a new table from transformed or filtered data without separately creating the table schema first. The other choices either use invalid syntax or create different database objects rather than a table from a query result.

Question 366

A data engineer needs to create a new table containing only selected records from an existing Delta table. Which SQL statement is most suitable?

1. CREATE VIEW new_table AS SELECT …
2. CREATE TABLE new_table AS SELECT * FROM source_table WHERE …
3. ALTER TABLE source_table FILTER …
4. UPDATE TABLE new_table FROM source_table …

Correct Answer: 2

Explanation

A CTAS statement can create a new table from a filtered query result. For example, CREATE TABLE new_table AS SELECT * FROM source_table WHERE status = ‘active’ creates a separate table containing only the records that satisfy the filter. This is different from creating a view, because the new table stores its own data rather than simply representing a saved query. ALTER TABLE does not provide a general filtering operation, and the proposed UPDATE TABLE syntax is invalid.

Question 367

A SQL query calculates total sales for each customer. The engineer wants to return only customers whose total sales exceed 10,000. Which clause should filter the grouped results?

1. WHERE
2. ORDER BY
3. HAVING
4. LIMIT

Correct Answer: 3

Explanation

The HAVING clause filters results after grouping and aggregation have been performed. In this scenario, the query might group records by customer and calculate SUM(sales), then use HAVING SUM(sales) > 10000 to retain only qualifying customers. WHERE filters individual rows before grouping and therefore is not the appropriate clause for filtering an aggregate result. ORDER BY sorts the final results, while LIMIT restricts the number of rows returned without evaluating the aggregate condition.

Question 368

A table contains NULL values in a phone_number column. The engineer wants to display the customer’s alternate phone number whenever the primary phone number is NULL. Which SQL function is appropriate?

1. NULLIF()
2. ISNULL()
3. NVL2()
4. COALESCE()

Correct Answer: 4

Explanation

COALESCE() returns the first non-NULL expression from the values provided. For example, COALESCE(phone_number, alternate_phone) returns the primary phone number when it exists and the alternate number when the primary value is NULL. This makes it useful for implementing fallback logic when datasets contain missing values. NULLIF() returns NULL when two expressions are equal, while the other functions have different null-handling behavior. COALESCE() is particularly convenient when more than two possible fallback values are available.

Question 369

A data engineer needs to categorize orders as High, Medium, or Low based on their total amount. Which SQL construct is most appropriate?

1. CASE WHEN
2. UNION ALL
3. HAVING
4. DISTINCT

Correct Answer: 1

Explanation

The CASE WHEN expression allows SQL queries to return different values based on specified conditions. An engineer can use it to classify orders according to their amount, such as returning High for amounts above a threshold, Medium for intermediate values, and Low for smaller amounts. UNION ALL combines query results, HAVING filters grouped results, and DISTINCT removes duplicate rows. CASE WHEN is therefore the appropriate construct for creating conditional categories within query results.

Question 370

Which statement correctly describes the difference between UNION and UNION ALL in SQL?

1. UNION always preserves duplicates, while UNION ALL removes them.
2. UNION removes duplicate rows, while UNION ALL retains them.
3. UNION sorts every result, while UNION ALL never returns rows.
4. UNION works only with Delta tables, while UNION ALL works only with views.

Correct Answer: 2

Explanation

UNION combines the results of compatible queries and removes duplicate rows from the combined result. UNION ALL also combines query results but preserves duplicate rows. Because duplicate removal requires additional processing, UNION ALL can be preferable when duplicates are meaningful or when the input datasets are already known to be distinct. Both operations can work with compatible query results from tables, views, or other supported relations. The choice depends on whether duplicate records should remain in the final dataset.

Question 371

A data engineer wants to count the number of unique customers who placed orders. Which SQL expression should be used?

1. COUNT(customer_id)
2. SUM(customer_id)
3. COUNT(DISTINCT customer_id)
4. DISTINCT(COUNT(customer_id))

Correct Answer: 3

Explanation

COUNT(DISTINCT customer_id) counts each unique non-NULL customer identifier once. This is different from COUNT(customer_id), which counts every non-NULL occurrence and may count the same customer multiple times when the customer has placed several orders. SUM() is designed for numeric aggregation rather than counting unique identifiers. Using DISTINCT inside COUNT directly expresses the requirement to determine the number of different customers represented in the dataset.

Question 372

A data engineer wants to create a table whose columns are generated from a query while explicitly storing the result in Delta format. Which statement is appropriate?

1. CREATE VIEW sales_table AS SELECT …
2. CREATE TABLE sales_table AS VIEW …
3. CREATE TABLE sales_table USING PARQUET AS SELECT …
4. CREATE TABLE sales_table USING DELTA AS SELECT …

Correct Answer: 4

Explanation

CREATE TABLE … USING DELTA AS SELECT explicitly specifies Delta as the storage format while creating a table from query results. This combines the CTAS pattern with an explicit format declaration. It is useful when the engineer wants to make the table format clear and ensure that the created table uses Delta Lake capabilities. A view does not materialize the query result as a stored table, while specifying another format such as Parquet would create a table using that format instead of Delta.

Question 373

A data engineer needs to replace an existing table definition with the result of a new query while keeping the operation concise. Which SQL pattern is designed for this purpose?

1. CREATE OR REPLACE TABLE
2. SHOW OR REPLACE TABLE
3. ALTER OR CREATE TABLE
4. UPDATE OR CREATE TABLE

Correct Answer: 1

Explanation

CREATE OR REPLACE TABLE provides a concise way to create a table if it does not exist or replace an existing table definition and data according to the supported operation. This pattern can simplify pipelines that repeatedly produce a complete table from a query or transformation. It should be used carefully because replacing a table can change its existing contents and metadata. The other listed statements are not valid SQL patterns for replacing a table in this manner.

Question 374

A data engineer needs to add a new column named email to an existing Delta table without removing its existing columns or rows. Which statement should be used?

1. UPDATE TABLE customers ADD email STRING
2. ALTER TABLE customers ADD COLUMNS (email STRING)
3. INSERT TABLE customers ADD email STRING
4. MODIFY TABLE customers COLUMN email STRING

Correct Answer: 2

Explanation

ALTER TABLE … ADD COLUMNS adds one or more columns to an existing table while preserving the existing table data. For example, ALTER TABLE customers ADD COLUMNS (email STRING) adds an email column with the specified data type. Existing rows will have NULL for the new column unless values are subsequently populated. The other options use invalid SQL syntax for adding columns. This operation is useful when the schema must evolve to accommodate a newly required attribute.

Question 375

A data engineer receives customer records where some rows have missing values in several fields. Which approach allows multiple fallback values to be checked from left to right?

1. CASE ONLY
2. NULLIF()
3. COALESCE()
4. DISTINCT

Correct Answer: 3

Explanation

COALESCE() accepts multiple expressions and returns the first expression that is not NULL. For example, COALESCE(primary_email, secondary_email, default_email) checks the values from left to right and returns the first available value. This makes the function useful for cleaning datasets with several possible sources for the same attribute. NULLIF() serves a different purpose by returning NULL when two expressions are equal. DISTINCT removes duplicate results rather than selecting fallback values from multiple expressions.

Question 376

A source column contains values such as active, inactive, and pending. The engineer needs to convert them into numeric status codes within a query. Which approach is most suitable?

1. UNION ALL
2. HAVING
3. COALESCE only
4. CASE WHEN

Correct Answer: 4

Explanation

CASE WHEN is appropriate for mapping categorical values to other representations. An engineer can define conditions such as WHEN status = ‘active’ THEN 1, WHEN status = ‘inactive’ THEN 2, and so on. This creates a derived numeric status code directly in the query. UNION ALL combines separate result sets rather than mapping values, HAVING filters aggregated results, and COALESCE() handles NULL fallback values. Therefore, conditional logic with CASE WHEN is the suitable approach.

Question 377

Which SQL clause should be used when an engineer wants to remove duplicate rows from the result of a query?

1. DISTINCT
2. HAVING
3. LIMIT
4. GROUP FILTER

Correct Answer: 1

Explanation

The DISTINCT keyword removes duplicate combinations of the selected columns from a query result. For example, SELECT DISTINCT city FROM customers returns each city only once. This is useful when duplicate rows exist in the source or when the desired output should contain unique values. HAVING filters groups after aggregation, while LIMIT controls the number of rows returned. GROUP FILTER is not a standard SQL clause for removing duplicates. DISTINCT directly expresses the requirement for unique query results.

Question 378

A data engineer wants to return orders placed during a specific date range before performing a grouping operation. Which clause should normally be used to filter the individual order records?

1. HAVING
2. WHERE
3. ORDER BY
4. LIMIT

Correct Answer: 2

Explanation

The WHERE clause filters individual source rows before grouping and aggregation take place. For a date-range requirement, the engineer can use a condition such as WHERE order_date >= … AND order_date < …. Filtering early can also reduce the amount of data that later aggregation needs to process. HAVING is intended for filtering grouped or aggregated results, while ORDER BY sorts rows and LIMIT restricts the number of returned rows. Therefore, WHERE is the appropriate clause for row-level date filtering.

Question 379

A Delta table stores customer events in a nested JSON structure. The engineer first parses the JSON and then needs to access the nested address.city field. What is the key benefit of parsing the JSON into a structured type?

1. It automatically deletes invalid records.
2. It converts all nested fields into separate physical tables.
3. It allows nested fields to be referenced as structured attributes.
4. It permanently converts every JSON document into plain text.

Correct Answer: 3

Explanation

Parsing JSON into a structured type allows Spark to understand the document’s schema and lets engineers reference nested attributes directly. For example, after parsing a customer JSON object, a field such as address.city can be selected or used in transformations. This is different from simply storing JSON as an unparsed string, where field-level access requires additional parsing. Structured data can therefore be filtered, selected, joined, and transformed more naturally within Spark and SQL operations.

Question 380

A pipeline needs to combine the results of two queries while preserving duplicate records because each occurrence represents a separate event. Which operation should the engineer use?

1. UNION
2. DISTINCT
3. GROUP BY
4. UNION ALL

Correct Answer: 4

Explanation

UNION ALL combines the results of two compatible queries while preserving duplicate rows. This is important when each occurrence represents a meaningful event and removing duplicates would incorrectly reduce the event count. In contrast, UNION removes duplicate rows from the combined result. DISTINCT also removes duplicates from a query result, while GROUP BY creates groups for aggregation or other grouped operations. When duplicate event records are intentionally meaningful, UNION ALL is the appropriate operation.