You are tasked with pulling data about the status of onboarding customers from an API to load to Snowflake and model in dbt. Ultimately you will help create a dashboard from the dbt models to visualize this information for the onboarding team.
The onboarding team is interested in tracking the status of companies in the onboarding process. The questions they are most interested in being able to answer are:
- How many companies are currently onboarding?
- Which products take the longest to onboard?
- What is the average amount of time it takes to onboard?
- Our target onboarding time is 14 days. How many companies are exceeding this over time.
- They want to be able to slice the data by onboarding manager, vertical, etc.
-
Examine the data from the API at https://suzcamnu65hid4pw4qo66azao40dyusc.lambda-url.us-east-1.on.aws/. You will be provided an API Key that is passed as the header
mt-api-key. -
Write a Python script in
response/flow.pyto pull the data from the API, transform it as needed, and save to an S3 Bucket. This S3 bucket is set up as stage in Snowflake and you will be tasked with loading the data from this stage to Snowflake. We typically load data from stages in AVRO, JSON, and CSV formats. You will be provided the bucket name and AWS credentials. -
Write the SQL to create a destination table to load the data to in a file called
response/destination_table.sql -
Write the SQL to load the data from the stage to the destination table in a file called
response/merge.sql. For the merge query you can reference the stage data as@onboarding_data_stage -
Write a dbt Model(s) that will be used to build dashboards for the team to answer the questions above. Save this file(s) with the prefix
response/dbt_{model_name}.sql. Please also include dbt docs for the model(s). You are not required to create a fully functional dbt project, just the models you would propose for this data and to answer these questions.
To submit your assessment, share your forked repo with @jmhollinger (if it is private) and send your recruiter and hiring manager a link to it.