Data Vault / Kimball Modeling Exercise with Sakila Database

etl.sql

USE sakila;
    

-- Film
    

DROP TABLE IF EXISTS src_film;
CREATE TABLE src_film LIKE film;
INSERT src_film SELECT * FROM film;

DROP TABLE IF EXISTS hub_film;
CREATE TABLE hub_film (
	`key` INT NOT NULL auto_increment primary key,
    `id` INT NOT NULL,
    `created_at` datetime,
    `updated_at` datetime,
    `source` varchar(100)
);

INSERT INTO hub_film (id, created_at, updated_at, source)
	SELECT 
		film_id, 
        NOW(), 
        NOW(), 
        'sakila' 
	FROM src_film
ON DUPLICATE KEY UPDATE updated_at=NOW();


-- Customer


DROP TABLE IF EXISTS src_customer;
CREATE TABLE src_customer LIKE customer; -- using LIKE copies the data types and indexes over
INSERT src_customer SELECT * FROM customer;
    
DROP TABLE IF EXISTS hub_customer;
CREATE TABLE hub_customer (
	`key` INT NOT NULL auto_increment primary key,
    `id` INT NOT NULL,
    `email` varchar(100),
    `created_at` datetime,
    `updated_at` datetime,
    `source` varchar(100)
);

INSERT INTO hub_customer (id, email, created_at, updated_at, source)
	SELECT 
		customer_id, 
        email,
        NOW(),
        NOW(),
        'sakila'
	FROM src_customer
ON DUPLICATE KEY UPDATE updated_at=NOW();


DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_customer
	SELECT 
		h.`key`, 
        c.*,
        CONCAT(c.first_name,' ',c.last_name) as full_name
    FROM src_customer c
    JOIN hub_customer h ON c.customer_id = h.id;


-- Store


DROP TABLE IF EXISTS src_store;
CREATE TABLE src_store LIKE store;
INSERT src_store SELECT * FROM store;

DROP TABLE IF EXISTS hub_store;
CREATE TABLE hub_store LIKE hub_film; -- Easier to write

INSERT INTO hub_store (id, created_at, updated_at, source)
	SELECT
		s.store_id,
        NOW(),
        NOW(),
        'sakila'
	FROM src_store s
ON DUPLICATE KEY UPDATE updated_at=NOW();

DROP TABLE IF EXISTS dim_store;
CREATE TABLE dim_store
	SELECT
		h.`key`,
        s.*
	FROM src_store s
    JOIN hub_store h on s.store_id = h.id