SQL Materialized Views
A Cloud Database Purpose-Built for
Segmentation and Personalization
Use Materialize to join multiple sources of real-time data and build a better customer experience - all using standard SQL.
We decided to look into Materialize to handle personalization and feature-serving in real-time, and by that evening we were up and running.
Tom Cooper Head of Data, Superscript
Put Analytics to Work in Customer-Facing Use Cases
Customer 360
Get a full view into how customers interact with your organization - across every touchpoint. Join multiple sources of data and ensure dashboards, customer service teams, and applications all run off the most up-to-date information.
Dynamic Pricing and Billing
Customers use Materialize to power dynamic pricing and billing because it can serve low-latency point lookups on large analytics tables that are always up-to-date.
App Customization and Promotions
Put your real-time segments to work - and create customized experiences or promotions across a range of applications like gaming, ecommerce, A/B tests, and marketing campaigns.
Content and Product Recommendations
Tailor recommended content or products with up-to-the-second customer data. Reduce cart abandonment, identify new sales opportunities - or simply stop promoting a product someone has already purchased.
Modern Data Applications need Modern Solutions
Segmentation and Personalization workloads need OLAP queries and OLTP performance.
Traditional Warehouses: Too Slow
Cloud Warehouses are easy to use, but get expensive when run continuously, and hit hard limits on latency and concurrency.
Stream Processors: Too Complicated
Stream Processors are fast, but they're a low-level tool, using them has high engineering costs, creates complex architectures.
Materialize packages the speed of stream processors in a familiar database abstraction.
Use the same SQL workflows from traditional warehouses but get results
updated continuously to power customer-facing use-cases in production.
Streaming Engine
Work is done at the moment of data arrival, rather than query time, so that maintained results are available almost instantly.
PostgreSQL Serving Layer
Materialize presents as Postgres - query it with high concurrency from any tool or driver compatible with Postgres.
Access via standard SQL
Incrementally Maintained Views
Write complex SQL transformations as materialized views that efficiently update themselves as inputs change.
Learn MoreBuilt for JOINs
Multi-way, complex join support, even across databases - all in standard SQL.
Learn MoreSliding Windows
Write queries that filter to a window of time anchored to the present, Materialize will update results as time advances.
Learn MoreSQL Subscriptions
Write alerts as SQL queries with filters and subscribe to new rows as they appear.
Learn MoreCREATE MATERIALIZED VIEW my_view AS
SELECT userid, COUNT(api.id), COUNT(pageviews.id)
FROM users
JOIN pageviews on users.id = pageviews.userid
JOIN api ON users.id = api.userId
GROUP BY userid;| userID | api_calls | pageviews |
|---|---|---|
| VPLaKV | 400 | 20 |
| MN37Mt | 60 | 9 |
| 1fT4KY | 72 | 42 |
| sT4QY | 10 | 342 |
Incrementally Maintained Views
Write complex SQL transformations as materialized views that efficiently update themselves as inputs change.
Learn MoreCREATE MATERIALIZED VIEW my_view AS
SELECT userid, COUNT(api.id), COUNT(pageviews.id)
FROM users
JOIN pageviews on users.id = pageviews.userid
JOIN api ON users.id = api.userId
GROUP BY userid;| userID | api_calls | pageviews |
|---|---|---|
| VPLaKV | 400 | 20 |
| MN37Mt | 60 | 9 |
| 1fT4KY | 72 | 42 |
| sT4QY | 10 | 342 |
Built for JOINs
Multi-way, complex join support, even across databases - all in standard SQL.
Learn MoreSELECT DISTINCT ON (auctions.id)
bids.amount,
auctions.item,
auctions.seller
FROM auctions, bids
WHERE auctions.id = bids.auction_id
ORDER BY auctions.id,
bids.amount DESC,
bids.buyer;
| amount | item | seller |
|---|
Sliding Windows
Write queries that filter to a window of time anchored to the present, Materialize will update results as time advances.
Learn MoreCREATE MATERIALIZED VIEW my_window AS
SELECT date_trunc('minute', received_at),
COUNT(*) as order_ct, SUM(amount) as revenue
FROM orders
WHERE mz_now() < received_at + interval '5 minutes'
GROUP BY 1;| minute | order_ct | revenue |
|---|
SQL Subscriptions
Write alerts as SQL queries with filters and subscribe to new rows as they appear.
Learn MoreSELECT userID, email, MAX(orders.id) as last_order
FROM users
JOIN orders ON orders.userID = users.id
GROUP BY userId, email
-- Use a filter to surface users with a high % of fraud
HAVING SUM(is_fraud) / COUNT(orders.id)::FLOAT > 0.5;| userID | last_order | |
|---|---|---|
| REOtIb | a@gmail.com | 13/12/2022 |
| Y5KBE8 | b@yahoo.com | 9/12/2022 |
| Wj7JQ0 | c@hotmail.com | 13/12/2022 |
| tPCQ0 | d@xyz.com | 13/11/2022 |
Built for JOINs
Multi-way, complex join support across real-time streams - all in standard SQL.
Active Replication
Use replication to increase availability, reduce downtime, scale seamlessly.
Secure and Compliant
SOC 2 Type 2 compliant, encrypted at rest, secure connectivity to your infra.
The Warehouse-Native Approach
to Segmentation and Personalization.
You don't need to compromise on speed, data quality, control, or simplicity.
“Our customers expect a personalized experience”
Running customer segments off a data warehouse is too slow for modern expectations. Move the exact same SQL from your data warehouse to Materialize and get segments that update in real-time.
“Our application microservices are tough to maintain”
With Materialize, you can cut out the notion of scheduling and jobs for microservices. Instead, you simply send data to Materialize, describe its transformations with SQL, and read results on the other side.
“Cache invalidation requires a ton of logic to get right”
Materialize is purpose-built for fast changing data. Its underlying engines know exactly which records to update or invalidate, giving data teams the most up-to-date value without complex cache invalidation logic.
“We need to join many data sources for a full customer view”
Materialize supports cross-stream and multi-way joins, without the need to microbatch or round-trip data at high latencies. With strict serializability, you don’t need to give up correctness guarantees to use multiple data inputs.
“Our segments are too large to run efficiently in real-time”
Materialize is built for horizontal scaling and separates storage and compute. Data is durably stored in S3, while compute scales independently. Run replicated compute clusters to increase availability and reduce downtime.
“We want to use machine learning to improve our segments”
For teams looking to improve their recommendation engine over time, Materialize can also be used as a real-time online feature store, training and serving machine learning models without waiting for batches of data to arrive.
More Use Cases
Real-Time & User-Facing Analytics
→Dashboards and data products need to be reactive to up-to-the-minute changes in your business.
Real-time Fraud Detection
→Monitor transactions as they occur and stop fraud in seconds.
Automation and Alerting
→Save time for your users, and build value by taking action or notifying at only the right moments.
ML in Production
→Online feature stores need continually updated data, operators need to monitor and react to changes in ML effectiveness.
