Get startedGet started for free

Creating dimensions

The company requires employees to take training yearly to stay up to date. As part of creating a report for HR employees and their training, it is necessary also to register the last date when the employee took the training. Your task is to introduce a new dimension to the model to document this event. Once you do this, it will finalize the fact table, linking together all the dimensions:

CREATE TABLE fact_employee_trainings (
    fact_id NUMBER(10,0) PRIMARY KEY,
    employee_id NUMBER(38,0),
    training_id NUMBER(38,0),
    date_id NUMBER(10,0), 
    FOREIGN KEY (employee_id) REFERENCES dim_employees(EMPLOYEE_ID),
    FOREIGN KEY (training_id) REFERENCES dim_trainings(TRAINING_ID),
      -- Waiting for the date dimension
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id) 
);

This exercise is part of the course

Introduction to Data Modeling in Snowflake

View Course

Exercise instructions

  • Create, or replace if it already exists, a new dimensions dim_date.
  • Assign a unique identifier to the entity, called date_id.
  • Add attribute year and month, with data type NUMBER(4,0) and NUMBER(2,0), respectively, to keep the date records.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Create new entity
___ (
  	-- Add unique identifier
    date_id NUMBER(10,0) ___,
  	-- Add new attributes to register date
    ___ NUMBER(4,0),
    ___ NUMBER(2,0)
);
Edit and Run Code