Secure Data Warehouses with Expert Policies

This skill acts as an expert in designing, implementing, and maintaining comprehensive security policies for data warehouses across cloud and on-premise environments, covering access controls, encryption standards, compliance frameworks, audit logging, and security monitoring. It grounds its guidance in three core principles: defense in depth (layering network, authentication, data, application, and physical controls), principle of least privilege (granular RBAC, time-bound access grants, regular access reviews, automated deprovisioning), and zero trust architecture (continuous authentication, micro-segmentation, contextual access decisions based on user/device/location).

For access control it provides worked examples of role-based access control in Snowflake - hierarchical roles, schema-level grants, and row access policies - alongside an attribute-based access control (ABAC) policy engine pattern that evaluates subject, resource, and environment attributes. For encryption it covers at-rest configuration (a Terraform example enabling KMS-backed encryption on an AWS Redshift cluster) and in-transit setup (certificate-based TLS connections to Snowflake).

On compliance it addresses GDPR with data lineage/retention table design and a right-to-be-forgotten stored procedure, and SOX with automated segregation-of-duties checks and unapproved-data-change detection queries. For monitoring it defines anomaly detection logic that flags large data extractions, destructive operations, off-hours access, and unauthorized data exports:

CREATE VIEW suspicious_activities AS
SELECT 
    user_name,
    query_text,
    execution_time,
    rows_produced,
    CASE 
        WHEN rows_produced > 1000000 THEN 'Large data extraction'
        WHEN query_text ILIKE '%DROP%' OR query_text ILIKE '%DELETE%' THEN 'Destructive operation'
        WHEN HOUR(start_time) NOT BETWEEN 6 AND 22 THEN 'Off-hours access'
        WHEN user_name NOT IN (SELECT approved_users FROM service_accounts) 
             AND query_text ILIKE '%COPY%' THEN 'Unauthorized data export'
    END as risk_type
FROM query_history
WHERE start_time >= CURRENT_DATE - 1
  AND risk_type IS NOT NULL;

plus a Kafka-based real-time alert processor for critical security events like failed login spikes, privilege escalation, and bulk data extraction.

Use it when designing or hardening a data warehouse's security posture: setting up RBAC/ABAC access control, configuring encryption at rest and in transit, implementing GDPR or SOX compliance controls, or building anomaly detection and alerting. It is oriented toward Snowflake, AWS Redshift, and Terraform-based infrastructure specifically in its worked examples - other platforms will need the same principles translated to their own tooling.

Output is policy guidance and working code (SQL, Python, YAML/Terraform) implementing the requested control, plus governance recommendations: a cross-functional Data Security Committee, quarterly policy reviews, documented security controls, regular penetration testing, and incident response playbooks. Operational recommendations include comprehensive audit logging, automated backup/disaster recovery, infrastructure-as-code security configuration, and secure CI/CD for schema changes.

Data engineers, security architects, and compliance teams responsible for securing enterprise data warehouses - defining access control, encryption, regulatory compliance, and monitoring policies for cloud or on-premise systems.