Data scientist backend interview questions

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?

As asked

A dbt incremental model running against Snowflake is producing duplicate rows in the target table after a schema change was made to the source. Walk me through your debugging process and what dbt and Snowflake behaviors could be causing this.

Sample answer outline

The most common cause is that the unique_key in the dbt model config does not match the actual uniqueness constraint of the incoming data, so the MERGE upsert inserts duplicates instead of updating. A secondary cause is a source schema change that changed a column name or type, causing the incremental SELECT to return unexpected nulls or mismatched rows that bypass the merge key. Debugging steps: check QUERY_HISTORY for the generated MERGE SQL, verify the unique_key covers all columns that define uniqueness, run dbt run with --full-refresh to rebuild the table from scratch, and add a source freshness check.

Expect these follow-ups

• When would you choose the delete+insert strategy over merge in a dbt Snowflake model?
• How do you handle dbt incremental models when the source data can arrive late and out of order?

As asked

Your squad owns the pipeline that aggregates raw play events into daily play counts used for artist royalty calculations. Events can be delivered more than once from Kafka (at-least-once). How do you ensure artists are paid correctly and not over- or under-counted when duplicate events arrive?

Sample answer outline

A strong answer identifies the need for event deduplication using a unique event ID (fingerprint of user + track + client timestamp) stored in a Redis set or a database with a unique constraint. The aggregate query then counts distinct event IDs rather than raw rows. The candidate discusses retention policy for the dedup store and what happens when an event arrives 48 hours late due to a mobile client reconnecting. They also address the difference between deduplication at ingestion versus at query time.

Expect these follow-ups

• How do you validate that your deduplication is working correctly without waiting for a royalty audit to catch errors?
• Artists are paid monthly. How does your architecture handle backfilling a bug fix for last month's counts?

As asked

You are building a pipeline that ingests records from an external source, transforms them, and writes results to a PostgreSQL database. The pipeline can fail and restart at any point. How do you ensure each source record is processed exactly once, without duplicating or dropping rows in the output table?

Sample answer outline

A strong answer covers idempotent write operations using upsert with a deterministic deduplication key, checkpointing the last-processed offset in a durable store, and the difference between at-least-once delivery with idempotent consumers versus true exactly-once semantics. The candidate should address the two-phase commit problem when writing to both a message broker and the database, and mention transactional outbox pattern or Kafka transactions as options.

Expect these follow-ups

• What happens if two pipeline instances start simultaneously after a crash?
• How do you test that your idempotency logic actually works under concurrent writes?