Data has develop into a critical tool for decision-making. To be actionable, data must be cleaned, transformed, and modeled.

This process is usually a part of an ELT pipeline that runs at a given frequency, for instance each day.

Alternatively, to regulate and make decisions fast, stakeholders sometimes need access to probably the most recent data to have the opportunity to react fast.

For instance, if there is a large drop within the variety of users of an internet site, they need to concentrate on this issue quickly and be given the needed information to know the issue.

The primary time I used to be asked to construct a dashboard with real-time data, I connected it on to the raw table that was real-time and provided some easy KPIs just like the variety of users and crashes. For monthly graphs and deeper evaluation, I created one other dashboard connected to our data model, that was updated each day.

This strategy was not optimal: I used to be duplicating logic between the info warehouse and the BI tool, so it was harder to keep up. Furthermore, the real-time dashboard could only perform well with a couple of days of knowledge, so stakeholders had to modify to the historical one to ascertain earlier dates.

I knew we needed to do something about it. We would have liked real-time data models without compromising performance.

In this text, we’ll explore different solutions to construct real-time models, and their pros and cons.

An SQL view is a virtual table that comprises the results of a question. Unlike tables, views don’t store data. They’re defined by a question that’s executed each time someone queries the view.

Here is an example of a view definition:

CREATE VIEW orders_aggregated AS (
SELECT 
order_date, 
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY order_date
)

Even when recent rows are added to the table, views stay awake so far. Nevertheless, if the table is big, views might develop into very slow as no data is stored.

They needs to be the primary choice to check out in case you are working on a small project.