Company Research and Business Understanding Questions

1. GMV (Gross Merchandise Value)

• Raw events/tables: transaction_completed event stream (order_id, buyer_id, seller_id, items, item_price, currency, fees, timestamp), refunds events; aggregated daily/orders table partitioned by date.
• Freshness & SLA: near-real-time (5–15 min) for dashboards; daily stitched canonical table available by 02:00 UTC.
• Instrumentation: idempotent event IDs, currency normalization, tax/discount breakdown, lineage metadata, and explicit refund linkage.

2. Take Rate

• Raw events/tables: transactions table (gross_amount, platform_fee, seller_payout), fee_config table; aggregated daily/monthly revenue_by_transaction.
• Freshness & SLA: daily authoritative rollup by 03:00 UTC; near-real-time estimates for ops.
• Instrumentation: audit of fee calculations, versioned fee rules, ability to recompute historic take rate if rules change.

3. Conversion Rate (listing view → purchase)

• Raw events/tables: listing_view, add_to_cart, checkout_start, transaction_completed; user-session table.
• Freshness & SLA: near-real-time (15 min) for funnel monitoring; daily aggregates.
• Instrumentation: session_id, user_id (or anonymous cookie), deduplication window, UTM/campaign tags.

4. Retention (cohort 7/30-day)

• Raw events/tables: user_created, user_active events (login, purchase, visit); user_cohort table with activity flags per day.
• Freshness & SLA: daily cohort refresh by 04:00 UTC; weekly historical recomputes as needed.
• Instrumentation: consistent user_id, first_touch attribution, timezone normalization, event TTL handling.

5. CAC (Customer Acquisition Cost)

• Raw events/tables: marketing_spend_by_campaign, click/impression events, attributed_user_acquisitions table, LTV table.
• Freshness & SLA: daily spend + attributed acqusitions by 06:00 UTC; monthly reconciled ledger.
• Instrumentation: deterministic attribution window, campaign IDs, cost source reconciliation, currency and fraud filtering.

Cross-cutting notes: enforce event schemas, schema evolution handling, monitoring/SLAs, data quality checks (row counts, nulls, joins), and lineage for reproducibility.

Assume our company is a consumer payments platform (wallet + merchant payments).

Direct competitor: PayPal
Adjacent competitor: Plaid (bank connectivity / data aggregation)

Comparisons vs PayPal (two product/data differences)

1. Transaction breadth: PayPal handles end-to-end payments (authorization, clearing, chargebacks) while we focus on P2P + wallets. Data impact: we must collect richer dispute/chargeback lifecycle events and settlement metadata. Instrumentation: event schema must include payment status transitions, dispute codes, settlement timestamps. Processing: need pipelines joining transactions → settlements → disputes, and time-series windows for reconciliation.
2. Volume and latency SLAs: PayPal has very high throughput and low-latency fraud signals. Data impact: stricter millisecond-level event ingestion and real-time feature computation for fraud. Instrumentation: low-latency streaming (Kafka/KS, CDC) with schema evolution handling. Processing: stream processing (Flink/Spark Structured Streaming) and materialized views for real-time lookups.

Comparisons vs Plaid (two product/data differences)

1. Data origin/type: Plaid ingests bank account/transaction feeds (aggregated, delayed) vs our native transactions (real-time, canonical). Data impact: we need connectors for third-party normalized feeds plus mapping logic. Instrumentation: enrichment pipelines to reconcile external feed IDs to internal user IDs; robust provenance metadata for audit. Processing: periodic backfills, deduplication, and confidence-scoring for inferred fields.
2. Privacy & consent model: Plaid’s data requires explicit consent scopes and token refreshes. Data impact: consent metadata must be tracked and honored across pipelines; data retention and deletion workflows are mandatory. Instrumentation: consent flags attached to every record, automated GDPR/CCPA deletion hooks. Processing: policy-driven ETL that filters or redacts data, and lineage tracking for compliance.

Overall implications for the data engineering team

• Need both robust batch and low-latency streaming architectures.
• Strong schema governance, provenance, and consent-aware pipelines.
• Workflows for reconciliation, deduplication, and real-time feature serving.
• Monitoring/SLAs focused on ingestion latency, data completeness, and compliance.

Business model (brief): The company sells a hybrid SaaS model — a baseline monthly subscription (recurring revenue, tiered plans) plus usage-based analytics billed on metered consumption (e.g., events processed, queries, GBs). Revenue drivers: MRR for subscriptions + variable usage revenue; key risks: billing mismatches, unexpected usage spikes, and churn.

Priority data sources and why (by use case):

1. Billing accuracy — highest priority

• Billing records (invoicing, payment status, plan SKU, discounts): canonical source of billed amounts and revenue recognition.
• Usage logs / metered metrics (raw event counts, timestamps, ingestion pipeline IDs): source of consumption that drives usage charges.
• Customer plan config (from CRM / billing system): mapping of usage to pricing tiers, free quotas, overage rules.
  Why: Reconcile usage to invoices; pipelines should join usage to billing windows to detect under/overcharging.

2. Churn analysis — high priority

• CRM (account status, contract start/end, renewal dates, account owner, segmentation): ground truth for churn events and customer attributes.
• Billing records (payment failures, cancellations, downgrade history): signals of financial churn drivers.
• Product usage aggregates (weekly active users, feature adoption): behavior predictors of churn.
  Why: Combine behavioral and financial signals to build retention models and identify at-risk accounts.

3. Product analytics — high priority for growth/ops

• Events / product telemetry (page/view events, feature events with user and session IDs, timestamps): enables funnel, retention, and feature-impact analysis.
• Usage logs (detailed and aggregated): correlates product use with cost and performance.
• Support/ticketing data: qualitative context for UX issues.
  Why: Understand how customers use features, optimize onboarding, and link usage to lifetime value.

Implementation/engineering notes:

• Create a single customer dimension (customer_id) as the join key; enforce SCD handling for plan changes.
• Ingest raw usage events into a time-partitioned lake (immutable), run nightly aggregation jobs by customer and billing window, and expose reconciled tables to analytics/finance.
• Implement reconciliation pipelines that compare aggregated usage → expected invoice and flag anomalies; add alerting for large deltas or payment failures.
• Ensure lineage and auditability: keep raw, transformed, and reconciled artifacts and hashes for dispute resolution.

Trade-offs:

• Real-time vs batch: billing reconciliation can be near-real-time for big customers (reduce disputes) but nightly for all others to balance cost.
• Granularity: store raw events for troubleshooting, but expose pre-aggregated metrics for analysts to reduce compute cost.

Stakeholder communication:

• Provide SLAs for data freshness to Finance and CS teams; document definitions (e.g., what counts as “usage”) and circulate sample reconciliations monthly.

OKRs (Objectives and Key Results) are a cadence-driven framework: an Objective states a clear, inspirational goal; Key Results are measurable outcomes that prove progress. As a Data Engineer, I align our roadmap by translating company-level objectives into technical outcomes that enable that business value, then defining delivery milestones and leading indicators that show trajectory.

How I align:

• Clarify company OKRs with stakeholders to derive data requirements.
• Map each Objective to specific data capabilities (pipelines, SLAs, metrics).
• Prioritize work that unlocks the most KR value and communicate trade-offs.
• Use KRs as acceptance criteria for releases and track leading indicators during development.

Example OKR 1 (support revenue growth):
Objective: Increase revenue from personalized offers by enabling real-time analytics.
Key Results (data-engineering influenced):

• KR1: Deliver streaming pipeline with <5s end-to-end latency for user events (measured via latency p95).
• KR2: 99.9% data availability for the personalization feature (measured via uptime and SLA breaches).
  Measurable outputs: deployed streaming ETL, infra runbooks, monitoring dashboards.
  Leading indicators: number of events processed/sec, pipeline error rate, test deployment success rate.

Example OKR 2 (improve data trust):
Objective: Increase analyst productivity by improving data quality.
Key Results:

• KR1: Reduce time to find trustworthy datasets from 3 days to <1 day (measured via user survey / support tickets).
• KR2: Decrease pipeline incidents causing stale data by 80% (measured via incident count).
  Measurable outputs: data catalog with lineage, automated quality checks, alerting.
  Leading indicators: % of tables with schema-tests, failed-quality-check rate, mean time to detect anomalies.

These OKRs tie technical deliverables to business outcomes; tracking leading indicators lets me surface risks early and keep stakeholders aligned while iterating toward the KRs.

Primary user journey (high-level):

• Acquisition: User clicks an ad or referral -> lands on landing page -> signs up (email or SSO).
• Activation/Onboarding: User completes first key action(s) (e.g., completes profile, creates first project/item).
• Engagement: Regular use of core product features (search, create/edit, share).
• Monetization (if applicable): Upgrades to paid plan or completes transaction.
• Retention: Returns over days/weeks; receives notifications, uses saved items.

Three product events/data points to instrument first:

1. user_signup (user_id, timestamp, acquisition_channel, campaign_id, device, region) — captures acquisition source and identity.
2. first_key_action (user_id, timestamp, action_type, metadata) — marks activation (e.g., created first project).
3. session_start (user_id, session_id, timestamp, device, app_version, entry_point) — enables engagement and frequency tracking.

Two derived metrics to compute:

1. Activation rate = count(distinct users with first_key_action within 7 days of signup) / count(distinct user_signups). Helps assess onboarding effectiveness.
2. 7-day retention rate = count(distinct users with >=1 session in days 1–7 after signup) / count(distinct new users). Indicates short-term stickiness.

Implementation notes for a Data Engineer:

• Send events to a unified event pipeline (e.g., Kafka), enforce a schema (Schemas in Avro/Protobuf), validate at ingestion, store raw events in a landing zone and transform into an analytics table (daily user_facts) for metric computation. Add alerts for schema drift and backfill strategy for late-arriving events.