Databricks Certified Data Engineer Associate Exam Questions

dbfs:/user/hive/warehouse Thereby showing 'dbfs:/user/hive/warehouse/customer360.db

The location of the customer360 database depends on the value of thespark.sql.warehouse.dirconfiguration property, which specifies the default location for managed databases and tables. If the property is not set, the default value isdbfs:/user/hive/warehouse. Therefore, the customer360 database will be located indbfs:/user/hive/warehouse/customer360.db. However, if the property is set to a different value, such asdbfs:/user/hive/database, then the customer360 database will be located indbfs:/user/hive/database/customer360.db. Thus, more information is needed to determine the correct response.

Option A is not correct, asdbfs:/user/hive/database/customer360is not the default location for managed databases and tables, unless thespark.sql.warehouse.dirproperty is explicitly set todbfs:/user/hive/database.

Option B is not correct, asdbfs:/user/hive/warehouseis the default location for the root directory of managed databases and tables, not for a specific database. The database name should be appended with.dbto the directory path, such asdbfs:/user/hive/warehouse/customer360.db.

Option C is not correct, asdbfs:/user/hive/customer360is not a valid location for a managed database, as it does not follow the directory structure specified by thespark.sql.warehouse.dirproperty.

Databases and Tables

[Databricks Data Engineer Professional Exam Guide]

The SQL keyword that can be used to convert a table from a long format to a wide format isPIVOT.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]

Auto Loader in Databricks utilizes Spark Structured Streaming for processing data incrementally. This allows Auto Loader to efficiently ingest streaming or batch data at scale and to recognize new data as it arrives in cloud storage. Spark Structured Streaming provides the underlying engine that supports various incremental data loading capabilities like schema inference and file notification mode, which are crucial for the dynamic nature of data lakes.

Reference: Databricks documentation on Auto Loader: Auto Loader Overview

To include a property indicating that a table contains personally identifiable information (PII), the TBLPROPERTIES keyword is used in SQL to add metadata to a table. The correct syntax to define a table property for PII is as follows:

CREATE TABLE customersInFrance

USING DELTA

TBLPROPERTIES ('PII' = 'true')

AS

SELECT id,

firstName,

lastName

FROM customerLocations

WHERE country = 'FRANCE';

The TBLPROPERTIES ('PII' = 'true') line correctly sets a table property that tags the table as containing personally identifiable information. This is in accordance with organizational policies for handling sensitive information.

Reference: Databricks documentation on Delta Lake: Delta Lake on Databricks

Auto Loader in Databricks utilizes Spark Structured Streaming for processing data incrementally. This allows Auto Loader to efficiently ingest streaming or batch data at scale and to recognize new data as it arrives in cloud storage. Spark Structured Streaming provides the underlying engine that supports various incremental data loading capabilities like schema inference and file notification mode, which are crucial for the dynamic nature of data lakes.

Reference: Databricks documentation on Auto Loader: Auto Loader Overview