Authenticating...
Skip to main content

0034: Redshift Data Sharing: OGAds Data Warehouse (Private Producer) → AdAction Data Warehouse (Public Consumer)

STATUS​

Proposed

CONTEXT​

Our organization operates two separate AWS accounts with distinct data warehouse requirements:

  • OGAds Account (*): Houses a private Redshift cluster that serves as the primary data warehouse for OGAds operations. This cluster is configured with PubliclyAccessible=false for enhanced security.
  • AdAction Data Warehouse Account (981558345463): Contains a public Redshift cluster that needs access to OGAds data for business intelligence and analytics purposes. This cluster is publicly accessible but locked down with security controls.

The current challenge is enabling AdAction's BI team to access live, read-only data from OGAds' private cluster without compromising security or creating complex data replication pipelines. Traditional approaches like UNLOAD/COPY operations or DMS replication introduce latency, complexity, and maintenance overhead.

Considered Options​

  • Option 1: Amazon Redshift Data Sharing with PrivateLink connectivity
  • Option 2: Amazon Redshift Data Sharing with VPC Peering connectivity
  • Option 3: Amazon Redshift Data Sharing with Transit Gateway connectivity
  • Option 4: S3-based data lake with Lake Formation cross-account access
  • Option 5: Traditional UNLOAD/COPY operations with scheduled data transfers
  • Option 6: AWS DMS for real-time data replication

DECISION​

We will adopt Amazon Redshift Data Sharing with private-only connectivity from the public consumer (AdGem) to the private producer (OGAds), implementing compensating controls for the public consumer cluster.

Architecture Overview​

Key Implementation Details​

  1. Security Controls: Implement compensating controls on the public consumer

    • Strict Security Group allowlists (deny-by-default)
    • TLS encryption enforced
    • KMS Customer Managed Keys (CMKs)
    • Enhanced VPC Routing enabled*
      • This is already enabled
    • Comprehensive CloudTrail logging
    • Namespace-scoped grants and monitoring

  2. Cross-Region Network Architecture:

    • Inter-Region Connectivity: Use VPC Peering or Transit Gateway for cross-region communication
    • PrivateLink Endpoints: Configure region-specific VPC endpoints for Redshift service
    • Security Groups: Update rules to allow cross-region traffic on port 5439
    • Route Tables: Ensure proper routing between regions for data sharing traffic
    • DNS Resolution: Configure cross-region DNS resolution for cluster endpoints

  3. Data Sharing Configuration:

    • Create data shares on producer cluster for specific schemas/tables
    • Associate data shares with consumer cluster
    • Grant read-only access to consumer database users
    • Monitor data share usage and performance

CONSEQUENCES​

Positive Outcomes​

  • Cross-cluster database joins: The AdAction Data Engineers will be able to better assist the OGAds side with business intelligence insights
  • Real-time Data Access: AdGem BI team gets live, read-only access to OGAds data without replication delays
  • Simplified Architecture: Eliminates complex ETL pipelines and data synchronization processes
  • Cost Efficiency: Reduces storage duplication and compute overhead from data replication
  • Security: Maintains OGAds cluster privacy while enabling controlled data access
  • Scalability: Data sharing scales automatically with cluster performance

Negative Outcomes​

  • Cross-Account Governance: Requires coordination between OGAds and AdGem teams for schema changes
  • Network Dependency: Data sharing requires stable network connectivity between accounts
  • Cross-Region Data Transfer Costs: Additional charges apply for data transfer between regions
    • Data Transfer Out: $0.02 per GB for first 1TB, then $0.015 per GB (US East to US West)
    • Data Transfer In: Free for most regions, but verify current pricing
    • Query Result Transfer: Costs apply to result sets returned to consumer region
    • Monitoring Required: Track data transfer volumes to estimate monthly costs
  • Complexity: Adds network configuration complexity for private connectivity

Risks​

  • Network Connectivity Issues: Data sharing will fail if private connectivity is disrupted
  • Security Exposure: Public consumer cluster remains a potential attack vector despite compensating controls
  • Performance Impact: Cross-account queries may have higher latency than local queries
  • Dependency Risk: AdGem becomes dependent on OGAds cluster availability and performance

ROLLOUT PLAN​

Phase 1: Network Connectivity Setup​

  1. Security Configuration
    • Implement strict security groups on AdGem cluster
    • Enable Enhanced VPC Routing, if needed
    • Configure KMS CMKs for encryption
      • KMS Key ARN: arn:aws:kms:us-east-2:981558345463:key/e08e22cd-f2a7-42d8-acbc-fab383d7131e -[ ] Set up CloudTrail logging for data share activities

Phase 2: Data Share Implementation​

  1. Producer Configuration

    -- Create data share for AdGem access
    CREATE DATASHARE aa_shared_data;
    ALTER DATASHARE aa_shared_data SET PUBLICACCESSIBLE TRUE;  


    -- Add schemas to the data share
    ALTER DATASHARE aa_shared_data ADD SCHEMA public;
    ALTER DATASHARE aa_shared_data ADD SCHEMA analytics;

    -- Grant usage on specific tables
    GRANT USAGE ON DATASHARE aa_shared_data TO NAMESPACE 'arn:aws:redshift:us-east-1:981558345463:namespace:consumer-namespace-id';

  2. Consumer Configuration

    -- Associate the data share
    CREATE DATABASE ogads_shared FROM DATASHARE aa_shared_data;

    -- Grant access to BI, Data Engineers, etc, users
    GRANT USAGE ON DATABASE ogads_shared TO ROLE reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO ROLE reader;

Phase 3: Validation and Monitoring​

  1. Connectivity Testing

    • Validate data share association and access
    • Test query performance and latency
    • Verify security controls are functioning

  2. Monitoring Setup

    • Configure CloudWatch alarms for data share metrics
    • Set up monitoring for network connectivity
    • Implement alerting for security events

Phase 4: User Communication​

  1. Documentation

    • Update BI team documentation with new connection details
    • Provide query examples and best practices
    • Document troubleshooting procedures

  2. Training

    • Conduct training sessions for BI team
    • Share performance optimization guidelines
    • Establish support procedures

ROLLBACK PLAN​

Immediate Rollback (Data Share)​

  1. Consumer Account

    -- Drop consumer database
    DROP DATABASE ogads_shared;

  2. Producer Account

    -- Disassociate data share
    ALTER DATASHARE aa_shared_data DISASSOCIATE NAMESPACE 'arn:aws:redshift:us-east-1:131038284129:namespace:consumer-namespace-id';

    -- Revoke usage on data share
    REVOKE USAGE ON DATASHARE aa_shared_data FROM NAMESPACE 'arn:aws:redshift:us-east-1:131038284129:namespace:consumer-namespace-id';

    -- Drop data share if no longer needed
    DROP DATASHARE aa_shared_data;

Network Rollback​

  1. Remove VPC Endpoint
    • Delete VPC endpoint from AdGem VPC
    • Remove associated security group rules
    • Clean up route table entries

Complete Rollback Verification​

  1. Security Verification

    • Confirm no cross-account access remains
    • Verify security groups are properly configured
    • Check CloudTrail logs for any residual activity

  2. Network Verification

    • Test that no connectivity exists between accounts
    • Verify DNS resolution is not affected
    • Confirm no performance impact on existing workloads

RUNBOOK​

AWS CLI Commands for Data Sharing Setup​

Producer Account (OGAds)​

# Set producer credentials
export PRODUCER_PROFILE="xxx"
export PRODUCER_REGION="us-east-x"

# Create data share
aws redshift-data execute-statement \
  --profile $PRODUCER_PROFILE \
  --region $PRODUCER_REGION \
  --cluster-identifier ogads-cluster \
  --database ogads_db \
  --sql "CREATE DATASHARE aa_shared_data;"

# Add schemas to data share
aws redshift-data execute-statement \
  --profile $PRODUCER_PROFILE \
  --region $PRODUCER_REGION \
  --cluster-identifier ogads-cluster \
  --database ogads_db \
  --sql "ALTER DATASHARE aa_shared_data ADD SCHEMA public;"

# Grant usage to consumer namespace
aws redshift-data execute-statement \
  --profile $PRODUCER_PROFILE \
  --region $PRODUCER_REGION \
  --cluster-identifier ogads-cluster \
  --database ogads_db \
  --sql "GRANT USAGE ON DATASHARE aa_shared_data TO NAMESPACE 'arn:aws:redshift:us-east-1:131038284129:namespace:CONSUMER_NAMESPACE_ID';"

Consumer Account (AdGem)​

# Set consumer credentials
export CONSUMER_PROFILE="superuser-adgem"
export CONSUMER_REGION="us-east-2"

# Associate data share
aws redshift-data execute-statement \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --cluster-identifier adgem-cluster \
  --database adgem_db \
  --sql "CREATE DATABASE ogads_shared FROM DATASHARE aa_shared_data OF NAMESPACE 'arn:aws:redshift:us-east-1:981558345463:namespace:PRODUCER_NAMESPACE_ID';"

# Grant access to BI users
aws redshift-data execute-statement \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --cluster-identifier adgem-cluster \
  --database adgem_db \
  --sql "GRANT USAGE ON DATABASE ogads_shared TO GROUP bi_users;"

Consumer Region VPC Endpoint​

# Create VPC endpoint for Redshift service in consumer region
aws ec2 create-vpc-endpoint \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --vpc-id vpc-08ad4260c5731e6c2 \
  --service-name com.amazonaws.$CONSUMER_REGION.redshift \
  --vpc-endpoint-type Interface \
  --subnet-ids subnet-xxx subnet-yyy \
  --security-group-ids sg-xxx \
  --private-dns-enabled

Cross-Region Connectivity Setup​

# Option 1: VPC Peering (for same-account or trusted accounts)
aws ec2 create-vpc-peering-connection \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --vpc-id vpc-08ad4260c5731e6c2 \
  --peer-vpc-id vpc-026e5a00cb56e629a \
  --peer-region us-east-1

# Option 2: Transit Gateway (for complex multi-region scenarios)
aws ec2 create-transit-gateway \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --description "Cross-region Redshift connectivity"

# Attach VPCs to Transit Gateway
aws ec2 create-transit-gateway-vpc-attachment \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --transit-gateway-id tgw-xxx \
  --vpc-id vpc-08ad4260c5731e6c2 \
  --subnet-ids subnet-xxx subnet-yyy

Monitoring and Troubleshooting​

Cross-Region Connectivity Monitoring​

# Test cross-region connectivity
aws ec2 describe-vpc-peering-connections \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --filters "Name=status-code,Values=active"

# Check Transit Gateway attachments (if using TGW)
aws ec2 describe-transit-gateway-attachments \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION

# Monitor cross-region data transfer costs
aws ce get-cost-and-usage \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --time-period Start=2024-01-01,End=2024-01-31 \
  --granularity MONTHLY \
  --metrics BlendedCost \
  --group-by Type=DIMENSION,Key=SERVICE

Data Share Performance Monitoring​

# Check data share status
aws redshift describe-data-shares \
  --profile $PRODUCER_PROFILE \
  --region $PRODUCER_REGION

# Monitor data share associations
aws redshift describe-data-shares \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION

# Check VPC endpoint status
aws ec2 describe-vpc-endpoints \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --filters "Name=service-name,Values=com.amazonaws.us-east-1.redshift"

# Monitor cross-region latency
aws cloudwatch get-metric-statistics \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --namespace AWS/Redshift \
  --metric-name DatabaseConnections \
  --dimensions Name=ClusterIdentifier,Value=adgem-cluster \
  --start-time 2024-01-01T00:00:00Z \
  --end-time 2024-01-01T23:59:59Z \
  --period 300 \
  --statistics Average

Cross-Region Troubleshooting​

# Test DNS resolution across regions
nslookup ogads-cluster.redshift.us-east-1.amazonaws.com

# Check security group rules for cross-region access
aws ec2 describe-security-groups \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --group-ids sg-xxx

# Verify route table configuration
aws ec2 describe-route-tables \
  --profile $CONSUMER_PROFILE \
  --region $CONSUMER_REGION \
  --filters "Name=vpc-id,Values=vpc-08ad4260c5731e6c2"

NOTES​

References​

Original Author​

Platform Team

Approval date​

TBD

Approved by​

TBD