Instacart: Flatten event payloads into analytics-ready tables via ETL pipelines. Python, Pandas, JSON.
Instacart is a modern, app-first company, with an iOS App and an Android App. To grow our company we must make a special effort in collecting all information available around the App and make it available for everyone in the company. Some information about App usage is generated by our App and our backend API. Other sources of information, like the Google Play Store, can provide very useful insights on App performance and user rating. Instacart is very data-oriented and, most decisions are made based on appropriate metrics, therefore, data quality is a must and a concern of everyone involved.
Some event data come as json files and need some transformation to be structured as tables. Convert the case.json file to 3 csv files, using the programming language of your choice, with the following rules:
CuratedOfferOptions.csv:CurationProvider: in quotes
OfferId: in quotes
DealerId: in quotes
UniqueOptionId: in quotes
OptionId: in quotes
IsMobileDealer: without quotes
IsOpen: without quotes
Eta: in quotes
ChamaScore: without quotes
ProductBrand: in quotes
IsWinner: without quotes
MinimumPrice: without quotes
MaximumPrice: without quotes
DynamicPrice: without quotes
FinalPrice: without quotes
DefeatPrimaryReason: in quotes
DefeatReasons: in quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)
DynamicPriceOption.csv:Provider: in quotes
OfferId: in quotes
UniqueOptionId: in quotes
BestPrice: without quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)
DynamicPriceRange.csv:Provider: in quotes
OfferId: in quotes
MinGlobal: without quotes
MinRecommended: without quotes
MaxRecommended: without quotes
DifferenceMinRecommendMinTheory: without quotes
EnqueuedTimeSP: DD/MM/YYYY (converted to Brasilian timezone - UTC-3)
You are given one file case.json. Below is an example of a single record from this file:
{
"EnqueuedTimeUtc": "2021-09-05 08:04:08 UTC",
"EventName": "DynamicPrice_Result",
"Payload": "{\"provider\":\"ApplyDynamicPriceRange\",\"offerId\":\"a6611d55-9624-4381-8cdd-323ee3689241\",\"algorithmOutput\":{\"min_global\":85.0,\"min_recommended\":87.2,\"max_recommended\":97.65,\"differenceMinRecommendMinTheory\":2.2}}"
}
Make sure that the solution reflects your entire thought process - it is more important how the code is structured rather than the final CSV files.
See the full interview round by round, with questions for each stage.