Export to Redshift

Parquet

First dump all tables to local parquet files from DuckDB.

COPY activities TO './activities.parquet' (FORMAT PARQUET);COPY addresses TO './addresses.parquet' (FORMAT PARQUET);COPY applications TO './applications.parquet' (FORMAT PARQUET);COPY decisions TO './decisions.parquet' (FORMAT PARQUET);COPY fields TO './fields.parquet' (FORMAT PARQUET);COPY form_responses TO './form_responses.parquet' (FORMAT PARQUET);COPY lookup_activities TO './lookup_activities.parquet' (FORMAT PARQUET);COPY lookup_decisions TO './lookup_decisions.parquet' (FORMAT PARQUET);COPY lookup_prompts TO './lookup_prompts.parquet' (FORMAT PARQUET);COPY mailings TO './mailings.parquet' (FORMAT PARQUET);COPY marketing_revenue_assumptions TO './marketing_revenue_assumptions.parquet' (FORMAT PARQUET);COPY messages TO './messages.parquet' (FORMAT PARQUET);COPY person_exposures TO './person_exposures.parquet' (FORMAT PARQUET);COPY person_tags TO './person_tags.parquet' (FORMAT PARQUET);COPY person_touches TO './person_touches.parquet' (FORMAT PARQUET);COPY persons TO './persons.parquet' (FORMAT PARQUET);COPY persons_enriched TO './persons_enriched.parquet' (FORMAT PARQUET);COPY pings TO './pings.parquet' (FORMAT PARQUET);COPY source_formats TO './source_formats.parquet' (FORMAT PARQUET);COPY source_records TO './source_records.parquet' (FORMAT PARQUET);COPY sources TO './sources.parquet' (FORMAT PARQUET);COPY statuses TO './statuses.parquet' (FORMAT PARQUET);COPY tags TO './tags.parquet' (FORMAT PARQUET);

Upload to S3

Using the AAWS CLI, upload all the parquet files to S3. This will take a while to upload.

aws s3 cp . s3://ucm-data-team/export/ --recursive --exclude "*" --include "*.parquet"

Copy from S3 to Redshift

In Redshift, create the tables and copy the data from S3 to tables.

DROP TABLE IF EXISTS public.activities;
CREATE TABLE public.activities (
  id VARCHAR(36),
  created_at TIMESTAMP,
  code VARCHAR(255),
  summary VARCHAR(65535),
  source_id VARCHAR(36),
  person_id VARCHAR(36)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.addresses;
CREATE TABLE public.addresses (
  record_id VARCHAR(36),
  city VARCHAR(255),
  county VARCHAR(255),
  region VARCHAR(255),
  country VARCHAR(255),
  postal_code VARCHAR(50),
  geomarket VARCHAR(255),
  type VARCHAR(255),
  rank INTEGER
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.applications;
CREATE TABLE public.applications (
  id VARCHAR(36),
  person_id VARCHAR(36),
  source_id VARCHAR(36),
  status VARCHAR(255),
  created_at TIMESTAMP,
  submitted_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.decisions;
CREATE TABLE public.decisions (
  application_id VARCHAR(36),
  released_at TIMESTAMP,
  code VARCHAR(255)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.fields;
CREATE TABLE public.fields (
  record_id VARCHAR(36),
  field VARCHAR(255),
  prompt VARCHAR(255),
  value VARCHAR(65535)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.form_responses;
CREATE TABLE public.form_responses (
  id VARCHAR(36),
  record_id VARCHAR(36),
  person_id VARCHAR(36),
  url VARCHAR(65535),
  utm_campaign VARCHAR(1024),
  utm_source VARCHAR(1024),
  utm_medium VARCHAR(1024),
  submitted_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.lookup_activities;
CREATE TABLE public.lookup_activities (
  code VARCHAR(255),
  name VARCHAR(65535),
  type VARCHAR(255)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.lookup_decisions;
CREATE TABLE public.lookup_decisions (
  code VARCHAR(255),
  name VARCHAR(65535)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.lookup_prompts;
CREATE TABLE public.lookup_prompts (
  id VARCHAR(255),
  prompt_value VARCHAR(65535),
  category VARCHAR(65535)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.mailings;
CREATE TABLE public.mailings (
  id VARCHAR(36),
  subject VARCHAR(65535),
  name VARCHAR(65535),
  active BOOLEAN,
  archived BOOLEAN,
  folder VARCHAR(65535),
  method VARCHAR(255),
  utm_source VARCHAR(1024),
  utm_campaign VARCHAR(1024),
  utm_content VARCHAR(1024),
  utm_term VARCHAR(1024),
  utm_medium VARCHAR(1024)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.messages;
CREATE TABLE public.messages (
  id VARCHAR(36),
  mailing_id VARCHAR(36),
  delivered_at TIMESTAMP,
  status VARCHAR(255),
  person_id VARCHAR(36)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.persons;
CREATE TABLE public.persons (
  id VARCHAR(36),
  status VARCHAR(255),
  ref_id VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.pings;
CREATE TABLE public.pings (
  id VARCHAR(255),
  person_id VARCHAR(36),
  url VARCHAR(65535),
  utm_campaign VARCHAR(1024),
  utm_source VARCHAR(1024),
  utm_medium VARCHAR(1024),
  created_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.sources;
CREATE TABLE public.sources (
  id VARCHAR(36),
  format_id VARCHAR(36),
  summary VARCHAR(65535),
  created_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.source_formats;
CREATE TABLE public.source_formats (
  id VARCHAR(36),
  name VARCHAR(65535)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.source_records;
CREATE TABLE public.source_records (
  id VARCHAR(36),
  source_id VARCHAR(36),
  record_id VARCHAR(36)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.statuses;
CREATE TABLE public.statuses (
  id VARCHAR(36),
  record_id VARCHAR(36),
  status VARCHAR(255),
  changed_at TIMESTAMP,
  rank INTEGER,
  source VARCHAR(36)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.tags;
CREATE TABLE public.tags (
  id VARCHAR(36),
  tag VARCHAR(255),
  person_id VARCHAR(36)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.person_touches;
CREATE TABLE public.person_touches (
  person_id VARCHAR(36),
  url VARCHAR(65535),
  utm_campaign VARCHAR(1024),
  utm_source VARCHAR(1024),
  utm_medium VARCHAR(1024),
  touched_at TIMESTAMP,
  touch_type VARCHAR(255)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.person_exposures;
CREATE TABLE public.person_exposures (
  person_id VARCHAR(36),
  exposure_type VARCHAR(255),
  exposure_name VARCHAR(65535),
  exposure_at TIMESTAMP,
  mailing_id VARCHAR(36),
  source VARCHAR(255)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.person_tags;
CREATE TABLE public.person_tags (
  person_id VARCHAR(36),
  tag_type VARCHAR(255),
  tag_value VARCHAR(65535),
  tag_date TIMESTAMP,
  source VARCHAR(255)
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.persons_enriched;
CREATE TABLE public.persons_enriched (
  person_id VARCHAR(36),
  status VARCHAR(255),
  ref_id VARCHAR(255),
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
DISTSTYLE AUTO
SORTKEY AUTO;

DROP TABLE IF EXISTS public.marketing_revenue_assumptions;
CREATE TABLE public.marketing_revenue_assumptions (
  student_group VARCHAR(255),
  avg_net_tuition_revenue DECIMAL(12,2)
)
DISTSTYLE AUTO
SORTKEY AUTO;

Now copy in the data from S3.

COPY public.activities FROM 's3://ucm-data-team/slate_export/activities.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.addresses FROM 's3://ucm-data-team/slate_export/addresses.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.applications FROM 's3://ucm-data-team/slate_export/applications.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.decisions FROM 's3://ucm-data-team/slate_export/decisions.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.fields FROM 's3://ucm-data-team/slate_export/fields.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.form_responses FROM 's3://ucm-data-team/slate_export/form_responses.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.lookup_activities FROM 's3://ucm-data-team/slate_export/lookup_activities.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.lookup_decisions FROM 's3://ucm-data-team/slate_export/lookup_decisions.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.lookup_prompts FROM 's3://ucm-data-team/slate_export/lookup_prompts.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.mailings FROM 's3://ucm-data-team/slate_export/mailings.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.marketing_revenue_assumptions FROM 's3://ucm-data-team/slate_export/marketing_revenue_assumptions.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.messages FROM 's3://ucm-data-team/slate_export/messages.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.person_exposures FROM 's3://ucm-data-team/slate_export/person_exposures.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.person_tags FROM 's3://ucm-data-team/slate_export/person_tags.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.person_touches FROM 's3://ucm-data-team/slate_export/person_touches.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.persons FROM 's3://ucm-data-team/slate_export/persons.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.persons_enriched FROM 's3://ucm-data-team/slate_export/persons_enriched.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.pings FROM 's3://ucm-data-team/slate_export/pings.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.source_formats FROM 's3://ucm-data-team/slate_export/source_formats.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.source_records FROM 's3://ucm-data-team/slate_export/source_records.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.sources FROM 's3://ucm-data-team/slate_export/sources.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.statuses FROM 's3://ucm-data-team/slate_export/statuses.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;

COPY public.tags FROM 's3://ucm-data-team/slate_export/tags.parquet'
IAM_ROLE 'arn:aws:iam::ACCOUNT_ID:role/YOUR_REDSHIFT_ROLE'
FORMAT AS PARQUET;