I’m using columnar fact tables for our star schemas, with dimensions as in-memory tables.
Typically, if a fact table contains a time dimension, I use it as the key. But this is more a heuristic I developed and I’m not quite sure what is best practice here.
What’s the right way to decide what the key(s) for a columnar table should be, and in what order should they be declared?
Here’s an example table definition:
CREATE TABLE fact_survey_submissions ( -- Time `timestamp` DATETIME NOT NULL, -- Foreign Keys account_id VARCHAR(32) NOT NULL, user_id VARCHAR(32) NOT NULL, response_id VARCHAR(32) NOT NULL, survey_id VARCHAR(32) NOT NULL, -- Measures survey_rating TINYINT, KEY(`timestamp`) USING CLUSTERED COLUMNSTORE );