Managing Data practice questions

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

Why it fails: Managed tables store data in the Unity Catalog managed storage location, so no LOCATION clause is supplied; specifying one creates an external table instead. The LOCATION clause is required precisely when registering external tables over existing cloud storage paths.

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

Why it fails: Unity Catalog does not encrypt managed table Parquet files with proprietary keys; external engines can read the underlying files directly. This would be correct only if a proprietary encryption layer existed, which it does not.

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

Why it fails: You cannot alter a managed table into an external table in place using a simple rename or alter command; the data must be physically copied or recreated using distinct CREATE TABLE statements with an explicit location.

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

Why it fails: Transferring ownership gives the group full control over the table, including the ability to drop, alter, and re-grant permissions. That is the opposite of read-only access. Ownership is a powerful administrative state, not a read privilege, so using ALTER TABLE OWNER TO would over-privilege the analysts and fail the stated requirement to prevent modification.

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

Why it fails: Workspace admins have broad administrative capabilities far beyond read access, including the ability to manage users, clusters, and permissions. Making analysts admins violates the principle of least privilege and grants excessive rights that the scenario explicitly wants to avoid. This is an inappropriate and unsafe way to provide read-only table access to a group of analysts.

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

Why it fails: USAGE is not a valid table-level privilege in Unity Catalog. USAGE applies to securable objects like catalogs and schemas, where it permits navigation into the object. Granting USAGE on a table would not produce the intended read access and is not the mechanism used to let a group query rows, so it does not fulfill the requirement here.

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

Why it fails: Temporary views are session-scoped and cannot be granted to other users or groups, nor do they persist beyond the creating session. They are unsuitable for sharing data with a group. A persistent view could be shared, but the scenario asks for direct table access and the temporary view approach cannot be authorized.

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

Why it fails: Unity Catalog does not support granting SELECT at the schema level to cascade to tables; SELECT applies to tables, views, and functions individually. A schema-level grant of SELECT is not a valid inheritance mechanism here. The group would still lack table-level SELECT on transactions, so reads would fail.

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

Why it fails: ALL PRIVILEGES includes MODIFY, which would give the group write access and violate the requirement to keep writes restricted. It is broader than necessary and harder to audit. Least privilege calls for granting only SELECT, since the scenario explicitly wants read-only access for the group.

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

Why it fails: USAGE on a schema allows the grantee to interact with the schema namespace but does not by itself grant SELECT on tables within it. The colleague would still be unable to read finance.transactions. This grant is necessary for navigation but insufficient for the read access the scenario requires.

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

Why it fails: ALL PRIVILEGES includes modify and manage capabilities, not just read. The requirement is read-only access, so granting all privileges violates least privilege by allowing the colleague to alter or drop the table. A targeted SELECT grant is the correct minimal privilege for read-only consumption.

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

Why it fails: Granting SELECT on the schema gives the colleague read access to every table in the finance schema, which exceeds the requirement. Unity Catalog schema-level grants are broad and would expose all tables, not just transactions. The scenario explicitly restricts access to one table, so a schema-wide grant is inappropriate.

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

Why it fails: Dynamic views require creating an entirely separate view object and can be bypassed if users query the underlying base table directly. Unity Catalog row filters provide a robust, unified security layer directly on the table itself without duplicating storage.

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

Why it fails: Unity Catalog folder-level grants control access to storage paths and tables, not individual rows, so they cannot filter on the region column. This is tempting because Unity Catalog does govern data access, but row filtering requires row filter functions or dynamic views, which evaluate predicates per user.

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

Why it fails: Cluster environment variables affect the entire compute resource and can be easily bypassed by switching clusters or running direct SQL queries. Security policies must be enforced at the data governance layer rather than trusting compute configurations.

SELECT * FROM retail.sales.customers LIMIT 0;

Why it fails: SELECT * with LIMIT 0 returns an empty result set with the column headers, but it still requires the query engine to resolve and plan against the table, and it does not produce the structured metadata output that DESCRIBE provides. It also is a data query rather than a metadata command, which is not the intended tool.

SHOW CREATE TABLE retail.sales.customers;

Why it fails: SHOW CREATE TABLE returns the DDL statement used to create the table, including column definitions and table properties. While it does expose schema information, it is a different command than DESCRIBE and returns the full CREATE statement rather than the tabular column listing. The scenario asks for metadata inspection in the standard column-list form.

SHOW TABLES IN retail.sales;

Why it fails: SHOW TABLES lists the names of tables that exist in the sales schema of the retail catalog. It does not return the column definitions or data types of the customers table. While it is valid metadata inspection, it answers a different question (which tables exist) and therefore does not give the column-level details requested.

DESCRIBE DETAIL events;

Why it fails: DESCRIBE DETAIL returns table-level metadata such as format, location, number of files, size, partition columns, and table properties. It does not include the per-operation audit trail with user and timestamp that the analyst needs. It is useful for understanding storage characteristics but not for change history.

SELECT * FROM events VERSION AS OF 0;

Why it fails: Time travel with VERSION AS OF reads the data as it existed at a specific version, but it returns the table rows, not the operation history. It can help compare data states, yet it does not reveal which user performed which operation or when, so it fails the audit requirement of the scenario.

SHOW TABLES IN main.default;

Why it fails: SHOW TABLES lists table names in a schema; it does not expose any operation history, user identities, or timestamps for a specific table. It would only confirm that the events table exists, which is not what the scenario asks for. It is a schema-level listing command, not a table-level audit command.

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

Why it fails: Users with sufficient privileges on the securable, such as the owner or a user granted the necessary rights, can inspect grants; being able to query the table is generally sufficient context. Requiring ownership or metastore admin is too restrictive and does not reflect how privilege inspection works for analysts in Unity Catalog.

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

Why it fails: SHOW GRANTS is scoped to the securable named in the command. To see catalog-level or schema-level grants, you must run SHOW GRANTS ON CATALOG or SHOW GRANTS ON SCHEMA separately. The command does not automatically roll up parent grants into the table output, so this statement is inaccurate.

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

Why it fails: SHOW GRANTS is not scoped to the current user; it lists grants for the securable across principals, subject to your own permission to view them. It does not filter output to only your identity. The statement misrepresents how the command behaves and would mislead an analyst trying to understand who else can access the table.

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

Why it fails: CREATE MANAGED TABLE is not valid Databricks SQL syntax. Managed tables are created with CREATE TABLE when no LOCATION is supplied; there is no MANAGED keyword in the CREATE TABLE grammar. The statement would fail to parse, so it cannot satisfy the requirement despite the intent to create a managed table.

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

Why it fails: CREATE EXTERNAL TABLE with an explicit LOCATION creates an external table whose data lifecycle is not managed by Unity Catalog; dropping the table does not remove the underlying files. The scenario explicitly asks for a managed table, so specifying an external location contradicts the requirement. The path '/mnt/data/summary' also references a mount style that is not the Unity Catalog external location pattern.

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

Why it fails: Providing an explicit LOCATION creates an external table rather than a managed one, and the dbfs:/user/hive/warehouse path is associated with the legacy Hive metastore, not Unity Catalog managed storage. This conflicts with the requirement that Unity Catalog manage the table, and the path style is not the correct Unity Catalog pattern for a managed table.

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

Why it fails: Databricks does not maintain a recycle-bin schema for dropped tables in Unity Catalog, and there is no 30-day self-service restore path for arbitrary users. Recovery depends on separate mechanisms such as Delta time travel on an existing table or restoring from backups, neither of which applies after a DROP has removed the table and its data.

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

Why it fails: Databricks SQL does not block DROP statements based on concurrent readers. There is no built-in lock that prevents dropping a table simply because someone might be querying it. The statement executes as long as the principal has the required privileges, so this option misrepresents how concurrency and DDL interact in Databricks.

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

Why it fails: This describes the behavior of an external table, not a managed one. With managed tables, Databricks controls the data location, so it deletes the files as part of the drop. Leaving the files behind would be incorrect here and would also make the storage state inconsistent with the catalog, which Databricks specifically avoids for managed tables.

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.

Why it fails: Unity Catalog does not provide a recycle bin schema where dropped tables are parked for any workspace user to restore. Dropping a managed table deletes its data files after the retention period, and recovery requires undeleting the data files or recreating the table. The privilege requirement to drop the table also still applies, so this identity could not perform the drop.

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

Why it fails: For a managed Delta table, DROP TABLE removes the metadata and also deletes the underlying data files from the managed storage location after the retention period. Leaving orphaned Parquet files behind is characteristic of an external table, not a managed one. Regardless of table type, the privilege check still blocks this user before any deletion occurs.

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

Why it fails: IF EXISTS is purely a guard against erroring when the object is absent; it carries no privilege semantics and does not downgrade the operation. Dropping a table is a destructive DDL action gated by ownership or MANAGE, so a schema user without those grants cannot execute it. There is also no soft-delete conversion in Unity Catalog DROP TABLE behavior.

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

Why it fails: Temporary views are never persisted to Unity Catalog, so the premise of a catalog privilege problem does not apply. The failure comes from the object simply not existing in the second session's namespace, not from a missing USE CATALOG grant. A privileges issue would produce a permission error rather than TABLE_OR_VIEW_NOT_FOUND.

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

Why it fails: total_revenue is not a reserved keyword in Databricks SQL, and even if an alias collided with a keyword it would cause a parse error at creation time, not silent removal. Databricks does not auto-drop views due to alias naming. The error is about the view being unresolvable in a new session, not about column naming.

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

Why it fails: Delta and Unity Catalog tables do not require primary keys to be queried or aggregated. The CREATE OR REPLACE TEMPORARY VIEW would have succeeded if permissions were valid, since GROUP BY does not depend on any key constraint. Lack of a primary key is not a reason for the view to disappear from another session.

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

Why it fails: A materialized view stores precomputed results and must be refreshed to reflect changes in the base table, so it does not automatically pick up future updates. It also requires a Databricks SQL warehouse with serverless or pro configuration and is intended for expensive aggregations, not simple row filtering. For a lightweight, always-current filter, a standard view is the appropriate choice.

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

Why it fails: A temporary view is scoped to the current session or notebook and disappears when the session ends, and it cannot be created with a three-part Unity Catalog name. It therefore cannot serve as a persistent, shareable object for other analysts. The requirement implies a durable object usable across sessions, which a temporary view cannot satisfy regardless of its refresh behavior.

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

Why it fails: WITH SCHEMA EVOLUTION is a clause for table operations such as CREATE TABLE AS SELECT and MERGE, not for defining a view. Including it in a CREATE VIEW statement produces a syntax or analysis error. Even if it parsed, schema evolution governs how new source columns are absorbed during writes, which is irrelevant to a read-only filtered view.

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

Why it fails: Row-level filters remove rows, not columns. Analysts would still see the email column for rows that pass the filter, which violates the requirement that the raw email must never be visible. Row filters address horizontal visibility, whereas this scenario needs vertical projection control, making the approach a poor fit.

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

Why it fails: Renaming a column does not restrict access; any user with SELECT on the table can still read the renamed column and discover its contents. Security by obscurity is not an access control mechanism and would fail an audit. The requirement demands that raw email values never be visible, which renaming cannot guarantee.

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

Why it fails: Column masks obscure values but do not prevent analysts from seeing the column's existence or from attempting to query it; they also require careful function design and privilege management. The requirement is that analysts must never see the raw email, and a mask can still return a value depending on the masking expression. A projection view with revoked table access is stricter and simpler here.

`VACUUM bronze.raw_events;`

Why it fails: VACUUM removes old data files that are no longer referenced by the transaction log and are past the retention threshold. It reclaims storage but does not compact small files into larger ones, so it will not improve read performance for the dashboards. Running VACUUM also reduces time-travel history, which is an unintended side effect unrelated to the stated goal.

`ANALYZE TABLE bronze.raw_events COMPUTE STATISTICS;`

Why it fails: Computing statistics collects metadata about data distribution used by the query optimizer to produce better plans. While useful, it does not change the physical layout of files on storage, so the small-file problem remains and scan overhead persists. Statistics help planning, not the underlying I/O cost of reading thousands of tiny files, so this does not solve the stated performance issue.

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

Why it fails: Setting optimizeWrite affects future writes by reducing the number of files produced per write, but it does not compact the existing small files already present in the table. The dashboards query current data, so historical fragmentation still hurts performance. This property would help going forward but cannot remediate the accumulated small-file layout that already exists.

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

Why it fails: OPTIMIZE compacts small files and can improve read performance, but it does not change how a reader session resolves the table's current version. A session reusing a stale snapshot will still see old data even after compaction. Scheduling OPTIMIZE is a maintenance practice, not a mechanism for forcing readers to observe the latest commit.

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

Why it fails: The Delta cache accelerates repeated reads of the same Parquet files but does not automatically invalidate entries when the table is updated. Enabling or enlarging the cache can actually prolong staleness because cached file data may be reused. The staleness stems from snapshot reuse, not from insufficient cache capacity, so this does not address the root cause.

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

Why it fails: Delta Lake provides ACID transactions and versioning; converting to plain Parquet removes those guarantees and does not inherently fix session-level snapshot reuse. Reading Parquet directly can also expose partially written files during concurrent updates. This changes the storage format rather than the reader's snapshot behavior, so it does not resolve the staleness.

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

Why it fails: Column masks transform values within a column; they do not remove rows from the result set. A mask might return nulls or obfuscated values, but it would not cause entire rows to disappear. Row reduction is the role of row filters, which are attached to tables, not column masks on views.

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.

Why it fails: Row filters are attached to tables, not defined inline on views in Unity Catalog, so a view cannot carry its own WITH ROW FILTER clause. SHOW CREATE TABLE does not hide filter definitions either. The observed behavior is better explained by a filter on the underlying base table that the view reads.

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

Why it fails: Time travel applies to Delta tables, not to standard views, and it does not filter rows based on a predicate; it returns a different table version. A view querying a table always resolves to the current version unless the SQL explicitly specifies a version or timestamp. Time travel also would not produce a consistent predicate-like reduction.

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.

Why it fails: Comparing a string column to a string literal does not introduce casting that defeats partition pruning. Partition pruning works on the partition key values regardless of whether they are strings or dates, as long as the predicate is a simple equality or range on the partition column. This option invents a casting problem that does not exist in the described query.

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

Why it fails: If the physical partitioning used a different column, the predicate on `event_date` could not prune files and performance would suffer. However, the scenario states the table is partitioned on `event_date`, so this explanation contradicts the given facts. It is plausible in general but does not match the described setup and therefore is not the best explanation here.

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

Why it fails: Wrapping the partition column in a function can indeed prevent pruning, and using current_date would be non-deterministic. But the stem shows a plain equality against a literal date, not a function call, so this does not describe the actual query. It is a real pitfall in other situations but is inconsistent with the predicate given.

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

Why it fails: Adding workers increases parallelism but does not fix the underlying small-file inefficiency or improve data skipping. Each worker still pays per-file overhead, and the table layout remains unoptimized. Scaling compute masks the symptom while increasing cost, whereas compacting files and clustering data address the root cause.

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

Why it fails: A view is a saved query, not a storage optimization. Converting the table to a view does not reduce file count or add data-skipping statistics, and it may push computation to query time, hurting dashboard performance. This option confuses access patterns with physical layout tuning and does not satisfy either requirement.

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

Why it fails: VACUUM removes files no longer referenced by the current table version, but a zero-hour retention can break time travel and concurrent readers. It does not compact small files or improve data skipping; it only deletes unreferenced data. Using zero retention risks data loss for readers still on older snapshots, so it is inappropriate for this performance goal.

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

Why it fails: CREATE FOREIGN TABLE is not a Databricks SQL statement; foreign tables are associated with external data sources in other database systems. Databricks exposes external files through external tables, views over files, or direct file queries. Using this syntax would produce an error and the analyst would not be able to query the CSV.

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

Why it fails: COPY INTO loads data from files into an existing Delta table; it does not create a queryable object by itself and requires a target table to be defined first. It also materializes the data, which contradicts the goal of querying the file without loading it. The CSV path would be the source, not the target, of a COPY INTO statement.

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

Why it fails: Databricks SQL cannot select directly from a raw storage path as if it were a table; the path must be wrapped in a file-reading function such as read_files or referenced through an external table. This statement would fail to resolve the path as a relation. A temporary view could be defined over read_files, but not over the bare path.