dbt-date for date spine - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of dbt-date date_spine with daily frequency

What is the output of this dbt SQL code that uses dbt-date's date_spine macro to generate dates from 2024-01-01 to 2024-01-05 with daily frequency?

dbt

select * from {{ dbt_date.date_spine(
  datepart='day',
  start_date='2024-01-01',
  end_date='2024-01-05'
)}} order by date_day

A [{'date_day': '2024-01-01'}, {'date_day': '2024-01-02'}, {'date_day': '2024-01-03'}, {'date_day': '2024-01-04'}, {'date_day': '2024-01-05'}]

B [{'date_day': '2024-01-01'}, {'date_day': '2024-01-03'}, {'date_day': '2024-01-05'}]

C [{'date_day': '2024-01-02'}, {'date_day': '2024-01-03'}, {'date_day': '2024-01-04'}]

D [{'date_day': '2024-01-01'}, {'date_day': '2024-01-02'}, {'date_day': '2024-01-04'}, {'date_day': '2024-01-05'}]

💡 Hint

Think about what daily frequency means for a date range from start to end inclusive.

The date_spine macro with datepart='day' generates every day from the start_date to the end_date inclusive, so all dates from 2024-01-01 to 2024-01-05 appear.

❓ data_output

intermediate

Number of weeks generated by dbt-date date_spine

Using dbt-date's date_spine macro with weekly frequency from 2024-01-01 to 2024-02-01, how many rows will the resulting date spine table have?

dbt

select count(*) as week_count from {{ dbt_date.date_spine(
  datepart='week',
  start_date='2024-01-01',
  end_date='2024-02-01'
)}}

A 4

B 5

C 6

D 7

💡 Hint

Count the number of weeks starting from the first week containing 2024-01-01 up to the week containing 2024-02-01.

The date_spine with weekly frequency includes all weeks overlapping the date range, resulting in 6 weeks.

🔧 Debug

advanced

Identify the error in dbt-date date_spine usage

What error will this dbt SQL code produce when running the date_spine macro with an invalid datepart 'fortnight'?

dbt

select * from {{ dbt_date.date_spine(
  datepart='fortnight',
  start_date='2024-01-01',
  end_date='2024-01-31'
)}}

A Syntax error: missing comma

B Runtime error: Invalid date format

C Empty result set with no error

D Compilation error: Unsupported datepart 'fortnight'

💡 Hint

Check if 'fortnight' is a valid datepart for the macro.

The date_spine macro only supports specific dateparts like day, week, month, quarter, year. 'fortnight' is not supported and causes a compilation error.

🚀 Application

advanced

Using dbt-date date_spine to fill missing dates in sales data

You have sales data with some missing dates. Which dbt-date date_spine usage will help create a complete date table from 2024-01-01 to 2024-01-10 to join and fill missing dates?

A select * from {{ dbt_date.date_spine(datepart='day', start_date='2024-01-01', end_date='2024-01-10') }}

B select * from {{ dbt_date.date_spine(datepart='month', start_date='2024-01-01', end_date='2024-01-10') }}

C select * from {{ dbt_date.date_spine(datepart='year', start_date='2024-01-01', end_date='2024-01-10') }}

D select * from {{ dbt_date.date_spine(datepart='hour', start_date='2024-01-01', end_date='2024-01-10') }}

💡 Hint

To fill missing daily sales, you need a daily date spine.

Using datepart='day' generates every day in the range, which can be joined to sales data to fill missing dates.

🧠 Conceptual

expert

Why use dbt-date date_spine for time series analysis?

Which is the best reason to use dbt-date's date_spine macro when preparing data for time series analysis?

A To filter out weekends and holidays from the dataset

B To generate a continuous sequence of dates ensuring no gaps in the timeline

C To aggregate data by random date intervals for sampling

D To convert string dates into datetime format automatically

💡 Hint

Think about what a date spine provides for time series data.

A date spine creates a continuous timeline of dates, which is essential to identify missing dates and perform consistent time series analysis.