Courseiva

CCNA Managing Data Questions

17 questions · Managing Data · All types, answers revealed

Practice these questionsDomain overviewAll questions
1
MCQmedium

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?

A.CREATE MATERIALIZED VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
B.CREATE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
C.CREATE TEMPORARY VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
D.CREATE OR REPLACE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000 WITH SCHEMA EVOLUTION;
AnswerB

A standard CREATE VIEW stores only the SQL definition, and each query re-executes it against the base table, so results always reflect the latest committed data. This matches the requirement for a live, filtered projection without storage or refresh overhead. The view inherits access through the base table, so the analyst needs SELECT on the orders table.

Why this answer

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.

Exam trap

The trap here is confusing materialized views, which cache results and require refresh, with standard views that always read live data.

Practice this question →
2
MCQhard

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?

A.Run OPTIMIZE gold.orders after every upstream commit so the table is compacted and readers pick up new files.
B.Enable the Delta cache on the SQL warehouse and increase the cache size so recent files stay resident.
C.Convert the table to a Parquet table so queries always read the latest files directly from cloud storage.
D.Configure the session to use a fresh snapshot by disabling snapshot reuse, for example by setting the appropriate Spark configuration for the session.
AnswerD

Spark and Databricks SQL can reuse a cached table snapshot within a session for performance. When the underlying Delta table changes, a session that reuses the snapshot may return stale data. Disabling snapshot reuse forces each query to resolve the current table version, so dashboards see the latest committed data without restarting the warehouse.

Why this answer

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.

Exam trap

The trap here is attributing stale query results to caching or file layout when the real cause is session-level snapshot reuse of the Delta table version.

Practice this question →
3
MCQmedium

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?

A.Add a row filter to the table so rows containing emails are excluded for analysts, and keep base-table SELECT granted.
B.Create a view that selects all columns except email, grant SELECT on the view to the analysts, and revoke SELECT on the base table.
C.Rename the email column to a non-obvious name and rely on security by obscurity to hide it from analysts.
D.Apply a column mask on the email column using a masking function, and leave SELECT on the base table in place.
AnswerB

A view that projects only the non-sensitive columns, combined with revoking direct table access, gives analysts the data they need while preventing them from reading the email column. Because Unity Catalog views can run with the definer's privileges, analysts do not need base-table SELECT, so this cleanly enforces the privacy requirement.

Why this answer

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.

Exam trap

The trap here is thinking that a column mask or row filter removes a column from visibility, when those features alter values or rows rather than hiding the column entirely.

Practice this question →
4
MCQmedium

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?

A.CREATE TABLE reporting.analytics.summary (id INT, total DOUBLE);
B.CREATE MANAGED TABLE reporting.analytics.summary (id INT, total DOUBLE);
C.CREATE EXTERNAL TABLE reporting.analytics.summary (id INT, total DOUBLE) LOCATION '/mnt/data/summary';
D.CREATE TABLE reporting.analytics.summary USING DELTA LOCATION 'dbfs:/user/hive/warehouse/summary';
AnswerA

In Unity Catalog, CREATE TABLE with a three-part name creates a managed table by default when no LOCATION is specified. Unity Catalog manages the storage location and lifecycle, which matches the requirement. The statement is syntactically valid and uses the correct catalog.schema.table namespace for the reporting catalog and analytics schema.

Why this answer

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.

Exam trap

The trap here is inventing a MANAGED keyword or adding a LOCATION, when managed versus external is determined by whether a location is specified.

Practice this question →
5
MCQeasy

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?

A.The table metadata, data files, and any associated directories are permanently removed from the metastore and cloud storage.
B.The table is renamed to a recycle-bin schema and can be restored by any workspace user within 30 days.
C.The command fails because Databricks SQL does not permit dropping tables that other users may be querying.
D.Only the table metadata is removed; the underlying Parquet data files remain in cloud storage for later recovery.
AnswerA

For a managed table, Databricks owns the lifecycle of both metadata and data. Dropping it removes the catalog entry and deletes the underlying data files from the managed storage location. This is the defining behavior that separates managed from external tables and is why the statement succeeds without any additional file cleanup step from the analyst.

Why this answer

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.

Exam trap

The trap here is assuming that DROP only removes metadata, which is true for external tables but false for managed tables where Databricks also deletes the data files.

Practice this question →
6
MCQhard

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?

A.The analyst's group has a column mask applied to the view that removes rows where the masked column is null.
B.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.
C.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.
D.Delta Lake time travel is enabled on the view, so the analyst is reading an older snapshot that contains fewer rows.
AnswerB

Row filters in Unity Catalog are attached to tables and apply whenever the table is read, including through views. Because a standard view re-executes its definition against the base table, the attached row filter is evaluated and silently restricts rows. The filter is invisible in the view's SQL text, which explains why the view definition appears to lack a WHERE clause.

Why this answer

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.

Exam trap

The trap here is assuming the filter must be written in the view definition, when Unity Catalog row filters on the base table apply automatically.

Practice this question →
7
MCQmedium

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?

A.Apply dynamic views using standard Spark SQL case statements that evaluate session user functions.
B.Implement Unity Catalog row filters using a SQL function that evaluates the current user.
C.Grant SELECT privileges on filtered partitions using Unity Catalog folder-level access controls.
D.Configure cluster-level environment variables to filter data frames automatically upon session start.
AnswerB

Unity Catalog row filters attach a SQL function to the table that evaluates the invoking user's identity, returning only rows matching their assigned territory. Enforcement occurs at query time in the governance layer, so analysts cannot bypass the restriction through direct table reads.

Why this answer

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.

Exam trap

Candidates often think custom view definitions or manual user-filtering clauses in queries are sufficient, ignoring native row-level security enforcement.

Practice this question →
8
Multi-Selectmedium

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.)

Select 2 answers
A.Increase the SQL warehouse cluster size so more workers can scan small files in parallel.
B.Run OPTIMIZE on the table to compact small files into larger ones.
C.Run ZORDER BY on the frequently filtered column to co-locate related data.
D.Convert the table to a view so queries always compute results from the latest data.
E.Run VACUUM with a retention of zero hours to remove old files and speed up reads.
AnswersB, C

OPTIMIZE rewrites many small files into fewer, larger files, which reduces per-file overhead during reads. For a dashboard that scans the table repeatedly, fewer files mean less metadata and I/O work. This directly addresses the small-file problem described in the scenario and is a standard performance maintenance operation for Delta tables.

Why this answer

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.

Exam trap

The trap here is treating VACUUM or cluster scaling as performance tuning for file layout, when VACUUM only deletes old files and scaling does not change physical data organization.

Practice this question →
9
MCQmedium

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?

A.DESCRIBE DETAIL events;
B.SELECT * FROM events VERSION AS OF 0;
C.SHOW TABLES IN main.default;
D.DESCRIBE HISTORY events;
AnswerD

DESCRIBE HISTORY returns the Delta transaction log entries for the table, including version, timestamp, operation, operation parameters, user identity, and other metadata. This directly satisfies the requirement to audit who changed the table and when, and it is the canonical Delta Lake command for table history inspection in Databricks.

Why this answer

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.

Exam trap

The trap here is confusing table history with table detail or time travel, which look similar but serve very different audit purposes.

Practice this question →
10
MCQeasy

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?

A.The view definition was persisted in Unity Catalog, but the second tab lacks USE CATALOG privileges on the default catalog.
B.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.
C.The aggregated alias total_revenue conflicts with a reserved keyword, so the view was dropped automatically after creation.
D.The CREATE OR REPLACE TEMPORARY VIEW statement silently failed because the underlying sales.orders table lacks a primary key.
AnswerB

Temporary views in Databricks SQL are session-scoped objects. They exist only for the duration of the session or notebook that created them and are not registered in Unity Catalog. A different query tab or SQL editor session cannot see the view, which explains the resolution failure when the second tab attempts to query monthly_sales.

Why this answer

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.

Exam trap

The trap here is assuming that any object created with CREATE VIEW, even a TEMPORARY one, becomes a persistent, catalog-registered object visible to all users and sessions.

Practice this question →
11
MCQeasy

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?

A.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.
B.The statement succeeds and only the table's metadata is removed while the underlying Parquet data files remain in the managed storage location.
C.The statement fails with an insufficient privileges error because DROP TABLE requires ownership of the table or MANAGE on the parent schema.
D.The statement succeeds because IF EXISTS implicitly converts the operation into a metadata-only soft delete that any schema user can perform.
AnswerC

Dropping a managed Unity Catalog table requires ownership of the table or the MANAGE privilege on its parent schema, plus USE CATALOG and USE SCHEMA. Because the identity has neither, the statement fails with a permissions error. The IF EXISTS clause only suppresses the not-found error; it does not grant or waive privilege checks.

Why this answer

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.

Exam trap

The trap here is assuming IF EXISTS weakens the privilege requirement or that dropping a managed table leaves the data files intact.

Practice this question →
12
MCQhard

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?

A.The table has thousands of small files within the matching partition, so pruning selects the right partition but still reads excessive files.
B.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.
C.The table was partitioned by a high-cardinality column other than `event_date`, such as a unique event identifier.
D.The predicate is wrapped in a non-deterministic function such as `WHERE CAST(event_date AS DATE) = current_date()`, preventing partition pruning.
AnswerA

Partition pruning narrows the scan to the correct partition, but if that partition contains thousands of tiny files from frequent appends, the engine still pays heavy per-file overhead. Pruning and compaction solve different problems. This explanation is consistent with the facts: the partition column matches the predicate, yet the layout inside the partition remains fragmented and slow.

Why this answer

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.

Exam trap

The trap here is assuming that correct partition pruning guarantees fast reads, when heavy small-file fragmentation within the selected partition can still dominate query time.

Practice this question →
13
MCQmedium

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?

A.`VACUUM bronze.raw_events;`
B.`ANALYZE TABLE bronze.raw_events COMPUTE STATISTICS;`
C.`OPTIMIZE bronze.raw_events;`
D.`ALTER TABLE bronze.raw_events SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');`
AnswerC

OPTIMIZE compacts many small files into fewer, larger files, which reduces file-open overhead and improves scan performance for dashboards. This directly addresses the small-file problem created by streaming writes. It is the standard maintenance command for this scenario and does not require changing the table definition or rewriting queries against the table.

Why this answer

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.

Exam trap

The trap here is confusing VACUUM, which deletes unreferenced old files, with OPTIMIZE, which compacts current small files into larger ones.

Practice this question →
14
MCQeasy

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?

A.Create a foreign table with CREATE FOREIGN TABLE and query it with standard SQL.
B.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');
C.Run COPY INTO with the file path as the target and then query the resulting table.
D.Create a temporary view with CREATE TEMPORARY VIEW csv_data AS SELECT * FROM 'abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv';
AnswerB

The read_files table-valued function lets Databricks SQL read files directly from cloud storage without creating a table or view. It supports CSV, JSON, Parquet, and other formats, and you can pass options such as header and delimiter. This satisfies the requirement to query the CSV in place without loading it into Delta.

Why this answer

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.

Exam trap

The trap here is assuming a raw cloud storage path can be selected from directly or loaded with COPY INTO when the goal is to query without loading.

Practice this question →
15
MCQhard

A data analyst needs to create a new table that stores only aggregated daily sales totals derived from an existing Unity Catalog table, and the result must be refreshed nightly. They want the simplest object that persists the results and can be queried by other analysts. Which approach should they use?

A.Create a temporary view with `CREATE TEMPORARY VIEW sales.daily_totals AS SELECT ...` and grant other analysts access.
B.Create a view with `CREATE VIEW sales.daily_totals AS SELECT ...` so the aggregation is recomputed on every query.
C.Create an external table at a cloud storage path and populate it with `INSERT INTO` after each aggregation run.
D.Create a materialized view with `CREATE MATERIALIZED VIEW sales.daily_totals AS SELECT ...` and schedule a refresh.
AnswerD

A materialized view stores the computed result and can be refreshed on a schedule, which matches the requirement to persist daily aggregates and update them nightly. Other analysts can query it like a table, and Databricks manages the refresh and incremental maintenance where possible. This is the purpose-built object for persisted, refreshable aggregation results in Databricks SQL.

Why this answer

Persisting aggregated results that refresh on a schedule is precisely what a materialized view provides. It stores computed data, supports scheduled refresh, and is queryable like a table by other users. Plain views recompute each time, temporary views do not persist or share, and external tables require manual orchestration, so the materialized view is the simplest fitting object.

Exam trap

The trap here is assuming a standard view persists results; in fact a view stores only the query text and recomputes the aggregation on every access.

Practice this question →
16
MCQeasy

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?

A.DESCRIBE TABLE retail.sales.customers;
B.SELECT * FROM retail.sales.customers LIMIT 0;
C.SHOW CREATE TABLE retail.sales.customers;
D.SHOW TABLES IN retail.sales;
AnswerA

DESCRIBE TABLE (or DESC TABLE) returns the column names, data types, and comments for the specified table without scanning or returning any data rows. It works with the fully qualified three-part Unity Catalog namespace catalog.schema.table, so it correctly targets retail.sales.customers and satisfies the requirement to inspect metadata only.

Why this answer

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.

Exam trap

The trap here is assuming that any command containing the table name will return metadata, when each metadata command returns a different shape of information.

Practice this question →
17
Multi-Selecthard

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.)

Select 2 answers
A.You can run SHOW GRANTS ON TABLE finance.transactions to see the privileges granted on that specific table.
B.Privileges granted at the catalog or schema level are inherited by the table, so SHOW GRANTS ON TABLE may not show every effective privilege you hold.
C.You must be the table owner or a metastore admin to run SHOW GRANTS ON TABLE on a table you can query.
D.SHOW GRANTS ON TABLE finance.transactions will also list the privileges granted on the parent catalog and schema automatically.
E.SHOW GRANTS ON TABLE returns only the privileges of the current user and hides grants to other principals for privacy.
AnswersA, B

SHOW GRANTS ON TABLE returns the privileges granted on the specified securable, including which principals hold SELECT, MODIFY, or other rights. It is the direct way to inspect table-level grants in Unity Catalog and works with the three-part catalog.schema.table namespace, so it correctly answers what privileges exist on the transactions table.

Why this answer

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.

Exam trap

The trap here is assuming that SHOW GRANTS ON TABLE shows all effective privileges, when inheritance from catalog and schema means you must check multiple levels.

Practice this question →

Ready to test yourself?

Try a timed practice session using only Managing Data questions.

Start 20-question session