1. What You'll Build — and Why Paradime Is Different

If you've ever hand-edited a profiles.yml file just to get dbt™ talking to ClickHouse — and then done it again for every developer on your team — this guide is for you.

We'll walk through connecting dbt™ to ClickHouse inside Paradime, from zero to a successful dbt run, with no local config files, no plaintext credentials, and no environment-specific guesswork.

The Problem with Local profiles.yml + ClickHouse Credentials

In a standard dbt Core™ setup, your ClickHouse connection lives in a profiles.yml file on each developer's machine:

my_clickhouse_project:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: your-clickhouse-host.example.com
      port: 8443
      user: dbt_user
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics
      secure: True

It works — until it doesn't. Here's what goes wrong in practice:

• Security exposure: Credentials live on local machines. One compromised laptop means your ClickHouse instance is at risk.
• Team drift: Every engineer maintains their own profiles.yml. Diverging settings, stale passwords, and schema typos become invisible landmines.
• CI/CD friction: The file that works locally doesn't translate cleanly to a production scheduler without extra credential plumbing.
• Slow onboarding: New team members spend time configuring local files instead of building models.

The real problem: profiles.yml was designed for solo developers. Analytics teams that need consistent, governed, and secure connections have outgrown it.

Paradime's UI-Driven Connection Flow

Paradime removes the local config layer entirely. Instead of editing YAML by hand, you set up your ClickHouse connection through a secure, centralized UI — once, for the whole team.

The setup covers two environments, each with their own isolated target and credentials:

Paradime generates and stores your profiles.yml securely in the cloud — encrypted, never exposed to users after initial entry. No file to commit, no secrets to rotate across laptops.

2. Prerequisites for ClickHouse

Before configuring Paradime, get your ClickHouse environment ready. This takes about 10–15 minutes and prevents the most common setup mistakes.

Identify Your ClickHouse Deployment Type

The dbt™ ClickHouse adapter supports two connection protocols. Paradime supports both:

ClickHouse Cloud defaults to HTTP over TLS. If you're self-hosted on an internal network with no TLS, the native driver on port 9000 is a common and simpler choice (as used in the Paradime example config below).

Create a Dedicated dbt™ User and Database

Don't reuse admin credentials for dbt™. Create a purpose-built user and database:

-- Create the database dbt will write models to
CREATE DATABASE IF NOT EXISTS analytics;

-- Create a dedicated dbt user
CREATE USER dbt_user IDENTIFIED BY 'a-strong-password-here';

-- Grant appropriate privileges
GRANT ALL ON analytics.* TO dbt_user;
GRANT CREATE TEMPORARY TABLE ON *.* TO dbt_user;

Opinion: Never run dbt™ with the default user or write to a database named default. Create named, purpose-built objects. It's five minutes of work that prevents catastrophic permission mistakes in production.

Allowlist Paradime's IP Addresses

ClickHouse Cloud and most firewalled self-hosted deployments restrict inbound connections by IP. You must allowlist Paradime's static egress IP for the data location you selected during onboarding:

In ClickHouse Cloud, add the IP under Settings → Network → Allowed IPs. For self-hosted deployments, update your firewall rules or ClickHouse config.xml accordingly.

See also: Paradime IP addresses

3. Configure the Code IDE Connection (Development)

The Code IDE connection is your personal development environment. It's where you write models, test queries, and iterate — isolated from production data.

Step-by-Step Setup

1. Click Settings in the top menu bar of the Paradime interface to access Account Settings
2. In the left sidebar, click Connections
3. Click Add New next to the Code IDE section
4. Select ClickHouse
5. In the Profile Configuration field, paste your connection YAML. At minimum, you need these required fields:

schema: dbt_your_name           # Your personal dev database in ClickHouse
host: clickhouse.company.com    # ClickHouse hostname
port: 9000                      # Native: 9000/9440 | HTTP: 8123/8443
user: dbt_user                  # ClickHouse user
password: your_password         # User password
secure: false                   # Set to true for TLS (recommended for ClickHouse Cloud)
driver: native                  # native or http

Tip: After pasting, validate formatting with a tool like YAML Formatter before saving. A misplaced space will silently break the connection.

1. Provide a dbt™ Profile Name — this must match the profile: key in your dbt_project.yml
2. In the Target field, enter dev — this is the default target your dbt™ project will use for development
3. Set Threads to the number of parallel operations your dbt™ project should run (defaults to 1)

Pick a Developer-Specific Schema

Set the schema field to a developer-namespaced ClickHouse database, like dbt_jane or dev_john. In the ClickHouse adapter, schema maps directly to a ClickHouse database — there is no intermediate schema layer.

This isolates each developer's work completely:

• Jane's staging model → dbt_jane.stg_orders
• John's staging model → dbt_john.stg_orders
• Production → analytics.stg_orders

No collisions. No accidental overwrites of production data.

Set quote_columns in dbt_project.yml

The ClickHouse dbt™ adapter requires you to explicitly configure quote_columns to suppress adapter warnings. Add this to your dbt_project.yml:

seeds:
  +quote_columns: false   # set to true if your CSV headers contain spaces

Skip this and you'll see a warning on every dbt run. It won't break anything — but it's noise you don't want.

4. Configure the Bolt Scheduler Connection (Production)

Production needs its own connection with dedicated credentials, a production-targeted schema, and tighter permissions. Never share dev credentials with your scheduler.

Step-by-Step Setup

1. Click Settings in the top menu bar of the Paradime interface to access Account Settings
2. In the left sidebar, click Connections
3. Click Add New next to the Bolt Schedules section
4. Select ClickHouse
5. In the Profile Configuration field, paste your production connection YAML:

schema: analytics                       # Production database in ClickHouse
host: clickhouse.internal.company.com   # ClickHouse hostname
port: 9000                              # Native protocol port
user: paradime_prod_user                # Dedicated production service account
password: prod_password_here            # Service account password
secure: false                           # Set to true for TLS
driver: native                          # native or http

1. Provide the same dbt™ Profile Name as your Code IDE connection — both must match the profile: key in dbt_project.yml
2. In the Target field, enter prod — this is the target your Bolt Schedules will use
3. Set Threads based on your production workload (start at 4–8 and tune from there)

Use a Dedicated Production Service Account

Create a production-specific ClickHouse user — never run scheduled jobs under a personal account:

CREATE USER paradime_prod_user IDENTIFIED BY 'a-very-strong-password';
GRANT ALL ON analytics.* TO paradime_prod_user;
GRANT CREATE TEMPORARY TABLE ON *.* TO paradime_prod_user;

Using a service account means credential rotation doesn't break pipelines when team members leave, and your audit logs cleanly distinguish developer activity from scheduled production runs.

Dev vs. Prod Connection at a Glance

5. Full Profile YAML Reference

Below is the complete profiles.yml structure that Paradime generates and manages securely on your behalf — you never create or touch this file. It's included here as a full reference for every supported ClickHouse parameter:

your_profile_name:
  target: dev
  outputs:
    dev:
      type: clickhouse

      # Required fields
      schema: analytics             # ClickHouse database for dbt models
                                    # Note: "schema" = ClickHouse database. There is no schema layer.
      user: dbt_user                # Database user name
      password: your_password       # Database user password

      # Connection
      driver: http                  # http or native. Auto-determined from port if not explicitly set
      host: localhost               # ClickHouse server hostname
      port: 8123                    # Defaults: http=8123, https=8443, native=9000, native+tls=9440
      secure: false                 # Use TLS (native protocol) or HTTPS (http protocol)
      verify: true                  # Validate TLS certificate when using TLS/SSL
      client_cert: null             # Path to a TLS client certificate (.pem format)
      client_cert_key: null         # Path to the private key for the TLS client certificate

      # Cluster settings
      cluster: ""                   # If set, DDL/table operations run with ON CLUSTER clause
      cluster_mode: false           # Optimised settings for Replicated databases (recommended for ClickHouse Cloud)

      # Performance & timeouts
      threads: 1                    # Parallel dbt threads. Read the read-after-write section before increasing
      compression: ""               # gzip compression if truthy (http), or compression type (native)
      connect_timeout: 10           # Seconds to wait when establishing a connection
      send_receive_timeout: 300     # Seconds to wait for a query response (5 minutes)

      # Incremental model behaviour
      use_lw_deletes: false         # Use delete+insert as the default incremental strategy
      allow_automatic_deduplication: false  # Enable ClickHouse automatic deduplication for Replicated tables

      # Reliability
      retries: 1                    # Retry attempts on retriable exceptions (e.g. 503 Service Unavailable)
      check_exchange: true          # Validate atomic EXCHANGE TABLES support (not needed for most versions)

      # Distributed materializations
      local_suffix: _local          # Table suffix for local tables on shards
      local_db_prefix: ""           # Database prefix for local shard tables. Empty = same db as distributed table

      # Advanced session settings
      custom_settings: {}           # Dictionary of custom ClickHouse session settings for the connection
                                    # Example: { select_sequential_consistency: 1 }

      # Native driver only
      sync_request_timeout: 5       # Timeout for server ping
      compress_block_size: 1048576  # Compression block size when compression is enabled
      tcp_keepalive: false          # TCP keepalive. Use [idle_time_sec, interval_sec, probes] for custom config

Profile Fields Reference

Recommended Settings for ClickHouse Cloud

If you're on ClickHouse Cloud, add these to your profile for correct behaviour with distributed replicas:

secure: true
verify: true
cluster_mode: true
custom_settings:
  select_sequential_consistency: 1

The select_sequential_consistency setting guarantees read-after-write consistency when Bolt runs parallel dbt™ threads across replicated nodes — without it, incremental models and tests can fail intermittently on Cloud.

Note on SET statements: Never use SET statements in dbt™ pre-hooks for ClickHouse settings. In HTTP connections through a load balancer, SET doesn't persist reliably across queries. Always use custom_settings in the profile instead — it's applied per-connection and survives load balancer distribution.

6. Validate Instantly with SQL Scratchpad

Before running a full dbt run, use Paradime's SQL Scratchpad to confirm your connection is live. The Scratchpad is a temporary, gitignored query environment built into the Code IDE — purpose-built for this kind of validation.

Confirm the Connection Is Live

Open a new Scratchpad file and run:

SELECT
    currentUser()     AS current_user,
    currentDatabase() AS current_database,
    version()         AS clickhouse_version;

You should see your configured user, the database specified in your Paradime connection settings, and your ClickHouse version. If the query times out or throws a connection error, go back to Settings → Connections and double-check your host, port, and secure settings.

Verify the Target Database Exists

SHOW DATABASES;

Your configured schema (database) should appear in the list. If it doesn't, confirm your user has CREATE DATABASE rights — the dbt™ adapter will attempt to create the database on first run:

CREATE DATABASE IF NOT EXISTS dbt_validation_test;
DROP DATABASE dbt_validation_test;

If either statement fails, your user is missing CREATE DATABASE privileges.

Sanity-Check Read and Write Permissions

-- Can we read source data?
SELECT count() FROM system.tables LIMIT 1;

-- Can we create, write to, and drop a table in the target database?
CREATE TABLE analytics.dbt_connection_test (id UInt32) ENGINE = Memory;
INSERT INTO analytics.dbt_connection_test VALUES (1);
SELECT * FROM analytics.dbt_connection_test;
DROP TABLE analytics.dbt_connection_test;

If any statement fails, the error will identify the exact permission gap. Fix it in ClickHouse, then re-run in the Scratchpad. This is faster than discovering the issue mid-dbt run.

Validate Cluster Access (If Using cluster Setting)

SELECT cluster, host_name, port
FROM system.clusters
ORDER BY cluster, host_name;

Your cluster name must appear here and match exactly what you've set in the cluster field of your Paradime connection. A mismatch means ON CLUSTER DDL will fail at runtime.

7. Run Your First dbt™ Commands in Paradime

dbt debug, dbt deps, dbt run

Open the Terminal in the Paradime Code IDE (press Ctrl+Shift+` or click New Terminal). You can also use the quick action button to run dbt deps && dbt debug automatically.

Step 1: dbt debug

dbt debug

Expected output when everything is configured correctly:

Running with dbt=1.7.x
...
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]
  ...
  Connection:
    host: your-clickhouse-host.clickhouse.cloud
    port: 8443
    user: dbt_user
    database: analytics
    ...
  Connection test: [OK connection ok]

All checks passed!

If you see All checks passed!, your ClickHouse connection is live.

Step 2: dbt deps

Install packages from your packages.yml:

dbt deps

Step 3: dbt run

Compile and execute all models in your project:

dbt run

On first run, expect to see models being created in your dev database:

Running 4 models in your project (using 4 threads)
...
1 of 4 OK created table model dbt_john.stg_orders ........... [OK in 1.23s]
2 of 4 OK created table model dbt_john.stg_customers ........ [OK in 0.98s]
...
Finished running 4 models in 0 hours 0 minutes and 4.12 seconds.
Completed successfully.
Done. PASS=4 WARN=0 ERROR=0 SKIP=0 TOTAL=4

Where Logs Live

Development (Code IDE): Logs stream directly in the integrated terminal. Use dbt autocompletion (type dbt + Tab) to quickly access commands.

Production (Bolt Scheduler): Navigate to your schedule in Bolt, click a specific run, and scroll to Logs and Artifacts. Three log levels are available:

8. Troubleshooting Common ClickHouse Setup Errors

Connection Refused / Timeout

Symptom: Connection refused or the Scratchpad query hangs with no response.

Common causes:

1. Wrong port. ClickHouse Cloud uses 8443 (HTTPS). Self-hosted HTTP uses 8123. Native TCP uses 9000 or 9440. Mixing these is the most frequent mistake.
2. TLS mismatch. If secure: true but you're hitting a non-TLS port (or vice versa), the connection fails silently.
3. IP not allowlisted. ClickHouse Cloud blocks connections from unknown IPs by default. Add Paradime's egress IP for your region (see Prerequisites).
4. Firewall blocking outbound. Verify your Paradime workspace region can reach your ClickHouse host on the configured port.

Fix: Start with the Scratchpad. A timeout points to a network-level issue, not a dbt-level one.

Authentication Failure

Symptom: Authentication failed: password is incorrect or Code: 516.

Common causes:

1. Wrong password. ClickHouse passwords are case-sensitive. Copy-paste rather than retype.
2. User doesn't exist on the target host. On self-hosted clusters, users may not be replicated across all nodes.
3. IP-based access restriction on the user. ClickHouse users can be configured with HOST restrictions.

Fix:

-- Check the user exists and its host access rules
SHOW CREATE USER dbt_user;

-- Reset the password if needed
ALTER USER dbt_user IDENTIFIED BY 'new-password';

Code: 243 — Insufficient Privileges

Symptom: dbt™ fails during model creation with a permissions error.

Common causes:

1. User lacks CREATE TABLE on the target database — the most common gap.
2. Wrong schema/database. The schema in your Paradime connection points to a database the user can't write to.
3. Cluster DDL requires broader grants. If using ON CLUSTER, the user may need permissions across nodes.

Fix:

GRANT CREATE TABLE ON analytics.* TO dbt_user;
GRANT DROP TABLE ON analytics.* TO dbt_user;
GRANT INSERT ON analytics.* TO dbt_user;
GRANT SELECT ON analytics.* TO dbt_user;

Run SHOW GRANTS FOR dbt_user; to audit exactly what's currently granted.

Schema / Database Not Found

Symptom: DB::Exception: Database analytics doesn't exist.

ClickHouse-specific note: Unlike Snowflake or BigQuery, ClickHouse will not automatically create a database on first connection — the dbt™ adapter handles this on first run, but only if the user has CREATE DATABASE rights.

Fix:

-- Pre-create the database manually
CREATE DATABASE IF NOT EXISTS analytics;

-- Or grant the privilege so the dbt adapter can create it
GRANT CREATE DATABASE ON *.* TO dbt_user;

Read-After-Write Consistency Errors (ClickHouse Cloud)

Symptom: Intermittent failures on incremental models or tests — errors like Table doesn't exist appearing mid-run for a table that was just created.

Cause: ClickHouse Cloud uses distributed replicas. dbt™'s read-after-insert model can break if reads and writes hit different replicas.

Fix: In your Paradime Bolt Scheduler connection YAML, add:

cluster_mode: true
custom_settings:
  select_sequential_consistency: 1

This guarantees sequential consistency across replicas for all queries in that connection. For self-hosted clusters, enabling select_sequential_consistency is not recommended — use sticky session / replica-aware routing at the load balancer level instead.

Wrapping Up

Getting dbt™ and ClickHouse working together doesn't have to mean wrangling profiles.yml across every developer's laptop. With Paradime, you get a centralized, encrypted connection layer that powers both development and production — configured through a UI in minutes, validated with Scratchpad before a single model runs, and monitored through built-in Bolt logs.

Here's what you accomplished:

• ✅ Set up ClickHouse prerequisites (dedicated user, database, permissions, IP allowlist)
• ✅ Configured a Development connection in Paradime's Code IDE with a personal schema
• ✅ Configured a Production connection in Paradime's Bolt Scheduler with a service account
• ✅ Understood the full YAML profile and every configurable field
• ✅ Validated the connection using SQL Scratchpad before running any dbt™ commands
• ✅ Ran dbt debug and dbt run against ClickHouse successfully
• ✅ Built a troubleshooting toolkit for the most common ClickHouse connection errors

Your ClickHouse connection is live, your credentials are secure, and your whole team is working from a single source of configuration truth. Time to build models.

Further Reading

• Paradime ClickHouse — Code IDE Connection Docs
• Paradime ClickHouse — Bolt Scheduler Connection Docs
• Paradime IP Restrictions
• Paradime Environment Variables
• Paradime SQL Scratchpad
• ClickHouse dbt Adapter — Profile Configuration
• ClickHouse dbt Adapter — Materializations
• ClickHouse Network Policies
• ClickHouse Read-After-Write Consistency
• dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.