Exploring Physical Data Models

Introduction to Data Modeling in Snowflake

Nuno Rocha

Director of Engineering

The role of the physical model

Constructors building a house

The physical model's details

E-commerce logical data model

The physical model's details (1)

E-commerce physical data model, adding data types

Establishing primary keys

Primary key (PK): Ensures every record in a table has a unique identifier

E-commerce physical data model, customers table primary key

Establishing primary keys (1)

Primary key (PK): Ensures every record in a table has a unique identifier

E-commerce physical data model, all tables primary keys

Creating Primary Keys

PRIMARY KEY: SQL clause to define a column as the unique identifier

CREATE OR REPLACE TABLE products (
    stockcode VARCHAR(255) PRIMARY KEY,
    description VARCHAR(255)
);

Establishing foreign keys

Foreign Key (FK): Connects records in different tables to keep the data related

E-commerce physical data model, foreign keys

Creating foreign keys

FOREIGN KEY () REFERENCES (): SQL clause to define a column that references the primary key of another table

CREATE OR REPLACE TABLE orders (
    invoiceno INT,
    customerid INT,
    invoicedate DATE,
    unitprice DECIMAL(10, 2),
    quantity INT,
    stockcode VARCHAR(255),
    FOREIGN KEY (stockcode) REFERENCES products(stockcode)
);

Finalizing the physical data model

E-commerce final physical model

Terminology and functions overview

Physical data model: Defines how data is stored and accessed, including table structures, data types, and primary and foreign keys
PRIMARY KEY: SQL clause to define a column as the unique identifier
FOREIGN KEY (...) REFERENCES (...): SQL clause to create a link between two tables

CREATE OR REPLACE TABLE table_name (
      unique_column column_datatype PRIMARY KEY,
      other_columns column_datatype,
      foreign_column column_datatype,
      FOREIGN KEY (foreign_column) REFERENCES foreign_table(PK_from_foreign_table)
);

Let's practice!

Introduction to Data Modeling in Snowflake