Source freshness checks in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of freshness check query

Given the following SQL query used in a dbt source freshness check, what will be the output if the source table's max timestamp is '2024-06-01 10:00:00' and the current timestamp is '2024-06-01 12:00:00'?

dbt

SELECT
  MAX(updated_at) AS last_loaded,
  CURRENT_TIMESTAMP AS check_time,
  TIMESTAMPDIFF(MINUTE, MAX(updated_at), CURRENT_TIMESTAMP) AS minutes_behind
FROM source_schema.source_table;

A SyntaxError: TIMESTAMPDIFF function not found

B {"last_loaded": "2024-06-01 10:00:00", "check_time": "2024-06-01 12:00:00", "minutes_behind": 120}

C {"last_loaded": null, "check_time": "2024-06-01 12:00:00", "minutes_behind": null}

D {"last_loaded": "2024-06-01 12:00:00", "check_time": "2024-06-01 10:00:00", "minutes_behind": -120}

💡 Hint

Think about how TIMESTAMPDIFF calculates the difference between two timestamps.

The query calculates the maximum updated_at timestamp from the source table and compares it to the current timestamp. Since the max updated_at is 2 hours before current time, minutes_behind is 120.

🧠 Conceptual

intermediate

Understanding freshness thresholds

In dbt source freshness checks, what does the 'warn_after' threshold represent?

A The maximum allowed time difference before a warning is triggered

B The exact time when the source data was last updated

C The minimum time difference required to mark data as fresh

D The time interval to refresh the source table automatically

💡 Hint

Think about what triggers a warning in freshness checks.

'warn_after' sets the time limit after which dbt will warn that the source data might be stale.

🔧 Debug

advanced

Identify the error in freshness SQL

This SQL snippet is used in a dbt freshness check but raises an error. What is the cause? SELECT MAX(last_updated) AS last_loaded, CURRENT_TIMESTAMP AS check_time, DATEDIFF(MINUTES, MAX(last_updated), CURRENT_TIMESTAMP) AS minutes_behind FROM source_schema.source_table;

A MAX function cannot be used on timestamp columns

B CURRENT_TIMESTAMP is not valid SQL syntax

C DATEDIFF function does not accept 'MINUTES' as a unit

D Missing GROUP BY clause causes error

💡 Hint

Check the syntax and parameters of the DATEDIFF function.

DATEDIFF usually accepts 'DAY' or similar units, but 'MINUTES' is not valid in many SQL dialects. TIMESTAMPDIFF is preferred for minutes.

❓ data_output

advanced

Result of freshness check with null timestamps

What will be the output of this freshness check query if the source table has no rows (empty)? SELECT MAX(updated_at) AS last_loaded, CURRENT_TIMESTAMP AS check_time, TIMESTAMPDIFF(MINUTE, MAX(updated_at), CURRENT_TIMESTAMP) AS minutes_behind FROM source_schema.source_table;

A RuntimeError: Cannot compute TIMESTAMPDIFF with null values

B {"last_loaded": null, "check_time": "2024-06-01 12:00:00", "minutes_behind": 0}

C {"last_loaded": "1970-01-01 00:00:00", "check_time": "2024-06-01 12:00:00", "minutes_behind": 0}

D {"last_loaded": null, "check_time": "2024-06-01 12:00:00", "minutes_behind": null}

💡 Hint

Consider how aggregate functions behave on empty tables.

MAX on an empty column returns null, so minutes_behind calculation also results in null.

🚀 Application

expert

Designing a freshness check for multiple sources

You have three source tables with different update frequencies. You want to configure dbt freshness checks with these thresholds: - source A: warn if data is older than 30 minutes, error if older than 1 hour - source B: warn if data is older than 2 hours, error if older than 4 hours - source C: warn if data is older than 1 day, error if older than 2 days Which YAML configuration snippet correctly sets these freshness checks?

dbt

version: 2
sources:
  - name: source_a
    freshness:
      warn_after: {count: 30, period: minute}
      error_after: {count: 1, period: hour}
  - name: source_b
    freshness:
      warn_after: {count: 2, period: hour}
      error_after: {count: 4, period: hour}
  - name: source_c
    freshness:
      warn_after: {count: 1, period: day}
      error_after: {count: 2, period: day}

A

version: 2
sources:
  - name: source_a
    freshness:
      warn_after: {count: 30, period: minute}
      error_after: {count: 1, period: hour}
  - name: source_b
    freshness:
      warn_after: {count: 2, period: hour}
      error_after: {count: 4, period: hour}
  - name: source_c
    freshness:
      warn_after: {count: 1, period: day}
      error_after: {count: 2, period: day}

B

version: 2
sources:
  - name: source_a
    freshness:
      warn_after: {count: 1, period: hour}
      error_after: {count: 30, period: minute}
  - name: source_b
    freshness:
      warn_after: {count: 4, period: hour}
      error_after: {count: 2, period: hour}
  - name: source_c
    freshness:
      warn_after: {count: 2, period: day}
      error_after: {count: 1, period: day}

C

version: 2
sources:
  - name: source_a
    freshness:
      warn_after: {count: 30, period: hour}
      error_after: {count: 1, period: minute}
  - name: source_b
    freshness:
      warn_after: {count: 2, period: minute}
      error_after: {count: 4, period: minute}
  - name: source_c
    freshness:
      warn_after: {count: 1, period: hour}
      error_after: {count: 2, period: day}

D

version: 2
sources:
  - name: source_a
    freshness:
      warn_after: {count: 1, period: minute}
      error_after: {count: 30, period: hour}
  - name: source_b
    freshness:
      warn_after: {count: 2, period: day}
      error_after: {count: 4, period: day}
  - name: source_c
    freshness:
      warn_after: {count: 1, period: hour}
      error_after: {count: 2, period: hour}

💡 Hint

Check that warn_after is less than error_after and units match the requirements.

Option A correctly sets warn_after and error_after with proper counts and periods matching the problem statement.