ABC is a startup that has been through various bends and steep curves climbing up to the spot where it is today. The company's leadership is still unsettled, for their vision is to be the #1 brand in their game. The leadership team is hard-working, energetic and deeply passionate about what they do. In their quest to conquer the coveted position of being the #1 player in serving its customers, it is on a lookout for like minded individuals to join its team, who instead of carrying the job attitude of taking orders to implement it, wears the attitude of a smart player who is proactive in problem solving and constantly strives to improve his skill-sets. Are you that one?
This take home exercise is to help us understand you and your skill-sets better while giving you a taste of the chaos you would likely step into, every single day, here at our startup. This place is not for the faint hearted. This place is for the warriors who dreams big and works harder to upskill himself and his team, for he is wise to know that success is no cakewalk.
While we have cautioned you enough, it is needless to say that we really care for your success and are so eager to your joining us in becoming tomorrow's most celebrated company in what we do.
You are a specialist Data Architect who is brought in to solve some of the craziest challenges that the live, big, fat SQLand monster of MS SQL Server DB is tormented with by virtue of the needs of transactional traffic, data warehousing and business analytics. Below is the overall architecture of the current system:
| Our Large MS SQL Server Monster Instance |
|---|
| SQL Server as OLTP + DW |
| SSAS as OLAP |
| SSRS for Reporting |
| SSIS fo ETL |
The big fat SQL Server instance has lots of DB Objects like Tables, Triggers, Stored Procedures, SQL Agent Jobs, etc. signifying that the architecture is heavily DB-centric and more so handling the needs of varied business functions.
The company grew so big that the current DB instance is struggling to meet the current demands and the leadership is worried about its capacity to handle the growing traffic. While re-architecture of the non-db layers provided some relief, the DB remains a bottleneck for a lot of heavy work of all business functions (Sales Order Processing, Purchase Order Processing, Accounting, and various BI Reporting for leadership) is handled by the DB via 100s of user-defined stored procedures and functions.
Some of the things that DB teams witness often are below:
- The TempDB bloat to the point of maxing out the disk space resulting in the re-boot of SQL Server
- Frequent slowness of the execution time of the scheduled jobs that run stored procedures.
- When the BI team runs queries on the DB, they experience painful delay or worse non-responsiveness in some occasions.
As a seasoned enterprise DBA or BI or Data Architect,
- What problems do you see in the current as-is Data Architecture?
- What is your proposed to-be Data Architecture?
- How would you go about drawing a road-map for the new architecture from the current one?
Hint: Make your assumptions explicit as first thing in your proposal of road-map. Divide the road-map into different areas/buckets of your specialization activities before delving into the check-list of things that you would be assessing/implementing.
ProTip: Take deep breadth. Read, and re-read the problem statement to understand the use-case that represents the monster of a problem you are signing up to fight by joining us. Do not hessitate to think over and beyond your experience and make your proposal, to present your shiny side to impress us.
Important: Choose your favorite area/bucket of expertise for deep exploration on the subject in the next rounds of discussion.
Feel free to make your submission in one of WordDoc or PDF formats as is convenient to you. We really are looking forward to having a fruitful conversation around your submission.
- To make productive use of our time in a way that we learn about each other better.
- It is our endeavor to see the best of yourself at every phase in our engagement and so we are trying to take transparency to its extreme by setting the expectations clear and you get the feeler of what it means to work for us - a win-win case!