Transformation, Part 1

Transforming CSVs (aka "text flat files") into relational data is both challenging and fun. Let's impose some typing and rules for the incoming data so that we can rely on it in our analysis:

drop schema if exists enceladus cascade;
create schema enceladus;

set search_path='enceladus';
create table inms(
  id bigserial primary key,
  created_at timestamp not null,
  altitude numeric(9,2) not null check(altitude > 0),
  source text not null check(source in('osi','csn','osnb','osnt'))
  mass numeric(6,3) not null check(mass >=0.125 and mass < 100),
  high_sensitivity_count int not null check(high_sensitivity_count > 0),
  low_sensitivity_count int not null check(low_sensitivity_count > 0)
);

Does this look overwhelming to you? That's OK - in the next video we're going to break things down a bit.