Databricks Data Analyst common mistakes
Databricks Data Analyst · Understand the traps and how to avoid them.
Answers namespace questions using two-part names (schema.table) or dbfs:/ paths, ignoring that in a Unity Catalog-enabled workspace every object is addressed as catalog.schema.table and files live under volumes or cloud storage paths, not DBFS root.
Habit from legacy Hive-metastore workspaces and community tutorials where the workspace default catalog is implicit, so the third level is invisible in daily work.
What to do instead
Before answering, ask which metastore/namespace the scenario implies. Drill the three-level rule and be able to state when dbfs:/FileStore or DBFS root is still valid versus when a volume (catalog.schema.volume) is required for non-tabular data.
Treats a statement like ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY as enforced, and designs models assuming duplicate or null keys will be rejected at write time.
Relational database experience carries over, and the DDL syntax is identical to an enforcing engine.
What to do instead
Learn which constraints Databricks SQL declares as informational (PRIMARY KEY, FOREIGN KEY - used for optimizer hints and BI tool metadata) versus NOT NULL, which is validated. Plan deduplication as an ETL responsibility, not a schema guarantee.
Drops or overwrites a managed table expecting the underlying data files to be gone, or expects a DELETE to immediately free/remove files at the storage layer.
Managed-versus-external semantics and Delta's log-structured storage are conflated with plain filesystem behaviour.
What to do instead
Separate the two concepts in notes: managed tables have their data lifecycle owned by the catalog (DROP removes data), external tables leave files; row-level deletes are logical until VACUUM passes the retention threshold. Be able to state that VACUUM with default retention is what physically removes stale files.
Loads CSV/JSON by letting Databricks infer the schema and does not specify options, then is surprised by all-string columns, wrong date parsing, or a header row loaded as data.
Schema inference succeeds on a small sample and the notebooks used for practice hide the inferred types.
What to do instead
Default to an explicit schema (DDL string or StructType) plus explicit format options such as header, delimiter, and dateFormat. For repeatable ingestion, prefer COPY INTO or Auto Loader with a declared schema and rescued-data column rather than inference.
Diagnoses a slow query by reading only total duration or the SQL text, and never opens the query profile to find the operator with the largest wall-clock time, shuffle spill, or partition skew.
Candidates apply general SQL tuning heuristics (add an index, rewrite the join) instead of the diagnostic tool Databricks actually exposes.
What to do instead
Practise the routine: open Query Profile, sort operators by time, then look for 'spilling to disk', high shuffle read size, and skew across tasks. Name the fix that matches the symptom - repartition or Z-ORDER/OPTIMIZE, broadcast the small side, filter earlier, or right-size the warehouse.
Writes queries with ${param} widget syntax or literal values where a Databricks SQL parameter marker (:param) or a dashboard parameter/date-range control is required, and cannot explain how the value is supplied at run time.
Notebook widget habits dominate, and SQL editor parameters, dashboard filters, and legacy dashboard query parameters look similar but behave differently.
What to do instead
Learn the three layers distinctly: parameter markers in the SQL editor and alerts, dashboard-level filters/date-range controls bound to a field, and notebook widgets. Know that dashboard filters are applied by the dashboard, not by editing the underlying query text.
Assumes anyone who can open a published dashboard can also see its data, or conversely grants direct table SELECT privileges to every dashboard viewer.
The owner-versus-viewer credential model is invisible in the UI until a viewer without table grants either succeeds or fails.
What to do instead
Answer permission questions by first asking who owns the query and whether the dashboard is configured to run as owner or as viewer. If it runs as owner, viewers need only dashboard access; if as viewer, they need USE CATALOG, USE SCHEMA, and SELECT on every underlying object.
Expects a Genie space to answer arbitrarily phrased questions over every table in the workspace, and treats poor answers as a Genie limitation rather than a curation gap.
Genie is assumed to be a general natural-language layer, when it is scoped to the tables, instructions, and examples configured in that space.
What to do instead
Describe Genie work as curation: select and describe the relevant tables, add general instructions, synonyms, example SQL, and trusted assets or measures, then test with representative phrasings and iterate. Also know how sharing a Genie space differs from sharing a dashboard.
Grants SELECT on a table and then cannot explain why the user's query fails, or assumes a column mask or row filter does not apply to the person who created it.
Unity Catalog privileges are hierarchical and masks/filters have explicit exemption handling that is easy to overlook.
What to do instead
Remember the inheritance chain (USE CATALOG on the catalog, USE SCHEMA on the schema, then SELECT) and that masks and row filters apply to all users except those explicitly exempted, typically the object owner and members of a designated exempt group. Reason through the full chain before choosing an answer.
Distinguishes managed versus external tables and selects a table type without weighing that external tables require an external location plus storage credential, and that dropping the table does not drop the files.
The exam frames this as a storage-ownership decision, but candidates answer from the DDL syntax alone.
What to do instead
Anchor on the decision rule: who should own the files' lifecycle? Managed when the catalog should clean up, external when files are shared with other engines or must survive DROP. Verify that external locations and storage credentials must exist and be granted before an external table can be created.