The hiring process consists of 6 interviews:

• First Round: 1 hour of technical questions about Big Data concepts, different AWS resources that I am familiar with (their main usage, pros, and cons), SQL queries, and Spark infrastructure.
• Second Round: Home assignment.
• Rounds 3/4/5/6: Four technical rounds in a single day:
  • Craft Demo Presentation
  • Technical interview about the assignment
  • Cultural-functional interview
  • Hiring Manager interview

I had a very bad experience with this company. The recruiter called me on my day off (in Israel, it's Friday). The first call was "to help me to prepare" for the round day of the final 4 interviews with the presentation. He provided me with some not really useful info and tips. Nothing that couldn't wait until the beginning of the next week.

The second call was to let me know that I didn't pass the round day and to list all the reasons why. It was very disrespectful to call me again on my day off and ruin my whole weekend. Also, the reasons that he mentioned sounded like they misunderstood and made their own interpretation of everything that I said.

When I mentioned that he works a lot on our off days, he said, "Not that much, only when I have to." Well, I prefer that my days off stay that way. For me, it's disrespectful to other's family time.

Besides that, the whole process felt very stressful, like I was constantly and heavily gauged, even for being nervous during the interview. Sorry, but being interviewed for 4 hours in a row by different bunches of people is a stressful situation for me.

Home Assignment: Quickbooks Online (QBO) is Intuit’s offering for running accounting for small businesses. QBO customers are called merchants, typically small businesses or self-employed individuals. These merchants prepare invoices for their customers, and we track payments made against these invoices. Your assignment is to build a small data mart to track payments made to QBO merchants by their customers.

Description of Main Business Entities:

• Every merchant has a number of properties: Merchant ID (unique), Merchant Name, Address, Phone Number, Email.
• Every payment is made by a customer to a merchant.
• Every payment has a Payment ID (unique), Payment Amount, a Payment Method (for example, Visa, Mastercard, Check – there may be many more), and a Status. For the sake of this assignment, we only support three statuses: Pending, Paid, Rejected.
• Every payment has a Payment Date – a timestamp of when the payment occurred.

We have simplified the scope of this assignment:

• Every customer has a unique Customer ID and no additional properties. There is no need to track customers.
• Similarly, for this exercise, we do not reference invoices at all.

Description of Database Source Objects:

We have created two tables in the Redshift Operational Data Store (ODS) schema:

• Merchants
• Payments

Both Payments and Merchants are updated in the data warehouse once per day. Both tables contain the field ingestion_date, referring to the day that the table was ingested into the ODS. For the Merchants table, all rows are processed every day. For the Payments table, only new or changed rows are processed.

Assignment 1 - DB:

1. Create tables as they will appear in the data mart. You can optionally create additional schemas for these tables.
2. Create SQL scripts to load the data mart tables – one script for each target table.
3. Be prepared to explain your design decisions.

Assignment 2 - Application:

Purpose: An internal team needs access to the data you created in the Data Mart section. In their use case, they are trying to assess if a merchant receiving a payment now has a history of an abundance of rejected payments. This information is important for fraud assessments, as it may indicate that the merchant is defrauding QBO.

For this use case, we are asked to provide the following:

Data Engineering Craft Demonstration Exercise Page 2 of 2

1. The total number of payments for a given merchant with status “Rejected” over the past 7 days.
2. The total number of payments for a given merchant over the past 7 days.
3. The total payment amounts of rejected payments for a given merchant over the past 7 days.
4. The total payment amounts for a given merchant over the past 7 days.

Implementation:

Create a Python application which provides an API to fulfill the requirements above. The application should:

1. Leverage the data mart you used in the previous section.
2. Leverage REST API.

Be prepared to explain your design decisions.