Issue with updation of record in latest Transform

Elastic Stack Elasticsearch
1

We are using Elasticsearch latest transforms to fetch latest records in our index based on sync time as ingest.time and sort as extractDatetime.
The transformation involves fields like extractDatetime and ingest.time, where extractDatetime is supposed to represent the time when the data was extracted and ingest.time is the time when data pushed to elasticsearch.

  • We have a record with same key an extractDatetime of Aug 20, 2024 @ 18:30:54.572 and an ingest.time of Aug 21, 2024 @ 10:57:00.468. This record is correctly stored and updated in our index.

  • We have a record with same key an extractDatetime of Aug 20, 2024 @ 18:30:54.572 and an ingest.time of Aug 21, 2024 @ 10:57:00.468. This record is correctly stored and updated in our index

  • Current Behavior: Records with an extractDatetime that is older than an existing record are replacing the existing records with newer extractDatetime values.

  • Expected Behavior: Records should only update existing entries if their extractDatetime is greater than the extractDatetime of the existing record.

Please refer the transform config

{
  "source": {
    "index": [
      "sample-v3"
    ],
    "query": {
      "match_all": {}
    }
  },
  "dest": {
    "index": "sample-v2"
  },
  "frequency": "2m",
  "sync": {
    "time": {
      "field": "ingest.time",
      "delay": "120s"
    }
  },
  "latest": {
    "unique_key": [
      "id",
      "usecase1",
      "usecase2"
    ],
    "sort": "extractDatetime"
  },
  "settings": {},
  "retention_policy": {
    "time": {
      "field": "extractDatetime",
      "max_age": "30d"
    }
  }
}

Is there a known issue with the latest transform functionality that could cause records with older extractDatetime values to replace those with newer values?
Could the sync.time configuration or delay settings be influencing this behavior?
What additional checks or configurations are recommended to ensure correct record updates?

2

By default sort order for extractDateTime is ascending. Can't we use in descending order?
Even in ascending order it is storing the data based on ingest.time. Could someone explain how sort will work for extractDateTime ?

I have tried both the case by sending older extractDateTime and newer as well, In both the cases it has stored the data based on ingest.time

3

Hi,
I have checked in the documentation

This method populates the destination index with the latest documents for each unique key value. For example, you might want to find the latest orders (sorted by the order_date field) for each customer or for each country and region.

In our case, the order is not determined by extractDateTime. Instead, any data with older or newer extractDateTime values will still be processed and ingested.