Skip to main content

Release - September 14th, 2021

· 8 min read
Roxanna Pourzand
Product Manager

✨ New ✨#

Source Query in Query Logs#

Details

Product Component

Metrics Catalog

More Detail

We now show the Source SQL generated from the data warehouse to create the dataset (along with the generated SQL) on our query logs page in the Metrics Catalog.

Verification Steps

  1. Navigate to the query logs through the settings page or any metric chart
  2. Notice how the page shows two code snippets, one for Source SQL and one for the Generated SQL

Multi-Hop joins#

Details

Product Component

Metrics Framework

More Detail

Multi-hop joins allow you to retrieve dimensions from data sources that require more than one join.

This is currently supported in the framework & CLI level, and will also be supported in the Metrics Catalog

Example:

transaction table:

measures: transaction_count

identifiers: account_id

bridge table:

identifiers: account_id, customer_id

customer table:

identifiers: customer_id

dimensions: customer_id

The following query will join the bridge table to the transaction table, then the customer table to the bridge table. This allows us to slice the transaction count metric by customer_name even though it requires two joins to retrieve customer_name. Additionally, note the syntax; the customer must specify the identifiers to chart a path to the dimension (account_id, then customer_id)

mql query --metrics transaction_count --dimensions account_id__customer_id__customer_name

this yields a query like:

SELECT  ct.customer_name as account_id__customer_id__customer_name  , SUM(am.txn_count) as txn_countFROM transform_schema.bridge_table btJOIN transform_schema.customer_table ct  ON ct.customer_id = bt.customer_idJOIN transform_schema.account_month_txns am  ON bt.account_id = am.account_idGROUP BY  ct.customer_name

Verification Steps

Pick a dimension you'd like to join to your metric that requires more than one join to retrieve. Specify that dimension by joining the identifiers (in order) via dunders "__" and pass that dimension to a query like the above query. Validate that it slices the metrics by the requested dimension as you'd expect.

New Models Page#

Details

Product Component

Metrics Catalog

More Detail

We have a new 'Model' settings page that shows all data sources & metrics created in the organization and it allows you to dig into code snippets for each of them.

Verification Steps

Navigate to Settings —> Models (Under Organization)

Find a list of your metrics and data sources. Each entity will show a code snippet, which aligns with the line in the file. Click on the entity to open and collapse it.

Various CLI UX improvements#

Details

Product Component

Interfaces - MQL CLI

More Detail

  1. mql commit-configs now has the same default as validate-configs, for the current working directory (no need to specify the --config-dir)
  2. Removed scientific notation when we format results for CLI output
  3. We added an option to show all dimensions in mql list-metrics, even when the list is long - via the show-all-dims argument in mql list-metrics
  4. Added an Explain (--explain) option for an MQL query, which show you the raw SQL generated by MQL to build the query
  5. When the CLI times out waiting for a long query, we'll give you a helpful message alerting you of the timeout so it’s clear that the error resulted from a timeout
  6. If a query fails for any reason, we'll do our best to pull out a useful error message to show you inline (rather than always requiring you to run stream-query-logs).

Verification Steps

  1. Run mql commit-configs from the root directory of your Transform repo without the --config-dir flag.
  2. Run query with result of greater than x(?) digits and ensure scientific notation is not returned
  3. Run mql list-metrics —show-all-dims to confirm all associated dimensions are listed
  4. Add --explain to your query and confirm you see the SQL output
  5. Set the timeout parameter to an arbitrarily short limit using-timeout argument and confirm the query failure indicates it was due to a timeout
  6. Try to create a query with a metric that doesn't exist:
mql query --metrics ghost_metric --dimensions ds

Before:

✗ mql query --metrics ghost_metric --dimensions ds --order ds              ✔ Query initialized: 1630370876675_0000116✖ Failure 🧐 - query failed after 4.05 seconds...💡 See MQL server logs for details

After:

✗ mql query --metrics ghost_metric --dimensions ds --order ds --timeout 100✔ Query initialized: 1630370876675_0000107✖ 'Unable to find metric `ghost_metric`. Perhaps it has not been registered?💡 See MQL server logs for more details:        mql stream-query-logs --query-id 1630370876675_0000107

Annotations as ISO time#

Details

Product Component

Metrics Catalog

More Detail

On the Metrics Page, Annotation dates were shown as MM/DD/YY but this led to some confusion. Now we're showing YYYY-MM-DD per the ISO standard.

Verification Steps

Create or view and existing annotation and confirm the format is updated.

Materialization Config. Validation#

Details

Product Component

Metrics Framework

More Details

We added configuration validation for materializations, so our validation process when committing your configurations to Transform will check if you have the necessary fields and that they are valid. For example, the metrics and dimensions listed in the materialization configuration need to refer to ones defined in the model, and we will throw a validation error if they do not.

Verification Steps

Create materialization config and confirm it's validated by Transform in the Github actions.

Trim incomplete time windows for granularity#

Details

Product Component

Metrics Catalog

More Details

Granularity now by default trims incomplete time periods so there are no false trends exhibited due to incomplete time ranges. This will also ensure that when time comparison is applied, it's only happening on complete ranges.

As an example, if you select weekly granularity and the latest day of data occurs in the middle of the week, we will trim the output to return only the latest complete week.

In a later release, we will also provide the option not to trim.

Verification Steps

  1. Select a time range that includes an incomplete month or week
  2. Select weekly or monthly granularity to confirm the time period is by default trimmed.

Support for Nullable Foreign Key#

Details

Product Component

Metrics Framework

More Detail

For a given foreign key we show null dimension values if the primary key does not exist. This functionality is visible when querying through the CLI and the UI. In the UI, it will show up as a NULL series when grouping by a particular dimension.

Example: In this case, the country_id of 3 is null and does not exist in the country table. Previously, when querying sales by country, we would have omitted the NULL value. It is now included in results. 

---Table: fct_sales
sales  country_id1        11        11        21        3
---Table: dim_country
country_id  country1           us2           canada
---Query: sales by country
sales   country_id   country2       1            us1       2            ca1       3            NULL

Verification Steps

Query a metric in the CLI with a dimension that has a value for a foreign key identifier, but not a primary one.

🐛 Bug Fixes 🐛#

Chart Axis Precision#

Details

Product Component

Metrics Catalog

More Detail

We now abbreviate our y-axis number display to show only 3 significant digits if there is no decimal and 5 significant digits if there is a decimal. We will abbreviate the number to include “K” for one thousand and “M” for a million,...etc.

Examples:

  • 100
  • 100%
  • 100K
  • 100K%
  • 100.88
  • 100.88%
  • 100.88K
  • 100.88K%

Verification Steps

Check your chart y-axis for metrics that have values greater than 3 significant digits. See below example where we've appended a k to each value to represent thousands.

Team Settings Page Member List#

Details

Product Component

Metrics Catalog

More Detail

The Team Settings page only showed 5 team members, and we fixed a bug so that the full list of all team members is present.

Verification Steps

Visit the team settings page for a team with more than 5 members and confirm you can see all team members.

Timezone bug fix#

Details

Product Component

Metrics Catalog

More Detail

We fixed a bug where the original timezone of the data point in the Metrics Catalog was erroneously overridden, causing certain metric values in the UI to be offset by a day. This only affected the front end, not the framework and CLI.

Verification Steps

Verify tooltip/data in the chart shows the correct date for a given value.

Tooltip not showing grouping#

Details

Product Component

Metrics Catalog

More Detail

We resolved an issue where sometimes when a single line was present on a chart for a grouping, the tooltip would not indicate what category/group was being displayed.

Verification Steps

Edit your metric chart to create a grouping that resolves to one line of values and confirm the tooltip indicates the grouping for the dimension.