Security And Governance

Why Security and Governance matter for Data Engineers

Security and governance ensure your data platform protects sensitive information, meets regulations, and stays trustworthy. As a Data Engineer, you design pipelines, storage, and access paths—making you a first line of defense. Strong practices reduce risk, speed audits, and unlock collaboration without exposing sensitive data.

• Protect customer trust and business reputation
• Enable safe collaboration across teams
• Meet regulatory and contractual requirements
• Prevent costly incidents and simplify audits

Who this is for

• Aspiring and current Data Engineers building pipelines, storage, and analytics platforms
• Analytics Engineers and Platform Engineers who touch data access or transformations
• Team leads standardizing secure data practices

Prerequisites

• Comfort with at least one cloud or on‑prem platform (e.g., object storage, SQL data warehouse)
• Basic SQL and one scripting language (e.g., Python)
• Familiarity with ETL/ELT patterns

Learning path

1. Week 1 — Access Foundations
   • Understand IAM, roles vs. users, least privilege
   • Set up role-based access to a bucket/table
2. Week 2 — Secrets and Encryption
   • Manage credentials without hardcoding
   • Enable encryption in transit (TLS) and at rest
3. Week 3 — PII, Logging, Lineage
   • Classify PII, implement masking
   • Enable audit logs and trace data lineage
4. Week 4 — Compliance and Reviews
   • Map controls to your platform (e.g., access reviews)
   • Run a small internal audit and remediation

Worked examples

1) IAM: read-only access to a data prefix

Goal: Allow a data scientist to read a curated dataset without write permissions or access to raw data.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject", "s3:ListBucket"],
      "Resource": [
        "arn:aws:s3:::company-curated",
        "arn:aws:s3:::company-curated/analytics/*"
      ],
      "Condition": {
        "StringLike": {"s3:prefix": ["analytics/*"]}
      }
    }
  ]
}

2) Secrets: use env vars + rotation-friendly pattern

Goal: Never hardcode credentials. Inject via environment variables populated by a secrets manager.

import os
from time import sleep

DB_USER = os.getenv("APP_DB_USER")
DB_PASS = os.getenv("APP_DB_PASS")  # rotated externally

assert DB_USER and DB_PASS, "Missing DB creds"

# Simulate long-running job that re-reads secrets periodically
for _ in range(6):
    # In production, reload from file/sidecar/parameter store instead of env if supported
    DB_PASS = os.getenv("APP_DB_PASS")
    # connect_and_run(DB_USER, DB_PASS)
    sleep(10)

3) Encryption in transit with a SQL warehouse

Goal: Ensure TLS is used end-to-end.

# Example DSN with TLS parameters (shape varies by driver)
# postgresql://user:pass@host:5432/db?sslmode=require

import psycopg2
conn = psycopg2.connect(
    dsn="postgresql://user:pass@db.example:5432/analytics?sslmode=require"
)
cur = conn.cursor()
cur.execute("SELECT current_setting('ssl')")
print(cur.fetchone())  # Expect 'on' or similar

4) PII masking view for analytics

Goal: Analysts see only masked email/phone, while authorized roles can query raw tables.

-- Raw table: customer_raw(email, phone, country, created_at)
-- Masked view for general analytics use
CREATE OR REPLACE VIEW customer_masked AS
SELECT
  CASE
    WHEN current_user IN (SELECT user_name FROM pii_readers)
      THEN email
    ELSE CONCAT(SUBSTRING(email, 1, 2), '***@', SPLIT_PART(email, '@', 2))
  END AS email_masked,
  CASE
    WHEN current_user IN (SELECT user_name FROM pii_readers)
      THEN phone
    ELSE CONCAT('(***)***-', RIGHT(phone, 4))
  END AS phone_masked,
  country,
  created_at
FROM customer_raw;

5) Audit logging: include IDs for traceability

Goal: Each pipeline action logs request_id, actor, resource, and outcome.

import json, logging, uuid

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("pipeline")

req_id = str(uuid.uuid4())
entry = {
  "ts": "2026-01-08T00:00:00Z",
  "request_id": req_id,
  "actor": "etl-service-role",
  "action": "load_table",
  "resource": "warehouse.sales_daily",
  "outcome": "success"
}
logger.info(json.dumps(entry))

6) Simple column-level lineage mapping

Goal: Track how output columns derive from inputs.

{
  "dataset": "warehouse.sales_daily",
  "columns": [
    {"name": "order_id", "source": "raw.orders.order_id"},
    {"name": "customer_region", "source": "raw.customers.country"},
    {"name": "gross_revenue", "source": "raw.orders.quantity * raw.orders.unit_price"}
  ],
  "owner": "data-eng",
  "last_updated": "2026-01-08"
}

Drills and quick exercises

• [ ] Identify which tables in your warehouse contain direct identifiers (email, phone, government ID). Tag them.
• [ ] Create a role that allows SELECT only on curated datasets. Test with a non-admin user.
• [ ] Prove TLS is enabled for your database connection by checking connection parameters and server settings.
• [ ] Rotate one secret used by a dev pipeline and keep the job running without restart.
• [ ] Add request_id and actor fields to your pipeline logs. Trigger a failure and ensure it’s logged.
• [ ] Document lineage for one important data mart at column level.

Common mistakes and debugging tips

Mini project: Secure Customer Analytics Pipeline

Build a small pipeline that ingests customer orders, masks PII in analytics outputs, and provides auditable, lineage-tracked transformations.

1. Define Datasets
   • raw.customers(id, email, phone, country)
   • raw.orders(id, customer_id, quantity, unit_price, created_at)
2. Access Controls
   • Create a role analytics_reader that can read only curated views
   • Restrict direct access to raw tables
3. Secrets
   • Load DB credentials from environment variables or secrets files mounted at runtime
   • Demonstrate rotation by changing the secret and keeping pipeline operational
4. Encryption
   • Enable TLS for database connections
   • Ensure storage encryption at rest is turned on for your object store or database
5. Transform + Mask
   • Create curated.customer_sales with masked email/phone
   • Include derived columns (gross_revenue, customer_region)
6. Audit Logging
   • Log start/end of each pipeline stage with request_id and actor
   • Log failures with error details
7. Lineage
   • Produce a JSON file mapping output columns to input sources
8. Validation
   • Attempt to query raw tables with analytics_reader; expect permission denied
   • Verify TLS in connection metadata
   • Show logs for a successful and a failed run

Practical projects

• Harden a data lake zone: implement prefix-level IAM, bucket policies, and object tagging for PII
• Warehouse governance starter kit: masked views, role hierarchy, and quarterly access reviews
• Lineage-aware ETL: generate and validate column-level lineage as part of CI for SQL models

Subskills

• IAM And Role Based Access: Create and maintain least-privilege roles for datasets, jobs, and services.
• Secrets Management Basics: Inject credentials at runtime, avoid hardcoding, and support rotation.
• Encryption In Transit And At Rest: Enforce TLS for connections and enable storage-level encryption.
• PII Handling And Masking: Identify sensitive fields and expose only masked or aggregated data to broad audiences.
• Audit Logging Basics: Produce structured, queryable logs with request_id and actor for every critical action.
• Data Lineage Concepts: Track how outputs depend on inputs at table and column level.
• Compliance Awareness Basics: Map platform controls to common obligations and document evidence.
• Access Review Processes: Run periodic entitlement reviews and remove stale privileges.

Next steps

• Pick one pipeline and implement at least two improvements (e.g., masked view + access review)
• Document your controls: what exists today, owners, review cadence
• Take the skill exam below to validate your understanding. Anyone can take it; saved progress is available for logged-in users.