Databricks Data Engineer Professional — Practice Questions and Mock Exam

Nested data flattening patterns: 1) Struct: df.select('order_id', 'customer.name', 'customer.email'). SQL: SELECT customer.name FROM orders. 2) Array: df.select('order_id', explode('items').alias('item')). SQL: SELECT EXPLODE(items) FROM orders. 3) Nested array of structs: df.select('order_id', explode('items').alias('item')).select('order_id', 'item.product', 'item.qty'). 4) Map: df.select('order_id', explode('metadata').alias('key', 'value')). 5) Star expansion: df.select('order_id', 'customer.*'). Expands struct to individual columns. 6) Recursive flattening: for deeply nested structures, write a recursive function that walks the schema and generates the select expressions. 7) Considerations: cardinality explosion — EXPLODE on arrays multiplies row count. Use array_size() to estimate expansion. For high-cardinality arrays (100+ elements), consider aggregation before flattening.

Unity Catalog namespace: 1) Three-level: catalog.schema.table. Catalog: top-level organizational boundary. Schema: logical grouping within a catalog. Table/View/Function: data objects. 2) Catalog strategies: per business unit: finance_catalog, marketing_catalog. Per environment: dev_catalog, staging_catalog, prod_catalog. Per domain: customers_catalog, orders_catalog. Hybrid: prod_finance, prod_marketing, dev_shared. 3) Ownership delegation: catalog owner manages their catalog. Can create schemas, grant permissions. Cannot modify other catalogs. 4) Governance consistency: metastore admin sets: default permissions. Audit log retention. Data classification requirements. These apply across all catalogs. 5) Migration: from Hive metastore: default (single catalog) → migrate to multiple catalogs. SHOW DATABASES → becomes schemas in a catalog. 6) Limits: one metastore per region. Multiple catalogs per metastore. Hundreds of schemas per catalog. Millions of tables per schema.

Standard Python UDFs in Spark serialize data one row at a time across the JVM-Python boundary, which is expensive at scale. Pandas UDFs (also called vectorized UDFs) instead use Apache Arrow to move columnar batches and execute pandas code on Series or DataFrames, amortizing serialization across many rows. They keep work distributed across executors, unlike collect(), and retain Catalyst planning, unlike the RDD API. For custom Python logic that cannot be expressed in native Spark functions, a Pandas UDF is the recommended high-performance choice. Native Spark SQL functions are still preferred when the logic can be expressed with them. Exam tip: when a question pairs 'custom Python logic' with 'best performance' on large data, pick the Pandas/vectorized UDF over a plain Python UDF.

Reverse ETL components: 1) Source query: SELECT customer_id, total_orders, avg_order_value, churn_score FROM gold.customer_360 WHERE updated_at > last_sync_time. 2) Sync tracking: CREATE TABLE sync_status (record_id STRING, target STRING, last_synced TIMESTAMP, status STRING, error_message STRING). 3) API integration: for batch in batches: try: response = crm_api.upsert(batch). update_status(batch, 'synced'). except RateLimitError: sleep(backoff). retry. except APIError as e: update_status(batch, 'failed', str(e)). 4) Idempotency: use upsert API calls (create or update). Re-running syncs the same records without duplicates. 5) Monitoring: alert when sync failure rate exceeds threshold. Dashboard: records synced per run, error rates, latency. 6) Schedule: match the refresh cadence of the gold table. If gold refreshes daily at 6 AM, sync to CRM at 7 AM.