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:

<span class="hljs-keyword">drop</span> schema if <span class="hljs-keyword">exists</span> enceladus cascade;
<span class="hljs-keyword">create</span> schema enceladus;

<span class="hljs-keyword">set</span> search_path<span class="hljs-operator">=</span><span class="hljs-string">&#x27;enceladus&#x27;</span>;
<span class="hljs-keyword">create table</span> inms(
  id bigserial <span class="hljs-keyword">primary key</span>,
  created_at <span class="hljs-type">timestamp</span> <span class="hljs-keyword">not null</span>,
  altitude <span class="hljs-type">numeric</span>(<span class="hljs-number">9</span>,<span class="hljs-number">2</span>) <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(altitude <span class="hljs-operator">&gt;</span> <span class="hljs-number">0</span>),
  source text <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(source <span class="hljs-keyword">in</span>(<span class="hljs-string">&#x27;osi&#x27;</span>,<span class="hljs-string">&#x27;csn&#x27;</span>,<span class="hljs-string">&#x27;osnb&#x27;</span>,<span class="hljs-string">&#x27;osnt&#x27;</span>))
  mass <span class="hljs-type">numeric</span>(<span class="hljs-number">6</span>,<span class="hljs-number">3</span>) <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(mass <span class="hljs-operator">&gt;=</span><span class="hljs-number">0.125</span> <span class="hljs-keyword">and</span> mass <span class="hljs-operator">&lt;</span> <span class="hljs-number">100</span>),
  high_sensitivity_count <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(high_sensitivity_count <span class="hljs-operator">&gt;</span> <span class="hljs-number">0</span>),
  low_sensitivity_count <span class="hljs-type">int</span> <span class="hljs-keyword">not null</span> <span class="hljs-keyword">check</span>(low_sensitivity_count <span class="hljs-operator">&gt;</span> <span class="hljs-number">0</span>)
);

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