Get The Most Updated Databricks-Certified-Professional-Data-Engineer Dumps To Databricks Certification Certification [Q33-Q55]

Q33. When building a DLT s pipeline you have two options to create a live tables, what is the main dif-ference between CREATE STREAMING LIVE TABLE vs CREATE LIVE TABLE?

CREATE STREAMING LIVE table is used in MULTI HOP Architecture

CREATE LIVE TABLE is used when working with Streaming data sources and Incremental data

CREATE STREAMING LIVE TABLE is used when working with Streaming data sources and Incremental data

There is no difference both are the same, CREATE STRAMING LIVE will be deprecated soon

CREATE LIVE TABLE is used in DELTA LIVE TABLES, CREATE STREAMING LIVE can only used in Structured Streaming applications

Q34. You are currently asked to work on building a data pipeline, you have noticed that you are currently working on a very large scale ETL many data dependencies, which of the following tools can be used to address this problem?

AUTO LOADER

JOBS and TASKS

SQL Endpoints

DELTA LIVE TABLES

STRUCTURED STREAMING with MULTI HOP

Q35. Your colleague was walking you through how a job was setup, but you noticed a warning message that said,
“Jobs running on all-purpose cluster are considered all purpose compute”, the colleague was not sure why he was getting the warning message, how do you best explain this warning mes-sage?

All-purpose clusters cannot be used for Job clusters, due to performance issues.

All-purpose clusters take longer to start the cluster vs a job cluster

All-purpose clusters are less expensive than the job clusters

All-purpose clusters are more expensive than the job clusters

All-purpose cluster provide interactive messages that can not be viewed in a job

Q36. What is the type of table created when you issue SQL DDL command CREATE TABLE sales (id int, units int)

Query fails due to missing location

Query fails due to missing format

Managed Delta table

External Table

Managed Parquet table

Q37. The below spark command is looking to create a summary table based customerId and the number of times the customerId is present in the event_log delta table and write a one-time micro-batch to a summary table, fill in the blanks to complete the query.
1.spark._________
2. .format(“delta”)
3. .table(“events_log”)
4. .groupBy(“customerId”)
5. .count()
6. ._______
7. .format(“delta”)
8. .outputMode(“complete”)
9. .option(“checkpointLocation”, “/tmp/delta/eventsByCustomer/_checkpoints/”)
10. .trigger(______)
11. .table(“target_table”)

writeStream, readStream, once

readStream, writeStream, once

writeStream, processingTime = once

writeStream, readStream, once = True

readStream, writeStream, once = True

Q38. How do you check the location of an existing schema in Delta Lake?

Run SQL command SHOW LOCATION schema_name

Check unity catalog UI

Use Data explorer

Run SQL command DESCRIBE SCHEMA EXTENDED schema_name
E Schemas are internally in-store external hive meta stores like MySQL or SQL Server

Q39. How do you access or use tables in the unity catalog?

schema_name.table_name

schema_name.catalog_name.table_name

catalog_name.table_name

catalog_name.database_name.schema_name.table_name

catalog_name.schema_name.table_name

Q40. Which of the following is the correct statement for a session scoped temporary view?

Temporary views are lost once the notebook is detached and re-attached

Temporary views stored in memory

Temporary views can be still accessed even if the notebook is detached and attached

Temporary views can be still accessed even if cluster is restarted

Temporary views are created in local_temp database

Q41. You have noticed that Databricks SQL queries are running slow, you are asked to look reason why queries are running slow and identify steps to improve the performance, when you looked at the issue you noticed all the queries are running in parallel and using a SQL endpoint(SQL Warehouse) with a single cluster. Which of the following steps can be taken to improve the performance/response times of the queries?
*Please note Databricks recently renamed SQL endpoint to SQL warehouse.

They can turn on the Serverless feature for the SQL endpoint(SQL warehouse).

They can increase the maximum bound of the SQL endpoint(SQL warehouse)’s scaling range

They can increase the warehouse size from 2X-Smal to 4XLarge of the SQL end-point(SQL warehouse).

They can turn on the Auto Stop feature for the SQL endpoint(SQL warehouse).

They can turn on the Serverless feature for the SQL endpoint(SQL warehouse) and change the Spot Instance Policy to “Reliability Optimized.”

Q42. You are tasked to set up a set notebook as a job for six departments and each department can run the task parallelly, the notebook takes an input parameter dept number to process the data by department, how do you go about to setup this up in job?

Use a single notebook as task in the job and use dbutils.notebook.run to run each note-book with parameter in a different cell

A task in the job cannot take an input parameter, create six notebooks with hardcoded dept number and setup six tasks with linear dependency in the job

A task accepts key-value pair parameters, creates six tasks pass department number as parameter foreach task with no dependency in the job as they can all run in parallel.
(Correct)

A parameter can only be passed at the job level, create six jobs pass department number to each job with linear job dependency

A parameter can only be passed at the job level, create six jobs pass department number to each job with no job dependency

Q43. What is the main difference between AUTO LOADER and COPY INTO?

COPY INTO supports schema evolution.

AUTO LOADER supports schema evolution.

COPY INTO supports file notification when performing incremental loads.

AUTO LOADER supports reading data from Apache Kafka

AUTO LOADER Supports file notification when performing incremental loads.

Explanation
Auto loader supports both directory listing and file notification but COPY INTO only supports di-rectory listing.
Auto loader file notification will automatically set up a notification service and queue service that subscribe to file events from the input directory in cloud object storage like Azure blob storage or S3. File notification mode is more performant and scalable for large input directories or a high volume of files.

Auto Loader and Cloud Storage Integration
Auto Loader supports a couple of ways to ingest data incrementally
1.Directory listing – List Directory and maintain the state in RocksDB, supports incremental file listing
2.File notification – Uses a trigger+queue to store the file notification which can be later used to retrieve the file, unlike Directory listing File notification can scale up to millions of files per day.
[OPTIONAL]
Auto Loader vs COPY INTO?
Auto Loader
Auto Loader incrementally and efficiently processes new data files as they arrive in cloud storage without any additional setup. Auto Loader provides a new Structured Streaming source called cloudFiles. Given an input directory path on the cloud file storage, the cloudFiles source automatically processes new files as they arrive, with the option of also processing existing files in that directory.
When to use Auto Loader instead of the COPY INTO?
*You want to load data from a file location that contains files in the order of millions or higher. Auto Loader can discover files more efficiently than the COPY INTO SQL command and can split file processing into multiple batches.
*You do not plan to load subsets of previously uploaded files. With Auto Loader, it can be more difficult to reprocess subsets of files. However, you can use the COPY INTO SQL command to reload subsets of files while an Auto Loader stream is simultaneously running.
Auto loader file notification will automatically set up a notification service and queue service that subscribe to file events from the input directory in cloud object storage like Azure blob storage or S3. File notification mode is more performant and scalable for large input directories or a high volume of files.
Here are some additional notes on when to use COPY INTO vs Auto Loader
When to use COPY INTO
https://docs.databricks.com/delta/delta-ingest.html#copy-into-sql-command When to use Auto Loader
https://docs.databricks.com/delta/delta-ingest.html#auto-loader

Q44. Which statement regarding stream-static joins and static Delta tables is correct?

Each microbatch of a stream-static join will use the most recent version of the static Delta table as of each microbatch.

Each microbatch of a stream-static join will use the most recent version of the static Delta table as of the job’s initialization.

The checkpoint directory will be used to track state information for the unique keys present in the join.

Stream-static joins cannot use static Delta tables because of consistency issues.

The checkpoint directory will be used to track updates to the static Delta table.

Q45. Which of the following scenarios is the best fit for the AUTO LOADER solution?

Efficiently process new data incrementally from cloud object storage

Incrementally process new streaming data from Apache Kafa into delta lake

Incrementally process new data from relational databases like MySQL

Efficiently copy data from data lake location to another data lake location

Efficiently move data incrementally from one delta table to another delta table

Explanation
The answer is, Efficiently process new data incrementally from cloud object storage.
Please note: AUTO LOADER only works on data/files located in cloud object storage like S3 or Azure Blob Storage it does not have the ability to read other data sources, although AU-TO LOADER is built on top of structured streaming it only supports files in the cloud object stor-age. If you want to use Apache Kafka then you can just use structured streaming.
Diagram Description automatically generated

Auto Loader and Cloud Storage Integration
Auto Loader supports a couple of ways to ingest data incrementally
1.Directory listing – List Directory and maintain the state in RocksDB, supports incremental file listing
2.File notification – Uses a trigger+queue to store the file notification which can be later used to retrieve the file, unlike Directory listing File notification can scale up to millions of files per day.
[OPTIONAL]
Auto Loader vs COPY INTO?
Auto Loader
Auto Loader incrementally and efficiently processes new data files as they arrive in cloud storage without any additional setup. Auto Loader provides a new Structured Streaming source called cloudFiles. Given an input directory path on the cloud file storage, the cloudFiles source automatically processes new files as they arrive, with the option of also processing existing files in that directory.
When to use Auto Loader instead of the COPY INTO?
*You want to load data from a file location that contains files in the order of millions or higher. Auto Loader can discover files more efficiently than the COPY INTO SQL command and can split file processing into multiple batches.
*You do not plan to load subsets of previously uploaded files. With Auto Loader, it can be more difficult to reprocess subsets of files. However, you can use the COPY INTO SQL command to reload subsets of files while an Auto Loader stream is simultaneously running.
Refer to more documentation here,
https://docs.microsoft.com/en-us/azure/databricks/ingestion/auto-loader

Q46. When using the complete mode to write stream data, how does it impact the target table?

Entire stream waits for complete data to write

Stream must complete to write the data

Target table cannot be updated while stream is pending

Target table is overwritten for each batch

Delta commits transaction once the stream is stopped

Q47. You have noticed the Data scientist team is using the notebook versioning feature with git integra-tion, you have recommended them to switch to using Databricks Repos, which of the below reasons could be the reason the why the team needs to switch to Databricks Repos.

Databricks Repos allows multiple users to make changes

Databricks Repos allows merge and conflict resolution

Databricks Repos has a built-in version control system

Databricks Repos automatically saves changes

Databricks Repos allow you to add comments and select the changes you want to commit.

Q48. You are working on a dashboard that takes a long time to load in the browser, due to the fact that each visualization contains a lot of data to populate, which of the following approaches can be taken to address this issue?

Increase size of the SQL endpoint cluster

Increase the scale of maximum range of SQL endpoint cluster

Use Databricks SQL Query filter to limit the amount of data in each visualization

Remove data from Delta Lake

Use Delta cache to store the intermediate results

Q49. Which of the statements are correct about lakehouse?

Lakehouse only supports Machine learning workloads and Data warehouses support BI workloads

Lakehouse only supports end-to-end streaming workloads and Data warehouses support Batch workloads

Lakehouse does not support ACID

In Lakehouse Storage and compute are coupled

Lakehouse supports schema enforcement and evolution

Q50. What is the purpose of gold layer in Multi hop architecture?

Optimizes ETL throughput and analytic query performance

Eliminate duplicate records

Preserves grain of original data, without any aggregations

Data quality checks and schema enforcement

Optimized query performance for business-critical data

Q51. You have accidentally deleted records from a table called transactions, what is the easiest way to restore the records deleted or the previous state of the table? Prior to deleting the version of the table is 3 and after delete the version of the table is 4.

RESTORE TABLE transactions FROM VERSION as of 4

RESTORE TABLE transactions TO VERSION as of 3
C .
1.INSERT INTO OVERWRITE transactions
2.SELECT * FROM transactions VERSION AS OF 3
3.MINUS
4.SELECT * FROM transactions

1.INSERT INTO OVERWRITE transactions
2.SELECT * FROM transactions VERSION AS OF 4
3.INTERSECT
4.SELECT * FROM transactions

COPY OVERWRITE transactions from VERSION as of 3

Q52. You are currently working on a project that requires the use of SQL and Python in a given note-book, what would be your approach

Create two separate notebooks, one for SQL and the second for Python

A single notebook can support multiple languages, use the magic command to switch between the two.

Use an All-purpose cluster for python, SQL endpoint for SQL

Use job cluster to run python and SQL Endpoint for SQL

Q53. A Delta Live Table pipeline includes two datasets defined using STREAMING LIVE TABLE.
Three datasets are defined against Delta Lake table sources using LIVE TABLE . The table is configured to
run in Development mode using the Triggered Pipeline Mode.
Assuming previously unprocessed data exists and all definitions are valid, what is the expected outcome after
clicking Start to update the pipeline?

All datasets will be updated once and the pipeline will shut down. The compute resources will persist to
allow for additional testing

All datasets will be updated once and the pipeline will shut down. The compute resources will be
terminated

All datasets will be updated continuously and the pipeline will not shut down. The compute resources
will persist with the pipeline

All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will
persist after the pipeline is stopped to allow for additional testing

All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will
be deployed for the update and terminated when the pipeline is stopped

Q54. The security team is exploring whether or not the Databricks secrets module can be leveraged for connecting to an external database.
After testing the code with all Python variables being defined with strings, they upload the password to the secrets module and configure the correct permissions for the currently active user. They then modify their code to the following (leaving all other variables unchanged).

Which statement describes what will happen when the above code is executed?

The connection to the external table will fail; the string “redacted” will be printed.

An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the encoded password will be saved to DBFS.

An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the password will be printed in plain text.

The connection to the external table will succeed; the string value of password will be printed in plain text.

The connection to the external table will succeed; the string “redacted” will be printed.

Q55. The data engineering team is using a bunch of SQL queries to review data quality and monitor the ETL job every day, which of the following approaches can be used to set up a schedule and auto-mate this process?

They can schedule the query to run every 1 day from the Jobs UI

They can schedule the query to refresh every 1 day from the query’s page in Databricks SQL.

They can schedule the query to run every 12 hours from the Jobs UI.

They can schedule the query to refresh every 1 day from the SQL endpoint’s page in Databricks SQL.

They can schedule the query to refresh every 12 hours from the SQL endpoint’s page in Databricks SQL