Data Collection and Instrumentation Questions

Designing and implementing reliable data collection and the supporting data infrastructure to power analytics and machine learning. Covers event tracking and instrumentation design, decisions about what events to log and schema granularity, data validation and quality controls at collection time, sampling and deduplication strategies, attribution and measurement challenges, and trade offs between data richness and cost. Includes pipeline and ingestion patterns for real time and batch processing, scalability and maintainability of pipelines, backfill and replay strategies, storage and retention trade offs, retention policy design, anomaly detection and monitoring, and operational cost and complexity of measurement systems. Also covers privacy and compliance considerations and privacy preserving techniques, governance frameworks, ownership models, and senior level architecture and operationalization decisions.

HardTechnical

37 practiced

Offer a cost-optimization plan that reduces monthly analytics storage and query spend by 30% without materially degrading analyst productivity. Include concrete levers such as partitioning, clustered tables, materialized views, cold storage, and query governance.

Sample Answer

Situation: Our analytics monthly bill is high — storage + query costs — and leadership asked to cut spend by 30% without materially slowing analysts who deliver dashboards and ad-hoc insights.

Plan overview (target: 30% reduction across storage+query in 3 months):- Quick wins (month 0–1): query governance + cost-awareness — expected 10% reduction- Mid-term (month 1–2): partitioning & clustering, materialized views — expected 15% reduction- Longer-term (month 2–3): cold storage tiering + lifecycle policies — expected 5% reduction

Concrete levers and actions1) Query governance & cost transparency (10%):- Enforce talk-before-run for expensive ad-hoc queries: require developers to annotate queries with expected row scan estimate and business purpose in a lightweight PR or Slack channel.- Create cost dashboards (top queries by cost, recurring scheduled queries) and daily budget alerts.- Implement query limits: sandbox vs prod quotas, and abort threshold for scans > X TB.Impact: stops runaway queries and reduces redundant scans.

2) Partitioning + clustered tables (8–10%):- Partition large fact tables by date (ingest or event_date) to limit scanned partitions for recent queries.- Apply clustering (or sort keys) on high-cardinality filter columns (customer_id, country) so queries with filters scan fewer blocks.- Migration plan: start with top 5 largest tables. Use backfill jobs to create partitioned/clustered tables; validate row counts & performance.Impact: reduces scanned bytes dramatically on common dashboard queries.

3) Materialized views and aggregate tables (5–7%):- Identify top-10 high-cost, repetitive dashboard queries and create materialized views or pre-aggregated nightly tables (e.g., daily/hourly rollups).- Use incremental refresh where supported. Prefer OLAP-friendly formats (columnar parquet) for storage.Impact: moves heavy compute from interactive time to cheap scheduled refreshes.

4) Cold storage lifecycle (3–5%):- Move >365-day raw event data to cheaper storage (long-term object store or lower-cost DB tier) and keep summarized aggregates in hot store.- Implement automatic lifecycle: raw events → warm (30–365d) → cold (>365d).Impact: reduces active storage costs while preserving ability to rehydrate if needed.

Implementation and trade-offs:- Pilot on one business domain: run A/B comparing query latency and analyst satisfaction.- Keep a self-serve “rehydrate” path for ad-hoc deep-dive (e.g., spin up temporary cluster or restore snapshot).- Monitor: cost by table, query latency SLOs, analyst tickets about slowness.

Expected outcome:- Governance: 10% saving- Partition/cluster + materialized views: ~20% saving combined- Cold storage: 3–5% savingTotal: ~33–35% estimated reduction while preserving analyst productivity via pre-aggregates and fast access to recent data.

Key success metrics: monthly $ spend, avg query bytes scanned, dashboard load time, number of interrupted analyst workflows.

EasyTechnical

52 practiced

Explain, in your own words, what 'instrumentation' and 'event tracking' mean for a product analytics team. Describe 3 concrete examples of events you would track for an e-commerce product, the minimum metadata each event should include, and how properly instrumented events enable downstream analytics and machine learning use cases.

HardTechnical

36 practiced

A major ETL job is failing intermittently and causing missing events in daily reports. Outline a root-cause investigation approach: what logs and metrics you would check, how you'd perform forensic queries, and how you'd present confidence in the corrected backfill to stakeholders.

Sample Answer

Situation: A daily ETL intermittently fails, causing missing events in reports. My goal: quickly identify root cause, quantify impact, fix and validate a backfill, and communicate confidence to stakeholders.

Investigation steps1. Triage & surface-level checks- Check orchestration system (Airflow/DBT) run history, task logs, retry counts, and timestamps.- Review job exit codes and stack traces in application logs.- Look at recent deploys/config changes and schema migrations.

2. Metrics and alerts to review- Upstream source ingestion rates (events/min), producer lags, and Kafka/queue offsets.- ETL throughput (rows/sec), processing latency, and error rates.- Downstream report row counts and key KPI time-series for anomalies (sudden drops).

3. Forensic SQL queries (examples)- Compare expected vs actual event counts by source/day:

sql

SELECT date(event_ts) as day,
       COUNT(*) as actual_events,
       expected.count as expected_events,
       COUNT(*) - expected.count as delta
FROM events e
LEFT JOIN expected_daily_counts expected USING (day)
WHERE day BETWEEN '2025-11-10' AND '2025-11-22'
GROUP BY day, expected.count;

- Identify missing event IDs by joining source and warehouse:

sql

SELECT src.id
FROM source_table src
LEFT JOIN warehouse_table wh ON src.id = wh.id
WHERE src.date = '2025-11-21' AND wh.id IS NULL LIMIT 100;

- Sample raw messages around ETL failure window to find corrupt records.

4. Root-cause hypotheses & tests- Bad records causing job crash: scan for schema violations or nulls.- Downstream write failures: check DB constraints, deadlocks, or permissions.- Resource exhaustion: examine CPU/memory/IO metrics during runs.

5. Backfill validation & confidence- Perform a controlled backfill in a staging environment; run reconciliation queries comparing source vs warehouse.- Produce reconciliation report: total expected vs ingested, sample of missing IDs, error log excerpts.- Calculate coverage and confidence metrics: e.g., % matched rows, % sampled rows verified, and number of exceptions.

Communication to stakeholders- Deliver a concise incident summary: impact window, root cause, actions taken, and residual risk.- Share metric-backed evidence (charts + SQL results) and attach verification checklist.- Provide action plan and rollback/backfill schedule with sign-off criteria: reconciliation passes (>=99.9% matched) and sample audit (manual review of 100 records).- Offer follow-ups: post-mortem, monitoring improvements, and preventative changes (schema validation, idempotent writes, alerting).

This approach balances technical forensics (logs, SQL reconciliation) with clear, measurable evidence to build stakeholder trust in the fix and backfill.

MediumTechnical

32 practiced

Describe the differences and trade-offs between sampling at collection (client-side) vs. sampling downstream (post-ingestion). Provide 4 criteria to decide which approach an organization should adopt.

MediumTechnical

28 practiced

Explain last-touch vs. first-touch vs. linear multi-touch attribution models. For each, describe the instrumentation requirements (events, timestamps, cross-device identity) and the main biases introduced if instrumentation is incomplete.

Sample Answer

Brief definitions:- First-touch: credit the very first observed interaction (click, impression, email open) that led to a conversion.- Last-touch: credit the last observed interaction immediately before conversion.- Linear multi-touch: split credit evenly across all observed touchpoints in the conversion path.

Instrumentation requirements (common needs):- Events: capture discrete touch events (channel, campaign, creative, event type) for every interaction.- Timestamps: precise timestamps to order touches and compute windows (sessionization, lookback windows).- Persistent identifiers: reliable user IDs (logged-in user ID) and device IDs (cookie, mobile advertising ID).- Cross-device identity: deterministic or probabilistic linking to stitch touches across devices/sessions into one path.- Session/context metadata: referrer, landing page, campaign parameters (UTM), attribution window settings.

Model-specific notes:- First-touch: needs accurate capture of earliest touch within lookback window and stable ID persistence over time.- Last-touch: needs accurate capture of final touch and correct ordering (timestamps) — robust sessionization to avoid attributing internal redirects.- Linear: needs complete path capture; requires consistent event capture across channels and devices so all touches are visible.

Biases when instrumentation is incomplete:- Missing timestamps or misordered events: last-touch and first-touch can assign credit to the wrong touch (e.g., redirects, delayed event logging).- Missing early touches (short cookie lifetime, no cross-device stitching): first-touch and linear undercount upper-funnel channels (brand, display) — overvalues last-touch channels (search, direct).- Missing final touches (ad blockers, server-side conversion): last-touch underestimates channels that drove the conversion; conversions may be unattributed.- Incomplete cross-device identity: fragments paths => inflation of touch counts on single device channels, bias toward channels where users are logged-in.- Channel-specific capture gaps (e.g., offline, call center): linear spreads credit only across observed touches, so offline drivers are ignored, skewing ROI.- Event loss/latency: probabilistic stitching can introduce false positives/negatives; linear model amplifies errors because many touches are included.

Practical guidance:- Prioritize reliable identifiers and timestamp quality; document capture gaps.- Choose model mindful of biases: last-touch for fast transactional funnels, first-touch for acquisition-focused analysis, linear for exploratory insight — but report limitations and consider hybrid or data-driven attribution when possible.

Unlock Full Question Bank

Get access to hundreds of Data Collection and Instrumentation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.