225 )Data Profiling and Handling Data Anomalies (Interview Style

 225 

225 )Data Profiling and Handling Data Anomalies (Interview Style

1. How do you typically identify and handle data anomalies when working in  SSMS?

Interview Answer: "When working in SSMS, I don't guess anomalies—I run a systematic profiling script right on the table. I check for nulls in mandatory columns using IS NULL, spot duplicate business keys using GROUP BY ... HAVING COUNT(*) > 1, and check for invalid formats or out-of-range values using LIKE patterns or numeric bounds (MIN and MAX).

Once I find an anomaly, I figure out where it broke down: Is it dirty data coming from the source file? Did an ETL step corrupt it? Or is the target table schema too restrictive? If it's a bad source record, I route it to a quarantine/error table so the main pipeline doesn't fail, and I flag it for the business owner to review."

2. Which specific tools or scripts do you use for profiling inside SSMS?

Interview Answer: "Inside SSMS, I rely entirely on custom T-SQL diagnostic queries rather than third-party tools. I write scripts that check column cardinalities (COUNT(DISTINCT col)), percentage of nulls, and data length violations. For example, if a column is supposed to be a numeric string, I use ISNUMERIC() or TRY_CAST() to find rows that will fail conversion before a deployment crashes my stored procedure."

3. Do you ever scan raw files directly or copy data to Excel for profiling?

Interview Answer: "No, I never copy production data to Excel. Excel has hard row limits (about 1 million rows), it can silently truncate leading zeros on postal codes or IDs, and it’s a security and compliance risk for sensitive data.

Instead, if the data is sitting in a flat file before loading, I will write a quick Python/PySpark snippet or use a T-SQL BULK INSERT into a staging table. Once it's in a staging table in SQL Server, I profile it natively using SQL queries. It keeps the data secure, handles millions of rows effortlessly, and avoids manual human error."

4. How do you handle unexpected data type changes or schema drift in your source files?

Interview Answer: "Schema drift is a classic issue—like a source CSV suddenly adding a new column or changing a date field to text. In SSMS/SQL Server, I handle this by using staging tables with looser data types (like VARCHAR(MAX) for everything in the raw layer). Then, my transformation stored procedures use TRY_CAST or TRY_CONVERT. If a value can't be cast into the correct data type (like a text string landing in an integer column), TRY_CAST safely returns a NULL instead of throwing a hard error, allowing me to catch and log that bad record in a quarantine table."

5. How do you validate referential integrity and cardinality issues during data profiling?

Interview Answer: "For cardinality and referential integrity, I write specific validation queries in SSMS. To check referential integrity, I use a LEFT JOIN with an IS NULL check—for example, finding child records in a transaction table that no longer have a matching parent ID in the customer dimension table. To check unexpected cardinality, I compare grain: if a table is supposed to have a unique grain on [InvoiceID, LineItemID], I run a query grouping by those columns to see if COUNT(*) ever exceeds 1, which instantly exposes duplicate loading issues."