Top 30 Data Engineering Interview Questions and Answers

30 questions. Pick an answer, or open “Show the answer” to read it.

Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.

1. Question 1 of 30

   To efficiently manage large datasets and avoid duplicating storage across versions, data versioning systems primarily utilize which technique?

   • aDelegating all data storage and deduplication tasks to the underlying file system or cloud provider.
   • bStoring a full, independent copy of the entire dataset for every new version.
   • cStoring each unique data file once and using lightweight pointers to represent different dataset versions.
   • dCompressing all data files into a single, highly optimized archive before each commit.
   Show the answer

   Answer: c · Storing each unique data file once and using lightweight pointers to represent different dataset versions.

   Data versioning systems achieve efficiency by storing only one copy of each unique data file and using lightweight pointers to reference these files across different dataset versions, avoiding full duplication. Option B describes the inefficient approach that data versioning aims to solve.

   Read the full bite: Data Versioning: Git for Your Datasets

2. Question 2 of 30

   Your team wants to measure six-month LTV impact of a pricing change. Which architecture avoids survivorship bias and cross-experiment collision traps common in long-term holdbacks?

   • aAssign users with a deterministic hash on a durable account ID, check a dedicated holdback flag before any regular experiment flags, seal metrics only after the observation window plus a bounded grace period, and use clustered standard errors with an intent-to-treat model.
   • bBucket users deterministically by device ID, compute long-term lift incrementally with weekly batch jobs, and limit the analysis set to users who were exposed to the new pricing UI at least once.
   • cRe-randomize remaining active users monthly to keep groups balanced, store group membership in browser cookies, and compare mean revenue among users still active at month six using a standard t-test.
   • dUse the standard A/B test framework with weekly re-randomization of unresponsive users, evaluate the holdback flag after regular experiment flags to prevent override, and apply standard t-tests on all originally enrolled users.
   Show the answer

   Answer: a · Assign users with a deterministic hash on a durable account ID, check a dedicated holdback flag before any regular experiment flags, seal metrics only after the observation window plus a bounded grace period, and use clustered standard errors with an intent-to-treat model.

   Option A is correct because it pins users indefinitely with durable identity storage, isolates the holdback from newer experiments via namespace ordering, seals metrics only after the full window closes, and uses intent-to-treat with clustered errors to avoid survivorship bias. Option B is tempting because deterministic bucketing is correct, but device IDs are not durable across reinstalls, incremental computation violates the sealed observation window, and analyzing only exposed users creates survivorship bias by dropping unexposed bucketed users.

   Read the full bite: How would you architect long-term holdback experiment groups?

3. Question 3 of 30

   When writing a query that groups by department and filters on COUNT(*) > 10, why must the predicate be in HAVING rather than WHERE?

   • aHAVING and WHERE are logically identical, but HAVING is the conventional clause for grouped results
   • bWHERE is evaluated before GROUP BY, so the aggregate count does not yet exist
   • cHAVING is more efficient than WHERE for aggregate filters because it processes fewer rows
   • dWHERE can accept aggregate functions only if the identical expression also appears in the SELECT list
   Show the answer

   Answer: b · WHERE is evaluated before GROUP BY, so the aggregate count does not yet exist

   WHERE filters individual rows before grouping and aggregation occur, so aggregate values like COUNT(*) have not been computed yet and do not exist at that stage. Option D is wrong because repeating the aggregate expression in WHERE does not help; the aggregate still does not exist when WHERE is evaluated.

   Read the full bite: What is the difference between WHERE and HAVING in SQL?

4. Question 4 of 30

   Why should batch product metadata and streaming click events typically use different physical storage formats?

   • aColumnar formats should be used for both to maximize compression and query performance across all workloads
   • bThe choice is purely based on cost, so the cheapest format should be selected regardless of data velocity or access pattern
   • cRow-oriented formats minimize write overhead for high-velocity events, while columnar formats improve projection and compression for batch analytics
   • dRow-oriented formats are preferred for both because they simplify schema evolution and reduce system complexity
   Show the answer

   Answer: c · Row-oriented formats minimize write overhead for high-velocity events, while columnar formats improve projection and compression for batch analytics

   Row-oriented formats minimize write overhead for streaming ingestion while columnar formats allow efficient projection and compression for batch training data. Choosing columnar for both is tempting but adds unnecessary write overhead to high-velocity events.

   Read the full bite: Design ingestion for clickstream and batch product metadata

5. Question 5 of 30

   When handling a GDPR erasure request, what is the correct way to update aggregated analytics dashboards derived from the user's data?

   • aMask the user's identity in the dashboard UI while preserving their data in the aggregates
   • bSubtract the user's contributions from each displayed aggregate and decrement related counters
   • cIssue a hard delete in the primary database and rely on the next scheduled ETL run to update figures
   • dReprocess the underlying data pipeline to exclude the user and rebuild all affected metrics
   Show the answer

   Answer: d · Reprocess the underlying data pipeline to exclude the user and rebuild all affected metrics

   The card states that aggregated dashboards must be fixed by reprocessing the underlying pipeline or using differential privacy, because simple subtraction fails for complex metrics like averages and funnels. Option B represents the common misconception of manually adjusting aggregates, which does not reliably remove the user's influence from derived metrics.

   Read the full bite: How do you fulfill a GDPR erasure request across data stores?

6. Question 6 of 30

   Which of the following describes a proper ETL implementation?

   • aA system operator manually extracts data, reformats it, and loads it into a report each week.
   • bA scheduled process automatically extracts data from several sources, transforms it, and loads it into a central database.
   • cA script automatically copies raw backup files to a secondary server every night without modifying them.
   • dA database administrator migrates a single table to a new system as a one-time task.
   Show the answer

   Answer: b · A scheduled process automatically extracts data from several sources, transforms it, and loads it into a central database.

   ETL is intended as an automated, three-phase pipeline run on recurring schedules, which option B illustrates. Option A represents the manual-run footgun the card warns against, while B omits the required transformation phase and D describes a one-time task lacking recurring automation.

   Read the full bite: ETL: Extract, Transform, Load

7. Question 7 of 30

   When building a time-decay attribution pipeline in a cloud warehouse, which practice distinguishes a model that measures true incremental impact from one that only captures correlation?

   • aApplying the half-life decay function directly to raw event streams before identity stitching
   • bSessionizing clickstream data with SQL window functions immediately upon ingestion
   • cComparing the model's fractional credit allocations against holdout experiments that test channel lift
   • dUsing deterministic and probabilistic matching to unify anonymous and authenticated user IDs
   Show the answer

   Answer: c · Comparing the model's fractional credit allocations against holdout experiments that test channel lift

   Holdout experiments validate that attributed credit reflects causal incrementality rather than mere correlation. Identity stitching is essential for resolving users across touchpoints, yet it does not prove that a specific channel caused the conversion.

   Read the full bite: Describe the architecture for multi-touch attribution with time-decay

8. Question 8 of 30

   Why is it architecturally necessary to keep batch historical features in a KV store while maintaining session-level streaming features in a separate in-memory cache?

   • aThe batch path produces terabyte-scale pre-aggregated profiles that are updated infrequently, while the streaming path handles high-velocity session events with natural TTL decay; a single storage system cannot optimize for both access patterns under the 50ms SLA.
   • bKV stores provide strong consistency guarantees required for monetary billing features, whereas in-memory caches only support at-least-once semantics.
   • cMerging at the serving layer is only possible when the two stores use identical serialization formats such as Flatbuffers; mixing storage types would require expensive transformation logic.
   • dIn-memory caches enforce schema-on-write, preventing training-serving skew, whereas KV stores use schema-on-read for backward compatibility with historical feature versions.
   Show the answer

   Answer: a · The batch path produces terabyte-scale pre-aggregated profiles that are updated infrequently, while the streaming path handles high-velocity session events with natural TTL decay; a single storage system cannot optimize for both access patterns under the 50ms SLA.

   The card states that batch and streaming data have fundamentally different latency, volume, and freshness requirements, so one storage system cannot handle both optimally without breaking the sub-50ms SLA. Distractor A sounds plausible because the follow-ups mention exactly-once semantics for billing, but the card never cites billing consistency as the reason for the dual-store split.

   Read the full bite: Design a sub-50ms real-time bidding feature pipeline

9. Question 9 of 30

   When converting a large list of flat dictionaries to a pandas DataFrame, which method minimizes Python-level overhead by leveraging vectorized C-backed construction?

   • aConvert the list of dicts into a list of lists first, then pass that to pd.DataFrame
   • bUse DataFrame.from_dict with orient='index' and then transpose the result
   • cPass the list of dicts directly to pd.DataFrame so the constructor builds the block manager in one pass
   • dIterate through the list and build the DataFrame incrementally using pd.concat for each dictionary
   Show the answer

   Answer: c · Pass the list of dicts directly to pd.DataFrame so the constructor builds the block manager in one pass

   Passing the list directly to pd.DataFrame leverages a C-backed constructor that builds the block manager in a single vectorized pass. Option D is tempting but wrong because iteratively using pd.concat creates a new DataFrame each iteration, resulting in quadratic time complexity from repeated memory copies.

   Read the full bite: Most efficient way to convert list of dicts to pandas DataFrame

10. Question 10 of 30

    What is the main architectural reason to avoid using an incrementing user property to track the 3-invite aha moment?

    • aStoring the count in a user property is sufficient as long as the analytics SDK automatically timestamps each update.
    • bEvent-time windowing eliminates the need to handle late-arriving events because windows emit only after the full seven days pass.
    • cAn incrementing user property would conflate lifetime invites with the first-week window and cannot naturally enforce a fixed seven-day expiration or reversal.
    • dUser properties cannot capture event metadata like recipient_id or invite_channel, so event-level tracking is required.
    Show the answer

    Answer: c · An incrementing user property would conflate lifetime invites with the first-week window and cannot naturally enforce a fixed seven-day expiration or reversal.

    User properties persist until overwritten, so they cannot enforce a fixed seven-day expiration and conflate lifetime behavior with first-week behavior. Distractor D is tempting because late arrivals are a real concern, but windowing does not remove the need to handle them; pipelines must explicitly reconcile late data.

    Read the full bite: How would you instrument events and query a 3-invite aha moment?

11. Question 11 of 30

    When backfilling a 90-day feature for millions of users, which strategy best protects production while ensuring the backfilled data matches live logic?

    • aReplay all historical events through the live ingestion topic with significantly scaled-up consumers to let the streaming pipeline backfill naturally.
    • bWrite a dedicated PySpark script that duplicates the live transformation logic and run it directly against the production feature store during off-peak hours.
    • cExecute the existing pipeline as a single unbounded query on the production cluster, writing results straight to the live serving table.
    • dReuse the live feature pipeline on a separate batch cluster, process bounded daily partitions, stage the results, validate them, and promote atomically.
    Show the answer

    Answer: d · Reuse the live feature pipeline on a separate batch cluster, process bounded daily partitions, stage the results, validate them, and promote atomically.

    Reusing the live pipeline on isolated compute with bounded partitions prevents both resource contention and logic drift, while staging and validation ensure correctness before atomic promotion. Option B is tempting because off-peak hours feel safer, but duplicating logic creates silent drift and direct production access can still impact serving.

    Read the full bite: Backfill a complex feature for millions of users without impacting production

12. Question 12 of 30

    To guarantee exact reproducibility of a historical training dataset after both schema and data have evolved in the feature store, what should a pipeline pin?

    • aA mutable latest tag that resolves dynamically at query time
    • bOnly the schema version of the feature group at training time
    • cOnly the commit timestamp or snapshot ID of the data
    • dBoth the schema version and the data timestamp or commit ID
    Show the answer

    Answer: d · Both the schema version and the data timestamp or commit ID

    Reproducibility requires pinning both the schema version and the data snapshot because schema evolution and data history are independent dimensions; pinning only the schema version would retrieve current data rather than the exact historical state used for training.

    Read the full bite: How would you implement versioning for feature definitions in a feature store?

13. Question 13 of 30

    You need to trace how a deprecated field in an upstream microservice affects downstream dashboards without slowing production APIs. Which strategy best achieves this?

    • aAdd synchronous instrumentation to every API call to log dataset identifiers and schema versions.
    • bRely on the data warehouse query logs to reconstruct data movement after the fact.
    • cRequire engineers to maintain a Confluence table mapping each microservice schema to downstream consumers.
    • dEmit metadata asynchronously at service boundaries and store column-level relationships in a central graph catalog.
    Show the answer

    Answer: d · Emit metadata asynchronously at service boundaries and store column-level relationships in a central graph catalog.

    Capturing metadata asynchronously at service boundaries records provenance where data originates without adding latency, while a graph catalog enables fast column-level impact analysis. Option A is tempting but wrong because synchronous instrumentation would introduce crippling latency to production APIs.

    Read the full bite: How would you implement data lineage for microservices analytics?

14. Question 14 of 30

    Which approach best prevents broken dashboards and silent data loss when a source database adds nullable columns in a CDC pipeline?

    • aUse a schema registry with compatibility checks, land raw events flexibly in a bronze layer, and enforce schemas only when casting to silver tables
    • bSerialize events as schemaless JSON and rely on downstream consumers to handle type conversions dynamically
    • cReject all schema changes by default and require explicit approval for every new field to prevent consumer breakage
    • dPause the pipeline and run coordinated ALTER TABLE statements across all downstream databases before resuming ingestion
    Show the answer

    Answer: a · Use a schema registry with compatibility checks, land raw events flexibly in a bronze layer, and enforce schemas only when casting to silver tables

    Decoupling ingestion from serving lets the bronze layer absorb drift without breaking historical data, while registry compatibility rules catch breaking changes early. Pausing for coordinated ALTER TABLES creates downtime and does not scale, making it the most tempting but dangerous distractor.

    Read the full bite: Design a CDC pipeline that handles schema evolution gracefully

15. Question 15 of 30

    A transaction event at 08:50 must be joined to a user profile table where the user upgraded to premium at 09:00. Which join behavior preserves point-in-time correctness for the training row?

    • aMatch the most recent profile row with a timestamp less than or equal to 08:50.
    • bRecover the profile table version committed closest to 08:50 using transaction metadata.
    • cMatch the profile row whose timestamp exactly equals 08:50.
    • dMatch the latest profile row for that user without considering event time.
    Show the answer

    Answer: a · Match the most recent profile row with a timestamp less than or equal to 08:50.

    An AS OF join retrieves the latest dimension record known at or before the event timestamp, so the 08:50 event correctly sees the pre-upgrade standard tier. Option D is the most tempting distractor because joining on user_id alone and taking the latest record silently leaks future state into the training set.

    Read the full bite: Design system ensuring point-in-time correctness for training data joins

16. Question 16 of 30

    A platform team advocates for a declarative feature platform to reduce boilerplate. Which risk best illustrates why this choice could fail without the right organizational maturity?

    • aFeature engineers will need to manually wire dependencies and manage idempotency for every pipeline, increasing toil as headcount grows.
    • bThe team must own the opinionated abstractions for checkpointing, schema evolution, and exactly-once semantics, requiring deep infrastructure expertise.
    • cDeclarative DSLs limit all pipelines to simple transformations, preventing the implementation of custom business logic.
    • dEnd users lose the ability to mix spot instances and targeted instance types, making large-scale batch jobs prohibitively expensive.
    Show the answer

    Answer: b · The team must own the opinionated abstractions for checkpointing, schema evolution, and exactly-once semantics, requiring deep infrastructure expertise.

    Declarative platforms centralize ownership of checkpoints, schema evolution, and exactly-once semantics within the platform team, so adopting one without that infrastructure maturity fails; option A describes imperative platforms, and D wrongly assumes declarative means no-code.

    Read the full bite: Argue for declarative or imperative feature platforms with trade-offs

17. Question 17 of 30

    Which client-side strategy best maximizes throughput for a 100 req/min API without triggering excessive 429 errors?

    • aLimit concurrency to four workers but send requests at full speed, using only reactive exponential backoff and ignoring rate-limit headers.
    • bBound concurrency to a small worker pool, proactively pace requests using rate-limit headers, and apply exponential backoff with jitter on 429s.
    • cKeep an unbounded number of workers and rely solely on reactive exponential backoff after receiving 429 responses.
    • dInsert a fixed one-second delay after every request and retry 429s with linear backoff while ignoring rate-limit headers.
    Show the answer

    Answer: b · Bound concurrency to a small worker pool, proactively pace requests using rate-limit headers, and apply exponential backoff with jitter on 429s.

    The correct answer combines proactive throttling, header-aware dynamic pacing, bounded concurrency, and resilient retries as described in the card. Option A is tempting because it limits concurrency, but it still creates burst traffic and ignores headers, relying on the server to punish the client rather than preventing 429s proactively.

    Read the full bite: Design a rate-limited REST API data collection script

18. Question 18 of 30

    Why might a dashboard query for unique visitor count still trigger an expensive raw event scan even when pre-aggregated rollups exist?

    • aBecause OLAP engines charge less for raw scans than rollup reads when many users query simultaneously
    • bBecause pre-aggregated tables automatically exclude high-cardinality dimensions like user IDs to control storage
    • cBecause distinct counts are non-additive and cannot be safely reused across dimension slices outside the rollup definition
    • dBecause the pre-aggregation refresh_key is too infrequent, forcing the dashboard to bypass stale rollups
    Show the answer

    Answer: c · Because distinct counts are non-additive and cannot be safely reused across dimension slices outside the rollup definition

    Non-additive measures such as distinct counts require carefully structured rollups and cannot be combined or re-sliced arbitrarily, causing cache misses when the query dimensions differ. Distractor B confuses staleness with structural mismatch; a stale rollup returns outdated results rather than forcing a fallback to raw scans.

    Read the full bite: Trade-offs between pre-aggregated and raw event data for dashboards

19. Question 19 of 30

    You must add a new attribution field to existing signup events consumed by BI tools and ML pipelines. Which strategy best prevents downstream breakage?

    • aAlter the raw event table to drop deprecated columns, add the new field as non-nullable, and rebuild the gold table from scratch
    • bRegister a backward-compatible schema version with the new field as nullable, land events in a raw layer that tolerates drift, then propagate to modeled tables after validation
    • cRewrite all historical Parquet files to include the new field with default values, then update the producer to emit the new schema version
    • dAdd the field as required in the schema registry, backfill the raw table over a weekend, and notify dashboard owners after deployment
    Show the answer

    Answer: b · Register a backward-compatible schema version with the new field as nullable, land events in a raw layer that tolerates drift, then propagate to modeled tables after validation

    Option B follows backward-compatible serialization, protects the immutable raw layer, and validates changes before they reach BI. Option C is tempting because rewriting history feels clean, but it destroys the immutable event log and incurs heavy reprocessing costs, while C and D immediately break old readers.

    Read the full bite: How do you manage event schema evolution without breaking reports?

20. Question 20 of 30

    In which situation would a formal ETL pipeline be considered unnecessary overhead?

    • aWhen raw input data must be modified before it can be placed into an output container
    • bWhen a system operator needs to perform a genuine one-time data transfer that requires no transformation
    • cWhen multiple data jobs need to be aggregated into a batch that runs automatically
    • dWhen data must be gathered from several sources, transformed, and loaded into a single container on a recurring schedule
    Show the answer

    Answer: b · When a system operator needs to perform a genuine one-time data transfer that requires no transformation

    The card states that ETL adds unnecessary overhead for genuine one-time transfers that a system operator can handle manually without automation. Option D mirrors the canonical example where ETL is the ideal choice.

    Read the full bite: ETL: The Three-Phase Data Pipeline

21. Question 21 of 30

    When scraping a dynamic website, what is the primary reason to prioritize finding its hidden API calls over using a headless browser?

    • aTo efficiently retrieve data that is loaded by JavaScript and not present in the initial HTML response.
    • bTo accurately simulate complex user interactions, such as clicking buttons or scrolling infinitely, to reveal content.
    • cTo effectively bypass sophisticated anti-bot measures that detect automated browser activity.
    • dTo capture the visual layout and styling of the webpage as seen by a user.
    Show the answer

    Answer: a · To efficiently retrieve data that is loaded by JavaScript and not present in the initial HTML response.

    The card states that finding API calls is for when data is missing from the initial HTML but visible in the browser, offering a faster and more reliable alternative. Headless browsers are generally used for complex interactions or when the API is too difficult to replicate, making them a 'last resort'.

    Read the full bite: Scraping Dynamic Sites: Find the API, Not Just Render

22. Question 22 of 30

    Which design best balances user experience, data quality, and scalability for tracking share clicks?

    • aBatch structured events with hashed IDs using exponential backoff retry, then land in partitioned columnar storage
    • bInclude raw user emails in the payload, send via asynchronous beacon, and store in a date-partitioned BigQuery table
    • cUse synchronous blocking POST requests per click with structured JSON and store in an unpartitioned relational table
    • dSend structured payloads immediately without retry and rely solely on server ingestion timestamps for event ordering
    Show the answer

    Answer: a · Batch structured events with hashed IDs using exponential backoff retry, then land in partitioned columnar storage

    Option A is correct because it combines non-blocking batching with resilient retry, privacy-preserving identifiers, and scalable partitioned storage. Option B is tempting because beacon delivery and partitioned tables are valid choices, but including raw emails violates privacy guardrails and risks PII exposure.

    Read the full bite: Describe the end-to-end data flow for tracking a 'Share' button click

23. Question 23 of 30

    What is the defining architectural difference that makes ELT attractive with modern cloud data warehouses?

    • aELT loads raw data first and runs transformations using the warehouse's own scalable compute, keeping raw data for reprocessing
    • bELT simply reverses the spelling of ETL with no real change
    • cELT always transforms data before it ever touches the warehouse
    • dELT removes the need to extract data from sources
    Show the answer

    Answer: a · ELT loads raw data first and runs transformations using the warehouse's own scalable compute, keeping raw data for reprocessing

    ELT loads raw data then transforms in place using the warehouse's elastic compute, preserving raw data for reprocessing. The other options either trivialize the difference, drop extraction, or describe ETL's pre-load transform.

    Read the full bite: What is the difference between ETL and ELT?

24. Question 24 of 30

    In a valid onboarding A/B test pipeline, why is it critical to log an exposure event before the variant renders?

    • aTo deterministically bucket users by hashing their user_id with the experiment key
    • bTo validate the variant assignment against server-side state before display
    • cTo create an intent-to-treat cohort and avoid survivorship bias from only tracking completers
    • dTo persist the assigned variant across logout and cross-device sessions
    Show the answer

    Answer: c · To create an intent-to-treat cohort and avoid survivorship bias from only tracking completers

    Logging exposure before rendering establishes an intent-to-treat cohort and prevents survivorship bias from only tracking users who completed the flow. Option A confuses the exposure event with deterministic bucketing, which is a separate assignment step that occurs earlier in the pipeline.

    Read the full bite: How do you technically implement an A/B test for onboarding flows?

25. Question 25 of 30

    When building data infrastructure to measure causal viral growth, which layer must be hardened first before downstream feature stores or model serving can be trusted?

    • aA streaming ingestion layer that delivers invite events with sub-hour latency
    • bAn identity resolution layer that links inviter and invitee across devices and sessions
    • cA feature store that serves network-state features like local graph density
    • dReal-time monitors that detect feature drift in the viral coefficient
    Show the answer

    Answer: b · An identity resolution layer that links inviter and invitee across devices and sessions

    The card emphasizes that without clean attribution between inviter and invitee, any k-factor model is built on garbage data, and a broken join causes the feature store to serve incorrect network topology. Fast streaming ingestion is valuable but cannot fix a broken identity link, making attribution the prerequisite.

    Read the full bite: What data pipelines and infrastructure feed a viral user acquisition model?

26. Question 26 of 30

    An interviewer asks you to trace a purchase click from the frontend to a BI dashboard. Which description best demonstrates you understand analytics as a supply chain?

    • aThe frontend sends the event to an analytics endpoint, which immediately updates the BI dashboard with the raw JSON payload
    • bThe frontend instruments the event, which is then routed, ingested into the warehouse, transformed into a model, and consumed by the BI tool
    • cThe React component fires an event, the backend API validates it, and the production database stores it for the BI tool to read directly
    • dThe user action is captured in application logs, picked up by Datadog, and visualized on an operations dashboard
    Show the answer

    Answer: b · The frontend instruments the event, which is then routed, ingested into the warehouse, transformed into a model, and consumed by the BI tool

    Option B correctly traces the full supply chain through instrumentation, routing, ingestion, transformation, and consumption. Option C is tempting because it describes a normal application request flow, but it wrongly stops at the production database and omits the analytics-specific ingestion and modeling layers.

    Read the full bite: Describe tracking a user event end-to-end from frontend to BI tool

27. Question 27 of 30

    Which statement best describes the separation of concerns between dbt, traditional Python ETL, and Airflow?

    • adbt is essentially a SQL scheduler that runs queries on external compute and lacks built-in testing or lineage.
    • bdbt replaces Airflow by natively orchestrating general data pipelines including file transfers and API calls.
    • cdbt executes declarative SQL transformations inside the warehouse, while Airflow schedules tasks and Python ETL relies on external compute.
    • dPython ETL and dbt both transform data inside the warehouse, but Python ETL is preferred for complex logic because it offers more control than SQL.
    Show the answer

    Answer: c · dbt executes declarative SQL transformations inside the warehouse, while Airflow schedules tasks and Python ETL relies on external compute.

    dbt runs declarative SQL directly in the warehouse with native lineage, testing, and documentation, whereas Python ETL uses external compute and Airflow only orchestrates tasks without transformation semantics. Option B is wrong because dbt complements rather than replaces Airflow, as it does not orchestrate general workflows like file transfers or API calls.

    Read the full bite: Describe dbt's role and how it differs from traditional ETL

28. Question 28 of 30

    Why do analytics SDKs batch events and send them to an ingestion endpoint rather than writing each click directly to the production database?

    • aIt is the only way to guarantee consistent variant assignment
    • bBatching plus an asynchronous pipeline scales better and decouples analytics from the app's critical path
    • cDirect writes would reveal the user's variant assignment to other users
    • dDatabases cannot store click events without a streaming buffer
    Show the answer

    Answer: b · Batching plus an asynchronous pipeline scales better and decouples analytics from the app's critical path

    Buffering and asynchronous ingestion absorb traffic spikes and keep analytics from slowing or coupling to the app's request path. Variant assignment is handled separately by deterministic hashing, not by the write strategy.

    Read the full bite: Trace an event from click to analysis

29. Question 29 of 30

    An experiment pipeline ingests one billion events daily across variant, country, device, and high-cardinality user ID. Which design best satisfies sub-second dashboard latency without causing storage explosion?

    • aBuild a complete OLAP cube materializing every combination of variant, country, device, and individual user ID
    • bRun a nightly batch job to pre-aggregate all metrics by variant and country, then serve dashboards from the resulting daily tables
    • cUse a stream processor to emit partial rollups by variant, country, and device into a real-time OLAP engine while retaining raw events and using HLL sketches for unique user counts
    • dCache all experiment queries in Redis and compute aggregations directly over the raw event store on each cache miss
    Show the answer

    Answer: c · Use a stream processor to emit partial rollups by variant, country, and device into a real-time OLAP engine while retaining raw events and using HLL sketches for unique user counts

    Partial rollups on high-traffic dimensions with HLL sketches deliver sub-second latency without storage explosion, whereas materializing every combination including user ID creates billions of cells and exhausts resources.

    Read the full bite: Design a pre-aggregation architecture for low-latency experiment results

30. Question 30 of 30

    When is a data lake the better choice over a traditional data warehouse?

    • aWhen data volume is tiny and fully structured
    • bWhen sources are diverse or unstructured and you want schema-on-read flexibility
    • cWhen you need the fastest governed SQL over fixed business metrics
    • dWhen you want to enforce a strict schema before any data lands
    Show the answer

    Answer: b · When sources are diverse or unstructured and you want schema-on-read flexibility

    Lakes excel at storing diverse, raw, large-volume data cheaply with schema applied at read time. Fast governed SQL over fixed metrics and strict schema-on-write are exactly what a warehouse is built for.

    Read the full bite: Data lake versus data warehouse