{"id":16788,"date":"2026-09-19T11:11:20","date_gmt":"2026-09-19T11:11:20","guid":{"rendered":"https:\/\/www.examlabs.com\/certification\/?p=16788"},"modified":"2026-09-19T11:11:20","modified_gmt":"2026-09-19T11:11:20","slug":"databricks-certified-data-engineer-associate-test-questions-and-exam-dumps-part19-q361-380","status":"publish","type":"post","link":"https:\/\/www.examlabs.com\/certification\/databricks-certified-data-engineer-associate-test-questions-and-exam-dumps-part19-q361-380\/","title":{"rendered":"Databricks Certified Data Engineer Associate Test Questions and Exam Dumps Part19 Q361-380"},"content":{"rendered":"<h1><\/h1>\n<h2><b>View Full <\/b><a href=\"https:\/\/www.examlabs.com\/certified-data-engineer-associate-exam-dumps\"><b>Databricks Certified Data Engineer Associate Exam Dumps<\/b><\/a><b> and Practice Test Dumps.<\/b><\/h2>\n<h3><b>Question 361<\/b><\/h3>\n<p><b>Which SQL command can be used to view the properties currently configured for a Delta table?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SHOW TBLPROPERTIES<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SHOW DATABASES<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SHOW FUNCTIONS<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SHOW COLUMNS<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 1<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">SHOW TBLPROPERTIES<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">SHOW DATABASES<\/span><span style=\"font-weight: 400;\"> lists catalogs or databases, <\/span><span style=\"font-weight: 400;\">SHOW FUNCTIONS<\/span><span style=\"font-weight: 400;\"> lists available functions, and <\/span><span style=\"font-weight: 400;\">SHOW COLUMNS<\/span><span style=\"font-weight: 400;\"> provides column information. Data engineers can use <\/span><span style=\"font-weight: 400;\">SHOW TBLPROPERTIES<\/span><span style=\"font-weight: 400;\"> when they need to inspect configuration values without modifying the underlying table.<\/span><\/p>\n<h3><b>Question 362<\/b><\/h3>\n<p><b>A data engineer wants to enable a specific Delta Lake feature through a table property. Which command is appropriate for modifying an existing table&#8217;s properties?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ALTER TABLE table_name ADD COLUMN property_name<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ALTER TABLE table_name SET TBLPROPERTIES (&#8216;property_name&#8217; = &#8216;value&#8217;)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UPDATE table_name SET TBLPROPERTIES = &#8216;value&#8217;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">MODIFY TABLE table_name PROPERTY property_name = &#8216;value&#8217;<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 2<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">ALTER TABLE &#8230; SET TBLPROPERTIES<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 363<\/b><\/h3>\n<p><b>A Delta table contains an array column named <\/b><b>items<\/b><b>. Each row may contain multiple product identifiers. The engineer needs one output row for every item in the array. Which function is most appropriate?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">concat()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">collect_list()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">explode()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">flatten()<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 3<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">explode()<\/span><span style=\"font-weight: 400;\"> 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, <\/span><span style=\"font-weight: 400;\">explode(items)<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">collect_list()<\/span><span style=\"font-weight: 400;\"> performs the opposite type of aggregation by collecting values into an array, while <\/span><span style=\"font-weight: 400;\">concat()<\/span><span style=\"font-weight: 400;\"> combines values.<\/span><\/p>\n<h3><b>Question 364<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">to_json()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">explode()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">concat()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">from_json()<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 4<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">from_json()<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">to_json()<\/span><span style=\"font-weight: 400;\"> performs the reverse operation by converting structured data into JSON text. <\/span><span style=\"font-weight: 400;\">explode()<\/span><span style=\"font-weight: 400;\"> expands arrays or maps, while <\/span><span style=\"font-weight: 400;\">concat()<\/span><span style=\"font-weight: 400;\"> combines strings or other compatible values.<\/span><\/p>\n<h3><b>Question 365<\/b><\/h3>\n<p><b>A data engineer wants to create a Delta table directly from the results of a SQL query. Which approach is appropriate?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE new_table AS SELECT &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE new_table USING JSON AS SELECT &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE VIEW new_table AS TABLE &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">INSERT TABLE new_table FROM SELECT &#8230;<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 1<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">CREATE TABLE &#8230; AS SELECT<\/span><span style=\"font-weight: 400;\">, 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.<\/span><\/p>\n<h3><b>Question 366<\/b><\/h3>\n<p><b>A data engineer needs to create a new table containing only selected records from an existing Delta table. Which SQL statement is most suitable?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE VIEW new_table AS SELECT &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE new_table AS SELECT * FROM source_table WHERE &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ALTER TABLE source_table FILTER &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UPDATE TABLE new_table FROM source_table &#8230;<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 2<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">A CTAS statement can create a new table from a filtered query result. For example, <\/span><span style=\"font-weight: 400;\">CREATE TABLE new_table AS SELECT * FROM source_table WHERE status = &#8216;active&#8217;<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">ALTER TABLE<\/span><span style=\"font-weight: 400;\"> does not provide a general filtering operation, and the proposed <\/span><span style=\"font-weight: 400;\">UPDATE TABLE<\/span><span style=\"font-weight: 400;\"> syntax is invalid.<\/span><\/p>\n<h3><b>Question 367<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">WHERE<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ORDER BY<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">HAVING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LIMIT<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 3<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">HAVING<\/span><span style=\"font-weight: 400;\"> clause filters results after grouping and aggregation have been performed. In this scenario, the query might group records by customer and calculate <\/span><span style=\"font-weight: 400;\">SUM(sales)<\/span><span style=\"font-weight: 400;\">, then use <\/span><span style=\"font-weight: 400;\">HAVING SUM(sales) &gt; 10000<\/span><span style=\"font-weight: 400;\"> to retain only qualifying customers. <\/span><span style=\"font-weight: 400;\">WHERE<\/span><span style=\"font-weight: 400;\"> filters individual rows before grouping and therefore is not the appropriate clause for filtering an aggregate result. <\/span><span style=\"font-weight: 400;\">ORDER BY<\/span><span style=\"font-weight: 400;\"> sorts the final results, while <\/span><span style=\"font-weight: 400;\">LIMIT<\/span><span style=\"font-weight: 400;\"> restricts the number of rows returned without evaluating the aggregate condition.<\/span><\/p>\n<h3><b>Question 368<\/b><\/h3>\n<p><b>A table contains NULL values in a <\/b><b>phone_number<\/b><b> column. The engineer wants to display the customer&#8217;s alternate phone number whenever the primary phone number is NULL. Which SQL function is appropriate?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">NULLIF()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ISNULL()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">NVL2()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">COALESCE()<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 4<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">COALESCE()<\/span><span style=\"font-weight: 400;\"> returns the first non-NULL expression from the values provided. For example, <\/span><span style=\"font-weight: 400;\">COALESCE(phone_number, alternate_phone)<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">NULLIF()<\/span><span style=\"font-weight: 400;\"> returns NULL when two expressions are equal, while the other functions have different null-handling behavior. <\/span><span style=\"font-weight: 400;\">COALESCE()<\/span><span style=\"font-weight: 400;\"> is particularly convenient when more than two possible fallback values are available.<\/span><\/p>\n<h3><b>Question 369<\/b><\/h3>\n<p><b>A data engineer needs to categorize orders as <\/b><b>High<\/b><b>, <\/b><b>Medium<\/b><b>, or <\/b><b>Low<\/b><b> based on their total amount. Which SQL construct is most appropriate?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CASE WHEN<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION ALL<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">HAVING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">DISTINCT<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 1<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">CASE WHEN<\/span><span style=\"font-weight: 400;\"> 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 <\/span><span style=\"font-weight: 400;\">High<\/span><span style=\"font-weight: 400;\"> for amounts above a threshold, <\/span><span style=\"font-weight: 400;\">Medium<\/span><span style=\"font-weight: 400;\"> for intermediate values, and <\/span><span style=\"font-weight: 400;\">Low<\/span><span style=\"font-weight: 400;\"> for smaller amounts. <\/span><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> combines query results, <\/span><span style=\"font-weight: 400;\">HAVING<\/span><span style=\"font-weight: 400;\"> filters grouped results, and <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> removes duplicate rows. <\/span><span style=\"font-weight: 400;\">CASE WHEN<\/span><span style=\"font-weight: 400;\"> is therefore the appropriate construct for creating conditional categories within query results.<\/span><\/p>\n<h3><b>Question 370<\/b><\/h3>\n<p><b>Which statement correctly describes the difference between <\/b><b>UNION<\/b><b> and <\/b><b>UNION ALL<\/b><b> in SQL?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION always preserves duplicates, while UNION ALL removes them.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION removes duplicate rows, while UNION ALL retains them.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION sorts every result, while UNION ALL never returns rows.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION works only with Delta tables, while UNION ALL works only with views.<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 2<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">UNION<\/span><span style=\"font-weight: 400;\"> combines the results of compatible queries and removes duplicate rows from the combined result. <\/span><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> also combines query results but preserves duplicate rows. Because duplicate removal requires additional processing, <\/span><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 371<\/b><\/h3>\n<p><b>A data engineer wants to count the number of unique customers who placed orders. Which SQL expression should be used?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">COUNT(customer_id)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SUM(customer_id)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">COUNT(DISTINCT customer_id)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">DISTINCT(COUNT(customer_id))<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 3<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">COUNT(DISTINCT customer_id)<\/span><span style=\"font-weight: 400;\"> counts each unique non-NULL customer identifier once. This is different from <\/span><span style=\"font-weight: 400;\">COUNT(customer_id)<\/span><span style=\"font-weight: 400;\">, which counts every non-NULL occurrence and may count the same customer multiple times when the customer has placed several orders. <\/span><span style=\"font-weight: 400;\">SUM()<\/span><span style=\"font-weight: 400;\"> is designed for numeric aggregation rather than counting unique identifiers. Using <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> inside <\/span><span style=\"font-weight: 400;\">COUNT<\/span><span style=\"font-weight: 400;\"> directly expresses the requirement to determine the number of different customers represented in the dataset.<\/span><\/p>\n<h3><b>Question 372<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE VIEW sales_table AS SELECT &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE sales_table AS VIEW &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE sales_table USING PARQUET AS SELECT &#8230;<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE TABLE sales_table USING DELTA AS SELECT &#8230;<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 4<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">CREATE TABLE &#8230; USING DELTA AS SELECT<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 373<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CREATE OR REPLACE TABLE<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">SHOW OR REPLACE TABLE<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ALTER OR CREATE TABLE<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UPDATE OR CREATE TABLE<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 1<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">CREATE OR REPLACE TABLE<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 374<\/b><\/h3>\n<p><b>A data engineer needs to add a new column named <\/b><b>email<\/b><b> to an existing Delta table without removing its existing columns or rows. Which statement should be used?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UPDATE TABLE customers ADD email STRING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ALTER TABLE customers ADD COLUMNS (email STRING)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">INSERT TABLE customers ADD email STRING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">MODIFY TABLE customers COLUMN email STRING<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 2<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">ALTER TABLE &#8230; ADD COLUMNS<\/span><span style=\"font-weight: 400;\"> adds one or more columns to an existing table while preserving the existing table data. For example, <\/span><span style=\"font-weight: 400;\">ALTER TABLE customers ADD COLUMNS (email STRING)<\/span><span style=\"font-weight: 400;\"> adds an <\/span><span style=\"font-weight: 400;\">email<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 375<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CASE ONLY<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">NULLIF()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">COALESCE()<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">DISTINCT<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 3<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">COALESCE()<\/span><span style=\"font-weight: 400;\"> accepts multiple expressions and returns the first expression that is not NULL. For example, <\/span><span style=\"font-weight: 400;\">COALESCE(primary_email, secondary_email, default_email)<\/span><span style=\"font-weight: 400;\"> 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. <\/span><span style=\"font-weight: 400;\">NULLIF()<\/span><span style=\"font-weight: 400;\"> serves a different purpose by returning NULL when two expressions are equal. <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> removes duplicate results rather than selecting fallback values from multiple expressions.<\/span><\/p>\n<h3><b>Question 376<\/b><\/h3>\n<p><b>A source column contains values such as <\/b><b>active<\/b><b>, <\/b><b>inactive<\/b><b>, and <\/b><b>pending<\/b><b>. The engineer needs to convert them into numeric status codes within a query. Which approach is most suitable?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION ALL<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">HAVING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">COALESCE only<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">CASE WHEN<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 4<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">CASE WHEN<\/span><span style=\"font-weight: 400;\"> is appropriate for mapping categorical values to other representations. An engineer can define conditions such as <\/span><span style=\"font-weight: 400;\">WHEN status = &#8216;active&#8217; THEN 1<\/span><span style=\"font-weight: 400;\">, <\/span><span style=\"font-weight: 400;\">WHEN status = &#8216;inactive&#8217; THEN 2<\/span><span style=\"font-weight: 400;\">, and so on. This creates a derived numeric status code directly in the query. <\/span><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> combines separate result sets rather than mapping values, <\/span><span style=\"font-weight: 400;\">HAVING<\/span><span style=\"font-weight: 400;\"> filters aggregated results, and <\/span><span style=\"font-weight: 400;\">COALESCE()<\/span><span style=\"font-weight: 400;\"> handles NULL fallback values. Therefore, conditional logic with <\/span><span style=\"font-weight: 400;\">CASE WHEN<\/span><span style=\"font-weight: 400;\"> is the suitable approach.<\/span><\/p>\n<h3><b>Question 377<\/b><\/h3>\n<p><b>Which SQL clause should be used when an engineer wants to remove duplicate rows from the result of a query?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">DISTINCT<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">HAVING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LIMIT<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">GROUP FILTER<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 1<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> keyword removes duplicate combinations of the selected columns from a query result. For example, <\/span><span style=\"font-weight: 400;\">SELECT DISTINCT city FROM customers<\/span><span style=\"font-weight: 400;\"> returns each city only once. This is useful when duplicate rows exist in the source or when the desired output should contain unique values. <\/span><span style=\"font-weight: 400;\">HAVING<\/span><span style=\"font-weight: 400;\"> filters groups after aggregation, while <\/span><span style=\"font-weight: 400;\">LIMIT<\/span><span style=\"font-weight: 400;\"> controls the number of rows returned. <\/span><span style=\"font-weight: 400;\">GROUP FILTER<\/span><span style=\"font-weight: 400;\"> is not a standard SQL clause for removing duplicates. <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> directly expresses the requirement for unique query results.<\/span><\/p>\n<h3><b>Question 378<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">HAVING<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">WHERE<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">ORDER BY<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">LIMIT<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 2<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">The <\/span><span style=\"font-weight: 400;\">WHERE<\/span><span style=\"font-weight: 400;\"> clause filters individual source rows before grouping and aggregation take place. For a date-range requirement, the engineer can use a condition such as <\/span><span style=\"font-weight: 400;\">WHERE order_date &gt;= &#8230; AND order_date &lt; &#8230;<\/span><span style=\"font-weight: 400;\">. Filtering early can also reduce the amount of data that later aggregation needs to process. <\/span><span style=\"font-weight: 400;\">HAVING<\/span><span style=\"font-weight: 400;\"> is intended for filtering grouped or aggregated results, while <\/span><span style=\"font-weight: 400;\">ORDER BY<\/span><span style=\"font-weight: 400;\"> sorts rows and <\/span><span style=\"font-weight: 400;\">LIMIT<\/span><span style=\"font-weight: 400;\"> restricts the number of returned rows. Therefore, <\/span><span style=\"font-weight: 400;\">WHERE<\/span><span style=\"font-weight: 400;\"> is the appropriate clause for row-level date filtering.<\/span><\/p>\n<h3><b>Question 379<\/b><\/h3>\n<p><b>A Delta table stores customer events in a nested JSON structure. The engineer first parses the JSON and then needs to access the nested <\/b><b>address.city<\/b><b> field. What is the key benefit of parsing the JSON into a structured type?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It automatically deletes invalid records.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It converts all nested fields into separate physical tables.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It allows nested fields to be referenced as structured attributes.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It permanently converts every JSON document into plain text.<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 3<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">Parsing JSON into a structured type allows Spark to understand the document&#8217;s schema and lets engineers reference nested attributes directly. For example, after parsing a customer JSON object, a field such as <\/span><span style=\"font-weight: 400;\">address.city<\/span><span style=\"font-weight: 400;\"> 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.<\/span><\/p>\n<h3><b>Question 380<\/b><\/h3>\n<p><b>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?<\/b><\/p>\n<ol>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">DISTINCT<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">GROUP BY<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">UNION ALL<\/span><\/li>\n<\/ol>\n<p><b>Correct Answer: 4<\/b><\/p>\n<p><b>Explanation<\/b><\/p>\n<p><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> 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, <\/span><span style=\"font-weight: 400;\">UNION<\/span><span style=\"font-weight: 400;\"> removes duplicate rows from the combined result. <\/span><span style=\"font-weight: 400;\">DISTINCT<\/span><span style=\"font-weight: 400;\"> also removes duplicates from a query result, while <\/span><span style=\"font-weight: 400;\">GROUP BY<\/span><span style=\"font-weight: 400;\"> creates groups for aggregation or other grouped operations. When duplicate event records are intentionally meaningful, <\/span><span style=\"font-weight: 400;\">UNION ALL<\/span><span style=\"font-weight: 400;\"> is the appropriate operation.<\/span><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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? SHOW TBLPROPERTIES SHOW DATABASES SHOW FUNCTIONS SHOW COLUMNS Correct Answer: 1 Explanation The SHOW TBLPROPERTIES command displays the properties associated with a table, including [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1648,1647],"tags":[],"_links":{"self":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/16788"}],"collection":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/comments?post=16788"}],"version-history":[{"count":1,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/16788\/revisions"}],"predecessor-version":[{"id":16791,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/16788\/revisions\/16791"}],"wp:attachment":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/media?parent=16788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/categories?post=16788"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/tags?post=16788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}