Toughest Challenges

In the quest for next big break or opportunity out there, I have come to expect these interview questions and I have decided to blog about it.

Phase 1 has ended. Phase 2 is up ahead.


Background –
After months of relentlessly applying for jobs with mix and match of skill sets that I am eligible for, going through multiple ghost callings (I believe that’s what it is called when recruiters spend around 20 to 30 minutes inquiring everything about you, explaining the job needs, setting the pay expectations, raising false hopes and then never hearing anything back even after sending out multiple mails or messages), I was fortunate to be set up a call finally with actual person in an actual company.

Unfortunately, the interview didn’t pan out well. In terms of actual interview itself, it was more about getting to know about the current role, about the team I would be working with and then straight down to the actual interview. The first question that I was asked was the ‘Toughest challenge’ till date. It caught me bit off-guard as I was expecting some technical questions to start with before settling down on behavioural and finally ending with the expectations of next meeting. I did my best to explain things I have done but I guess it just was not good enough.

Why really?

  • This will act as not only reference to myself but also reminder of the other ‘tough challenges’ that lie ahead that I need to face in the future.
  • Second and foremost reason being writing this down has been very cathartic.
  • Not EVERYTHING can be put on resume, so here it is then.

    Without further ado, let me get to the Toughest Challenge question. Questions like this needs to be addressed via STAR system.

Toughest Challenges Faced –

Accenture
My stint at Accenture has been the most challenging and most gratifying time of my professional career. I had worn several hats during my 5 years there – started off as a Software Engineer, BI Developer, BI Team Lead, Operations Team Lead (BI). There were numerous times when I had to stay back at the office for one whole day and more to ensure smooth operations. As I write this, it feels as if it was only yesterday I left Accenture as everything feels so real even though it’s been 5 years since I left.

Here are few of the most challenging times and how I handled them –

  • Data Migration –
    • Challenge – Project was being upgraded from version 1 to version 2. This necessitated initiating data migration from Operational Data Store (ODS) to Data Warehouse (DWH). Daily Nightly Job at that point was designed to handle about ~1M records data migration in about 4-6 hours between ODS and DWH on daily basis. Task was to migrate about 90M records.
    • Action Taken – I re-architected the ETL jobs for loading the main fact tables end to end to come up with a scalable, efficient and most performant solution. This involved introducing cached lookups, replacing row by row updates with bulk updates, removing blocking transformations, creating indexed views etc. I had also included audit table to track each batch progress.
    • Result – Data migration was successfully achieved in 12 Hrs timeframe as opposed to original design that took about 240+ hours. Additionally, some of the design changes introduced were kept unchanged for daily normal runs thereafter.
  • BI Data Feed
    • Challenge – BI Data Feed solution was to be implemented and that required someone to be familiar with C#.Net and BI technologies to develop a command line application to ingest data coming from 4 different SQL Server instances comprising 60+ tables. Business logic too dictated numerous complex rules that had to be incorporated.
    • Action Taken – Upskilled myself in learning to write efficient C#.Net multi-threaded application, using SMO.Net API calls, BCP commands and combining that with dynamic SQL and SSIS to build the needed solution.
    • Result – Solution was delivered much ahead of the time adhering to business specifications.

MindTree –

It was through this company that I first landed here in Sydney, Australia and I worked as Technology Lead for LINK Group over 2.5 years. Best thing I found about the role I can say is the complete autonomy that was offered to perform the task needed with whatever tools available at hand.

Here are few of the most challenging times and how I handled while working here-

  • Data import monitoring –
    • Challenge – Data imports were regularly done over varying periods of time using out-of-the-box TrimPort and HP Trim. There was no proper way to track the imports to estimate the job completion times resulting in unnecessary waiting for data completion or not able to report on the progress.
    • Action Taken – Developed a Power BI dashboard to report the batch import progress via monitoring the data at SQL Server. Created a SSIS package that continually refreshed the data at regular intervals.
    • Result – Created a wholistic pluggable data monitoring solution that is fully flexible for future monitoring and giving flexibility for the end-users to properly plan the imports.

Macquarie Group –

Work at Macquarie Group has been the most joyous one. It is a company that I would say puts technology at the forefront of whatever they do.

Here are few of the most challenging times and how I handled while working here-

  • Historical data migration failure handling –
    • Challenge – Production release got halted when an issue was identified with one of the data migrations script for historical data pertaining to 18 reporting months. Reproducing the issue in non-Prod environment was not possible as it did not have requisite data. Upstream flows had huge impact in absence of this change and release window for the change implementation was not long.
    • Action Taken – I broke down the analysis into multiple phases, first by testing out the column counts, then schema validation and identifying the root cause of the problem. Developed unified script handling all the discrepancies. Performed data sampling by running it across the whole dataset to ensure it is working.
    • Result – Was able to deliver the solution within two hours after the issue was identified after going through proper peer review process, attaching test evidences, and obtaining business sign-off.
  • Setting up of new Environment –
    • Challenge – Application team was living on dangerous grounds with only one environment to work with i.e. Production. All the workflows (over 40+), dependent objects, were being run from one drive and there was no safety net. Periodic backups were taken but it was not an efficient system. UAT process had to be set-up.
    • Action Taken – I broke down the needs into categories of varying complexities and needs. Co-ordinated with multiple teams in setting a new environment, creating a new repository for version control (BitBucket), performing unit testing and system testing on the UAT branch. Integrated Alteryx with Python in developing a localization workflow that could refresh the underlying data pertaining to an environment.
    • Result – Robust new system was in place for the team to work with without having to take multiple backups each time they had to work on change. End to End integrated module was set-up right from raising a Jira, to merging the code was properly documented in a workflow detailing each step.
  • Performance enhancement –
    • Challenge –  Regulatory report containing complex business logic needed to be replicated with data on the data hub. Data pertained to movements across two successive reporting periods and the challenge was to perform cross currency conversion across 15+ reporting currencies using Power BI. Incorporating the business logic within the Power BI was leading to slow-performing reports that was not effective to scaled data volumes. Task needed was to create a scalable solution that also enhances the response time.
    • Action Taken – Solution was tackled by moving all the logic on to the data hub. Utilizing the power of Impala queries on the hub, cross currency conversion, look up data inclusion, and using the Pivot along with cross join, whole business logic was replicated. Decision was taken further to move the output to a flat table instead of retrieving it directly from Power BI.
    • Result – Highly performant report was generated by using DAX queries and pulling in the transformed data from the flat table. Report performance was brought down from 300+ seconds to <20 sec.

Amaysim –

Amaysim was the first product company that I worked with. One of the things that blew my mind was orientation classes that were planned in the first week of joining in the company. I mean who even does that these days? There were series of workshops where different people chipped in and explained the company culture (even CEO).

As I listed here, it was a role that I got accepted because of their belief in my technical capabilities and not the tools themselves and this led me to work on technologies such as Alteryx, AWS Redshift, PostgreSQL, Tableau etc.

  • Report Generation –
    • Challenge – Finance team were tasked to generate Revenue Reports of NBN customers. This entailed following series of steps in document that involved manual extraction of data, data clean-up for discrepancies, data reconciliation and repeating this process over multiple use cases.
    • Action Taken – Developed Alteryx workflow that hooked data right at the source, embedded the business logic within the workflow and using the Tableau Data Extract (TDE) as output with no manual intervention and data getting refreshed at scheduled time.
    • Result – Tableau dashboard that business could directly use to perform the reporting, analytics saving hours of effort every month in getting it done.

Mantras to live by

I am inspired to write this blog after seeing a post in my company’s Workplace talking about wellbeing during COVID times and making effective communication. As a working professional there are some ‘mantras’ that I live by and here they go

General

  • Ownership of a Production issue – If you are tasked with an issue that requires urgent attention for production need, ensure that right from start to finish you own it. This would mean assigning proper timeline to start with, upon delivery ensure it is getting tested in UAT, goes Pre-Prod and then finally getting deployed to Prod. I want your eyes and ears all throughout this process either through regular follow-up’s and ensure post production.
  • Look out for opportunities – Quite often apart from your regular work, you may come across some tasks that are being done following a set process following certain sequence of steps in a methodical manner. Usually the people doing it do spend considerable amount of time out of their normal routine in accomplishing this (sometimes a week or more!). That should be the first sign of opportunity to seize and automate things.
  • Commitment to task- Never commit to anything upfront. If anyone is coming to you with a request to deliver something urgently, take some time to pause to first analyse it. Only after proper analysis give a timeline on when it can be done. When giving an estimate consider the time for actual build, Unit Testing, Regression Testing (if needed), Design document update, Peer Review (must), Rework time.
  • Meetings – Always have an agenda for a meeting and circulate it before hand as it provides context for participants. Be mindful of time and do not book it after-hours just because only person is off-limits. If you feel a phone call would be easier, then by all means give a ring and get it done fast.
  • Technical Front –
    • When solutioning a problem ask yourself the following questions – 
      • How critical is this problem?
      • Are there any more areas where such problem exists? If so what can be done about it?
      • At whole component level, is there something I can do to make the solution better?
      • Don’t be afraid to loosen things up and go one step further if you feel you can deliver a more robust and stable solution. Pitch for it if you are confident that you can deliver within the time frame.
    • Actual Fix – 
      • Provide proper code comments in the code and even in the Fix Details so that the anyone can understand what has gone into it.
      • Do thorough formatting of the code to make it more readable. 
      • Look for extensibility and scalability of the solution (how does it impact Asset) 
      • Ability for the solution to withstand large volumes (Query Plan analysis, stastics check etc.)

Mail Etiquettes –

  • Signatures – During my initial years, it was such a fanciful thing I used to put my heart into trying to come up with very unique, non-repetitive, colour formatted signatures, images etc mostly for bragging rights. It’s only later I see the futility of it. Anything non-standard is just plain distraction Here is how to tackle it
    • Check the organization’s internal pages for any signature etiquettes. 90% of the time they always do and it’s a straightforward process of just using it.
    • In cases where there isn’t one, the only things of need should be – Name, Role, Company Address, Official Mail Id (Very Important), Extension. That’s about it. Avoid putting in images.
  • Mail Acknowledgement – Always acknowledge a mail that is sent to you seeking advice/reply. If it takes time let them know about it that you would get back after certain time. If that time elapses and you aren’t able to do accomplish the task, put the mail again on why you weren’t able to. Essence is you should not keep people in dark, when they are waiting for response.
  • To/CC/BCC know-how – Always put the person whom you are referring to in ‘To’ and rest in CC. If there are group of 10 people in ‘To’ and if you are intending to reply only to one/two people, then except them everyone else should be in CC. Make this a practise. I have seen people create rules specific to being mentioned in ‘To’/’Cc’. Remember CC means it is an FYI and putting the unintended people in ‘To’ you are just wasting their time.
  • Mail Content – Remember that depending on the recipient your content should be altered. If you are writing mail to say senior leaders / non-technical people, then explain the issue with as much less technical details as possible, connect to them from customers impact point of view. As a general guideline you can structure your mail with blocks such as – Issue Observed (Job is failing/job taking long time etc.), Customer Impact (non-timely reports, process blockage etc.), Root Cause (if you have found out then put it else you can say it is still being investigated), Workaround/Solution Proposed. In this manner you are structuring your message properly and succinctly
  • In continuation of the above, when seeking for clarification, make it a point to let the team know that you have done some homework prior to reaching out. This would mean explaining them the thought process that went into coming to the current logic. Only then seek information. Remember half sent information causes to & fro exchange that would cost you a day or more.
  • Manager and Above – When you put mail to manager or above, wait for 10 to 15 minutes (don’t go anywhere from your desk) to see if they are seeking any clarifications. They are the busiest people and if they are looking for some more info then you are buying them good time in waiting for it. Also, never be afraid in terms of grammatical errors or overthinking in detailing. Everybody makes mistakes, own it and improve on it. Trust me they don’t mind as long as they understand what you are trying to convey.

Personal Front-

  • Folks, love everything you do. Only then you would be able to deliver results.
  • Never look for personal gains. Derive satisfaction in the work you deliver. Remember your bread and butter and your daily life is being provided to you by this job that you have got. Respect it. Rewards and recognition will automatically follow
  • When it comes to discussing things/issues keep in mind that every work you are doing is affecting someone and all your efforts are addressing a real problem.
  • Learn, learn and learn. Remember. Keep learning. Aim to cater at least 1 Hr per week in learning to enhance your career – it could be technical or non-technical ones.Once you learn, aim to deliver one session within the project team by preparing a deck and presenting it to them. This goes a long way in improving your presentation and oral skills.
  • Remember to invest in yourself – Of late, many of the training sites are demand hefty annual fee which may look daunting at first (say $400 – $600) but ration it per month and you will see, you will be able to afford it.
  • Don’t be a frog in the well. Have a periodic connect with managers or senior managers to know what their future plans for you is/ career opportunities are.