DuckDB

DuckDB is an in-process SQL OLAP database management system you can run on your local machine.

For the Slate data pipeline work, DuckDB can ba a transformation layer, pull flat files or exports from Slate, run analytical SQL against them locally or in a pipeline step, then push results to a warehouse. It’s a natural fit for prototyping analytics logic before uploading data to Redshift.

Install DuckDB

curl https://install.duckdb.org | sh

Learn more on the DuckDB website.

DuckDB File

Jump into using DuckDB. DuckDB uses a single file to store data. You can name the .duckdb file whatever you’d like.

duckdb slate.duckdb

Extract Slate Data

You can use DuckDB’s ATTACH to connect to Slate and extract data. Make sure to use the correct username, password, and port number.

ATTACH 'mssql://slate_dbname_username:{ SLATE_DB_PASSWORD_UNDG }@sql.technolutions.net:1444/dbname?use_encrypt=true' AS crm (TYPE mssql);

ATTACH 'mssql://slate_dbname_username:{ SLATE_DB_PASSWORD_GRAD }@sql.technolutions.net:1444/dbname?use_encrypt=true' AS crm (TYPE mssql);

Now run queries to collect table data from Slate.

Activity → activities

CREATE OR REPLACE TABLE activities AS SELECT id, date AS created_at, code, summary, source AS source_id, record AS person_id FROM crm.dbo.activity;

Address → addresses

A person may have more than one address, so consider sorting by type or rank. Addresses can also be joined to the applications table.

CREATE OR REPLACE TABLE addresses AS SELECT record AS record_id, city, county, region, country, postal AS postal_code, geomarket, type, rank_overall AS rank FROM crm.dbo.address;

Column	Type
record_id	uuid
city	varchar
county	varchar
region	varchar
country	varchar
postal_code	varchar
geomarket	uuid
type	varchar
rank	integer

Application → applications

CREATE OR REPLACE TABLE applications AS SELECT id,person AS person_id, status, created AS created_at, submitted AS submitted_at FROM crm.dbo.application;

Column	Type
id	uuid
person_id	uuid
status	varchar
created_at	timestamp
submitted_at	timestamp

Decision → decisions

CREATE OR REPLACE TABLE decisions AS SELECT application AS application_id, released AS released_at, code FROM crm.dbo.decision;

Column	Type
application_id	uuid
released_at	timestamp
code	varchar

Field → fields

Caution

You can define which fields (person, application, or other) in Slate you want to retain. The record_id column can be a foreign key for either an application_id or a person_id.

Helpful links from the Slate documentation: see [https://knowledge.technolutions.net/docs/standard-fields-prompts](standard Slate person-scoped fields) and [https://knowledge.technolutions.net/docs/application-fields](creating application-scoped fields and prompts).

CREATE OR REPLACE TABLE fields AS SELECT record AS record_id, field, prompt, value FROM crm.dbo.field WHERE record IS NOT NULL;

Column	Type
record_id	uuid
field	varchar
prompt	uuid
value	varchar

If you’d prefer to query some fields, you can pluck them by field value. This can also help with data migration, as the field table can be large.

CREATE OR REPLACE TABLE fields AS SELECT record AS record_id, field, prompt, value FROM crm.dbo.field WHERE record IS NOT NULL AND field IN ('ga_id','intended_term','intended_program','utm_campaign','utm_source','utm_medium');

Form Response → form_responses

Caution

The record_id column can be a foreign key for either an application_id or a person_id.

CREATE OR REPLACE TABLE form_responses AS SELECT id, record AS record_id, referrer AS url, utm_campaign, utm_source, utm_medium, submitted AS submitted_at FROM crm.dbo."form.response";

Column	Type
id	uuid
record_id	uuid
url	varchar
utm_campaign	varchar
utm_source	varchar
utm_medium	varchar
submitted_at	timestamp

Lookup Activity → lookup_activities

CREATE OR REPLACE TABLE lookup_activities AS SELECT id AS code, summary AS name, parent AS type FROM crm.dbo."lookup.activity";

Column	Type
code	varchar
name	varchar
type	varchar

Lookup Decision → lookup_decisions

CREATE OR REPLACE TABLE lookup_decisions AS SELECT id AS code, name FROM crm.dbo."lookup.decision";

Column	Type
code	varchar
name	varchar

Lookup Prompt → lookup_prompts

CREATE OR REPLACE TABLE lookup_prompts AS SELECT id, value AS prompt_value, category FROM crm.dbo."lookup.prompt";

Column	Type
id	uuid
prompt_value	varchar
category	varchar

Mailing → mailings

CREATE OR REPLACE TABLE mailings AS SELECT id, subject, summary AS name, active, archived, CONCAT(folder1,'/',folder2) AS folder,  method, utm_source, utm_campaign, utm_content, utm_term, utm_medium FROM crm.dbo."message.mailing";

Column	Type
id	uuid
subject	varchar
name	varchar
active	boolean
archived	boolean
folder	varchar
method	varchar
utm_source	varchar
utm_campaign	varchar
utm_content	varchar
utm_term	varchar
utm_medium	varchar

Message → messages

CREATE OR REPLACE TABLE messages AS SELECT id, mailing AS mailing_id, delivered AS delivered_at, status, person as person_id FROM crm.dbo.message;

Column	Type
id	uuid
mailing_id	uuid
delivered_at	timestamp
status	varchar
person_id	uuid

Person → persons

Appending a slate_instance literal to help us differentiate between grad and undg student records.

CREATE OR REPLACE TABLE persons AS SELECT id, status, created AS created_at, updated AS updated_at, 'undg' AS slate_instance FROM crm.dbo.person;

Column	Type
id	uuid
status	varchar
created_at	timestamp
updated_at	timestamp

Ping → pings

Caution

Only ping rows matched to a person_id.

CREATE OR REPLACE TABLE pings AS SELECT identity AS id, record AS person_id, url, utm_campaign, utm_source, utm_medium, timestamp AS created_at FROM crm.dbo.ping WHERE record IS NOT NULL;

Column	Type
id	bigint
person_id	uuid
url	varchar
utm_campaign	varchar
utm_source	varchar
utm_medium	varchar
created_at	timestamp

Source → sources

CREATE OR REPLACE TABLE sources AS SELECT id, format AS format_id, summary, created AS created_at FROM crm.dbo.source;

Column	Type
id	uuid
format_id	uuid
summary	varchar
created_at	timestamp

Source Format → source_formats

CREATE OR REPLACE TABLE source_formats AS SELECT id, name FROM crm.dbo."source.format";

Column	Type
id	uuid
name	varchar

Source Record → source_records

CREATE OR REPLACE TABLE source_records AS SELECT id, source AS source_id, record AS record_id FROM crm.dbo."source.record";

Column	Type
id	uuid
source_id	uuid
record_id	uuid

Status → statuses

Caution

The record_id column can be a foreign key for either an application_id or a person_id.

CREATE OR REPLACE TABLE statuses AS SELECT id, record AS record_id, status, timestamp AS changed_at, rank, source FROM crm.dbo.status WHERE record IS NOT NULL;

Column	Type
id	uuid
record_id	uuid
status	varchar
changed_at	timestamp
rank	integer
source	uuid

Tag → tags

CREATE OR REPLACE TABLE tags AS SELECT id, tag, record AS person_id FROM crm.dbo.tag;

Column	Type
id	uuid
tag	varchar
person_id	uuid

Complete SQL

BEGIN;

CREATE OR REPLACE TABLE activities AS
SELECT id, date AS created_at, code, summary, source AS source_id, record AS person_id
FROM crm.dbo.activity;

CREATE OR REPLACE TABLE addresses AS
SELECT record AS record_id, city, county, region, country, postal AS postal_code, geomarket, type, rank_overall AS rank
FROM crm.dbo.address;

CREATE OR REPLACE TABLE applications AS
SELECT id, person AS person_id, source AS source_id, status, created AS created_at, submitted AS submitted_at
FROM crm.dbo.application;

CREATE OR REPLACE TABLE decisions AS
SELECT application AS application_id, released AS released_at, code
FROM crm.dbo.decision;

CREATE OR REPLACE TABLE fields AS
SELECT record AS record_id, field, prompt, value
FROM crm.dbo.field
WHERE record IS NOT NULL
  AND field IN ('ga_id','intended_term','intended_program','utm_campaign','utm_source','utm_medium');

CREATE OR REPLACE TABLE form_responses AS
SELECT id, record AS record_id, person AS person_id, referrer AS url, utm_campaign, utm_source, utm_medium, submitted AS submitted_at
FROM crm.dbo."form.response";

CREATE OR REPLACE TABLE lookup_activities AS
SELECT id AS code, summary AS name, parent AS type
FROM crm.dbo."lookup.activity";

CREATE OR REPLACE TABLE lookup_decisions AS
SELECT id AS code, name
FROM crm.dbo."lookup.decision";

CREATE OR REPLACE TABLE lookup_prompts AS
SELECT id, value AS prompt_value, category
FROM crm.dbo."lookup.prompt";

CREATE OR REPLACE TABLE mailings AS
SELECT id, subject, summary AS name, active, archived, CONCAT(folder1,'/',folder2) AS folder, method, utm_source, utm_campaign, utm_content, utm_term, utm_medium
FROM crm.dbo."message.mailing";

CREATE OR REPLACE TABLE messages AS
SELECT id, mailing AS mailing_id, delivered AS delivered_at, status, person AS person_id
FROM crm.dbo.message;

CREATE OR REPLACE TABLE persons AS
SELECT id, status, ref AS ref_id, created AS created_at, updated AS updated_at, 'undg' AS slate_instance
FROM crm.dbo.person;

CREATE OR REPLACE TABLE pings AS
SELECT identity AS id, record AS person_id, url, utm_campaign, utm_source, utm_medium, timestamp AS created_at
FROM crm.dbo.ping
WHERE record IS NOT NULL;

CREATE OR REPLACE TABLE sources AS
SELECT id, format AS format_id, summary, created AS created_at
FROM crm.dbo.source;

CREATE OR REPLACE TABLE source_formats AS
SELECT id, name
FROM crm.dbo."source.format";

CREATE OR REPLACE TABLE source_records AS
SELECT id, source AS source_id, record AS record_id
FROM crm.dbo."source.record";

CREATE OR REPLACE TABLE statuses AS
SELECT id, record AS record_id, status, timestamp AS changed_at, rank, source
FROM crm.dbo.status
WHERE record IS NOT NULL;

CREATE OR REPLACE TABLE tags AS
SELECT id, tag, record AS person_id
FROM crm.dbo.tag;

COMMIT;