Hi, Is there a way to get rid of constraint over timeColumn that it must always be between

1971-01-01 UTC

to

2071-01-01 UTC

? We do have usecases where we only have one timeColumn in the data and its values are outside this range.

You have data before 1970? Is that correct data or epoch 0 defaulted?

We have data from before 1970. This will be negative epoch number

Hmm, may be use a dimension instead then? I am guessing you won't need to use time column for retention etc ?

It's an offline table with 10000 segments based on 10000 locations. We always update certain segments in place when we get new data and take care of clearing out deprecated rows in our ETL pipeline So we don't need Pinot to manage retention here. We haven't yet tried dimension tables as they must be small in size as per docs.

Yeah, then go with dimension and REFRESH

Is it possible skip timeColumn by just using segmentIngestionType : REFRESH ? And the only downside will be that there is no retention? Our table is almost 100GB and I would want to avoid using dimensional tables. Sorry I am out of office and will try it tomorrow

Not dimension table, use that column as dimension

Nope.

Yeah then it should be ok, treating this column as dimension

Ah ok. We currently have this column in dateTimeFieldSpecs in schema. I will try adding it to dimensionFieldSpecs . We do have datetime logic in SQL queries. I will check tomorrow if we can figure something out for it. Thanks for your support

Thanks for the update

@piby Since you mention it as a drawback, I am curious as to what you would expect from a retention management feature in your use case? As I understand it, you seem to be updating the the table with with newer data for locations AFAICS you don't want to remove any data, no matter how old.

@Subbu Subramaniam We want rentention manager table disabled for tables like this. For this table for example, we usually get new data for 5 locations (out of 10k) at a time. So we update the corresponding 5 parquet files on S3 and trigger segment creation and push task for only 5 segments. When our ETL pipeline modify the parquet file, it delete obsolete rows. The criteria of deletion is not based on time but rather on other business logic. So we don't want retention for this table from Pinot side. We also have many tables where we don't have a datetime column and we want to deploy non timeseries data on Pinot as well.

I think you used the word “drawback” which is causing the confusion. This is not a drawback, this is expected behavior from REFRESH tables, and works for your case also