### Start Minikube Cluster

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

Initializes and starts a Minikube cluster, which is a prerequisite for the local Materialize deployment.

```shell
minikube start
```

--------------------------------

### Install dbt-materialize Plugin (Python)

Source: https://materialize.com/docs/manage/dbt/get-started

Installs the dbt-materialize plugin and dbt-core using pip within a virtual environment. This adapter is compatible with dbt Core and not directly with dbt Cloud.

```bash
python3 -m venv dbt-venv
source dbt-venv/bin/activate
pip install dbt-core dbt-materialize
```

--------------------------------

### Check dbt Plugin Installation (Bash)

Source: https://materialize.com/docs/manage/dbt/get-started

Verifies if the dbt-materialize plugin is successfully installed by checking the 'Plugins' section in the dbt version output.

```bash
dbt --version
```

--------------------------------

### Create Working Directory and Navigate (Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Creates a new directory for local Materialize setup and changes the current directory into it. This is the initial step for organizing the local deployment files.

```shell
mkdir my-local-mz
cd my-local-mz
```

--------------------------------

### Serve DBT Documentation Website

Source: https://materialize.com/docs/manage/dbt/get-started

Launches a web server to host the generated DBT project documentation website, typically on port 8000.

```shell
dbt docs serve #--port <port>
```

--------------------------------

### Install mz CLI with Binary Download on Linux

Source: https://materialize.com/docs/integrations/cli/installation

Installs the Materialize CLI (mz) on Linux by downloading the latest binary. It fetches the correct architecture for Linux and extracts it to /usr/local.

```bash
curl -L https://binaries.materialize.com/mz-latest-$(uname -m)-unknown-linux-gnu.tar.gz \
    | sudo tar -xzC /usr/local --strip-components=1
```

--------------------------------

### Generate DBT Project Documentation

Source: https://materialize.com/docs/manage/dbt/get-started

Generates documentation for the DBT project by compiling project information and Materialize catalog metadata into JSON files.

```shell
dbt docs generate
```

--------------------------------

### Materialize Setup for EXPLAIN ANALYZE Examples

Source: https://materialize.com/docs/sql/explain-analyze

Sets up sample source, view, and index objects to demonstrate the usage of EXPLAIN ANALYZE with runtime metrics. This includes creating an 'auction_house' source, a 'winning_bids' view, and a 'wins_by_item' index.

```sql
CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION
(TICK INTERVAL '1s', AS OF 100000)
FOR ALL TABLES;

CREATE VIEW winning_bids AS
  SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
    FROM auctions AS a
    JOIN bids AS b
      ON a.id = b.auction_id
   WHERE b.bid_time < a.end_time
     AND mz_now() >= a.end_time
   ORDER BY a.id, b.amount DESC, b.bid_time, b.buyer;

CREATE INDEX wins_by_item ON winning_bids (item);
```

--------------------------------

### Verify Metrics Server Installation

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

This command lists pods in the 'kube-system' namespace that are part of the 'metrics-server' instance, allowing verification of its running status.

```bash
kubectl get pods -n kube-system -l app.kubernetes.io/instance=metrics-server
```

--------------------------------

### Install mz CLI with Binary Download on macOS

Source: https://materialize.com/docs/integrations/cli/installation

Installs the Materialize CLI (mz) on macOS by downloading the latest binary. It fetches the correct architecture for Darwin and extracts it to /usr/local.

```bash
curl -L https://binaries.materialize.com/mz-latest-$(uname -m)-apple-darwin.tar.gz \
    | sudo tar -xzC /usr/local --strip-components=1
```

--------------------------------

### Subscribe to Real-time Flippers Data

Source: https://materialize.com/docs/get-started/quickstart

Uses the Materialize `SUBSCRIBE` command to stream changes from the `flippers` view. The `WITH (snapshot = false)` option ensures only new data arriving after the subscription starts is displayed.

```sql
SUBSCRIBE TO (
   SELECT *
   FROM flippers
) WITH (snapshot = false)
;
Copy
```

--------------------------------

### Create a new dbt project (Bash)

Source: https://materialize.com/docs/manage/dbt/get-started

Initializes a new dbt project with a specified name. This command creates a default project structure, configuration files like profiles.yml, and sample models.

```bash
dbt init <project_name>
```

--------------------------------

### Install Materialize Operator

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

Installs the Materialize Operator using Helm, configuring it for the 'minikube' cloud provider, enabling pod metrics, and applying the downloaded `sample-values.yaml` file. The operator is installed in the `materialize` namespace.

```shell
helm install my-materialize-operator materialize/materialize-operator \
    --namespace=materialize --create-namespace \
    --version v25.2.6 \
    --set operator.cloudProvider.region=minikube \
    --set observability.podMetrics.enabled=true \
    -f sample-values.yaml
```

--------------------------------

### Show Objects in Materialize

Source: https://materialize.com/docs/manage/dbt/get-started

SQL commands to display sources, views, and materialized views within Materialize, used for verifying object creation after running DBT models.

```sql
SHOW SOURCES [FROM database.schema];
```

```sql
SHOW VIEWS;
```

```sql
SHOW MATERIALIZED VIEWS;
```

--------------------------------

### Subscribe to Real-time Flippers Data

Source: https://materialize.com/docs/self-managed/v25.2/get-started/quickstart

Subscribes to the `flippers` view to receive real-time updates on identified auction flippers. The `WITH (snapshot = false)` option ensures that only new flippers appearing after the subscription starts are displayed, providing a continuous stream of newly flagged users.

```sql
SUBSCRIBE TO (
   SELECT *
   FROM flippers
) WITH (snapshot = false)
;
```

--------------------------------

### Create a kind Cluster (Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Initializes a Kubernetes cluster using 'kind' (Kubernetes in Docker). This cluster will host the Materialize components.

```shell
kind create cluster
```

--------------------------------

### Install mz CLI with APT on Linux

Source: https://materialize.com/docs/integrations/cli/installation

Installs the Materialize CLI (mz) on Debian-based Linux distributions (like Ubuntu) using APT. It adds the Materialize repository and installs the materialize-cli package.

```bash
curl -fsSL https://dev.materialize.com/apt/materialize.sources | sudo tee /etc/apt/sources.list.d/materialize.sources
sudo apt update
sudo apt install materialize-cli
```

--------------------------------

### Show Schemas and Materialized Views for Tests

Source: https://materialize.com/docs/manage/dbt/get-started

SQL commands to verify the creation of schemas and materialized views for tests within Materialize after running 'dbt test'.

```sql
SHOW SCHEMAS;
```

```sql
SHOW MATERIALIZED VIEWS FROM public_etl_failure;
```

--------------------------------

### Get mz-debug version and help

Source: https://materialize.com/docs/integrations/mz-debug

Demonstrates how to check the installed version of the mz-debug tool using the --version flag and how to view available options and usage instructions with the --help flag.

```shell
mz-debug --version
```

```shell
mz-debug --help
```

--------------------------------

### Define MySQL Source and Subsources

Source: https://materialize.com/docs/manage/dbt/get-started

Creates a MySQL source in Materialize, reading all tables from the database. This example shows how to define these automatically created subsources in a dbt YAML file for dbt to recognize them.

```yaml
sources:
  - name: mysql
    schema: "{{ target.schema }}"
    tables:
      - name: table_a
      - name: table_b

```

--------------------------------

### Install mz CLI with Homebrew on macOS

Source: https://materialize.com/docs/integrations/cli/installation

Installs the Materialize CLI (mz) using Homebrew on macOS. Requires Homebrew to be installed and adds the materializeinc/materialize tap.

```bash
brew install materializeinc/materialize/mz
```

--------------------------------

### Add and Update Helm Repositories

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

This snippet demonstrates how to add the metrics-server Helm repository and subsequently update it to fetch the latest chart information. These are essential steps before installing any Helm charts.

```bash
helm repo add metrics-server https://kubernetes-sigs.github.io/metrics-server/
helm repo update metrics-server
```

--------------------------------

### Download Materialize Sample Configuration Files (Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Downloads essential YAML configuration files from the Materialize GitHub repository. These files are used to configure the Materialize Operator, PostgreSQL, MinIO, and the Materialize instance itself for local testing. Versions are specified by environment variables.

```shell
mz_operator_version=v0.154.0
mz_version=v0.147.7

curl -o sample-values.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_operator_version/misc/helm-charts/operator/values.yaml
curl -o sample-postgres.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/postgres.yaml
curl -o sample-minio.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/minio.yaml
curl -o sample-materialize.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/materialize.yaml
```

--------------------------------

### Query Winning Bids View (SQL)

Source: https://materialize.com/docs/self-managed/v25.2/get-started/quickstart

Demonstrates how to query the 'winning_bids' view to retrieve data. The examples show fetching all records ordered by bid time and filtering by item, with a limit of 10 results.

```sql
SELECT * FROM winning_bids
ORDER BY bid_time DESC
LIMIT 10;
```

```sql
SELECT * FROM winning_bids
WHERE item = 'Best Pizza in Town'
ORDER BY bid_time DESC
LIMIT 10;
```

--------------------------------

### Install PostgreSQL (Kubectl/Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Applies the PostgreSQL configuration from `sample-postgres.yaml` to the Kubernetes cluster. This sets up PostgreSQL as the metadata database for Materialize.

```shell
kubectl apply -f sample-postgres.yaml
```

--------------------------------

### Install and Launch MCP Server

Source: https://materialize.com/docs/integrations/llm

Installs the MCP Server for Materialize using uv and then launches the server. Assumes uv is already installed and configured.

```shell
uv venv
uv pip install mcp-materialize
uv run mcp-materialize
```

--------------------------------

### Materialize Credit Consumption Rate Example Output

Source: https://materialize.com/docs/free-trial-faqs

An example output from a Materialize query monitoring credit consumption. This shows a scenario where the total credit consumption rate is 0.25 credits per hour, typically observed when only the 'quickstart' cluster is active.

```sql
 credit_consumption_rate
-------------------------
                     .25
(1 row)

```

--------------------------------

### Create a basic dbt View in Materialize

Source: https://materialize.com/docs/manage/dbt/get-started

Sets up a dbt model to be materialized as a standard view in Materialize. This is the default behavior but explicit configuration is recommended for maintainability.

```sql
{{ config(materialized='view') }}

SELECT
    col_a, ...
FROM {{ ref('kafka_topic_a') }}

```

--------------------------------

### Create PostgreSQL Source

Source: https://materialize.com/docs/manage/dbt/get-started

Defines a PostgreSQL source in Materialize using dbt, reading all tables from a specified publication. Requires a pre-existing PostgreSQL connection.

```sql
{{ config(materialized='source') }}

FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
FOR ALL TABLES

```

--------------------------------

### Add Materialize Helm Repository (Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Adds the official Materialize Helm chart repository to your local Helm configuration. This allows you to fetch and install Materialize using Helm.

```shell
helm repo add materialize https://materializeinc.github.io/materialize
```

--------------------------------

### Install MinIO (Kubectl/Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Applies the MinIO configuration from `sample-minio.yaml` to the Kubernetes cluster. This sets up MinIO as the blob storage for Materialize.

```shell
kubectl apply -f sample-minio.yaml
```

--------------------------------

### Deploy Materialize using kubectl

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

This command applies a Kubernetes manifest file named 'sample-materialize.yaml' to create the 'materialize-environment' namespace and deploy Materialize components within it.

```bash
kubectl apply -f sample-materialize.yaml
```

--------------------------------

### Install Materialize Operator (Helm/Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Installs the Materialize Operator into the 'materialize' namespace using Helm. It enables observability metrics and uses the downloaded `sample-values.yaml` for configuration. A specific operator version is specified.

```shell
helm install my-materialize-operator materialize/materialize-operator \
    --namespace=materialize --create-namespace \
    --version v25.2.6 \
    --set observability.podMetrics.enabled=true \
    -f sample-values.yaml
```

--------------------------------

### Add Metrics Server Helm Repository

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

Adds the metrics-server Helm repository to your local Helm configuration. This command fetches the chart repository from the specified URL.

```bash
helm repo add metrics-server https://kubernetes-sigs.github.io/metrics-server/
```

--------------------------------

### Install Metrics Server in Kubernetes

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

This command installs the Kubernetes metrics server into the 'kube-system' namespace. It includes flags to disable TLS verification for the kubelet and prioritize internal IP addresses for communication.

```bash
helm install metrics-server metrics-server/metrics-server \
   --namespace kube-system \
   --set args="{--kubelet-insecure-tls,--kubelet-preferred-address-types=InternalIP\,Hostname\,ExternalIP}"
```

--------------------------------

### Example output of SHOW VIEWS

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-views

Provides an example of the output from the SHOW VIEWS command, displaying a list of view names.

```sql
name
---------
my_view
```

--------------------------------

### Verify Node Labels

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

Confirms that the previously applied labels have been successfully assigned to the Minikube node by listing nodes with their labels.

```shell
kubectl get nodes --show-labels
```

--------------------------------

### Run DBT Models

Source: https://materialize.com/docs/manage/dbt/get-started

Executes DBT models to generate and run SQL code in the target environment. Compiled statements are stored in the target directory.

```shell
dbt run
```

--------------------------------

### Specify Database for Model Creation (SQL)

Source: https://materialize.com/docs/manage/dbt/get-started

Configures a Materialize model (source, view, materialized_view, or sink) to be created in a specific database.

```sql
{{ config(materialized='materialized_view', database='database_a') }}
Copy
```

--------------------------------

### Show Sources in Materialize

Source: https://materialize.com/docs/get-started/quickstart

Lists all available sources within the Materialize environment, displaying their names, types, associated clusters, and comments.

```sql
SHOW SOURCES;
```

--------------------------------

### Create MySQL Source in Materialize

Source: https://materialize.com/docs/self-managed/v25.2/manage/dbt/get-started

Shows how to create a MySQL source in Materialize using dbt. Similar to PostgreSQL, it uses a `.yml` file workaround to manage subsources and includes a dbt model demonstrating dependency on these subsources.

```sql
{{ config(materialized='source') }}

FROM MYSQL CONNECTION mysql_connection
FOR ALL TABLES;

```

```yaml
sources:
  - name: mysql
    schema: "{{ target.schema }}"
    tables:
      - name: table_a
      - name: table_b

```

```sql
-- depends_on: {{ ref('mysql') }}
{{ config(materialized='view') }}

SELECT
    table_a.foo AS foo,
    table_b.bar AS bar
FROM {{ source('mysql','table_a') }}
INNER JOIN
     {{ source('mysql','table_b') }}
    ON table_a.id = table_b.foo_id

```

--------------------------------

### Create MySQL Source

Source: https://materialize.com/docs/manage/dbt/get-started

Defines a MySQL source in Materialize using dbt, reading all tables from the connected MySQL database. Requires a pre-existing MySQL connection.

```sql
{{ config(materialized='source') }}

FROM MYSQL CONNECTION mysql_connection
FOR ALL TABLES;

```

--------------------------------

### Example Connection Creation SQL - SQL

Source: https://materialize.com/docs/sql/show-create-connection

An example of the output from `SHOW CREATE CONNECTION`, displaying the actual SQL statement used to create a Kafka connection, including broker details and authentication mechanisms.

```sql
CREATE CONNECTION "materialize"."public"."kafka_connection" TO KAFKA (BROKER 'unique-jellyfish-0000.us-east-1.aws.confluent.cloud:9092', SASL MECHANISMS = 'PLAIN', SASL USERNAME = SECRET sasl_username, SASL PASSWORD = SECRET sasl_password)
```

--------------------------------

### Debug dbt Connection Configuration (Bash)

Source: https://materialize.com/docs/manage/dbt/get-started

Locates the dbt profiles.yml configuration directory. This is useful for verifying or creating the file needed to manage dbt connection profiles.

```bash
dbt debug --config-dir
```

--------------------------------

### Run DBT Tests

Source: https://materialize.com/docs/manage/dbt/get-started

Executes data tests in a DBT project. When 'store_failures' is enabled, this command creates materialized views for each test to continuously monitor assertions.

```shell
dbt test # use --select test_type:data to only run data tests!
```

--------------------------------

### Create Kafka Sink (SQL)

Source: https://materialize.com/docs/self-managed/v25.2/manage/dbt/get-started

Defines a Materialize sink to write data to a Kafka topic using the AVRO format with Confluent Schema Registry and Debezium envelope.

```sql
{{ config(materialized='sink') }}

FROM {{ ref('materialized_view_a') }}
INTO KAFKA CONNECTION kafka_connection (TOPIC 'topic_c')
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_connection
ENVELOPE DEBEZIUM
Copy

```

--------------------------------

### Update Helm Repositories

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

Updates the local Helm repository cache to fetch the latest chart information. This ensures you have access to the most recent versions of the metrics-server chart.

```bash
helm repo update metrics-server
```

--------------------------------

### Sample Data from Flipping Activities View

Source: https://materialize.com/docs/get-started/quickstart

A simple SQL `SELECT` statement to retrieve a sample of 10 rows from the `flip_activities` view, useful for verifying the view's output.

```sql
SELECT * FROM flip_activities LIMIT 10;
Copy
```

--------------------------------

### View Sample Flipping Activities

Source: https://materialize.com/docs/self-managed/v25.2/get-started/quickstart

Retrieves a sample of 10 rows from the `flip_activities` view to inspect the detected auction flipping activities. This command is used to verify the data generated by the `flip_activities` view.

```sql
SELECT * FROM flip_activities LIMIT 10;
```

--------------------------------

### Define PostgreSQL Source and Subsources

Source: https://materialize.com/docs/manage/dbt/get-started

Creates a PostgreSQL source in Materialize, reading from a specified publication. Materialize automatically creates subsources for each table. This example shows how to define these subsources in a dbt YAML file for dbt to recognize them.

```yaml
sources:
  - name: pg
    schema: "{{ target.schema }}"
    tables:
      - name: table_a
      - name: table_b

```

--------------------------------

### Create a basic Materialized View in Materialize

Source: https://materialize.com/docs/manage/dbt/get-started

Configures a dbt model to be materialized as a materialized view in Materialize, ensuring that results are incrementally updated and stored durably.

```sql
{{ config(materialized='materialized_view') }}

SELECT
    col_a, ...
FROM {{ ref('view_a') }}

```

--------------------------------

### Create Source with Load Generator in Materialize SQL

Source: https://materialize.com/docs/self-managed/v25.2/get-started/quickstart

Defines a new source named 'auction_house' using Materialize's built-in 'AUCTION' load generator. It's configured to emit data every second and starts from a specific point.

```sql
CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION
(
  TICK INTERVAL '1s',
  AS OF 100000
)
FOR ALL TABLES;
```

--------------------------------

### Materialize Connection Profile Configuration (YAML)

Source: https://materialize.com/docs/manage/dbt/get-started

Defines connection profiles for Materialize within the profiles.yml file. It includes configurations for development ('dev') and production ('prod') environments, specifying connection details like host, port, user, and database.

```yaml
default:
  outputs:

    prod:
      type: materialize
      threads: 1
      host: <host>
      port: 6875
      # Materialize user or service account (recommended)
      # to connect as
      user: <user@domain.com>
      pass: <password>
      database: materialize
      schema: public
      # optionally use the cluster connection
      # parameter to specify the default cluster
      # for the connection
      cluster: <prod_cluster>
      sslmode: require
    dev:
      type: materialize
      threads: 1
      host: <host>
      port: 6875
      user: <user@domain.com>
      pass: <password>
      database: <dev_database>
      schema: <dev_schema>
      cluster: <dev_cluster>
      sslmode: require

  target: dev
```

--------------------------------

### Materialize SQL Example: Creating Resources for EXPLAIN ANALYZE

Source: https://materialize.com/docs/self-managed/v25.2/sql/explain-analyze

This set of SQL statements demonstrates how to create a source, a materialized view, and an index in Materialize. These objects are then used in subsequent examples to illustrate the output of the `EXPLAIN ANALYZE` command.

```sql
CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION
(TICK INTERVAL '1s', AS OF 100000) 
FOR ALL TABLES;

CREATE VIEW winning_bids AS
  SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
    FROM auctions AS a
    JOIN bids AS b
      ON a.id = b.auction_id
   WHERE b.bid_time < a.end_time
     AND mz_now() >= a.end_time
   ORDER BY a.id, b.amount DESC, b.bid_time, b.buyer;

CREATE INDEX wins_by_item ON winning_bids (item);
```

--------------------------------

### Configure persist_docs in dbt_project.yml

Source: https://materialize.com/docs/manage/dbt/get-started

Enables documentation persistence for all models by adding the `persist_docs` configuration to the `models` property in your `dbt_project.yml` file. This ensures both relation and column descriptions are saved.

```yaml
models:
  +persist_docs:
    relation: true
    columns: true
```

--------------------------------

### Install libpq on macOS using Homebrew (Shell)

Source: https://materialize.com/docs/integrations/sql-clients

Installs the libpq library, which includes the psql client, on macOS using the Homebrew package manager.

```shell
brew install libpq
```

--------------------------------

### Create View for Auction Flipping Activities

Source: https://materialize.com/docs/get-started/quickstart

Defines a SQL view named `flip_activities` to identify users who buy an item and resell it at a higher price within 8 days. It joins the `winning_bids` table with itself to compare purchase and sale records.

```sql
CREATE VIEW flip_activities AS
SELECT w2.seller as flipper_id,
       w2.item AS item,
       w2.amount AS sold_amount,
       w1.amount AS purchased_amount,
       w2.amount - w1.amount AS diff_amount,
       datediff('days', w2.bid_time, w1.bid_time) AS timeframe_days
 FROM  winning_bids AS w1
   JOIN winning_bids AS w2
     ON w1.buyer = w2.seller   -- Buyer and seller are the same
        AND w1.item = w2.item  -- Item is the same
 WHERE w2.amount > w1.amount   -- But sold at a higher price
   AND datediff('days', w2.bid_time, w1.bid_time) < 8;
Copy
```

--------------------------------

### Create Kafka Source

Source: https://materialize.com/docs/manage/dbt/get-started

Defines a Kafka source in Materialize using dbt. It specifies the Kafka connection, topic, and uses Confluent Schema Registry for Avro format. Requires a pre-existing Kafka connection and Confluent Schema Registry connection.

```sql
{{ config(materialized='source') }}

FROM KAFKA CONNECTION kafka_connection (TOPIC 'topic_a')
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_connection

```

--------------------------------

### Query Sample Row from 'bids' in Materialize

Source: https://materialize.com/docs/get-started/quickstart

Fetches a single sample row from the 'bids' table in Materialize, allowing users to examine the bid data.

```sql
SELECT * FROM bids LIMIT 1;
```

--------------------------------

### Specify Database for Model Creation (SQL)

Source: https://materialize.com/docs/self-managed/v25.2/manage/dbt/get-started

Assigns a model (source, view, materialized_view, sink) to a specific database. If not specified, the default database for the connection is used.

```sql
{{ config(materialized='materialized_view', database='database_a') }}
Copy

```

--------------------------------

### Create Kafka Sink (SQL)

Source: https://materialize.com/docs/manage/dbt/get-started

Defines a Materialize sink to write data to an external Kafka topic, specifying connection details, topic name, format, and schema registry integration.

```sql
{{ config(materialized='sink') }}

FROM {{ ref('materialized_view_a') }}
INTO KAFKA CONNECTION kafka_connection (TOPIC 'topic_c')
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION csr_connection
ENVELOPE DEBEZIUM
Copy
```

```sql
database.schema.kafka_topic_c

Copy
```

--------------------------------

### Get Server Start Time - SQL

Source: https://materialize.com/docs/sql/functions

Returns the timestamp when the Materialize server process started. This function is unmaterializable.

```sql
pg_postmaster_start_time() -> timestamptz
```

--------------------------------

### Example Output of SHOW SOURCES

Source: https://materialize.com/docs/sql/show-sources

This example demonstrates the typical tabular output format of the SHOW SOURCES command, displaying the name, type, and associated cluster for each source.

```SQL
name               | type     | cluster
-------------------+----------+--------
my_kafka_source    | kafka    | c1
my_postgres_source | postgres | c2
```

--------------------------------

### Get Server Start Time

Source: https://materialize.com/docs/self-managed/v25.2/sql/functions

Returns the timestamp when the Materialize server process was started. Note: This function is unmaterializable.

```SQL
pg_postmaster_start_time() -> timestamptzCopy
```

--------------------------------

### Subscribe to Fund Movement View

Source: https://materialize.com/docs/get-started/quickstart

Subscribes to the 'funds_movement' view to stream real-time updates. This allows monitoring the total credits, total debits, and their difference as new data arrives, ensuring consistency.

```sql
SUBSCRIBE TO (
   SELECT *
   FROM funds_movement
);
```

--------------------------------

### Example Materialize CLI Configuration (mz.toml)

Source: https://materialize.com/docs/self-managed/v25.2/integrations/cli/configuration

An example of the `mz.toml` configuration file, demonstrating how to set global parameters and define multiple authentication profiles with their respective credentials and regions.

```toml
# Activate the "acme-corp" authentication profile by default.
profile = "acme-corp"

# Store app passwords directly in the configuration file.
vault = "inline"

# Profile for Acme Corp.
[profiles.acme-corp]
# The app password that the CLI will use to authenticate.
app-password = "mzp_fg91g4fslgq329023..."
# The default region to use for the Acme Corp profile.
region = "aws/us-east-1"

# Profile for Hooli.
[profiles.hooli]
app-password = "mzp_a48df1039ecb2d94c..."
region = "aws/eu-west-1"

```

--------------------------------

### Create Webhook Source

Source: https://materialize.com/docs/manage/dbt/get-started

Defines a webhook source in Materialize using dbt. It specifies the body format as JSON and includes a check for authorization using a secret, ensuring secure reception of data.

```sql
{{ config(materialized='source') }}

FROM WEBHOOK
    BODY FORMAT JSON
    CHECK (
      WITH (
        HEADERS,
        BODY AS request_body,
        -- Make sure to fully qualify the secret if it isn't in the same
        -- namespace as the source!
        SECRET basic_hook_auth
      )
      constant_time_eq(headers->'authorization', basic_hook_auth)
    );

```

--------------------------------

### Get Operator Example

Source: https://materialize.com/docs/self-managed/v25.2/sql/explain-plan

Shows the Get operator, used to retrieve rows from existing relations like tables, views, or CTEs.

```sql
Get materialize.public.ordered
```

--------------------------------

### Show Create View Example - SQL

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-create-view

An example of how to use the `SHOW CREATE VIEW` command to retrieve the creation statement for a view named 'my_view'.

```sql
SHOW CREATE VIEW my_view;
```

--------------------------------

### Run mz CLI with Docker

Source: https://materialize.com/docs/integrations/cli/installation

Runs the Materialize CLI (mz) using the official Docker image. It mounts the local ~/.mz directory to the container to persist configuration and authentication tokens.

```bash
docker run -v $HOME/.mz:/root/.mz materialize/mz [args...]
```

--------------------------------

### Define PostgreSQL Source in dbt

Source: https://materialize.com/docs/self-managed/v25.2/manage/dbt/get-started

Demonstrates how to define a PostgreSQL source in dbt using a `.yml` file. This approach is a workaround for handling subsources by explicitly listing tables. It also shows a dbt model that depends on these subsources.

```sql
{{ config(materialized='source') }}

FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
FOR ALL TABLES

```

```yaml
sources:
  - name: pg
    schema: "{{ target.schema }}"
    tables:
      - name: table_a
      - name: table_b

```

```sql
-- depends_on: {{ ref('pg') }}
{{ config(materialized='view') }}

SELECT
    table_a.foo AS foo,
    table_b.bar AS bar
FROM {{ source('pg','table_a') }}
INNER JOIN
     {{ source('pg','table_b') }}
    ON table_a.id = table_b.foo_id

```

--------------------------------

### Create View to Flag Auction Flippers

Source: https://materialize.com/docs/self-managed/v25.2/get-started/quickstart

Defines a SQL view named `flippers` that consolidates identified flippers. It combines users with more than two flipping activities (as detected by `flip_activities`) and users listed in the `known_flippers` table, providing a unified list of potential flippers.

```sql
CREATE VIEW flippers AS
 SELECT flipper_id
 FROM (
     SELECT flipper_id
     FROM flip_activities
     GROUP BY flipper_id
     HAVING count(*) >= 2

     UNION ALL

     SELECT flipper_id
     FROM known_flippers
 );
```

--------------------------------

### Create Index on Winning Bids View (SQL)

Source: https://materialize.com/docs/get-started/quickstart

Creates an index named 'wins_by_item' on the 'winning_bids' view, specifically on the 'item' column. This optimizes point lookups and ensures up-to-date results are readily available.

```sql
CREATE INDEX wins_by_item ON winning_bids (item);
```

--------------------------------

### SUBSTRING Example: Extracting from a starting position

Source: https://materialize.com/docs/sql/functions/substring

Demonstrates how to use the SUBSTRING function to extract a substring starting from a specified position until the end of the string.

```sql
SELECT substring('abcdefg', 3) AS substr;
```

--------------------------------

### Example Output of SHOW SINKS - SQL

Source: https://materialize.com/docs/sql/show-create-sink

This snippet displays a sample output from the SHOW SINKS command, listing the names of the available sinks. This helps in selecting the correct sink for the SHOW CREATE SINK command.

```sql
     name
--------------
 my_view_sink
```

--------------------------------

### Example Materialize CLI TOML Configuration

Source: https://materialize.com/docs/integrations/cli/configuration

An example TOML file demonstrating the configuration structure for the Materialize CLI, including global settings and multiple authentication profiles.

```toml
# Activate the "acme-corp" authentication profile by default.
profile = "acme-corp"

# Store app passwords directly in the configuration file.
vault = "inline"

# Profile for Acme Corp.
[profiles.acme-corp]
# The app password that the CLI will use to authenticate.
app-password = "mzp_fg91g4fslgq329023..."
# The default region to use for the Acme Corp profile.
region = "aws/us-east-1"

# Profile for Hooli.
[profiles.hooli]
app-password = "mzp_a48df1039ecb2d94c..."
region = "aws/eu-west-1"
```

--------------------------------

### Extract substring from starting position

Source: https://materialize.com/docs/self-managed/v25.2/sql/functions/substring

Example of using the SUBSTRING function to extract a substring from a given string, starting from a specified position until the end of the string. It shows the SQL query and its result.

```sql
SELECT substring('abcdefg', 3) AS substr;

```

```sql
 substr
--------
 cdefg

```

--------------------------------

### Query Winning Bids by Item (SQL)

Source: https://materialize.com/docs/get-started/quickstart

Filters and retrieves the top 10 winning bids for a specific item ('Best Pizza in Town') from the 'winning_bids' view, ordered by bid time.

```sql
SELECT * FROM winning_bids
WHERE item = 'Best Pizza in Town'
ORDER BY bid_time DESC
LIMIT 10;
```

--------------------------------

### Test dbt Connection (Bash)

Source: https://materialize.com/docs/manage/dbt/get-started

Tests the active dbt connection profile to ensure that dbt can successfully connect to the configured Materialize instance. A successful connection will output 'All checks passed!'.

```bash
dbt debug
```

--------------------------------

### Example output for SHOW SOURCES

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-sources

An example of the output generated by the `SHOW SOURCES` command, illustrating typical source entries with their names, types, and cluster associations.

```sql
name               | type     | cluster
--------------------+----------+---------
 my_kafka_source    | kafka    | c1
 my_postgres_source | postgres | c2
```

--------------------------------

### Label kind Node (Shell)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

Applies specific Kubernetes labels to the 'kind' control-plane node. These labels, `materialize.cloud/disk=true` and `workload=materialize-instance`, are required for Materialize to function correctly within the cluster.

```shell
MYNODE=$(kubectl get nodes --no-headers | awk '{print $1}')
kubectl label node  $MYNODE materialize.cloud/disk=true
kubectl label node  $MYNODE workload=materialize-instance
```

--------------------------------

### Show Create Source Statement Example

Source: https://materialize.com/docs/sql/show-create-source

This example demonstrates how to use the `SHOW CREATE SOURCE` command to retrieve the DDL for a specific source named 'market_orders_raw'. It shows the expected output format, including the source's name and its creation SQL.

```sql
SHOW CREATE SOURCE market_orders_raw;
```

```sql
                 name                 |                                      create_sql
--------------------------------------+--------------------------------------------------------------------------------------------------------------
 materialize.public.market_orders_raw | CREATE SOURCE "materialize"."public"."market_orders_raw" IN CLUSTER "c" FROM LOAD GENERATOR COUNTER
```

--------------------------------

### Install libpq on macOS

Source: https://materialize.com/docs/self-managed/v25.2/integrations/sql-clients

Installs the libpq library, which provides the necessary client binaries for PostgreSQL, including psql. This is done using the Homebrew package manager on macOS.

```bash
brew install libpq
```

```bash
brew link --force libpq
```

--------------------------------

### Create Source with Load Generator in Materialize

Source: https://materialize.com/docs/get-started/quickstart

Defines a new source named 'auction_house' using Materialize's sample 'AUCTION' load generator, configured to emit data every second.

```sql
CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION
(TICK INTERVAL '1s', AS OF 100000)
FOR ALL TABLES;
```

--------------------------------

### Speed up Filtering with Indexes

Source: https://materialize.com/docs/self-managed/v25.2/sql/create-index

This example shows how to create indexes to speed up filtering operations. It includes creating an index on a single column ('guid'), an index using an expression ('upper(guid)'), and an index using multiple fields ('upper(guid)', 'geo_id'). These indexes improve performance when filtering by literal values or expressions.

```sql
CREATE MATERIALIZED VIEW active_customers AS
    SELECT guid, geo_id, last_active_on
    FROM customer_source
    GROUP BY geo_id;

CREATE INDEX active_customers_idx ON active_customers (guid);

-- This should now be very fast!
SELECT * FROM active_customers WHERE guid = 'd868a5bf-2430-461d-a665-40418b1125e7';

-- Using indexed expressions:
CREATE INDEX active_customers_exp_idx ON active_customers (upper(guid));
SELECT * FROM active_customers WHERE upper(guid) = 'D868A5BF-2430-461D-A665-40418B1125E7';

-- Filter using an expression in one field and a literal in another field:
CREATE INDEX active_customers_exp_field_idx ON active_customers (upper(guid), geo_id);
SELECT * FROM active_customers WHERE upper(guid) = 'D868A5BF-2430-461D-A665-40418B1125E7' and geo_id = 'ID_8482';
```

--------------------------------

### Install mz CLI (macOS/Debian)

Source: https://materialize.com/docs/integrations/cli

Provides installation instructions for the Materialize CLI ('mz') on macOS using Homebrew and on Ubuntu/Debian using a shell script and apt.

```shell
# On macOS:
brew install materializeinc/materialize/mz
# On Ubuntu/Debian:
curl -fsSL https://dev.materialize.com/apt/materialize.sources | sudo tee /etc/apt/sources.list.d/materialize.sources
sudo apt update
sudo apt install materialize-cli
```

--------------------------------

### Show Create Cluster Output Example

Source: https://materialize.com/docs/sql/show-create-cluster

This snippet provides an example of the output returned by the SHOW CREATE CLUSTER command. It includes the cluster name and the full CREATE CLUSTER SQL statement with its configuration parameters.

```sql
    name          |    create_sql
------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 c                | CREATE CLUSTER "c" (INTROSPECTION DEBUGGING = false, INTROSPECTION INTERVAL = INTERVAL '00:00:01', MANAGED = true, REPLICATION FACTOR = 1, SIZE = '100cc', SCHEDULE = MANUAL)
```

--------------------------------

### jsonb_agg Example with FILTER Clause

Source: https://materialize.com/docs/self-managed/v25.2/sql/functions/jsonb_agg

Demonstrates the usage of the jsonb_agg function with a FILTER clause to aggregate values that meet a specific condition (content starting with 'h'). The example uses a VALUES clause to create a temporary table.

```sql
SELECT
  jsonb_agg(t) FILTER (WHERE t.content LIKE 'h%')
    AS my_agg
FROM (
  VALUES
  (1, 'hey'),
  (2, NULL),
  (3, 'hi'),
  (4, 'salutations')
  ) AS t(id, content);
```

--------------------------------

### Navigate to Materialize Terraform Examples

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-gcp/upgrade-on-gcp

Changes the current directory to the simple example folder within the Materialize Terraform repository.

```bash
cd terraform-google-materialize/examples/simple
```

--------------------------------

### Materialize Key-Value Load Generator Example

Source: https://materialize.com/docs/sql/create-source/load-generator

Shows an example of creating a 'KEY VALUE' load generator in Materialize. This generator is suitable for key-value data. The example includes options for defining keys and value size.

```sql
CREATE SOURCE kv_source
FROM KEY VALUE
WITH OPTIONS (
  "KEYS" = 'id',
  "VALUE SIZE" = '100'
);
```

--------------------------------

### Create View for Fund Movement Verification

Source: https://materialize.com/docs/get-started/quickstart

Creates a SQL view named 'funds_movement' to verify data consistency by calculating total credits, total debits, and their difference. This view aggregates data from 'seller_credits' and 'buyer_debits' views.

```sql
CREATE VIEW funds_movement AS
SELECT SUM(credits) AS total_credits,
       SUM(debits) AS total_debits,
       SUM(credits) - SUM(debits) AS total_difference
FROM (
  SELECT SUM(credits) AS credits, 0 AS debits
  FROM seller_credits

  UNION

  SELECT 0 AS credits, SUM(debits) AS debits
  FROM buyer_debits
);
```

--------------------------------

### CREATE TABLE Example

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-create-table

An example of creating a simple table named 't' with an integer column 'a' and a non-null text column 'b'. This serves as a prerequisite for demonstrating SHOW CREATE TABLE.

```sql
CREATE TABLE t (a int, b text NOT NULL);
```

--------------------------------

### Create View for Seller Credits

Source: https://materialize.com/docs/get-started/quickstart

Creates a SQL view named 'seller_credits' to calculate the total credit amount for each seller from completed auctions. It groups winning bids by seller and sums their amounts.

```sql
CREATE VIEW seller_credits AS
SELECT seller, SUM(amount) as credits
FROM winning_bids
GROUP BY seller;
```

--------------------------------

### Delete Kubernetes Cluster

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-kind

This command deletes the entire local Kubernetes deployment, including all Materialize instances and associated data.

```shell
kind delete cluster
```

--------------------------------

### SUBSTRING Example: Extracting with a specific length

Source: https://materialize.com/docs/sql/functions/substring

Illustrates how to use the SUBSTRING function to extract a substring of a specified length starting from a given position.

```sql
SELECT substring('abcdefg', 3, 3) AS substr;
```

--------------------------------

### Install mz-debug on Linux

Source: https://materialize.com/docs/integrations/mz-debug

Installs the mz-debug CLI tool on Linux by downloading the latest binary, extracting it, and placing it in /usr/local. It automatically detects the system architecture.

```shell
ARCH=$(uname -m)
sudo echo "Preparing to extract mz-debug..."
curl -L "https://binaries.materialize.com/mz-debug-latest-$ARCH-unknown-linux-gnu.tar.gz" \
| sudo tar -xzC /usr/local --strip-components=1
```

--------------------------------

### Specify Cluster for Model Creation (SQL)

Source: https://materialize.com/docs/manage/dbt/get-started

Configures a Materialize model (materialized_view, source, sink, or index) to be created in a specific cluster.

```sql
{{ config(materialized='materialized_view', cluster='cluster_a') }}
Copy
```

--------------------------------

### Example: Showing Databases and Schemas

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-schemas

Demonstrates the output of `SHOW DATABASES` and `SHOW SCHEMAS FROM my_db`. This provides context for how schemas are organized within databases.

```sql
SHOW DATABASES;
-- Output:
--   name
-- -----------
-- materialize
-- my_db

SHOW SCHEMAS FROM my_db;
-- Output:
--   name
-- --------
--  public
```

--------------------------------

### Materialize SHOW SCHEMAS Example

Source: https://materialize.com/docs/sql/show-objects

An example demonstrating the SHOW SCHEMAS command in Materialize to list available schemas. This is often used before querying objects within a specific schema.

```sql
SHOW SCHEMAS;
-- Output:
--   name
-- --------
--  public
```

--------------------------------

### Show Create Connection Example

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-create-connection

An example of using the SHOW CREATE CONNECTION statement to retrieve the creation DDL for a connection named 'kafka_connection'.

```sql
SHOW CREATE CONNECTION kafka_connection;
```

--------------------------------

### Preview Model Results with dbt Show

Source: https://materialize.com/docs/self-managed/v25.2/manage/dbt/development-workflows

Shows how to use the `dbt show` command to preview model results without full materialization, including an example output. Notes performance limitations with Materialize's `LIMIT` clause.

```bash
dbt show --select "model_name.sql"
```

```text
23:02:20  Running with dbt=1.7.7
23:02:20  Registered adapter: materialize=1.7.3
23:02:20  Found 3 models, 1 test, 4 seeds, 1 source, 0 exposures, 0 metrics, 430 macros, 0 groups, 0 semantic models
23:02:20
23:02:23  Previewing node 'model_name':
| col                  |
| -------------------- |
| value1               |
| value2               |
| value3               |
| value4               |
| value5               |
```

--------------------------------

### Materialize Cluster Creation Example

Source: https://materialize.com/docs/sql/create-cluster

This example demonstrates how to create a Materialize cluster with a specified size and replication factor. It includes a sequence of events showing how credit usage is calculated based on replica provisioning and deprovisioning.

```SQL
CREATE CLUSTER c (SIZE '400cc', REPLICATION FACTOR 2)
ALTER CLUSTER c SET (REPLICATION FACTOR 1)
DROP CLUSTER c
```

--------------------------------

### Kubernetes Port Forwarding with Error Handling (Bash)

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-local-minikube

This Bash command sets up port forwarding for a Kubernetes service, automatically restarting it if an error related to 'portforward.go' is detected. It uses a loop to ensure a stable connection and pipes output for logging.

```bash
kubectl port-forward svc/$MZ_SVC_CONSOLE 8080:8080 -n materialize-environment 2>&1 | tee /dev/stderr |
     grep -q "portforward.go" && echo "Restarting port forwarding due to an error." || break;
  done;)
```

--------------------------------

### GCP Materialize Local SSD Configuration Example

Source: https://materialize.com/docs/self-managed/v25.2/installation/install-on-gcp/appendix-deployment-guidelines

Illustrates the calculation of minimum local SSD counts for Materialize on GCP to maintain a 2:1 disk-to-RAM ratio, showing example machine types, RAM, required disk space, and total SSD storage.

```text
Machine Type | RAM | Required Disk | Minimum Local SSD Count | Total SSD Storage  
---|---|---|---|---
`n2-highmem-8` | `64GB` | `128GB` | 1 | `375GB`  
`n2-highmem-16` | `128GB` | `256GB` | 1 | `375GB`  
`n2-highmem-32` | `256GB` | `512GB` | 2 | `750GB`  
`n2-highmem-64` | `512GB` | `1024GB` | 3 | `1125GB`  
`n2-highmem-80` | `640GB` | `1280GB` | 4 | `1500GB`
```

--------------------------------

### SHOW CREATE TABLE Output Example

Source: https://materialize.com/docs/self-managed/v25.2/sql/show-create-table

Illustrates the typical output format when executing SHOW CREATE TABLE. It shows the table name and the detailed SQL CREATE TABLE statement, including column types and constraints.

```sql
         name         |                                             create_sql
----------------------+-----------------------------------------------------------------------------------------------------
 materialize.public.t | CREATE TABLE "materialize"."public"."t" ("a" "pg_catalog"."int4", "b" "pg_catalog"."text" NOT NULL)
```