The process has been documented in
https://docs.google.com/document/d/1Aj2oOZvwb6D6lm89XU_T9brtXApz5b7R00UtbzEiYkI/edit

@odimitrijevic The documentation is ready, what is the next step here? Where should this be published to make sure the community will have access?

I have found some interesting information that correlates with our idea that the problem starts occurring after we migrate to BigTop (2021-02-19):

• The problem happens due to a bug in the Hive Cost Based Optimizer (CBO) - see https://issues.apache.org/jira/browse/HIVE-21778
• The CBO component has a switch allowing to turn it on or off - see https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-hive.cbo.enable
• The switch was set to false in our CDH distros (link sent for latest used version 5.16, and I have checked for previous versions) - see https://docs.cloudera.com/documentation/enterprise/6/properties/6.3/topics/cm_props_cdh5160_hive.html#concept_6.3.x_hiveserver2_props__section_performance_props
• We have not manually changed the switch in our Hive configuration - see https://github.com/wikimedia/puppet/search?q=cbo.enable
• We have disabled the switch for one job just after the migration, as it was causing issues - see https://gerrit.wikimedia.org/r/c/analytics/refinery/+/668236/3/oozie/cassandra/daily/pageview_top_percountry.hql#29

This leads me to conclude that the problem got introduced with our BigTop migration.

Thank you @JAllemandou

The report has been published on: https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Data_Issues/2021-02-09_Unique_Devices_By_Family_Overcount