ElasticSearch Transform Not Working

Hello, hope you can help me with a transform issue. I have this sales index that as an id, timestamp and value fields and Im working on another index (sales-latest) that dynamically searches for unique ids and the most recent timestamps of that id (all this from the sales index). I'm anexing the scripts below:

PUT sales-latest
{
  "mappings": {
    "properties": {
      "id": { "type": "keyword" },
      "timestamp": { "type": "date" }
    }
  }
}

PUT _transform/sales-latest
{
  "frequency": "5s",
  
  "sync": {
    "time": {
       "field": "timestamp",
       "delay": "60s"
     }
  },
  
  "source": {
    "index": "sales"
  },
  "dest": {
    "index": "sales-latest"
  },
  "pivot": {
    "group_by": {
      "id": {
        "terms": {
          "field": "id"
        }
      }
    },
    "aggregations": {
      "timestamp": {
        "max": {
          "field": "timestamp"
        }
      }
    }
  }
} 

When I execute the _start query it works the first time but everytime I insert a new id or an entry with a more recent timestamp for a specific id it does not update.
Could you help me?

Hi @loverissimo Welcome to the community.

What you are describing sounds like a latest transform, not a pivot transform

If you want a resulting index that shows the last time a transaction was made by id, that is a latest transform ... it will keep the whole entry so you will also have the values of that latest entry.

So in your case that would look something like this... I would use the UI the first time.

PUT _transform/sales-latest
{
  "source": {
    "index": [
      "sales"
    ],
    "query": {
      "match_all": {}
    }
  },
  "dest": {
    "index": "sales-latest"
  },
  "latest": {
    "unique_key": [
      "id"
    ],
    "sort": "@timestamp"
}

Hi, thank you for the response. That is not quite what I'm looking for, I want the transform to dynamically update the new index everytime a new entry is saved in the source index, I tried thta script and it did not worked. It is probably my Basic License

If you look in the documentation around transform limitations you can see that even continuous transforms run on a schedule. Updates will therefore most of the time come with a delay.

I have tried waiting for the 60s delay to even 5min. Does it usually take more than that?