deck_edw_ses_monitoring

Question 1

EDW (Enterprise Data Warehouse)

Answer 1

Central database that collects and stores data from multiple source systems across the company. At Likewize this is where the BI team aggregates CTR data, HITS data, Zendesk data, and other operational feeds into one place for reporting.

Question 2

DCNA2-P-BI-O1

Answer 2

Database server name for the Likewize EDW. This is the server you connect to in SSMS to browse all the reporting tables. Contains HITS 3, HITS 2, and external data views including AWS Connect data.

Question 3

SSMS (SQL Server Management Studio)

Answer 3

Free Microsoft application used to connect to SQL Server databases, browse tables, and run queries. Download it from Microsoft directly. You connect by entering the server name (DCNA2-P-BI-O1), then you can explore tables and write SQL against them.

Question 4

SQL (Structured Query Language)

Answer 4

Language used to interact with relational databases. Core syntax is SELECT (what columns you want), FROM (what table to look in), WHERE (what conditions to filter by). Straightforward to learn, non-technical people pick it up quickly.

Question 5

NVARCHAR

Answer 5

SQL Server data type that stores text as a string. The EDW uses NVARCHAR for most columns because it makes data ingestion easier since you don’t get type mismatch errors on import. Downside is you can’t sort or filter dates or numbers properly because the database treats them as text, not actual date or number values.

Question 6

Indexed Column

Answer 6

A database column that has been optimized for fast lookups and sorting. Think of it like a table of contents in a book. Without an index the database has to scan every single row to find what you’re looking for, which gets slow on large tables. In the EDW, initiation_timestamp is indexed so you can sort and filter by it quickly.

Question 7

AWS Connected Table

Answer 7

Table inside the Likewize EDW that is built from CTR data coming out of the Connect instance. The BI team already populates it with attributes like initial contact ID, timestamps, and call handling data. Also has columns for CSAT call survey questions. Does not have a row_start_date column which is unusual compared to other EDW tables.

Question 8

CTR (Contact Trace Record)

Answer 8

Record generated for every interaction in Amazon Connect containing full metadata about the contact. Includes agent name, queue, handle time, contact ID, timestamps, survey responses, and other attributes. CTR files flow to S3 and are also ingested into the EDW by the BI team.

Question 9

Row Start Date

Answer 9

A timestamp column that normally exists on every EDW table indicating when the row was inserted. The AWS Connected table is missing this column, which makes it harder to find recent records. You have to sort by another column like initiation_timestamp instead.

Question 10

BI Team

Answer 10

Business Intelligence team at Likewize that reports into Ranga. They extract data from multiple sources including CTR logs and load it into the EDW for reporting. Swapnil is the key person who understands the data. They already consume all CTR files, so they may be able to add new fields like CSAT survey results without going through a full product requirement cycle.

Question 11

Swapnil

Answer 11

BI team member who understands everything about the EDW data. Can potentially add new CTR attributes to the database without formal product or requirements cycles. If you need a new field parsed out of CTR logs and added to the AWS Connected table, he is the person to talk to.

Question 12

Interim Reporting via Excel Workbook

Answer 12

Temporary reporting approach where you embed a SQL query inside an Excel workbook. The end user (like Jo) opens the workbook, right-clicks, and hits refresh to pull in the latest data from the database. DK built the supply planner reports that are used globally using this method. Works as a bridge until a proper dashboard UI is built.

Question 13

EDW External Data Views

Answer 13

Section of the EDW database containing views built from external data sources. This is where the AWS Connected table lives alongside data from other systems like Zendesk. There are roughly 500+ tables and views in the EDW total.

Question 14

Jason’s US/Canada CSAT Solution

Answer 14

A separate CSAT survey implementation that Jason built for US and Canada. It does not use the DynamoDB-based approach that the UK solution uses, so survey data from Jason’s solution may appear differently in the CTR records. Important to know when looking at CSAT columns in the EDW because the data format depends on which solution captured it.

Question 15

Amazon SES (Simple Email Service)

Answer 15

AWS service for sending and receiving email. In the Connect context, you verify a domain on SES so it appears in the dropdown inside the Connect instance to enable email as a channel. SES is also used by Cognito to send OTP communications like one-time passcodes.

Question 16

SES Domain Verification

Answer 16

Process of proving you own a domain so SES allows you to send and receive email through it. Once verified, the domain shows up as a selectable option in the Connect instance settings. Required even if you are not using SES as your primary email gateway.

Question 17

Cognito

Answer 17

AWS identity management service that handles user registration, email verification, and one-time passcode delivery. At Likewize it is used on HITS portals and GTP portals for user authentication. Cognito sends all its OTP communications through SES, so if SES is misconfigured, OTP delivery can break.

Question 18

OTP (One-Time Passcode)

Answer 18

A temporary code sent to a user to verify their identity during login. Cognito generates the code, user enters it, Cognito checks if it matches. At Likewize, OTP is used on HITS and GTP portals. Cognito delivers OTPs through SES, so any SES misconfiguration directly affects whether users can log in.

Question 19

Cognito Identity Pool

Answer 19

A Cognito resource that provides temporary AWS credentials to users so they can access AWS services directly. During the hotfix, Sarab mistakenly blamed the IVA team for manually creating a Cognito identity pool. The actual issue turned out to be related to an SES misconfiguration in the same region.

Question 20

SES Misconfiguration (Hotfix Discovery)

Answer 20

During a hotfix, DK found that the likewize.com domain already existed in certain SES instances across multiple AWS regions. This misconfiguration was contributing to Cognito OTP delivery failures. Showed that the security team did not fully understand how SES and Cognito interact within AWS.

Question 21

GTP Portal

Answer 21

One of Likewize’s web portals that uses Cognito and OTP for user authentication. Along with HITS portals, it depends on SES being correctly configured in order for one-time passcodes to be delivered to users.

Question 22

SolarWinds

Answer 22

Database monitoring tool used by Likewize to watch production database performance. Tracks query execution, resource usage, and can alert when databases are degrading. DK logged in during an incident and found that monitoring on the production primary database had been turned offline with no alert generated.

Question 23

AppDynamics

Answer 23

Application performance monitoring tool used to watch HITS application servers. Shows error rates, response times, and transaction health. During the IVA timeout incident, AppDynamics showed a big spike in errors on the EMEA HITS servers which confirmed the problem was on the HITS side, not AWS.

Question 24

DBA (Database Administrator)

Answer 24

Person responsible for managing, tuning, and monitoring databases. At Likewize, DBAs are part of a shared service team. DK told them to monitor databases post-release and tune any problematic queries, but they don’t do it proactively. They wait to be told exactly what to do.

Question 25

Query Tuning

Answer 25

Process of optimizing a database query so it runs faster and uses fewer resources. When a query is inefficient it can slow down or crash the entire database. In the EMEA incident, there was a query running that needed DBA attention and tuning, but nobody was watching for it.

Question 26

Production Database Monitoring Offline

Answer 26

DK logged into SolarWinds and found the monitoring agent on the production primary EMEA database was offline. No alert was generated to notify anyone that monitoring had stopped. This meant database issues could happen without any automated warning.

Question 27

Shared Service Team Culture Issue

Answer 27

The COE shared services team operates on a model where they provide people, but those people need to be told explicitly what to do. They don't operate independently, monitor proactively, or take initiative. This is a cultural problem that causes production issues to go undetected until someone outside the team notices.

Question 28

US Database Crash (Dish Incident)

Answer 28

Earlier in the week, a BI job got hung up and took down the US production database server. This affected an API that Dish calls. The issue self-recovered after about 15 minutes. Dish monitors Likewize APIs externally, so they would have escalated if it hadn't recovered. SolarWinds monitoring was also offline during this event.

Question 29

Dish API Monitoring

Answer 29

Dish Networks monitors Likewize's APIs externally as part of their integration. If the API degrades or goes down, Dish sends an escalation email and expects Likewize to join an emergency bridge call. This creates external pressure to keep production databases stable.

Question 30

APAC Database Issues

Answer 30

When DK logged into SolarWinds to check all regions during the EMEA incident, APAC databases were showing active problems. US was okay. This highlights that database health varies by region and no single team is watching all of them consistently.

Question 31

Performance Testing Gap

Answer 31

Likewize does not do any performance testing before pushing releases to production. Production is effectively the performance testing environment. This means you don't know your system's limitations until something breaks in front of real customers. Makes it impossible to say whether an issue is normal load or a capacity problem.

Question 32

Distribution List Restructuring

Answer 32

The COE restructured all internal distribution lists without telling anyone. Some old distros still work, some don't. Makes it hard to contact the right team during incidents. DK had to hunt for the correct DBA distribution list to report the EMEA query issue.