/
CCC Data Warehouse - Streaming Data to Your Local Data Warehouse

CCC Data Warehouse - Streaming Data to Your Local Data Warehouse

Introduction

Colleges can set up their systems to automatically pull down systemwide data from the CCCTC Data Warehouse to their local data warehouse, similar to a subscription. This provides colleges the ability to use data locally and the ability combine it with other local data for analysis. This subscription model also enables the CCCTC to better serve colleges by:

  • implementing a cost saving method of downloading only changed data as opposed to downloading the entire data set

  • controlling quality by ensuring data in the local Data Warehouse matches what is in the CCTC Data Warehouse

  • synched up data improves the efficiency in diagnosing issues at the college level

Setting up Your Subscription

To allow Tech Center Account to write into an S3 bucket owned by College Account:

Create a Role in College Account (bucket owner)

Once created, this role will be assumed by the CCCTC Account.

In College Account:

  1. Go to IAM > Roles > Create Role.

  2. Trusted entity type: Another AWS account.

  3. Account ID: enter the Tech Center Account's AWS Account ID (this will be shared by the tech center. For more details, contact Mark Cohen).

  4. Check Require external ID (optional but recommended for added security).

Attach permissions to this role:

{ "Version": "2012-10-17",   "Statement": [     {       "Sid": "AllowS3PutAccess",       "Effect": "Allow",       "Action": [         "s3:PutObject",         "s3:PutObjectAcl"       ],       "Resource": "arn:aws:s3:::my-bucket-name/*"     }   ] }

  

 

Add a Trust Policy to the role:

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam:: Tech Center Account:root" }, "Action": "sts:AssumeRole" } ]  }

 

  1. Name the role (e.g., TechCenterAccount-WriteToBucketB) and create it.

Update the S3 Bucket Policy in College Account

Allow the role from the College Account to write to the bucket.

{   "Version": "2012-10-17",   "Statement": [     {       "Sid": "AllowAccountARolePutObject",       "Effect": "Allow",       "Principal": {         "AWS": "arn:aws:iam::ACCOUNT_B_ID:role/AccountA-WriteToBucketB"       },       "Action": [         "s3:PutObject",         "s3:PutObjectAcl"       ],       "Resource": "arn:aws:s3:::my-bucket-name/*"     }   ] }

Submit Metadata to the Technology Center

The Technology Center needs metadata to complete the streaming setup. Send the following information to staff support at staffsupportccctc@openccc.zendesk.com:

  • If your college uses AWS, include the AWS account information

For each table to be distributed, include this information:

  • Database Name

  • Database Table

  • Load Type (Full or Incremental)

  • Frequency (Daily, Monthly, etc.)

  • Point of Contact for Validation

  • Destination (Database, S3, etc.)