Interview - Data Engineer

1

Easy

0 / 2 points

Predstav si, že máš tabuľku `customers` v transakčnom systéme. Zákazník zmení email adresu. Ako by si to riešil v klasickej relačnej DB vs v Data Vault? Aký je hlavný rozdiel?

Klasická DB: UPDATE na existujúcom riadku - pôvodná hodnota sa stratí (alebo SCD Type 2 duplikuje celý riadok)

Data Vault prístup: Hub zostáva nezmenený (immutable), pridá sa nový riadok do Satellite s novým emailom

Výhoda: V Data Vault pôvodný email zostáva zachovaný, máš plnú históriu zmien, lepšia auditovateľnosť

Praktické použitie: Vieš zodpovedať "Aký email mal zákazník 1.1.2024?" - v klasickej DB už nevieš, v Data Vault áno

2

Easy

0 / 2 points

Máš dbt projekt s 50+ modelmi a build trvá 2 hodiny. Business chce aktualizovať len jeden report každú hodinu. Ako by si to optimalizoval?

Selective run: Použiť `dbt run --select report_name+` (model + všetky jeho dependencies), nie plný build

Incremental models: Zmeniť modely na incremental (materialized='incremental'), spracovať len nové/zmenené dáta

Tags/groups: Použiť tagy (dbt run --select tag:hourly) alebo groups pre organizáciu modelov podľa refresh frequency

Scheduling: Rozdeliť na viac jobov v orchestrátore (Airflow) - kritické modely častejšie, menej dôležité zriedkavejšie

3

Medium

0 / 3 points

Potrebuješ integrovať dáta z API, ktoré má rate limit 100 requestov/minútu a máš 50 000 produktov na update. Ako by si to navrhol v dbt/Python pipeline?

Batch processing: Rozdeliť na batch-e po 100 produktoch, sleep medzi batch-ami (60 sekúnd), celkový čas ~8.5 hodín

Incremental approach: Načítať len changed produkty (delta load) - identifikovať pomocou last_modified_date alebo hash comparison

Staging layer: Prvý krok: Python script stiahne API dáta do staging table/file, druhý krok: dbt spracuje staging → raw vault

Error handling: Retry mechanism s exponential backoff, logovanie failed requests, queue pre retry (dead letter queue)

Optimalizácia: Využiť bulk endpoints ak API podporuje (namiesto 1 request = 1 produkt), alebo paralelizovaťs viacerými API keys

4

Medium

0 / 3 points

V Data Vault máš 3 satellites na produkt z rôznych zdrojov (ERP, E-shop, CRM). Business chce jeden unified view. Ako vytvoríš "Golden Record" a ako rozhodneš, ktorému zdroju dôverovať pri konfliktoch?

Business Vault view: Vytvoriť view/table joinujúci všetky 3 satellites, každý atribút má definovaného "source of truth" (napr. cena z E-shop, kategória z ERP)

Priority rules: Definovať hierarchiu zdrojov pre každý atribút pomocou COALESCE (napr. COALESCE(eshop.price, erp.price, crm.price))

Metadata tracking: Pridať stĺpce source_system, last_updated_date pre každý atribút, aby user vedel odkiaľ hodnota pochádza

Conflict detection: Flagovať produkty s konfliktmi (napr. rozdielne názvy v ERP vs E-shop), vytvoriť monitoring report pre business

Business rules: Implementovať validácie (napr. e-shop cena nesmie byť > 120% ERP base price), flagovať anomálie

5

Medium

0 / 3 points

dbt test zlyháva na production, ale na dev prostredí prechádza. Ako by si debugoval tento problém a aké sú možné príčiny?

Rozdielne dáta: Production má viac/iné dáta ako dev (edge cases, ktoré dev nemá), skontrolovať row counts a sample dát

Timing issues: Na production bežia testy skôr než sa dokončí load (race condition), riešiť pomocou dependencies/waits v orchestrátore

Environment config: Rozdielne schema, permissions, alebo dbt profiles.yml nastavenia medzi dev a prod

Debug stratégia: Použiť `dbt test --store-failures` na prod, pozrieť failed rows, porovnať s dev dátami, reprodukovať lokálne

Test design: Test môže byť príliš striktný alebo nezohľadňovať business rules (napr. test na unique, ale business povoluje duplikáty v určitých prípadoch)

6

Hard

0 / 4 points

Business potrebuje report "Customer 360" - všetky info o zákazníkovi (profile, orders, payments, support tickets) z posledných 3 rokov. V Data Vault to vyžaduje 8+ JOINov a query trvá 5 minút. Ako by si to optimalizoval na < 10 sekúnd?

Point-in-Time (PIT) table: Pre-compute daily snapshoty všetkých satellites pre zákazníka, redukuje JOINy z 8 na 1-2

Bridge table: Materialized table s pre-joined hubmi a linkami (customer → orders → products), refresh denne cez dbt

Agregácie: Pre-compute metriky (total_orders, lifetime_value, avg_ticket_size) do summary table, nemusíš to počítať on-the-fly

Partitioning/Clustering: PARTITION BY customer_hk a year, CLUSTER BY (customer_hk, load_date), skenuje len relevantné partitions

Materialized view: Vytvoriť customer_360 ako materialized view/table, incrementálne update len changed customers

Caching layer: Pre často pristupovaných zákazníkov použiť Redis/memcached cache, alebo query result cache v DWH

7

Hard

0 / 4 points

Zistíš, že za posledných 6 mesiacov sa do Data Vault nahrávali duplicitné záznamy kvôli bug-u v ETL. Teraz máš v hub_customer duplikáty (rovnaký customer_id s rôznymi hash). Ako by si to vyčistil bez straty dát a histórie?

Analýza rozsahu: Najprv identifikovať všetky duplicate páry, počet ovplyvnených zákazníkov, satellites a links, ktoré ukazujú na duplicate hashes

Merge stratégia: Určiť "master" hash (najstarší load_date alebo najviac satellites), všetky ostatné duplikáty sú "slave" hashes

Satellite migration: UPDATE/INSERT satellites - zmeniť customer_hk z "slave" na "master", zachovať load_date a record_source (append, nie overwrite)

Link update: UPDATE linky - nahradiť "slave" hash za "master" hash v customer_hk FK, zachovať link_hk integritu

Audit trail: Vytvoriť audit table zaznamenávajúcu merge (old_hash, new_hash, merge_date, reason), soft-delete duplicate hubov (nie hard delete)

Prevention: Pridať dbt test unique na (customer_id) v staging, constraint na hub level, monitoring pre future duplicates

8

Hard

0 / 4 points

Potrebuješ implementovať GDPR "right to be forgotten" v Data Vault architektúre. Zákazník žiada vymazanie všetkých osobných údajov. Ako to urobíš bez porušenia Data Vault principov (never delete)?

Pseudonymization approach: Nahradiť PII v satellites anonymizovanými hodnotami (email → hashed_email, name → "REDACTED"), zachovať štruktúru

Separate PII satellites: Dizajn: Oddeliť PII do vlastných satellites (sat_customer_pii), non-PII ostávajú (sat_customer_metadata), delete len PII satellites

Deletion satellite: Pridať nový satellite row s flagom is_deleted=true a anonymizovanými dátami, Business Vault views filtrujú deleted records

Encryption/Tokenization: PII uložené encrypted s customer-specific key, pri GDPR request zmazať encryption key - data ostávajú ale sú nečitateľné

Audit a compliance: Logovať všetky GDPR requests a actions, retention policy pre audit logs (napr. 7 rokov), dokázať compliance pre regulátory

Hub integrity: Hub zostáva (obsahuje len hash), všetky FK v linkoch fungujú, analytika na non-PII dátach možná (agregáty, trendy)

9

Easy

0 / 2 points

What is the difference between batch processing and stream processing in data engineering?

Batch Processing: Processes large volumes of data at scheduled intervals, higher latency but more efficient for bulk operations (e.g., daily ETL jobs)

Stream Processing: Processes data in real-time as it arrives, lower latency, continuous processing (e.g., Kafka Streams, Apache Flink)

10

Easy

0 / 2 points

Explain the concept of data partitioning in distributed systems. Why is it important?

Definition: Dividing large datasets into smaller chunks distributed across multiple nodes/machines for parallel processing

Benefits: Improves query performance, enables horizontal scaling, reduces bottlenecks, allows parallel processing across cluster nodes

11

Easy

0 / 2 points

What is the purpose of a data warehouse vs a data lake?

Data Warehouse: Structured, processed data optimized for analytics and reporting, predefined schema (schema-on-write), relational format

Data Lake: Raw, unprocessed data in native format, schema-on-read, stores any data type (structured, semi-structured, unstructured), more flexible but requires processing

12

Medium

0 / 3 points

Explain slowly changing dimensions (SCD) in data warehousing. What are the different types?

SCD Type 1: Overwrite old values with new ones, no history maintained, simplest approach

SCD Type 2: Create new record for changes, maintain full history with effective dates and version numbers, most common approach

SCD Type 3: Add new columns for changed attributes, limited history (previous and current), rarely used

13

Medium

0 / 3 points

How do you handle late-arriving data in a streaming pipeline?

Watermarks: Define allowed lateness threshold, process events within watermark window, trigger computations after watermark passes

Window Strategies: Use tumbling, sliding, or session windows with grace periods, reprocess data if arrives within grace period

State Management: Maintain state stores to update aggregations, implement side outputs for very late data, use event-time processing vs processing-time

14

Medium

0 / 3 points

What is data lineage and why is it important in data engineering?

Definition: Tracking data flow from source to destination, documenting transformations, dependencies, and data lifecycle

Benefits: Debugging and root cause analysis, compliance and audit trails, impact analysis for changes, data quality tracking

Tools: Airflow for orchestration tracking, Apache Atlas for metadata management, dbt for transformation lineage, custom solutions with metadata stores

15

Critical

0 / 5 points

Design a real-time data pipeline that ingests 100,000 events/second from IoT devices, performs aggregations, and serves results with sub-second latency. Include architecture, technologies, and scaling strategies.

Ingestion Layer: Apache Kafka/Kinesis for event streaming, partitioned topics for parallelism, producer batching and compression, schema registry for data validation

Processing Layer: Apache Flink/Spark Structured Streaming for stateful processing, windowed aggregations (tumbling/sliding), exactly-once semantics with checkpointing

Storage & Serving: Redis/Druid for low-latency serving, time-series DB (InfluxDB/TimescaleDB) for historical data, materialized views for pre-aggregation

Scaling: Horizontal scaling with Kubernetes, auto-scaling based on lag metrics, data partitioning by device ID/region, monitoring with Prometheus/Grafana

Reliability: Multi-zone deployment, circuit breakers, backpressure handling, dead letter queues for failures, disaster recovery with backup clusters

16

Critical

0 / 5 points

You need to migrate a legacy 50TB data warehouse to the cloud while maintaining 24/7 availability. Design the migration strategy including data validation, rollback plan, and zero-downtime approach.

Parallel Running: Implement dual-write to both systems, use CDC (Change Data Capture) for real-time sync, maintain legacy system during transition

Phased Migration: Migrate by data domain/table priority, start with read-only/archived data, gradually move active tables, use blue-green deployment pattern

Data Validation: Row count comparisons, checksum validation, reconciliation reports, automated testing of critical queries, sample data validation

Rollback Strategy: Maintain legacy system operational, implement traffic routing switches, preserve data snapshots, define rollback triggers and procedures

Tools & Optimization: AWS DMS/Azure Data Factory for migration, data compression during transfer, parallel transfer sessions, network optimization, monitoring dashboards

17

Critical

0 / 5 points

Design a data quality framework for a large-scale data platform. Include anomaly detection, validation rules, automated remediation, and SLA monitoring.

Validation Framework: Schema validation (Great Expectations, dbt tests), business rule validation, referential integrity checks, NULL/duplicate detection, statistical profiling

Anomaly Detection: Statistical methods (z-score, IQR), ML-based anomaly detection, trend analysis, volume/freshness monitoring, pattern recognition

Automated Remediation: Auto-correction for known issues, quarantine bad data, trigger reprocessing workflows, fallback to default values, notification systems

SLA Monitoring: Define quality metrics (completeness, accuracy, timeliness), dashboard with quality scores, alerting thresholds, quality gates in pipelines

Governance: Data quality dimensions tracking, lineage integration, audit trails, quality reports, continuous improvement feedback loops

18

Critical

0 / 5 points

Implement a CDC (Change Data Capture) solution for a production database with millions of transactions daily. How do you handle initial snapshot, incremental changes, schema evolution, and ensure exactly-once delivery?

CDC Implementation: Use Debezium/AWS DMS for log-based CDC, capture INSERT/UPDATE/DELETE operations, maintain transaction ordering, handle DDL changes

Initial Snapshot: Consistent snapshot with minimal locking, parallel snapshot for large tables, capture LSN/SCN position, seamless transition to incremental mode

Exactly-Once Delivery: Use Kafka transactions, implement idempotency keys, deduplication at consumer, offset management, state store for tracking processed records

Schema Evolution: Schema registry integration, backward/forward compatibility, graceful handling of column additions/removals, version management, automated schema migration

Monitoring & Reliability: Lag monitoring, throughput metrics, error handling with DLQ, backpressure management, alerting on replication delays, disaster recovery procedures

🎯 Interview T-Mobile

📋 Candidate Information

📊 Total Score

📋 Interview Report - T-Mobile Data Engineer