S3-Based Automated Reporting Implementation

### Problem Statement

**Objective:**
Automate the processing of a source file to split it, transform data, and load it into Snowflake, enabling the generation of specific sales reports.

**Components Involved:**
1. **Amazon S3**:
- **Folder-1**: Source file location.
- **Folder-2**: Destination for split files.
2. **AWS Lambda**: Function to process and split the file.
3. **Snowflake**:
- **Landing Table [RAW]**: Initial storage for uploaded data.
- **Processed Table**: Final processed data for reporting.

**Process Flow:**
1. **Source File Handling**:
- Upload a source file to **S3 Folder-1**.
- Trigger an **AWS Lambda** function upon file upload.

2. **File Splitting and Transformation**:
- **Lambda** splits the source file into 5 smaller files ensuring that no ‘ordernum’ is shared between two files.
- Calculate the total for each order.
- Convert the amounts from dollars to Rupees.

3. **Data Upload**:
- Save the split files into **S3 Folder-2**.
- **Snowpipe** to load the split files into the **Snowflake Landing Table [RAW]**.

4. **Data Processing and Storage**:
- Process the raw data in the **Landing Table** to generate the **Processed Table** in Snowflake.

5. **Reporting**:
- Generate SQL queries to produce the following reports:
1. Total sales per day.
2. Top 5 product lines per month based on total sales.
3. Sales trend for a given product line for each quarter of a specified year.

**Scheduling**:
- Schedule the **Lambda** function to run at a specific time daily.

**Detailed Requirements**:
1. **Lambda Function**:
- Must split the source file into 5 smaller files based on 'ordernum' without any duplication across files.
- Calculate the total amount for each order.
- Convert amounts from dollars to Rupees using a predefined conversion rate.

2. **Snowpipe**:
- Automate the loading of split files from **S3 Folder-2** into the **Landing Table [RAW]** in Snowflake.

3. **Snowflake SQL Queries**:
- **Total Sales Per Day**: Calculate total sales for each day.
- **Top 5 Product Lines Per Month**: Identify the top 5 product lines each month based on total sales.
- **Sales Trend for a Given Product Line**: Analyze the sales trend quarterly for a given product line in a specified year.

**Data Flow Diagram**:

```
S3 Folder-1 (Source File) -> Lambda (Split, Transform) -> S3 Folder-2 (Split Files)
| |
V V
Snowpipe Snowflake Landing Table [RAW]
| |
V V
Snowflake Processed Table <----------------- Data Processing
|
V
SQL Queries for Reports
```

**Deliverables**:
1. **Lambda Function Code**: For file splitting, order total calculation, and currency conversion.
2. **Snowflake Schema**: Definition for both raw and processed tables.
3. **SQL Queries**: For generating the specified reports.
4. **Scheduling Configuration**: For the Lambda function.

**Success Criteria**:
- Successful and accurate splitting of the source file into 5 files without shared 'ordernum'.
- Correct calculation and conversion of order totals.
- Automated and error-free data upload to Snowflake.
- Accurate and timely generation of reports as specified.