Sometimes ETL doesn’t go according to plan, or is just done irregularly. In these situations where you don’t have data for yesterday but don’t want to have to always alert your users every time something happens, you might want to have the date range filters used in your Explores only look at the dates that have data.
Example: A user searches for your customers/users that have been created in the last 14 days. ETL didn’t run properly this morning so you only have data from 2 days ago. You want to show data from 14 days before yesterday.
Pre-warning - this is a thin use case and has some snags (listed under Caveats below)
Step 1:
Create a derived table that explicitly returns the max(date) from an ETL jobs/log table. I don’t have an ETL table handy, so I’m going to just explicitly return an arbitrary date:
view: etl_checker {
derived_table: {
## Change this to something like sql: SELECT max(etl_date) as etl_date FROM etl_logs ;;
sql: SELECT (CURRENT_DATE -2) as etl_date ;;
}
dimension: etl_date {
sql: ${TABLE}.etl_date ;;
}
}
Step 2:
CROSS JOIN this table in an Explore with your base table so every row has this date:
explore: users {
join: etl_checker {
type: cross
relationship: many_to_one
}
}
Step 3:
Use this date to create a DATEDIFF between the date and the CURRENT_DATE . Here we make use of Templated Filters’ lesser used date_end to extract the end date value of a date_filter we’ve also created.
This date_filter uses the sql parameter and is self-referential so it inserts into the WHERE clause, offsets the filter by the amount of days between today and the ETL date.
view: users {
...
# Take a user's input and check if the date range they selected matches the offset date range in the base table, where the offset is the number of days between their chosen date and the max ETL date (below dimension)
filter: date_filter {
type: date
sql:
CASE WHEN
DATEDIFF(day, ({% date_start date_filter %})::date,({% date_end date_filter %})::date) = 1 THEN
({% date_start date_filter %})::date = ${created_date}
ELSE
{% condition date_filter %} DATEADD(day,${etl_date_diff},${created_date}) {% endcondition %}
END
;;
}
# Get the difference in days between the max(date) from ETL and the date the user chose.
dimension: etl_date_diff{
type: number
sql: datediff(day,${etl_checker.etl_date},{% date_end date_filter %}) ;;
}
dimension_group: created {
type: time
timeframes: [
raw,
date,
]
sql: ${TABLE}.CREATED_AT ;;
}
}
Caveats:
As always, with these workarounds there are some subtle things to watch for. You should really only use this for Explores where there’s always a date range being filtered.
The purpose of this approach is to make sure you’re not showing NULLs for dates which haven’t been ETLd in your database yet and only showing the max dates which are present.
Edit:
I updated the filter so you can select single dates. Thanks
