focus table (AWS Glue) used by Focus Dashboard has no data from source accounts

Based on the information provided, it appears that you're experiencing an issue with the Focus Dashboard where the focus table in AWS Glue is not being populated with data from all source accounts, despite the crawler running successfully and processing the account folders.

This situation is similar to issues that have been encountered with other CID (Cloud Intelligence Dashboards) deployments. The root cause might be related to how the Glue crawler is interpreting the schema of the data in the S3 bucket.

Here are some potential steps to troubleshoot and resolve the issue:

1. Check the Glue crawler configuration: Ensure that the crawler is set to recognize new folders as partitions rather than new tables. You may need to set the table level to 2 in the crawler configuration, which would force it to recognize newer folders as partitions instead of tables.

2. Examine the schema differences: Compare the schema of the existing focus table with the schema of the new data being processed. If there are significant differences, the crawler might be trying to create new tables instead of updating the existing one.

3. Recreate the crawler: If the crawler has already established metadata for some account folders as separate tables, it may be necessary to delete and recreate the crawler to remove these stale associations.

4. Use the CID update command: Try running the command "cid-cmd update --force --recursive" in AWS CloudShell. This will update the dashboard to the latest version and recreate underlying resources, including the Glue crawler. Be cautious with this approach if you have made any customizations to Athena views or QuickSight datasets.

5. Check IAM permissions: Ensure that the IAM role used by the Glue crawler has sufficient permissions to perform all necessary actions on the focus table and related resources.

6. Verify table metadata: Check if the focus table has all required metadata attributes, particularly the TableType attribute. If it's missing, you may need to update the table definition to include it.

If these steps don't resolve the issue, you may need to engage AWS support for further assistance, as there could be underlying complexities specific to your deployment that require more in-depth investigation.
Sources
Backfilled CUR data and Additional Orgs not being displayed | AWS re:Post
Table incompatible when using AWS Glue with Athena in Amazon QuickSight - Amazon QuickSight
AWS_FEEDS TABLE_NOT_FOUND discrepancy | AWS re:Post

For the items 2 to 6, it is all clear.

**for the item 1: **

• I set table level to 2 at Advanced Options of "Set output and scheduling" while editing the Focus Crawler.
• I also left only a subset of accounts at s3://${DestinationS3}/focus/ (and backed up all data from other source accounts). Then I successfully ran Focus crawler, by producing data at focus table.
• I started adding one by one of the remaining account source data. For some of them, the crawler again separate output into tables (instead of partitions on focus table). When I remove the one that caused the issue, it works successfully again. I compared source table schema (parquet file) for the "bad" sources and "good" sources. They are the same!

What else should I check on the source account data (parquet files) so that I could prevent Glue issue of splitting output into multiple tables ?

I also noticed that some of the accounts that were not being processed by the crawler had lowercase folder names on the S3 data replicated from source accounts (billing_period=2025-02/ instead of BILLING_PERIOD=2025-02/). However all the source accounts were configured to send FOCUS data to Data Collection account through the same Cloudformation stack!! (https://catalog.workshops.aws/awscid/en-US/dashboards/foundational/cudos-cid-kpi/deploy#step-2.-in-managementpayersource-account-create-cur-2.0-and-replication)

That generated Crawler error "Folder partition keys do not match table partition keys, skipped folder"