Data Processing & Transformations: Databricks Data Engineer Associate Practice Questions

A data engineer is building a Lakeflow Spark Declarative Pipeline that reads from a cloud storage Auto Loader source and writes to a materialized view called silver_orders. A stakeholder asks the engineer to clarify the technical difference between a Streaming Table and a Materialized View in Lakeflow SDP, specifically with regard to how each processes data and when it should be used. A product manager who will use the Gold layer asks whether Gold layer aggregation tables should be Streaming Tables or Materialized Views. Which explanation is most technically accurate?

1. AStreaming Tables and Materialized Views are functionally equivalent in Lakeflow SDP — both process only new incremental records on each pipeline run and both automatically recompute when any upstream dependency changes. The only operational difference is that Streaming Tables use a Kafka-style offset mechanism to track processed records, while Materialized Views use Delta Lake transaction log versions as their checkpoint mechanism, making Materialized Views slightly faster for high-volume sources.
2. BStreaming Tables are the appropriate choice for all pipeline datasets — both ingestion and aggregation — because Lakeflow SDP's query optimizer automatically converts aggregation logic defined on Streaming Tables into incremental stateful computations using Spark Structured Streaming's updateStateByKey, so there is no need to use Materialized Views for aggregations; Materialized Views exist only for backward compatibility with older Delta Live Tables pipelines.
3. CA Streaming Table processes data incrementally, appending only new records from the source on each pipeline run using a persistent checkpoint, making it ideal for append-oriented ingestion workloads (e.g., Auto Loader from cloud storage). A Materialized View recomputes its full result set on each pipeline update based on the current state of upstream tables, making it ideal for aggregations, joins, and derived metrics — exactly the pattern needed for Gold layer summary tables.
4. DMaterialized Views in Lakeflow SDP are equivalent to standard SQL views in that they do not store data physically on disk; instead, they are logical query definitions that are evaluated at query runtime against the underlying Streaming Tables, which means they always reflect the most current state of upstream data without requiring any pipeline execution and without consuming any additional Delta Lake storage beyond the source tables.

A senior data engineer is presenting the case for migrating the team's existing hand-written PySpark ETL pipeline — currently a 1,200-line Python script submitted to a Databricks job cluster — to Lakeflow Spark Declarative Pipelines. The existing script manually manages checkpoint locations, handles schema evolution with try/except blocks, logs data quality failures to a separate error table, and requires the team to manually specify the execution order of 18 transformation stages via explicit DataFrame references. A skeptical stakeholder asks the engineer to identify the strongest architectural advantages that Lakeflow SDP provides over the current approach. Which answer most comprehensively and accurately describes those advantages?

1. ALakeflow SDP offers automatic dependency graph resolution (no manual ordering of 18 stages), built-in CONSTRAINT-based data quality with configurable actions (warn/drop/fail), native support for both streaming and batch modes in the same pipeline definition, automatic checkpoint and state management for streaming sources, schema evolution handling without custom code, and lineage tracking integrated with Unity Catalog — all reducing operational complexity compared to maintaining a 1,200-line imperative script.
2. BLakeflow Spark Declarative Pipelines provides faster query performance than hand-written PySpark scripts because the declarative query optimizer rewrites the user's Python code into a more efficient execution plan at runtime, eliminating the need for developers to manually tune shuffle partitions or broadcast hints, and it also provides a visual drag-and-drop pipeline editor in the Databricks UI that allows non-engineers to modify transformation logic without writing any code.
3. CLakeflow SDP is advantageous exclusively for streaming pipelines because its CONTINUOUS execution mode uses micro-batch Structured Streaming under the hood, which eliminates end-to-end latency; for batch-only workloads like the existing 18-stage PySpark script, the hand-written approach remains superior because Lakeflow SDP's TRIGGERED mode introduces additional scheduling overhead of 2–5 minutes per pipeline run that compounds across large numbers of transformation stages.
4. DLakeflow SDP's primary advantage over hand-written PySpark is that it automatically partitions output Delta tables using the most selective filter column observed in recent query patterns (leveraging the same Liquid Clustering algorithm used by OPTIMIZE), whereas hand-written PySpark scripts produce unpartitioned Delta tables by default unless the engineer explicitly specifies partitionBy() in every DataFrame write operation.

A data engineering team ingests raw JSON clickstream events from an e-commerce platform into a Bronze Delta table named bronze_events. The JSON payloads contain inconsistently cased field names, some records have null order_id values, and there are duplicate events originating from retry logic in the upstream system. The team needs to build a Silver layer table silver_events that can be safely used for revenue reporting. Which transformation approach most accurately reflects Silver layer responsibilities in the Medallion Architecture?

1. AApply dropDuplicates(['event_id']) to remove duplicate events, cast order_id to LongType and filter out null order_id rows using .filter(col('order_id').isNotNull()), standardize column name casing with .withColumnRenamed() for all inconsistent fields, and write the output to silver_events as a Delta table with trigger(availableNow=True) to support incremental processing.
2. BWrite the Bronze data directly to silver_events without transformation, then create a separate Gold table that applies all filtering and deduplication before exposing data to the reporting team, because introducing transformations at the Silver layer violates the append-only contract that makes Bronze-to-Silver reprocessing idempotent when upstream source data changes.
3. CStore only the raw JSON string payload in silver_events without parsing or transforming it, then expose a view on top of silver_events that uses from_json() at query time to dynamically parse the payload into structured columns, ensuring that any changes to the schema can be accommodated retroactively without rewriting historical Silver records.
4. DMerge all rows from Bronze into silver_events using a MERGE INTO statement that matches on event_id, inserts new records, and updates existing records with the latest payload, then apply schema enforcement using ALTER TABLE silver_events SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name') to handle the inconsistent field name casing automatically.

A data engineering team is designing a new enterprise data platform on Databricks using the Medallion Architecture. The platform architect asks each engineer to clearly define the responsibility boundaries of each layer. A junior engineer proposes that the Bronze layer should perform deduplication and schema enforcement, the Silver layer should ingest raw files directly from cloud storage as-is, and the Gold layer should store cleansed, enriched, and joined data ready for downstream ML model training. Which assessment of this proposal is correct?

1. AThe proposal is partially correct: Bronze should ingest raw files as-is and may apply light schema inference, while Silver should apply deduplication, filtering, and data quality enforcement; but the Gold layer should contain only pre-aggregated BI-ready summary tables and should never be used for ML feature engineering because ML models require access to row-level Silver data.
2. BThe proposal is fully correct as described: the Bronze layer is the right place for schema enforcement and deduplication because catching errors as early as possible reduces downstream reprocessing cost, Silver stores raw files to allow analysts to explore original data without transformations, and Gold provides cleansed and enriched data for both BI and ML consumption.
3. CThe proposal has the Bronze and Silver layer responsibilities reversed: Bronze should ingest and persist raw data from source systems exactly as received (append-only, minimal transformation), Silver should apply data quality rules including deduplication, filtering, type casting, and schema conformance, and Gold should contain business-level aggregations and domain-specific data products ready for BI and ML consumption.
4. DThe proposal is internally consistent for streaming-only architectures but is incompatible with the Medallion Architecture pattern for batch ingestion, which requires that Bronze and Silver layers be merged into a single 'Raw-Cleansed' zone to avoid redundant storage costs, with the Gold layer serving as both the cleansed dataset and the aggregated reporting layer simultaneously.

A data engineering team processes retail transaction data in PySpark using a DataFrame transactions_df with columns: store_id (STRING), product_id (STRING), category (STRING), sale_date (DATE), quantity (INTEGER), and revenue (DOUBLE). The team needs to compute the following metrics grouped by store_id and category: total revenue, average transaction revenue, number of distinct products sold, and the maximum single-transaction revenue. The output must alias columns as total_revenue, avg_revenue, unique_products, and max_revenue. Which PySpark code correctly produces this result?

1. Afrom pyspark.sql.functions import sum, avg, count, max result_df = transactions_df.groupBy('store_id', 'category').agg( sum('revenue').alias('total_revenue'), avg('revenue').alias('avg_revenue'), count('product_id').alias('unique_products'), max('revenue').alias('max_revenue') )
2. Bfrom pyspark.sql.functions import sum, mean, countDistinct, max result_df = transactions_df.groupBy('store_id', 'category').agg( sum('revenue').alias('total_revenue'), mean('revenue').alias('avg_revenue'), countDistinct('product_id').alias('unique_products'), max('revenue').alias('max_revenue') )
3. Cfrom pyspark.sql.functions import sum, avg, countDistinct, max result_df = transactions_df.agg( sum('revenue').alias('total_revenue'), avg('revenue').alias('avg_revenue'), countDistinct('product_id').alias('unique_products'), max('revenue').alias('max_revenue') ).groupBy('store_id', 'category')
4. Dfrom pyspark.sql.functions import collect_set, sum, avg, max result_df = transactions_df.groupBy('store_id', 'category').agg( sum('revenue').alias('total_revenue'), avg('revenue').alias('avg_revenue'), collect_set('product_id').alias('unique_products'), max('revenue').alias('max_revenue') )

A data engineer is setting up a new Delta table for a product catalog. The table must be created only if it does not already exist, and if no catalog data is available yet, the table should be empty. Later, the team wants to replace the table structure entirely (renaming a column and adding a new one) without dropping it manually first, ensuring the old data is gone and the new schema takes effect atomically. Which pair of DDL statements correctly handles the two distinct scenarios?

1. AScenario 1: CREATE TABLE product_catalog IF NOT EXIST (product_id STRING, name STRING) USING DELTA; — Scenario 2: ALTER TABLE product_catalog REPLACE COLUMNS (product_id BIGINT, product_name STRING, category STRING); — because IF NOT EXIST prevents duplication errors on first creation, and ALTER TABLE REPLACE COLUMNS atomically replaces all column definitions and purges all existing row data from the Delta table.
2. BScenario 1: CREATE OR REPLACE TABLE product_catalog (product_id STRING, name STRING) USING DELTA; — Scenario 2: DROP TABLE IF EXISTS product_catalog; CREATE TABLE product_catalog (product_id BIGINT, product_name STRING, category STRING) USING DELTA; — because CREATE OR REPLACE handles the idempotency requirement for initial creation, and an explicit DROP followed by CREATE ensures all existing data is purged before the new schema definition is committed.
3. CScenario 1: CREATE TABLE product_catalog (product_id STRING, name STRING) USING DELTA LOCATION '/mnt/delta/product_catalog'; — Scenario 2: TRUNCATE TABLE product_catalog; ALTER TABLE product_catalog ADD COLUMNS (category STRING); ALTER TABLE product_catalog RENAME COLUMN name TO product_name; — because specifying an explicit LOCATION ensures the external table path is always writable, and TRUNCATE followed by column modification commands achieves the equivalent of a schema replacement without full table recreation.
4. DScenario 1: CREATE TABLE IF NOT EXISTS product_catalog (product_id STRING, name STRING) USING DELTA; — Scenario 2: CREATE OR REPLACE TABLE product_catalog (product_id BIGINT, product_name STRING, category STRING) USING DELTA; — because IF NOT EXISTS skips creation silently if the table already exists, and CREATE OR REPLACE TABLE atomically drops and recreates the table with the new schema and zero rows.

A data engineer has a Gold layer Delta table daily_sales_summary that was populated by a batch job that ran incorrectly — the totals are double-counted due to a bug. The engineer needs to completely replace the table's contents with a corrected result set generated by re-running the transformation query against silver_sales, while ensuring the operation is atomic (no window where the table holds partial data) and preserves the Delta transaction history for auditability. Which approach is most appropriate?

1. AUse INSERT OVERWRITE daily_sales_summary SELECT ... FROM silver_sales — because INSERT OVERWRITE atomically replaces all data in the table with the results of the SELECT query in a single Delta transaction, preserving the full transaction history and allowing time travel back to the pre-overwrite version, while also updating table statistics used by the query optimizer.
2. BRun TRUNCATE TABLE daily_sales_summary to remove all rows, then immediately run INSERT INTO daily_sales_summary SELECT ... FROM silver_sales to populate the corrected data — because TRUNCATE is a fast metadata-only operation that adds a single Delta log entry, and the subsequent INSERT adds another entry, giving a two-step atomic replacement that is fully auditable in DESCRIBE HISTORY.
3. CUse CREATE OR REPLACE TABLE daily_sales_summary AS SELECT ... FROM silver_sales — because this command atomically recreates the table with corrected data in a single DDL transaction; however, it also resets the Delta transaction log to version 0, removing all historical entries and making pre-correction time travel permanently inaccessible, which may violate audit requirements.
4. DRun DELETE FROM daily_sales_summary WHERE 1=1 followed by INSERT INTO daily_sales_summary SELECT ... FROM silver_sales — because the DELETE statement with a constant true predicate is optimized by the Delta Lake engine into a fast full-table deletion using partition metadata rather than a row-by-row scan, and the subsequent INSERT completes the replacement in a separate transaction that is independently auditable.

A data engineering team is implementing a Lakeflow Spark Declarative Pipeline to process financial transaction records. At the Silver layer, business rules require that: (1) any record with a null transaction_id must cause the entire pipeline run to fail immediately and alert the on-call engineer; (2) records with a negative amount must be silently dropped and counted in a metrics table for auditing; (3) records where currency_code is not in the approved list should be flagged with a warning but allowed to pass through. The team is implementing these rules using CONSTRAINT clauses in SQL. Which implementation correctly maps all three rules to the appropriate constraint actions?

1. ACONSTRAINT valid_id EXPECT (transaction_id IS NOT NULL) ON VIOLATION WARN; CONSTRAINT positive_amount EXPECT (amount >= 0) ON VIOLATION FAIL UPDATE; CONSTRAINT valid_currency EXPECT (currency_code IN ('USD','EUR','GBP')) ON VIOLATION DROP ROW; — because FAIL UPDATE causes the pipeline to retry the failed batch after correcting the source data, DROP ROW removes invalid currency records from the output, and WARN allows null IDs to be tracked without blocking the pipeline.
2. BCONSTRAINT valid_id EXPECT (transaction_id IS NOT NULL) ON VIOLATION DROP ROW; CONSTRAINT positive_amount EXPECT (amount >= 0) ON VIOLATION WARN; CONSTRAINT valid_currency EXPECT (currency_code IN ('USD','EUR','GBP')) ON VIOLATION FAIL UPDATE; — because this configuration drops records with null IDs to ensure downstream joins never encounter null keys, warns on negative amounts to allow finance analysts to investigate, and causes the pipeline to fail when unknown currency codes are detected until a currency table update resolves the violation.
3. CCONSTRAINT valid_id EXPECT (transaction_id IS NOT NULL) ON VIOLATION FAIL UPDATE; CONSTRAINT positive_amount EXPECT (amount >= 0) ON VIOLATION WARN; CONSTRAINT valid_currency EXPECT (currency_code IN ('USD','EUR','GBP')) ON VIOLATION DROP ROW; — because FAIL UPDATE halts the pipeline and triggers an alert, WARN allows negative-amount records through while logging violation counts for audit, and DROP ROW silently removes non-standard currency records from the Silver output.
4. DCONSTRAINT valid_id EXPECT (transaction_id IS NOT NULL) ON VIOLATION FAIL UPDATE; CONSTRAINT positive_amount EXPECT (amount >= 0) ON VIOLATION DROP ROW; CONSTRAINT valid_currency EXPECT (currency_code IN ('USD','EUR','GBP')) ON VIOLATION WARN; — because FAIL UPDATE immediately halts the pipeline run and raises an alert for null transaction IDs, DROP ROW silently excludes negative-amount records from the Silver table while recording violation counts, and WARN allows non-standard currency records to pass through while flagging them.