ClickHouse Certified Developer Exam

Modeling Data

• Create a new database
• Create a new table that satisfies a given criteria or matches a given file format
• Choose efficient data types for columns when appropriate
• Define an efficient primary key given a specific criteria of the types of queries that will be executed on a MergeTree table

Inserting Data

• Insert a local file into a table
• Insert a file from cloud storage into a table
• Insert a Parqet, CSV, or TSV file into a table
• Provide minor transformations to columns as they are being inserted
• Insert data from one table into another

Analyzing Data

• Write a query that satisfies a given criteria
• Write a query that uses regular functions. For example, searches for substrings within a String column, or converts a timestamp to the beginning of a time interval
• Write a query that uses aggregate functions. For example, find the max/min/sum/avg of a column, or the number of unique values, or a given quantile
• Use a GROUP BY to compute buckets of aggregated values given a specified timeframe or grouping criteria

Optimizing Query Performance

• Define a materialized view that stores the result of a non-aggregation query
• Define a materialized view that stores the result of an aggregate function using the AggregatingMergeTree or SummingMergeTree table engines
• Define a projection on a table
• Define a set or minmax skipping index on a table

Deduplication and Mutations

• Perform a lightweight delete operation on a table
• Implement an efficient upsert strategy using the ReplacingMergeTree table engine
• Implement an efficient strategy for performing frequent updates using the CollapsingMergeTree table engine