Ride-Hailing Data Modeling & Analytics Requirements Questions
Data modeling and analytics requirements for ride-hailing and mobility-marketplace platforms, including ride event data, trip-level schemas, driver and rider dimensions, pricing and surge data, and geospatial/location data. Covers analytics needs such as reporting, dashboards, and real-time analytics: analytic schema design (star/snowflake), ETL/ELT patterns, data quality and governance at scale, data lineage, privacy considerations, and integration with the broader data stack (data lake/warehouse, streaming pipelines).
EasyTechnical
83 practiced
In Looker and Tableau, teams can place derived logic either in the BI semantic layer (LookML derived tables, Tableau calculations) or as database views/materialized views. As a Lyft BI analyst, outline criteria to decide where to put business logic (consider performance, maintenance, testing, governance, and cost) and give two examples of logic that should live in the warehouse and two that should remain in the BI layer.
Sample Answer
Decision criteria (quick checklist)- Performance: put heavy, repeated, compute‑intensive transforms in the warehouse (pre-join, aggregations, windowing) to leverage DB parallelism and reduce query time from the BI tool.- Maintenance & ownership: logic shared across teams or used by multiple dashboards belongs in the warehouse for single-source-of-truth; BI-layer ok for dashboard-specific tweaks.- Testing & reproducibility: DB views/materialized views are easier to unit test, version, and validate with CI; use warehouse for logic that needs strong test coverage.- Governance & lineage: regulated metrics or KPIs that require auditability should live in warehouse objects with RBAC and documented lineage.- Cost: materializing expensive queries can increase compute/storage costs — prefer BI layer for low-volume, ad‑hoc calculations; use materialized views when query frequency justifies cost.Examples to put in the warehouse1) Canonical daily active users (DAU) / revenue attribution: joins across events, sessions, and payments with window functions and backfills — shared company KPI that’s expensive to compute; implement as a tested scheduled view or materialized table.2) Pre-aggregated time-series tables (hourly rides by region): high-cardinality aggregation used by many dashboards; materialize to reduce repeated heavy scans.Examples to keep in the BI semantic layer1) Presentation-level calculations and filters (e.g., percent-of-parent, rank in current visualization, conditional formatting thresholds): specific to a chart and inexpensive.2) Experiment-specific slices or ad-hoc segmentation (e.g., compare variant A vs B for a dashboard): flexible, frequently changing, and better maintained in LookML/Tableau without touching warehouse.Reasoning: centralize costly, shared, auditable computations in the warehouse for performance, testing, and governance; keep lightweight, presentation-focused, or rapidly changing logic in the BI layer to maximize agility and minimize pipeline churn.
HardSystem Design
125 practiced
Design a strategy to support multi-warehouse analytics where some teams use Snowflake and others use BigQuery, while ensuring consistent metric definitions and minimizing data duplication. Discuss semantic layer options, query federation, CDC-based replication, SQL dialect translation, and how to handle consistency and latency across warehouses.
Sample Answer
Requirements & constraints:- Multiple teams use Snowflake and BigQuery; metrics must be identical regardless of warehouse.- Minimize data duplication, allow low-latency analytics for operational users, and support BI tools (Looker/Tableau/Power BI).- Maintain single source of truth for metric definitions and governance.High-level strategy:1. Canonical semantic layer + metric registry- Create a single canonical metrics catalog (metadata-first): definitions, dimensions, ownership, SQL expressions, tests, and expected freshness SLAs.- Implement via a neutral semantic layer that can publish to both ecosystems: options include dbt Metrics + dbt semantic layer, Cube/MetricFlow, or a centralized Looker/LookML model with translations. This catalog is the authoritative source of truth.2. Query federation vs replication- Primary approach: keep compute-local models where possible and avoid full raw-data duplication. - For cross-warehouse joins or ad-hoc queries, use a query federation engine (Trino/Starburst or BigQuery Omni / Snowpark + external tables) so BI tools can query a virtualized view across warehouses without duplicating all data.- For low-latency operational dashboards, replicate only necessary curated tables (aggregates, dimensions) using CDC (Fivetran, Debezium, or cloud-native Dataflow) into the target warehouse. Keep replication targeted and schema-driven to minimize duplication.3. CDC-based replication- Use CDC for source-of-truth systems to stream inserts/updates/deletes into both warehouses (or into a staging/landing layer from which transformations run). For minimal duplication, replicate base change streams into a neutral storage (e.g., GCS/S3) and materialize derived aggregates per warehouse.- Maintain watermark/fencing columns (cdc_event_time, source_lsn) so consumers can check staleness and reconcile.4. SQL dialect translation & model deployment- Keep metric expressions in the canonical layer in a dialect-agnostic form (dbt + Jinja macros or MetricFlow). Compile/generate warehouse-specific SQL artifacts: - dbt packages that build the same models in Snowflake and BigQuery (use dbt adapters and macros to translate functions). - For semantic layers not supporting compilation, maintain tests that validate equivalence of generated SQL across warehouses.- Use a CI pipeline to run unit tests and data diff checks (row counts, sample keys) when deploying models.5. Consistency, latency, and reconciliation- Classify metrics by SLA: near-real-time (seconds/minutes), batched (hourly), or historical. Drive replication/materialization frequency accordingly.- Provide freshness metadata in the semantic layer (last_updated_at) and surface it in dashboards. Enforce versioned metric definitions so changes are explicit and auditable.- Reconciliation processes: nightly jobs that sample key aggregates across warehouses and surface diffs > threshold to owners; automated alerts for schema drift or large deltas.- For strict consistency across BI tools, route metric calculation through the canonical metric API (if supported) or pre-materialized metric tables; if live federation used, ensure the engine applies the canonical predicates and timezones.Operational considerations & trade-offs:- Federation reduces duplication but can increase query latency and complexity for distributed joins—use for exploratory analytics and administrative queries.- Targeted CDC replication + local materialized aggregates gives best performance for dashboards at cost of some duplicated storage and need for sync pipelines.- Investing in a canonical semantic layer and automated SQL generation/tests yields long-term governance and consistent metrics with moderate engineering effort.Example flow (near-real-time KPI dashboard):- Source DB -> CDC -> streaming to S3 + load into Snowflake (staging) and BigQuery (staging).- dbt (CI) compiles canonical metric models into both warehouses; materialized hourly aggregates kept in each warehouse.- BI tool queries local materialized metrics via semantic layer (Looker or dbt metrics API). Freshness and metric version shown on dashboard. Nightly reconciliation validates parity.This approach balances consistency, minimal duplication, and performance while keeping metric definitions centralized and auditable.
MediumTechnical
62 practiced
For embedded Looker dashboards used by Lyft drivers, explain how to implement row-level security so an embedded session only shows trips belonging to the authenticated driver. Cover configuration using Looker user attributes, access filters, parameterized explores, performance and security trade-offs, and how to prevent privilege escalation in embedded contexts.
Sample Answer
Approach (summary)- Use Looker user attributes + model-level access_filters or parameterized explores to enforce row-level security (RLS) so embedded sessions only see trips for the authenticated driver. Combine secure embed generation (signed sessions) with least-privilege model permissions and query-time filters.Implementation steps1. Provision a Looker user attribute (e.g., driver_id).2. When creating an embedded session (via Looker’s Embed SDK / SSO Embed API), set the user_attribute driver_id to the authenticated driver’s id in the signed session payload.3. In the LookML model, apply an access_filter that ties data to the user attribute:
4. If multiple roles exist (dispatcher, driver), create role-based permission sets and ensure embedded sessions get only the driver role.Performance and security trade-offs- Access_filters push predicates to SQL, so databases can use indexes => good for performance if driver_id is indexed.- Many distinct user attributes can reduce cache hit rates (Looker query cache keyed by SQL). Mitigate via aggregated materialized views for common queries, or cache per-driver where feasible.- Parameterized explores (using user attributes) are simpler and secure; avoid client-side filters as they can be bypassed.Preventing privilege escalation in embedded contexts- Never pass elevated permissions in the embed signature. Generate signed embed sessions server-side with only the driver role and limited attributes.- Do not expose API credentials to the client. Use a backend service that validates the driver’s auth and signs Looker embed URLs.- Use model-level access_filters rather than liquid templating that references user-submitted values.- Audit: enable Looker usage logging and periodically review explore/query access patterns.- Test: attempt to change user_attribute in a forged embed—signing should prevent it. Also verify SQL generated includes driver_id predicate.Key notes- Index driver_id in the trips table.- Consider TTL for embed sessions and rotate signing keys.- For multi-tenant or aggregated reports, design derived tables/materializations to reduce per-driver latency.
MediumSystem Design
114 practiced
Design a self-serve metrics layer for Lyft that allows product and data analysts to author, discover, and reuse canonical metrics like completed_rides and rider_churn. Describe components (central metric registry, semantic layer, approval workflow), interfaces (SQL macros, APIs), testing, lineage integration, and an adoption/migration plan to move from ad-hoc queries to the central layer.
Sample Answer
Requirements & goals:- Provide a discoverable, governed, reusable place for canonical metrics (completed_rides, rider_churn) so analysts and PMs can trust and reuse definitions while still enabling ad-hoc analysis.High-level architecture:- Central Metric Registry: single source-of-truth storing metric definitions (name, description, owner, labels, logic, time grain, freshness, SLAs, tests, tags, version).- Semantic Layer: implements metric logic as parameterized SQL macros / LookML measures / dbt models that map metrics to physical tables and dims.- Governance & Approval Workflow: UI + Git-backed PR workflow where metric proposals are drafted, reviewed by owners/data stewards, and approved/published.- Catalog & Lineage: integrate with data catalog (Amundsen/DataHub) and OpenLineage to show upstream tables/transformations and downstream dashboards.- APIs & Interfaces: - SQL macros (e.g., completed_rides(metric_date, cohort_filter)) packaged for use in BI tools. - REST API to list/resolve metrics, return SQL snippets or precomputed values. - Looker/LookML integration or semantic layer adapter so BI tools consume metric names instead of raw SQL. - CLI for metric authors (create, test, publish).Key components & responsibilities:- Metric Registry Service (metadata DB + UI + API)- Metric Compiler (resolves metric to concrete SQL based on grain/filters)- Execution layer: either compute-on-read (SQL compilation at query time) or materialized aggregates (via dbt + scheduled tables) for heavy metrics.- Approval Engine: Git-backed PRs, reviewers, SLA guardrails, version history.- Observability: metric lineage, freshness, test results dashboard.Testing & QA:- Unit tests for metric logic (dbt tests that assert counts, ratio bounds).- Regression tests comparing canonical vs existing ad-hoc query outputs (data diff).- Synthetic data tests for edge cases (nulls, time zones).- CI pipeline: run tests on every metric PR; block merge on failures.- Production shadowing: run canonical metric in parallel with existing queries for N days and compare variances; alert if > threshold.Lineage & Data Quality:- Emit lineage events (OpenLineage) from ETL/dbt and metric compiler to link source tables → metric → dashboards.- Surface quality metrics (freshness, failure rates, drift) in the registry UI.- Owners assigned; automated SLA alerts to owners.Adoption & Migration plan:1. Pilot (4–6 weeks): choose 5 high-value metrics (completed_rides, gross_booking, rider_churn, active_users, avg_trip_value). Work with 2–3 analytics teams to implement in semantic layer and dashboards.2. Shadowing phase (2–4 weeks): canonical metrics computed alongside existing reports; measure divergence and resolve definition differences.3. Migration tooling: provide automatic query scanner that finds ad-hoc SQL referencing source columns and suggests metric-rewrite (SQL snippet) or creates migration PR draft.4. Training & docs: playbooks, lunch-and-learns, office hours, example LookML/SQL patterns.5. Incentives & governance: deprecate unapproved metrics over 3–6 months; require new dashboards to use registry metrics; recognize metric authors.6. Rollout: expand by org, maintain backlog and quarterly reviews; continuously onboard data stewards.Trade-offs:- Compute-on-read: flexible, simpler to maintain, but higher query cost/latency.- Materialized aggregates: faster & cheaper at runtime, more infra & freshness complexity.Choose hybrid: light metrics compute-on-read; heavy/aggregate metrics precompute.Why this works for a BI analyst:- You get consistent definitions to build dashboards reliably, simple SQL macros/LookML measures to call, visibility into lineage and owners to ask questions, and CI-backed tests to trust numbers. The gradual migration & tooling minimize disruption while driving adoption.
MediumTechnical
73 practiced
Explain SCD Type 1, Type 2, and Type 3 with practical examples, and recommend which SCD type to apply for driver dimension attributes such as current_rating (fast-changing), hometown (rarely changes), and preferred_vehicle (can change). Discuss implications for storage growth and query patterns.
Sample Answer
SCD (Slowly Changing Dimension) patterns control how dimensional attribute changes are stored.SCD Type 1 — Overwrite:- Definition: Replace old value with new; no history kept.- Example: Update driver's hometown from "Springfield" to "Riverton" and previous value lost.- Pros/Cons: Simple, minimal storage; loses historical context.SCD Type 2 — Add row (full history):- Definition: Insert new row for each change, track effective_from/effective_to or is_current flag, often with surrogate key.- Example: Driver’s preferred_vehicle changes from "Sedan" to "SUV": create new row with new surrogate key and set previous is_current=false.- Pros/Cons: Preserves full history, supports point-in-time queries; increases storage and slightly more complex joins.SCD Type 3 — Add column (limited history):- Definition: Keep current and one previous value in separate columns (e.g., preferred_vehicle_current, preferred_vehicle_prev).- Example: Store current_rating and previous_rating columns—only last value retained.- Pros/Cons: Limited history, moderate storage; useful for recent-change analytics.Recommendations for driver attributes:- current_rating (fast-changing): Use Type 1 for real-time dashboards where only latest rating matters, or Type 2 if you need rating trend analysis. Prefer Type 2 if analytics require historical performance (longer storage growth).- hometown (rarely changes): Use Type 1 — overwrite is fine for reporting; minimal storage and complexity.- preferred_vehicle (can change): Use Type 2 if you need to analyze vehicle-dependent metrics (e.g., fuel costs over time); use Type 3 only if you care about current vs immediately prior vehicle but not full history.Implications:- Storage growth: Type 2 increases row count proportional to number of changes; Type 3 increases column count slightly; Type 1 minimal.- Query patterns: Type 2 enables point-in-time and historical joins (need date-range joins or is_current filters), may require surrogate keys and more complex ETL. Type 1 simplifies joins (single row per driver). Type 3 suits comparisons between current and last value but complicates when more history is needed.For BI: pick Type 2 for attributes where historical analysis drives decisions (ratings, vehicle impact), Type 1 for static attributes to keep dashboards fast and storage low.
Unlock Full Question Bank
Get access to hundreds of Ride-Hailing Data Modeling & Analytics Requirements interview questions and detailed answers.
