Started with information_schema and just ran describe on everything, which felt obvious but they seemed fine with it.
Start by describing a top-down, iterative process: first profile the dataset at a high level (row counts, column types, sample values), then drill into relationships and keys, and finally validate your understanding against domain logic and business rules. Emphasize that you'd document findings as you go and use both automated profiling and manual spot-checks to build confidence.
Pro tip: Mention that you'd look for 'data dictionaries in disguise'—naming conventions, comments in DDL, or even ETL logs—and always validate keys by checking uniqueness and referential integrity before trusting them.
Run summary statistics (row counts, distinct values, null percentages, min/max) for every column to get a quick sense of data distribution and potential issues.
List all tables and columns, then identify candidate primary and foreign keys by checking uniqueness and matching values across tables.
Test candidate keys for uniqueness (e.g., COUNT vs COUNT DISTINCT) and check referential integrity between related tables to confirm relationships.
Examine sample rows and value distributions to infer what each table represents (e.g., fact vs dimension) and how they connect to business entities like hotels, searches, and bookings.
Cross-check findings with domain knowledge or business rules, and document the schema, keys, and assumptions for future reference.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Currency was the one I actually had a decent answer for since I'd dealt with FX normalization before.
Start by outlining a systematic data profiling plan: first understand the dataset structure and key variables, then compute summary statistics and missingness patterns, and finally deep-dive into timezone, locale, and currency inconsistencies. Emphasize how these issues impact downstream analysis and model reliability, and propose concrete validation and remediation steps.
Pro tip: Highlight that timezone and currency issues often stem from inconsistent data capture at the source, so profiling should include tracing data lineage and checking for mixed standards (e.g., local vs. UTC timestamps, currency codes vs. symbols). Demonstrating awareness of Expedia's global user base and multi-currency transactions will show business acumen.
Identify tables, columns, and data types, focusing on fields related to time (booking, search, travel dates), location (user and property), and monetary values (price, fees). Document expected formats and standards.
Compute summary statistics (mean, median, percentiles, histograms) for numerical variables and frequency counts for categoricals. Quantify missingness per column and investigate patterns (e.g., missing currency for certain locales).
Check timestamp fields for consistency (UTC vs. local), detect ambiguous or out-of-range values, and verify that timezone offsets are correctly applied. Cross-validate with user location and property location data.
Examine locale codes (language, country) for validity and consistency with user profiles. For currency, check that amounts are stored with correct currency codes, detect mixed symbols or missing conversions, and verify exchange rates if applicable.
Document identified issues, their prevalence, and potential impact on analysis. Suggest data cleaning steps, standardization rules (e.g., convert all timestamps to UTC, enforce ISO currency codes), and monitoring to prevent future issues.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This is where I should've talked about counting before joining.
Start by explaining that duplicate records and join inflation are common when combining event-level data at different grains, so you first validate uniqueness at each table's natural key and then check row counts before and after each join. Walk through a systematic process: profile keys, use anti-joins and aggregation to detect duplicates, and compare pre/post-join counts to catch inflation, then propose mitigation like deduplication windows or pre-aggregation.
Pro tip: Emphasize that you always validate join cardinality with a quick 'count before vs. after' check and a fan-out ratio per key—this shows you think in terms of data integrity, not just query results. Mentioning that you'd automate these checks as data quality tests in the pipeline demonstrates production maturity.
Identify the natural key and expected grain for each table (e.g., search_id, impression_id, click_id, booking_id) and confirm whether keys are unique or require composite keys. Document expected row counts per key to establish a baseline.
Run GROUP BY key HAVING COUNT(*) > 1 queries and anti-joins (e.g., LEFT JOIN ... WHERE right.key IS NULL) to surface duplicate or orphan records. Check for near-duplicates using fuzzy matching on timestamps or user IDs when exact keys are unreliable.
Compare row counts before and after each join and compute a fan-out ratio (post-join rows / pre-join rows) per key. Flag any ratio > 1 as potential inflation and drill into the offending keys to understand the cause (e.g., multiple impressions per search).
Apply window functions (ROW_NUMBER) to keep the latest or first record per key, or pre-aggregate to the desired grain before joining. Use DISTINCT or GROUP BY at the appropriate level and validate that business metrics (e.g., conversion rate) remain stable.
Codify uniqueness and cardinality checks as data quality tests in the pipeline (e.g., dbt tests or Great Expectations) and set alerts for unexpected fan-out. Periodically review join logic as new data sources are added.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Watermarking came up and I gave a reasonable answer about setting a late arrival window based on observed lag distributions.
Start by describing how you would model the event sequence as a state machine or funnel with defined valid transitions, then explain validation techniques like sequence checks, timestamp ordering, and anomaly detection. For late-arriving data, discuss strategies such as event-time processing with watermarks, session windows, and reconciliation jobs to handle out-of-order events.
Pro tip: Emphasize that late-arriving data is inevitable in distributed systems, so design for it upfront with event-time semantics and idempotent processing rather than treating it as an exception.
Model the funnel as a state machine with allowed transitions (e.g., search → impression → click → booking → cancellation) and specify constraints like timestamps must be non-decreasing within a session.
Use SQL window functions or stream processing to verify that events occur in the correct order per user/session, flagging violations such as a click before an impression or a booking without a prior click.
Process events based on event timestamps rather than arrival time, using watermarks and allowed lateness in stream processing (e.g., Apache Flink, Spark Structured Streaming) to accommodate out-of-order events.
Run periodic batch reconciliation jobs to reprocess late events and update downstream aggregates, ensuring idempotency to avoid double-counting.
Set up dashboards and alerts for sequence anomalies and late data volumes, enabling quick detection and remediation of data quality issues.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The GMV vs contribution margin distinction is something I've explained before so that part was fine.
Start by clarifying definitions and assumptions for each metric, then walk through the computation logic step by step, emphasizing data sources, joins, and edge cases. Use a structured framework to show how you'd implement each metric in SQL or code, and highlight potential pitfalls like time zone handling and denominator choices.
Pro tip: Demonstrate product intuition by explaining why these metrics matter for Expedia—e.g., bookings per search as a conversion proxy, margin as a profitability driver, and GMV vs. contribution margin as top-line vs. bottom-line views. Also mention how you'd validate results with sanity checks and stakeholder alignment.
Confirm what 'bookings per search' means (e.g., unique bookings divided by unique searches), the 7-day window (rolling vs. fixed, time zone), and how margin and GMV are defined (e.g., GMV includes taxes/fees, margin excludes them).
Specify tables for searches, bookings, pricing, and costs; describe how to join them on user/session and date keys, and handle missing or duplicate records.
Aggregate searches and bookings within the 7-day window per user or session, then divide total bookings by total searches, ensuring consistent denominators and handling zero-search cases.
For each booking, calculate margin as price minus cost, and GMV as total booking value (including taxes/fees). Sum these over the window and compare to show the difference.
Run sanity checks (e.g., margin ≤ GMV, no negative values), compare with historical trends, and explain business implications of each metric.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Pretty standard territory if you've used any testing framework for data pipelines.
Start by framing data quality tests and contracts as complementary layers: contracts define expectations at the pipeline boundary, while tests enforce them continuously. Then walk through each dimension (nullability, uniqueness, referential integrity, numeric range) with concrete examples of how you'd implement them in a typical Expedia-style pipeline, emphasizing trade-offs like test coverage vs. performance and where to place checks (ingestion vs. transformation).
Pro tip: Tie each check to a business impact—e.g., null hotel IDs break personalization, duplicate bookings inflate revenue metrics—and mention how you'd prioritize tests based on data criticality and failure cost. This shows you think like a product-minded data scientist, not just a tester.
Specify schema, semantics, and quality expectations (e.g., nullability, uniqueness, ranges) for each dataset at the producer-consumer boundary, using a machine-readable format like YAML or JSON Schema. Include ownership and SLAs to make expectations enforceable.
Decide where each test runs: at ingestion (raw data), after transformation (cleaned data), and before serving (final tables). For example, referential integrity checks belong after joins, while nullability checks can run early to fail fast.
For nullability, assert non-null on critical fields; for uniqueness, use primary key or composite key checks; for referential integrity, validate foreign keys against dimension tables; for numeric range, enforce min/max or statistical bounds (e.g., price > 0, rating between 1-5).
Run tests as part of the pipeline orchestration (e.g., Airflow, dbt tests) and block deployments or alerts on failures. Use a framework like Great Expectations or dbt tests to codify checks and generate reports.
Track test pass rates and data quality metrics over time, alert on anomalies, and review failures to refine contracts and tests. Treat data quality as an ongoing process, not a one-time setup.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This was the most interesting part of the whole conversation.
Structure your answer by first categorizing each pitfall (multi-room bookings, partial cancellations, rebookings, FX timing, guest counts) and explaining why it's a data quality issue. Then, for each, describe a concrete detection method such as reconciliation checks, outlier analysis, or validation rules. Emphasize how you would operationalize these checks in a data pipeline to catch issues early.
Pro tip: Tie each pitfall to a real business impact (e.g., revenue leakage, inaccurate demand forecasting) and mention that you'd validate detection logic with domain experts and A/B tests where possible.
List each pitfall and assess its frequency and business impact to prioritize detection efforts.
For each pitfall, specify what correct data should look like (e.g., room count equals sum of individual room records, cancellation amounts match booking amounts).
Create validation rules, reconciliation checks, and anomaly detection metrics (e.g., variance thresholds, distribution shifts) for each pitfall.
Integrate checks into ETL pipelines with automated alerts and dashboards to flag issues in real-time or near-real-time.
Regularly review false positives/negatives, refine rules, and perform root cause analysis on detected anomalies to prevent recurrence.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.