Home / DE Professional practice test / Developing Code for Data Processing using Python and SQL

Free · 8 questions with explanations

Developing Code for Data Processing using Python and SQL: Databricks Data Engineer Professional Practice Questions

Exam-style questions on Developing Code for Data Processing using Python and SQL. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Updated November 16, 2026

1 Developing Code for Data Processing using Python and SQL Medium

A data engineer is implementing a Pandas UDF that computes a rolling 7-day weighted moving average of revenue for each store_id partition. The function requires access to multiple rows within a group simultaneously. The engineer writes the following code: ``python from pyspark.sql.functions import pandas_udf from pyspark.sql import Window import pandas as pd @pandas_udf('double') def weighted_moving_avg(revenue: pd.Series) -> pd.Series: return revenue.rolling(window=7, min_periods=1).mean() result_df = df.withColumn( 'wma_revenue', weighted_moving_avg(col('revenue')).over( Window.partitionBy('store_id').orderBy('sale_date') ) ) ` When this code runs, it raises: AnalysisException: Pandas UDF does not support 'over' with a Window spec`. What is the correct approach to implement this grouped rolling calculation using Pandas UDFs?

  1. AChange the UDF decorator to @pandas_udf('double', PandasUDFType.SCALAR_ITER). Iterator-based Pandas UDFs support window operations: they receive an iterator of batches that correspond to window frames, and the rolling computation then runs across the rows of each store_id window.
  2. BUse the grouped-map pattern: df.groupBy('store_id').applyInPandas(weighted_moving_avg_fn, schema=result_schema). The function receives a full Pandas DataFrame for each store_id group and returns a Pandas DataFrame, and the rolling logic runs inside each group with native Pandas operations.
  3. CKeep the @pandas_udf('double') decorator and replace the window with Window.partitionBy('store_id').orderBy('sale_date').rowsBetween(-6, 0) to specify an explicit 7-row frame. Series-to-Series Pandas UDFs support .over() once the frame bounds are set with rowsBetween or rangeBetween.
  4. DConvert the Pandas UDF into a standard Python UDF with @udf('double'). Standard Python UDFs support .over() window expressions: they operate row by row and are free of the Arrow batching constraints that make Pandas UDFs incompatible with Spark window operations.
  5. ECollect the DataFrame to the driver with toPandas(), apply df.groupby('store_id').apply(lambda g: g.assign(wma=g['revenue'].rolling(7).mean())), and convert the result back with spark.createDataFrame(). Pandas UDFs have no grouped execution mode, which leaves the driver as the place for this logic.
Show answer & explanation

Correct answer: B

WHY B: The pattern for group-level Pandas operations (like rolling windows per partition) is applyInPandas, the modern equivalent of the GROUPED_MAP Pandas UDF (@pandas_udf(schema, PandasUDFType.GROUPED_MAP)). The function receives the whole Pandas DataFrame for each store_id group, which permits full Pandas rolling/window operations within the group, and returns a Pandas DataFrame. This is the idiomatic PySpark pattern for partition-level operations that need access to multiple rows simultaneously. WHY NOT A: SCALAR_ITER UDFs receive an iterator of batches for memory efficiency with scalar transformations. The batches do not correspond to partition groups or window frames and do not enable .over(). WHY NOT C: Scalar (Series-to-Series) Pandas UDFs do not support .over() window expressions, regardless of the frame specification. The AnalysisException is not resolved by changing the frame bounds. WHY NOT D: Standard Python UDFs also do not support .over() window expressions. A row-at-a-time UDF receives one row per call and cannot compute a rolling average across rows. WHY NOT E: Pandas UDFs do have a grouped mode (applyInPandas), which achieves the same result in a distributed way without moving data to the driver. Collecting a large DataFrame (hundreds of millions of rows) to the driver via toPandas() is an OOM anti-pattern.

2 Developing Code for Data Processing using Python and SQL Medium

A data engineering team is operating a production Lakeflow Spark Declarative Pipeline in TRIGGERED mode that processes financial transaction data from Auto Loader. After a routine deployment, the pipeline fails with the following error in the Event Log: SCHEMA_MISMATCH: Schema of data does not match schema of streaming table: expected 'amount DECIMAL(18,2)' but got 'amount DOUBLE'. The upstream source team has confirmed they changed the amount field type from DECIMAL(18,2) to DOUBLE in a recent data export. The pipeline must resume processing as quickly as possible without data loss. What is the CORRECT remediation sequence?

  1. AUpdate the streaming table definition to change the amount column type from DECIMAL(18,2) to DOUBLE to match the new source schema. Run a full refresh of the pipeline to rebuild the streaming table from scratch with the new schema, then resume normal TRIGGERED mode operation.
  2. BIn the pipeline settings, change cloudFiles.schemaEvolutionMode from none to addNewColumns and restart the pipeline. The addNewColumns mode handles type changes as well as new columns by widening the schema to accommodate both type representations of amount.
  3. CRun ALTER TABLE silver_transactions ALTER COLUMN amount TYPE DOUBLE on the pipeline's output Delta table from a Databricks SQL notebook, then restart the pipeline without a full refresh. The type change applied directly to the Delta table resolves the mismatch without reprocessing.
  4. DUpdate cloudFiles.schemaEvolutionMode to rescue in the pipeline configuration. Records with the new DOUBLE type for amount are then stored in the _rescued_data column as JSON strings instead of failing, and the pipeline resumes while a permanent schema fix is prepared.
  5. EDelete the pipeline's checkpoint directory in DBFS and restart the pipeline. Deleting the checkpoint forces Auto Loader to re-scan the files from the beginning and re-infer the schema from the latest data, and the streaming table is rebuilt with the DOUBLE type on that run.
Show answer & explanation

Correct answer: A

WHY A: A SCHEMA_MISMATCH error where an existing column's data type has changed (not just a new column added) requires a schema-breaking change to the streaming table definition. The procedure is: (1) update the streaming table's definition to reflect the new type, (2) perform a Full Refresh (pipeline reset, from the UI or databricks pipelines start --full-refresh), which truncates and rebuilds the target table so the new schema is applied consistently from the beginning, then resume TRIGGERED operation. This is the Lakeflow SDP procedure for type-changing schema evolution. WHY NOT B: addNewColumns handles the addition of new fields. It does not handle type changes to existing columns, so a DECIMAL-to-DOUBLE change is not resolved by it. WHY NOT C: Altering the Delta table schema directly via ALTER TABLE outside the pipeline can cause the Lakeflow SDP engine to detect a schema conflict between the pipeline definition and the physical table schema, potentially causing further errors. The pipeline definition and the target table schema are updated in a coordinated way via a Full Refresh. WHY NOT D: rescue mode captures records that don't match the current schema into _rescued_data. The amount field would land as a JSON string there rather than being processed correctly, and rescued records require manual reprocessing, so this is not a quick resume strategy. WHY NOT E: Manually deleting the checkpoint directory corrupts the pipeline's incremental state. Databricks manages checkpoints as part of the pipeline metadata, and a Full Refresh through the pipeline UI/API is the supported way to reset pipeline state.

3 Developing Code for Data Processing using Python and SQL Hard

A data engineering team is building a CDC pipeline using Lakeflow Spark Declarative Pipelines. The source is a Kafka topic that emits change events with the following schema: operation STRING (values: INSERT, UPDATE, DELETE), customer_id LONG (the business key), name STRING, email STRING, and updated_at TIMESTAMP (the sequencing column). The team wants to maintain a current-state Silver table silver_customers that reflects the latest version of each customer, including deletes. Which AUTO CDC INTO statement (the current recommended syntax, formerly APPLY CHANGES INTO) correctly implements this CDC pattern?

  1. AAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' COLUMNS * EXCEPT (operation) SEQUENCE BY updated_at STORED AS SCD TYPE 1;
  2. BAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation IN ('DELETE', 'UPDATE') SEQUENCE BY updated_at COLUMNS name, email; — UPDATE events must also be declared as deletes followed by inserts to correctly reflect row-level changes in the target table.
  3. CMERGE INTO silver_customers USING bronze_cdc_events ON silver_customers.customer_id = bronze_cdc_events.customer_id WHEN MATCHED AND operation = 'DELETE' THEN DELETE WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *; — The AUTO CDC API is only available for batch sources; for Kafka streaming CDC, MERGE INTO is the required approach.
  4. DAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY updated_at COLUMNS * EXCEPT (operation);
  5. EAUTO CDC INTO silver_customers FROM STREAM(bronze_cdc_events) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' APPLY AS TRUNCATE WHEN operation = 'TRUNCATE' SEQUENCE BY updated_at STORED AS SCD TYPE 2 COLUMNS * EXCEPT (operation);
Show answer & explanation

Correct answer: D

WHY D: This is the correct minimal AUTO CDC INTO syntax for a SCD Type 1 CDC target (default behavior, meaning the latest value overwrites the previous). FROM STREAM(bronze_cdc_events) reads from the upstream streaming table. KEYS (customer_id) specifies the business key for matching. APPLY AS DELETE WHEN operation = 'DELETE' instructs the engine to delete rows when the operation column is 'DELETE'. SEQUENCE BY updated_at uses the timestamp to order out-of-order events correctly. COLUMNS * EXCEPT (operation) writes all columns except the CDC metadata column. Note: AUTO CDC INTO is the current recommended syntax (replacing the legacy APPLY CHANGES INTO alias); both have identical clause structure. WHY NOT E: APPLY AS TRUNCATE is not a valid AUTO CDC clause. SCD TYPE 2 is a valid option but is not required here (and changes the semantics to historical tracking rather than current-state), and was not specified in the requirements. WHY NOT A: The clause order is invalid — in the documented AUTO CDC grammar, SEQUENCE BY must appear before the optional COLUMNS clause (KEYS → APPLY AS DELETE/TRUNCATE WHEN → SEQUENCE BY → COLUMNS → STORED AS). Option A places SEQUENCE BY after COLUMNS, which does not parse. (STORED AS SCD TYPE 1 by itself is legal but redundant, since SCD Type 1 is the default.) The minimal correct statement is option D. WHY NOT C: AUTO CDC INTO fully supports streaming sources from Kafka via upstream Streaming Tables. MERGE INTO is a batch SQL operation, not a streaming CDC mechanism. WHY NOT B: UPDATE events should NOT be declared as deletes. AUTO CDC INTO handles UPDATE events by matching on the key and updating the target row automatically — declaring them as deletes would incorrectly remove rows that should be updated.

4 Developing Code for Data Processing using Python and SQL Medium

A data engineering team is implementing a CDC pipeline using APPLY CHANGES INTO in Lakeflow SDP. The source CDC stream sometimes delivers events out of order — a DELETE event for a customer can arrive before the INSERT event for the same customer due to upstream replication lag. The business requirement is that the Silver table always reflects the last known state based on event timestamp, even when events arrive out of order. The team also wants to maintain a full historical audit trail showing all versions of each customer record, not just the current state. Which APPLY CHANGES configuration correctly satisfies both requirements?

  1. AAPPLY CHANGES INTO silver_customers_history FROM STREAM(bronze_cdc) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY event_ts COLUMNS * EXCEPT (operation) STORED AS SCD TYPE 2; — SCD Type 2 mode maintains a full historical record of all changes by inserting new rows for each version (with __START_AT and __END_AT columns) rather than overwriting the current row, satisfying the audit trail requirement. SEQUENCE BY event_ts ensures out-of-order events are applied in the correct logical order.
  2. BAPPLY CHANGES INTO silver_customers FROM STREAM(bronze_cdc) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY event_ts STORED AS SCD TYPE 3 COLUMNS * EXCEPT (operation); — SCD Type 3 stores both the current and previous value of each column in side-by-side column pairs (e.g., current_email, previous_email), providing a full audit trail of all changes in a single row without requiring additional join operations.
  3. CAPPLY CHANGES INTO silver_customers FROM STREAM(bronze_cdc) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY event_ts; — The SEQUENCE BY event_ts clause automatically handles out-of-order events by buffering all events and sorting by timestamp before applying them, regardless of arrival order.
  4. DAPPLY CHANGES INTO silver_customers FROM STREAM(bronze_cdc) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY arrival_timestamp COLUMNS * EXCEPT (operation); — Using arrival_timestamp (the Kafka message timestamp or Auto Loader file modification time) instead of the business event timestamp ensures events are always applied in the order they were received, regardless of out-of-order delays in the business event timestamp.
  5. EAPPLY CHANGES INTO silver_customers FROM STREAM(bronze_cdc) KEYS (customer_id) APPLY AS DELETE WHEN operation = 'DELETE' SEQUENCE BY event_ts STORED AS SCD TYPE 1 IGNORE NULL UPDATES COLUMNS * EXCEPT (operation); — IGNORE NULL UPDATES prevents out-of-order NULL-valued events from overwriting valid current-state data, and SCD TYPE 1 overwrites older versions while keeping only the current state.
Show answer & explanation

Correct answer: A

WHY A: This is the correct configuration for both requirements. STORED AS SCD TYPE 2 is the Lakeflow SDP mechanism for maintaining a full history — each change creates a new row with __START_AT and __END_AT columns tracking validity periods, and DELETE events close the current row by setting __END_AT. SEQUENCE BY event_ts uses the business event timestamp to determine ordering, so even out-of-order arrival events are applied to the history in the correct logical sequence. This satisfies both the 'latest state reflects actual event order' and 'full historical audit trail' requirements. WHY NOT C: SCD Type 1 (default) only keeps the current state — it overwrites previous values, so there is no audit trail of all versions. While SEQUENCE BY event_ts handles ordering correctly, the audit trail requirement is not satisfied. WHY NOT E: IGNORE NULL UPDATES prevents null-valued update events from overwriting non-null current values — this is a valid option for data quality but does not provide a full audit trail. SCD TYPE 1 still overwrites history. WHY NOT D: Using arrival_timestamp defeats the purpose of SEQUENCE BY for out-of-order handling — arrival order does not equal event order, so a DELETE arriving before an INSERT would still be applied incorrectly if ordered by arrival time. WHY NOT B: SCD Type 3 is not supported in Lakeflow SDP's APPLY CHANGES INTO syntax. SCD Type 3 (current + previous value columns) is not a valid option for this API — only SCD Type 1 (current state) and SCD Type 2 (full history) are supported.

5 Developing Code for Data Processing using Python and SQL Easy

A data engineering team is evaluating whether to use Spark Structured Streaming directly (via the DataFrame API) or Lakeflow Spark Declarative Pipelines (Lakeflow SDP) for a new production pipeline. The pipeline reads from a Kafka topic, applies a series of transformations including deduplication, type casting, and a join with a slowly changing dimension table, writes a Silver table, and feeds a Gold aggregation table. The team has five engineers with varying levels of Spark expertise. Which statement MOST accurately compares the two approaches for this specific use case, and leads to the correct recommendation?

  1. ASpark Structured Streaming directly is the correct choice: Lakeflow SDP supports cloud storage sources through Auto Loader and has no connector for Kafka topics. For Kafka-based pipelines the DataFrame streaming API is the supported approach in Databricks, and the team would write the Silver and Gold tables with foreachBatch and explicit checkpoint locations.
  2. BThe two approaches are functionally equivalent for this use case, but Spark Structured Streaming directly suits teams with varying expertise better: it uses plain Python/Scala DataFrame code that is easier to debug in the Spark UI, whereas Lakeflow SDP uses a proprietary YAML-based configuration language that requires learning new syntax and abstractions.
  3. CSpark Structured Streaming directly is preferred: Lakeflow SDP runs in TRIGGERED mode by default, which introduces a fixed scheduling delay of 2–5 minutes between pipeline updates. Direct Structured Streaming supports sub-second trigger intervals, which makes it the viable option when end-to-end latency requirements for the Silver and Gold tables are under 5 minutes.
  4. DLakeflow SDP is the preferred choice for this multi-table pipeline: it manages checkpoints and state for the streaming tables, provides expectation-based data quality, resolves the dependency order between the Silver and Gold tables, and integrates lineage with Unity Catalog. Direct Structured Streaming leaves checkpoints, orchestration and quality checks to hand-written code.
  5. ELakeflow SDP is limited to pipelines with no more than 10 tables. For multi-table pipelines with Silver and Gold layers, the recommended approach is to split the work: use Lakeflow SDP for the Bronze-to-Silver ingestion layer and a separate Spark Structured Streaming job for the Silver-to-Gold aggregation, with the two orchestrated by a Lakeflow Jobs workflow.
Show answer & explanation

Correct answer: D

WHY D: For a multi-table pipeline with quality checks, dimension joins, and multiple output tables, Lakeflow SDP provides substantial advantages: checkpoint and state management without manual checkpoint path configuration, declarative data quality with CONSTRAINT / expectation clauses, DAG dependency resolution between the Silver and Gold tables, Unity Catalog lineage, and the AUTO CDC API for SCD patterns such as the dimension. With direct Structured Streaming the team manages checkpoints, writes explicit query orchestration logic, and builds custom data quality enforcement, increasing implementation complexity and operational burden. Teams with mixed Spark expertise benefit from the higher-level declarative abstractions that reduce boilerplate and error-prone state management code. WHY NOT A: Lakeflow SDP supports Kafka as a streaming source through the Structured Streaming Kafka connector under the hood; it is not restricted to Auto Loader cloud storage sources. WHY NOT C: Lakeflow SDP supports both TRIGGERED and CONTINUOUS modes. CONTINUOUS mode runs as a persistent streaming job with sub-minute latency, comparable to direct Structured Streaming, and there is no fixed 2–5 minute delay. WHY NOT B: Lakeflow SDP uses Python and SQL, not a proprietary YAML language. Pipeline definitions are written in SQL CREATE STREAMING TABLE / AUTO CDC INTO syntax or Python decorators, which are standard languages rather than new abstractions, and the approaches are not operationally equivalent. WHY NOT E: Lakeflow SDP has no 10-table restriction; production pipelines can contain dozens or hundreds of tables, so there is no need to split the Silver-to-Gold step into a separate job.

6 Developing Code for Data Processing using Python and SQL Medium

A senior data engineer is designing a new production Python project to be deployed via Databricks Asset Bundles across three environments: dev, staging, and prod. The team consists of 8 engineers who each need to run isolated unit tests locally before pushing code. The project includes shared utility functions used by multiple pipeline notebooks, a set of Lakeflow Spark Declarative Pipeline definitions, and environment-specific configuration values. The engineer wants to ensure the structure is modular, testable, and DAB-compatible. Which project structure and DAB design pattern best satisfies all of these requirements?

  1. APlace the Python logic inside a single main.py file at the project root, with environment-specific configuration values hardcoded as Python constants at the top of the file. Reference this file from each DAB job task. Use a tests/ directory with pytest for unit testing. Store the Lakeflow Spark Declarative Pipelines SQL directly inside the databricks.yml resource block as inline strings to keep configuration in one file.
  2. BPackage the pipeline code into a single Python wheel file that encapsulates each environment's configuration at build time. Use a CI/CD step that rebuilds the wheel for each target environment before deployment. This approach provides environment isolation at the artifact level, avoids the risk of variable substitution errors at deploy time, and keeps the databricks.yml file minimal and free of environment-specific logic.
  3. CStore the logic inside Databricks notebooks in the workspace and use DAB's include: directive to reference workspace notebook paths directly. Deploy configuration differences between environments by maintaining three separate databricks.yml files — one per environment — and manually switching between them during deployment. This avoids the complexity of variable substitution while still using DAB for deployment automation.
  4. DOrganize shared utility functions into a Python package (e.g., src/myproject/utils/) installable as a local wheel, store pipeline definitions as separate .py or .sql files under a pipelines/ directory, define environment-specific values using DAB ${var.*} substitution in databricks.yml with per-target overrides, and maintain a tests/ directory with pytest that imports from the src/ package for local unit testing without requiring a live Databricks cluster.
  5. EUse DAB's built-in Jinja2 templating support to generate environment-specific notebook files by rendering templates at deploy time with databricks bundle render. Store base templates in a templates/ directory and rendered outputs in a generated/ directory. This pattern is the recommended DAB approach for multi-environment projects, as it gives engineers full control over the final deployed artifact content.
Show answer & explanation

Correct answer: D

WHY D: This is the canonical scalable DAB project structure. A src/ installable package keeps shared logic modular and locally testable via pip install -e without a live cluster. Separate pipeline definition files keep notebook/pipeline logic decoupled from deployment config. DAB variable substitution (${var.*}) with per-target overrides in databricks.yml is the idiomatic way to handle environment differences without duplicating resource definitions. A tests/ directory with pytest enables fast, cluster-free unit testing. WHY NOT A: Hardcoding environment-specific values as Python constants makes multi-environment deployment impossible without code changes and violates separation of concerns, and a single main.py is not modular. Inline pipeline SQL inside databricks.yml is not supported. WHY NOT C: Maintaining three separate databricks.yml files is an anti-pattern that duplicates the resource definitions per environment and introduces drift risk. DAB variable substitution and targets exist precisely to avoid this, and notebook-only logic is hard to unit test locally. WHY NOT B: Rebuilding a wheel per environment embeds configuration inside an artifact, which is an anti-pattern. Configuration should be external and injected at deploy/runtime, not baked into binary artifacts. WHY NOT E: DAB does not support Jinja2 templating or a databricks bundle render command. Variable substitution is handled natively by the DAB YAML engine, not via a template rendering step.

7 Developing Code for Data Processing using Python and SQL Medium

A data engineering team has an existing production Spark Structured Streaming job written in PySpark that reads from Delta Lake, applies a stateful sessionization window function using flatMapGroupsWithState, and writes results to a Gold Delta table. The team is evaluating migrating this job to Lakeflow Spark Declarative Pipelines. A senior engineer raises concerns about whether Lakeflow SDP can support all the features required. Which statement MOST accurately describes the capabilities and limitations of Lakeflow SDP compared to the team's direct Structured Streaming job for this specific use case?

  1. ALakeflow SDP does not natively support flatMapGroupsWithState or other arbitrary stateful operators in its declarative table definitions; it targets SQL-expressible transformations and common streaming patterns. A direct Structured Streaming job remains the better fit for this custom stateful logic.
  2. BLakeflow SDP can replace the job, as it runs pipeline code on the same Structured Streaming engine. flatMapGroupsWithState can be applied inside a @dlt.table function to the streaming DataFrame before it is returned, and the framework accepts arbitrary streaming operators there.
  3. CLakeflow SDP is more capable than direct Structured Streaming, as it runs on a newer proprietary Databricks engine that replaced Structured Streaming internally. flatMapGroupsWithState and the other operators run there with better performance, and migration is recommended.
  4. DThe team can migrate by declaring the Gold table as a Streaming Table and using APPLY CHANGES INTO with a SEQUENCE BY expression that reproduces the sessionization logic. SEQUENCE BY accepts arbitrary windowing functions, which makes it a replacement for flatMapGroupsWithState.
  5. EBoth approaches are equivalent in capability, but direct Structured Streaming is preferable in production: the pipeline metadata, event logging, and Unity Catalog lineage in Lakeflow SDP add 15–30% query latency compared with an equivalent direct job running the same transformations.
Show answer & explanation

Correct answer: A

WHY A: Lakeflow SDP is built on Spark Structured Streaming and handles the vast majority of streaming ETL patterns (APPLY CHANGES / AUTO CDC, aggregations, joins) extremely well, but it is a declarative framework that constrains table definitions to SQL-expressible or DataFrame API patterns that fit its table update model. flatMapGroupsWithState is a low-level stateful operator that manages arbitrary per-key state across micro-batches using a custom user-defined state transition function. This type of arbitrary stateful processing does not map cleanly to Streaming Table or Materialized View constructs. For such cases, direct Structured Streaming (the DataFrame API in a regular Python/Scala job) gives the team full access to every streaming operator. The guidance is to use Lakeflow SDP for the majority of pipeline logic and fall back to direct Structured Streaming when custom stateful operators are required. WHY NOT B: @dlt.table functions do work with a streaming DataFrame, but the framework imposes constraints on which operators can be applied: arbitrary stateful operators like flatMapGroupsWithState are not supported within the declarative pipeline execution model. WHY NOT C: Lakeflow SDP is not a replacement streaming engine; it runs on top of Spark Structured Streaming. It does not replace Structured Streaming internally, and flatMapGroupsWithState is not available within pipeline table definitions. WHY NOT D: APPLY CHANGES INTO (AUTO CDC) is for CDC (insert/update/delete from a change stream), not for arbitrary sessionization window logic. The SEQUENCE BY clause determines event ordering for CDC operations, not custom stateful window computations. WHY NOT E: Lakeflow SDP's management overhead does not add 15–30% query latency. The metadata and lineage operations are asynchronous and do not block query execution paths, and the two approaches are not equivalent in capability.

8 Developing Code for Data Processing using Python and SQL Easy

A data engineering team maintains a Databricks Job that processes data for 12 different retail brands. Each brand has a separate source schema and a slightly different transformation logic. Currently, the team maintains 12 nearly identical notebooks, one per brand. A senior engineer wants to refactor this into a single parameterized notebook that uses control flow to select the brand-specific logic at runtime, driven by a job task parameter. Additionally, the team wants the job to dynamically iterate over all 12 brands in parallel using a Databricks Workflow construct rather than sequential task execution. Which combination of Databricks features correctly implements this dynamic parallel execution pattern?

  1. AUse one notebook with an if/elif chain on dbutils.widgets.get('brand'). Create 12 separate jobs, one per brand with its own brand parameter, and wrap them in a parent job whose shell tasks call databricks jobs run-now for the 12 jobs in parallel.
  2. BUse one parameterized notebook with an if/elif block or dictionary dispatch on dbutils.widgets.get('brand'). Configure a for_each task that iterates over the list of brand names and runs the notebook as a nested task per brand, with a configurable concurrency.
  3. CUse one notebook with a Python loop inside a single task: for brand in brand_list: process_brand(brand). This is preferred over the for_each construct, as in-task processing avoids the scheduling overhead of 12 separate tasks and costs less for small workloads.
  4. DUse a Lakeflow Spark Declarative Pipeline with a @dlt.table function that takes a brand parameter. Run it in CONTINUOUS mode with the brand names as a JSON array in the pipeline configuration, and the pipeline fans out one parallel run per brand.
  5. EUse a multi-task job with 12 tasks defined in the job YAML with depends_on: [], each referencing the shared notebook with a different brand value in its task parameters. The static tasks run in parallel and need no iteration construct such as for_each.
Show answer & explanation

Correct answer: B

WHY B: This is the canonical Databricks pattern for dynamic parallel fan-out. The for_each task iterates over a dynamic input list (defined at run time or in the job configuration) and invokes a child task (notebook, Python script, etc.) for each item, with configurable concurrency. This is more flexible than static multi-task definitions because the brand list can change without modifying the job definition. The parameterized notebook reads brand via dbutils.widgets.get() and uses Python control flow (if/elif or a dispatch dictionary) to select brand-specific logic. WHY NOT A: Maintaining 12 separate jobs is the anti-pattern that the refactoring is trying to eliminate. A parent orchestration job using shell tasks adds unnecessary complexity. WHY NOT C: A for loop within a single task processes brands sequentially, eliminating parallelism. The 12 brands may have large independent datasets, and parallel execution is explicitly required. WHY NOT D: Lakeflow SDP does not support per-brand fan-out from a single pipeline config. Pipelines are defined as a single DAG; there is no built-in @dlt.table parameter mechanism for running parallel pipeline instances. WHY NOT E: A static definition with 12 hardcoded tasks is inflexible: adding a 13th brand requires modifying the job definition. The for_each construct handles dynamic lists without job definition changes.

Take the full DE Professional practice test →

From the free full-length test

14 more Developing Code for Data Processing using Python and SQL questions with answers

Each opens on its own page with the correct answer and the full explanation.

All free Data Engineer Professional exam questions & answers →