Computer systems and methods for determining environment impact indicators for food products

A non-transitory computer-readable medium, wherein the non-transitory computer-readable medium is provisioned with program instructions that, when executed by at least one processor, cause a computing platform to:

• detect a trigger event for determining environmental-impact indicators for product-level ingredients across multiple different food products;
• in response to detecting the trigger event, invoke a data pipeline that is configured to determine environmental-impact indicators for product-level ingredients at scale (i) across multiple different food products during each individual run of the data pipeline rather than for only one individual food product during each individual run of the data pipeline, and (ii) based on source data that is spread out across at least six different database tables, wherein the data pipeline reduces time and computing resources required to determine the environmental-impact indicators across the multiple different food products based on the source data that is spread out across the at least six different database tables, and wherein, when invoked, the data pipeline functions to:
• extract a first source dataset from a first database table containing data about product-level ingredients, wherein the first source dataset comprises (i) a set of rows representing data records for a given set of multiple different product-level ingredients that are each defined by a respective combination of food product and ingredient type, and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• extract a second source dataset from a second database table containing data about food products, wherein the second source dataset comprises (i) a set of rows representing data records for a given set of multiple different food products and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective food product represented by the respective row;
• extract a third source dataset from a third database table containing data about plants where food products are manufactured, wherein the third source dataset comprises (i) a set of rows representing data records for a given set of multiple different plants and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective plant represented by the respective row;
• extract a fourth source dataset from a fourth database table containing data about source locations for ingredients, wherein the fourth source dataset comprises (i) a set of rows representing data records for a given set of multiple different source locations for ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective source location represented by the respective row;
• extract a fifth source dataset from a fifth database table containing data about transportation modes for ingredients, wherein the fifth source dataset comprises (i) a set of rows representing data records for a given set of multiple different transportation modes for ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective transportation mode represented by the respective row;
• merge the first source dataset, the second source dataset, the third source dataset, the fourth source dataset, and the fifth source dataset into a first merged dataset by (i) producing a first intermediate dataset by performing a first left join operation using the first source dataset as a first left table, the second source dataset as a first right table, and a food-product identifier as a first key for joining the first and second source datasets, (ii) producing a second intermediate dataset by performing a second left join operation using the first intermediate dataset as a second left table, the third source dataset as a second right table, and a plant identifier as a second key for joining the first intermediate dataset and the third source dataset, (iii) producing a third intermediate dataset by performing a third left join operation using the second intermediate dataset as a third left table, the fourth source dataset as a third right table, and a location identifier as a third key for joining the second intermediate dataset and the fourth source dataset, and (iv) producing the first merged dataset by performing a fourth left join operation using the third intermediate dataset as a fourth left table, the fifth source dataset as a fourth right table, and a transportation-mode identifier as a fourth key for joining the third intermediate dataset and the fifth source dataset, wherein the first merged dataset comprises (i) a set of rows representing data records for the product-level ingredients in the given set and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• update the first merged dataset by inserting an additional column representing a data variable that, for each respective row in the set of rows, provides a measure of a respective distance between a respective source location and a respective plant location for a respective product-level ingredient represented by the respective row;
• extract a sixth source dataset from a sixth database table containing environmental-impact values for ingredients, wherein the sixth source dataset comprises (i) a set of rows representing data records for a given set of multiple different ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective environmental-impact values for a respective ingredient represented by the respective row;
• merge the updated first merged dataset and the sixth source dataset into a second merged dataset by performing a left join operation using the updated first merged dataset as a left table, the sixth source dataset as a right table, and a transportation-mode identifier as a key for joining the updated first merged dataset and the sixth source dataset, wherein the second merged dataset comprises (i) a set of rows representing data records for the product-level ingredients in the given set and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• and
• determine a respective group of environmental-impact indicators for each respective product-level ingredient in the given set using the second merged dataset;
• store the respective group of environmental-impact indicators for each respective product-level ingredient in the given set into a target database table;
• and
• utilize the target database table to service network-based requests from users for visualizations of environmental-impact indicators for food products from the given set of multiple different food products, wherein the visualizations are based on respective groups of environmental-impact indicators for at least some of the product-level ingredients in the given set that are a synthesis of source data spread out across the first, second, third, fourth, fifth, and sixth database tables.

A method implemented by a computing platform, the method comprising:

• detecting a trigger event for determining environmental-impact indicators for product-level ingredients across multiple different food products;
• in response to detecting the trigger event, invoking a data pipeline that is configured to determine environmental-impact indicators for product-level ingredients at scale (i) across multiple different food products during each individual run of the data pipeline rather than for only one individual food product during each individual run of the data pipeline, and (ii) based on source data that is spread out across at least six different database tables, wherein the data pipeline reduces time and computing resources required to determine the environmental-impact indicators across the multiple different food products based on the source data that is spread out across the at least six different database tables, and wherein, when invoked, the data pipeline carries out functions including:
• extracting a first source dataset from a first database table containing data about product-level ingredients, wherein the first source dataset comprises (i) a set of rows representing data records for a given set of multiple different product-level ingredients that are each defined by a respective combination of food product and ingredient type, and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• extracting a second source dataset from a second database table containing data about food products, wherein the second source dataset comprises (i) a set of rows representing data records for a given set of multiple different food products and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective food product represented by the respective row;
• extracting a third source dataset from a third database table containing data about plants where food products are manufactured, wherein the third source dataset comprises (i) a set of rows representing data records for a given set of multiple different plants and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective plant represented by the respective row;
• extracting a fourth source dataset from a fourth database table containing data about source locations for ingredients, wherein the fourth source dataset comprises (i) a set of rows representing data records for a given set of multiple different source locations for ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective source location represented by the respective row;
• extracting a fifth source dataset from a fifth database table containing data about transportation modes for ingredients, wherein the fifth source dataset comprises (i) a set of rows representing data records for a given set of multiple different transportation modes for ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective transportation mode represented by the respective row;
• merging the first source dataset, the second source dataset, the third source dataset, the fourth source dataset, and the fifth source dataset into a first merged dataset by (i) producing a first intermediate dataset by performing a first left join operation using the first source dataset as a first left table, the second source dataset as a first right table, and a food-product identifier as a first key for joining the first and second source datasets, (ii) producing a second intermediate dataset by performing a second left join operation using the first intermediate dataset as a second left table, the third source dataset as a second right table, and a plant identifier as a second key for joining the first intermediate dataset and the third source dataset, (iii) producing a third intermediate dataset by performing a third left join operation using the second intermediate dataset as a third left table, the fourth source dataset as a third right table, and a location identifier as a third key for joining the second intermediate dataset and the fourth source dataset, and (iv) producing the first merged dataset by performing a fourth left join operation using the third intermediate dataset as a fourth left table, the fifth source dataset as a fourth right table, and a transportation-mode identifier as a fourth key for joining the third intermediate dataset and the fifth source dataset, wherein the first merged dataset comprises (i) a set of rows representing data records for the product-level ingredients in the given set and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• updating the first merged dataset by inserting an additional column representing a data variable that, for each respective row in the set of rows, provides a measure of a respective distance between a respective source location and a respective plant location for a respective product-level ingredient represented by the respective row;
• extracting a sixth source dataset from a sixth database table containing environmental-impact values for ingredients, wherein the sixth source dataset comprises (i) a set of rows representing data records for a given set of multiple different ingredients and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective environmental-impact values for a respective ingredient represented by the respective row;
• merging the updated first merged dataset and the sixth source dataset into a second merged dataset by performing a left join operation using the updated first merged dataset as a left table, the sixth source dataset as a right table, and a transportation-mode identifier as a key for joining the updated first merged dataset and the sixth source dataset, wherein the second merged dataset comprises (i) a set of rows representing data records for the product-level ingredients in the given set and (ii) a set of columns representing data variables that, for each respective row in the set of rows, provide respective information about a respective product-level ingredient represented by the respective row;
• and
• determining a respective group of environmental-impact indicators for each respective product-level ingredient in the given set using the second merged dataset;
• storing the respective group of environmental-impact indicators for each respective product-level ingredient in the given set into a target database table;
• and
• utilizing the target database table to service network-based requests from users for visualizations of environmental-impact indicators for food products from the given set of multiple different food products, wherein the visualizations are based on respective groups of environmental-impact indicators for at least some of the product-level ingredients in the given set that are a synthesis of source data spread out across the first, second, third, fourth, fifth, and sixth database tables.