What you will be able to do
- Use the :daterange and :datebucket system filters in a query, and know the difference between applying and saving a filter value
- Set up a custom filter from a list or a query and recognise its visibility, ownership and refresh behaviour
- Edit, duplicate, unplace or delete dashboard tiles without losing queries by accident
- Diagnose dashboards that error for some viewers, show masked or missing rows, or show stale numbers
1.System filters: :daterange and :datebucket
Filters let a viewer reshape a dashboard without opening its SQL. In Snowsight, a filter is a special keyword in the query that resolves to a subquery or a list of values when the query runs. Because a filter is substituted into the query this way, you can only use it in certain places in the SQL.
Snowsight provides two system filters that every role can use, and you cannot edit or drop them. The :daterange filter limits a column to a date range, such as Last day, Last 7 days, Last 28 days, a custom range or All time. It defaults to Last day. It always uses the UTC time zone and ignores the TIMESTAMP_INPUT_FORMAT parameter, so day boundaries can differ from a viewer's local calendar. The :datebucket filter groups aggregate data by a period: Second, Minute, Hour, Day, Week, Month, Quarter or Year. It defaults to Day. The Snowflake sample query below uses both filters against the TPCH ORDERS table.
Checkpoint 1 of 8· Fill the gap
Which system filter keyword completes the WHERE clause so that the viewer's selected date range limits O_ORDERDATE?
SELECT
COUNT(O_ORDERDATE) as orders, O_ORDERDATE as date
FROM
SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE
O_ORDERDATE = ?
GROUP BY
:datebucket(O_ORDERDATE), O_ORDERDATE
ORDER BY
O_ORDERDATE
LIMIT 10;:daterange filters a column by a date range. :datebucket is the grouping filter used in GROUP BY, and :page_path is only the documentation's example of a custom filter keyword.
Source: docs.snowflake.comHow a filter change reaches other people matters for the exam. Selecting Apply reruns the dashboard with the new value for you. Selecting Save stores that value as the default for everyone who opens the dashboard. The documentation's example: you apply All time to look at the full history, then set the range back to Last 7 days, apply it, and save. That way the next viewer does not trigger a scan of the entire table when the dashboard opens.
Checkpoint 2 of 8· Check yourself
You pick All time on a dashboard's date range filter and select Apply, but you do not select Save. What do other users see the next time they open the dashboard?
Apply only runs the dashboard with the new value. Save is the action that stores a default filter value for dashboard users.
“select Apply to run the dashboard, and then select Save to save that default filter value for dashboard users.”Source: docs.snowflake.com
Sources1
2.Custom filters for business-specific slicing
System filters only handle time. To let viewers slice by region, product or page, you need a custom filter. Custom filters are created in Snowsight by roles that have been given permission to create them. A user with ACCOUNTADMIN grants that permission, and the grant can only be made in Snowsight.
When you create a custom filter, you choose a SQL keyword in the format :<string> with no spaces, for example :page_path. You then put that keyword in tile queries, just as you would :daterange.
| Setting | What it controls |
|---|---|
| Display Name | The label viewers see on the filter in a worksheet or dashboard |
| SQL Keyword | The unique :<string> keyword you insert into queries |
| Role | Owns the filter and runs the query that populates its values, when the filter is query-based |
| Warehouse | Refreshes query-based filter values. The owner role needs USAGE on it |
| Options via | Query (must return name and value columns) or List (name/value pairs, with Value Type set to Text or Number) |
| Multiple values / All / Other toggles | Let viewers select several items, select every item, or include items not in the list |
The All toggle has two modes. 'Any value' lets the filtered column hold any value, including values that are not in the filter list. 'Any value in list of options' limits results to items that are in the list. A query-based filter can run only one query to build its options, and that query must return name and value columns. It can also return an optional description column.
Three behaviours have governance consequences. First, every user in the account can see and use a custom filter, and the filter's role does not limit who sees it. Second, the options query runs as the user who created or last modified the filter, so its values must never expose sensitive data. Third, a query-based filter refreshes hourly by default, and you can change that to daily or never. Each refresh uses warehouse compute even when nobody has the filter open.
Checkpoint 3 of 8· Check yourself
An administrator creates a custom filter :region and associates it with the role SALES_ANALYST. Who in the account can see and use the filter?
Custom filters are visible to the whole account. The associated role decides who can edit the filter and which role runs its options query, not who can see it.
“A custom filter has an associated role, but that role does not limit filter visibility.”Source: docs.snowflake.com
Sources1
3.Editing tiles without losing work
Customizing a presentation usually means editing tiles that already exist. From a tile's menu, View Chart opens the chart in a worksheet so you can change its type, columns or appearance. Edit query opens the SQL. In both cases you select Return to <dashboard name> to save the change and go back to the dashboard. To add a chart for a tile that currently shows a table, open Edit Query, select Chart and return. A new chart tile then appears at the bottom of the dashboard. Going the other way, from chart to table, takes two steps: remove the tile, then add a tile and drag the table version of the query onto the dashboard.
Removing a tile and deleting a tile are different actions, and the difference is permanent. Unplace Tile takes the tile off the dashboard but keeps it in the Add tile menu. Delete removes the tile and its underlying query, and you cannot undo it. If a table tile and a chart tile share the same query, deleting one deletes both. A safer edit, when you want a variant, is Duplicate Tile.
One more edit can break a chart without warning you up front. A chart updates automatically only while the columns it uses are still in the query results. If you rename a column in the SQL, the chart flags the column it cannot find, and you have to point the chart at the new name.
Checkpoint 4 of 8· Match them up
Match each tile action to its effect on the tile's query.
Tap a term, then the definition that fits it.
Only Delete destroys the query. Unplace keeps it for later, Duplicate copies it, and Edit query opens it for changes.
“Deleting a tile from a dashboard also deletes the underlying queries. This action cannot be undone.”Source: docs.snowflake.com
4.Troubleshooting dashboards and reports
Most dashboard problems come from one fact: each tile is a query that runs in a particular role context, against data that some other process maintains. When a tile looks wrong, ask three questions. Which role ran the query? What do Snowflake's security policies return to that role? And is the data the query reads up to date? The table below maps common symptoms to the cause the documentation identifies.
| Symptom | Likely cause | What to check |
|---|---|---|
| A shared dashboard errors for a viewer but works for its author | Dashboard queries run in sessions with an assigned role, and the viewer must use that same role | The role in the dashboard's context selector compared with the viewer's role |
| Access granted through a secondary role does not help | Dashboards run only with the user's primary role, whatever DEFAULT_SECONDARY_ROLES is set to | Grant access to the role used as primary |
| Viewers see different rows from the same tile | A row access policy decides which rows each query returns | Row access policies on the tables behind the tile |
| A column shows partially or fully masked values for some viewers | A masking policy is applied at query time based on the execution context and role | Masking policies on that column |
| Numbers loaded by a scheduled task are stale or zero | The task never resumed, did not run, or ran but its SQL failed | TASK_HISTORY, the task state (tasks are created SUSPENDED), and the cron expression |
| A custom filter's drop-down stops updating | The filter's creator or last modifier was dropped or disabled, or has not signed in for 3 months | Filter details, and LOGIN_HISTORY for failed authentication attempts |
| Date-filtered totals look off by some hours | The :daterange filter always uses UTC | The time zone of the timestamp column |
| A chart says a column cannot be found | A column in the query was renamed | Point the chart at the new column name |
The policy rows explain behaviour that looks like a bug but is not one. Row access policies give Snowflake row-level security by deciding which rows a query returns. Dynamic Data Masking applies masking policies to table and view columns at query time. As a result, two people looking at one tile can each see correct results that differ. One of them may see plain text while the other sees a partially or fully masked value. The task row matters for reports that read a table loaded on a schedule. Snowflake creates every task in the SUSPENDED state. TASK_HISTORY shows whether the task ran, and also whether it ran but the SQL statement inside it failed.
Checkpoint 5 of 8· Check yourself
Two managers open the same customer tile. Neither has edited the tile query. One sees full email addresses and the other sees partially masked addresses. What is the most likely explanation?
Dynamic Data Masking is applied at query runtime. Depending on the policy conditions, context and role, a viewer sees plain-text, partially masked or fully masked values.
“Snowflake query operators may see the plain-text value, a partially masked value, or a fully masked value.”Source: docs.snowflake.com
Checkpoint 6 of 8· Exam question
An analyst charts `SUM(order_total)` by `order_date` in Snowsight and wants the line chart to show one point per month instead of one point per day. What should they configure?
Correct answer: D — Set the bucketing on the date column in the chart's data settings to Month, so the aggregated measure is grouped per calendar month
- A. LIMIT truncates rows arbitrarily; it does not group daily values into months and would drop most of the data.
- B. A scorecard shows one value, not a time series, so it cannot display one point per month.
- C. `:datebucket` is a dashboard system filter referenced in SQL; it does not relabel an axis, and chart bucketing is set in the chart's own settings.
- D. Snowsight charts support bucketing on date columns (date, week, month, year), grouping the aggregation without rewriting the SQL.
Checkpoint 7 of 8· Check yourself
A report tile reads a table that a newly created task is supposed to load every night. The table has never received rows. What should you verify about the task first?
A new task does not run until it is resumed. Check its state with DESCRIBE TASK or SHOW TASKS, then use TASK_HISTORY to confirm whether runs happened and succeeded.
“Snowflake creates all tasks in the SUSPENDED state.”Source: docs.snowflake.com
Checkpoint 8 of 8· Exam question
A data analyst needs to report how strongly `temperature` and `ice_cream_sales` move together as a single number in a Snowflake query that feeds a dashboard scorecard. Which approach returns that value?
Correct answer: B — Run `SELECT CORR(temperature, ice_cream_sales) FROM daily_sales` and display the returned coefficient between -1 and 1 in the scorecard
- A. Covariance is unbounded and depends on units; dividing by a constant does not turn it into a standardized coefficient.
- B. `CORR` is the Snowflake aggregate function that returns the Pearson correlation coefficient for two numeric expressions.
- C. `REGR_COUNT` only counts non-null pairs; it contains no information about the direction or strength of a relationship.
- D. The difference of medians compares central tendency of two unrelated scales and says nothing about co-movement.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.If a viewer's secondary role can read the table, the viewer's dashboard tiles will work as long as DEFAULT_SECONDARY_ROLES is set to ALL.Why is that wrong?
Dashboards turn off secondary roles and run only with the user's primary role.
Covered in Troubleshooting dashboards and reports
2.Associating a custom filter with a restricted role keeps its option list private to that role.Why is that wrong?
Every user in the account can view and use the filter, and its options query runs as the filter's creator. So the options list must not contain sensitive data.
Covered in Custom filters for business-specific slicing
3.Deleting a tile is a safe way to tidy a dashboard because the query can be added back later.Why is that wrong?
Delete removes the underlying query permanently. Use Unplace Tile to hide a tile and keep its query.
Covered in Editing tiles without losing work
4.The :daterange filter follows the session's time zone, or the TIMESTAMP_INPUT_FORMAT setting.Why is that wrong?
The date range filter is always evaluated in UTC.
Covered in System filters: :daterange and :datebucket
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Filters are implemented as special keywords that resolve as a subquery or list of values”
↩︎ System filters: :daterange and :datebucket“The following system filters are available to all roles:”
↩︎ System filters: :daterange and :datebucket“These filters cannot be edited or dropped.”
↩︎ System filters: :daterange and :datebucket“:datebucket Groups aggregate data by a period of time”
↩︎ System filters: :daterange and :datebucket“a user with the ACCOUNTADMIN role must grant the relevant permissions to a role granted to that user.”
↩︎ Custom filters for business-specific slicing“For SQL Keyword, enter a unique keyword to insert into queries. Use the format :<string>, without spaces.”
↩︎ Custom filters for business-specific slicing“The query used to populate a list of filter options is run as the user that created (or last modified) the filter.”
↩︎ Custom filters for business-specific slicing“will run the underlying query according to the configured schedule, even if no user has the filter open”
↩︎ Custom filters for business-specific slicing“The user is inactive because they have not signed in for 3 months.”
↩︎ Troubleshooting dashboards and reports“Anyone in your account can view and use a custom filter after it is created.”
↩︎ Exam trap 2“The date range filter always uses the UTC time zone and is not affected by the TIMESTAMP_INPUT_FORMAT parameter.”
↩︎ Exam trap 4“select Apply to run the dashboard, and then select Save to save that default filter value for dashboard users.”
↩︎ Checkpoint“A custom filter has an associated role, but that role does not limit filter visibility.”
↩︎ Checkpoint - 2.
“When you finish editing the query, select Return to <dashboard name> to save your changes and return to the dashboard.”
↩︎ Editing tiles without losing work“such as a table and chart view of the same query results, both tiles are deleted.”
↩︎ Editing tiles without losing work“To view shared dashboards, the Snowflake user must use the same role as the session context for the queries that drive the dashboard.”
↩︎ Troubleshooting dashboards and reports“Dashboards run exclusively with the user’s primary role, regardless of the value set for DEFAULT_SECONDARY_ROLES.”
↩︎ Exam trap 1“The tile is removed from the dashboard, but remains available to add to the dashboard from the Add tile menu.”
↩︎ Exam trap 3“Deleting a tile from a dashboard also deletes the underlying queries. This action cannot be undone.”
↩︎ Checkpoint - 3.
“If a column name changes, you must update the chart to use the new column name. Charts indicate any columns that cannot be found.”
↩︎ Editing tiles without losing work - 4.
“Snowflake supports row-level security through the use of row access policies to determine which rows to return in the query result.”
↩︎ Troubleshooting dashboards and reports - 5.
“uses masking policies to selectively mask plain-text data in table and view columns at query time”
↩︎ Troubleshooting dashboards and reports“Snowflake query operators may see the plain-text value, a partially masked value, or a fully masked value.”
↩︎ Checkpoint - 6.https://docs.snowflake.com/en/user-guide/tasks-tsOfficial docs
“It is possible that the task ran successfully but the SQL statement in the task definition failed.”
↩︎ Troubleshooting dashboards and reports“Snowflake creates all tasks in the SUSPENDED state.”
↩︎ Checkpoint