ClickHouse replacing Merge Tree engine inner workings

clickhouse_replacing_engine.sql

DROP TABLE tmp_test_replacing;

-- Create a table with the ReplacingMergeTree Engine 
CREATE TABLE IF NOT EXISTS tmp_test_replacing (
  ts Date, 
  sort_var1 String,
  sort_var2 String,
  version_var UInt64,
  indep_var UInt64
)
ENGINE = ReplacingMergeTree(version_var)
PARTITION BY (ts)
ORDER BY (sort_var1, sort_var2);

-- Add some data. 
INSERT INTO tmp_test_replacing  FORMAT CSV
2019-01-01, Amy, Apples,	1, 1
2019-01-01, Bob, Apples,	1, 1
2019-01-01, Clara, Apples,	1, 1
2019-01-01, Denise, Apples,	1, 1
;

-- Check the table data
SELECT * FROM tmp_test_replacing;

-- Change the versioning var
INSERT INTO tmp_test_replacing  FORMAT CSV
2019-01-01, Amy, Apples,	2, 1
2019-01-01, Bob, Apples,	2, 1
2019-01-01, Clara, Apples,	1, 1
2019-01-01, Denise, Apples,	1, 1
;
OPTIMIZE TABLE tmp_test_replacing;
SELECT * FROM tmp_test_replacing;

-- Change the independent var
INSERT INTO tmp_test_replacing  FORMAT CSV
2019-01-01, Amy, Apples,	2, 2
2019-01-01, Bob, Apples,	2, 2
2019-01-01, Clara, Apples,	1, 1
2019-01-01, Denise, Apples,	1, 1
;
OPTIMIZE TABLE tmp_test_replacing;
SELECT * FROM tmp_test_replacing;

-- Change the partition var
INSERT INTO tmp_test_replacing  FORMAT CSV
2019-01-02, Amy, Apples,	2, 2
2019-01-02, Bob, Apples,	2, 2
2019-01-01, Clara, Apples,	1, 1
2019-01-01, Denise, Apples,	1, 1
;
OPTIMIZE TABLE tmp_test_replacing;
SELECT * FROM tmp_test_replacing;