Is it possible to have a custom plugin specify a “last updated” date that is only updated if there are any changes to the data? Right now, the cq-sync time is set, but that is just when the process ran. I want to be able to pull deltas.
Yes, it is! The functionality that you are looking for is what we call an incremental table. Here are the docs for how to implement that in your own custom plugin: Incremental Tables Documentation
Let us know if you have any questions or issues!
Well… so… will this check the state of existing rows and just leave them if they aren’t touched?
Also, do you have any docs for how to do this in JS? It seems like incremental tables are more for things that the source API can tell you it hasn’t been touched… like for example aws_cloudtrail_events… that seems easy because it’s all new events.
But if I’m looking at something like an ec2instance, well that might have changed in a bunch of ways…
What I care about is being able to tell if the local data in my destination has been changed or not.
You are correct in that incremental tables are designed to leverage a source API that can return the items that have changed since a specific date.
At this time, this functionality is only available in the Go and Python (we just added support in the last few weeks) SDKs. If you want to see incremental table support in the JS SDK, I would suggest opening a GitHub issue: Feature Request.
Theoretically, you could have a custom destination plugin that only does an update if a field other than the _cq* fields change. In Postgres, you can leverage the conditional update mechanism to achieve this. Example of a conditional upsert: PostgreSQL Upsert with a Where Clause.
As an example in the Postgres Destination, here is where it generates the update statement: Postgres Destination Update Statement.
Thanks for creating the issue at GitHub.
Additionally, this would be a nice extension to the PostgreSQL destination plugin.
If you could open a feature request for the Postgres destination, it would be helpful as well!
Done… Hopefully the ask is clear enough GitHub Issue #18643