Practice Test #1

DROP removes database objects (tables, views, schemas) rather than writing data. It is destructive and used for cleanup or resetting environments, not for ingestion logic. It cannot be used to prevent duplicates during writes; it would instead delete the table or related objects, causing data loss and failing the intent of controlled, incremental loading.

IGNORE is not the primary Delta Lake command for writing data while preventing duplicates. While some SQL dialects support patterns like INSERT IGNORE, Delta Lake’s standard, exam-relevant approach for deduplicating writes is MERGE with a match condition. Relying on “ignore” semantics is not a typical or robust Delta ingestion strategy and is not the expected answer here.

APPEND writes new rows to the end of a Delta table (e.g., DataFrameWriter mode("append") or SQL INSERT INTO). It does not check for existing records and will create duplicates if the same data is written again. APPEND is appropriate for strictly additive datasets where duplicates are not a concern or are handled downstream.

INSERT adds rows into a table but, like APPEND, does not inherently prevent duplicates in Delta Lake because primary key constraints are not enforced by default. Without additional logic (such as MERGE conditions or pre-write deduplication), INSERT will write duplicate records if the incoming data overlaps with existing rows.

Databricks SQL endpoints (SQL warehouses) are optimized for serving SQL queries and BI workloads. They do not directly solve slow startup of Spark clusters used by Databricks Jobs with multiple tasks. Even though SQL warehouses can have warm-up behaviors, they are a different compute type and generally not applicable to Spark-based multi-task job clusters.

Job clusters are recommended for production Jobs because they are ephemeral, isolated per run, and reproducible (cluster config is tied to the job). However, job clusters are often created at run time, so they can still suffer from slow VM provisioning. Switching from all-purpose to job clusters is good practice, but it does not specifically improve cluster startup time like cluster pools do.

Single-node clusters can reduce resource provisioning needs and may start somewhat faster, but they are only appropriate for small workloads, development, or certain lightweight jobs. Many nightly pipelines require distributed compute for performance and reliability. This option is not the primary Databricks mechanism for improving startup time across multiple tasks and is not a general best practice for production jobs.

Autoscaling adjusts the number of workers based on workload demand, improving cost efficiency and potentially runtime performance for variable data sizes. It does not address the initial delay of provisioning and starting the cluster. A cluster can still take a long time to start even if autoscaling is enabled, so this is not the best action for improving startup time.

Incorrect because simply navigating to the Runs tab does not by itself allow immediate review of the processing notebook/task details. You must select the specific active run to access run-level and task-level diagnostics (timeline, logs, Spark UI). The Runs tab is the right area, but the action is incomplete for troubleshooting.

Incorrect because the Tasks tab is mainly for defining and configuring tasks in the job (task graph, parameters, dependencies). While you might navigate from job configuration to runs, the standard and most direct troubleshooting path for an in-progress execution is via the Runs tab, not Tasks.

Incorrect because Databricks provides multiple ways to investigate slow job tasks: run details, task logs, cluster event logs, and Spark UI (stages, jobs, SQL, storage, executors). These tools are specifically designed to diagnose performance issues such as skew, shuffle spill, insufficient resources, or inefficient queries.

Incorrect because the Tasks tab does not “immediately” provide runtime troubleshooting details for an active execution. It focuses on job design/configuration. To review why a task is slow in the current run, you need execution context (run page, task run page, Spark UI), which is accessed from Runs.

SELECT * FROM sales is a SQL statement, not a PySpark operation. By itself it doesn’t indicate how the Python test suite would execute the query and capture results as a DataFrame. In Databricks, you could run this SQL in a SQL cell, but the question asks specifically for an operation the engineering team can use in PySpark to run the query and work with the results.

spark.delta.table is not a standard SparkSession API. Delta Lake tables are accessed using Spark SQL (spark.sql) or DataFrame readers (spark.read.format("delta").load(path), spark.read.table(name), spark.table(name)). While Delta has a DeltaTable class in delta.tables (e.g., DeltaTable.forName/forPath), that is primarily for programmatic Delta operations (merge, update, delete), not for executing an arbitrary SQL query string.

This is incorrect: Databricks is built to share data and logic between SQL and PySpark. Both interfaces use the same underlying Spark engine and catalog, so a Delta table queried in SQL can be queried in PySpark and vice versa. You can execute SQL from Python using spark.sql, or register DataFrames as temp views to query them from SQL.

spark.table loads a table or view by name and returns it as a DataFrame, which is useful when you just need the full table (or will apply DataFrame transformations afterward). However, it does not execute an arbitrary SQL query provided by the analyst. If the analyst’s logic is expressed as a full SQL query, spark.sql is the appropriate operation to run it directly.

SELECT count(member_id) counts only non-NULL values in member_id. This returns the number of present (non-missing) member_id values, not the number of NULLs. It’s a frequent exam distractor because COUNT is associated with “counting values,” but in SQL semantics COUNT(column) explicitly excludes NULLs.

This expression is not valid/meaningful in Databricks SQL as written because count_null(member_id) is not a standard Spark SQL aggregate function. Even conceptually, subtracting a NULL-count from a non-NULL count would not yield the NULL count; you’d typically do COUNT(*) - COUNT(member_id) to get NULLs.

There is no SQL function null(member_id) that returns a count of NULLs. NULL is a literal value, and IS NULL is the operator used to test for NULL. This option confuses NULL handling with function invocation and does not perform aggregation or counting.

count_null(member_id) is not a standard function in Databricks SQL/Spark SQL. While some systems or custom libraries might offer similarly named helpers, the exam expects built-in Spark SQL/Databricks SQL behavior. The reliable built-in pattern is COUNT_IF(member_id IS NULL) (or COUNT(*) - COUNT(member_id)).

This option is incorrect because 'updated once and shut down' describes Triggered mode, not Continuous mode. In a continuous pipeline, DLT does not stop after processing the current backlog; it remains active and continues monitoring for new data. The statement about compute termination also does not fit Development mode, where resources are intended to remain available for iterative work. Therefore, both the execution behavior and cluster lifecycle are wrong.

This option is close on the continuous-update behavior but wrong on the reason and implication of compute persistence. The question explicitly states the pipeline is in Development mode, where compute persists to support additional testing and development workflows. Option B describes persistence generically and aligns more closely with a production-style continuously running job rather than emphasizing the development-mode behavior being tested. Since another option captures both Continuous mode and Development mode more precisely, B is not the best answer.

This option is incorrect because Continuous mode does not perform only a single update and then sit idle indefinitely. After processing existing unprocessed data, the pipeline remains active and continues to process new incoming data automatically. While there may be moments with no new data to process, the pipeline is still running and waiting for additional input. The 'updated once' portion makes this incompatible with Continuous mode.

This option incorrectly states that the pipeline will update once and then shut down, which is the hallmark of Triggered mode rather than Continuous mode. Although it mentions compute persisting for testing, that alone is not enough to make the option correct because the execution behavior is fundamentally wrong. In Development mode, compute may persist, but the pipeline itself continues running in Continuous mode. Therefore, this option mixes one correct idea with a critical error.

Incorrect. DLT is commonly used to implement medallion architectures (bronze/silver/gold). Multi-hop pipelines are a core DLT use case: each table can depend on upstream tables, and DLT manages ordering, incremental updates, and lineage. You may rename layers or refine definitions, but you do not need to abandon the medallion approach.

Incorrect. DLT pipelines do not need to be entirely in SQL. DLT supports defining tables in Python (using the DLT API) and in SQL (using CREATE LIVE TABLE / STREAMING LIVE TABLE). Many real pipelines mix languages: engineering-heavy ingestion/cleansing in Python and analytics modeling in SQL.

Incorrect. DLT supports streaming sources and streaming tables. You can read from streaming inputs (e.g., Auto Loader, Kafka) and process continuously or in triggered mode. DLT manages checkpoints and state for streaming transformations. There is no requirement to convert a streaming source into batch to use DLT.

Incorrect. DLT pipelines do not need to be entirely in Python. SQL is a first-class authoring option in DLT and is frequently used for gold-layer transformations and business logic. The key is that DLT manages the pipeline execution and dependencies regardless of whether the transformation logic is written in SQL or Python.

Athena query settings control operational details such as result location, encryption, engine behavior, and related execution preferences. These settings influence how queries or sessions behave after the environment is already available, but they do not create or enable Spark access. In other words, query settings are configuration details, not the feature that provides Athena for Apache Spark. Choosing this option confuses runtime tuning with the actual Spark execution capability.

An Athena data source identifies where Athena reads data from, such as Amazon S3, AWS Glue catalog metadata, or federated external systems. Data sources are about the origin of the data being queried, not about the compute engine used to process it. Apache Spark support in Athena is provided by Athena for Apache Spark, which is tied to workgroups rather than data source definitions. Therefore, a data source does not give the company the ability to use Spark to access Athena.

The Athena query editor is the console interface used to write and run SQL queries interactively. Although Athena also offers notebook-style experiences for Spark, the editor itself is not the enabling construct for Spark support. The underlying capability is provided through Athena for Apache Spark within a workgroup, not through the editor UI. This option focuses on the interface rather than the service feature that enables Spark execution.

A missing connection between QuickSight and Athena is primarily a configuration or data source setup issue, not necessarily a permissions issue. If the Athena data source were not configured, the dashboard would typically fail because the source is unavailable or misconfigured rather than because of insufficient permissions. The question specifically asks about factors that could cause permissions-related errors. Therefore this is less precise than missing IAM or S3 access.

If Athena tables are not cataloged, Athena usually returns metadata-related errors such as table not found or database not found. That situation indicates missing or incorrect schema registration in the Glue Data Catalog rather than an authorization failure. While catalog permissions can exist as a separate issue, the option as written refers to uncataloged tables, which is not inherently a permissions problem. So this is not one of the best answers.

Lack of permission to decrypt S3 data can absolutely cause access failures, but only when the S3 objects or Athena results are encrypted with a customer-managed KMS key or another encryption mode requiring explicit decrypt permissions. The question does not mention encryption at all, so this option depends on an unstated condition. In contrast, missing S3 access and missing QuickSight IAM role/service permissions are general causes that apply directly to the scenario as given. For exam purposes, conditional answers are usually weaker than universally required permissions.