[source-amazon-seller-partner] Reports not generating proper start & end dates

[thedannywilcox]

Connector Name

source-amazon-seller-partner

Connector Version

4.2.3

What step the error happened?

Configuring a new connector

Relevant information

This is a follow up from #36543, as it seemed to help initially but after more testing, I don't believe the issue is actually resolved.

I have been trying to get this connector setup but running into various problems getting report data. I was able to get it setup using the code below but on the following scheduled runs, the connector hasn't succeeded since. The failure bounces between several reports:

• GET_VENDOR_NET_PURE_PRODUCT_MARGIN_REPORT
• GET_VENDOR_TRAFFIC_REPORT
• GET_BRAND_ANALYTICS_SEARCH_TERMS_REPORT

Ultimately, I would like to not define a start date and get data 2 years back, as the documentation suggests is the case; however, for getting off the ground I've been focusing on getting recent data (though I would think the solution to this issue would resolve the historical backfill problem I've been running into, as it seems related to the start/end dates.)

TL;DR on this code, I have a list of regions I want to pull data from and stored the credentials in AWS secrets manager. The failure occurs in all 3 regions, impacting the same few reports in all regions. Oddly, the code worked fine at 2PM on Friday Pacific time but then failed at 5:20PM or so when the cron is scheduled to run. I have not seen a successful run since the initial attempt.

resource "airbyte_connection" "multi_region" {
  for_each                                      = local.amazon_seller_partner_region_map
  data_residency                           = "auto"
  destination_id                             = var.airbyte_s3_destination_id
  name                                            = "Amazon Seller Partner - ${upper(each.key)} → S3"
  namespace_definition                = "custom_format"
  namespace_format                     = "amazon_seller_partner"
  non_breaking_schema_updates_behavior = "propagate_fully"

  prefix         = "${upper(each.key)}_"
  source_id  = airbyte_source_amazon_seller_partner.multi_region[each.key].source_id
  status        = "active"

  configurations = {
    streams = [
      {
        name      = "VendorOrders"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_BRAND_ANALYTICS_MARKET_BASKET_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_BRAND_ANALYTICS_SEARCH_TERMS_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_VENDOR_SALES_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_VENDOR_NET_PURE_PRODUCT_MARGIN_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_BRAND_ANALYTICS_REPEAT_PURCHASE_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_VENDOR_TRAFFIC_REPORT"
        sync_mode = "incremental_append"
      },
      {
        name      = "GET_VENDOR_INVENTORY_REPORT"
        sync_mode = "incremental_append"
      }
    ]
  }

  schedule = {
    schedule_type   = "cron"
    cron_expression = "0 23 * * * ? UTC"
  }
}

resource "airbyte_source_amazon_seller_partner" "multi_region" {
  for_each = local.amazon_seller_partner_region_map
  configuration = {
    account_type    = "Vendor"
    aws_environment = "PRODUCTION"

    lwa_app_id           = local.amazon_seller_partner_secret_map[each.key].lwa_app_id
    lwa_client_secret = local.amazon_seller_partner_secret_map[each.key].lwa_client_secret
    refresh_token       = local.amazon_seller_partner_secret_map[each.key].refresh_token

    region = upper(each.key)
    replication_start_date = "2024-05-05T00:00:00Z"

    report_options_list = [
      {
        stream_name  = "GET_BRAND_ANALYTICS_REPEAT_PURCHASE_REPORT"
        options_list = [local.weekly_report_option_default]
      },
      {
        stream_name = "GET_VENDOR_SALES_REPORT"
        options_list = [
          local.daily_report_option_default,
          {
            option_name  = "distributorView"
            option_value = "MANUFACTURING"
          },
          {
            option_name  = "sellingProgram"
            option_value = "RETAIL"
          }
        ]
      },
      {
        stream_name = "GET_VENDOR_INVENTORY_REPORT"
        options_list = [
          local.daily_report_option_default,
          {
            option_name  = "distributorView"
            option_value = "MANUFACTURING"
          },
          {
            option_name  = "sellingProgram"
            option_value = "RETAIL"
          }
        ]
      },
      {
        stream_name  = "GET_BRAND_ANALYTICS_MARKET_BASKET_REPORT"
        options_list = [local.daily_report_option_default]
      },
      {
        stream_name  = "GET_BRAND_ANALYTICS_SEARCH_TERMS_REPORT"
        options_list = [local.daily_report_option_default]
      },
      {
        stream_name  = "GET_VENDOR_NET_PURE_PRODUCT_MARGIN_REPORT"
        options_list = [local.daily_report_option_default]
      },
      {
        stream_name  = "GET_VENDOR_TRAFFIC_REPORT"
        options_list = [local.daily_report_option_default]
      }
    ]
  }
  name         = "Amazon Seller Partner - ${each.key}"
  workspace_id = var.airbyte_workspace_id
}

data "aws_secretsmanager_secret" "amazon_seller_partner" {
  for_each = local.amazon_seller_partner_region_map
  name     = "airbyte/source/amazon-seller-partner-${lower(each.key)}"
}

data "aws_secretsmanager_secret_version" "amazon_seller_partner" {
  for_each  = local.amazon_seller_partner_region_map
  secret_id = data.aws_secretsmanager_secret.amazon_seller_partner[each.key].id
}

locals {
  weekly_report_option_default = {
    option_name  = "reportPeriod"
    option_value = "WEEK"
  }
  daily_report_option_default = {
    option_name  = "reportPeriod"
    option_value = "DAY"
  }
  amazon_seller_partner_region_map = { for r in var.amazon_seller_reports.regions : r => r }
  amazon_seller_partner_secret_map = { for r in values(local.amazon_seller_partner_region_map) : r => jsondecode(data.aws_secretsmanager_secret_version.amazon_seller_partner[r].secret_string) }
}

variable "amazon_seller_reports" {
    default = {
       regions = ["UK", "DE", "FR"]
  }
  type        = object({
    regions = list(string)
  })
}

Here are the full logs for the run after a successful completion.

amazon-seller-partner-logs.txt

However, in these logs, I see GET_BRAND_ANALYTICS_SEARCH_TERMS_REPORT is succeeding but in the run directly after these 10 failed attempts, it starts failing too. Happy to provide those logs if needed but I suspect it should be pretty easy to reproduce this issue using the terraform provided.

Relevant log output

airbyte_cdk.utils.traced_exception.AirbyteTracedException: Failed to retrieve the report 'GET_VENDOR_NET_PURE_PRODUCT_MARGIN_REPORT' for period 2024-05-11T00:00:00Z-2024-05-12T11:51:30Z. This will be read during the next sync. Error: {'errorDetails': 'Error in report request: The report data for the requested date range is not yet available. Try submitting this createReport request at a later time, or modify dataEndTime to be an earlier date.'}

airbyte_cdk.utils.traced_exception.AirbyteTracedException: Failed to retrieve the report 'GET_VENDOR_TRAFFIC_REPORT' for period 2024-05-11T00:00:00Z-2024-05-12T11:52:01Z. This will be read during the next sync. Error: {'errorDetails': 'Error in report request: The report data for the requested date range is not yet available. Try submitting this createReport request at a later time, or modify dataEndTime to be an earlier date.'}

Failed to retrieve the report 'GET_BRAND_ANALYTICS_SEARCH_TERMS_REPORT' for period 2024-05-11T00:00:00Z-2024-05-12T05:01:53Z. This will be read during the next sync. Error: {'errorDetails': 'A client error occurred. Please double check that your parameters are valid and fulfill the requirements of the report type.'}

Contribute

• Yes, I want to contribute