Databricks-Certified-Data-Engineer-Associate Exam Dumps - Databricks Certified Data Engineer Associate Exam

Option D retains only orders whose customer identifier matches a row in customers_df. Because customers_df contains only active customers, an inner join acts as a membership filter: orders belonging to active customers match and remain, while orders belonging to inactive customers have no corresponding row and are removed. A left join with orders_df on the left would preserve every order, including orders from inactive customers, unless an additional NULL filter were applied. A left join with customers_df on the left changes the driving dataset and can exclude the required order structure or produce different cardinality. A cross join generates the Cartesian product of both DataFrames and is unrelated to key-based filtering. Therefore, the inner join in option D precisely implements the stated active-customer filtering requirement.

The SQL keyword that can be used to convert a table from a long format to a wide format is PIVOT. The PIVOT clause is used to rotate the rows of a table into columns of a new table1. The PIVOT clause can aggregate the values of a column based on the distinct values of another column, and use those values as the column names of the new table1. The PIVOT clause can be useful for transforming data from a long format, where each row represents an observation with multiple attributes, to a wide format, where each row represents an observation with a single attribute and multiple values2. For example, the PIVOT clause can be used to convert a table that contains the sales of different products by different regions into a table that contains the sales of each product by each region as separate columns1.

The other options are not suitable for converting a table from a long format to a wide format. CONVERT is a function that can be used to change the data type of an expression3. WHERE is a clause that can be used to filter the rows of a table based on a condition4. TRANSFORM is a keyword that can be used to apply a user-defined function to a group of rows in a table5. SUM is a function that can be used to calculate the total of a numeric column.

1: PIVOT | Databricks on AWS

2: Reshaping Data - Long vs Wide Format | Databricks on AWS

3: CONVERT | Databricks on AWS

4: WHERE | Databricks on AWS

5: TRANSFORM | Databricks on AWS

[SUM | Databricks on AWS]

 A streaming live table or view processes data that has been added only since the last pipeline update. Streaming tables and views are stateful; if the defining query changes, new data will be processed based on the new query and existing data is not recomputed. This is useful when data needs to be processed incrementally, such as when ingesting streaming data sources or performing incremental loads from batch data sources. A live table or view, on the other hand, may be entirely computed when possible to optimize computation resources and time. This is suitable when data needs to be processed in full, such as when performing complex transformations or aggregations that require scanning all the data. References: Difference between LIVE TABLE and STREAMING LIVE TABLE, CREATE STREAMING TABLE, Load data using streaming tables in Databricks SQL.

Databricks intentionally stops existing streaming reads and writes after an external Delta table is converted to a Unity Catalog managed table. This protects data consistency because the table’s managed storage location and access path have changed. The documented recovery action is to restart the stream with the same configuration. The existing checkpoint can then resume processing from the last committed offset, and supported path-based access is redirected to the converted managed table. REFRESH TABLE invalidates cached metadata but does not repair a stopped Structured Streaming query or modify its checkpoint. Deleting the checkpoint would discard progress information and could cause unnecessary reprocessing or duplicate output. Additional permissions are not the indicated solution when the failure is specifically caused by managed-table conversion. Therefore, option A is correct.

================

 In Databricks, you can use different languages within the same notebook by using magic commands. Magic commands are special commands that start with a percentage sign (%) and allow you to change the behavior of the cell. To use SQL within a cell of a Python notebook, you can add %sql to the first line of the cell. This will tell Databricks to interpret the rest of the cell as SQL code and execute it against the default database. You can also specify a different database by using the USE statement. The result of the SQL query will be displayed as a table or a chart, depending on the output mode. You can also assign the result to a Python variable by using the -o option. For example, %sql -o df SELECT * FROM my_table will run the SQL query and store the result as a pandas DataFrame in the Python variable df. Option A is incorrect, as it is possible to use SQL in a Python notebook using magic commands. Option B is incorrect, as attaching the cell to a SQL endpoint is not necessary and will not change the language of the cell. Option C is incorrect, as simply writing SQL syntax in the cell will result in a syntax error, as the cell will still be interpreted as Python code. Option E is incorrect, as changing the default language of the notebook to SQL will affect all the cells, not just one. References: Use SQL in Notebooks - Knowledge Base - Noteable, [SQL magic commands - Databricks], [Databricks SQL Guide - Databricks]

A materialized view is appropriate because it stores the results of a defining query and refreshes them as source data changes. Expensive joins and aggregations can therefore be computed before BI users execute their queries, improving response time and providing a consistent definition of business metrics. This aligns with the Gold layer, which normally contains highly refined, aggregated, and business-oriented datasets designed for reporting and analytics. A view over raw Bronze tables would expose unvalidated source data and repeatedly execute its underlying transformations. A streaming table is more suitable for incremental data processing than for presenting precomputed business metrics. CSV files would not provide the managed table semantics, centralized definitions, or efficient query experience required. Therefore, option A best satisfies the Gold-layer requirements.

================

In the Medallion Architecture promoted by Databricks, the Bronze layer represents the raw ingestion layer where data is stored in its original form with minimal transformation. The primary goal is to ensure data fidelity and traceability , not to apply business logic or heavy transformations. Typical operations in the Bronze layer include schema inference (or enforcement), ingestion of raw data, and the addition of metadata columns such as ingestion timestamp, load date/time, and process identifiers. These metadata fields help track lineage, enable debugging, and support incremental processing downstream. Extensive data cleansing (A), aggregations (B), and business transformations (C) are intentionally deferred to the Silver and Gold layers , where data is refined and structured for analytics. By keeping Bronze data close to the source and append-only, organizations maintain a reliable audit trail and can reprocess data if needed. Therefore, adding operational metadata like load timestamps and process IDs is the correct characteristic of Bronze layer transformations.

=========