Amazon Data Engineer I interview questions

Design an ETL process to collect tickets in a real-time manner. (Her description was too long to memorize, but that's the gist)

How many steps/stages of a pipeline would you consider to include?

There are two tables (tab1 and tab2). Both have one column and below is the data. Tab1 (col1) 1 1 NULL Tab2 (col1) 1 1 NULL NULL How many records would every join (LOJ, ROJ and FOJ) result?

Table MERCHANTS (DIM): PRIMARY KEY(merchant_id, marketplace) merchant_id INTEGER marketplace VARCHAR(255) launch_date TIMESTAMP product_desc VARCHAR(255) country_of_origin VARCHAR(2) SAMPLE DATA merchant_id marketplace launch_date product_desc country_of_origin 12 DE 1/1/2016 BOOKS DE 12 FR 6/1/2016 BOOKS DE 34 FR 6/1/2016 ELECTRONICS 34 UK 3/1/2016 WIRELESS FR 56 DE 4/1/2015 BOOKS UK 56 IT 1/1/2015 SOFTLINES 56 UK 2/1/2015 SOFTLINES UK 78 ES 12/1/2019 ELECTRONICS ES Table ORDERS (FACT): PRIMARY KEY(merchant_id, marketplace, order_id), PARTITION KEY(order_date) merchant_id INTEGER marketplace VARCHAR(255) order_id VARCHAR(20) sales DECIMAL(10,2) units INTEGER order_date DATE SAMPLE DATA merchant_id marketplace order_id sales units order_date 12 DE BTHDOF 100.50 6 1/1/2017 0:45 12 DE BAFTHF 120.32 8 1/1/2017 7:12 43 FR GTHDAE 18.5 1 1/1/2017 14:37 12 DE RTZHGD 90.3 3 1/2/2017 10:35 56 IT ASDQER 180.2 9 1/2/2017 22:01 56 UK YCCCOD 4000 400 1/3/2017 17:01 Please write a query to return the first (earliest) ORDER_ID carried out from each merchant on each day. Expected output columns: merchant_id, order_day, order_id

Given a string, write a function “recurring char” to find the strings first recurring character. Return “None” if there is no recurring character. Note: Treat upper and lower case letters as distinct characters. You may assume the input string includes no spaces. Example: input = "interviewquery" output = "i" input = "interv" output = "None"

Tell me a time when you needed to finish your project within a tight deadline. What did you do? What sacrifice did you make? Be prepared for follow-ups.