# Elastic Latest Transform is not working if sync time and sort time is different **URL:** \ **Category:** Kibana\ **Tags:** transforms\ **Created:** [March 8, 2024, 4:20am UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001 "2024-03-08T04:20:29Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![shivaraj\_kv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shivaraj_kv/32/126297_2.png) [@shivaraj\_kv](https://discuss.elastic.co/u/shivaraj_kv)\ **Post date:** [March 8, 2024, 4:20am UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/1 "2024-03-08T04:20:29Z")
```auto { "id": "poc.transform", "version": "7.17.11", "create_time": 1709818588566, "source": { "index": [ "poc.test.source*" ], "query": { "match_all": {} } }, "dest": { "index": "poc.test.transform" }, "frequency": "1m", "sync": { "time": { "field": "@timestamp", "delay": "60s" } }, "latest": { "unique_key": [ "entity_name.keyword" ], "sort": "occured_timestamp" }, "settings": { "max_page_search_size": 500 } } ``` Here I am trying to get the latest records from the source index and ingest the data into transformed index. Since I cannot depend on the ingestion time(@timestamp) to find the latest record, because of the possibility of non chronological insertion of data. I have a field called occured\_timestamp. From which I have to get the latest record. Hence I have added it to sort key. But this seems to be not working. No matter what is the value in occured\_timestamp. Data seems to be getting ingested into transformed index. Anything wrong with my code? Example Source Index ```auto [ { "entity_name":"enity1", "occured_timestamp":"2024-03-07T09:14:29.000Z" "@timestamp": "2024-03-07T14:02:59.000Z" }, { "entity_name":"enity1", "occured_timestamp":"2024-03-07T08:01:29.000Z" "@timestamp": "2024-03-07T14:03:59.000Z" }, { "entity_name":"enity1", "occured_timestamp":"2024-03-07T07:01:29.000Z" "@timestamp": "2024-03-07T14:04:59.000Z" } ] ``` Output Expected in destination Index ```auto [ { "entity_name":"enity1", "occured_timestamp":"2024-03-07T09:14:29.000Z" "@timestamp": "2024-03-07T14:02:59.000Z" }] ``` ---
**Author:** ![greco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greco/32/106815_2.png) [@greco](https://discuss.elastic.co/u/greco)\ **Post date:** [March 8, 2024, 10:21am UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/2 "2024-03-08T10:21:17Z")
Hi @shivaraj_kv , It seems you are using the _@timestamp_ field to keep the transform in sync, however, I'd recommend you to use the _occured\_timestamp_ field instead ( [see our doc](https://www.elastic.co/guide/en/elasticsearch/reference/current/put-transform.html) ). The "sync" part of your transform configuration should then look like this : ```auto "sync": { "time": { "field": "occured_timestamp", "delay": "60s" } ``` Also, where does you _occured\_timestamp_ field come from ? How old can it be (maximum difference betweeen _occured\_timestamp_ and _@timestamp_) ? ---
**Author:** ![shivaraj\_kv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shivaraj_kv/32/126297_2.png) [@shivaraj\_kv](https://discuss.elastic.co/u/shivaraj_kv)\ **Post date:** [March 8, 2024, 10:40am UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/3 "2024-03-08T10:40:54Z")
Since my unique key is entity\_name and occured\_timestamp for different entity\_name can be same or different and can come in any order. Let's say for entity1 occured\_timestamp can be today and for entity2 it can be yesterday sometime. Also the order of occured\_timestamp is not guaranteed. Is using the latest transform the right usecase or should we use pivot trabsform. If so how? ---
**Author:** ![greco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greco/32/106815_2.png) [@greco](https://discuss.elastic.co/u/greco)\ **Post date:** [March 8, 2024, 11:04am UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/4 "2024-03-08T11:04:45Z")
Did you try changing the sync to "occured\_timestamp" as I pointed ? You wrote > "No matter what is the value in occured\_timestamp. Data seems to be getting ingested into transformed index. " I think this is because you are using the _@timestamp_ field to check for new documents, you should change your transform configuration as follows : ```auto PUT _transform/poc-transform { "source": { "index": [ "poc.test.source*" ] }, "latest": { "unique_key": [ "entity_name.keyword" ], "sort": "occured_timestamp" }, "dest": { "index": "poc.test.transform" }, "sync": { "time": { "field": "occured_timestamp" } } } ``` ---
**Author:** ![shivaraj\_kv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shivaraj_kv/32/126297_2.png) [@shivaraj\_kv](https://discuss.elastic.co/u/shivaraj_kv)\ **Post date:** [March 8, 2024, 12:32pm UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/5 "2024-03-08T12:32:51Z")
> [@greco](#): > > Did you try changing the sync to "occured\_timestamp" as I pointed ? Yes, Tried. But it is skipping some values due to unordered ingestion of occured\_timestamp ---
**Author:** ![greco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greco/32/106815_2.png) [@greco](https://discuss.elastic.co/u/greco)\ **Post date:** [March 8, 2024, 1:37pm UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/6 "2024-03-08T13:37:03Z")
> But it is skipping some values due to unordered ingestion of occured\_timestamp If it's skipping old _occured\_timestamp_ for entities, I think that's an expected behavior as I understand you only want the latest timestamp for each entity. If it misses intermediate _entity\_names_ with more recent _occured\_timestamp_, you can mitigate that by lowering the frequency so that your transform checks for changes in the source index more often. If the transform is not lagging, the worst case delay you'll face is : query\_delay + frequency . Hope that helps ---
**Author:** ![greco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/greco/32/106815_2.png) [@greco](https://discuss.elastic.co/u/greco)\ **Post date:** [March 8, 2024, 1:44pm UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/7 "2024-03-08T13:44:19Z")
Another reason for missing update, as pointed by @przemekwitek[here](https://discuss.elastic.co/t/transform-is-only-partially-updated/351935/10) is the following : when the document is ingested into source index, its timestamp is "old", i.e.: older than `60s` (the configured delay) than the actual server timestamp. Yet that can be fixed by either : - using _@timestamp_ field in the `sync.time` property, as you did originally and with a lower frequency setting, - or, _@timestamp_ is not the ingested time, by setting up an ingest pipeline on the **source** index that will populate a new `event.ingested` field for every source document and then to use `event.ingested` field in the `sync.time` section of the transform config. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 5, 2024, 1:44pm UTC](https://discuss.elastic.co/t/elastic-latest-transform-is-not-working-if-sync-time-and-sort-time-is-different/355001/8 "2024-04-05T13:44:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.