constraints are not creating on materialized views

[diliprreddy]

Hi,

We are loading data from the Bronze layer to the Silver layer and creating materialized views with primary key and foreign key constraints. We have a total of 33 dependent tables, and the load is being performed using a Spark Declarative Pipeline (SDP).

During the load, we are intermittently receiving the following error

Update b1cc4f is FAILED unexpectedly. Please contact Databricks support.

Has anyone experienced this issue before? If so, could you please share any information or recommendations that might help us troubleshoot it?

Any assistance would be greatly appreciated. Thank you.

[ThomazNeto]

Hi,

"FAILED unexpectedly, please contact Databricks support" is the generic wrapper, the real cause is in the event log

SELECT timestamp, level, message, error, details FROM event_log('your-pipeline-id') WHERE error IS NOT NULL OR level = 'ERROR' ORDER BY timestamp DESC;

Only the pipeline's run-as user can query it by default, so run it as that identity or publish the event log to a UC table in the pipeline settings.
https://docs.databricks.com/aws/en/ldp/monitor-event-logs

On the constraints themselves, a few things from the docs that tend to trip people up with 33 interdependent MVs:

- PK/FK on materialized views are supported in SQL and Python, but "the constraints are informational and are not enforced", and the pipeline must be Unity Catalog-enabled.
- In pipeline MVs you have to write NOT NULL together with PRIMARY KEY, otherwise the statement isn't valid. Regular Delta tables imply NOT NULL, pipelines don't.
- A foreign key must reference a table that already has a PK, and you need SELECT on it. The official example references a table outside the pipeline (main.default.customers). I couldn't find anything in the docs guaranteeing ordering when the FK points to another MV created in the same update, and the event log schema has a warning message for "cycle in the set of foreign key constraints" among pipeline tables. If your FK graph has cycles or points at MVs that haven't been created yet in that run, that's a likely suspect for intermittent failures.
https://docs.databricks.com/aws/en/ldp/developer/ldp-sql-ref-create-materialized-view
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-table-constraint
https://docs.databricks.com/aws/en/tables/constraints

A quick test that isolates it: drop the FOREIGN KEY clauses, keep the PKs, run twice. If the failures stop, the issue is the FK dependency, and since they're informational anyway you lose nothing at runtime. Then add them back for the tables whose parent is outside the pipeline or clearly upstream, and check the event log deprecation warnings for the cycle message.

If the event log shows an internal error with no useful message even then, that's the moment for the support ticket, with the update id and the error JSON attached.

Hope this helps.