Your company receives a daily sales file from an external system containing order details such as Order ID, Customer Email, Order Amount, and Order Date.
The business wants:

How would you design this solution using Automation Studio?

I will start by creating a new automation in Automation Studio to handle the entire daily process.

First, I will use a File Transfer activity to pick up the daily sales file from the Enhanced FTP location. I will configure the file naming pattern so it automatically picks the correct daily file and moves it to a safe location before processing.

Next, I will add a Data Import activity to import the sales data into a raw data extension. I will set the import type as Append, since this is daily transactional data, and map fields like Order ID, Customer Email, Order Amount, and Order Date.

After the data is imported, I will use a SQL Query activity to summarize the sales data. In this step, I will calculate the total number of orders and total revenue for the current day and store the results in a separate summary data extension. This helps keep reporting clean and optimized.

Once the summary data is ready, I will add an Email Send activity that uses the summary data extension as the sendable data source. The email will display key metrics such as total orders and total revenue and will be sent to the stakeholders automatically.

Finally, I will schedule the automation to run daily at a fixed time so the entire process—from file pickup to email delivery—runs without any manual intervention.

Describe a situation where an automation failed to run. How did you fix it?
Give an example. (Asked in a Pwc Interview 🌀)

Once, my daily sales automation did not run. The summary email was not sent to the team.

Example problem:
The sales file was missing on the FTP folder.

First, I checked the automation run history. It showed an error in the File Transfer step.

Next, I checked the FTP folder and saw that the file was not there for that day.

Then, I checked the file name setting. The file name was different from what the automation expected.

After that, I fixed the file name pattern in the File Transfer activity.

I then tested the activity again to make sure it worked.

Finally, I ran the automation again, and the file was imported, the data was updated, and the email was sent successfully.