Practice Test #4

SELECT * FROM my_table WHERE age > 25 returns only rows matching the condition in the query result, but it does not change the underlying Delta table. This is a common confusion between filtering data during retrieval versus modifying stored data. The table remains unchanged after the query completes, so it does not “remove rows” or “save the updated table.”

UPDATE my_table WHERE age > 25 is not valid SQL syntax for updating rows because UPDATE requires a SET clause (for example, UPDATE ... SET col = value WHERE ...). Even if corrected, UPDATE modifies values in existing rows rather than deleting them. Therefore it cannot be used to remove rows from the table.

UPDATE my_table WHERE age <= 25 is also invalid syntax due to the missing SET clause. More importantly, UPDATE changes data values rather than removing rows. Even if the intent were to keep only age <= 25, UPDATE cannot delete the other rows; you would still need DELETE with the opposite predicate or a rewrite operation.

DELETE FROM my_table WHERE age <= 25 is valid Delta Lake syntax, but it deletes the wrong set of rows. The question asks to remove rows where age > 25; this option would remove rows with age 25 or less, leaving the age > 25 rows behind. It’s a predicate-direction trap.

A Databricks account representative is not part of your workspace’s security boundary and does not administer your Unity Catalog objects. They can help with licensing, support escalation, and account-level guidance, but they do not log in to your environment to change table ownership. Exam pattern: vendor reps are almost never the correct choice for operational governance tasks.

This transfer is possible. Databricks governance is designed to handle offboarding and prevent orphaned data assets. Administrators can manage securables and reassign ownership or adjust privileges even when the original owner is no longer present. If you see “not possible,” it’s usually a distractor unless the question explicitly states there is no admin access at all.

The new lead data engineer may be the desired recipient of ownership, but they are not necessarily authorized to perform the transfer. Unless they also hold an administrative role (workspace admin/metastore admin) or have been delegated the necessary privileges, they cannot override current ownership. The question asks who must perform the transfer, which points to an admin.

The original data engineer cannot transfer ownership because the scenario states they no longer have access. Even if they were the owner, ownership transfer requires them to be able to authenticate and execute the change. This option is included to test whether you notice the constraint that the original owner is unavailable.

Unity Catalog provides governance, access control, and metadata management for data assets such as catalogs, schemas, tables, volumes, and external locations. It can secure and organize the source or target locations used by a pipeline, but it does not detect newly arrived files or maintain ingestion progress across runs. The requirement here is operational file discovery and incremental ingestion, not governance. Therefore, Unity Catalog is not the tool that solves this problem.

Delta Lake is a storage layer that adds ACID transactions, schema enforcement, and reliable table semantics on top of cloud object storage. It is commonly used as the destination format after ingestion, and Delta tables can be read incrementally once data is already loaded. However, Delta Lake does not itself watch a raw directory and determine which source files are new since the last pipeline run. That discovery and state-tracking function is handled by Auto Loader.

Databricks SQL is designed for querying, reporting, and analytics over data stored in Databricks-managed or external tables. While it can query files and tables, it does not provide a stateful ingestion mechanism that remembers which files were processed in prior runs. The problem specifically requires incremental file ingestion from an accumulating directory, which is outside the core purpose of Databricks SQL. As a result, it is not the best tool for this use case.

Data Explorer is a UI feature for browsing and inspecting catalogs, schemas, tables, volumes, and related metadata in Databricks. It helps users discover and understand available data assets, but it does not execute ingestion pipelines or track processed files over time. There is no checkpointing or file-notification capability in Data Explorer for incremental loading. It is an exploration interface, not an ingestion solution.

TRANSFORM in Spark SQL is commonly a higher-order function used with arrays (and sometimes maps) to apply an expression to each element and return a new array. It does not convert a relational table from long format to wide format. Because the word “transform” sounds like a general reshaping operation, it can be tempting, but it is not the pivoting keyword in Databricks SQL.

SUM is an aggregation function used to add numeric values within groups. While SUM is frequently used inside a PIVOT clause (e.g., SUM(amount)), it cannot by itself create new columns from row values. It changes the number of rows via aggregation but does not perform the structural reshaping from long to wide without PIVOT.

CONVERT is generally associated with data type conversion in some SQL dialects (e.g., converting strings to dates) or formatting. It is not used to reshape relational data from long to wide. In Databricks SQL, casting is typically done with CAST(expr AS type) rather than a CONVERT keyword for pivot-like transformations.

WHERE filters rows based on a predicate. It can reduce the dataset before pivoting, but it does not change the orientation of data (rows to columns). WHERE is part of selection and filtering, not reshaping. On exam questions about long-to-wide conversion, WHERE is a distractor because it is a common SQL keyword but unrelated to pivoting.

Incorrect. The "Depends on" field does not replace an existing task with a new one; it only defines execution order between tasks. If a task needs to be replaced, the job definition itself must be edited to remove or modify the old task. Dependencies are about orchestration and prerequisites, not substitution of workflow components. Choosing this option confuses workflow design changes with runtime dependency configuration.

Incorrect. Standard task dependencies in Databricks Jobs are used to wait for successful completion of upstream tasks, not for upstream failure. A failed upstream task typically prevents dependent tasks from running unless special control-flow or repair logic is used. Failure scenarios are handled through retries, alerts, and operational remediation rather than by setting a dependency that requires failure. Therefore this does not describe the normal purpose of the "Depends on" field.

Incorrect. Having the same dependency libraries does not require one task to depend on another. Library configuration is managed separately at the task or cluster level and does not imply any execution ordering. Two tasks can use identical libraries and still run independently or in parallel if no data dependency exists. This option confuses environment setup with workflow orchestration.

Incorrect. The purpose of "Depends on" is not to minimize compute resource usage, even though serialized execution may incidentally reduce concurrent resource consumption. Compute efficiency is addressed through cluster sizing, autoscaling, serverless options, and job design choices rather than dependency configuration alone. A dependency should be added because of logical or data prerequisites, not simply to reduce resource usage. This option mistakes an indirect side effect for the primary function of the feature.

This is not the best answer because the most direct way to identify the table owner is the explicit Owner field on the table’s page in Data Explorer. However, the Permissions tab can also help identify the owner because the owner is the principal with the OWN privilege on the table. On the exam, Databricks typically expects the most straightforward metadata location, which is the Owner field rather than inferring ownership from the permissions listing.

Not all listed options can identify the owner. The cloud storage solution does not track Unity Catalog table ownership, and the Permissions tab is not the canonical “owner” indicator compared to the explicit Owner field. Therefore, it is incorrect to say all options work.

Cloud storage ownership/ACLs (S3/ADLS/GCS) do not represent the Databricks/Unity Catalog table owner. A table can be managed by Databricks (managed table) or point to external storage (external table), but in both cases the table owner is a metastore concept. Looking in the storage solution will not reliably identify the table owner.

This is incorrect. Databricks provides multiple ways to identify ownership, including the Owner field in Data Explorer and metadata/SQL commands. Ownership is a key governance attribute and is intended to be discoverable for access requests and administration.

WebSocket connections are not a supported or typical interface for Amazon Redshift query execution. Redshift is accessed via SQL over JDBC/ODBC or via the Redshift Data API (HTTPS). Using WebSockets would require custom middleware/proxies and would add significant complexity and operational overhead, failing the “least operational overhead” requirement.

JDBC connections can run real-time queries, but they require the application to manage database drivers, credentials, network connectivity (often VPC configuration), connection pooling, retries, and scaling under concurrent web traffic. This increases operational overhead compared to using the Redshift Data API, which abstracts away connection management and simplifies authentication via IAM.

S3 Select queries data stored in Amazon S3 objects (CSV/JSON/Parquet) and cannot directly query data that resides in Amazon Redshift. To use S3 Select, the company would need to export/replicate frequently accessed Redshift data to S3 and manage freshness, partitioning, and consistency—adding operational overhead and risking stale results for a real-time trading application.

This relies on Firehose writing objects to S3, then S3 event notifications triggering Lambda, then writing to Aurora, then visualizing in QuickSight. Firehose buffering plus object creation events introduce micro-batch latency, and Aurora is not optimized as a time-series sink for high-ingest sensor metrics. QuickSight is typically not used for second-level operational wallboards, increasing effective latency.

Although Flink is low-latency, the option’s key issue is the claim of using a Kinesis Data Firehose delivery stream to publish directly to Timestream (not a standard/typical native Firehose destination in many exam contexts). Additionally, QuickSight generally has higher refresh latency than Grafana for operational monitoring. Even with Timestream, the visualization layer makes this less suitable for lowest-latency display.

AWS Glue bookmarks are intended for incremental/batch ETL processing, not true real-time streaming from S3. Reading “in real time” from S3 is constrained by object delivery and listing/polling patterns, and bookmarks don’t change that. This approach would have higher latency than consuming directly from Kinesis Data Streams with Flink, and it adds unnecessary ETL overhead.

A Lambda-based diff approach is not cost-effective or scalable for tens-of-terabytes daily snapshots. Lambda has execution time and memory constraints, and implementing a robust distributed comparison would require chunking, orchestration, and large S3 read volumes. It also lacks built-in ACID/merge semantics, so you would be re-implementing CDC logic and state management at high operational complexity and cost.

Loading snapshot files into Amazon RDS for MySQL to derive changes is expensive and not aligned with the source pattern. RDS is not designed for ingesting and comparing tens of TB daily snapshots, and AWS DMS CDC works best with database transaction logs, not file-to-file snapshot comparisons. This adds unnecessary database licensing/instance costs and operational overhead.

Aurora Serverless reduces some capacity management, but it still introduces an OLTP database layer that is ill-suited for ingesting and comparing massive daily snapshot files. AWS DMS is primarily for log-based CDC from databases, not for computing deltas from file snapshots. The approach increases cost and complexity without providing an efficient mechanism to detect changes from JSON snapshots at scale.