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

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.

Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as cloning, committing, pushing, pulling, and branch management. Databricks Notebooks versioning is a legacy feature that allows users to link notebooks to GitHub repositories and perform basic Git operations. However, Databricks Notebooks versioning does not support the use of multiple branches for development work, which is an advantage of using Databricks Repos. With Databricks Repos, users can create and manage branches for different features, experiments, or bug fixes, and merge, rebase, or resolve conflicts between them. Databricks recommends using a separate branch for each notebook and following data science and engineering code development best practices using Git for version control, collaboration, and CI/CD. References: Git integration with Databricks Repos - Azure Databricks | Microsoft Learn, Git version control for notebooks (legacy) | Databricks on AWS, Databricks Repos Is Now Generally Available - New ‘Files’ Feature in …, Databricks Repos - What it is and how we can use it | Adatis.

 Databricks SQL allows users to schedule queries to run automatically at a specified frequency and time zone. This can help users to keep their dashboards or alerts updated with the latest data. To schedule a query, users need to do the following steps:

In the Query Editor, click Schedule > Add schedule to open a menu with schedule settings.

Choose when to run the query. Use the dropdown pickers to specify the frequency, period, starting time, and time zone. Optionally, select the Show cron syntax checkbox to edit the schedule in Quartz Cron Syntax.

Choose More options to show optional settings. Users can also choose a name for the schedule, and a SQL warehouse to power the query.

Click Create. The query will run automatically according to the schedule.

The other options are incorrect because they do not refer to the correct location or frequency to schedule the query. The query’s page in Databricks SQL is the place where users can edit, run, or schedule the query. The SQL endpoint’s page in Databricks SQL is the place where users can manage the SQL warehouses and SQL endpoints. The Jobs UI is the place where users can create, run, or schedule jobs that execute notebooks, JARs, or Python scripts. References: Schedule a query, What are Databricks SQL alerts?, Jobs.

The best practice is to use "Complete" as output mode instead of "append" when working with aggregated tables. Since gold layer is work final aggregated tables, the only option with output mode as complete is option E.

https://spark.apache.org/docs/latest/api/python/reference/pyspark.ss/api/pyspark.sql.streaming.DataStreamWriter.trigger.html

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 expected behavior when a batch of data containing data that violates the expectation is processed is that the job will fail. This is because the expectation clause has the ON VIOLATION FAIL UPDATE option, which means that if any record in the batch does not meet the expectation, the entire batch will be rejected and the job will fail. This option is useful for enforcing strict data quality rules and preventing invalid data from entering the target dataset.

Option A is not correct, as the ON VIOLATION FAIL UPDATE option does not drop the records that violate the expectation, but fails the entire batch. To drop the records that violate the expectation and record them as invalid in the event log, the ON VIOLATION DROP RECORD option should be used.

Option C is not correct, as the ON VIOLATION FAIL UPDATE option does not drop the records that violate the expectation, but fails the entire batch. To drop the records that violate the expectation and load them into a quarantine table, the ON VIOLATION QUARANTINE RECORD option should be used.

Option D is not correct, as the ON VIOLATION FAIL UPDATE option does not add the records that violate the expectation, but fails the entire batch. To add the records that violate the expectation and record them as invalid in the event log, the ON VIOLATION LOG RECORD option should be used.

Option E is not correct, as the ON VIOLATION FAIL UPDATE option does not add the records that violate the expectation, but fails the entire batch. To add the records that violate the expectation and flag them as invalid in a field added to the target dataset, the ON VIOLATION FLAG RECORD option should be used.