Knowledge has develop into a important device for decision-making. To be actionable, information must be cleaned, reworked, and modeled.
This course of is commonly a part of an ELT pipeline that runs at a given frequency, for instance every day.
However, to regulate and make selections quick, stakeholders generally want entry to the latest information to have the ability to react quick.
For instance, if there’s a enormous drop within the variety of customers of an internet site, they want to pay attention to this difficulty shortly and be given the mandatory data to grasp the issue.
The primary time I used to be requested to construct a dashboard with real-time information, I linked it on to the uncooked desk that was real-time and offered some easy KPIs just like the variety of customers and crashes. For month-to-month graphs and deeper evaluation, I created one other dashboard linked to our information mannequin, that was up to date every day.
This technique was not optimum: I used to be duplicating logic between the information warehouse and the BI device, so it was tougher to keep up. Furthermore, the real-time dashboard may solely carry out properly with just a few days of information, so stakeholders needed to swap to the historic one to examine earlier dates.
I knew we needed to do one thing about it. We would have liked real-time information fashions with out compromising efficiency.
On this article, we’ll discover totally different options to construct real-time fashions, and their professionals and cons.
An SQL view is a digital desk that incorporates the results of a question. In contrast to tables, views don’t retailer information. They’re outlined by a question that’s executed each time somebody queries the view.
Right here is an instance of a view definition:
CREATE VIEW orders_aggregated AS (
SELECT
order_date,
COUNT(DISTINCT order_id) AS orders,
COUNT(DISTINCT customer_id) AS clients
FROM orders
GROUP BY order_date
)
Even when new rows are added to the desk, views keep updated. Nevertheless, if the desk is huge, views would possibly develop into very sluggish as no information is saved.
They need to be the primary choice to check out in case you are engaged on a small mission.
