Hi Team, I had few questions around data quality regarding 2 aspects and wanted to get suggestions or how others are doing it: Pinot related issues: How can we guarantee Pinot actioned/persisted all the rows that were provided during ingestion. For offline tables it is easier since the complete segment gets pushed and we can check for segments presence in the table. But for realtime, how do we guarantee data completeness wrt upstream? For non Pinot related issues: We have third eye for anomaly detection and other anomaly detection tools can be integrated but can we do a reconciliation between some source truth data and Pinot table without putting too much query load on Pinot table.

Pinot: 1. You can see the latest offset that’s’ been ingested using the consumer lag API -https://startree.ai/blog/apache-pinot-tm-0-12-consumer-record-lag 2. If there is a failure to ingest a record, the ingestion pipeline will throw an exception and then stop ingesting. You can override that behaviour if you want to. Non Pinot: It might be better to take that question to the StarTree Slack, which has a ThirdEye channel. There are more ThirdEye experts over there as well https://dev.startree.ai/slack-invite

Real-time ingestion also checkpoints offset and commits segment to deep store. If server restarts for any reason, it will start consuming from previously saved checkpoint, hence no data loss

Thanks Mark, Mayank, We are using the lag metrics and it helps us understand lag wrt upstream. I believe we can also look at the failure to ingest metrics, I could not find these in the monitoring page. We will still have challenges where a server restarts and the metrics are re-emitted, implying a higher number of records ingested.

@Rohit Yadav not sure if I understood the last statement. Are you relying on metrics to count events ingested? It may have duplicates due to server restart re-ingesting from previous checkpoint

I wouldn't be using it due to the same reason. My requirements are coming from the fact, how can I show the data completeness in Pinot compared to upstream for a time range. A way to represent this fact mathematically would be:

number of events processed(success+failures) / number of events ingested

for a time range. Duplicates wouldn't affect this metric.

Is this a real-time only table? Simple count(*) will show the number of events ingested in Pinot. By design, the only events dropped from ingestion are ones that Pinot may not be able to parse.

Can be a hybrid table, but realtime table is under question here since its tricky. I think we can rely on Pinot's design that it either parses and persists or fails to parse and skips processing/persisting the record. We employ the count(*) as well. Do you think a single metric as above to represent completeness per time bucket can be a value add? Since its going to be a percentage, server restarts should not affect the metric.

You can still achieve that check with count(*) group by.

If there is a metric, visualisation tools/alerting tools can be used easily otherwise I need to run count(*) queries against the table every time to measure data completeness. The kafka consumers guarantee completeness via checkpointing, the metric is capturing Pinot's completeness i.e. the record was actioned(either success or failure). If Pinot guarantees this then this metric will always be 100% and is useless.

Yes, these are guarantees that come with the design. What’s your requirement behind checking this? Is it just to make sure there is no data loss or is there a business reason behind it?

Both, the business reason is, we keep track of completeness and freshness metrics for datasets and Pinot is a sink in most cases and wanted to extend our current definitions of completeness and freshness.

@Mayank a side question is - for segments which are still in consuming state and if we have servers running in aws spot instances, which goes down. we cant recover those segments, right?