Recommended Deduplication process with AWS source plugin

General
1

Hi Guys,
I am trying to build a solution to create a history of changes to the given resource. It seems that only aws_cloudtrail_events supports incremental changes. I was playing around with the write_mode feature of the Postgres destination, but append mode keeps creating the same records, and overwrite mode does not keep history.
Let me know if there is any recommended approach from CloudQuery to handle the deduplication process elegantly. I am using AWS ECS Fargate with EventBridge to schedule CloudQuery periodically to fetch AWS resource details into AWS Aurora Postgres.

Thanks.
Ankit

2

Hi Ankit,

To make use of incremental syncing you need to pass a state backend in the sync config. An example of this configuration is available in our docs: Managing Incremental Tables | CloudQuery. Are you setting the backend_options field? Can you share a redacted version of your config?

3

Thanks, Marcel. It worked for only the aws_cloudtrail_events table from the AWS plugin, but I am also looking for something similar for other tables from the AWS plugin. As per the plugin documentation, it only supports “aws_cloudtrail_events” as an incremental type; hence, we are trying to find a custom solution to achieve similar results for other tables from the AWS plugin.

Here is my config-

kind: source
spec:
  # Source spec section
  name: aws
  path: "cloudquery/aws"
  registry: "cloudquery"
  version: "v27.24.0"
  spec:
    regions: ["us-east-1","ap-southeast-1"]
    use_paid_apis: true
  tables: ["*"]
  destinations: ["postgresql"]
  backend_options:
     table_name: "cq_state_recuperer"
     connection: "@@plugins.postgresql.connection"
---
kind: destination
spec:
  name: postgresql
  path: cloudquery/postgresql
  registry: cloudquery
  version: "v8.2.4"
  write_mode: "overwrite"
  migrate_mode: "forced"
  spec:
    connection_string: ${PG_CONNECTION_STRING}

Thanks again.

4

Unless tables support incremental syncing there is no way to incrementally sync the data they expose. Not all AWS endpoints support querying by fields we’d need to make this work.

With write_mode: append for tables that are not incremental CloudQuery will resync all the records available in that table. You could use this to create data partitions that track the history of records that were synced.