What data quality checks would you add to a pipeline?

What the interviewer wants to hear

• Uniqueness and not-null checks on keys
• Referential integrity between tables
• Freshness and volume checks
• Accepted values and range checks
• Alerting and blocking bad data

Model answer

I start with basic schema tests: primary keys should be unique and not null, and foreign keys should exist in the parent table, for example every order's customer_id exists in customers. Then I add freshness checks, such as the latest event being less than two hours old, and volume checks that alert if today's row count is far outside the normal range. I add accepted value tests for columns like status, and range checks such as amount greater than zero. For critical tables I compare totals with the source system. In dbt these are simple tests, and tools like Great Expectations or Soda can do more. Most important, failures should alert the owner and, for critical models, stop downstream dashboards from refreshing with bad data instead of failing silently.

More easy Data Engineer questions

• Describe a data pipeline you built or would build, from source to dashboard.
• What is the difference between an OLTP database and an OLAP data warehouse?
• Explain a star schema and why it is used in data warehouses.
• Why use an orchestration tool like Airflow instead of cron jobs?
• What is a slowly changing dimension, and how do SCD Types 1, 2 and 3 differ?
• Why would you store analytics data as Parquet rather than CSV or JSON?
• What is change data capture (CDC), and why do data teams use it?
• Explain Kafka topics, partitions, offsets and consumer groups to a new teammate.
• What does dbt do in a modern data stack, and what are its main features?