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.

[anuj_lathi]

That message is a generic "internal failure" surface, not a specific cause. The real reason is almost always in the pipeline event log or the update details, and if it isn't, it's something Databricks support needs to look at using the update ID.

1. Pull the actual error from the event log

Open the failed update in the pipeline UI and check the event log for the first ERROR event, not the final "FAILED unexpectedly" one. You can also query it:

SELECT timestamp, level, event_type, message, error
FROM event_log("<your-pipeline-id>")
WHERE level = 'ERROR'
  AND origin.update_id LIKE 'b1cc4f%'
ORDER BY timestamp;

Check whether the failure is tied to one specific flow or table, or whether it varies from run to run. With 33 interdependent tables, "intermittent" often means it's always the same few objects failing at a particular stage (planning, constraint application, or table update).

2. Isolate the PK/FK constraints as a variable

Since constraints are the unusual part of your setup, test them directly:

• Remove the FK constraints from the MV definitions (keep the PKs) and run the pipeline several times. If the failures stop, add the FKs back in batches to find the offending relationship.
• Check the dependency chain. An FK needs a PK on the referenced table, so every referenced MV must define its PK. Cross-references among your 33 tables can create an ordering problem if the parent isn't resolved before the child.
• Check the key columns. PK columns must be NOT NULL. Make sure the column types on both sides of each FK match exactly.
• Remember that PK/FK constraints on these tables are informational, not enforced, so they won't fail a load because of bad data. If they're breaking the update, it's a metadata or planning problem, not a data-quality one.

3. Reduce the blast radius

• Run a full refresh on a subset of the tables (or a copy of the pipeline with only a few of them) to see whether the failure correlates with the size or depth of the dependency graph.
• Look at whether failures coincide with other jobs touching the same catalog/schema, or with changes to upstream Bronze tables mid-run. Intermittent failures often point to concurrency or a race with something else.
• Make sure you're on the current channel for the pipeline (check channel in the settings). Internal failures are often fixed in newer releases, so test whether the issue reproduces there.

4. Open a support ticket with the right details

If the event log doesn't give a concrete cause, which is likely when the message says "unexpectedly", open a ticket and include:

• Pipeline ID and the failing update ID (b1cc4f…, full ID)
• Workspace ID and region
• Timestamps of a few failed and successful runs
• Whether the failures disappear when FK constraints are removed (this is very useful to the engineers)

Recommendation

Start with the first ERROR event in the event log, then run the test with FKs removed. That will tell you quickly whether this is a constraint and ordering issue you can fix in your definitions, or an internal platform bug that needs support. If it's the latter, your isolation results will speed up the ticket considerably.