Information has develop into a crucial software for decision-making. To be actionable, knowledge must be cleaned, remodeled, and modeled.
This course of is commonly a part of an ELT pipeline that runs at a given frequency, for instance every day.
Alternatively, to regulate and make choices quick, stakeholders typically want entry to the newest knowledge to have the ability to react quick.
For instance, if there’s a large drop within the variety of customers of a web site, they want to concentrate on this concern rapidly and be given the mandatory data to know the issue.
The primary time I used to be requested to construct a dashboard with real-time knowledge, I related 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 related to our knowledge mannequin, that was up to date every day.
This technique was not optimum: I used to be duplicating logic between the info warehouse and the BI software, so it was more durable to keep up. Furthermore, the real-time dashboard may solely carry out properly with just a few days of knowledge, so stakeholders needed to change to the historic one to test earlier dates.
I knew we needed to do one thing about it. We wanted real-time knowledge fashions with out compromising efficiency.
On this article, we’ll discover completely different options to construct real-time fashions, and their execs and cons.
An SQL view is a digital desk that incorporates the results of a question. Not like tables, views don’t retailer knowledge. 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. Nonetheless, if the desk is large, views would possibly develop into very sluggish as no knowledge is saved.
They need to be the primary choice to check out if you’re engaged on a small challenge.
