SQL Materialized Views
A Cloud Database Purpose-Built for
Automation and Alerting
Materialize is a distributed database with streaming internals: Use SQL to transform data in real-time, and use event-driven primitives to automate it.
By moving SQL models for fraud detection from an analytics warehouse to Materialize, Ramp cut lag from hours to seconds, stopped 60% more fraud and reduced the infra costs by 10x.
Ryan Delgado Staff Software Engineer, Data Platform - Ramp
Put Analytics to Work in Automation and Alerting
User-facing notifications
Customers expect only the most useful notifications to be delivered when they are most relevant. Build highly-specific notifications on high-volume, rapidly changing data - no need to wait for your data warehouse to run.
Fraud and anomaly detection
Fraud and bot management models need to work immediately to detect and eliminate anomalous activity faster than they can adapt and exploit. Detect fraud on large datasets at extremely low latencies - and easily adjust models as needed in SQL.
Risk Modeling
Instantly determine how much risk and volatility is present in a particular trade, investment, or series of cash flows. Use your existing SQL models to validate every trade in a portfolio in real-time, instead of in batches.
Monitoring and maintenance
Monitor and manage the health of networks, IoT devices, and connected fleets. Build dashboards for real-time visibility into conditions and locations, then automate alerts to status changes and better enable preventative maintenance.
Modern Data Applications need Modern Solutions
Automation and Alerting workloads need a solution that can push updates out proactively.
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.
Event-Driven Primitives
Sink data out of Materialize to Kafka, or Subscribe to updates via any PostgreSQL driver.
Managed in standard SQL
Incrementally Maintained Views
Write complex SQL transformations as materialized views that efficiently update themselves as inputs change.
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 Alerting
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 |
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 Alerting
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 |
Presents as PostgreSQL
Manage and query Materialize using any Postgres driver or tool.
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.
Low-Latency Serving Layer
Results can be maintained in memory, making read latency similar to Redis.
Secure and Compliant
SOC 2 Type 2 compliant, encrypted at rest, secure connectivity to your infra.
The Warehouse-Native Approach
to Automation and Alerting.
You don't need to compromise on speed, data quality, control, or simplicity.
“Our data warehouse alerts run too slowly”
Data warehouses power many user-facing notifications and data models - but can only work in batches. Materialize incrementally maintains the results of SQL queries in real-time so alerts never run off of old data.
“Creating and updating notification logic is a hassle”
Hard-coded notification logic requires a ton of effort to update and maintain as business requirements shift. Materialize allows you to adjust and test using standard SQL, saving time both in the short and long term.
“We don’t have the data engineers for real-time alerts”
Anyone who knows standard SQL - including full-stack engineers, data scientists, or PMs - can build notification logic within Materialize, eliminating the need for long back-and-forth review cycles with data engineering.
“We can’t risk automation if our system gives bad data”
Don’t risk weird failure cases caused by eventual consistency. All results from Materialize reflect correct answers, meaning alerts and automated processes are never falsely triggered by late-arriving data.
“We use other systems for our notifications”
Materialize is postgres wire-compatible and acts like a standard postgres database. Keep your existing alerting and notification systems - but power them with real-time data.
“We need to run alerts at massive scale”
Materialize is designed for scale, and is powered by a stack of stream processors - Timely Dataflow and Differential Dataflow - that have been battle-tested by Fortune 100 companies in global deployments.
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.
Segmentation and Personalization
→Value of personalization, recommendations, dynamic pricing increases as latency of data aggregations approaches zero.
ML in Production
→Online feature stores need continually updated data, operators need to monitor and react to changes in ML effectiveness.
