Data Engineer

[name] asked: How to implement data retention for only 2 years in ClickHouse?

Our website users search for airline tickets. We need to log search events in ClickHouse so that analysts can determine popular destinations and conversion rates. Design a table and write the DDL. Stream, 5 minutes daily, date, destination, once daily, 2 years, JSON. Event ID, time, session, user (can be anonymous — without login), departure and arrival airports/cities, searched departure date, return date (can be one-way flight), number of passengers, cabin class, device type, whether flights were found, minimum price among found options, currency, promo code (may not be present). CREATE TABLE target_table ( sob_id UUID, date_sob DateTime(3), ses_id UUID, user_id Uint64, out_city LowCardinality(String), in_city LowCardinality(String), date_search_out Date, date_search_in Date, count_pass UInt16, class LowCardinality(String), devise LowCardinality(String), poisk, min_price, valute, procode )

[name] asked: What needs to be added to the table engine to keep data consistent across all nodes in the cluster?

[name] asked: Which columns should be Nullable and how to specify this in DDL?

[name] asked: The field 'found flights' has only two values (yes/no). How is it better to represent it in ClickHouse? What data type is used under the hood for Bool?