Schema changes notification

[pharma70]

Hi all,

Sometimes an upstream source adds a new column without letting us know. We do not want the Databricks workflow to fail when this happens, but we would like to be notified so we can review the new column and decide whether to include it in our processing later.

Is there a Databricks-native way to detect and alert on schema changes, while allowing the pipeline to continue running normally?

[Satyasai]

Write a Databrciks Alerts with following sql server Query

SELECT
event_time,
user_identity.email AS triggered_by,
request_params.table_full_name AS table_name,
action_name,
request_params
FROM system.access.audit
WHERE action_name = 'addColumns'
AND request_params.table_full_name = 'catalog.schema.bronze_table'
AND event_time >= NOW() - INTERVAL 1 DAY;

Navigate to SQL editor -> Alerts -> Create Alert.

Set alert conditions to trigger when query result has Rows > 0.

Configure alert notifications to be delivered via Email, Slack, Microsoft Teams or Webhook/PagerDuty.

[suryaprayaga]

This is a good question. Databricks Auto Loader specifically supports a "rescue schema-evolution" mode where new columns don't cause the stream to fail; unexpected fields are placed into a rescued-data column. 

Besides we could safely introduce what is called as "Schema Change Detection Layer" that detects what changed between the incoming data stream vs the expected / approved schema. Take a look at a simple scenario like this:

(Expected Schema - Incoming Schema) --> New columns, Removed Columns, Schema changes, Renamed columns.

We could simply store such results in an audit table something with the following structure:

Though this capture is per-se not native within Datbaricks (others can correct me), you can perhaps use a native Databricks ability to do notification via Slack or Pager Duty. 

Thanks

SP

[ivanvyd]

@pharma70 if this ingestion is using Auto Loader, cloudFiles.schemaEvolutionMode = "rescue" seems like a good fit here. In that mode Auto Loader keeps the stream running, does not evolve the schema, and puts new fields into _rescued_data.

You can then alert on recent bronze rows where _rescued_data IS NOT NULL. If this runs through Lakeflow Jobs, the alert can be added as a SQL Alert task after ingestion. One useful detail is that a triggered alert still reports the task as Succeeded; the task only fails if alert evaluation itself errors.

_rescued_data also captures type and case mismatches, so for a strict "new column" notification I'd filter the rescued fields against the approved schema first.