Keys for Relational Data

Introduction to Data Modeling in Snowflake

Nuno Rocha

Director of Engineering

Introduction to relational data

Entity cluttered with lots of products

Introduction to relational data (1)

Relational data: Structured data organized into individual entities and keys that establish relationships between them

Products split into three individual entities

The power of keys in relational data

Data structure & integrity: Keys organize data into tables and maintain accuracy
Real-world connections: Keys facilitate meaningful links and reflect complex interactions
Efficient access: Keys streamline data retrieval and querying
Scalability: Keys ensure data adapts to growth with integrity

Recap of primary and foreign keys

Primary Key: Unique identifier of each record in an entity

CREATE OR REPLACE TABLE products (
    id NUMBER(10,0) PRIMARY KEY,
    name VARCHAR(255)
);

Recap of primary and foreign keys (1)

Primary Key: Unique identifier of each record in an entity
Foreign Key: Links one entity to another

CREATE OR REPLACE TABLE products (
    product_id NUMBER(10,0) PRIMARY KEY,
    product_name VARCHAR(255)


);

Recap of primary and foreign keys (2)

Primary Key: Unique identifier of each record in an entity
Foreign Key: Links one entity to another

CREATE OR REPLACE TABLE products (
    product_id NUMBER(10,0) PRIMARY KEY,
    product_name VARCHAR(255),
    manufacturer_id NUMBER(10,0),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(manufacturer_id)
);

Relationships in the data model

Data model including products, manufacturers and product details entities

Relationships in the data model (1)

New categories entity

Adjusting the data model

CREATE OR REPLACE TABLE categories (
  category_id NUMBER(10,0) PRIMARY KEY,
  category VARCHAR(255)
);

Cardinality of categories entity towards products entity

Adjusting the data model (1)

ALTER TABLE products
ADD COLUMN category_id NUMBER(10,0);

ALTER TABLE products
ADD FOREIGN KEY (category_id) REFERENCES categories(category_id);

Adjusting the data model (2)

Final physical model for products

Terminology and functions overview

Relational data: Structured data organized into individual entities and keys that establish relationships between them
ALTER TABLE: SQL command used to modify the structure of an existing entity
ADD: SQL command, used with ALTER TABLE, to add new elements to the entity

ALTER TABLE table_name
ADD COLUMN column_name column_datatype;

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES foreign_table(PK_from_foreign_table);

Let's practice!

Introduction to Data Modeling in Snowflake