This is a self-contained demo using Materialize.
This demo shows you how to use dbt together with Materialize.
For this demo, we are going to monitor the reviews left by users on a demo website, and use dbt to model our business logic, like getting a list of important users that left bad reviews. We will then explore how to use this data to potentially reach out to the flagged users and improve our website experience.
Before you get started, you need to make sure that you have Docker and Docker Compose installed.
You can follow the steps here on how to install Docker:
Also, you would need to make sure that you have
dbt (v0.18.1+) installed:
🤩 Our Amazing Sponsors 👇View WebsiteDigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.View WebsiteLaravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.View WebsiteScriptables orchestrates Ubuntu servers for PHP/Laravel apps. Production ready components like MySQL, Nginx, Redis, and more in a few clicks.
You can find the files for this demo in this GitHub repository here.
As shown in the diagram above, we will have the following components:
- A mock service to continually generate reviews and users.
- The reviews and the users will be stored in a MySQL database.
- As the database writes occur, Debezium streams the changes out of MySQL to a Redpanda topic.
- We then ingest this Redpanda topic into Materialize directly.
- After that, we use dbt to define transformations on the data and create a model that lists any VIP users that left bad reviews.
- You could, later on, visualize the data in a BI tool like Metabase.
As a side note here, you would be perfectly fine using Kafka instead of Redpanda. I just like the simplicity that Redpanda brings to the table, as you can run a single Redpanda instance instead of all of the Kafka components.
Running the demo
First, start by cloning the repository:
git clone https://github.com/bobbyiliev/materialize-tutorials.git
After that, you can access the directory:
Let's start by running the Redpanda container:
docker-compose up -d redpanda
Build the images:
Then pull all of the other Docker images:
Finally, start all of the services:
docker-compose up -d
In order to launch the Materialize CLI, you can run the following command:
docker-compose run mzcli
This is just a shortcut to a Docker container with a compatible CLI pre-installed; if you already have
psqlinstalled, you could instead connect to the running Materialize instance using that:
psql -U materialize -h localhost -p 6875 materialize.
As soon as the demo is running, the mock service will start generating reviews and users.
Prepare dbt configuration
First, we will need to install the
python3 -m venv dbt-venv source dbt-venv/bin/activate pip install dbt-materialize
After that, with your favorite text editor, open the
~/.dbt/project.yml file and add the following lines:
user_reviews: outputs: dev: type: materialize threads: 1 host: localhost port: 6875 user: materialize pass: pass dbname: materialize schema: analytics target: dev
After that, to make sure that the connection to the Materialize container is working, run:
Finally, we can use dbt to create materialized views on top of the 3 Redpanda/Kafka topics. To do so just run the following dbt command:
This command generates executable SQL from our model files (found in the
models directory of this project) and executes that SQL against the target database, creating our materialized views.
Note: If you installed
dbt-materializein a virtual environment, make sure it's activated. If you don't have it installed, please revisit the setup above.
Finally, you can run your dbt tests:
Verify the Materialized Views and Sources are created
Congratulations! You just used dbt to create materialized views in Materialize.
You can check the columns of the
reviews source by running the following statement:
SHOW COLUMNS FROM analytics.reviews_raw;
You'll see that, as Materialize is pulling the message schema from the Redpanda registry, it knows the column types to use for each attribute:
name | nullable | type ---------------+----------+----------- id | f | bigint user_id | t | bigint review_text | t | text review_rating | t | integer created_at | t | text updated_at | t | timestamp
You can verify the views were created from your psql shell connected to Materialize:
SHOW VIEWS FROM analytics;
name -------------------- badreviews vipusers vipusersbadreviews
You can also verify the data is being pulled from Redpanda by running the following query a few times:
SELECT COUNT(*) FROM analytics.vipusersbadreviews;
You will be able to see that the result changes each time you run the query, meaning that the data is being incrementally updated without you having to run
dbt run again.
Generate the dbt docs
Once we have our materialized views created, we can generate the dbt docs. To do so, run the following command:
dbt docs generate
After that you can serve the docs by running the following command:
dbt docs serve
Then visit the docs at http://localhost:8080/dbt/docs/. There, you will have a list of all the views that were created and you can click on any of them to see the SQL that was generated. You would also see some nice Lineage Graphs that show the relationships between the views:
In order to access the Metabase instance, visit
http://localhost:3030 if you are running the demo locally or
http://your_server_ip:3030 if you are running the demo on a server. Then follow the steps to complete the Metabase setup.
Materialize integrates natively with Metabase using the official PostgreSQL connector. To connect to your Materialize database, specify the following connection properties:
Field | Value ----------------- | ---------------- Database | PostgreSQL Name | user_reviews Host | materialized Port | 6875 Database name | materialize Database username | materialize Database password | Leave empty
Once ready, you will be able to visualize your data just as you would with a standard PostgreSQL database.
Stopping the Demo
To stop all of the services, run the following command:
As you can see, this is a barebones example of how to use Materialize together with dbt. You can use Materialize to ingest data from a variety of sources and then stream it to a variety of destinations.
To learn more about dbt and Materialize, check out the documentation here:
- dbt + Materialize demo: Running dbt’s jaffle_shop with Materialize
- dbt + Materialize: streaming Wikipedia data demo
- Materialize and dbt getting started guide
Also make sure to check out this video here on how to go from Batch to Streaming in One Easy Step (w/ Emily Hawkins & Arjun Narayan):