Your error is caused by a missing field or column (date_of_birth#1554) in your Spark SQL logical plan, which is needed during query optimization. This issue happens only when running the notebook in a Workflow job because of subtle differences between the interactive and scheduled execution environments.

Root Cause

• NoSuchElementException: key not found: date_of_birth#1554 points to Spark not being able to resolve a required column, often due to schema mismatches or column references that exist in one environment but not the other.

• When running standalone in a notebook, your session might have some state (such as temporary tables, views, or cached data) that exists only in that interactive session. Those extras are missing in the clean workflow job context.

• The referenced column or alias (date_of_birth#1554) may be created dynamically or depend on upstream data not present or not refreshed when the job workflow runs. Temporary objects created in notebook sessions (CREATE OR REPLACE TEMP VIEW, etc.) do not persist to workflows unless specifically made global or permanent.

Common Reasons and Fixes

1. Temporary Objects Not Persisting

• Interactive notebooks often create temporary views or tables. These are not persistent across sessions. Workflow jobs run in new sessions where such objects are missing.

• Fix: Make sure all tables/views needed are global or permanent (e.g., use CREATE OR REPLACE GLOBAL TEMP VIEW or write to a real table).

2. Code Path Differences or Non-Deterministic Logic

• Sometimes code paths differ slightly based on job parameters or data presence, leading to missing columns when run as workflows.

• Fix: Ensure all branch logic is tested under both environments.

3. Overlapping or Out-of-Sync Schemas

• If your notebook creates temporary columns or changes schemas and relies on session state, those changes may not propagate to workflow jobs.

• Fix: Write robust pipelines that do not rely on stateful session changes and ensure all schema changes are reproducible.

4. Caching/Persistence Issues

• DataFrame or table caching in a notebook may mask missing/wrong columns.

• Fix: Avoid relying on cache for workflow jobs, or be explicit with refresh and cache commands.

5. Parameterization and Path Issues

• Jobs may run on different data or with different parameters, causing differences in available fields.

• Fix: Double-check all input data, table paths, and parameters.

Next Steps

• Audit your notebook for:

  • Any CREATE TEMP VIEW, CREATE TEMP TABLE, or similar commands.

  • Any assumptions about session state or cached data.

• After each key operation, verify output schemas—especially if using intermediate SQL functions/views.

• Test running your notebook from start-to-finish in a fresh environment (restart/clear state).

• Update all dependencies to ensure nothing is session-local unless it's a global/permanent table.