Why this matters
As a Data Platform Engineer, you are accountable for who accessed which data, when, and why. Strong audit logging and regular access reviews reduce breach impact, prove compliance, and build trust with stakeholders.
- Investigate incidents: Trace a suspicious query back to a user, time, IP, and client.
- Meet compliance requests: Export evidence of quarterly access reviews and approvals.
- Operate safely: Detect overly broad roles and stale service accounts before they cause damage.
Concept explained simply
Audit logging captures security-relevant events (who did what, when, from where, to which resource). Access reviews are periodic checks to confirm that each person or service still needs their permissions.
Mental model
Think of audit logs as a security camera for your data systems. Access reviews are the regular check where you audit who holds keys and take back the ones no longer needed. Together, they form a closed loop: record everything, then trim access based on evidence.
What good looks like
- Coverage: Logs for data warehouses, data lakes, orchestration tools, consoles, and identity providers.
- Completeness: Critical fields captured: timestamp (UTC), actor (user/service), action, resource, outcome, source IP, client/app, request ID.
- Retention: 12–24 months online; longer in cold storage if required by policy.
- Integrity: Write-once or tamper-evident storage and unique event IDs.
- Routing: Centralized pipeline to a searchable store with basic dashboards.
- Detection: Alerts for high-risk patterns (e.g., mass downloads, failed logins burst, off-hours PII access).
- Evidence: Exportable reports of events and review decisions with timestamps and reviewers.
- Privacy: Minimize sensitive payloads in logs; mask data values; store metadata not raw data.
Worked examples
Example 1 — Warehouse query audit
Goal: Record every query with actor and outcome.
- Enable query, login, and access logs at the platform level.
- Define a minimal schema: event_id, ts_utc, actor_type, actor_id, action, resource, outcome, source_ip, client, request_id.
- Route logs to a central store and build a dashboard: queries by actor, failed logins over time, top accessed schemas.
- Alert: If a human account queries sensitive schema outside business hours, notify the on-call channel.
Example 2 — Data lake object access
Goal: Track object read/write events and detect unusual patterns.
- Turn on object-level access events in your cloud provider.
- Log read/write/delete with principal, bucket/container, object path, and client.
- Create a rule: Alert when a single principal downloads more than N GB in an hour from a sensitive prefix.
- Retention: 400 days online; archive to cold storage after.
Example 3 — Quarterly access review for analytics
Goal: Certify access for the analytics group.
- Scope: All roles granting access to production datasets and BI tools.
- Prepare evidence: Last-login date, top accessed datasets, role membership, manager info.
- Reviewers: Data owners approve dataset access; managers approve role memberships; platform team approves service accounts.
- Decisions: Keep, Reduce (least privilege), or Revoke (no activity for 90 days).
- Record decisions and apply changes within 5 business days; export a signed PDF/CSV as evidence.
Core processes and cadence
Daily
- Ingest and validate logs; check pipeline health.
- Review critical alerts and triage.
Monthly
- Report on top access patterns and dormant accounts.
- Test a random sample of events for completeness.
Quarterly
- Run formal access reviews for production data and admin roles.
- Document decisions and apply revocations promptly.
Implementation checklists
Audit log fields (aim for all):
- Unique event_id (UUID)
- Timestamp in UTC
- Actor type (human/service)
- Actor id (user id, service account)
- Action (login, query, read_object, grant_role, etc.)
- Resource (warehouse DB.table, lake path, role name)
- Outcome (success/deny/error)
- Source IP and client/app
- Request/correlation id
Access review readiness:
- Inventory of roles, groups, datasets, owners
- Mapping of datasets to data owners
- Managers for human accounts; owners for service accounts
- Activity evidence (last login, recent accesses)
- Standard decision outcomes and revocation SLAs
Common mistakes and self-check
- Logging payload data instead of metadata, leaking sensitive values.
- Relying only on platform defaults; missing object-level or admin events.
- No tamper protection; logs are modifiable by admins being audited.
- Reviews without evidence; decisions become rubber-stamps.
- One-time cleanup with no recurring cadence.
Self-check prompts
- Can you answer: who accessed a sensitive table in the last 30 days?
- Can non-auditors delete logs?
- Do you have evidence of the last quarterly review with decisions and timestamps?
- How quickly can you revoke access after a decision?
Practical projects
- Build a minimal audit log pipeline: collect, normalize, store, and dashboard two event types (e.g., query and login).
- Create a quarterly review packet generator that compiles access evidence per team.
- Implement an alert for mass exports from a sensitive dataset and test the on-call workflow.
Exercises
Complete these, then take the Quick Test below. The test is available to everyone; if you are logged in, your progress will be saved.
Exercise 1 — Minimal audit log schema and samples
Design a minimal, normalized audit log schema and produce three sample events: a successful query by a human, a denied read by a service account, and a successful role grant by an admin.
Exercise 2 — Plan an access review for a new BI team
Define scope, reviewers, decision rules, and an evidence template for a BI team that needs read access to sales and marketing datasets.
Mini challenge
You discover that 15% of events lack a source IP because one ingest source is missing that field. Outline a two-step plan to fix forward and backfill with clear acceptance criteria.
Hint
Start with schema enforcement on ingest, then identify and enrich from upstream logs where possible.
Who this is for
- Data Platform Engineers enabling secure, compliant data access
- Data Engineers who own pipelines touching sensitive data
- Analytics platform owners and security-minded engineers
Prerequisites
- Basic understanding of data platform components (warehouse, lake, orchestration)
- Familiarity with identity and access concepts (users, groups, roles)
- Comfort with JSON/SQL for log schemas and querying
Learning path
- Understand event coverage and required fields
- Implement a central log pipeline and storage
- Add alerts for high-risk behaviors
- Run and document an access review
- Automate evidence generation and revocation
Next steps
- Finish the exercises above.
- Take the Quick Test to check your understanding.
- Pick one Practical project and deliver it end-to-end within a week.