Reporting with Amazon Quick Sight: datasets, SPICE and row-level security
Turn the pipeline's gold tables into a reconciliation dashboard: connect Quick Sight to Athena, choose SPICE or direct query, restrict rows per group with a permissions dataset, and know the free alternative when you do not want a paid seat.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.Athena 4.1.1.1) · Amazon Quick Sight (feature of AWS Quick; API namespace quicksight) · Amazon Athena engine v3 · AWS CLI v2 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Name the service correctly (Quick Sight inside AWS Quick) and explain which API, CLI and IAM names did not change.
- Connect Quick Sight to an Athena workgroup, authorise its S3 buckets and KMS key, and explain why it should read the gold tables rather than curated data.
- Choose between SPICE and direct query for a dashboard, using the data size, the freshness needed and the Athena cost.
- Restrict a dataset with a row-level security (RLS) permissions dataset, predict what a user with no rule sees, and explain why Quick RLS and Lake Formation are two separate layers.
- Design a reconciliation dashboard that finance can sign off, and build the same check with a free C# report gate.
Prerequisites
- Amazon Athena for workgroups, views and scan limits
- Governance with Lake Formation and Secure pipeline design, because reports must not undo the controls built there
- The gold tables and views from the module project (the
sql/athena/03_project_reports.sqlfile) - Tools: AWS CLI v2 (
learn-sandbox), .NET 10 SDK or Docker. A Quick Sight account is optional: every step has a free alternative
Why this matters in banking
Finance does not read Parquet. At 09:00 the head of payments operations wants one screen that says: "Yesterday's three partner files were read, 4,312 rows were accepted, 67 were quarantined for known reasons, and every control total matches." If any line says BREAK, someone must explain it before the numbers go into the ledger. A pipeline whose results can only be read by running SQL is a pipeline nobody outside engineering trusts, and a dashboard that shows UK and EU figures to everyone is a data-sharing breach waiting to be noticed. This lesson is about the last mile: making the pipeline's evidence visible to the right people, and only them.
The idea in plain language
Amazon Quick Sight is AWS's business-intelligence tool: you point it at data, build charts and tables, publish them as a dashboard, and share the dashboard with people. Three ideas carry the lesson:
- A data source is a connection (here, to Athena through one workgroup).
- A dataset is a prepared table that dashboards use. It can be SPICE (Quick Sight copies the rows into its own in-memory engine on a schedule) or direct query (every chart sends SQL to Athena when someone opens it).
- Row-level security is a rule table: "this user or group may see only the rows where
currencyisGBP".
Analogy: direct query is a reporter phoning the archive every time a reader opens the newspaper. SPICE is printing the morning edition once. The row-level rules are the delivery round: each reader only gets the sections on their round.
What problem the AWS service solves
| Need | Quick Sight feature | Limitation |
|---|---|---|
| Non-engineers read pipeline results | Dashboards with filters and tables | Per-user pricing; someone must own the design |
| Fast dashboards over many queries | SPICE datasets with scheduled refresh | Data is a copy: stale until refreshed; SPICE storage is billed per GB |
| Always-fresh numbers | Direct query to Athena | Every visual runs queries; cost and latency follow your scan sizes |
| Each audience sees its own rows | RLS with a permissions dataset (Enterprise edition) | Works on text columns only; no rule means no rows |
| Share outside the organisation | Embedding, with tag-based RLS for unregistered users | Needs capacity or session pricing ⚠ VERIFY |
| Scheduled delivery | Email reports | Reports are copies: they leave the access control boundary |
The name has changed: what to call it
AWS's documentation now describes AWS Quick as an AI-powered service for automating tasks, analysing data and building apps, and says it evolved from Amazon QuickSight: Quick Sight continues as a feature inside Quick, and all existing QuickSight APIs, SDKs and integrations continue to work without changes. The documentation and pricing pages currently write "AWS Quick Sight", older material and the module overview write "Amazon QuickSight" or "Amazon Quick Sight", and the research notes for this course use "Amazon Quick" for the wider product ⚠ VERIFY the final product and brand naming before you put a name in a diagram for a customer. Practically:
- In the console the product appears under Quick. In code, the CLI command is still
aws quicksight, the SDK package isAWSSDK.QuickSight, and IAM actions still start withquicksight:. - Amazon Q Business, the separate enterprise assistant, is closed to new customers (since 2026-07-30) and points to Amazon Quick. This lesson does not use it.
- Features branded "Q" inside Quick Sight (Q&A topics) and the generative-BI features belong to the paid "Pro" roles; this lesson uses none of them.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Power BI Pro / Premium per-user licence | Quick Sight Reader / Author roles | Per-user monthly price for named users; Readers can also be billed by 30-minute session capacity |
| Power BI Import mode | SPICE | Scheduled refresh; storage per GB beyond what each Author includes |
| Power BI DirectQuery | Direct query | Each visual becomes a query; on Athena you pay per byte scanned |
| Power BI dataset RLS roles with DAX filters | RLS permissions dataset (user or group name → allowed values) | Rules are data, a table you maintain, not an expression |
| Power BI workspace / app | Quick Sight folders, dashboards and shared datasets | Sharing is per dashboard to users and groups |
| Power BI deployment pipelines | Asset bundle export and import, or CDK/Terraform for data sources | Dashboards and datasets are easiest to move as asset bundles ⚠ VERIFY the current API names |
Architecture
Security-boundary view. Quick Sight queries Athena with its own service role, not with each viewer's identity, unless you configure trusted identity propagation (supported only with IAM Identity Center and Lake Formation-managed data, and it turns off SPICE, custom SQL, email reports and exports for those datasets). So the Lake Formation filters from the previous lesson protect the data from analysts who query Athena directly, while inside Quick Sight the per-viewer restriction is Quick's RLS. They are two layers, each configured separately, and a gap in one is not covered by the other.
Step-by-step implementation
Decide what the dashboard must answer
Write the questions before choosing charts. For the pipeline-control dashboard:
| Question | Source | Visual |
|---|---|---|
| Did every expected file arrive and get processed? | v_reconciliation (one line per run and currency) | Table of runs with a coloured status |
| Is anything a BREAK? | status column | A KPI tile: count of BREAK lines (target 0) |
| How many rows were read, accepted, quarantined today? | reconciliation counts | Three KPI tiles |
| Why were rows rejected? | hl_m07_curated.quarantine grouped by reason | Bar chart (restricted audience: it exposes row counts per reason only) |
| Are daily totals plausible? | v_daily_summary | Line chart of net amount by day and currency |
| Any suspicious-activity signals? | fraud_signals grouped by rule | Bar chart and table (high-risk audience) |
Build the reporting layer in Athena, not in Quick Sight
Keep the logic where it can be tested and versioned: the views in 03_project_reports.sql already aggregate across runs. Point Quick Sight at views over the gold tables (and the small quarantine summary), never at the curated table. Curated rows are the sensitive dataset; gold figures are aggregates. If you need a view that excludes the free-text columns, create it once in Athena.
Authorise Quick Sight to reach Athena, S3 and KMS
A Quick administrator opens Manage Quick Sight → Security & permissions → Quick Sight access to AWS services → Manage, ticks Amazon Athena, and under Amazon S3 selects the gold bucket and the Athena results bucket. Because the buckets use SSE-KMS, the Quick Sight service role must also be allowed to decrypt with the key. The AWS guidance is a KMS grant for the role (aws-quicksight-s3-consumers-role-v0, or aws-quicksight-service-role-v0 if the first is absent). Both the S3 selection and the key grant are needed, or queries fail with an access error.
Create the Athena data source on the capped workgroup
The data source names the workgroup. Use hl-m07-analysts: it enforces the scan limit (1 GB per query) and the encrypted results location, so a careless chart cannot run up a bill. Validate the connection before building anything.
Create the datasets and choose SPICE or direct query
Rule of thumb for this pipeline: the gold views are tiny (hundreds to thousands of rows), so SPICE with one scheduled refresh after the pipeline's expected finish time gives instant dashboards and almost no Athena cost. Choose direct query only when freshness matters more than speed (for example, a dashboard watching a live run), and then keep the Athena side small and partitioned.
| Factor | SPICE | Direct query |
|---|---|---|
| Freshness | As of the last refresh | Each open |
| Dashboard speed | Fast, independent of Athena | Depends on the query |
| Athena cost | One query per refresh | One or more queries per visual per open |
| Storage cost | SPICE per GB (some included per Author) | None in Quick Sight |
| RLS rules datasets | Strict ingestion rules apply (invalid rows fail the refresh) | Allowed, and can be a live query |
| Datasets inheriting RLS from a parent | Not supported in SPICE | Direct query only |
Create groups and the RLS rules dataset
Create three Quick Sight groups, add the finance users, and build a small rules dataset (flagged as a rules dataset when created). One row grants a group access to one value of currency:
GroupName,currency
hl-finance-uk,GBP
hl-finance-eu,EUR
hl-finance-all,GBP
hl-finance-all,EURThe "all" group lists every currency explicitly. Do not rely on an empty row to mean "everything": AWS's documentation says empty rules are skipped, so a rule with all values blank grants nothing (the same documentation page shows an older example of blank meaning all rows, so test your own account ⚠ VERIFY). Explicit values are unambiguous and survive a change in that behaviour.
Apply the rules to the dataset and test as each group
On the dataset page, Row-level security → Set up → User-based rules, choose the rules dataset, policy Grant access to dataset, Apply and activate. The dataset is marked RESTRICTED. Share the dashboard with the three groups, then test each audience: a UK user sees GBP lines only; a user with no rule sees an empty dashboard.
Build and publish the dashboard, then schedule the refresh
Build the visuals from the table above, add a conditional format that turns BREAK red, publish the analysis as a dashboard, and set the dataset refresh on a daily schedule after the pipeline's usual finish time. Check the refresh history after the first run.
AWS Console instructions
- Quick Sight home → Datasets → New dataset → Athena. Name the source
hl-m07-athena, workgrouphl-m07-analysts, Validate connection, Create data source. - Choose catalog
AwsDataCatalog, databasehl_m07_gold, table or viewv_reconciliation. Choose Import to SPICE for quicker analytics and Edit/Preview data. Check thatcurrencyis a String column. Save and publish the dataset. - Datasets → New dataset → New rules dataset (or upload the CSV above). Name it
rls-currency-by-group. - Datasets →
v_reconciliation→ Row-level security → Set up → User-based rules → rls-currency-by-group → Grant access to dataset → Apply dataset → Apply and activate. - Analyses → New analysis, choose the dataset, add KPI tiles, a table of runs and a line chart. Format visual → Conditional formatting on
status. - Publish → Publish dashboard → Share, add the three groups as Viewers.
- Datasets →
v_reconciliation→ Refresh → Add new schedule daily at the chosen time. Open Refresh history to confirm.
AWS CLI
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
# Who is registered in Quick Sight? You need user and group ARNs for sharing and for Lake Formation grants.
aws quicksight list-users --aws-account-id "$ACCOUNT" --namespace default
aws quicksight list-groups --aws-account-id "$ACCOUNT" --namespace default
# Groups for the three audiences (there is no console page to create them).
for g in hl-finance-uk hl-finance-eu hl-finance-all; do
aws quicksight create-group --aws-account-id "$ACCOUNT" --namespace default --group-name "$g" \
--description "Harbourline finance reporting audience"
done
aws quicksight create-group-membership --aws-account-id "$ACCOUNT" --namespace default \
--group-name hl-finance-uk --member-name YOUR_QUICKSIGHT_USERNAME
# Let the Quick Sight service role decrypt the data key (AWS documents this grant for KMS-encrypted data).
KEY_ARN=$(aws kms describe-key --key-id alias/hl-m07-data --query KeyMetadata.Arn --output text)
aws kms create-grant --key-id "$KEY_ARN" \
--grantee-principal "arn:aws:iam::${ACCOUNT}:role/service-role/aws-quicksight-s3-consumers-role-v0" \
--operations Decrypt
# The Athena data source (the datasets and dashboards are easier to build in the console).
aws quicksight create-data-source --aws-account-id "$ACCOUNT" --data-source-id hl-m07-athena \
--name hl-m07-athena --type ATHENA \
--data-source-parameters '{"AthenaParameters":{"WorkGroup":"hl-m07-analysts"}}'
# After the first publish: what exists?
aws quicksight list-data-sets --aws-account-id "$ACCOUNT"
aws quicksight list-dashboards --aws-account-id "$ACCOUNT"
# Trigger a SPICE refresh by hand (ingestion id must be unique).
aws quicksight create-ingestion --aws-account-id "$ACCOUNT" --data-set-id YOUR_DATASET_ID \
--ingestion-id "manual-$(date +%Y%m%d%H%M%S)"The dataset API is changing
The CreateDataSet API documents RowLevelPermissionDataSet as deprecated for the legacy data-preparation experience, and adds DataPrepConfiguration and SemanticModelConfiguration for the new one. Because this part of the API is moving, the lesson builds datasets and RLS in the console and keeps scripts to the stable calls above. If you automate datasets, read the current API reference first and test in a sandbox ⚠ VERIFY.
Infrastructure as Code
CloudFormation, CDK and Terraform cover the data source and permissions well. Datasets and dashboards are authored in the console and moved between accounts as asset bundles, so they are not shown here.
using Amazon.CDK;
using Amazon.CDK.AWS.QuickSight;
using Constructs;
namespace Harbourline.DataPipeline.Infra;
public sealed class ReportingStack : Stack
{
public ReportingStack(Construct scope, string id, string quickSightAdminUserArn, IStackProps? props = null)
: base(scope, id, props)
{
// The data source Quick Sight uses to reach Athena, pinned to the capped workgroup.
_ = new CfnDataSource(this, "AthenaSource", new CfnDataSourceProps
{
AwsAccountId = Account,
DataSourceId = "hl-m07-athena",
Name = "hl-m07-athena",
Type = "ATHENA",
DataSourceParameters = new CfnDataSource.DataSourceParametersProperty
{
AthenaParameters = new CfnDataSource.AthenaParametersProperty { WorkGroup = "hl-m07-analysts" },
},
Permissions = new CfnDataSource.IResourcePermissionProperty[]
{
new CfnDataSource.ResourcePermissionProperty
{
Principal = quickSightAdminUserArn,
Actions =
[
"quicksight:DescribeDataSource", "quicksight:DescribeDataSourcePermissions",
"quicksight:PassDataSource", "quicksight:UpdateDataSource",
"quicksight:DeleteDataSource", "quicksight:UpdateDataSourcePermissions",
],
},
},
Tags = [new CfnTag { Key = "Project", Value = "cea-lab" }],
});
}
}Complete code
The report gate is the free alternative to a dashboard seat, and it is also useful next to one: it runs the same reconciliation view in Athena, prints one line per run and currency, and exits with code 1 if any line is not MATCHED. Run it from a schedule or a pipeline step so a break is noticed even if nobody opens the dashboard. It was built against AWS SDK for .NET v4 (AWSSDK.Athena 4.1.1.1).
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AWSSDK.Athena" Version="4.1.1.1" />
<!-- Needed at run time for the IAM Identity Center (SSO) profile learn-sandbox. -->
<PackageReference Include="AWSSDK.SSO" Version="4.1.0.2" />
<PackageReference Include="AWSSDK.SSOOIDC" Version="4.1.0.2" />
</ItemGroup>
</Project>// dotnet run (AWS_PROFILE=learn-sandbox, after "aws sso login")
// Prints the reconciliation view and exits 1 if any run is a BREAK: a report gate for a pipeline or a schedule.
using Amazon.Athena;
using Amazon.Athena.Model;
const string workGroup = "hl-m07-analysts";
const string sql = """
SELECT ingest_run_id, source_file, currency, source_row_count, accepted_row_count,
quarantined_row_count, difference, status
FROM hl_m07_gold.v_reconciliation
ORDER BY started_at DESC
LIMIT 50
""";
using var athena = new AmazonAthenaClient();
var started = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest { QueryString = sql, WorkGroup = workGroup });
QueryExecution execution;
do
{
await Task.Delay(TimeSpan.FromSeconds(1));
execution = (await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest { QueryExecutionId = started.QueryExecutionId })).QueryExecution;
}
while (execution.Status.State == QueryExecutionState.QUEUED || execution.Status.State == QueryExecutionState.RUNNING);
if (execution.Status.State != QueryExecutionState.SUCCEEDED)
{
Console.Error.WriteLine($"Query {execution.Status.State}: {execution.Status.StateChangeReason}");
return 2;
}
var rows = new List<List<string>>();
string? token = null;
do
{
var page = await athena.GetQueryResultsAsync(new GetQueryResultsRequest { QueryExecutionId = started.QueryExecutionId, NextToken = token });
rows.AddRange(page.ResultSet.Rows.Select(r => r.Data.Select(d => d.VarCharValue ?? string.Empty).ToList()));
token = page.NextToken;
}
while (token is not null);
var header = rows[0];
var statusColumn = header.IndexOf("status");
var breaks = 0;
foreach (var row in rows.Skip(1))
{
var isBreak = row[statusColumn] != "MATCHED";
breaks += isBreak ? 1 : 0;
Console.WriteLine($"{(isBreak ? "BREAK " : "MATCHED")} {row[0]} {row[2]} read={row[3]} accepted={row[4]} quarantined={row[5]} difference={row[6]}");
}
var scanned = execution.Statistics.DataScannedInBytes;
Console.WriteLine($"{rows.Count - 1} line(s), {breaks} break(s). Athena scanned {scanned:N0} bytes.");
return breaks == 0 ? 0 : 1;Code explained line by line
workGroup— the same capped workgroup the dashboard uses, so the gate inherits the scan limit and the encrypted results location. The gate cannot run an expensive query by mistake.sql— a raw string literal (C# 11) holding the view query. It reads a view over gold tables, never curated rows, so the tool needs no access to personal data.StartQueryExecutionAsync— Athena is asynchronous: this returns an execution id, not rows.- The
do ... whileloop — polls once a second until the state leavesQUEUEDandRUNNING. A production version would add a timeout and exponential back-off. - State check —
FAILEDandCANCELLEDreturn code 2, which is different from "there are breaks" (code 1), so a scheduler can tell "report broken" from "data broken". GetQueryResultsAsyncwithNextToken— results are paged; the first row of the first page is the header.VarCharValue ?? string.Empty— Athena returns every value as text andnullfor SQL NULL.IndexOf("status")— finds the column by name so a reordered query does not silently read the wrong field.DataScannedInBytes— the bytes Athena billed for. Printing it reminds the learner that the reporting layer has a price too.
Permissions: athena:StartQueryExecution, GetQueryExecution, GetQueryResults on the workgroup, Glue read on the gold database and the views' base tables, and S3 and KMS access to the results bucket (as in the Lab 7.5 role).
Security considerations
⚠ Learning shortcut — not for production
The lab shares the dashboard with three groups from one account and builds RLS rules from a hand-written CSV. A bank maps Quick Sight groups to its identity provider through IAM Identity Center, keeps the rules in a governed table with an owner and a change process, restricts who can edit datasets (owners see all rows), reviews the viewer list every quarter, disables or limits downloads, and keeps production dashboards in a separate account promoted by asset bundles. Because the Quick Sight service role reads Athena for everyone, that role should have access only to the gold views.
- RLS restricts viewers, not owners. When a dataset is shared with a co-owner, they see every row. Keep ownership with a small data team.
- No rule means no rows. This is the safe default. An empty rule is skipped. Test with a user who is in no group.
- RLS works on text columns only. It does not work on dates or numbers, so design the restricting column as a string (
currency,entity,region). Each user's rule set is capped (999 rules per user at the time of writing ⚠ VERIFY), so group rules help. - A dataset without the restricting column cannot be restricted. The quarantine-reason dataset has no
currency. Share it only with the group that may see it, or add the column to the view. - Do not put tokens or raw identifiers in a dashboard. Gold aggregates are enough for control reporting. A report that shows
counterparty_iban_tokenhands out a join key. - Downloads and email reports are copies. Once a table is exported or emailed it leaves your access control. Decide per dashboard whether exports are allowed.
- Quick Sight and Lake Formation are different layers. Lake Formation filters apply to the principal that queries Athena. With the default service-role connection that principal is Quick's role, not the viewer. Do not assume a Lake Formation row filter protects a dashboard.
- KMS. The KMS grant lets the Quick Sight service role decrypt the data key. Treat that as part of the key's "who can use it" review.
Compliance review required
Whether a dashboard may show a given aggregate to a given group, how long SPICE copies may hold it, and whether exports are permitted are data-protection and compliance decisions. Quick Sight enforces rules you provide; it does not decide them.
Banking use case
Every morning Harbourline's payments operations team opens Pipeline control. Three KPI tiles show rows read, accepted and quarantined for the previous business day; a fourth shows the number of BREAK lines in red when above zero. Below, a table lists each run by file and currency with its control-total difference. The UK finance group sees only GBP lines and the EU subsidiary group only EUR lines; the group controller sees both. A second dashboard, shared with the financial-crime team only, shows suspicious-activity signals by rule. A break triggers the runbook: look at the quarantine reasons for that run, decide whether the source file or the rules were at fault, and record the decision with the run id, which ties back to the audit record.
Common mistakes
- Pointing Quick Sight at the curated table → every dashboard author can build charts on sensitive columns. Use gold views.
- Choosing direct query "for freshness" on a large table → every visual scans the table and the bill follows. Use SPICE or small gold tables.
- Forgetting the KMS grant → the dataset fails with an access error even though the S3 box is ticked.
- Using Standard edition for RLS → RLS needs Enterprise edition.
- A blank rule meaning "everyone" → documented as skipped. List values explicitly.
- Sharing the analysis, not the dashboard, with viewers → viewers need the published dashboard; analyses are for authors.
- Assuming Lake Formation filters apply to viewers → they apply to the querying role.
- Leaving the SPICE refresh at "manual" → the dashboard shows yesterday's numbers with today's date on it.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Dataset creation: "Insufficient permissions" or access error to Athena | Quick Sight access to Athena or the S3 buckets not enabled | Manage Quick Sight → Security & permissions → enable Athena and select gold and results buckets |
Query fails with KMS AccessDenied | Quick Sight service role cannot decrypt the key | Create the KMS grant for the service role named in the documentation |
| Dataset shows data but a restricted user sees nothing | No rule for the user or group, or the group name differs in case | Compare names exactly (case-sensitive); add the rule |
| RLS option not available | Standard edition | Use Enterprise edition |
| SPICE refresh fails on the rules dataset | Rules datasets have strict ingestion: an invalid row or too-long cell fails the refresh | Fix the rows; keep rule values short |
| Lake Formation permission errors when reading a governed table | The Quick Sight role or user has no Lake Formation grant on it | Point the dataset at the gold views, or grant Lake Formation permissions to the right principal |
| Direct query dashboard is slow and costly | Each visual runs Athena over a big scan | Switch to SPICE or query gold tables with partition filters |
create-ingestion returns an error about the ingestion id | Id reused | Use a unique id per call |
Cost and cleanup
The paid part is the Quick Sight subscription. The pricing page at the time of writing lists per-user monthly prices (Reader about USD 3, Author about USD 24, Pro roles more), SPICE at about USD 0.38 per GB-month beyond what each Author includes, a USD 250 per month account infrastructure fee when the account has a Pro user or Q&A enabled, and Reader-session capacity packs. It states no free-trial length, and a limited promotion gives Authors access to Enterprise capabilities until 2026-12-31 in regions where the new capabilities are available ⚠ VERIFY all of these on the current page and in your Region before signing up; prices and plans change.
Free-first path: skip the subscription and use the Athena views plus the C# report gate in this lesson. You lose interactivity and per-viewer RLS, but you can complete every other learning objective on paper (design the dataset, write the RLS rules table, write the test matrix).
If you do subscribe, close the exposure the same day:
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
aws quicksight list-dashboards --aws-account-id "$ACCOUNT" --query "DashboardSummaryList[].DashboardId"
aws quicksight delete-dashboard --aws-account-id "$ACCOUNT" --dashboard-id YOUR_DASHBOARD_ID
aws quicksight list-data-sets --aws-account-id "$ACCOUNT" --query "DataSetSummaries[].DataSetId"
aws quicksight delete-data-set --aws-account-id "$ACCOUNT" --data-set-id YOUR_DATASET_ID # releases SPICE storage
aws quicksight delete-data-source --aws-account-id "$ACCOUNT" --data-source-id hl-m07-athena
for g in hl-finance-uk hl-finance-eu hl-finance-all; do
aws quicksight delete-group --aws-account-id "$ACCOUNT" --namespace default --group-name "$g"
doneThen, in the Quick console, remove extra users and, if this was a throw-away account subscription, unsubscribe under account settings. Deleting dashboards does not stop per-user subscription charges; removing users and unsubscribing does. Revoke the KMS grant (aws kms list-grants then revoke-grant) and run the module's tag check from the project page.
Exercises
- 🟢 Guided: Run the report gate against the project's gold tables (or read its code) and explain each column of the output. Then write the RLS rules CSV for three groups and predict what each sees on the reconciliation dataset.
- 🟡 Challenge: Add an
entitycolumn (UKorEU) to the reconciliation view, extend the rules table to restrict by bothentityandcurrency, and write a test matrix of five users with their expected row counts. Include a user with no rule. - 🔴 Live problem: The EU controller reports seeing GBP lines on the dashboard. The Lake Formation filter for the EU analyst role is correct. Find the possible causes, in the order you would check them, and decide what to change.
Assignment
Write a reporting design pack for your portfolio repository: the dashboard question list, the Athena views, the SPICE-versus-direct decision with numbers, the RLS rules table with a test matrix, the cost estimate for ten Readers and two Authors, and the free-alternative runbook using the report gate. Mark which decisions need sign-off from finance and the data-protection officer.
Solutions
The gate prints, per line: the run id (which maps to one audit record), the currency, rows read, accepted and quarantined, and the control-total difference. A line is MATCHED when the counts agree and the difference is zero. Rules table: hl-finance-uk,GBP; hl-finance-eu,EUR; hl-finance-all,GBP; hl-finance-all,EUR. A UK user sees only GBP lines, an EU user only EUR lines, the controller sees both, and a user in no group sees nothing. Predicting "nothing" for the unlisted user is the important part: no rule means no rows.
Create the view with entity derived from the currency or the booking entity, add the column to the rules dataset (GroupName,entity,currency), and keep it all string. Example rows: hl-finance-uk,UK,GBP; hl-finance-eu,EU,EUR; hl-finance-all,UK,GBP, hl-finance-all,UK,EUR, hl-finance-all,EU,GBP, hl-finance-all,EU,EUR (every combination for the group that sees all). A rule that names two columns must match both. Test matrix: UK user (GBP and UK rows only), EU user, controller (all four combinations), a user in two groups (union of rules), a user in no group (zero rows). Check the 999-rule-per-user limit if you generate many rows ⚠ VERIFY.
Check in this order: (1) is the dashboard's dataset the RLS-restricted one, or is the EU controller looking at a second, unrestricted dataset or an analysis they own? (2) Are they a co-owner of the dataset (owners see all rows)? (3) Are they in two groups, one of which has a GBP rule (rules are additive)? (4) Does the rules dataset contain the right group name with the exact case? (5) Did the refresh of the rules dataset fail, so old rules apply? (6) Is the data source connected with the Quick service role, which ignores the analyst's Lake Formation filter? The Lake Formation filter being right is irrelevant if Quick queries as its own role. Fix by correcting the group membership or dataset ownership, and add the test matrix to a quarterly review.
Knowledge check
Answer: No rows of the restricted dataset. A rule is required to see data.
Answer: When freshness matters more than speed and the Athena side is small and partitioned. Otherwise SPICE gives faster dashboards and far fewer Athena queries.
Answer: True. AWS states that existing QuickSight APIs, SDKs and integrations continue to work, and the IAM actions keep the quicksight: prefix.
Answer: With the default connection, Quick Sight queries Athena with its own service role, so Lake Formation evaluates that role, not each viewer. Per-viewer restriction needs Quick Sight RLS, or trusted identity propagation, which has its own limits (for example no SPICE).
Answer: Text (string-type) columns only. It does not work on dates or numbers.
Interview questions
- "How do you secure a dashboard built on a data lake?" — Point the BI tool at gold aggregates, not curated rows; grant the tool's role only those views; use row-level security for viewers; limit owners; control exports; and keep the KMS and S3 permissions of the service role under review.
- "SPICE or direct query?" — SPICE for speed and cost on small or moderate datasets with a refresh schedule; direct query for freshness, with a capped, partitioned source.
- "How would you prove to finance that the pipeline reconciles?" — A dashboard or gate that lists every run's control totals and status, ties each to an audit record and fails loudly on a break.
- "Why not build reports straight from the curated table?" — It exposes sensitive columns to anyone who can author a chart and bypasses the aggregation the control process has signed off.
Certification practice
Regional managers share one Quick Sight dashboard. Each manager must see only rows for their own region, and the region list changes monthly. What is the most maintainable approach?
A. Publish one dashboard per region. B. Apply row-level security with a permissions dataset that maps users or groups to region values, and maintain that dataset. C. Create a different Athena workgroup for each manager. D. Use SPICE capacity limits.
Answer: B. One dashboard, rules as data. A multiplies maintenance. C does not restrict rows inside a shared dataset. D is a capacity feature.
A dashboard reads a 40 MB daily summary table loaded by a nightly batch at 02:00. Users open it throughout the day. Which design is the most cost-effective and responsive?
A. Direct query to Athena for every visual. B. A SPICE dataset with a daily scheduled refresh after 02:00. C. An hourly refresh of a 2 TB SPICE dataset. D. Export a CSV by e-mail every hour.
Answer: B. The data changes once a day, so a single refresh keeps the dashboard current and fast. A repeats queries for no benefit, C and D waste money and create copies.
A small team of 5 analysts will build dashboards and 200 colleagues will only view them occasionally. Which pricing choice is most likely to be cheapest?
A. Make all 205 people Authors. B. Make 5 Authors and the rest Readers, and compare with Reader-session capacity pricing if usage is low and spiky. C. Use only Admin roles. D. Buy annual capacity for 1.6 million sessions.
Answer: B. Only builders need the Author role. Readers are priced lower per user, and session capacity can beat per-user prices for occasional viewers. A and C are the most expensive roles. D is far too large. Check current prices ⚠ VERIFY.
Completion checklist
- I can explain the Quick Sight naming and that APIs and IAM prefixes are unchanged
- I can list the three things Quick Sight needs to read an encrypted Athena table (service access to Athena, S3 buckets, KMS grant)
- I can choose SPICE or direct query and defend it with numbers
- I wrote an RLS rules table and tested a user with no rule
- I can explain why Lake Formation filters do not protect a service-role dashboard
- I ran the report gate or read its code, and removed any Quick Sight resources and users I created
Key takeaways
- Quick Sight is now part of AWS Quick, but the API, CLI, SDK and IAM names are unchanged.
- Build reports on gold views over the pipeline's aggregates, never on curated personal data.
- SPICE with a scheduled refresh suits small, daily-changing gold data; direct query costs a query per visual.
- RLS is a rules table that works on text columns, gives no rows without a rule, and needs Enterprise edition.
- Quick RLS and Lake Formation are two layers with different principals, so test both.
- The free alternative, Athena views plus a report gate, keeps the control and costs almost nothing.
Further reading
- What is AWS Quick?
- Using row-level security with user-based rules
- Authorizing connections to Amazon Athena
- Authorizing connections through AWS Lake Formation
- Amazon Quick Sight pricing
- Previous lesson: Secure pipeline design
- Project: Banking transaction processing pipeline
- Cheat sheet: Module 7 cheat sheet
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Secure pipeline design: encryption, least privilege, private networking and PII protection
Design a data pipeline that is safe by construction: a KMS key per data domain, one narrow role per job, private network paths, PII tokenised and masked inside the job, and an audit trail you can show a reviewer.
Lab 7.1: generate seeded banking transactions, including the messy ones
Use the C# dataset generator to produce 1k, 100k or 1M reproducible Harbourline transactions in CSV, JSON Lines and Parquet, inject deliberate defects with an answer key, and prove with the pipeline's own validator that every defect is found.