Prepare Data for Visualisations¶

Page Topics:

• Defining insights tables
• Exposing tables for the visualisation environment

To prepare a data model to be used to build charts and dashboards, you need to define business-friendly tables called Insights tables. They are designed to provide packaged data sources to business users.

The insight table needs two steps:

1. Define the insights table
2. Expose it to the visualisation environment.

Defining Insights Tables¶

Insights tables are designed to provide packaged data sources to business users. They are refreshed every day and are used to create dashboards that stay up to date as new data comes into the lake.

Generally, Data Analysts will define insights tables and will ensure that they are easy to use and intuitive.

To make insights tables most useful and optimal, the following principles should be followed:

Defined as a SQL query¶

The insights tables are the result of any SQL query.

While the tables can handle significant data loads, there are some limitations:

• The queries need to run within 30 minutes. Tables that take more than 30 minutes to generate will result in an error. Note that an error of this nature generally only happens under data volume or calculation loads, and points to an error in the SQL query (such as an unlimited join).
• Some scenarios result in the query resulting in an error due to calculation complexity. In this case, the query will need to be split up.
• You can use the sandbox schema to create temporary tables to test the SQL query. Use the "create table as" statement in the Sqllab.

create table qainitech_sandbox.people_extract as
    (select name, surname from "qainitech-lake".people)

Priority Order¶

Insights tables can draw data from both the data lake and other insights tables. Each insights table is defined with a priority between 1 and 9. On the daily refresh, priority 1 tables are run first, and so on. This is useful for the structuring of insights tables - and allowing tables to be used in multiple lower priority tables.

Granularity¶

As mentioned, Insights tables generally don't require summarisation.

Deleted rows¶

As explained here, Comotion Dash does not allow for rows to be deleted. When defining insights tables, be sure to take into account what type of lake table you are selecting from.

Partitioning and Bucketing¶

Partitioning and Bucketing are a way of defining your table so that when a user runs a query, the data is organised in such a way that not all data needs to be scanned.

Partitioning¶

Partitioning is useful when the cardinality of a column of interest is low (where there are only a limited number of possible values for a column).

Only one partitioning scheme is allowed per table - as it affects how the data is physically organised. It may be a good idea to have multiple insights tables for different use cases (even if the insights contain fairly similar data).

Insights tables can have a maximum of 100 partitions per table.

Agent=Ben / Product=Widget
Agent=William / Product=Flange
Agent=William / Product=Widget
Agent=Tim / Product=Fitting
Agent=Tim / Product=Flange

Bucketing¶

Bucketing is similar to partitioning, except for more continuous data. The table in question is split into a defined number of buckets based on the value of the target column, or a combination of columns. This allows for more efficient retrieval.

Intuitive table and column names¶

Table names should be as intuitive and explanatory as possible, and meaningful to business users.

Similarly, column headings should be as intuitive as possible.

Format columns correctly¶

Using Presto SQL for the table definition, ensure that each column is the correct type. For example, dates should not be defined as strings, but rather parsed into date types. In your table definition SQL, if you are using a column in the source table that contains dates formatted as a string, you can use the date_parse function to parse it into a date.

Formatting of booleans¶

When creating boolean columns, using descriptive categories as opposed to true/false is encouraged. This is more friendly for building intuitive graphs. For instance, the case of the flagged_for_underwriting column mentioned above should be transformed into values

Null Handling¶

Nulls should be handled explicitly inside insights table definitions. This allows for a more intuitive presentation when creating dashboards.

with people_filtered as (
    select * from (
        select *, row_number() OVER (PARTITION BY changedDate desc) as rn 
        from
        people
    ) as temp
    where rn = 1 and not(deleted) -- select only the latest version of the line
),

with pets_filtered as (
        select * 
        from
        pets
        where snapshot_id in (select snapshot_id from pets order by snapshot_id desc limit 1)
        -- select only the latest snapshot
)

select 
    from_iso8601_date(birthdate) as birthdate, 
            -- proper date typeing
    coalesce(title,'unknown') as title,
            -- null handling
    coalesce(try_cast(pets_filtered.paws as INTEGER),0) as paws, 
            -- casting from string to integer with null handling,
    pets_filtered.breed_category, 
            -- partitions must be the last columns in the table definition
    service_client_id 
            -- service_client_id must always be the last partition
from
    people_filtered 
left join
    pets_filtered
on people_filtered.id = pets_filtered.people_id

breed_category
service_client_id

Run Triggers¶

Run triggers serve as a condition that determines when specific queries need to be executed, thereby reducing the usage costs associated with unnecessary recalculation of insights tables.

Think of a 'Run Trigger` like a condition that decides when to run a specific query:

• If the condition returns a True value, it signals us to execute a particular action or query for that table.
• If the condition is False, we do not execute any instruction.
• If there is no Run Trigger is specified, and the action gets executed.

In simpler terms, Run triggers give us precise control over when to run tasks, making our data processing smarter and more efficient. They help us respond to specific events or conditions, ensuring that we do the right things at the right time. For more information, check out our technical blog post on optimizing insights tables with run triggers.

Talk to us¶

Once you have completed the final design, get in touch with your Comotion Representative and we will configure the insights table for you to run daily.

Exposing and Configuring Tables for the Visualisation Environment¶

Before using the tables in the dashboards, they should first be exposed in the visualisation schema.

The Comotion Dash frontend is based on Apache Superset and these instructions show how to set up the table so that it can be used in the Superset visualisation environment:

1. To start, hover over the Data menu in the top navigation bar and select Datasets.
2. Then click the top right Plus Dataset button.
3. Select your Data Source, Schema Name (which will either be your sandbox or insights schema), and Table name, click the Add button on the bottom right corner.
   
4. Once created, navigate back to the table, and click the Edit button on the right side, in line with the new table.
   
5. This page provides several options for configuring your newly created table. Note that these configuration options determine how this table can be used in graphing tools, without changing any of the underlying data."

The configuration is split into five tabs:

Before you go¶

This documentation portal has been created to be your right hand of guidance on this journey. We will be evolving the content from time to time and if there is any specific information you want us to add to improve your experience, please get in touch or send us a direct email to dash@comotion.co.za.

We love hearing from you!