Data Ingestion and Source Systems Questions
Understand different data sources and ingestion methods: batch processing (scheduled jobs), streaming (Kafka, Kinesis), API polling, database replication, and log aggregation. Know the trade-offs between real-time and batch ingestion. Understand concepts like exactly-once delivery and idempotency.
MediumTechnical
45 practiced
Describe how you would design an idempotent API ingestion flow that supports retries. Explain how to generate idempotency tokens, where to store tokens or processed IDs, and how to design sinks to use these tokens to avoid duplicate downstream writes for BI systems.
Sample Answer
Situation / goal: Build an idempotent ingestion flow so retrying API calls (or duplicate webhooks) never create duplicate BI records or metrics.Approach (high-level):- Require clients to provide an idempotency token for each logical operation (or generate one client-side when applicable). Treat identical tokens as the same operation.- Persist tokens (or processed IDs) at the ingestion boundary and check them before processing.- Ensure downstream sinks (data warehouse, event table) also enforce uniqueness so eventual consistency / replay won't duplicate BI writes.Generating idempotency tokens:- Prefer stable deterministic tokens tied to business keys: e.g., SHA256(customer_id + event_type + event_timestamp + source_id). This makes retries produce same token.- Alternatively clients can supply UUIDv4 per logical operation when business keys are not unique.Where to store tokens / processed IDs:- Fast, durable store at ingestion: a transactional DB or key-value store (Postgres, DynamoDB, Redis with persistence).- Store record: {idempotency_token, received_at, processing_state (pending/complete/failed), result_reference (row_id or event_id)}.- Use a TTL for tokens if operations are one-off; keep permanent for financial records.Processing flow:1. On receive: look up token. If exists and state=complete, return stored result (200/201) — no reprocessing.2. If not exists, insert token with state=pending in a single atomic op (use DB unique constraint on token to prevent races).3. Process event: write to staging/event table. On successful write, update token state=complete and store the produced record id.4. If processing fails, set state=failed (allow retries) or remove token after backoff.Design sinks for BI (avoid duplicate downstream writes):- Append an event table with unique constraint on idempotency_token (or a composite business key) so warehouse rejects duplicates.- Use CDC or ETL that de-duplicates by token before merging into fact tables: merge INTO fact USING (SELECT ... GROUP BY idempotency_token) ON conflict do nothing/update.- For streaming sinks, include token in event payload and have consumers deduplicate within a time window (e.g., using stateful windowed dedupe keyed by token).Examples & trade-offs:- Postgres: create table tokens(token text primary key, state text, result_id uuid). Use INSERT ... ON CONFLICT DO NOTHING to claim token.- DynamoDB: PutItem with conditional expression to ensure single writer.- Keep token retention long for legal/audit reasons; short TTL for ephemeral events to save space.Why this works:- Atomic claim prevents concurrent double-processing.- Persisted token + downstream unique constraints enforce end-to-end idempotency so BI metrics remain correct even with retries or replayed webhooks.
HardSystem Design
33 practiced
Explain techniques to achieve exactly-once semantics end-to-end for streaming pipelines (e.g., Kafka -> Flink -> Snowflake). Cover the roles of checkpointing, Kafka transactions, idempotent sinks, two-phase commit, and practical limitations when sinks do not support transactions.
Sample Answer
Requirements & goal:- End-to-end exactly-once (E2E) means each input message from Kafka is reflected exactly once in the target (e.g., Snowflake) despite failures, retries, or restarts — no duplicates, no omissions. For BI this ensures metrics and dashboards are correct.High-level approach:1. Checkpointing (Flink)- Flink’s distributed snapshots record stream processing state and offsets atomically. When Flink restores from a checkpoint it reprocesses from the saved Kafka offsets so computation state + source position stay consistent. This provides exactly-once semantics within Flink’s operator chain.2. Kafka transactions- Producers can write to Kafka using transactions so a group of messages is committed atomically. Useful when Flink writes back to Kafka or for write-once atomic handoffs. Kafka transactions ensure consumers reading committed transactions don’t see partial results.3. Idempotent sinks- If the sink supports idempotence (upserts keyed by a unique id, or dedupe on write), you can retry writes safely. Example: write row with a unique event_id and use INSERT ... ON CONFLICT or MERGE in Snowflake. This is often the simplest practical path for BI pipelines.4. Two-phase commit (2PC) / XA-style sinks- Flink implements a 2PC sink interface (XA-style) that coordinates checkpoint->pre-commit->commit with external systems that support transactions. During checkpoint, the sink prepares a transaction (writes but does not commit), and only on successful checkpoint commit will the sink commit the external transaction. This achieves E2E when the sink supports transactional prepare/commit.Practical limitations & patterns when sinks lack transactions (e.g., many data warehouses):- No native prepare/commit: cannot do true 2PC. Options: - Use idempotent, deduplicating writes (best practical choice): write batch with explicit batch_id or event_id and have downstream dedupe logic or use MERGE by key in Snowflake. - Exactly-once via idempotent file staging: Flink writes files with unique names to cloud storage, checkpoint ensures visibility, then an external job loads files into Snowflake and marks them processed. This is “atomic by convention.” - Compensating/at-least-once with downstream dedupe: accept at-least-once writes but dedupe during reads/queries (materialized view keyed by event_id). - Use Kafka as the source of truth for reprocessing and build BI from compacted topics or changelogs.Trade-offs & BI considerations:- True E2E requires sink transactional support — rare for analytic stores. Idempotent writes + strong ordering keys and deterministic processing are pragmatic and usually sufficient for dashboards.- Complexity vs ROI: 2PC adds operational complexity; prefer idempotent upserts, monotonic event_ids, and monitoring for duplicates. Verify metric correctness with audits (counts, watermarks).- Latency: transactional or staging patterns may add latency; assess dashboard SLAs.Recommendation for BI pipelines:- Use Flink with checkpointing to make processing deterministic.- Produce idempotent writes to Snowflake using MERGE/UPSERT with event_id or batch_id.- If you need absolute guarantees and low latency, consider sinks that support transactions or use Kafka as a materialized source with compacted changelogs and a consumer that builds the final table.
HardTechnical
42 practiced
A critical upstream system accidentally published corrupted data for a 6-hour window. Describe a disaster recovery and reprocessing plan to correct downstream BI datasets and dashboards. Include detection, scope identification, replay/backfill steps, idempotent writes, resource planning, and communication to stakeholders.
Sample Answer
Situation: An upstream system published corrupted records for a 6‑hour window that fed our BI pipelines. We need a clear recovery plan to correct downstream datasets and dashboards with minimal business disruption.1) Detection- Automated monitoring: alerts from data quality checks (row counts, schema validation, column checksums, null-rate thresholds) flagged anomaly.- Quick validation: sample corrupted records, confirm timestamp range and corruption type (e.g., malformed JSON, swapped fields, out-of-range values).2) Scope identification- Trace lineage: use pipeline catalog (Airflow/DBT lineage, data catalog) to list affected tables, marts, aggregate tables, materialized views, and dashboards.- Determine consumer impact: execute queries to count affected rows per dataset, identify dashboards relying on those fields, and prioritize by business criticality (finance > ops > ad-hoc).3) Containment- Pause automated downstream refreshes for affected datasets OR switch to a safe snapshot to prevent further propagation.- Freeze publishing of reports to executives until validated.4) Replay / Backfill strategy- Source fix coordination: confirm upstream will re-publish corrected raw data or provide a clean snapshot for the 6‑hour window.- Extract clean data for the window using source_timestamp.- Backfill approach: - For raw/landing layer: insert clean records for that window. - For transformed/consumption layers: run targeted reprocessing jobs only for partitions/time-partitions covering that window (avoid full rebuilds). - Use batch replays or CDC-based replay if available (replay event log).5) Idempotent writes and safety- Ensure all write operations are idempotent: - Use upserts MERGE on primary key + source_timestamp or a tombstone/version column. - Add data provenance columns (source_version, ingestion_time) to choose latest valid record. - Implement transactional writes or staging tables + atomic swap of partitions.- If only partial fields corrupted, write logic to update only affected columns while preserving others, using coalesce(versioned_field, existing_field).6) Validation & QA- Run row-level diff between pre- and post-backfill snapshots; run reconciliation tests: counts, sums, key metrics.- Recompute a small set of dependent dashboards in a sandbox and compare to expected values from business owners.- Smoke-test executive dashboards and sign off with stakeholders.7) Resource and schedule planning- Estimate data volume for 6‑hour window to size compute (warehouse slots, Spark executors).- Schedule backfill during low-usage hours to reduce impact; allocate engineers for monitoring and quick rollback.- Reserve extra compute and set time buffers for multiple iterations (~24–48 hours depending on complexity).8) Communication- Immediate notification to stakeholders: what happened, scope, that we paused downstream refreshes, and expected timeline for fix.- Daily status updates (or hourly while active) with clear owners, next steps, and risk.- Final report once complete: root cause summary, data validation evidence, dashboards restored, and recommended preventative measures.9) Post‑mortem & prevention- Implement additional detection (field-level constraints, checksums) and automated rollback triggers.- Add versioned raw snapshots and automated replay capability (message retention, CDC).- Update runbooks and assign SLA owners.Result: This structured approach limits exposure, provides repeatable, auditable idempotent reprocessing, minimizes downtime, and restores trust in BI outputs while improving future resilience.
HardTechnical
33 practiced
Design SCD Type 2 processing for slowly-changing dimensions in a hybrid streaming + batch pipeline. Provide the schema for a dimension table (surrogate_key, natural_key, attributes..., effective_start, effective_end, is_current) and show pseudo-SQL or pseudo-code for how you would update the table on each incoming change event while preserving history, handling out-of-order events and late-arriving data.
Sample Answer
Approach: maintain immutable history rows with surrogate_key and validity window [effective_start, effective_end). For each incoming change event include natural_key, changed attributes, event_time, and a tie-breaker (source, event_id). On arrival, find the row active at event_time and adjust windows; if event is newer than current, close current and insert new; if event is late (falls inside history) split range and insert corrected row; use transactions/locks and idempotent keys to avoid duplicates.Dimension schema:CREATE TABLE dim_customer ( surrogate_key BIGINT PRIMARY KEY, -- surrogate PK natural_key VARCHAR NOT NULL, -- business key attr1 ..., attr2 ..., effective_start TIMESTAMP NOT NULL, -- inclusive effective_end TIMESTAMP NOT NULL, -- exclusive; use '9999-12-31' for open is_current BOOLEAN NOT NULL, source VARCHAR, event_id VARCHAR, -- idempotency/tiebreaker UNIQUE(natural_key, effective_start) -- optional);Pseudo-SQL (transactional; works for batch or per-event streaming upserts):-- parameters: :natural_key, :attrs..., :event_time, :event_id, :sourceBEGIN;-- 1) Find rows for this natural_key that overlap event_timeSELECT * FROM dim_customer WHERE natural_key = :natural_key AND effective_start <= :event_time AND effective_end > :event_time FOR UPDATE; -- lock affected rowsIF no row found THEN -- first-ever or arrived earlier than earliest: insert new row covering [event_time, +inf) INSERT INTO dim_customer (...) VALUES (..., :event_time, '9999-12-31', TRUE, :source, :event_id);ELSE LET existing = the row found; -- idempotency: ignore if same event_id already applied IF existing.event_id = :event_id THEN COMMIT; RETURN; END IF; -- If attributes identical and event_time <= existing.effective_start -> maybe ignore IF attributes_equal(existing, :attrs) THEN -- update metadata if needed (e.g., set source/event_id) and exit UPDATE dim_customer SET source = :source, event_id = :event_id WHERE surrogate_key = existing.surrogate_key; COMMIT; RETURN; END IF; -- Split or close existing depending on event_time position IF :event_time = existing.effective_start THEN -- replace existing current row (close and insert new) UPDATE dim_customer SET effective_end = :event_time, is_current = FALSE WHERE surrogate_key = existing.surrogate_key; INSERT INTO dim_customer (...) VALUES (..., :event_time, existing.effective_end, existing.is_current, :source, :event_id); ELSE -- event_time is inside existing row; split into [start, event_time) and [event_time, end) UPDATE dim_customer SET effective_end = :event_time, is_current = FALSE WHERE surrogate_key = existing.surrogate_key; INSERT INTO dim_customer (...) VALUES (...attrs from new event..., :event_time, existing.effective_end, existing.is_current, :source, :event_id); END IF;END IF;-- After modifications, ensure only one row per natural_key is marked is_current (the one with effective_end = '9999-12-31')UPDATE dim_customer SET is_current = (effective_end = '9999-12-31') WHERE natural_key = :natural_key;COMMIT;Key points / handling:- Out-of-order & late events: use event_time to place change into correct historical slot; split existing rows rather than overwrite.- Idempotency: include event_id and check before applying.- Concurrency: SELECT ... FOR UPDATE or optimistic timestamps to avoid races.- Integration with streaming: run this per-event in a transactional store (Postgres, Snowflake streams + tasks, Delta Lake with ACID).- Backfill: process older events same logic; deduplicate by event_id and ignore if redundant.- Performance: index on (natural_key, effective_start, effective_end). Consider batching and merge-based approaches for high throughput.Edge cases:- Exact tie on event_time -> use event_id or source priority as deterministic tiebreaker.- Deletes: model as a row with attributes null and closed later.- Clock skew: if event_time unreliable, prefer ingestion_time + source-correction policy.
HardTechnical
33 practiced
Design a testing framework for ingestion pipelines covering unit tests, integration tests, contract tests, and data quality (DQ) tests. Explain how you would create synthetic/sampled datasets, run contract tests in CI, simulate failures (late data, duplicates), and gate deployments based on test results.
Sample Answer
Requirements:- Ensure BI pipelines deliver correct, timely, and complete data for dashboards (unit, integration, contract, and DQ tests).- CI gating so broken pipelines block deployment to prod.- Ability to simulate edge conditions: late data, duplicates, schema drift.High-level design:- Test harness integrated with ETL orchestration (Airflow/DBT) + CI (GitHub Actions/GitLab CI).- Test catalog stored alongside pipeline code: unit specs, contract definitions (schema/types/semantics), DQ rules, integration scenarios.Components & responsibilities:1. Unit tests: small functions/transforms in DBT or Python using pytest/DBT tests with synthetic rows covering edge logic.2. Contract tests: JSON/Avro/SQL-based schema + semantic constraints (primary keys, allowed enums, ranges). Use a contract runner (great_expectations + schema registry) executed in CI on sample output.3. Integration tests: end-to-end runs in a lightweight test env (sandbox warehouse) using sampled/synthetic datasets; validate upstream->downstream behavior.4. DQ tests: Great Expectations suites for row counts, null rates, uniqueness, referential integrity, freshness.5. Failure simulation: test data generators produce: - Late data: timestamps behind current watermark - Duplicates: duplicate key rows - Schema drift: extra/missing columns Run these scenarios in integration tests to verify deduplication, watermark handling, and alerting.6. Synthetic/sampled datasets: - Create small parametrized fixtures representing normal, edge, and failure modes; store generators in repo; use stratified sampling of production snapshots (anonymized) for realism.7. CI integration & gating: - On PR: run unit + contract tests (fast). If contract fails, block merge. - On merge to main: run full integration + DQ scenarios in a test warehouse. Use status checks; require all tests pass before promoting DAGs to prod. - Post-deploy: run smoke DQ checks and monitor metrics (freshness, volumes); auto-rollback or alert on critical failures.Practical notes:- Version contracts with schema registry; pin contracts per downstream dashboard.- Instrument test failures with reproducible artifacts (failed rows, diffs).- Collaborate with data engineers to maintain test environments and ensure test datasets reflect business logic for BI metrics.
Unlock Full Question Bank
Get access to hundreds of Data Ingestion and Source Systems interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.