Lob Logical Read and lob read-ahead reads in NCCI
How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?


There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect.


  How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?


Logical reads count the number of times a page in memory was touched. The reduction is from 469,697 (cold cache) to 229,248 (warm). I don't have a complete explanation for this, but it may be in part because columnstore data is cached contiguously in a separate columnstore object pool, rather than the general page-sized buffer pool.

Read-ahead reads pages from the upper levels of a b-tree to identify pages to read in advance of the scan, which may also result in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Columnstore indexes have a b-tree structure on persistent storage.

Another factor is parallelism (it seems your test was run at DOP 24) since overlapping requests from different threads can result in additional logical reads. You may find the excess logical reads reduce somewhat if you run with OPTION (MAXDOP 1).

Internal storage details for columnstore are not well-documented, nor fully supported by the usual DMVs. At this stage, I would say the best explanation for the reduction in reads between cold and warm cache cases is the difference between on-disk and in-memory (cached) storage.

Columnstore data (dictionaries and segments) are stored at LOBs on disk, but are not held in memory in the same format, for performance reasons. Because they are different things, you can't just add logical reads to physical reads to get a meaningful result.