Update the fact table based on t1_pricing

[^pricing.csv] Introduction: Implement different pricing strategies (cost-plus, value-based, market-based). Considering factors such as competition, market demand, and reimbursement scenarios. Configuring tiered pricing, discounts for bulk purchases, and promotional offers. Track and document pricing changes for compliance audits. Based on the above factors we get pricing file to change the amount in the fact table.

 

Requirements: Develop Databricks Pyspark code to read the pricing file in CSV format from the path "/FileStore/tables/pricing.csv" and load it into a DataFrame using an explicitly defined schema. The file includes a header as the first record. Based on the price column in the pricing file, compute the updated sales_amt in the fact table f_daily_sales. Perform a left join between the pricing data and the f_daily_sales table using product_id and country as join keys. Update the sales_amt as follows

 

sales_amt = sales_amt * price.

 

For remaining records where the price is not available (null) in the pricing data, retain the original sales_amt value without modification.

 

Prerequisites:

 

Drop the table ‘f_daily_sales_clone’ if exist.

 

Create replica of ‘f_daily_sales’ table in 'f_daily_sales_clone' table and perform the requirement in the replica table.

 

Final Output: Display the result.

 

Unity Catalog Information: pricing, f_daily_sales