The Auditor Wants 90 Days of Query History
An auditor requests evidence of all queries touching FIN.PAYROLL for the last 90 days, including who ran them and from which IP. The team checks ACCOUNT_USAGE.QUERY_HISTORY and finds the default retention is 1 year, so data exists. However, they discover that queries executed through a specific service account use a role whose queries were run under a different user name after a credential rotation, and that ACCESS_HISTORY is only retained for a shorter window than QUERY_HISTORY. They also find that some payroll access happened via a view that reads FIN.PAYROLL, so the direct object name does not appear in the query text.
1. Explain how to reconstruct the full 90-day evidence set given the user-name change and the view indirection.
Read the worked answer
Two separate problems. (1) User-name change: query history records the user at execution time. To reconstruct, join QUERY_HISTORY to USERS/login history and correlate the old and new user names for the service account (same role, same client, overlapping timeline). Document the mapping in the evidence package so the auditor sees continuity. (2) View indirection: QUERY_HISTORY text shows the view name, not the base table. Use ACCESS_HISTORY (which records objects read, including base tables accessed through views) to identify queries that touched FIN.PAYROLL indirectly, then join back to QUERY_HISTORY for user/IP/time. Because ACCESS_HISTORY has a shorter retention than QUERY_HISTORY, the 90-day window may exceed what ACCESS_HISTORY covers - so the team must state the coverage gap explicitly rather than imply complete evidence.
2. The team wants to 'just export everything to S3 monthly.' Evaluate this as a compliance control.
Read the worked answer
Exporting is useful but is not by itself a compliance control, and doing it naively creates new risk. Concerns: (1) The export bucket now contains sensitive payroll access logs - it needs its own access controls, encryption, and retention matching the audit requirement. (2) A monthly export leaves up to a month of exposure where the shorter-retention ACCESS_HISTORY data is already gone before export - schedule the export more frequently than the shortest retention window, or you lose evidence. (3) The export must be tamper-evident (object lock / versioning / checksums) or it is not credible evidence. (4) Define what 'everything' means - exporting all query text may capture sensitive literals; scope to the audit need. The right framing: the export is a retention bridge, and its value depends on cadence, integrity, and access control, not on the act of copying.