Distributed SQL Workshop

SQL exercises to learn how distributed databases work.

Setup Postgres

Add Postgres Data Source to your SQL Client

• Host = localhost.
• Database = problems.
• User = user.
• Password = postgres.

Setup schema tables

• Run all the SQL statements in the setup_postgres.sql file to create the schema and the tables for Postgres.

Setup Trino

Add Trino Data Source to your SQL Client

• Host = localhost.
• Port = 8080.
• User = user.
• Leave password empty.

Setup schema tables

• Run all the SQL statements in the setup_trino.sql file to create the schema and the tables for Trino.

Access MINIO UI

• To access the Minio UI, go to http://localhost:9001/.
• User: minio.
• Password: minio123.

Access TRINO UI

• To access the Trino UI, go to http://localhost:8080/ui/.
• Enter any username, no password required.

Notes on the technniques used

Cumulative Table Design

Strengeths

• Historical analysis without shuffling data, when all facts are preserved in an array or array of structs.
• Easy "transition" (active, inactive, churn, resurrected) analysis.

Drawbacks

• Can only be backfilled sequentially.
• Handling PII data can be a mess since deleted/inactive users get carried forward.