### Install Dependencies and Start Dev Server

Source: https://github.com/materializeinc/materialize/blob/main/console/CLAUDE.md

Use these commands to install project dependencies and start the local development server. The `yarn start` command points to the staging cloud by default, while `DEFAULT_STACK=local yarn start` uses a local cloud stack.

```bash
corepack enable
yarn install

yarn start

DEFAULT_STACK=local yarn start
```

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

### Install and Run Development Server

Source: https://github.com/materializeinc/materialize/blob/main/console/AGENTS.md

Install project dependencies and start the local development server. The default server points to the staging cloud.

```bash
corepack enable
yarn install
yarn start
```

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

### Example: Default privileges for tables created by blue.berry

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/security/cloud/access-control/manage-roles.md

This example demonstrates setting default privileges for tables created by the 'blue.berry' role. It includes setup for verification.

```SQL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO view_manager;
```

```SQL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO data_reader;
```

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

### Agent-optimized help commands

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/mz-deploy/agent-setup.md

Use these commands to get detailed guides for mz-deploy commands, including behavior notes, examples, and error recovery steps.

```bash
mz-deploy help <command>    # Detailed guide for a single command
```

```bash
mz-deploy help --all        # All command guides concatenated
```

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

### Install kind on Linux

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/cloudtest.md

Installs kind on Linux by downloading the binary and making it executable.

```shell
curl -fL https://kind.sigs.k8s.io/dl/v0.29.0/kind-linux-amd64 > kind
chmod +x kind
sudo mv kind /usr/local/bin
```

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

### Install k6

Source: https://github.com/materializeinc/materialize/blob/main/console/doc/guide-testing.md

Install the k6 load testing tool using Homebrew.

```shell
brew install k6
```

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

### Install kubectl on Linux

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/cloudtest.md

Installs kubectl on Linux by downloading the binary and making it executable.

```shell
curl -fL https://dl.k8s.io/release/v1.34.5/bin/linux/amd64/kubectl > kubectl
chmod +x kubectl
sudo mv kubectl /usr/local/bin
```

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

### Render SQL Example with Description and Results

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/layouts/shortcodes/include-example.html

This shortcode renders a Materialize SQL example, including its description and results. It processes a file path to locate the example and can optionally indent the code.

```html
{{- $pathArray := split (lower (.Get "file")) "/" -}} {{- $data := .Site.Data -}} {{- range $pathArray }} {{- $data = index $data . -}} {{- end }} {{- $example := .Get "example" -}} {{- $indent := .Get "indent" -}} {{- range $data }} {{- if eq .name $example -}} {{- if .description -}} {{ .description | markdownify -}} {{- end -}} {{- if .code -}} {{- $code := .code -}} {{- if $indent }} {{- $code = replaceRE "(?m)^" " " $code -}} {{- end }} {{ if $indent }} {{ end -}}
```mzsql {{ $code | safeHTML }} {{ if $indent }} {{ end -}}
``` {{- end -}} {{- if .results }} {{ if $indent }} {{ end -}}{{ .results | markdownify -}} {{- end -}} {{- end -}} {{- end -}}
```

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

### Quickstart: Launch Materialize MCP Server

Source: https://github.com/materializeinc/materialize/blob/main/misc/mcp-materialize/README.md

Launches the server with default settings. This command immediately exposes any indexed views as tools.

```bash
uv run mcp-materialize
```

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

### Example 2: SUBSTRING with start position and length

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/functions/substring.md

This example demonstrates extracting a substring by specifying both the starting position and the desired length.

```APIDOC
## Example 2: SUBSTRING with start position and length

### Description
Extracts a substring starting from the 3rd character with a length of 3 characters.

### Request Example
```mzsql
SELECT substring('abcdefg', 3, 3) AS substr;
```

### Response Example
```nofmt
 substr
--------
 cde
```
```

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

### Get Help for optbench Commands

Source: https://github.com/materializeinc/materialize/blob/main/misc/python/materialize/optbench/README.md

View available options for the `init` and `run` commands by appending `--help`.

```bash
bin/optbench init --help
```

```bash
bin/optbench run --help
```

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

### Example 1: SUBSTRING with start position

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/functions/substring.md

This example shows how to extract a substring starting from a specific position without defining the length.

```APIDOC
## Example 1: SUBSTRING with start position

### Description
Extracts a substring starting from the 3rd character to the end of the string.

### Request Example
```mzsql
SELECT substring('abcdefg', 3) AS substr;
```

### Response Example
```nofmt
 substr
--------
 cdefg
```
```

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

### Install and start CockroachDB on macOS

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/guide.md

Installs a forked version of CockroachDB using Homebrew and starts it as a service. This is recommended for running the metadata store locally on macOS.

```shell
brew install materializeinc/cockroach/cockroach
brew services start cockroach
```

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

### Create Index and View for Examples

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/explain-analyze.md

These SQL statements set up the necessary source, view, and index for the subsequent EXPLAIN ANALYZE examples.

```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);
```

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

### Run All Documentation Examples

Source: https://github.com/materializeinc/materialize/blob/main/test/doc-examples/README.md

Execute all documentation examples recursively using the `mzcompose` tool. This ensures comprehensive testing of the documentation against the current syntax and expected results.

```bash
bin/mzcompose --find doc-examples run default
```

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

### Install mz CLI

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/integrations/cli/_index.md

Install the Materialize CLI using Homebrew on macOS or apt on Ubuntu/Debian. Refer to the installation guide for other options.

```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
```

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

### Install and run LocalStack

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/testdrive.md

Installs LocalStack and starts the necessary services for AWS interaction in testdrive tests.

```bash
pip install localstack
localstack start
```

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

### Example: Setting Profiles Directory via Environment Variable

Source: https://github.com/materializeinc/materialize/blob/main/src/mz-deploy/src/cli/help/profiles.md

Shows how to configure the profiles directory by setting the `MZ_DEPLOY_PROFILES_DIR` environment variable.

```bash
# Or set via environment variable
export MZ_DEPLOY_PROFILES_DIR=/path/to/config
mz-deploy profiles
```

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

### Deploy Project: Setup

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/mz-deploy/get-started.md

Initialize deployment tracking tables, the deployment server cluster, and RBAC roles if enabled. This is a one-time setup step.

```bash
mz-deploy setup
```

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

### Show Create Connection Example

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/show-create-connection.md

This example demonstrates how to use `SHOW CREATE CONNECTION` to view the creation statement for a connection named `kafka_connection`.

```mzsql
SHOW CREATE CONNECTION kafka_connection;
```

```text
    name          |    create_sql
------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
kafka_connection | 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)
```

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

### Get mz-debug version

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/integrations/mz-debug/_index.md

Displays the installed version of the mz-debug tool.

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

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

### Create and Show Tables

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/drop-table.md

Demonstrates creating a table and then listing all tables to verify creation.

```mzsql
CREATE TABLE t (a int, b text NOT NULL);
SHOW TABLES;
```

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

### Install and Run Console Locally

Source: https://github.com/materializeinc/materialize/blob/main/console/README.md

Installs dependencies and starts the Materialize web console locally. Ensure you have the correct Node and Yarn versions as specified in package.json.

```bash
corepack enable
yarn install
yarn start
open http://localhost:3000
```

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

### Download Sample Configuration Files

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/layouts/shortcodes/self-managed/versions/curl-sample-files-local-install.html

Use these commands to download sample configuration files for Materialize, PostgreSQL, MinIO, and the Materialize Operator. These files are intended for local evaluation and testing only.

```shell
mz_version={{ $environmentd_version }} curl -o sample-values.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/operator/values.yaml
```

```shell
mz_version={{ $environmentd_version }} curl -o sample-postgres.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/postgres.yaml
```

```shell
mz_version={{ $environmentd_version }} curl -o sample-minio.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/minio.yaml
```

```shell
mz_version={{ $environmentd_version }} curl -o sample-materialize.yaml https://raw.githubusercontent.com/MaterializeInc/materialize/refs/tags/$mz_version/misc/helm-charts/testing/materialize.yaml
```

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

### Install oauth2c CLI tool on macOS

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/security/self-managed/sso.md

Use Homebrew to install the oauth2c CLI tool for fetching OIDC ID tokens. Refer to the official guide for other platforms.

```shell
brew install cloudentity/tap/oauth2c
```

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

### Create and Populate Example Table

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/update.md

These statements are used to set up the example table for subsequent UPDATE operations.

```sql
CREATE TABLE example_table (a int, b text);
INSERT INTO example_table VALUES (1, 'hello'), (2, 'goodbye');
```

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

### Run Specific Documentation Example

Source: https://github.com/materializeinc/materialize/blob/main/test/doc-examples/README.md

Run a specific documentation example by providing its file path to the `mzcompose` tool. This is useful for testing individual documentation fragments or when focusing on a particular feature.

```bash
bin/mzcompose --find doc-examples run default rbac-sm/alter_default_privileges.yml
```

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

### Start Confluent Platform Schema Registry

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/guide.md

Start the Confluent Platform services, including Schema Registry, ZooKeeper, and Kafka. This is necessary for testing Kafka sources and sinks against a local Kafka installation.

```shell
confluent local services schema-registry start  # Also starts ZooKeeper and Kafka.
```

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

### Install Confluent Platform on Debian-based Linux

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/guide.md

Installs Java and the Confluent Community Edition on Debian-based Linux distributions using APT. It also configures the CONFLUENT_HOME environment variable and starts the Confluent services.

```shell
curl http://packages.confluent.io/deb/8.2/archive.key | sudo apt-key add -

```

```shell
sudo add-apt-repository "deb [arch=amd64] https://packages.confluent.io/deb/8.2 stable main"

```

```shell
sudo apt update

```

```shell
sudo apt install openjdk-21-jre-headless confluent-community-2.13

```

```shell
echo export CONFLUENT_HOME=/ >> ~/.bashrc

```

```shell
source ~/.bashrc

```

```shell
confluent local services start
```

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

### Example Output of SHOW CREATE VIEW

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/show-create-view.md

This is an example of the output you might receive when executing `SHOW CREATE VIEW`. It shows the view name and the exact SQL used to create it.

```text
            name            |                                            create_sql
----------------------------+--------------------------------------------------------------------------------------------------
 materialize.public.my_view | CREATE VIEW "materialize"."public"."my_view" AS SELECT * FROM "materialize"."public"."my_source"
```

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

### Git Bisect Script Example

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/feature-benchmark.md

An example bash script to automate the git bisect process for identifying performance regressions. It starts the bisect, marks good and bad versions, and runs the benchmark script.

```bash
#!/usr/bin/env bash
THIS_SHA=$(git rev-parse HEAD)
GOOD_MZ_VERSION="vX.Y.Z"

pushd /path/to/test/feature-benchmark
./mzcompose down -v
./mzcompose run feature-benchmark --this-tag unstable-$THIS_SHA --other-tag $GOOD_MZ_VERSION --root-scenario=...
```

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

### Example SQL Connection Overrides

Source: https://github.com/materializeinc/materialize/blob/main/src/mz-deploy/src/cli/help/profiles.md

Demonstrates how to define different SQL connection configurations for staging and production environments using profile-specific file naming.

```sql
CREATE CONNECTION pg_conn TO POSTGRES (
    HOST 'prod-replica.internal',
    DATABASE 'app',
    ...
);
```

```sql
CREATE CONNECTION pg_conn TO POSTGRES (
    HOST 'staging-replica.internal',
    DATABASE 'app',
    ...
);
```

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

### Ingest Data from PostgreSQL

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/ingest-data/postgres/neon.md

Create a source in Materialize to begin ingesting data from your PostgreSQL database. This example assumes a cloud setup.

```mzsql
CREATE SOURCE pg_source
FROM POSTGRES
CONNECTION "pg_connection"
PUBLICATION "my_publication";
```

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

### Create Tables, Indexes, and Explain Query

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/transform-data/optimization.md

This snippet demonstrates creating sample tables and indexes, then using `EXPLAIN` to analyze the query plan for a join operation with a filter. It helps verify that specific indexes like `teachers_name` are used for lookups.

```mzsql
CREATE TABLE teachers (id INT, name TEXT);
CREATE TABLE sections (id INT, teacher_id INT, course_id INT, schedule TEXT);
CREATE TABLE courses (id INT, name TEXT);

CREATE INDEX pk_teachers ON teachers (id);
CREATE INDEX teachers_name ON teachers (name);
CREATE INDEX sections_fk_teachers ON sections (teacher_id);
CREATE INDEX pk_courses ON courses (id);
CREATE INDEX sections_fk_courses ON sections (course_id);

EXPLAIN
  SELECT
      t.name AS teacher_name,
      s.schedule,
      c.name AS course_name
  FROM teachers t
  INNER JOIN sections s ON t.id = s.teacher_id
  INNER JOIN courses c ON c.id = s.course_id
  WHERE t.name = 'Escalante';
```

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

### Project Configuration Example (project.toml)

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/mz-deploy/project-structure.md

Shows the structure of the `project.toml` file for configuring Materialize version and dependencies.

```toml
# project.toml
# mz_version = "v1.0"
# dependencies = ["external/dependency"]

```

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

### Start Environmentd with System Parameter

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/tracing.md

Configure the OpenTelemetry filter on startup by passing system parameters to the `environmentd` command. This is useful for initial setup or when resetting the environment.

```bash
./bin/environmentd --reset --monitoring -- --system-parameter-default='opentelemetry_filter=debug'
```

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

### Prepare and Execute a Statement

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/execute.md

This example demonstrates how to prepare a simple SQL statement with a parameter and then execute it using the `EXECUTE` command, providing a value for the parameter.

```mzsql
PREPARE a AS SELECT 1 + $1;
EXECUTE a (2);
```

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

### Get public keys for SSH bastion host

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/ingest-data/postgres/alloydb.md

Retrieve public keys required for connecting to an SSH bastion host. This is part of the SSH tunnel setup.

```bash
ssh-keyscan ssh_host
```

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

### Install and Launch MCP Server

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/integrations/mcp-server/llm.md

Install the MCP Materialize agents and launch the server using uv.

```bash
uv venv
uv pip install mcp-materialize-agents
uv run mcp_materialize_agents
```

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

### Claude Code setup script for mz-deploy

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/mz-deploy/agent-setup.md

This Bash script installs mz-deploy in a Claude Code web sandbox. It downloads the latest version for the detected architecture and places it in /usr/local/bin.

```bash
#!/bin/bash
set -euo pipefail
ARCH=$(uname -m)
curl -L "https://binaries.materialize.com/mz-deploy-latest-$ARCH-unknown-linux-gnu.tar.gz" \
| tar -xzC /usr/local --strip-components=1
```

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

### LENGTH function usage

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/functions/length.md

Demonstrates how to use the LENGTH function to get the number of characters in a string. The first example uses the default UTF-8 encoding, and the second specifies 'big5' encoding.

```APIDOC
## LENGTH function

### Description
Returns the number of characters (code points) in a string using a specified or default encoding.

### Parameters
#### Path Parameters
- `_str_` (string or bytea) - Required - The string whose length you want to calculate.
- `_encoding_name_` (string) - Optional - The encoding to use for calculating the string's length. Defaults to UTF-8.

### Return Value
- `int` - The number of code points in the string.

### Examples
```sql
-- Example 1: Using default UTF-8 encoding
SELECT length('你好') AS len;
-- Expected output:
--  len
-- -----
--   2

-- Example 2: Specifying 'big5' encoding
SELECT length('你好', 'big5') AS len;
-- Expected output:
--  len
-- -----
--   3
```

### Errors
- Returns `NULL` if the `_encoding_name_` is not available.
- Returns `NULL` if a byte sequence in `_str_` is not compatible with the selected encoding.
```

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

### Example: Show Create Cluster

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/show-create-cluster.md

This example demonstrates how to retrieve the creation DDL for a cluster named 'c'. The output shows the cluster name and its corresponding `CREATE CLUSTER` statement.

```sql
SHOW CREATE CLUSTER c;
```

```text
    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)
```

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

### Mocking Materialize API with Mock Service Worker

Source: https://github.com/materializeinc/materialize/blob/main/console/doc/guide-testing.md

Example of using `buildSqlQueryHandlerV2` and `mapKyselyToTabular` to mock Materialize API responses in integration tests. This setup is useful for testing interactions with the Materialize API.

```typescript
server.use(
  buildSqlQueryHandlerV2({
    queryKey: secretQueryKeys.list(),
    results: mapKyselyToTabular({
      rows: [],
      columns: useSecretsListColumns,
    }),
  }),
);
```

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

### Example: Specifying Profiles Directory

Source: https://github.com/materializeinc/materialize/blob/main/src/mz-deploy/src/cli/help/profiles.md

Demonstrates how to explicitly set the directory for profile configuration files using the `--profiles-dir` CLI flag.

```bash
# Use an explicit directory
mz-deploy profiles --profiles-dir /path/to/config
```

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

### GitHub Actions Integration Example

Source: https://github.com/materializeinc/materialize/blob/main/test/console/README.md

Illustrates the typical flow for CI/CD in the console repository using Materialize version testing. It involves checking out the repo, getting the version matrix, and running tests for each version.

```bash
# 1. Checkout the materialize repo
# 2. Run "./mzcompose run list-versions" to get the version matrix as JSON
# 3. Parse the JSON to create a GitHub Actions matrix strategy
# 4. For each version in the matrix:
#    - Run "./mzcompose run start-version <version-alias>"
#    - Run "yarn test:sql"
#    - Run "./mzcompose down -v"
```

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

### Example Output of SHOW CREATE SOURCE

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/show-create-source.md

This output displays the name of the source and the corresponding `CREATE SOURCE` statement. The `create_sql` field shows the exact command used to create the source, including its schema, name, and connection details.

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

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

### Create Materialized View in a Scheduled Cluster

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/transform-data/patterns/refresh-strategies.md

Host materialized views with REFRESH EVERY strategies in scheduled clusters. This example sets up a view to refresh daily at 12PM UTC, benefiting from the cluster's early start.

```mzsql
CREATE MATERIALIZED VIEW mv_refresh_every
IN CLUSTER my_scheduled_cluster
WITH (
  -- Refresh at creation, so the view is populated ahead of
  -- the first user-specified refresh time
  REFRESH AT CREATION,
  -- Refresh every day at 12PM UTC
  REFRESH EVERY '1 day' ALIGNED TO '2024-06-18 00:00:00'
) AS
SELECT ...;
```

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

### Bulk Source and View Creation Example

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/design/20210413_source_sink_resource_sharing.md

Demonstrates how to create multiple sources and materialized views within a single SQL transaction for potential optimization.

```sql
BEGIN;
CREATE SOURCE kafka_src FROM KAFKA BROKER '...' TOPIC 'top' FORMAT AVRO ...
CREATE MATERIALIZED VIEW view1 AS SELECT col1 FROM kafka_src
CREATE MATERIALIZED VIEW view2 AS SELECT col2 FROM kafka_src
COMMIT;
```

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

### Merge Partial Webhook Events with DISTINCT ON

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/sql/create-source/webhook.md

Handle events that arrive in multiple parts by using `DISTINCT ON` to merge related data. This example merges build job start and finish events based on job ID.

```mzsql
CREATE MATERIALIZED VIEW my_build_jobs_merged AS (
  SELECT DISTINCT ON (id) *
  FROM (
    SELECT 
      body->>'id' as id,
      try_parse_monotonic_iso8601_timestamp(body->>'started_at') as started_at,
      try_parse_monotonic_iso8601_timestamp(body->>'finished_at') as finished_at
    FROM my_build_jobs_source
  )
  ORDER BY id, finished_at NULLS LAST, started_at NULLS LAST
);
```

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

### Optimized Query Plan with Size Hints

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/design/20230829_topk_size_hint.md

This is an example of an optimized query plan generated by the system, illustrating how size hints like 'exp_group_size' are represented. It shows the hierarchical structure of operations including Reduce, TopK, and Get.

```text
                                      Optimized Plan
------------------------------------------------------------------------------------------
 materialize.public.nested_distinct_on_group_by_limit:                                   + 
   Return                                                                                + 
     Union                                                                               + 
       Get l0                                                                            + 
       Map (null, null, null)                                                            + 
         Union                                                                           + 
           Negate                                                                        + 
             Project ()                                                                  + 
               Get l0                                                                    + 
           Constant                                                                      + 
             - ()                                                                        + 
   With                                                                                  + 
     cte l0 =                                                                            + 
       Reduce aggregates=[sum(#0), sum(#1), sum(#2)]                                     + 
         TopK order_by=[#1 asc nulls_last, #0 asc nulls_last] limit=2 exp_group_size=1000+ 
           TopK group_by=[#1] order_by=[#0 asc nulls_last] limit=1 exp_group_size=1000   + 
             Reduce group_by=[#0, #1] aggregates=[max(#2)] exp_group_size=1000           + 
               Project (#0..=#2)                                                         + 
                 Get materialize.public.sections                                         + 

(1 row)

```

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

### Example EXPLAIN PLAN PROFILE Output

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/design/20240925_attribution_profiling.md

This example shows the potential output of the `EXPLAIN PLAN PROFILE FOR MATERIALIZED VIEW` command, illustrating operator details, memory usage, and dataflow statistics.

```sql
> CREATE MATERIALIZED VIEW v AS SELECT name, id FROM v4362 WHERE name = (SELECT name FROM v4362 WHERE id = 1);
> EXPLAIN PLAN PROFILE FOR MATERIALIZED VIEW v;
|operator                                                        |memory|
|----------------------------------------------------------------|------|
|Project (#0, #1)                                                |      |
|  Join on=(#0 = #2) type=differential                           |      |
|    ArrangeBy keys=[[#0]]                                       |  12GB|
|      ReadStorage materialize.public.t4362                      |      |
|    ArrangeBy keys=[[#0]]                                       |  10GB|
|      Union                                                     |      |
|        Project (#0)                                            |      |
|          Get l0                                                |      |
|        Map (error("more than one record produced in subquery"))|      |
|          Project ()                                            |      |
|            Filter (#0 > 1)                                     |      |
|              Reduce aggregates=[count(*)]                      |   3GB|
|                Project ()                                      |      |
|                  Get l0                                        |      |
|cte l0 =                                                        |      |
|  Filter (#1 = 1)                                               |
|    ReadStorage materialize.public.t4362                        |

```

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

### Verify mz-deploy Installation

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/mz-deploy/get-started.md

Check the installed version of mz-deploy to confirm successful installation.

```shell
mz-deploy --version
```

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

### Install mz CLI with Homebrew on macOS

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/integrations/cli/installation.md

Use this command to install the `mz` CLI on macOS if you have Homebrew installed. It installs `mz` from the Materialize Inc. Homebrew tap.

```shell
brew install materializeinc/materialize/mz
```

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

### Initialize Project Configuration for Offline Compile

Source: https://github.com/materializeinc/materialize/blob/main/test/canary-environment/README.md

Copy the example project configuration to `project.toml` to enable offline compilation on a fresh checkout. This is necessary before running `mz-deploy compile`.

```bash
cp test/canary-environment/project.toml.example test/canary-environment/project.toml

```

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

### Create SQL Server Source with Options (Cloud)

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/ingest-data/sql-server/self-hosted.md

This example demonstrates creating a SQL Server source with additional options, such as specifying a schema or enabling SSL. Adjust the options based on your SQL Server configuration and security requirements.

```sql
CREATE SOURCE sql_server_source
FROM SQL SERVER
CONNECTION 'host=your_sql_server_host;port=1433;user=your_user;password=your_password'
TABLE 'your_table' WITH (
  ssl = 'true',
  schema = 'your_schema'
);
```

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

### Install dbt-core and dbt-materialize

Source: https://github.com/materializeinc/materialize/blob/main/doc/user/content/manage/dbt/get-started.md

Install the dbt-core and dbt-materialize packages within a virtual environment. Ensure the virtual environment is activated before installation.

```bash
python3 -m venv dbt-venv                  # create the virtual environment
source dbt-venv/bin/activate              # activate the virtual environment
pip install dbt-core dbt-materialize      # install dbt-core and the adapter
```

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

### Basic mzcompose Workflow Example

Source: https://github.com/materializeinc/materialize/blob/main/doc/developer/mzcompose.md

This example demonstrates a complete mzcompose workflow, including the declaration of necessary services and a workflow function to bring them up and run tests.

```python
from materialize.mzcompose import (
    Kafka,
    Materialized,
    SchemaRegistry,
    Testdrive,
    Workflow,
    Zookeeper,
)

SERVICES = [
    Zookeeper(),
    Kafka(),
    SchemaRegistry(),
    Materialized(),
    Testdrive()
]

def workflow_test(c: Composition):
    c.up("zookeeper", "kafka", "schema-registry", "materialized")

    c.run_testdrive_files("*.td")
```