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

CCC Data Warehouse - Streaming Data to Your Local Data Warehouse

Introduction

Colleges who use AWS 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

The subscription service only transmits to AWS S3 buckets.

Setting up Your Subscription

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 receiver AWS account information

For each table to be distributed, include this information:

  • Database Name

  • Database Table

  • Frequency (Daily, Monthly, etc.)

  • Point of Contact at the College for Validation

  • Destination (S3 address)

Staff support will send you the Tech Center AWS Account ID. Once you have this ID number, proceed to the next section.

Create a Role in College Account (bucket owner)

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

In the College Account:

  1. Go to IAM > Roles > Create Role.

  2. Trusted entity type: Another AWS account.

  3. Account ID: enter the Tech Center AWS Account ID.

  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/*"     }   ] }