topic Error when running OPTIMIZE on a Delta table with generated columns in Data Engineering

Error when running OPTIMIZE on a Delta table with generated columns

jericksoncea — Mon, 20 Mar 2023 16:55:31 GMT

I am seeing an error when running OPTIMIZE on a Delta table with generated columns:

com.databricks.sql.transaction.tahoe.schema.DeltaInvariantViolationException: CHECK constraint Generated Column (created <=> now()) violated by row with values:

- created : 1672360020111000

Using a cluster with runtime 11.3 LTS (includes Apache Spark 3.3.0, Scala 2.12)

Any ideas how I can successfully OPTIMIZE this table?

Re: Error when running OPTIMIZE on a Delta table with generated columns

Ajay-Pandey — Mon, 20 Mar 2023 18:47:51 GMT

Have you applied any constraints on your table?

As it's giving errors for constraint

Re: Error when running OPTIMIZE on a Delta table with generated columns

jericksoncea — Tue, 21 Mar 2023 05:39:09 GMT

It appears the constraint is related to the generated column:

https://docs.databricks.com/delta/generated-columns.html

"For example, you can automatically generate a date column (for partitioning the table by date) from the timestamp column; any writes into the table need only specify the data for the timestamp column. However, if you explicitly provide values for them, the values must satisfy the constraint (<value> <=> <generation expression>) IS TRUE or the write will fail with an error."

Re: Error when running OPTIMIZE on a Delta table with generated columns

Anonymous — Wed, 22 Mar 2023 03:06:52 GMT

Hi @Jeff Erickson

Thank you for your question! To assist you better, please take a moment to review the answer and let me know if it best fits your needs.

Please help us select the best solution by clicking on "Select As Best" if it does.

Your feedback will help us ensure that we are providing the best possible service to you.

Thank you!