Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

Lab 7.5: an analyst who sees only GBP rows and no free-text columns

Register the curated bucket with Lake Formation, switch the transactions table from IAM-only access to Lake Formation permissions, create a data cells filter, and prove from the analyst's own session that the row and column restrictions hold.

advanced⏱ 1 h 30 min🟢 FreeDEA-C01SCS-C03

Assumes: AWS Lake Formation · AWS Glue Data Catalog · Amazon Athena engine v3 · AWS CLI v2 · Region us-east-1 · profile learn-sandbox · labs/m07-transaction-pipeline/infra/cli/lab75-lake-formation.sh. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lab you can:

  • Make yourself a data lake administrator and explain why the setup script refuses to do it for you.
  • Register an SSE-KMS S3 location with Lake Formation and give its service-linked role the key permission it needs.
  • Remove IAMAllowedPrincipals from a table and describe exactly what changes for whom.
  • Create a data cells filter (currency = 'GBP', excluding three free-text columns) and grant SELECT on it to a role.
  • Assume the analyst role in a separate CLI profile and show, with three queries, that rows and columns are restricted.
  • Name the ways the restriction can still be bypassed and verify the analyst has no direct S3 path.
  • Tear the whole configuration down and give the table back to IAM-only access.

Prerequisites

  • Governance, lineage and cataloguing with Lake Formation, which explains the concepts used here. Read Secure pipeline design for the key and role background.
  • Data in the curated table. This lab filters hl_m07_curated.transactions, which the project pipeline fills. Do the module project up to and including "Make it queryable in Athena" first, or the analyst will correctly see zero rows of nothing.
  • The stack HlM07-DataLake deployed, AWS CLI v2, aws sso login --profile learn-sandbox, Git Bash (the script is bash).

Cost label

🟢 Free. Lake Formation permissions, filters and tags have no charge. You pay only for the services that read data: a handful of Athena queries over a few hundred kilobytes, which round up to the 10 MB minimum (a fraction of a cent). The stack's standing cost is the one from Lab 7.3 and the project.

Scenario

Harbourline's UK retail analysts study card spend. They may see GBP transactions only, and they have no business reason to see counterparty names or payment references, which often contain people's names. Today there is one table and one S3 location, and everyone with S3 access sees every row and column. You will enforce the restriction in the catalogue layer, once, instead of copying the table three times.

Architecture

Security-boundary view. The analyst's IAM policy allows Athena on one workgroup, catalogue reads, lakeformation:GetDataAccess, and read/write on the results bucket only. It deliberately has no s3:GetObject on the curated bucket. Lake Formation hands Athena temporary credentials for the data, and Athena applies the filter before it returns a row.

The files

FileRole in the lab
infra/cli/lab75-lake-formation.shsetup and teardown (about 80 lines of AWS CLI)
sql/athena/02_project_tables.sqlCreates hl_m07_curated.transactions (run by the project)
src/.../AccessReview.cs (from the governance lesson)Optional: lists every grant for review

Part A: before you start

Become a data lake administrator (one-off, in the console)

Lake Formation → Administration → Administrative roles and tasks → Data lake administrators → Add, and choose the IAM role behind your SSO session. The script never changes data lake settings because the setting replaces the whole administrator list: a script that "adds you" can remove everyone else. If you are using a shared sandbox, add to the list, do not replace it.

To find the role, remove the session suffix from your caller identity:

export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1 MSYS_NO_PATHCONV=1
aws sts get-caller-identity --query Arn --output text
# arn:aws:sts::111122223333:assumed-role/AWSReservedSSO_<PermissionSet>_<hash>/<you>
aws iam list-roles --path-prefix /aws-reserved/sso.amazonaws.com/ --query "Roles[].Arn" --output text

Confirm the table exists and has rows

ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
aws glue get-table --database-name hl_m07_curated --name transactions --query "Table.StorageDescriptor.Location"

Then run SELECT currency, count(*) FROM hl_m07_curated.transactions GROUP BY currency in workgroup hl-m07-analysts as yourself. You should see both GBP and EUR. This is your before picture.

Part B: set up

Run the setup script

cd labs/m07-transaction-pipeline
./infra/cli/lab75-lake-formation.sh setup

What it does, in order (read the script, do not run it blind):

  1. Creates the role hl-m07-analyst-uk with a trust policy for the account (arn:aws:iam::<account>:root) so your SSO session can assume it, tagged Project=cea-lab. Its inline policy allows: Athena start/get/stop on workgroup hl-m07-analysts; Glue read of the catalogue; lakeformation:GetDataAccess; S3 get/put/list on the results bucket only; and kms:Decrypt/GenerateDataKey conditioned on the alias alias/hl-m07-data.
  2. Registers the curated bucket with Lake Formation using the service-linked role, so Lake Formation can vend credentials for it.
  3. Revokes IAMAllowedPrincipals on hl_m07_curated.transactions. That default grant means "IAM decides"; once it is gone, Lake Formation permissions decide for integrated engines.
  4. Creates the data cells filter uk_gbp_no_free_text: row filter currency = 'GBP', excluded columns counterparty_name, reference, source_file.
  5. Grants the role DESCRIBE on the database and SELECT on the filter, and nothing else.

Let the service-linked role decrypt the data key

The curated bucket is SSE-KMS, and Lake Formation reads it through its service-linked role (it exists now, because registration created it). That role needs kms:Decrypt on the key, or every analyst query fails with a KMS error. AWS documents adding the role to the key policy; a grant achieves the same without editing the policy:

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/aws-service-role/lakeformation.amazonaws.com/AWSServiceRoleForLakeFormationDataAccess" \
  --operations Decrypt DescribeKey GenerateDataKey

The exact operations and the role ARN path should be checked against the Lake Formation documentation for your account ⚠ VERIFY. If the call is rejected, add the role to the key policy instead. The CDK stack's key policy does not include this statement, which is why the lab shows it separately.

Give yourself back access to the table

By removing IAMAllowedPrincipals you also removed the default way you query the table in Athena. Data lake administrators can grant, but do not automatically read. Grant your SSO role SELECT on the table so you can compare views:

SSO_ROLE_ARN=arn:aws:iam::${ACCOUNT}:role/aws-reserved/sso.amazonaws.com/AWSReservedSSO_<PermissionSet>_<hash>
aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" \
  --permissions SELECT DESCRIBE --resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}'
aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" \
  --permissions DESCRIBE --resource '{"Database":{"Name":"hl_m07_curated"}}'

Whether Lake Formation needs the path in /aws-reserved/sso.amazonaws.com/ in the principal ARN should be tested in your account ⚠ VERIFY. This grant is yours only: teardown revokes it.

Create a profile for the analyst

Add this to ~/.aws/config (replace the account id):

[profile hl-analyst-uk]
role_arn = arn:aws:iam::111122223333:role/hl-m07-analyst-uk
source_profile = learn-sandbox
region = us-east-1

Check who you are: aws sts get-caller-identity --profile hl-analyst-uk should show assumed-role/hl-m07-analyst-uk/....

Part C: prove it from the analyst's session

Define the helper once (it takes a profile as the first argument):

q() {
  local p=$1 id state
  id=$(aws athena start-query-execution --profile "$p" --work-group hl-m07-analysts --query-string "$2" --query QueryExecutionId --output text) || return
  while :; do
    state=$(aws athena get-query-execution --profile "$p" --query-execution-id "$id" --query QueryExecution.Status.State --output text)
    [ "$state" = QUEUED ] || [ "$state" = RUNNING ] || break
    sleep 1
  done
  echo "[$p] $state"
  if [ "$state" = SUCCEEDED ]; then
    aws athena get-query-results --profile "$p" --query-execution-id "$id" --output text --query "ResultSet.Rows[].Data[].VarCharValue" | head -20
  else
    aws athena get-query-execution --profile "$p" --query-execution-id "$id" --query QueryExecution.Status.StateChangeReason --output text
  fi
}

Rows: the analyst sees only GBP

q learn-sandbox "SELECT currency, count(*) AS n FROM hl_m07_curated.transactions GROUP BY currency"
q hl-analyst-uk "SELECT currency, count(*) AS n FROM hl_m07_curated.transactions GROUP BY currency"

You (admin, with your own grant) see both currencies. The analyst sees GBP only, with the same GBP count as yours. The filter is applied inside Athena before the result is formed, so there is no way to GROUP BY or WHERE around it.

Columns: the free-text fields are not there

q hl-analyst-uk "SELECT * FROM hl_m07_curated.transactions LIMIT 3"
q hl-analyst-uk "SELECT reference FROM hl_m07_curated.transactions LIMIT 3"

The first query succeeds and returns every column except counterparty_name, reference and source_file. The second fails, because the column does not exist as far as this principal is concerned. The exact message depends on the engine version (a column-not-found or a permission style message), so compare the behaviour, not the wording. The columns the analyst can see include counterparty_iban_token and counterparty_iban_masked: tokens for counting, masks for recognition, never the raw IBAN, which the Glue job removed before the data reached curated.

The bypass check: no direct S3 path

aws s3 ls "s3://hl-m07-curated-${ACCOUNT}/transactions/" --profile hl-analyst-uk
aws iam simulate-principal-policy --policy-source-arn "arn:aws:iam::${ACCOUNT}:role/hl-m07-analyst-uk" \
  --action-names s3:GetObject --resource-arns "arn:aws:s3:::hl-m07-curated-${ACCOUNT}/transactions/*" \
  --query "EvaluationResults[].EvalDecision" --output text

The ls fails with AccessDenied, and the simulation returns implicitDeny. If the analyst could list and read the Parquet files, the filter would be decoration: they could download the files and read every row and column. This check is the most important one in the lab.

Look at the grants

aws lakeformation list-permissions --resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}' \
  --query "PrincipalResourcePermissions[].{who:Principal.DataLakePrincipalIdentifier,what:Permissions}" --output table
aws lakeformation list-permissions --resource '{"DataCellsFilter":{"TableCatalogId":"'"$ACCOUNT"'","DatabaseName":"hl_m07_curated","TableName":"transactions","Name":"uk_gbp_no_free_text"}}'

The table should show your own grant but no IAM_ALLOWED_PRINCIPALS; the filter listing shows the analyst role with SELECT. The governance lesson's C# access-review tool prints the same list in a form an owner can sign.

Part D: console view

  1. Lake Formation → Data catalog → Tables → transactions → Actions → View permissions. Confirm IAMAllowedPrincipals is absent.
  2. Data catalog → Data filters. Open uk_gbp_no_free_text: row filter, excluded columns.
  3. Administration → Data lake locations. The curated bucket shows registered with the service-linked role.
  4. Permissions → Data lake permissions, filter by principal hl-m07-analyst-uk.
  5. CloudTrail → Event history → Event name GetDataAccess. Each analyst query leaves an event: that is your audit evidence.

Security considerations

⚠ Learning shortcut — not for production

The analyst role trusts the whole account, so any principal there with sts:AssumeRole can use it, and "UK" is approximated by currency = 'GBP'. A real bank grants roles through IAM Identity Center permission sets tied to groups, uses a business attribute (booking entity or region) for the row filter, gives access through an approval workflow with expiry, and keeps data lake administrators in a separate, MFA-protected role.

  • IAMAllowedPrincipals is the classic hole. If it is still on the table, IAM alone grants access and your filter does nothing. The setup script revokes it, and the access-review tool warns when it reappears.
  • No direct S3 for analysts. Lake Formation protects access through integrated engines; it does not stop s3:GetObject if IAM allows it. Prefer a bucket policy that denies reads except for the pipeline role and the Lake Formation role.
  • The results bucket is a side door. Athena writes result sets to S3 and Lake Formation does not govern that location. The analyst role can read results of its own workgroup queries; do not share the workgroup with people who should not see those results, and keep results short-lived (seven days here).
  • Views do not inherit cell filters. AWS documents that cell filters defined on a table do not apply to a view over that table. Do not give analysts a view and assume the filter follows.
  • The filter is only as good as its column list. ExcludedColumnNames is a deny-list: a new free-text column added next month is visible by default. Prefer an include-list or tag-based control (LF-Tags) for columns that matter.
  • Registration role and KMS. The service-linked role can decrypt the data key, so it is part of the key's "who can use it" review.

Compliance review required

Hiding names and references reduces exposure, but tokens and masks may still be personal data under UK GDPR if they can be linked back to a person. Which analysts may see which columns is a decision for your data-protection officer; Lake Formation enforces the decision.

Common mistakes

  • Running setup before the table exists → the filter command fails. Finish the project's Athena step first.
  • Forgetting to grant yourself SELECT after revoking IAMAllowedPrincipals → you cannot see the unfiltered table and think the lab is broken.
  • Skipping the KMS permission for the service-linked role → every analyst query fails with a KMS error.
  • Testing as yourself and not as the analyst → administrators are not restricted by the analyst's filter.
  • Granting SELECT on the table instead of on the filter → the analyst sees everything.
  • Leaving the table in Lake Formation mode after the lab → later labs and cdk destroy behave differently. Run teardown.

Troubleshooting

SymptomLikely causeFix
AccessDeniedException creating the filter or grantingYou are not a data lake administratorAdd your SSO role in Lake Formation → Administrative roles and tasks
Analyst query: Insufficient Lake Formation permission(s)Missing DESCRIBE on the database, or SELECT not on the filterCheck list-permissions; the principal ARN must match exactly
Analyst query fails with a KMS AccessDeniedThe Lake Formation service-linked role cannot decrypt the keyCreate the grant (or key policy statement) in Part B step 2
Analyst gets zero rows although you see GBP rowsWrong principal, or partition projection and the filter interact badlyTest with a table without projection; check the principal ARN; ⚠ VERIFY behaviour of projected partitions with filters in your account
Analyst sees both currenciesIAMAllowedPrincipals still granted, or the analyst has direct S3 accessRe-run the revoke; remove S3 permissions
Athena asks for DESCRIBE on defaultDocumented requirement when using Athena with Lake FormationGrant DESCRIBE on the default database to the analyst role
assume-role failsTrust policy or profile misconfiguredCheck source_profile and that your SSO session is current
Setup says the role already existsEarlier partial runRun teardown first (its `

Cost and cleanup

./infra/cli/lab75-lake-formation.sh teardown

The script revokes the analyst's grants, deletes the filter, re-grants IAMAllowedPrincipals ALL on the table, deregisters the location and deletes the role and its inline policy. Then remove what the script does not know about:

# Your own grants and the KMS grant (list first, then revoke by id)
aws lakeformation revoke-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" --permissions SELECT DESCRIBE \
  --resource '{"Table":{"DatabaseName":"hl_m07_curated","Name":"transactions"}}'
aws lakeformation revoke-permissions --principal DataLakePrincipalIdentifier="$SSO_ROLE_ARN" --permissions DESCRIBE \
  --resource '{"Database":{"Name":"hl_m07_curated"}}'
aws kms list-grants --key-id alias/hl-m07-data --query "Grants[].{id:GrantId,to:GranteePrincipal}"
aws kms revoke-grant --key-id alias/hl-m07-data --grant-id <GrantId-from-the-list>
# Remove the [profile hl-analyst-uk] block from ~/.aws/config.
# Check nothing tagged remains
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
  --query "ResourceTagMappingList[?contains(ResourceARN, 'hl-m07')].ResourceARN"

Remove yourself from the data lake administrators only if you added yourself for this lab and no one else relies on it. Do it in the console.

Exercises

  • 🟢 Guided: Create eu_eur_no_free_text and a role hl-m07-analyst-eu, extend a copy of the script, and prove with one query per role that each sees only its own currency and no reference column.
  • 🟡 Challenge: Replace the deny-list of excluded columns with an LF-Tag design: tag counterparty_name, reference and source_file as classification=confidential, everything else internal, and grant the analyst role SELECT on classification = internal. Show that a column you add next month is not visible until someone classifies it.
  • 🔴 Live problem: An audit finding says "UK analysts can download full transaction files from S3". You see the Lake Formation filter is correct and Athena behaves. List the possible causes in the order you would check them, the evidence for each, and the remediation plan.

Assignment

Add a docs/analyst-access.md to your portfolio repository: your access matrix (audiences, tables, columns, rows), the filter definitions, the five verification queries with their expected results and the teardown evidence (the tag query output).

Solutions

Knowledge check

Interview questions

  • "How do you stop analysts reading raw files from S3?" — Remove direct S3 permissions from their roles, add a bucket policy denying reads to anyone but the pipeline and Lake Formation roles, and give access only through Lake Formation and Athena.
  • "Row filter or separate tables?" — A data cells filter on one table avoids duplicating data and its personal-data footprint; separate tables only when physical separation (jurisdiction, encryption keys) is required.
  • "What evidence would an auditor want?" — The grants list, the filter definitions, proof that IAMAllowedPrincipals is gone, CloudTrail GetDataAccess events, and the periodic access review.

Certification practice

Completion checklist

  • I am (temporarily) a data lake administrator and the curated table has data
  • I ran setup, granted the Lake Formation role key access and granted myself SELECT
  • The analyst sees only GBP rows and no reference column; I saw the failing SELECT reference
  • The analyst's aws s3 ls on curated failed and the simulation says implicitDeny
  • I ran teardown, revoked my own grants and the KMS grant, and ran the tag query

Key takeaways

  • Filters only work if IAMAllowedPrincipals and direct S3 access are gone.
  • One table and one filter per audience beats copies of the data.
  • The Lake Formation service-linked role needs key permission on an SSE-KMS location.
  • Test as the restricted principal, and test the bypass paths, not only the happy path.
  • A deny-list of columns fails open; classify with tags or use an include-list.

Further reading

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.

On this page