Feature request: aws_ses source, add a sending_metrics table from BatchGetMetricData

[jakesciotto]

Feature request

Add a sending_metrics table to the aws_ses source, built on BatchGetMetricData. This is the only SESv2 path to send, delivery, bounce, complaint, open and click data.

Why this matters: customers ask the aws_ses source for sending outcomes, not configuration. SESv2 serves no per-message data, because SES publishes per-message events to an event destination such as SNS, Firehose or EventBridge. BatchGetMetricData is the exception. It returns those outcomes as daily aggregates, which answers most of the question without an event pipeline.

Depends on: the request-body signing work, because this operation is POST. A separate issue covers that.

Every shape below comes from botocore's bundled service model, sesv2/2019-09-27.

The API contract

POST /v2/email/metrics/batch, with a Queries list. Each query requires Id, Namespace, Metric, StartDate and EndDate, and accepts up to 3 Dimensions.

• Namespace accepts one value, VDM. So the account must enable Virtual Deliverability Manager. Verify this against a live account, because the enum alone does not prove the subscription requirement.
• Metric accepts SEND, COMPLAINT, PERMANENT_BOUNCE, TRANSIENT_BOUNCE, OPEN, CLICK, DELIVERY, DELIVERY_OPEN, DELIVERY_CLICK and DELIVERY_COMPLAINT.
• Dimensions is a map. Its keys accept EMAIL_IDENTITY, CONFIGURATION_SET and ISP.
• A request carries at most 10 queries. AWS documents 16 requests per second, and 160 queries per second cumulative.
• The response returns Results and Errors. Each result holds an Id, plus parallel Timestamps and Values arrays.

Table design

This table is a synthesis, not a mirror of one response. So the design needs a decision, and here is the proposal.

• One row per metric, dimension and day: metric, dimension_name, dimension_value, timestamp, value.
• primary_key=["metric", "dimension_name", "dimension_value", "timestamp"].
• Enumerate the dimension values from the tables the source already syncs. Fan out over ListConfigurationSets for CONFIGURATION_SET, and over ListEmailIdentities for EMAIL_IDENTITY. Run all 10 metrics for each value.
• Leave ISP out of the first version. No list operation enumerates ISP values, so there is nothing to fan out over.
• Batch the queries 10 at a time, and give each query an Id that maps back to its metric and dimension pair. The response returns Id only, so the mapping must survive the round trip.
• Read Errors on every response. A partial failure must fail the sync or log the dropped queries. It must not silently write a short table that reads as a real decline in sending.

Sync behavior

StartDate and EndDate are real request parameters, so this table is genuinely incremental on timestamp. It is the second such table in this source, after suppressed_destinations.

• Add an INCREMENTAL_FIELDS entry on timestamp.
• Reuse the INCREMENTAL_OVERLAP rewind of one day. Merge on the primary key absorbs the re-read rows.
• Confirm the maximum lookback window that AWS allows, and clamp StartDate to it. A first sync must not request a range the API rejects.

Definition of done

1. The table appears in the schema picker, and syncs rows against a live AWS account with VDM enabled.
2. The schema picker reports a clear reason when VDM is off, in the same way endpoint_permission_reason reports an AccessDeniedException.
3. A unit test asserts the query batching, the Id round trip, and the Errors handling.
4. A unit test asserts the incremental window, including the first sync with no watermark.
5. SOURCES.md and the posthog.com source doc describe the table and its VDM requirement.

Implementation guide: .agents/skills/implementing-warehouse-sources/SKILL.md.

Related: #86804 adds the request-body signing this table needs. #86803 covers the GET-reachable tables.