Databricks-DA-Assoc Managing Data — All Questions With Answers

Question 1

A data engineering team has created a managed Delta table in Unity Catalog. Which TWO of the following statements accurately describe the characteristics and management of this managed table? (Choose two)

Accepted Answer

Dropping the managed table using a DROP TABLE statement automatically deletes the underlying data files from cloud storage.. Managed tables in Unity Catalog store both metadata and underlying data files within the metastore's designated root storage location. Unity Catalog fully manages the lifecycle of these files, meaning dropping the table deletes both the catalog metadata and the underlying Parquet and Delta log files automatically.

Answer

Managed tables require the user to explicitly define a custom LOCATION clause pointing to an external cloud storage path.

Answer

External tools cannot read managed table data files directly because Unity Catalog encrypts all underlying Parquet files at rest using proprietary keys.

Answer

Converting a managed table to an external table can be performed in-place using an ALTER TABLE RENTER statement.

Question 2

An analyst must grant read-only access to a Unity Catalog table `sales.orders` so that members of the group `analysts` can query it but cannot modify it. Which TWO actions accomplish this? (Choose two.)

Accepted Answer

Run `GRANT SELECT ON TABLE sales.orders TO analysts;` from a principal with sufficient privileges.. Read-only access requires the SELECT privilege, which can be granted either at the table level for a narrow scope or at the schema level to cover all tables within it. Both approaches grant only reading capability. Administrative roles and ownership transfer are far more permissive and would let the group modify or destroy data, so they cannot be the intended solution.

Answer

Run `ALTER TABLE sales.orders OWNER TO analysts;` so the group owns the table and can read it.

Answer

Add the group to the workspace admins role so they inherit implicit read access to all tables.

Answer

Run `GRANT USAGE ON TABLE sales.orders TO analysts;` from a principal with sufficient privileges.

Question 3

An analyst needs to make a Delta table `main.finance.transactions` readable by every member of the `finance_analysts` group while keeping write access restricted. The analyst also wants future columns added to the table to be readable without re-granting. Which TWO actions should the analyst take? (Choose two.)

Accepted Answer

Grant USE SCHEMA on main.finance to the finance_analysts group in addition to the table-level SELECT.. Unity Catalog requires layered privileges: USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table. Granting SELECT to the group covers all members and automatically applies to columns added later, so no re-grant is needed when the schema evolves. Granting ALL PRIVILEGES would breach the read-only intent, and schema-level SELECT or temporary views do not provide the required persistent, inherited access.

Answer

Create a temporary view over the table and grant SELECT on the temporary view to the finance_analysts group.

Answer

Grant SELECT on the parent schema main.finance so that all present and future tables in the schema are automatically readable.

Answer

Grant ALL PRIVILEGES on the table to the finance_analysts group so that reads and future operations are both covered.

Question 4

A data analyst needs to give a colleague read-only access to a single Delta table finance.transactions in Unity Catalog without granting access to any other table in the finance schema. The analyst has MANAGE privileges on the schema. Which grant should the analyst issue?

Accepted Answer

GRANT SELECT ON TABLE finance.transactions TO `colleague@example.com`;. Unity Catalog allows privileges to be granted at the table level, so SELECT on finance.transactions alone gives the colleague read access to that table only. Schema-level SELECT would expose every table in finance, USAGE alone does not permit reading data, and ALL PRIVILEGES grants more than read access. The table-scoped SELECT grant matches the least-privilege requirement.

Answer

GRANT USAGE ON SCHEMA finance TO `colleague@example.com`;

Answer

GRANT ALL PRIVILEGES ON TABLE finance.transactions TO `colleague@example.com`;

Answer

GRANT SELECT ON SCHEMA finance TO `colleague@example.com`;

Question 5

A data engineer has created a Delta table named `sales_summary` and needs to ensure that downstream analysts can only read rows where the `region` column matches their assigned territory. Which Databricks feature should be implemented to enforce this restriction securely at the row level?

Accepted Answer

Implement Unity Catalog row filters using a SQL function that evaluates the current user.. Row filters in Unity Catalog allow administrators and data owners to apply SQL-based filtering logic directly to tables, ensuring users only see authorized rows. This mechanism is critical for maintaining data governance, security, and compliance across diverse enterprise teams accessing shared datasets in Databricks.

Answer

Apply dynamic views using standard Spark SQL case statements that evaluate session user functions.

Answer

Grant SELECT privileges on filtered partitions using Unity Catalog folder-level access controls.

Answer

Configure cluster-level environment variables to filter data frames automatically upon session start.

Question 6

You are a data analyst working in Databricks SQL. Your workspace has Unity Catalog enabled. You need to inspect the metadata of a table named `customers` in the `sales` schema of the `retail` catalog, but you do not want to return any rows of data. Which SQL statement should you use?

Accepted Answer

DESCRIBE TABLE retail.sales.customers;. In Databricks SQL with Unity Catalog, DESCRIBE TABLE is the standard command to retrieve column names, types, and comments for a table, and it accepts the three-part namespace catalog.schema.table. The other commands either list table names, run a data query, or return a CREATE statement, none of which match the requirement to inspect column metadata without returning rows.

Answer

SELECT * FROM retail.sales.customers LIMIT 0;

Answer

SHOW CREATE TABLE retail.sales.customers;

Answer

SHOW TABLES IN retail.sales;

Question 7

A data analyst has a Delta table `events` in Unity Catalog and needs to see the history of operations performed on it, including which user ran each operation and when, in order to audit recent changes. Which command should the analyst run?

Accepted Answer

DESCRIBE HISTORY events;. Delta Lake records every transaction in its log, and DESCRIBE HISTORY exposes that log as a table with version, timestamp, operation, and user columns. This is the intended command for auditing who changed a table and when. The other options either list schema objects, describe storage metadata, or read data at a point in time, none of which provide the operation audit trail.

Answer

DESCRIBE DETAIL events;

Answer

SELECT * FROM events VERSION AS OF 0;

Answer

SHOW TABLES IN main.default;

Question 8

You are an analyst in a Unity Catalog-enabled Databricks workspace. A colleague has shared a table `finance.transactions` with you, and you need to confirm what privileges you currently hold on it before running a sensitive query. Which TWO of the following statements about inspecting privileges in Unity Catalog are accurate? (Choose two.)

Accepted Answer

You can run SHOW GRANTS ON TABLE finance.transactions to see the privileges granted on that specific table.. Unity Catalog uses a hierarchical privilege model, so inspecting a table involves both the direct grants on the table and the inherited grants from its catalog and schema. SHOW GRANTS ON TABLE reveals direct table grants, while effective access can come from higher levels. The other statements misstate the command's scope, required permissions, or inheritance behavior and would lead an analyst to wrong conclusions about their access.

Answer

You must be the table owner or a metastore admin to run SHOW GRANTS ON TABLE on a table you can query.

Answer

SHOW GRANTS ON TABLE finance.transactions will also list the privileges granted on the parent catalog and schema automatically.

Answer

SHOW GRANTS ON TABLE returns only the privileges of the current user and hides grants to other principals for privacy.

Question 9

An analyst needs to create a new table in a Unity Catalog schema to store aggregated results, and the table should be managed by Unity Catalog so that storage lifecycle and access are handled by the platform. Which SQL statement correctly creates a managed Delta table named `summary` in the `analytics` schema of the `reporting` catalog?

Accepted Answer

CREATE TABLE reporting.analytics.summary (id INT, total DOUBLE);. In Unity Catalog, a table created with CREATE TABLE and a three-part name but no LOCATION is managed by Unity Catalog, which controls its storage and lifecycle. External tables require a LOCATION and are not managed by the platform. The MANAGED keyword is not part of the syntax, and legacy dbfs paths are not the Unity Catalog managed pattern.

Answer

CREATE MANAGED TABLE reporting.analytics.summary (id INT, total DOUBLE);

Answer

CREATE EXTERNAL TABLE reporting.analytics.summary (id INT, total DOUBLE) LOCATION '/mnt/data/summary';

Answer

CREATE TABLE reporting.analytics.summary USING DELTA LOCATION 'dbfs:/user/hive/warehouse/summary';

Question 10

A data analyst runs the following command in a Databricks SQL editor connected to a Unity Catalog workspace:

```sql
DROP TABLE IF EXISTS analytics.events.raw_clicks;
```

What is the result of this statement if `analytics.events.raw_clicks` is a managed Delta table?

Accepted Answer

The table metadata, data files, and any associated directories are permanently removed from the metastore and cloud storage.. Dropping a managed Delta table in Unity Catalog removes both the catalog metadata and the underlying data files because Databricks owns the data lifecycle. External tables behave differently: their files persist. Analysts must therefore treat DROP on a managed table as destructive and irreversible through normal query tools, which is why knowing the table type before running DDL matters.

Answer

The table is renamed to a recycle-bin schema and can be restored by any workspace user within 30 days.

Answer

The command fails because Databricks SQL does not permit dropping tables that other users may be querying.

Answer

Only the table metadata is removed; the underlying Parquet data files remain in cloud storage for later recovery.

Question 11

A data analyst runs `DROP TABLE IF EXISTS main.default.customer_orders;` in a Databricks SQL warehouse. The table is a managed Delta table in Unity Catalog and the analyst's identity has no applicable owner or admin privileges. What happens?

Accepted Answer

The statement fails with an insufficient privileges error because DROP TABLE requires ownership of the table or MANAGE on the parent schema.. Removing a managed Unity Catalog table is a privileged DDL operation. The executing identity needs to be the table owner or hold MANAGE on the parent schema, along with USE CATALOG and USE SCHEMA on the enclosing objects. The IF EXISTS qualifier only prevents a not-found error; it does not relax authorization, so the drop is rejected before any data or metadata is touched.

Answer

The statement succeeds but the table is moved to a recycle bin schema where it can be restored for 30 days by any workspace user.

Answer

The statement succeeds and only the table's metadata is removed while the underlying Parquet data files remain in the managed storage location.

Answer

The statement succeeds because IF EXISTS implicitly converts the operation into a metadata-only soft delete that any schema user can perform.

Question 12

A data analyst runs the following SQL in a Databricks SQL warehouse:

CREATE OR REPLACE TEMPORARY VIEW monthly_sales AS
SELECT month, SUM(revenue) AS total_revenue
FROM sales.orders
GROUP BY month;

Immediately afterward, the analyst opens a new query tab in the same SQL warehouse session and runs SELECT * FROM monthly_sales; The query fails with a TABLE_OR_VIEW_NOT_FOUND error. What is the most likely reason for the failure?

Accepted Answer

The view was created as a temporary view, which is scoped to the notebook or session that created it, so a new query tab cannot resolve it.. Temporary views are bound to the session or notebook that creates them and are not stored in Unity Catalog. When the analyst opens a separate query tab, that new session has its own namespace and cannot resolve the temporary view, producing TABLE_OR_VIEW_NOT_FOUND. Persisting the view as a regular view or a managed table would make it accessible across sessions.

Answer

The view definition was persisted in Unity Catalog, but the second tab lacks USE CATALOG privileges on the default catalog.

Answer

The aggregated alias total_revenue conflicts with a reserved keyword, so the view was dropped automatically after creation.

Answer

The CREATE OR REPLACE TEMPORARY VIEW statement silently failed because the underlying sales.orders table lacks a primary key.

Question 13

An analyst wants to create a view named `main.sales.high_value` that returns only rows from `main.sales.orders` where `total_amount > 1000`, and wants the view to reflect future updates to the base table automatically. Which statement should the analyst run?

Accepted Answer

CREATE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;. Because the analyst wants results that always reflect the current state of the base table, a standard view is correct. A view persists only the query definition, and Databricks re-evaluates it on every read, so inserts or updates to the orders table appear immediately. Materialized views cache results and need refreshes, while temporary views vanish at session end, so neither matches the persistent, always-current requirement.

Answer

CREATE MATERIALIZED VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;

Answer

CREATE TEMPORARY VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;

Answer

CREATE OR REPLACE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000 WITH SCHEMA EVOLUTION;

Question 14

A data analyst has a Delta table silver.customers in Unity Catalog. A new privacy requirement states that analysts must never see the raw email column, but they still need to query all other columns. The analyst has CREATE VIEW and SELECT privileges on the schema. Which approach best enforces the requirement while keeping the table queryable?

Accepted Answer

Create a view that selects all columns except email, grant SELECT on the view to the analysts, and revoke SELECT on the base table.. Projecting only the safe columns into a view and revoking direct SELECT on the base table is the canonical least-privilege pattern in Unity Catalog. Analysts query the view, which returns every column except email, while the raw table remains inaccessible. Column masks or row filters do not remove the column from query results in the same unambiguous way.

Answer

Add a row filter to the table so rows containing emails are excluded for analysts, and keep base-table SELECT granted.

Answer

Rename the email column to a non-obvious name and rely on security by obscurity to hide it from analysts.

Answer

Apply a column mask on the email column using a masking function, and leave SELECT on the base table in place.

Question 15

An analyst has a Delta table `bronze.raw_events` that has accumulated many small files over months of streaming writes. They now need to optimize read performance for downstream dashboards. Which Databricks SQL command should they run?

Accepted Answer

`OPTIMIZE bronze.raw_events;`. The small-file problem from streaming writes is resolved by compaction, and OPTIMIZE performs exactly that by merging small files into larger ones. Statistics gathering and vacuuming address different concerns, and write-time properties only influence future writes rather than existing layout. Compaction is the correct maintenance action for improving scan performance on a fragmented Delta table.

Answer

`VACUUM bronze.raw_events;`

Answer

`ANALYZE TABLE bronze.raw_events COMPUTE STATISTICS;`

Answer

`ALTER TABLE bronze.raw_events SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');`

Question 16

A data analyst manages a Delta table gold.orders that is updated by an upstream job every 15 minutes. Analysts run long-running dashboard queries against the table and occasionally see stale results, even seconds after an update. The analyst wants dashboard queries to reflect the latest committed data without restarting the SQL warehouse. Which action should the analyst take?

Accepted Answer

Configure the session to use a fresh snapshot by disabling snapshot reuse, for example by setting the appropriate Spark configuration for the session.. Snapshot reuse within a Spark or Databricks SQL session caches the table's state for performance, which can cause readers to miss recent commits. Disabling snapshot reuse forces the session to resolve the current Delta version on each query, so dashboards reflect the latest committed data. Cache tuning and OPTIMIZE address performance, not snapshot freshness, and converting formats sacrifices Delta guarantees.

Answer

Run OPTIMIZE gold.orders after every upstream commit so the table is compacted and readers pick up new files.

Answer

Enable the Delta cache on the SQL warehouse and increase the cache size so recent files stay resident.

Answer

Convert the table to a Parquet table so queries always read the latest files directly from cloud storage.

Question 17

An analyst queries a view `main.ops.active_shipments` and receives a row-level filtered result, but the view's definition does not contain a WHERE clause. The analyst has SELECT on the view and on the base table. What most likely explains the filtered output?

Accepted Answer

A row filter function is attached to the base table, and the view inherits the filter because it reads the base table at query time.. Row filters in Unity Catalog are attached to tables and evaluated on every read, including reads performed through views. Since a standard view re-runs its definition against the base table, the base table's row filter silently restricts the rows returned, even though the view's own SQL has no WHERE clause. Column masks alter values, not row counts, and time travel returns different table versions rather than predicate-filtered subsets.

Answer

The analyst's group has a column mask applied to the view that removes rows where the masked column is null.

Answer

The view was created with a row filter using the CREATE VIEW ... WITH ROW FILTER syntax, but the filter text is hidden from SHOW CREATE TABLE output.

Answer

Delta Lake time travel is enabled on the view, so the analyst is reading an older snapshot that contains fewer rows.

Question 18

A data analyst queries a partitioned Delta table `logs.events` that has a `event_date` column. The query filters `WHERE event_date = '2024-06-01'`. Performance is poor even though the table is partitioned on `event_date`. Which factor most likely explains the poor performance?

Accepted Answer

The table has thousands of small files within the matching partition, so pruning selects the right partition but still reads excessive files.. Partition pruning only eliminates partitions that cannot match; it does not fix fragmentation inside a matching partition. If the correct partition contains many small files, the query reads all of them, incurring high overhead. The likely remedy is compaction with OPTIMIZE on that partition, possibly combined with ZORDER, rather than changing the partition column or the predicate form.

Answer

The `event_date` column is stored as a string while the predicate compares to a string literal, forcing a full scan due to implicit casting.

Answer

The table was partitioned by a high-cardinality column other than `event_date`, such as a unique event identifier.

Answer

The predicate is wrapped in a non-deterministic function such as `WHERE CAST(event_date AS DATE) = current_date()`, preventing partition pruning.

Question 19

A data analyst is preparing a Delta table in Unity Catalog for a dashboard that must return results quickly and consistently. The analyst needs to reduce the number of small files and improve data skipping on a frequently filtered column. (Choose two.)

Accepted Answer

Run OPTIMIZE on the table to compact small files into larger ones.. OPTIMIZE compacts small files into larger ones, reducing read overhead, while ZORDER BY co-locates related values so Delta can skip files using column statistics. Together they address both the small-file problem and the data-skipping requirement. VACUUM only deletes unreferenced files, views do not change storage layout, and larger clusters do not fix inefficient file organization.

Answer

Increase the SQL warehouse cluster size so more workers can scan small files in parallel.

Answer

Convert the table to a view so queries always compute results from the latest data.

Answer

Run VACUUM with a retention of zero hours to remove old files and speed up reads.

Question 20

An analyst has a CSV file at `abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv` and wants to query it directly from Databricks SQL without loading it into a Delta table. Which approach is appropriate?

Accepted Answer

Use a read_files table-valued function in a query, for example SELECT * FROM read_files('abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv', format => 'csv');. The read_files table-valued function is designed to query external files in place, returning a relation you can select from without creating a persistent object. It supports CSV and other formats and accepts options for headers and delimiters. COPY INTO writes into Delta tables, foreign tables are not a Databricks SQL construct, and a temporary view cannot select from a bare storage path without a file-reading function.

Answer

Create a foreign table with CREATE FOREIGN TABLE and query it with standard SQL.

Answer

Run COPY INTO with the file path as the target and then query the resulting table.

Answer

Create a temporary view with CREATE TEMPORARY VIEW csv_data AS SELECT * FROM 'abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv';