1. You receive 2 billion finance transactions daily from Billing, GL, Revenue, and OPEX systems. How would you design the pipeline?
What interviewers are looking for
Scalability, reliability, lineage, reconciliation, and cost control.
Strong answer
I would design a layered architecture:
Ingestion Layer
- APIs → Azure API Management + Event Hubs
- Batch files → ADLS Gen2 landing zone
- CDC sources → Kafka/Event Hubs
Raw Layer (Bronze)
- Store immutable source data
- Partition by source and ingestion date
- Preserve metadata such as source system, load timestamp, and file name
Processing Layer (Silver)
- Standardize schemas
- Deduplicate records
- Apply business validations
- Generate surrogate keys
Business Layer (Gold)
- Finance-ready datasets
- Fact and dimension modeling
- Aggregations for reporting
Reliability Practices
- Checkpointing
- Idempotent processing
- Schema evolution handling
- Data quality rules (Great Expectations/dbt tests)
Reconciliation
- Compare source record counts and financial totals
- Control tables with expected vs actual counts
- Automated exception reporting
Monitoring
- Freshness
- Volume anomalies
- SLA tracking
- Cost monitoring
The key principle is that finance pipelines must be reconcilable and auditable, not just fast.
2. A pipeline processes 100 million records, but after 70 million a downstream API fails. How do you recover without restarting everything?
Strong answer
I would not restart from zero.
Design the pipeline to support:
Checkpointing
Track:
- Last successfully processed batch
- Record ranges
- Offsets
- Watermarks
Idempotency
The target system should safely accept retries.
Examples:
- MERGE operations
- UPSERT patterns
- Business key deduplication
Additional Improvements
- Dead-letter queue for failed records
- Retry with exponential backoff
- Circuit breaker if API remains unavailable
This minimizes recovery time and avoids duplicate processing.
3. A Snowflake query normally takes 3 minutes but suddenly takes 40 minutes with the same warehouse size. What do you investigate first?
First checks
Query Profile
Look at:
- Scan volume
- Join strategies
- Spill to disk
- Partition pruning
Data Changes
Has the table:
- Grown significantly?
- Lost clustering effectiveness?
- Experienced skew?
Warehouse Contention
Check:
- Concurrent workload increase
- Queued queries
Statistics & Pruning
Look for:
- Missing micro-partition pruning
- Full table scans
Recent Code Changes
Did someone:
- Add joins?
- Introduce cross joins?
- Remove filters?
The first stop is always the Query Profile, because it reveals exactly where execution time increased.
4. A Snowflake Stream has not been consumed for several days while underlying data changed heavily. What is the risk?
Risk
The stream maintains change-tracking metadata.
Potential issues:
Stream Staleness
If retention windows are exceeded:
The stream can become stale.
Consequences
- Lost CDC records
- Missed inserts/updates/deletes
- Need for full reload
Actions
- Monitor stream lag
- Alert on unconsumed streams
- Regular consumption
- Sufficient retention periods
For critical finance systems, relying solely on streams without monitoring is dangerous.
5. A PySpark job processing 2 TB develops severe data skew. How do you identify and fix it?
Identify
Review Spark UI:
Look for:
- Long-running tasks
- Uneven partition sizes
- One executor doing most work
Check:
for highly concentrated values.
Fixes
Salting
Distributes skewed records.
Broadcast Small Tables
Avoid expensive shuffles.
Repartition
Use a better distribution key.
AQE
Enable Adaptive Query Execution.
Filter Earlier
Reduce data before joins.
My first step is always identifying which key is causing skew, not immediately increasing cluster size.
6. A Structured Streaming pipeline fails and restarts 10 minutes later. How do you prevent duplicate processing?
Key principle
Make processing exactly-once or effectively-once.
Use
- Checkpointing
- Transaction logs (Delta Lake)
- Watermarks
- Idempotent writes
Example
If writing to Delta:
using business keys.
Why?
On restart, Spark replays data from the last committed checkpoint.
Without idempotent logic:
7. A 500-line dbt model is reused by six downstream models. How would you redesign it?
Problem
Single massive model creates:
- Maintenance issues
- Slow builds
- Tight coupling
- Testing complexity
Better Design
Break into:
layers.
Example
Benefits
- Reusable logic
- Easier testing
- Better lineage
- Faster troubleshooting
I would prefer modular, composable models over monolithic SQL.
8. An Airflow DAG has 50 tasks, but one failure causes unnecessary downstream reprocessing. How do you improve fault isolation?
Improvements
Smaller Independent Units
Separate unrelated task chains.
Dataset-Driven Dependencies
Instead of entire DAG reruns.
Checkpoint Intermediate Outputs
Persist successful stages.
Retry Only Failed Tasks
Use Airflow's task-level recovery.
Idempotent Processing
Downstream tasks should consume only new changes.
The goal is to reprocess only affected data, not the entire workflow.
9. The pipeline reports SUCCESS, but the target contains only 80% of expected data. How can that happen?
This is a classic senior-level question.
Possible Causes
Silent Data Quality Failures
Rows rejected during validation.
Partial Loads
A batch completed without errors but skipped partitions.
Bad Filters
Incorrect date filters.
Truncation
Unexpected data type issues.
Upstream Incomplete Data
Source delivered only part of the dataset.
Lesson
Technical success ≠ Business success.
Success should include:
- Row count validation
- Financial totals validation
- Reconciliation checks
10. Design a secure Azure architecture for finance data arriving through APIs, batch files, and Event Hubs
Ingestion
- API Management
- Event Hubs
- ADLS Gen2
Security
Identity
- Azure AD
- Managed Identities
Secrets
- Azure Key Vault
Network
- Private Endpoints
- VNet Integration
- NSGs
Data Protection
- Encryption at rest
- Encryption in transit
Access Control
- RBAC
- Row-Level Security
- Column-Level Security
Governance
- Microsoft Purview
- Data lineage
- Data classification
Processing
- Databricks
- Snowflake (optional)
Monitoring
- Azure Monitor
- Log Analytics
- Sentinel
Finance data should follow least-privilege access.
11. A SQL query joins a 5-billion-row transaction table with several dimensions and takes hours. What do you investigate before increasing compute?
First Review
Execution Plan
Understand where time is spent.
Join Order
Poor optimization can explode runtime.
Partition Pruning
Are filters reducing scanned data?
Clustering/Indexing
Can joins be accelerated?
Data Skew
One hot key can dominate processing.
Broadcast Opportunities
Small dimensions should be broadcast.
Predicate Pushdown
Filter before joining.
Adding compute before understanding the bottleneck is usually expensive and ineffective.
12. Spark works on 10 GB but fails with OOM at 2 TB. What would you investigate?
Areas to Check
Partition Count
Too few partitions causes oversized tasks.
Shuffle Operations
Large joins/group-bys create huge memory pressure.
Data Skew
One partition may hold massive data.
Cache Usage
Unnecessary caching consumes memory.
Wide Transformations
Large exchanges between executors.
Executor Sizing
Memory and cores may be poorly configured.
Question
Is this a memory problem or a data distribution problem?
Most OOM issues are actually partitioning and shuffle problems.
13. A finance pipeline has 99.9% technical success, but users say the numbers are wrong. Is it really successful?
Senior-Level Answer
No.
The goal is not pipeline execution.
The goal is correct financial outcomes.
If reports are wrong:
Metrics should include:
- Reconciliation accuracy
- Data quality
- Financial balancing
- User trust
An incorrect financial report delivered on time is still a failed pipeline.
14. A developer gets access to an entire finance table because column-level security is inconvenient. What is wrong with that?
Multiple Issues
Least Privilege Violation
Access exceeds job requirements.
Compliance Risk
Possible SOX, GDPR, PCI, or internal policy violations.
Data Exposure
May reveal:
- Salary data
- PII
- Financial forecasts
Audit Concerns
Difficult to justify during audits.
Correct Approach
- Row-level security
- Column masking
- Role-based access
- Approved exception process
Convenience should never override governance.
15. Two financial systems do not reconcile, and someone asks you to "adjust the pipeline so the numbers match." What do you do?
This is the answer many interviewers love.
My response:
I would never manipulate the pipeline simply to force agreement between systems.
First, I would investigate:
Reconciliation Process
Compare:
- Record counts
- Amount totals
- Missing transactions
- Timing differences
- Currency conversions
- Business rules
Root Cause Analysis
Questions:
- Is one source incomplete?
- Are accounting rules different?
- Is there a late-arriving data issue?
- Is there a mapping problem?
Document Findings
Produce evidence showing:
Escalate to Finance Owners
Finance should decide how numbers should be interpreted.
Engineering should not silently alter financial facts.
Senior-Level Principle
Data engineering is responsible for moving and validating data accurately, not changing reality to satisfy an expected outcome.
Final Interview Takeaway
For Senior Data Engineer interviews, the strongest answers consistently emphasize:
- Idempotency
- Checkpointing
- Fault isolation
- Observability
- Data quality
- Reconciliation
- Governance
- Security
- Scalability
- Business correctness over technical success
A senior engineer thinks less about "How do I run Spark?" and more about "How do I ensure this finance platform remains correct, recoverable, auditable, and trusted at scale?".
