Databricks-Certified-Data-Engineer-Associate Sample Questions Answers

To create a table in Databricks using data from an SQLite database, the correct syntax involves specifying the format of the data source. The format in the case of using JDBC (Java Database Connectivity) with SQLite is specified by the org.apache.spark.sql.jdbc format. This format allows Spark to interface with various relational databases through JDBC. Here is how the command should be structured:

CREATE TABLE jdbc_customer360

USING org.apache.spark.sql.jdbc

OPTIONS (

url "jdbc:sqlite:/customers.db",

dbtable "customer360"

)

The USING org.apache.spark.sql.jdbc line specifies that the JDBC data source is being used, enabling Spark to interact with the SQLite database via JDBC.

References:Databricks documentation on JDBC: Connecting to SQL Databases using JDBC

The MERGE command can be used to upsert data from a source table, view, or DataFrame into a target Delta table. It allows you to specify conditions for matching and updating existing records, and inserting new records when no match is found. This way, you can avoid writing duplicate records into a Delta table1. The other commands (DROP, IGNORE, APPEND, INSERT) do not have this functionality and may result in duplicate records or data loss234. References: 1: Upsert into a Delta Lake table using merge | Databricks on AWS 2: SQL DELETE | Databricks on AWS 3: SQL INSERT INTO | Databricks on AWS 4: SQL UPDATE | Databricks on AWS

To grant full privileges on a table such as 'sales' to a group like 'team', the correct SQL command in Databricks is:

GRANT ALL PRIVILEGES ON TABLE sales TO team;

This command assigns all available privileges, including SELECT, INSERT, UPDATE, DELETE, and any other data manipulation or definition actions, to the specified team. This is typically necessary when a team needs full control over a table to manage and manipulate it as part of a project or ongoing maintenance.

References:Databricks documentation on SQL permissions: SQL Permissions in Databricks

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.

References:Databricks documentation on Delta Lake: Delta Lake on Databricks

Structured Streaming uses checkpointing and write-ahead logs to record the offset range of the data being processed in each trigger. This ensures that the engine can reliably track the exact progress of the processing and handle any kind of failure by restarting and/or reprocessing. Checkpointing is the mechanism of saving the state of a streaming query to fault-tolerant storage (such as HDFS) so that it can be recovered after a failure. Write-ahead logs are files that record the offset range of the data being processed in each trigger and are written to the checkpoint location before the processing starts. These logs are used to recover the query state and resume processing from the last processed offset range in case of a failure. References: Structured Streaming Programming Guide, Fault Tolerance Semantics

 Data Explorer is a graphical interface that allows you to browse, create, and manage data objects such as databases, tables, and views in your workspace. You can also review and modify the permissions on these data objects using Data Explorer. To access Data Explorer, you can click on the Data icon in the sidebar, or use the %sql magic command in a notebook. You can then select a database and a table, and click on the Permissions tab to view and edit the access control lists (ACLs) for the table. You can also use SQL commands such as SHOW GRANT and GRANT to query and modify the permissions on a Delta table. References:

• Data Explorer
• Access control for Delta tables
• SHOW GRANT
• [GRANT]

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 the review_period variable to a string value "True", which is different from the boolean value True. Option D is the correct answer, as it uses the == operator to compare the day_of_week variable to the integer value 1, and the and operator to check if both conditions are true. If both conditions are true, then the final block of the Python program will be executed. References: [Python Operators], [Python If … Else]

Cron syntax is a tool that can be used to represent and submit a complex run schedule programmatically. Cron syntax is a string of six fields that specify the frequency, date, and time of a job run. For example, the cron expression 0 0 12 * * ? means run the job at 12:00 PM every day. The data engineer can use the Databricks REST API to create or update a job with a cron schedule. The data engineer can also use the Databricks CLI to create or update a job with a cron schedule by using a JSON file that contains the cron expression. The other tools are either invalid or not suitable for representing and submitting a complex run schedule programmatically. References: Schedule a job, Jobs API, Databricks CLI, Cron expressions

The DELETE command in Delta Lake allows you to remove data that matches a predicate from a Delta table. This command will delete all the rows where the value in the column age is greater than 25 from the existing Delta table my_table and save the updated table. The other options are either incorrect or do not achieve the desired result. Option A will only select the rows that match the predicate, but not delete them. Option B will update the rows that match the predicate, but not delete them. Option D will update the rows that do not match the predicate, but not delete them. Option E will delete the rows that do not match the predicate, which is the opposite of what we want. References: Table deletes, updates, and merges — Delta Lake Documentation

 To grant full permissions on a table to a user or a group, you can use the GRANT ALL PRIVILEGES ON TABLE statement. This statement will grant all the possible privileges on the table, such as SELECT, CREATE, MODIFY, DROP, ALTER, etc. Option A is the only code block that follows this syntax correctly. Option B is incorrect, as it does not grant all the possible privileges on the table, but only a subset of them. Option C is incorrect, as it only grants the SELECT privilege on the table, which is not enough to fully manage the project. Option D is incorrect, as it grants the USAGE privilege on the table, which is not a valid privilege for tables. Option E is incorrect, as it grants all the privileges on the table team to the user or group sales, which is the opposite of what the question asks. References: Grant privileges on a table using SQL | Databricks on AWS, Grant privileges on a table using SQL - Azure Databricks, SQL Privileges - Databricks

A temporary view is a relational object that is defined in the metastore and points to an existing DataFrame. It does not copy or store any physical data, but only saves the query that defines the view. The lifetime of a temporary view is tied to the SparkSession that was used to create it, so it does not persist across different sessions or applications. A temporary view is useful for accessing the same data multiple times within the same notebook or session, without incurring additional storage costs. The other options are either materialized (A, E), persistent (B, C), or not relational objects ©. References: Databricks Documentation - Temporary View, Databricks Community - How do temp views actually work?, Databricks Community - What’s the difference between a Global view and a Temp view?, Big Data Programmers - Temporary View in Databricks.

 In a medallion architecture, a common data design pattern for lakehouses, data flows from Bronze to Silver to Gold layer tables, with each layer progressively improving the structure and quality of data. Bronze tables store raw data ingested from various sources, while Silver tables apply minimal transformations and cleansing to create an enterprise view of the data. Silver tables can also join and enrich data from different Bronze tables to provide a more complete and consistent view of the data. Therefore, option D is the correct answer, as Silver tables contain a more refined and cleaner view of data than Bronze tables. Option A is incorrect, as it is the opposite of the correct answer. Option B is incorrect, as Silver tables do not necessarily contain aggregates, but can also store detailed records. Option C is incorrect, as Silver tables may contain less data than Bronze tables, depending on the transformations and cleansing applied. Option E is incorrect, as Silver tables may contain more data than Bronze tables, depending on the joins and enrichments applied. References: What is a Medallion Architecture?, Transforming Bronze Tables in Silver Tables, What is the medallion lakehouse architecture?

The processingTime option specifies a time-based trigger interval for fixed interval micro-batches. This means that the query will execute a micro-batch to process data every 5 seconds, regardless of how much data is available. This option is suitable for near-real time processing workloads that require low latency and consistent processing frequency. The other options are either invalid syntax (A, C), default behavior (B), or experimental feature (E). References: Databricks Documentation - Configure Structured Streaming trigger intervals, Databricks Documentation - Trigger.

A data lakehouse is a data management architecture that combines the flexibility, cost-efficiency, and scale of data lakes with the data management and ACID transactions of data warehouses, enabling business intelligence (BI) and machine learning (ML) on all data12. One of the key features of a data lakehouse is that it supports ACID-compliant transactions, which means that it ensures data integrity, consistency, and isolation across concurrent read and write operations3. This feature results in improved data quality over a traditional data lake, which does not support transactions and may suffer from data corruption, duplication, or inconsistency due to concurrent or streaming data ingestion and processing . References: 1: What is a Data Lakehouse? - Databricks 2: What is a Data Lakehouse? Definition, features & benefits. - Qlik 3: ACID Transactions - Databricks : [Data Lake vs Data Warehouse: Key Differences] : [Data Lakehouse: The Future of Data Engineering]

Option D is the correct answer because it enables the Serverless feature for the SQL endpoint, which allows the endpoint to automatically scale up and down based on the query load. This way, the endpoint can handle more concurrent queries and reduce the time it takes to return results. The Serverless feature also reduces the cold start time of the endpoint, which is the time it takes to start the cluster when a query is submitted to a non-running endpoint. The Serverless feature is available for both AWS and Azure Databricks platforms.

References: Databricks SQL Serverless, Serverless SQL endpoints, New Performance Improvements in Databricks SQL

 The VACUUM command is used to remove files that are no longer referenced by a Delta table and are older than the retention threshold1. The default retention period is 7 days2, but it can be changed by setting the delta.logRetentionDuration and delta.deletedFileRetentionDuration configurations3. If the VACUUM command was run on the table with a retention period shorter than 3 days, then the data files that were needed to restore the table to a 3-day-old version would have been deleted. The other commands do not delete data files from the table. The TIME TRAVEL command is used to query a historical version of the table4. The DELETE HISTORY command is not a valid command in Delta Lake. The OPTIMIZE command is used to improve the performance of the table by compacting small files into larger ones5. The HISTORY command is used to retrieve information about the operations performed on the table. References: 1: VACUUM | Databricks on AWS 2: Work with Delta Lake table history | Databricks on AWS 3: [Delta Lake configuration | Databricks on AWS] 4: Work with Delta Lake table history - Azure Databricks 5: [OPTIMIZE | Databricks on AWS] : [HISTORY | Databricks on AWS]

The COPY INTO statement is an idempotent operation, which means that it will skip any files that have already been loaded into the target table1. This ensures that the data is not duplicated or corrupted by multiple attempts to load the same file. Therefore, if the data engineer runs the same command every day without specifying the names of the files to be copied with the FILES keyword or a glob pattern with the PATTERN keyword, the statement will only copy the first file that matches the source location and ignore the rest. To avoid this problem, the data engineer should either use the FILES or PATTERN keywords to filter the files to be copied based on the date or some other criteria, or delete the files from the source location after they are copied into the table2. References: 1: COPY INTO | Databricks on AWS 2: Get started using COPY INTO to load data | Databricks on AWS

Delta Lake is the optimized storage layer that provides the foundation for storing data and tables in the Databricks lakehouse. Delta Lake is open source software that extends Parquet data files with a file-based transaction log for ACID transactions and scalable metadata handling1. Delta Lake stores its data and metadata in a collection of files in a directory on a cloud storage system, such as AWS S3 or Azure Data Lake Storage2. Each Delta table has a transaction log that records the history of operations performed on the table, such as insert, update, delete, merge, etc. The transaction log also stores the schema and partitioning information of the table2. The transaction log enables Delta Lake to provide ACID guarantees, time travel, schema enforcement, and other features1. References:

• What is Delta Lake? | Databricks on AWS
• Quickstart — Delta Lake Documentation

The only option that is stored in the Databricks customer’s cloud account is data. Data is stored in the customer’s cloud storage service, such as AWS S3 or Azure Data Lake Storage. The customer has full control and ownership of their data and can access it directly from their cloud account.

Option A is not correct, as the Databricks web application is hosted and managed by Databricks on their own cloud infrastructure. The customer does not need to install or maintain the web application, but only needs to access it through a web browser.

Option B is not correct, as the cluster management metadata is stored and managed by Databricks on their own cloud infrastructure. The cluster management metadata includes information such as cluster configuration, status, logs, and metrics. The customer can view and manage their clusters through the Databricks web application, but does not have direct access to the cluster management metadata.

Option C is not correct, as the repos are stored and managed by Databricks on their own cloud infrastructure. Repos are version-controlled repositories that store code and data files for Databricks projects. The customer can create and manage their repos through the Databricks web application, but does not have direct access to the repos.

Option E is not correct, as the notebooks are stored and managed by Databricks on their own cloud infrastructure. Notebooks are interactive documents that contain code, text, and visualizations for Databricks workflows. The customer can create and manage their notebooks through the Databricks web application, but does not have direct access to the notebooks.

References:

• Databricks Architecture
• Databricks Data Sources
• Databricks Repos
• [Databricks Notebooks]
• [Databricks Data Engineer Professional Exam Guide]

 The spark.sql method can be used to execute SQL queries programmatically and return the result as a DataFrame. The spark.sql method accepts a string argument that contains a valid SQL statement. The data engineer can use a formatted string literal (f-string) to insert the Python variable table_name into the SQL query. The other methods are either invalid or not suitable for running SQL queries. References: Running SQL Queries Programmatically, Formatted string literals, spark.sql

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.

References:Databricks documentation on Auto Loader: Auto Loader Overview

 Delta Live Tables expectations are optional clauses that apply data quality checks on each record passing through a query. An expectation consists of a description, a boolean statement, and an action to take when a record fails the expectation. The ON VIOLATION clause specifies the action to take, which can be one of the following: warn, drop, or fail. The drop action means that invalid records are dropped from the target dataset before data is written to the target. The failure is reported as a metric for the dataset, which can be viewed by querying the Delta Live Tables event log. The event log contains information such as the number of records that violate an expectation, the number of records dropped, and the number of records written to the target dataset. References:

• Manage data quality with Delta Live Tables
• Monitor Delta Live Tables pipelines
• Delta Live Tables SQL language reference

 A data lakehouse is a data management architecture that combines the flexibility, cost-efficiency, and scale of data lakes with the data management and ACID transactions of data warehouses, enabling business intelligence (BI) and machine learning (ML) on all data12. By using a data lakehouse, both the data analysis and data engineering teams can access the same data sources and formats, ensuring data consistency and quality across their reports. A data lakehouse also supports schema enforcement and evolution, data validation, and time travel to old table versions, which can help resolve data conflicts and errors1. References: 1: What is a Data Lakehouse? - Databricks 2: What is a data lakehouse? | IBM

he approach that can be used to identify the owner of new_table is to review the Owner field in the table’s page in Data Explorer. Data Explorer is a web-based interface that allows users to browse, create, and manage data objects such as tables, views, and functions in Databricks1. The table’s page in Data Explorer provides various information about the table, such as its schema, partitions, statistics, history, and permissions2. The Owner field shows the name and email address of the user who created or owns the table3. The data engineer can use this information to contact the table owner and request for permission to access the table.

The other options are not correct or reliable for identifying the owner of new_table. Reviewing the Permissions tab in the table’s page in Data Explorer can show the users and groups who have access to the table, but not necessarily the owner4. Reviewing the Owner field in the table’s page in the cloud storage solution can be misleading, as the owner of the data files may not be the same as the owner of the table5. There is a way to identify the owner of the table, as explained above, so option E is false.

References:

• 1: Data Explorer | Databricks on AWS
• 2: Table details | Databricks on AWS
• 3: Set owner when creating a view in databricks sql - Databricks - 9978
• 4: Table access control | Databricks on AWS
• 5: External tables | Databricks on AWS

The correct command to create a new table that contains all records from two tables without duplicate records is to use the UNION operator. The UNION operator combines the results of two queries and removes any duplicate rows. The INNER JOIN, OUTER JOIN, and MERGE operators do not remove duplicate rows, and the INTERSECT operator only returns the rows that are common to both tables. Therefore, option B is the only correct answer. References: Databricks SQL Reference - UNION, Databricks SQL Reference - JOIN, Databricks SQL Reference - MERGE, [Databricks SQL Reference - INTERSECT]

JSON data is a text-based format that represents data as a collection of name-value pairs. By default, when Auto Loader infers the schema of JSON data, it treats all columns as strings. This is because JSON data can have varying data types for the same column across different files or records, and Auto Loader does not attempt to reconcile these differences. For example, a column named “age” may have integer values in some files, but string values in others. To avoid data loss or errors, Auto Loader infers the column as a string type. However, Auto Loader also provides an option to infer more precise column types based on the sample data. This option is called cloudFiles.inferColumnTypes and it can be set to true or false. When set to true, Auto Loader tries to infer the exact data types of the columns, such as integers, floats, booleans, or nested structures. When set to false, Auto Loader infers all columns as strings. The default value of this option is false. References: Configure schema inference and evolution in Auto Loader, Schema inference with auto loader (non-DLT and DLT), Using and Abusing Auto Loader’s Inferred Schema, Explicit path to data or a defined schema required for Auto loader.