Misleading suggestions when updating table schema

[jodatafy]

Is your feature request related to a problem? Please describe

What up bro

I have a large fact table with many partitions that have had a new column added recently. For all historic partitions this column will remain empty (as we do not have the historic records). For any rows in partitions going forward, the column will be populated. We do not ever plan to reprocess the historic partitions.

However, when updating the table schema, the new column is not recognized as present (which it is, but only in the most recent partition), and Tabular Editor suggests to remove it. This also happens when the update table schema function is executed by right-clicking on the new partition.

See image:

Describe the solution you'd like

Could you make it so that update table schema takes all partition definitions into account when using it on the table level? And if using it on a specific partition, then suggest based on the content of that partition?

Goals

1. Suggest appropriate actions when updating table schema on existing facts based on context in which it is used

Background

Alternative solution: Can easily be worked around by adding the column manually

Additional context

No response

[otykier]

Hi @jodatafy
What you're seeing is caused by the fact that Tabular Editor uses the partition expression on the first partition defined on the table (in metadata order), to determine the schema of the table.

I was actually not aware that it's possible to have a queryable column that is only populated by some of the partitions on a table - but I just tested it, and to my surprise it works fine. However, be aware that if you ever want to reprocess any of the "old" partitions, you must add the same column to the partition query - otherwise the processing of those partitions are going to fail with an error such as in the screenshot below:

While I don't see a good way for Tabular Editor 3's Schema Update feature to consider all partitions on a table, we can certainly add a mechanism that allows you to specify one partition as the "primary" partition, which is then the partition that will be used for all schema update operations at the model/table level. In practice, we'll probably just implement that using an annotation.

Does that solution sound workable?

[jodatafy]

Yup!