Which solution will meet these requirements with the LEAST operational overhead?
Use Amazon EMR to ingest the data directly from the database to the QuickSight SPICE engine. Include only the required columns.
Use AWS Glue Studio to ingest the data from the database to the S3 data lake. Attach an IAM policy to the QuickSight users to enforce column-level access control. Use Amazon S3 as the data source in QuickSight.
Use AWS Glue Elastic Views to create a materialized view for the database in Amazon S3. Create an S3 bucket policy to enforce column-level access control for the QuickSight users. Use Amazon S3 as the data source in QuickSight.
Use a Lake Formation blueprint to ingest the data from the database to the S3 data lake. Use Lake Formation to enforce column-level access control for the QuickSight users. Use Amazon Athena as the data source in QuickSight.
Explanations:
Using Amazon EMR to ingest data directly from the database into the QuickSight SPICE engine does not inherently provide column-level access control. Moreover, it involves higher operational overhead as it requires managing EMR clusters and ensuring data refreshes.
While using AWS Glue Studio to ingest data into S3 can allow for ETL processes, simply attaching an IAM policy for column-level access control is insufficient. IAM policies do not enforce column-level security at the data lake level effectively. The integration with Lake Formation is necessary for this feature.
AWS Glue Elastic Views could create materialized views, but implementing S3 bucket policies for column-level access control is not a straightforward or effective method. S3 bucket policies apply to objects in S3 rather than specific columns within those objects, leading to potential data access issues.
Using a Lake Formation blueprint for data ingestion allows for integration with Lake Formation’s data governance features, including fine-grained column-level access control. This approach minimizes operational overhead, as Lake Formation manages access policies and integrates smoothly with Amazon Athena, allowing QuickSight to visualize the data efficiently.
From what I’ve seen, the answer is:
Use a Lake Formation blueprint to ingest the data from the database to the S3 data lake. Use Lake Formation to enforce column-level access control for the QuickSight users. Use Amazon Athena as the data source in QuickSight.