Reliable data engineering is not just about moving data from one system to another. It is about ensuring that data is complete, valid, timely, understandable, and safe to use. Interviewers often use these questions to evaluate how you detect failures, contain bad data, coordinate across teams, and protect analytical consumers.
1. How would you detect a sudden drop in source data volume?
What the interviewer is testing: Your ability to detect anomalies proactively instead of relying only on pipeline failures.
Answer: I would monitor source volume at multiple levels—total records, records by business date, records by key dimensions, and file or partition counts. I would compare current volume with historical patterns and business expectations rather than using only a fixed threshold.
A practical approach
- Establish a baseline: Calculate expected volume by source, weekday, hour, or business cycle. Monday traffic may naturally differ from Sunday.
- Measure at ingestion: Capture row counts, file sizes, source timestamps, maximum event time, and partition counts.
- Detect anomalies: Alert on percentage drops, statistical deviations, missing partitions, or zero-record loads.
- Localize the issue: Compare source counts with landing-zone, staging, and curated-layer counts to identify where records disappeared.
- Respond safely: Quarantine or mark the affected partition as incomplete, notify the owner, and prevent downstream publication when the threshold is critical.
Example SQL:
WITH daily_counts AS (
SELECT
source_name,
business_date,
COUNT(*) AS row_count
FROM raw_orders
GROUP BY source_name, business_date
),
with_baseline AS (
SELECT
source_name,
business_date,
row_count,
LAG(row_count) OVER (
PARTITION BY source_name
ORDER BY business_date
) AS previous_count
FROM daily_counts
)
SELECT
source_name,
business_date,
row_count,
previous_count,
ROUND(
100.0 * (previous_count - row_count)
/ NULLIF(previous_count, 0), 2
) AS drop_pct
FROM with_baseline
WHERE previous_count > 0
AND row_count < previous_count * 0.70;This is a simple example using the previous day's count. Production monitoring should compare against seasonality-aware baselines and handle expected source delays.
2. How do you prevent bad data from reaching downstream dashboards?
Answer: I would implement quality checks as explicit pipeline gates between ingestion, transformation, and publication. The key is to identify which failures require blocking data and which can be handled through warnings or quarantine.
| Layer | Checks | Action on failure |
|---|---|---|
| Ingestion | File presence, schema, row count, freshness | Retry, quarantine, or mark incomplete |
| Staging | Nulls, duplicates, type validity, key integrity | Reject invalid records or fail the batch |
| Curated models | Business rules, referential integrity, reconciliation | Block publication for critical failures |
| Dashboard serving | Freshness status, completeness, release version | Show stale/incomplete status or retain last-good version |
Design principles
- Validate early: Catch malformed records and schema violations as close to ingestion as possible.
- Separate critical and noncritical checks: A missing primary key may block publication; a small number of optional descriptive fields may only trigger a warning.
- Quarantine bad records: Store rejected rows with the reason, source, batch ID, and ingestion timestamp for investigation and replay.
- Publish atomically: Build and validate a new partition or table version, then switch consumers to it only after validation passes.
- Make quality visible: Expose freshness, completeness, and last successful load status to dashboard users.
if schema_valid and freshness_ok and critical_tests_pass:
publish_validated_dataset()
else:
quarantine_invalid_records()
mark_dataset_not_ready()
notify_data_owner()Strong interview point: Do not silently replace a trusted dataset with a partial load. Depending on the business use case, retain the last known-good version and clearly communicate that it is stale, rather than presenting bad data as current.
3. What is the difference between data quality and data observability?
Short answer: Data quality checks whether data meets defined expectations. Data observability helps you understand the health of data and pipelines over time, detect unexpected behavior, and investigate the cause.
| Dimension | Data quality | Data observability |
|---|---|---|
| Primary question | Does the data meet expectations? | What changed, where, and why might it have changed? |
| Typical signals | Validity, uniqueness, completeness, accuracy | Freshness, volume, schema changes, distribution, lineage, job health |
| Example | Order ID must not be null | Order volume dropped 45% after an upstream release |
| Best use | Enforce known rules | Detect unknown issues and speed up diagnosis |
They complement each other. A pipeline can pass all predefined quality tests and still be unhealthy—for example, it may contain valid records but be six hours late. Observability can detect the freshness problem even when row-level validation passes.
4. How would you enforce a data contract between two teams?
Answer: I would make the contract explicit, version-controlled, machine-readable where possible, and tested automatically in CI/CD and at runtime. A contract should describe not only field names and data types, but also semantics and operational expectations.
What a useful contract includes
- Schema: Field names, data types, nullability, and allowed values.
- Semantics: Definitions of fields, units, time zones, primary keys, and business meaning.
- Quality rules: Uniqueness, referential integrity, valid ranges, and completeness requirements.
- Delivery expectations: Refresh frequency, freshness SLA, expected volume, and availability window.
- Change policy: Versioning, compatibility rules, deprecation periods, owners, and communication requirements.
- Security classification: Sensitive fields, access restrictions, retention, and permitted use.
Enforcement workflow
- Producer and consumer teams agree on the schema and business meaning.
- Store the contract in version control alongside the producer's model or pipeline.
- Run compatibility and data-quality tests during pull requests and deployment.
- Validate incoming data at runtime and route violations to quarantine or a controlled failure path.
- Publish contract changes with an owner, version, migration plan, and deprecation window.
- Monitor violations and notify both teams with actionable details.
# Illustrative contract, not a vendor-specific format
dataset: orders
version: 2
fields:
order_id:
type: string
nullable: false
order_total:
type: decimal
nullable: false
expectations:
unique_key: order_id
freshness_minutes: 60
owner: commerce-data-teamHandling a breaking change: Prefer an additive change where possible. For a breaking change, publish a new contract version, allow a transition period, migrate consumers, monitor usage of the old version, and remove it only after agreed consumers have moved.
5. How would you trace the impact of a breaking schema change?
Answer: I would combine schema-change detection with data lineage, dependency metadata, repository search, and automated tests. The goal is to identify direct and indirect consumers before deployment, then verify the change after release.
Step-by-step impact analysis
- Detect the change: Compare the new schema with the registered or previously deployed schema. Identify removed or renamed fields, incompatible type changes, and altered semantics.
- Classify compatibility: Adding an optional field is often backward-compatible; removing a field or changing its type can break consumers. Actual compatibility depends on the format, registry rules, and consumer behavior.
- Trace lineage: Find downstream tables, dbt models, views, dashboards, feature pipelines, exports, and APIs that depend on the changed field.
- Search code and metadata: Check SQL, transformation code, orchestration definitions, and declared dependencies. Lineage alone may miss dynamic SQL or undocumented consumers.
- Test affected assets: Run unit tests, schema tests, integration tests, and targeted data comparisons in a non-production environment.
- Plan rollout: Use an expand-and-contract migration when possible: add the new field or version, migrate consumers, monitor, and only then remove the old field.
- Verify after deployment: Monitor failed jobs, data-quality results, freshness, and consumer errors.
customer_id from an integer to a string. Impact analysis should identify transformations that cast it to an integer, joins using that key, dashboards grouped by customer, and ML features that rely on its previous representation.
Useful engineering practices: Maintain searchable lineage metadata, version schemas, require compatibility checks in pull requests, assign clear dataset ownership, and make dependency graphs available to both producers and consumers.
6. How would you protect sensitive data in analytical pipelines?
Answer: I would apply security controls across the entire data lifecycle—from ingestion and storage through transformation, analytics, sharing, and deletion. The design should follow least privilege and use the minimum sensitive data necessary for each use case.
| Control | How it helps | Example |
|---|---|---|
| Classification | Identifies sensitive fields and required controls | Classify email, phone, financial, and regulated identifiers |
| Least privilege | Limits who and what can access data | Separate service accounts and role-based access |
| Encryption | Protects data at rest and in transit | Managed encryption keys and TLS |
| Minimization | Reduces exposure by limiting copied data | Drop unused identifiers before analytical marts |
| Masking / tokenization | Reduces direct exposure of identifiers | Mask emails in non-production environments |
| Auditing | Supports investigation and compliance | Log access, permission changes, and data exports |
| Retention & deletion | Avoids keeping sensitive data longer than necessary | Apply retention rules and propagate deletion requests |
Additional controls for analytics platforms
- Use row-level and column-level security where consumers require different views of the same dataset.
- Keep secrets in a managed secret store; never hard-code credentials in SQL, notebooks, or pipeline configuration.
- Prevent sensitive values from appearing in logs, error messages, test fixtures, or monitoring payloads.
- Use synthetic or appropriately masked data in development and testing.
- Scan for sensitive fields and track their lineage into derived tables, extracts, and feature stores.
- Review cross-project sharing, external exports, and service-account permissions regularly.
Quick Revision Table
| Question | Key points to mention |
|---|---|
| Detect volume drops | Historical baseline, anomaly thresholds, partitions, source-to-target reconciliation |
| Block bad data | Quality gates, quarantine, atomic publication, last-known-good data |
| Quality vs observability | Defined expectations vs health signals and diagnosis |
| Enforce contracts | Schema, semantics, tests, ownership, versioning, runtime validation |
| Trace schema impact | Compatibility checks, lineage, code search, tests, staged migration |
| Protect sensitive data | Least privilege, encryption, minimization, masking, audit, retention |
Scenario-Based Follow-Up Questions
Practice explaining your reasoning out loud. Expand each question to reveal the main points a strong answer should cover.
Scenario 1: A source delivers 70% fewer records, but the pipeline succeeds. What do you do?
Scenario 2: A producer wants to remove a column used by multiple dashboards.
Scenario 3: A quality check fails for 0.2% of records. Should the pipeline fail?
Scenario 4: Analysts need a sensitive field for a legitimate use case.
Chapter Summary
- Monitor volume, freshness, and completeness against meaningful baselines.
- Use quality gates to stop critical bad data before publication.
- Combine data quality rules with observability signals for faster diagnosis.
- Make data contracts executable through automated tests and runtime validation.
- Use lineage, compatibility checks, and staged migrations for schema changes.
- Protect sensitive data throughout its lifecycle with least privilege and minimization.
Practice Challenge
Design a data-quality framework for a daily sales pipeline. Define three volume or freshness checks, two record-level quality rules, the conditions that block dashboard publication, and how you would alert owners and trace the impact of a schema change.
Comments
Post a Comment