Home assignment: Quick Books Online (QBO) is Intuit’s offering to run accounting for small businesses. QBO customers are called merchants - typically small businesses or self-employed. 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 3 statuses: Pending, Paid, Rejected • Every payment has a payment date – a timestamp that the payment occurred • We have simplified the scope of this assignment: o Every customer has a unique customer Id and no additional properties. No need to track customers o Similarly for this exercise we don’t reference invoices at all Description of Database source objects: We have created two tables in 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 Merchants table all rows are processed every day. For 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. Requirements: 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.
