feat(aws)!: Define composite primary key for regions (#12415)

smokentar · web-flow · commit 681ea9731fc1 · 2023-07-19T16:44:20.000Z
#### Summary This PR is aimed to address #12414 by creating a composite primary key on `aws_regions` table comprising `account_id` and `region`. With this key there will be one set of regions (27) per discovered AWS Account. The reason why we're not limiting them to only one set of regions for all accounts is that different AWS Accounts can have different `opt_in_status` / `enabled` values for different regions.
diff --git a/plugins/source/aws/resources/services/ec2/regions.go b/plugins/source/aws/resources/services/ec2/regions.go
@@ -21,7 +21,7 @@ func Regions() *schema.Table {
 		Multiplex:   client.AccountMultiplex(tableName),
 		Transform:   transformers.TransformWithStruct(&types.Region{}),
 		Columns: []schema.Column{
-			client.DefaultAccountIDColumn(false),
+			client.DefaultAccountIDColumn(true),
 			{
 				Name:     "enabled",
 				Type:     arrow.FixedWidthTypes.Boolean,
@@ -33,9 +33,10 @@ func Regions() *schema.Table {
 				Resolver: client.ResolveAWSPartition,
 			},
 			{
-				Name:     "region",
-				Type:     arrow.BinaryTypes.String,
-				Resolver: schema.PathResolver("RegionName"),
+				Name:       "region",
+				Type:       arrow.BinaryTypes.String,
+				Resolver:   schema.PathResolver("RegionName"),
+				PrimaryKey: true,
 			},
 		},
 	}
diff --git a/website/tables/aws/aws_regions.md b/website/tables/aws/aws_regions.md
@@ -4,18 +4,18 @@ This table shows data for Regions.
 
 https://docs.aws.amazon.com/AWSEC2/latest/APIReference/API_Region.html
 
-The primary key for this table is **_cq_id**.
+The composite primary key for this table is (**account_id**, **region**).
 
 ## Columns
 
 | Name          | Type          |
 | ------------- | ------------- |
-|_cq_id (PK)|`uuid`|
+|_cq_id|`uuid`|
 |_cq_parent_id|`uuid`|
-|account_id|`utf8`|
+|account_id (PK)|`utf8`|
 |enabled|`bool`|
 |partition|`utf8`|
-|region|`utf8`|
+|region (PK)|`utf8`|
 |endpoint|`utf8`|
 |opt_in_status|`utf8`|
 |region_name|`utf8`|
