<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Hands-on dbt with ClickHouse - Medium]]></title>
        <description><![CDATA[A practical step by step dbt course built around real production patterns. Clean layers. Solid testing. ClickHouse friendly workflows. For analysts and engineers who want to model data with confidence. - Medium]]></description>
        <link>https://medium.com/hands-on-dbt-with-clickhouse?source=rss----30e90288cb0d---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Hands-on dbt with ClickHouse - Medium</title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse?source=rss----30e90288cb0d---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 03:07:07 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/hands-on-dbt-with-clickhouse" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #9 Orchestrating dbt with Airflow and Cosmos]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-9-orchestrating-dbt-with-airflow-and-cosmos-436f09f7c1b6?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/436f09f7c1b6</guid>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[data-infrastructure]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dbt]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Tue, 27 Jan 2026 08:02:41 GMT</pubDate>
            <atom:updated>2026-01-27T08:02:40.538Z</atom:updated>
            <content:encoded><![CDATA[<p>By this point in the series we already have a <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">local ClickHouse</a>, a dbt project with clean layer separation, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">seeds and snapshots</a>,<a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d"> an analyses folder</a> for ad hoc work, and a full set of tests, including unit tests. All of this lives inside one dbt DAG: dbt knows it should build <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">staging first</a>, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">then intermediate</a>, then dims and facts, then marts on top, and dbt build walks that graph in the right order.</p><p>Now we need to solve one tiny issue that can turn into a big problem: dbt is great at describing what should happen inside the dbt DAG and not great at answering questions like when and how often to run, how to split critical and non critical paths, how to alert people, and how to fit into the wider pipeline landscape of the company. dbt gives you dbt run, dbt build, dbt test, dbt source freshness. It knows how to build models, run tests, and check contracts between layers, but does not know how to:</p><ul><li>run itself on a schedule</li><li>recalc only the tail of the dbt DAG after a failure, starting from the broken model and down</li><li>manage dependencies between different pipelines, for example “first check freshness of sources, then run critical models, then everything else”</li><li>store a global history of runs and alerts across all projects</li></ul><p>The simplest hack on top of that is cron. You drop something like this into crontab:</p><pre>0 3 * * * cd /path/to/training_project &amp;&amp; source dbt-env/bin/activate &amp;&amp; dbt build</pre><p>Cron then triggers the dbt DAG on schedule. As long as everything is green you might feel this is enough. As soon as one model fails inside the dbt DAG, you see the limits. From cron’s point of view you have one command that exited with an error. To understand where exactly it broke you still need to dig into the dbt log. If you want to re-run only the part of the dbt DAG after that point, cron does not help. It just calls dbt build again and walks the whole graph from the start.</p><p>The next step many teams try is a “poor man’s Airflow”. You spin up Airflow, create one Airflow DAG, put one BashOperator inside, and call dbt build. You gain a UI, a list of runs, and manual trigger buttons, but in the specific case of dbt this still behaves very close to cron. Airflow sees a single task that wraps the whole dbt DAG. If one model fails inside the dbt DAG, Airflow will honestly report that task dbt_build has failed, and you still go to the dbt logs to find the root cause. Restarting only the tail of the dbt DAG is still not possible, the task runs the whole dbt build again.</p><p>To turn Airflow into a real orchestrator for dbt, Airflow needs to see the dbt DAG itself. It has to treat each dbt model as a separate task, respect dependencies between them, and understand where runs happen and where tests live. Astronomer Cosmos does exactly this. It reads the dbt project, runs dbt ls, turns the dbt DAG into an Airflow DAG, and then creates:</p><ul><li>individual Airflow tasks for running each dbt model</li><li>individual Airflow tasks for tests, attached to those models</li></ul><p>Once that works, you can run a single model and its downstream, run only part of the dbt DAG, run only unit tests, or only critical models. You see the full graph in the Airflow UI with retries, alerts, and run history.</p><p>The training setup you can build here looks like this:</p><ul><li>Airflow in Docker next to your dbt project</li><li>a custom Airflow image that includes Cosmos and dbt-clickhouse</li><li>several Airflow DAGs powered by Cosmos that can run one model, the whole dbt DAG, the critical slice, the non critical slice, and a separate unit tests run</li><li>Slack notifications that summarise how each run went</li></ul><p>Assume the dbt project lives at something like /path/to/training_project. In the terminal you enter the project and activate your dbt environment:</p><pre>cd /path/to/training_project<br>source dbt-env/bin/activate</pre><p>This is the world from the previous articles. Now you need a neighbour directory for Airflow. Go one level up and create a folder:</p><pre>cd ..<br>mkdir airflow<br>cd airflow</pre><p>The structure becomes:</p><pre>.../dbt_course/<br>  ├── training_project/   # dbt project<br>  └── airflow/            # everything related to Airflow</pre><p>Inside airflow you place docker-compose.yaml. It is much easier to take the official example instead of assembling one by hand. At the time of writing the article the current version is 3.1.1, so the command looks like this:</p><pre>curl -LfO &#39;https://airflow.apache.org/docs/apache-airflow/3.1.1/docker-compose.yaml&#39;</pre><p>After that the airflow directory contains docker-compose.yaml</p><p>Before the first docker compose up it is better to add a .env file with AIRFLOW_UID and AIRFLOW_GID. If you skip that and immediately run:</p><pre>docker compose up -d</pre><p>Docker prints warnings that AIRFLOW_UID is not set and that .env was not found. Airflow still starts, but it is better to follow the official docs and define the user explicitly. On macOS the usual pattern is:</p><pre>echo -e &quot;AIRFLOW_UID=$(id -u)\nAIRFLOW_GID=0&quot; &gt; .env</pre><p>Now you have both docker-compose.yaml and .env next to each other, and Docker knows which user id to use inside the containers.</p><p>You can bring up the stack with:</p><pre>docker compose up -d</pre><p>On the first run Docker pulls the images for Airflow, Postgres, and Redis, creates the airflow_default network, and starts the containers. If you run docker compose ps you will see names similar to:</p><ul><li>airflow-postgres-1</li><li>airflow-redis-1</li><li>airflow-airflow-init-1</li><li>airflow-airflow-scheduler-1</li><li>airflow-airflow-worker-1</li><li>airflow-airflow-apiserver-1</li><li>airflow-airflow-dag-processor-1</li><li>airflow-airflow-triggerer-1</li></ul><p>One of them, airflow-airflow-init-1, is a one time init container. It sets up the Airflow metadata database, creates the default user, then finishes and stops. Seeing this container exit is expected and not an error. If anything else fails to start or keeps restarting, you can open Docker Desktop and inspect logs from there, or call docker logs for the container directly.</p><p>At this point Docker resources can become an issue. On laptops the Docker Desktop defaults are often too small for Airflow. In the Settings under Resources you control CPU count, memory, and disk usage limit. For a training Airflow instance you usually want at least two CPUs, four gigabytes of RAM, and a disk limit that is not tiny but also not so large that Docker can quietly fill half your SSD. After changing these settings you restart Docker Desktop and re-run docker compose up -d.</p><p>Once everything is healthy, the Airflow UI is available on <a href="http://localhost:8080.">http://localhost:8080.</a> The airflow-apiserver service in docker-compose.yaml maps port 8080:8080, so you do not need to add anything extra. The default login and password are both airflow. They are mentioned in comments inside the compose file and also printed in the logs of airflow-airflow-init-1.</p><p>Right after login Airflow UI shows a long list of example Airflow DAGs with names starting with example_. For this training project they are just noise. In the compose file there is already an environment variable AIRFLOW__CORE__LOAD_EXAMPLES that can tell Airflow not to create example DAGs for new instances by switching it to ‘false’. But if the very first startup was done with the default value true (as it usually happens), the examples are already written into the Postgres metadata database and they stay in the UI even after switching that flag to false.</p><p>In a toy environment you could stop there. Open the DAGs list in the UI, find all Airflow DAGs whose names start with example_, switch the toggle off so they are not active, go inside each one and clear all existing DagRuns. After that the scheduler stops touching them and the noise is gone. For the training project this is already enough.</p><p>In our setup it is still worth doing one more step, because we are going to adjust docker compose anyway. Later in the article we will extend the image to include Cosmos and dbt-clickhouse. It makes sense to also keep AIRFLOW__CORE__LOAD_EXAMPLES=false at config level so that any new Airflow instance you start from this compose file will boot without examples from day one. For production, teams often add an extra script to clean old example entries directly from the Airflow database, but that is more of an infrastructure topic and outside this series.</p><p>Now Airflow needs a copy of the dbt project. Airflow expects Airflow DAGs and related code under /opt/airflow/dags inside the container. When you launch the compose, Docker creates dags, logs, plugins, and config on the host side. If for some reason the dags directory is not there, you can always create it yourself:</p><pre>mkdir -p dags</pre><p>Then you copy the dbt project into dags/dbt. On the host it looks like this:</p><pre>cd /path/to/dbt_course/airflow<br>cp -R ../training_project ./dags/dbt</pre><p>So now you have:</p><pre>.../dbt_course/<br>  ├── training_project/        # original dbt project for local work<br>  └── airflow/<br>      ├── docker-compose.yaml<br>      ├── .env<br>      ├── dags/<br>      │   └── dbt/             # copy of the dbt project for Airflow<br>      ├── logs/<br>      ├── config/<br>      └── plugins/</pre><p>The original dbt project stays where it was. Local dbt debug, dbt test, dbt build still run from there and connect to localhost. The copy under dags/dbt exists for Airflow only. This separation keeps local development and orchestration clearly apart.</p><p>You can open dags/dbt in the editor as a separate project. For local commands you can keep using the same dbt-env virtual environment or create a dedicated .venv inside dags/dbt. Airflow will not use that environment anyway, it has its own Python inside the container. The local environment is for your convenience, not for the scheduler.</p><p>To let Airflow run dbt and Cosmos, the image in docker-compose.yaml has to be extended. In the x-airflow-common block the default image line looks like this:</p><pre>#image: ${AIRFLOW_IMAGE_NAME:-apache/airflow:3.1.1}</pre><p>Replace it with a build instruction and wire volumes for the project and for .dbt:</p><pre>x-airflow-common:<br>  &amp;airflow-common<br>  #image: ${AIRFLOW_IMAGE_NAME:-apache/airflow:3.1.1}<br>  build: .<br>  env_file:<br>    - ${ENV_FILE_PATH:-.env}<br>  environment:<br>    &amp;airflow-common-env<br>    AIRFLOW__CORE__EXECUTOR: CeleryExecutor<br>    AIRFLOW__CORE__LOAD_EXAMPLES: &#39;false&#39;<br>    # plus the rest from the official example<br>  volumes:<br>    - ${AIRFLOW_PROJ_DIR:-.}/dags:/opt/airflow/dags<br>    - ${AIRFLOW_PROJ_DIR:-.}/logs:/opt/airflow/logs<br>    - ${AIRFLOW_PROJ_DIR:-.}/config:/opt/airflow/config<br>    - ${AIRFLOW_PROJ_DIR:-.}/plugins:/opt/airflow/plugins<br>    # important: mount local ~/.dbt into the container<br>    - ${HOME}/.dbt:/opt/airflow/.dbt<br>  user: &quot;${AIRFLOW_UID:-50000}:0&quot;</pre><p>The key points are simple. The image line is commented out. The build: . line is enabled, which tells Docker to build from a Dockerfile in the root of the airflow directory. The ${HOME}/.dbt volume is mounted into /opt/airflow/.dbt so that profiles.yml lives outside git on your host and is still visible inside the container.</p><p>This is the main safety trick. The dags folder is almost always under git, because it holds your Airflow DAGs and other code that you want to version. If you put profiles.yml there, there is a high chance someone will commit it with database hosts and credentials. Cleaning that up later is painful. When profiles.yml stays under ~/.dbt and is only mounted into the container, you avoid that risk, and Airflow can still use it.</p><p>In the same directory you create a Dockerfile:</p><pre>FROM apache/airflow:3.1.1<br><br>RUN pip install --upgrade pip &amp;&amp; \<br>    pip install \<br>        &quot;astronomer-cosmos[dbt-postgres]&quot; \<br>        dbt-core \<br>        dbt-clickhouse</pre><p>Here you install Cosmos, dbt core, and the ClickHouse adapter. The dbt-postgres extra in the Cosmos dependency is only there to pull the optional packages Cosmos expects. The fact that your warehouse is ClickHouse is handled by dbt-clickhouse.</p><p>If you prefer, you can move the list of dependencies into requirements.txt:</p><pre>astronomer-cosmos[dbt-postgres]<br>dbt-core<br>dbt-clickhouse</pre><p>and simplify the Dockerfile:</p><pre>FROM apache/airflow:3.1.1<br><br>COPY requirements.txt /requirements.txt<br>RUN pip install --upgrade pip &amp;&amp; \<br>    pip install -r /requirements.txt</pre><p>After that you rebuild the stack:</p><pre>docker compose down<br>docker compose up --build -d</pre><p>The --build flag makes Docker rebuild the image using your Dockerfile. Once the containers are up again, you can check that Cosmos and dbt-clickhouse are importable:</p><pre>docker exec -it airflow-airflow-scheduler-1 bash<br>python -c &quot;import cosmos; import dbt; import dbt.adapters.clickhouse&quot;</pre><p>If no exceptions are thrown, the Python environment inside the Airflow container is ready.</p><p>Cosmos now needs to “see” the dbt project. In the official Cosmos docs the examples usually target Postgres and show how Cosmos builds a profiles.yml on the fly from an Airflow Connection. With ClickHouse and an already configured ~/.dbt/profiles.yml it is often simpler to reuse that file. That is why we mounted ${HOME}/.dbt into /opt/airflow/.dbt.</p><p>When Cosmos imports an Airflow DAG, it runs dbt ls under the hood and passes:</p><ul><li>--project-dir pointing to a temporary copy of the dbt project</li><li>--profiles-dir pointing to /opt/airflow/.dbt</li><li>--profile with the profile name</li><li>--target with the target inside that profile</li></ul><p>If the profile name or target do not exist, dbt returns an error, and the Airflow DAG ends up in the Import Errors view until you fix profiles.yml.</p><p>To hook this up, you add a separate target for Airflow in ~/.dbt/profiles.yml. For example:</p><pre>training_project:<br>  target: dev          # local dbt still uses dev<br>  outputs:<br>    dev:<br>      type: clickhouse<br>      host: localhost<br>      port: 9000<br>      user: trainer<br>      password: trainer_pwd<br>      secure: false<br>      verify: false<br>      threads: 1<br>      schema: dbt_training<br>      driver: native<br><br>    airflow:<br>      type: clickhouse<br>      host: ch-training   # name of the ClickHouse container inside airflow_default<br>      port: 9000<br>      user: trainer<br>      password: trainer_pwd<br>      secure: false<br>      verify: false<br>      threads: 1<br>      schema: dbt_training<br>      driver: native</pre><p>Local dbt continues to work with target: dev and connects to localhost. Airflow cannot reach localhost on your host from inside the container, it only sees other containers in the airflow_default network. So the host for the airflow target is set to the name of the ClickHouse container. In the training setup that is something like ch-training, but you can always check the actual name with docker compose ps.</p><p>After changing profiles.yml, it is useful to verify that the scheduler container sees the file and the profile inside it:</p><pre>docker compose exec airflow-airflow-scheduler-1 bash<br>ls /opt/airflow/.dbt<br>cat /opt/airflow/.dbt/profiles.yml | head -20</pre><p>You should see training_project with both dev and airflow outputs.</p><p>With that in place you can create the first Airflow DAG powered by Cosmos that controls a single dbt model inside the dbt DAG. It is a nice minimal end to end test that the full chain dbt → Cosmos → Airflow → ClickHouse is wired correctly.</p><p>In airflow/dags/dbt you create my_first_dag.py:</p><pre>from datetime import datetime<br>from pathlib import Path<br><br>from cosmos import DbtDag, ProjectConfig, ProfileConfig, ExecutionConfig, RenderConfig<br>from cosmos.constants import TestBehavior, SourceRenderingBehavior<br><br>DBT_PROJECT_PATH = Path(&quot;/opt/airflow/dags/dbt&quot;)<br>DBT_PROFILES_PATH = &quot;/opt/airflow/.dbt/profiles.yml&quot;<br><br>profile_config = ProfileConfig(<br>    profile_name=&quot;training_project&quot;,<br>    target_name=&quot;airflow&quot;,<br>    profiles_yml_filepath=DBT_PROFILES_PATH,<br>)<br><br>execution_config = ExecutionConfig(<br>    dbt_executable_path=&quot;dbt&quot;,<br>)<br><br>render_config = RenderConfig(<br>    select=[&quot;stg_prod_clickhouse__organizations&quot;],<br>    test_behavior=TestBehavior.AFTER_EACH,<br>    source_rendering_behavior=SourceRenderingBehavior.NONE,<br>)<br><br>my_first_dag = DbtDag(<br>    project_config=ProjectConfig(DBT_PROJECT_PATH),<br>    profile_config=profile_config,<br>    execution_config=execution_config,<br>    render_config=render_config,<br>    schedule=&quot;@daily&quot;,<br>    start_date=datetime(2025, 10, 1),<br>    catchup=False,<br>    dag_id=&quot;my_first_dag&quot;,<br>    default_args={&quot;retries&quot;: 1},<br>)</pre><p>Cosmos will call dbt ls with --select stg_prod_clickhouse__organizations, build the relevant slice of the dbt DAG, and turn it into an Airflow DAG with a run task for that model and a separate test task if tests are defined in dbt. After a short delay you will see my_first_dag in the Airflow UI. The Graph view shows the dbt model as an Airflow task, and tests as separate tasks attached to it.</p><p>If the Airflow DAG never appears or shows up under Import Errors, the usual reason is that Airflow cannot find profiles.yml at /opt/airflow/.dbt or that the profile training_project is missing the airflow target. Fixing the profile and saving the file usually resolves the import problem.</p><p>Once the single model run works, you can lift the idea to the whole dbt DAG and make an Airflow DAG that behaves very close to dbt build. In the same airflow/dags/dbt directory you add my_second_dag.py:</p><pre>from datetime import datetime<br>from pathlib import Path<br><br>from cosmos import DbtDag, ProjectConfig, ProfileConfig, ExecutionConfig<br><br>DBT_PROJECT_PATH = Path(&quot;/opt/airflow/dags/dbt&quot;)<br>DBT_PROFILES_PATH = &quot;/opt/airflow/.dbt/profiles.yml&quot;<br><br>profile_config = ProfileConfig(<br>    profile_name=&quot;training_project&quot;,<br>    target_name=&quot;airflow&quot;,<br>    profiles_yml_filepath=DBT_PROFILES_PATH,<br>)<br><br>execution_config = ExecutionConfig(<br>    dbt_executable_path=&quot;dbt&quot;,<br>)<br><br>my_second_dag = DbtDag(<br>    project_config=ProjectConfig(DBT_PROJECT_PATH),<br>    profile_config=profile_config,<br>    execution_config=execution_config,<br>    schedule=&quot;@daily&quot;,<br>    start_date=datetime(2025, 10, 1),<br>    catchup=False,<br>    dag_id=&quot;my_second_dag&quot;,<br>    default_args={&quot;retries&quot;: 2},<br>)</pre><p>Here there is no RenderConfig. Cosmos interprets this as “build the full dbt DAG”, which is roughly equivalent to dbt build for the project. In the Airflow UI the Graph of this Airflow DAG looks very similar to what you see in dbt docs: staging models at the bottom, intermediate above them, then dims and facts, and then marts if you have them. For models with tests you will see extra Airflow tasks that run those tests.</p><p>The important part is that failing a single dbt model now colors only that branch of the Airflow DAG in red. Everything upstream that already ran and does not depend on the broken model stays green. And you finally get the ability to re-run only the tail of the dbt DAG: select the failed node and its downstream in the Airflow UI and trigger a partial run, instead of rebuilding everything from scratch.</p><p>To make this orchestration more flexible, each dbt model needs to say how important it is and what tests should act as hard stop signals. In the training project this is done with tags and severity.</p><p>All fact models that feed money-facing marts can be tagged as critical_mart, all dim models as non_critical_mart. The staging layer is not tagged. It will be pulled into the dbt DAG automatically as ancestors. In dbt model configs that looks like:</p><pre>{{ config(tags=[&#39;critical_mart&#39;]) }}<br><br>select<br>  ...<br>from ...</pre><p>and</p><pre>{{ config(tags=[&#39;non_critical_mart&#39;]) }}<br><br>select<br>  ...<br>from ...</pre><p>With these tags in place you can use selectors in Cosmos render config. For example:</p><pre>select=[&quot;+tag:critical_mart&quot;]</pre><p>The plus means “take the model and all its parents in the dbt DAG”. For non critical models you use +tag:non_critical_mart.</p><p>Tests on sources and staging are configured with severity: warn. In models/staging/prod_clickhouse/_prod_clickhouse__sources.yml it looks like this:</p><pre>sources:<br>  - name: prod_clickhouse<br>    tables:<br>      - name: superset_transactions_2<br>        columns:<br>          - name: transaction_id<br>            tests:<br>              - not_null:<br>                  severity: warn<br>          - name: amount_usd<br>            tests:<br>              - not_null:<br>                  severity: warn</pre><p>These tests give a useful signal that something changed in the raw data, but they do not stop the critical pipeline. In the intermediate layer and for facts, tests are configured with the default severity, which is error. In models/intermediate/prod_clickhouse/_int_prod_clickhouse__models.yml that looks like:</p><pre>models:<br>  - name: fct_transactions<br>    columns:<br>      - name: transaction_id<br>        tests:<br>          - not_null<br>          - unique<br>  - name: dim_org_project<br>    columns:<br>      - name: project_id<br>        tests:<br>          - not_null</pre><p>Here a broken key or uniqueness is a hard stop, and that is what you want for facts and bridges.</p><p>Unit tests live under tests/…/unit and are tagged in a way that allows you to select them with test_type:unit. In the eighth article we already used dbt test --select “test_type:unit” to run them locally. Cosmos uses the same selector inside an Airflow DAG.</p><p>On top of this you define three Cosmos based Airflow DAGs: one for critical models, one for non critical ones, and one for unit tests.</p><p>The critical Airflow DAG uses the critical_mart tag:</p><pre>from cosmos import DbtDag, ProjectConfig, ProfileConfig, ExecutionConfig, RenderConfig<br>from cosmos.constants import TestBehavior, SourceRenderingBehavior<br>from datetime import datetime<br>from pathlib import Path<br><br>DBT_PROJECT_PATH = Path(&quot;/opt/airflow/dags/dbt&quot;)<br>DBT_PROFILES_PATH = &quot;/opt/airflow/.dbt/profiles.yml&quot;<br><br>profile_config = ProfileConfig(<br>    profile_name=&quot;training_project&quot;,<br>    target_name=&quot;airflow&quot;,<br>    profiles_yml_filepath=DBT_PROFILES_PATH,<br>)<br><br>execution_config = ExecutionConfig(<br>    dbt_executable_path=&quot;dbt&quot;,<br>)<br><br>critical_render_config = RenderConfig(<br>    select=[&quot;+tag:critical_mart&quot;],<br>    test_behavior=TestBehavior.AFTER_EACH,<br>    source_rendering_behavior=SourceRenderingBehavior.NONE,<br>)<br><br>dbt_critical_pipeline = DbtDag(<br>    project_config=ProjectConfig(DBT_PROJECT_PATH),<br>    profile_config=profile_config,<br>    execution_config=execution_config,<br>    render_config=critical_render_config,<br>    schedule=&quot;@daily&quot;,<br>    start_date=datetime(2025, 10, 1),<br>    catchup=False,<br>    dag_id=&quot;dbt_critical_pipeline&quot;,<br>    default_args={&quot;retries&quot;: 2},<br>)</pre><p>Cosmos runs dbt ls --select “+tag:critical_mart”, creates an Airflow DAG with all critical models and their ancestors, and wires up the tests. Staging tests with severity: warn do not fail the tasks. Tests with severity error stop their branches. This is effectively an orchestrated dbt build for the critical part of your dbt DAG.</p><p>The non critical Airflow DAG focuses on non_critical_mart:</p><pre>non_critical_render_config = RenderConfig(<br>    select=[&quot;+tag:non_critical_mart&quot;],<br>    test_behavior=TestBehavior.AFTER_EACH,<br>    source_rendering_behavior=SourceRenderingBehavior.NONE,<br>)<br><br>dbt_non_critical_pipeline = DbtDag(<br>    project_config=ProjectConfig(DBT_PROJECT_PATH),<br>    profile_config=profile_config,<br>    execution_config=execution_config,<br>    render_config=non_critical_render_config,<br>    schedule=&quot;@daily&quot;,<br>    start_date=datetime(2025, 10, 1),<br>    catchup=False,<br>    dag_id=&quot;dbt_non_critical_pipeline&quot;,<br>    default_args={&quot;retries&quot;: 1},<br>)</pre><p>If this Airflow DAG fails, the critical one is not blocked. In a real environment you might run the non critical pipeline less often and accept looser SLOs for it.</p><p>The unit tests get their own Airflow DAG:</p><pre>unit_tests_render_config = RenderConfig(<br>    select=[&quot;test_type:unit&quot;],<br>    test_behavior=TestBehavior.AFTER_ALL,<br>    source_rendering_behavior=SourceRenderingBehavior.NONE,<br>)<br><br>dbt_unit_tests = DbtDag(<br>    project_config=ProjectConfig(DBT_PROJECT_PATH),<br>    profile_config=profile_config,<br>    execution_config=execution_config,<br>    render_config=unit_tests_render_config,<br>    schedule=None,<br>    start_date=datetime(2025, 10, 1),<br>    catchup=False,<br>    dag_id=&quot;dbt_unit_tests&quot;,<br>    default_args={&quot;retries&quot;: 0},<br>)</pre><p>You can trigger this Airflow DAG manually after refactors or wire it to CI. It relies on the same dbt project and selectors, but it stays independent from the daily data refresh schedules.</p><p>In production setups this pattern is usually combined with freshness checks for sources. In sources.yml you define loaded_at_field and thresholds like warn_after and error_after for critical tables. In Cosmos RenderConfig you then use SourceRenderingBehavior.WITH_TESTS_OR_FRESHNESS. This makes sources and their freshness checks first class tasks inside the Airflow DAG. If a source is stale, the pipeline fails early. The same Slack alerts and summaries capture these failures in the same way as any other task error.</p><p>To notify humans you add Slack integration on top of these dbt based Airflow DAGs. For the training project a simple Incoming Webhook is enough. In Slack you create an app, enable Incoming Webhooks, and copy the generated URL. In Airflow you configure a Connection of type HTTP, for example with the id slack_webhook_default. The Host field can stay empty, and the Extra field stores a JSON payload with the webhook_url.</p><p>In the plugins folder you create slack_notifications.py:</p><pre>from __future__ import annotations<br>import os<br>from airflow.providers.slack.hooks.slack_webhook import SlackWebhookHook<br>from airflow.models.taskinstance import TaskInstance<br>from airflow.utils.context import Context<br><br>SLACK_CONN_ID = os.getenv(&quot;SLACK_CONN_ID&quot;, &quot;slack_webhook_default&quot;)<br>DEFAULT_CHANNEL = os.getenv(&quot;SLACK_CHANNEL&quot;, &quot;#dbt-alerts&quot;)<br><br>def _post(text: str, channel: str | None = None, conn_id: str = SLACK_CONN_ID):<br>    SlackWebhookHook(slack_webhook_conn_id=conn_id).send(<br>        text=text,<br>        channel=channel or DEFAULT_CHANNEL,<br>    )<br><br>def task_success_callback(context: Context):<br>    ti: TaskInstance = context[&quot;ti&quot;]<br>    _post(<br>        f&quot;✅ *{ti.dag_id}* - task *{ti.task_id}* SUCCESS &quot;<br>        f&quot;(run_id=`{context[&#39;run_id&#39;]}`, {ti.duration:.1f}s)&quot;<br>    )<br><br>def task_failure_callback(context: Context):<br>    ti: TaskInstance = context[&quot;ti&quot;]<br>    _post(<br>        f&quot;❌ *{ti.dag_id}* - task *{ti.task_id}* FAILED &quot;<br>        f&quot;(run_id=`{context[&#39;run_id&#39;]}`)\nLogs: {ti.log_url}&quot;<br>    )</pre><p>Then you plug these callbacks into the Cosmos based Airflow DAGs through default_args. For the critical pipeline you might keep only the failure callback to avoid noise:</p><pre>from slack_notifications import task_failure_callback<br><br>default_args = {<br>    &quot;retries&quot;: 2,<br>    &quot;on_failure_callback&quot;: task_failure_callback,<br>}</pre><p>This gives you a message every time a task in the critical Airflow DAG fails, with a direct link to the log in the UI. If you ever want very detailed traces, you can also add the success callback, but in practice it gets noisy fast.</p><p>On top of per task callbacks you can build a single summary message per run using dbt artifacts. dbt writes run_results.json into a directory controlled by DBT_TARGET_PATH. You tell Cosmos where to put those artifacts via ExecutionConfig:</p><pre>execution_config = ExecutionConfig(<br>    dbt_executable_path=&quot;dbt&quot;,<br>    env={<br>        &quot;DBT_TARGET_PATH&quot;: &quot;/opt/airflow/logs/dbt_artifacts/{{ dag.dag_id }}/{{ run_id }}&quot;,<br>    },<br>)</pre><p>Each run of an Airflow DAG then writes its dbt results into a directory that includes both the dag_id and the run_id. After the run you can walk that directory tree, read all run_results.json files, count how many tasks succeeded and how many failed, and how many of these failures were tests. From that you build a short human friendly verdict and send it to Slack.</p><p>A simple summary operator might look like this:</p><pre>import json<br>import os<br>import glob<br>from airflow.operators.python import PythonOperator<br><br>def summarize_and_notify(**context):<br>    dag_id = context[&quot;dag_run&quot;].dag_id<br>    run_id = context[&quot;run_id&quot;]<br>    base = f&quot;/opt/airflow/logs/dbt_artifacts/{dag_id}/{run_id}&quot;<br>    files = glob.glob(os.path.join(base, &quot;**&quot;, &quot;run_results.json&quot;), recursive=True)<br><br>    total = 0<br>    passed = 0<br>    failed = 0<br>    error_tests = 0<br><br>    for f in files:<br>        try:<br>            with open(f) as fh:<br>                data = json.load(fh)<br>            for r in data.get(&quot;results&quot;, []):<br>                total += 1<br>                status = r.get(&quot;status&quot;)<br>                if status == &quot;success&quot;:<br>                    passed += 1<br>                elif status in (&quot;error&quot;, &quot;fail&quot;):<br>                    failed += 1<br>                    if r.get(&quot;unique_id&quot;, &quot;&quot;).startswith(&quot;test.&quot;):<br>                        error_tests += 1<br>        except Exception:<br>            pass<br><br>    if total &gt; 0 and failed == 0:<br>        verdict = &quot;✅ models built, tests are green&quot;<br>    elif error_tests &gt; 0 and passed &gt; 0:<br>        verdict = &quot;🟡 models built partially, some tests failed&quot;<br>    else:<br>        verdict = &quot;❌ models did not update or failed before tests&quot;<br><br>    text = (<br>        f&quot;{verdict}\n&quot;<br>        f&quot;*{dag_id}* run_id=`{run_id}` - {passed}/{total} succeeded, {failed} failed &quot;<br>        f&quot;(including tests: {error_tests}).&quot;<br>    )<br>    from slack_notifications import _post<br>    _post(text)<br><br>summary = PythonOperator(<br>    task_id=&quot;slack_summary&quot;,<br>    python_callable=summarize_and_notify,<br>)</pre><p>You attach this operator to the end of the Cosmos generated Airflow DAG, so that it runs after dbt tasks finish. In some Cosmos versions you can connect it directly with dbt_critical_pipeline &gt;&gt; summary. In others it might be cleaner to create a separate “reporter” Airflow DAG that waits for dbt_critical_pipeline via ExternalTaskSensor and then runs summarize_and_notify. Either way, the idea is the same: send one compact, readable message per run instead of a firehose of per task notifications.</p><p>Once all this is in place, the training stack looks much closer to how real teams run dbt in production. You still have the dbt DAG with clean layers and tests. Above it you now have Airflow DAGs built with Cosmos that can run whole slices of that dbt DAG on a schedule, with different SLAs for critical and non critical paths. And on top of that you have Slack notifications that turn raw run results into something your team can actually act on.</p><p>If you followed the series from the first article to this one, you now have a full training stack: ClickHouse, a dbt project with clear layers, tests and unit tests, and an Airflow setup that can run it safely in production-like conditions. From here you can start adapting this blueprint to your own data stack, your own products, and your own team.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=436f09f7c1b6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-9-orchestrating-dbt-with-airflow-and-cosmos-436f09f7c1b6">Hands-on dbt with ClickHouse: #9 Orchestrating dbt with Airflow and Cosmos</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #8 Unit tests as seatbelts for model logic]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-8-unit-tests-as-seatbelts-for-model-logic-97aeb3825d45?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/97aeb3825d45</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Tue, 20 Jan 2026 08:27:10 GMT</pubDate>
            <atom:updated>2026-01-20T08:27:09.033Z</atom:updated>
            <content:encoded><![CDATA[<p>By this point in the series we already have quite a lot in place. A safe local ClickHouse and dbt sandbox from the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">first article</a>. Clean sources and a staging layer from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">the second</a>. An intermediate layer with dim and fact models from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">the third</a>. Configs and properties under control from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">the fourth</a>. Seeds and snapshots for lookups and history from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">the fifth</a>. And in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d">the sixth</a> article we learned how to keep one-off SQL research in the analyses folder.</p><p>In <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d">the seventh</a> article we finally faced data quality head on. We saw how dbt creates a temporary table, runs tests against it, and only then swaps it into the target name. We set up generic tests through YAML, wrote singular tests as focused SQL files, and used severity, warn_if and error_if to separate annoying dirt from real money risks. Unit tests appeared there as a separate family, but we only touched them briefly.</p><p>This article is about unit tests only. They live in the same world as other tests, but they solve a different problem. Generic and singular tests ask if the data is shaped correctly. Unit tests ask if the model does what we think it does.</p><p>If we look at the full family of tests, the picture looks like this.</p><ul><li>Generic tests are reusable templates. We attach them to columns and models in YAML through tests. They cover rules like not null, unique, relationships, accepted values and more advanced checks from packages.</li><li>Singular tests are focused SQL files. Each file returns the rows that must not exist. Missing project_id in a transaction. Orphan organization_id that does not exist in the organization table. Transactions from the future.</li><li>Unit tests treat a model as a small function. We define input fixtures, run the model, and compare the result with an expected set of rows. This is where we protect bridges, slow changing dimensions, and incremental logic.</li></ul><p>If you see many similar singular tests copied across models, it usually means a generic test or a package could do the job better. If you have a fragile business rule, complex joins, or incremental behavior, unit tests are the right tool.</p><p>Before we look at YAML, it helps to decide where unit tests live in the project. Physically they sit next to other tests. In <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d">Article 7</a> we kept all tests in the tests folder and dbt discovered them automatically. If one day you decide to split test types into separate folders, the process is the same as before. You extend the test paths in dbt_project.yml and then reset the environment with dbt clean and dbt deps so dbt can see the new structure.</p><p>A typical layout looks like this</p><pre>tests/<br>  staging/<br>    singular/<br>    unit/<br>  intermediate/<br>    singular/<br>    unit/<br>      unit_intermediate__dim_org_project.yml<br>      unit_intermediate__fct_transactions.yml<br>  marts/<br>    singular/<br>    unit/</pre><p>Singular tests in this tree are SQL files. Generic tests mostly live in shared YAML files that describe sources and models. Unit tests are different. They live in separate YAML files, one file per model, with several scenarios inside.</p><p>The naming pattern mirrors the one we already used for other tests</p><pre>tests/intermediate/unit/unit_intermediate__dim_org_project.yml<br>tests/intermediate/unit/unit_intermediate__fct_transactions.yml</pre><p>Compared to singular tests, which are plain SQL, unit tests are always YAML. Compared to generic tests, which we often keep in a big YAML file that describes many tables at once, unit tests live in their own file per model. It keeps each file focused and easier to read.</p><p>The basic format of a dbt unit test is compact</p><pre>version: 2<br><br>unit_tests:<br>  - name: dim_org_project__maps_project_to_org<br>    model: dim_org_project<br><br>    given:<br>      - input: ref(&#39;stg_prod_clickhouse__projects&#39;)<br>        rows:<br>          - { id: &quot;10&quot;, organization_id: &quot;1&quot;,  title: &quot;X&quot; }<br>          - { id: &quot;20&quot;, organization_id: null, title: &quot;Y&quot; }<br><br>      - input: ref(&#39;stg_prod_clickhouse__organizations&#39;)<br>        rows:<br>          - { id: &quot;1&quot;, alias: &quot;Org A&quot; }<br><br>    expect:<br>      rows:<br>        - { project_id: &quot;10&quot;, organization_id: &quot;1&quot;,  organization_alias: &quot;Org A&quot;, project_title: &quot;X&quot; }<br>        - { project_id: &quot;20&quot;, organization_id: null, organization_alias: null,    project_title: &quot;Y&quot; }</pre><p>Each scenario has a name, a target model, a given block and an expect block. Given describes which inputs we feed into the model. It can reference ref(..) and source(..), and it supports several fixture formats. Expect describes which rows we want to see at the output.</p><p>There is one nuance that is easy to miss. dbt treats given and expect differently. In given you must describe rows in enough detail for the model to run. That means using real column names, filling all non nullable fields, and respecting actual data types from the staging models. In expect you can list only the columns that you really want to check. The model will still compute all other fields, but the test will ignore them.</p><p>A practical mental model is this</p><ul><li>In given you describe realistic rows so the model has everything it needs.</li><li>In expect you focus on the columns that represent the business rule that you want to pin down.</li></ul><p>This keeps fixtures close to real data while each scenario still stays focused on one specific question and does not mirror the entire table structure.</p><p>ClickHouse makes these rules more strict. It does not accept null in non nullable fields and it does not silently cast arbitrary strings into integers. If a staging model has id as String and fake as UInt8, a unit test that tries to feed null into these columns will fail before the model logic even runs. The error will be a type conversion failure, not a mismatch in expect.</p><p>We saw this with the transactions staging model. It has a long list of non nullable fields such as id, funnel_id, funnel_revision, profile_id, project_id and so on. A minimal fixture that sets only id, project_id, created_at and price_usd may look attractive, but it will never reach the target model. ClickHouse will try to cast implicit null values into these non nullable columns and throw an error.</p><p>The safe rule is simple:</p><ul><li>If a column is non nullable in the schema, give it some value in the fixture, even a dummy one</li><li>If a column is Nullable, you are free to use null</li></ul><p>For DateTime fields, use valid timestamps. For text fields, short strings are enough. This adds a bit of noise to fixtures, but avoids strange failures that have nothing to do with the business logic that you are trying to test.</p><p>A more realistic unit test for dim_org_project looks like this</p><pre>version: 2<br><br>unit_tests:<br>  - name: dim_org_project__maps_project_to_org<br>    model: dim_org_project<br><br>    given:<br>      - input: ref(&#39;stg_prod_clickhouse__projects&#39;)<br>        rows:<br>          - { id: &quot;10&quot;, organization_id: &quot;1&quot;,  subdomain: &quot;s&quot;,  custom_domain: null, title: &quot;X&quot;,<br>              icon_url: null, created_at: &quot;2025-01-01 00:00:00&quot;, updated_at: &quot;2025-01-01 00:00:00&quot;,<br>              _version: &quot;2025-01-01 00:00:00&quot; }<br>          - { id: &quot;20&quot;, organization_id: null, subdomain: &quot;s2&quot;, custom_domain: null, title: &quot;Y&quot;,<br>              icon_url: null, created_at: &quot;2025-01-01 00:00:00&quot;, updated_at: &quot;2025-01-01 00:00:00&quot;,<br>              _version: &quot;2025-01-01 00:00:00&quot; }<br><br>      - input: ref(&#39;stg_prod_clickhouse__organizations&#39;)<br>        rows:<br>          - { id: &quot;1&quot;, alias: &quot;Org A&quot;, description: null, code: &quot;&quot;,<br>              enabled: 1, created_at: &quot;2025-01-01 00:00:00&quot;,<br>              updated_at: &quot;2025-01-01 00:00:00&quot;, _version: &quot;2025-01-01 00:00:00&quot; }<br><br>    expect:<br>      rows:<br>        - { project_id: &quot;10&quot;, organization_id: &quot;1&quot;,   organization_alias: &quot;Org A&quot;, project_title: &quot;X&quot; }<br>        - { project_id: &quot;20&quot;, organization_id: null, organization_alias: null,     project_title: &quot;Y&quot; }</pre><p>Here we follow the actual schemas of both staging models. All non nullable fields are filled with simple values. At the same time the expect block stays small. It only checks that project 10 is attached to organization 1 with alias Org A and that project 20, which has organization_id missing, still appears in the result and is not silently dropped.</p><p>Once we add the expect block to this example, the whole story becomes clear. We verify that a project with a filled organization_id is mapped to the right organization and that a project without organization_id continues to exist in the bridge. This is the same scenario we discussed when we first built dim_org_project in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">third article</a>. The unit test locks it into code and protects it from refactors that might break the join in subtle ways.</p><p>dbt supports several ways to describe fixtures. In practice you will switch between them depending on the size of the scenario.</p><p>For small scenarios rows is usually enough</p><pre>rows:<br>  - { id: &quot;10&quot;, organization_id: &quot;1&quot;, title: &quot;X&quot;, ... }<br>  - { id: &quot;20&quot;, organization_id: null, title: &quot;Y&quot;, ... }</pre><p>If you need dozens of similar rows, csv can be more practical and readable, especially when you want to copy a table from a spreadsheet. For very specific ClickHouse behavior, or when you need expressions, casts, or functions, sql fixtures act like a tiny source table created from a custom SELECT.</p><p>The rule of thumb is simple. Use rows when you test a handful of edge cases. Use csv when the volume grows and a table view helps. Use sql when you need full control over types and expressions.</p><p>Unit tests pay for themselves in two main types of models:</p><ul><li>The first group are bridges and models that restore relationships. dim_org_project is a good example. It reconstructs the organization for transactions where organization_id is missing and carries human readable attributes down into facts and marts. If this logic breaks, downstream reports will still look reasonable, but will attribute money to the wrong clients.</li><li>The second group are aggregates with business rules. Models that filter fake transactions, group by different time grains, compute buckets like over 10k MTR per month, or join several facts together. These models are usually short in code but dense in assumptions. A tiny refactor can change filter order or double count a subset of rows.</li></ul><p>Incremental models form a separate category. Here unit tests help us simulate the conflict between existing data and new rows and see exactly how the model resolves it.</p><p>In an incremental unit test we usually have two inputs. The first one is this, which represents the current content of the target table. The second one is a staging model that brings fresh rows. To make dbt behave as if the model runs in incremental mode, we also override the is_incremental macro in the test</p><pre>version: 2<br><br>unit_tests:<br>  - name: fct_transactions__incremental_merge<br>    description: &quot;Incremental: update id 2 and insert id 5&quot;<br>    model: fct_transactions<br><br>    overrides:<br>      macros:<br>        is_incremental: true<br><br>    given:<br>      - input: this<br>        rows:<br>          - {<br>              id: &quot;2&quot;, project_id: &quot;10&quot;,<br>              created_at: &quot;2025-11-01 00:00:00&quot;,<br>              price_usd: 200, vendor: &quot;stripe&quot;, vendor_transaction_id: &quot;vt&quot;,<br>              billing_reason: &quot;subscription_start&quot;, payment_method: &quot;card&quot;,<br>              purchased_at: &quot;2025-11-01 00:00:00&quot;,<br>              organization_id: &quot;&quot;, status: &quot;succeeded&quot;<br>            }<br><br>      - input: ref(&#39;stg_prod_clickhouse__transactions&#39;)<br>        rows:<br>          - {<br>              id: &quot;2&quot;, project_id: &quot;10&quot;,<br>              created_at: &quot;2025-11-01 00:01:00&quot;, price_usd: 250,<br>              vendor: &quot;stripe&quot;, vendor_subscription_id: null, <br>              billing_reason: &quot;subscription_start&quot;,payment_method: &quot;card&quot;,<br>              purchased_at: &quot;2025-11-01 00:00:00&quot;,<br>              organization_id: &quot;&quot;, status: &quot;succeeded&quot;<br>            }<br>          - {<br>              id: &quot;5&quot;, project_id: &quot;10&quot;,<br>              created_at: &quot;2025-11-01 00:01:00&quot;, price_usd: 150,<br>              vendor: &quot;stripe&quot;, vendor_subscription_id: null,<br>              billing_reason: &quot;subscription_start&quot;,payment_method: &quot;card&quot;,<br>              purchased_at: &quot;2025-11-01 00:00:00&quot;,<br>              organization_id: &quot;&quot;, status: &quot;succeeded&quot;<br>            }<br><br>    expect:<br>      rows:<br>        - { id: &quot;2&quot;, project_id: &quot;10&quot;, price_usd: 250, status: &quot;succeeded&quot; }<br>        - { id: &quot;5&quot;, project_id: &quot;10&quot;, price_usd: 150, status: &quot;succeeded&quot; }</pre><p>Here the intention is explicit. Before the run we have one transaction with id 2 and price_usd 200. After the run we expect id 2 to be updated to 250 and a new id 5 to appear with 150. All other fields will also be computed by the model, but we do not list them in expect because they do not matter for this particular check.</p><p>There are a couple of small details to remember. If the model uses a time window based on the current clock, for example created_at greater than or equal to today minus sixty days, fixtures must fall into that window. Otherwise the model may simply ignore them. In tests it is usually easier to avoid binding business logic to now or to use a window that always includes the demo data.</p><p>Running unit tests uses the same selectors that we introduced in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d">Article 7</a>.</p><p>All unit tests in the project</p><pre>dbt test --select &quot;test_type:unit&quot;</pre><p>Unit tests of a single model</p><pre>dbt test --select &quot;dim_org_project test_type:unit&quot;</pre><p>One specific scenario</p><pre>dbt test --select &quot;unit_test:fct_transactions__incremental_merge&quot;</pre><p>There is one trap when the project grows. dbt parses all tests before running any of them. If one unit test has invalid YAML or a broken fixture, it can stop the whole run even when you use a selector for a different test. The options are simple. Either fix the broken test quickly or temporarily disable it with enabled: false until you have time to revisit it.</p><p>At this stage in the series we already have several layers that work together. <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">Staging models </a>that clean raw ClickHouse tables.<a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3"> Intermediate models</a> that restore relationships and compute core business facts. <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">Seeds and snapshots</a> for lookups and slow history. <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d">Analyses for one off</a> questions. <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d">Generic and singular tests that protect data quality</a>. And unit tests that keep model logic honest, especially in complex bridges and incremental flows.</p><p>In the final article we will plug this project into an orchestrator. We will use it to run dbt on a schedule, enforce tests as part of the pipeline, make sure that a green build really means something, and send notifications when something goes wrong so issues do not stay hidden in logs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=97aeb3825d45" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-8-unit-tests-as-seatbelts-for-model-logic-97aeb3825d45">Hands-on dbt with ClickHouse: #8 Unit tests as seatbelts for model logic</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #7 Why tests matter and why a green build does not prove anything by…]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/c15f1cb1264d</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Tue, 13 Jan 2026 08:28:47 GMT</pubDate>
            <atom:updated>2026-01-16T07:11:58.100Z</atom:updated>
            <content:encoded><![CDATA[<h3>Hands-on dbt with ClickHouse: #7 Why tests matter and why a green build does not prove anything by itself</h3><p>In the first six articles we built a working project on top of ClickHouse. We <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">set up a safe local sandbox</a>, connected raw tables, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">created the staging layer</a>, and <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">introduced the rule</a> that project_id is always present while organization_id may be missing and can be restored through dim_org_project. We <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">configured materializations</a>, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">added seeds and snapshots</a>, and <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d">learned to store ad hoc investigations in the analyses folder</a>.</p><p>Now for a topic that is less pleasant but absolutely essential. All the work we did before means very little if the numbers cannot be trusted. A green dbt build only appears after the tests pass. As we already saw earlier, dbt first creates a temporary table, runs tests against it, and only then swaps it into the target name. Tests are part of the delivery process, not decoration. If they turn red, no revenue numbers should ever reach the business.</p><p>Before we look at the types of tests, we need a place to store them. dbt reads tests from the directories listed in dbt_project.yml. In many projects this is just one folder</p><pre>test-paths: [&quot;tests&quot;]</pre><p>If you prefer to separate different kinds of tests, you can extend the list</p><pre>test-paths: [&quot;tests&quot;, &quot;unit_tests&quot;]</pre><p>After changing the project configuration it is a good idea to refresh the environment</p><pre>dbt clean<br><br>dbt deps</pre><p>In our training project we will keep everything under tests/, but the internal structure will mirror the model layers. This makes it easier to understand where a failure comes from</p><pre>tests/<br>  staging/<br>    singular/<br>  intermediate/<br>    singular/<br>  marts/<br>    ...</pre><p>With this structure in place we can start looking at each test type and how it fits into the flow.</p><p>Singular tests are simple SQL files that live inside tests/&lt;layer&gt;/singular. They return the rows that violate an expected rule. If the query returns zero rows the test is green. If it returns one or more rows the test is red. This type works well when you have a clear scenario that must never happen. For example, an id in the organization dictionary cannot be empty</p><pre>-- tests/staging/singular/stg_prod_clickhouse__organizations__id.sql<br><br>SELECT<br>    id,<br>    alias<br>FROM {{ ref(&#39;stg_prod_clickhouse__organizations&#39;) }}<br>WHERE id IS NULL<br>   OR id = &#39;&#39;</pre><p>Notice that it uses ref(..) instead of source(..). We test the staging model, not the raw table. This is the same pattern we used when we first built the staging layer.</p><p>To run all singular tests we can rely on the selector that dbt provides</p><pre>dbt test --select &quot;test_type:singular&quot;</pre><p>To run one specific test we use its name</p><pre>dbt test --select &quot;staging__transactions__project_id&quot;</pre><p>Singular tests run against real materialized tables. If a staging model does not exist yet, dbt has nothing to test.</p><p>Sometimes a binary answer is not enough. Earlier in the series we agreed that losing project_id breaks the revenue attribution chain forever. Missing organization_id is tolerable because dim_org_project can restore the relationship later. dbt has a mechanism for expressing this difference through severity, warn_if and error_if.</p><p>These parameters work together.</p><ul><li>severity sets whether the test raises a warning or an error</li><li>warn_if defines when a warning should appear</li><li>error_if defines when the test should fail</li></ul><p>Expressions like &gt;0 mean exactly what they say. If the number of bad rows is greater than zero, the threshold is crossed.</p><p>For project_id the threshold is zero. Even one missing value is a blocker</p><pre>-- tests/staging/singular/staging__transactions__project_id.sql<br><br>{{ config(<br>    severity = &#39;error&#39;,<br>    warn_if = &#39;&gt;0&#39;,<br>    error_if = &#39;&gt;0&#39;<br>) }}<br><br>SELECT<br>    transaction_id,<br>    project_id<br>FROM {{ ref(&#39;stg_prod_clickhouse__transactions&#39;) }}<br>WHERE project_id IS NULL<br>   OR project_id = &#39;&#39;</pre><p>For organization_id the rule is more relaxed. A few gaps are fine. Hundreds are not</p><pre>-- tests/staging/singular/staging__transactions__orphan_organization_id.sql<br><br>{{ config(<br>    severity = &#39;warn&#39;,<br>    warn_if = &#39;&gt;0&#39;,<br>    error_if = &#39;&gt;100&#39;<br>) }}<br><br>SELECT<br>    t.transaction_id,<br>    t.organization_id<br>FROM {{ ref(&#39;stg_prod_clickhouse__transactions&#39;) }} AS t<br>LEFT JOIN {{ ref(&#39;stg_prod_clickhouse__organizations&#39;) }} AS o<br>    ON t.organization_id = o.id<br>WHERE t.organization_id IS NOT NULL<br>  AND t.organization_id != &#39;&#39;<br>  AND o.id IS NULL</pre><p>Here we encode reality in code. Losing project_id means money will never reach the right organization, so we fail immediately. A missing organization_id is acceptable up to a point, because we can still restore the link through the project.</p><p>Generic tests are the next step up. We already used them earlier when we defined sources and columns. A generic test is a reusable rule that dbt expands into SQL. The built in set includes several simple but powerful checks.</p><ul><li>not_null ensures the field is always present</li><li>unique ensures that each value appears only once</li><li>relationships requires that each key exists in a referenced table</li><li>accepted_values restricts allowed values to a given list</li></ul><p>Each test supports attributes such as severity, warn_if and error_if. accepted_values supports a list of allowed values. relationships points to the target table and column.</p><p>A simple example for a primary key in a dictionary</p><pre>columns:<br>  - name: id<br>    tests:<br>      - not_null<br>      - unique</pre><p>A relationship between projects and organizations</p><pre>columns:<br>  - name: organization_id<br>    tests:<br>      - not_null<br>      - relationships:<br>          to: source(&#39;prod_clickhouse&#39;, &#39;organizations&#39;)<br>          field: id</pre><p>Under the hood dbt turns these definitions into SQL that returns failing rows. We do not see that SQL in day to day work, but we see the result in test reports.</p><p>To run all generic tests we can use another selector</p><pre>dbt test --select &quot;test_type:generic&quot;</pre><p>If built in checks are not enough, there is an ecosystem of packages that extend dbt.</p><ul><li><strong>dbt_utils</strong> brings integrity checks and helper macros for common patterns</li><li><strong>dbt_expectations</strong> adds expectation style tests and richer data quality checks</li><li>some packages focus on specific architectures such as data vault</li><li>some act as linters for the project itself, checking naming, metadata and folder structure</li></ul><p>In our training project the built in tests are more than enough. The important part is to get used to declaring expectations in YAML instead of copying and pasting SQL.</p><p>Generic tests usually live inside the main sources YAML. This file acts as a data contract. It describes raw tables, columns, relationships and freshness rules. A fragment may look like this</p><pre>version: 2<br><br>sources:<br>  - name: prod_clickhouse<br>    schema: training_raw<br><br>    tables:<br>      - name: projects<br>        description: &quot;projects, their id, etc.&quot;<br>        columns:<br>          - name: id<br>            description: &quot;project identifier in our database&quot;<br>            tests:<br>              - not_null<br>              - unique<br><br>          - name: organization_id<br>            description: &quot;organizations, their id, etc.&quot;<br>            tests:<br>              - not_null<br>              - relationships:<br>                  to: source(&#39;prod_clickhouse&#39;, &#39;organizations&#39;)<br>                  field: id<br>                  severity: warn</pre><p>Here documentation, data quality rules and relationships live together. The same file can also define freshness checks for transactional tables</p><pre> - name: transactions<br>        description: &quot;data about end-user transactions in client apps&quot;<br>        columns:<br>          - name: id<br>            tests:<br>              - not_null<br>              - unique<br><br>          - name: project_id<br>            tests:<br>              - not_null:<br>                  severity: error<br>              - relationships:<br>                  to: source(&#39;prod_clickhouse&#39;, &#39;projects&#39;)<br>                  field: id<br>                  severity: warn<br><br>        freshness:<br>          warn_after:<br>            count: 6<br>            period: hour<br>          error_after:<br>            count: 1<br>            period: day<br>          filter: &quot;created_at &gt;= now() - toIntervalDay(3) and fake = 0&quot;<br>        loaded_at_field: created_at</pre><p>The YAML now captures several things at once. It documents raw tables. It tells dbt to check that keys and links are valid. It defines how fresh real money should be. It also shows which fields are critical enough to fail the build if they break.</p><p>It is useful to think about severity and thresholds not only at the level of individual tests, but also per layer. On staging we usually accept some noise in the data. We still want to see it, but not every problem is a reason to stop the pipeline. On future marts that face the business we do not have this luxury.</p><p>dbt lets us set default behavior for whole folders in dbt_project.yml</p><pre>models:<br>  training_project:<br>    staging:<br>      +materialized: table<br><br>      +severity: warn<br>      +warn_if: &quot;&gt;0&quot;<br>      +error_if: &quot;&gt;500&quot;</pre><p>This configuration means that for models under models/staging/** tests will act as warnings by default. We want to see issues as soon as at least one row is affected, but we will not fail the build until the problem is large enough. Critical checks, like the presence of project_id, override these defaults locally and fail on the first bad row.</p><p>For marts the policy is usually stricter. These models aggregate and present money to managers. Any serious inconsistency is a stop signal, not a notification.</p><p>With data tests in place we can build a simple flow that will later become a CI pipeline. It starts with dependencies, seeds and snapshots</p><pre>dbt deps<br>dbt seed<br>dbt snapshot</pre><p>Before building anything it is worth checking that fresh data actually arrived</p><pre>dbt source freshness</pre><p>If freshness shows that transactions have not arrived for many hours, there is no point in rebuilding layers. You will only recompute yesterday’s numbers.</p><p>Then we move layer by layer. For staging</p><pre>dbt run  --select &quot;staging&quot;<br>dbt test --select &quot;staging&quot;</pre><p>For intermediate models</p><pre>dbt run  --select &quot;intermediate&quot;<br>dbt test --select &quot;intermediate&quot;</pre><p>For future marts you would follow the same pattern once they appear in the project. The important part is that tests run close to where logic lives. Critical failures on staging, such as missing project_id, should stop the pipeline immediately. Warnings can be tolerated if you know exactly why they appear and how they affect downstream models.</p><p>So far we focused on tests that look at real data in real tables. They catch missing keys, broken links and freshness issues. There is one more layer that looks at transformation logic itself. These are unit tests. Instead of checking a live table they build a tiny temporary environment for a single model, feed it prepared input rows and compare the result with an expected set of rows.</p><p>In this article it is enough to know that unit tests exist and that they protect us from a different class of errors. Data tests can tell us that keys are present and that relationships hold. They cannot tell us that a complex transformation quietly moved money to the wrong organization while still producing valid looking rows. This is where unit tests become important.</p><p>We will return to unit tests in the next article and build them step by step for dim_org_project and for a revenue fact model. We will see how they catch bugs that data tests alone would miss, and how to keep them realistic in ClickHouse where types and nullability matter a lot.</p><p>At this point in the series we already have a <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">staging layer</a>, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">intermediate logic</a>, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">seeds and snapshots</a> for lookups and history, a <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d">structured way to keep one off analyses</a> and a full set of data tests that guard contracts at each layer. In the next article we will zoom in on unit tests. After that we will be ready to wire everything into an orchestrator, run dbt on a schedule, enforce tests as part of the pipeline, make sure that a green build really means something, and send notifications when something goes wrong.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c15f1cb1264d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-7-why-tests-matter-and-why-a-green-build-does-not-prove-anything-by-c15f1cb1264d">Hands-on dbt with ClickHouse: #7 Why tests matter and why a green build does not prove anything by…</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #6 The analyses/ folder for one-off research in dbt]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/27892ffb4f5d</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Wed, 07 Jan 2026 11:52:17 GMT</pubDate>
            <atom:updated>2026-01-07T11:52:16.357Z</atom:updated>
            <content:encoded><![CDATA[<p>By this point in the series we already have a local ClickHouse + dbt sandbox from the<a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843"> first article</a>, clean sources and staging models <a href="https://medium.com/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">from the second</a>, an intermediate layer with fact and dim tables from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">the third</a>, a clear config and properties setup from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">the fourth</a>, and helper mechanics like seeds and snapshots from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">the fifth</a>. We can already compute revenue by organizations and projects on top of these layers and we understand how BI dashboards could sit on top of them.</p><p>There is still one big area in an analyst’s life that easily turns into chaos. These are one-off analyses. You need to check a hypothesis, look at some metric quickly, or build an ad hoc export for colleagues who do not live in BI. Without dbt this usually ends up as a pile of .sql files in folders, chats, emails, and notebooks. A few months later it is not clear which of the ten almost identical scripts is the “right” one, where tables in the query come from, and which models feed them.</p><p>In dbt there is a dedicated resource type for this work called analysis. These resources live in the analyses/folder. Each analysis is a SQL file with Jinja that does not create any objects in the database and is not executed by dbt run or dbt build. It only goes through dbt compile, after which you get a clean SQL file under target/analysis/… with all ref(..), source(..), and macros expanded. As a result all one-off analyses live next to the model code, use the same sources and layers, and you always have a reproducible query that you can open in DBeaver and run.</p><p>Physically the structure can look like this:</p><pre>analyses/<br>  product/<br>    a01_users_retention_probe.sql<br>    a02_orgs_mtr_buckets_explore.sql<br>  ops/<br>    a10_data_gaps_by_source.sql</pre><p>We simply split analyses by domain and give files descriptive names. Inside each file we write regular SQL with Jinja: {{ ref(...) }}, {{ source(...) }}, var(..), macros, everything we already use in models.</p><p>In dbt commands analysis nodes behave in a very predictable way:</p><ul><li>dbt run and dbt build ignore analyses/, no tables or views are created</li><li>dbt compile creates clean SQL under target/analysis/… with no Jinja</li><li>dbt ls --resource-type analysis shows the list of analysis resources</li><li>dbt compile --select analysis:* compiles all analyses</li><li>dbt compile --select analysis.package.name compiles a specific analysis</li></ul><p>So analysis nodes live in the same world of ref(..) and source(..) but represent research queries instead of production models.</p><p>To make the need more concrete, let us stay close to our training universe. In the third article we already built fct_org_monthly_revenue and computed monthly MTR by organization. Now we want to quickly estimate the contribution of organizations to MTR for an arbitrary date window, highlight heavy customers above a threshold, and avoid rewriting SQL every time the period changes.</p><p>This is a perfect candidate for an analysis node. It is a one-off slice that is easy to reuse, but there is no reason to promote it to a full mart. It helps us see who drives revenue, how MTR is distributed, which thresholds make sense for the “top” bucket, and which windows are reasonable for marts and alerts.</p><p>The simplest version of such an analysis can look like this:</p><pre>-- analyses/product/a02_orgs_mtr_buckets_explore.sql<br>-- One-off cut of org MTR with a configurable date window<br>-- Example:<br>-- dbt compile --select analysis.product.a02_orgs_mtr_buckets_explore \<br>--   --vars &#39;{month_from: &quot;2025-01-01&quot;, month_to: &quot;2025-06-30&quot;}&#39;<br>WITH base AS (<br>    SELECT<br>        organization_id,<br>        organization_alias,<br>        month,<br>        org_mtr<br>    FROM {{ ref(&#39;fct_org_monthly_revenue&#39;) }}<br>    WHERE month BETWEEN toDate(&#39;{{ var(&quot;month_from&quot;, &quot;2025-01-01&quot;) }}&#39;)<br>                    AND toDate(&#39;{{ var(&quot;month_to&quot;,   &quot;2025-12-31&quot;) }}&#39;)<br>)<br>SELECT<br>    organization_id,<br>    anyLast(organization_alias) AS organization_alias,<br>    SUM(org_mtr) AS mtr_total,<br>    COUNT() AS months_count,<br>    SUM(org_mtr) &gt;= 50000 AS is_top_bucket<br>FROM base<br>GROUP BY organization_id<br>ORDER BY mtr_total DESC<br>LIMIT 200</pre><p>We build on top of fct_org_monthly_revenue that already lives in the intermediate layer. In the most naive approach we could hardcode both dates and the threshold directly in WHERE and in the flag calculation. That would work, but the query would be almost single use. As soon as the window changes, we edit the text again.</p><p>Here we take a small step forward and move only what we usually change by hand into var(..), which is the date window.</p><ul><li>month_from and month_to define the period boundaries</li><li>the threshold of 50 000 is still hardcoded as SUM(org_mtr) &gt;= 50000</li></ul><p>The query already becomes a bit more alive. To look at another window we do not touch the file, we just change the parameters:</p><pre>dbt compile --select analysis.product.a02_orgs_mtr_buckets_explore \<br>  --vars &#39;{month_from: &quot;2025-03-01&quot;, month_to: &quot;2025-08-31&quot;}&#39;</pre><p>After dbt compile the clean SQL with substituted dates appears in target/analysis/…. We only need to open it in the database client. This is the basic level of flexibility: one analysis script works for different periods without code changes.</p><p>If later we see that the threshold also changes all the time, we can move it to a variable in the same way:</p><pre>SUM(org_mtr) &gt;= toFloat64(&#39;{{ var(&quot;mtr_bucket_min&quot;, 50000) }}&#39;) AS is_top_bucket</pre><p>and call the analysis with three parameters, including different thresholds for different questions.</p><p>Sometimes even this is not enough. Instead of a simple ranking we might want to “spread” MTR for specific organizations into separate columns, for example to quickly compare a pair or a small set of customers side by side. At this point we go one level deeper and use Jinja not only for parameters but also to generate columns in the SELECT clause.</p><p>Here is a variation based on the same query:</p><pre>{% set org_ids = var(&quot;org_ids&quot;, [1, 2]) %}<br><br>WITH base AS (<br>    SELECT<br>        organization_id,<br>        organization_alias,<br>        month,<br>        org_mtr<br>    FROM {{ ref(&#39;fct_org_monthly_revenue&#39;) }}<br>    WHERE month BETWEEN toDate(&#39;{{ var(&quot;month_from&quot;, &quot;2025-01-01&quot;) }}&#39;)<br>                    AND toDate(&#39;{{ var(&quot;month_to&quot;,   &quot;2025-12-31&quot;) }}&#39;)<br>)<br><br>SELECT<br>  {%- for oid in org_ids -%}<br>  SUM(CASE WHEN organization_id = {{ oid }} THEN org_mtr ELSE 0 END) AS org_{{ oid }}_mtr<br>  {%- if not loop.last %},{% endif %}<br>  {%- endfor %}<br>FROM base</pre><p>Several things happen in this template.</p><p>First, with set org_ids we define the list of organization_id values that we care about. We can override it from the command line:</p><pre>dbt compile --select analysis.product.a02_orgs_mtr_buckets_explore \<br>  --vars &#39;{org_ids: [1,2,3], month_from: &quot;2025-01-01&quot;, month_to: &quot;2025-06-30&quot;}&#39;</pre><p>Then the for loop generates one aggregated column for each org_id.<br>if not loop.last keeps commas between columns in the right places so the final SQL is valid. After compilation we get a clean static query that ClickHouse can execute directly.</p><p>This approach saves a lot of manual work when the list of organizations changes often. We do not add or remove columns by hand and we do not worry about commas. It is only important to keep the balance. Jinja generation makes the template easier to maintain, but the code becomes less obvious to a new reader. If the template turns into something “magical”, it is usually better to fall back to explicit columns, use a PIVOT in BI, move logic into a macro, or add a separate step to the pipeline.</p><p>Like other dbt resources, analysis nodes can be described in YAML so that they show up in dbt docs. This becomes useful once you have dozens of research queries.</p><p>An example YAML definition:</p><pre>version: 2<br><br>analyses:<br>  - name: a02_orgs_mtr_buckets_explore<br>    description: &quot;One-off MTR cut with a configurable date window and bucket threshold&quot;<br>    meta:<br>      owner: &quot;analytics@company.tld&quot;<br>      domain: &quot;revenue&quot;<br>    tags: [&quot;exploration&quot;, &quot;mtr&quot;, &quot;org&quot;]</pre><p>Two points matter here.</p><p>First, the top level block is analyses:, not models:. We explicitly tell dbt that this is an analysis, not a model.</p><p>Second, materializations and tests do not apply to analysis nodes. We do not write materialized=’table’ or tests: […] for them. This would not have any effect, because analysis nodes never create tables. On the other hand description, meta, and tags work very well as extra documentation and make search in dbt docs much easier.</p><p>In daily work the loop around an analysis node is quite simple.</p><p>We start by creating a file under analyses/…/*.sql and write SQL with ref(..), source(..), and var(..), ideally without SELECT *. We rely on the existing staging and intermediate models and only pick the columns that we need.</p><p>Then, if it is worth it, we add a YAML description with an analyses: block that contains description, meta, and tags. This is a good place to link to a ticket or a Notion or Confluence page with the full research description.</p><p>After that we compile the analysis. We can compile all of them:</p><pre>dbt compile --select analysis:*</pre><p>or a specific one with parameters:</p><pre>dbt compile --select analysis.product.a02_orgs_mtr_buckets_explore \<br>  --vars &#39;{month_from: &quot;2025-01-01&quot;, month_to: &quot;2025-06-30&quot;, mtr_bucket_min: 75000}&#39;</pre><p>The compiled SQL appears under target/analysis/…. We open it in DBeaver, run it, look at the result, and discuss findings with the team. The .sql file and YAML stay in the repo, so the history of the research does not disappear and we can repeat it or refine it later.</p><p>A few practical notes:</p><ul><li>Analysis nodes never create database objects. They only turn Jinja into SQL. If a research query matures into a product and becomes a stable report or mart, we move the logic into models/ on the right layer, set materialized, configure engine and partitions for ClickHouse, and add tests.</li><li>It is helpful to keep formatting aligned with the rest of the project. SQL keywords in upper case, no SELECT *, date windows limited by parameters so they do not slide too far into the past. This makes queries easier to read and reduces the risk that a research query sneaks into production and accidentally overloads the database.</li><li>We should not forget about the data itself. Code in the repository is easy to reuse and is visible in code review, so analysis nodes are a bad place for sensitive data that should never end up in git or for logic that would be hard to anonymize later.</li></ul><p>If we describe analysis nodes in YAML, dbt docs becomes a documentation site for research queries in terms of code.</p><ul><li>analyses show up in navigation and search together with models</li><li>description, meta, tags, and owner give a quick idea about the context</li><li>tickets and Notion or Confluence pages can be linked directly from YAML</li></ul><p>At the same time we should keep expectations realistic. The analyses/ folder is not part of the execution DAG. It documents the code of research queries, not their artifacts such as tables, charts, or notebooks.</p><p>In most teams it is more convenient to split responsibilities like this:</p><ul><li>keep SQL for research in analyses/ inside the dbt project, compile it to target/analysis/…, and connect it to models through ref(..) and source(..)</li><li>keep the full research page with context, conclusions, charts, links to dashboards, tables, and notebooks in Confluence or Notion</li><li>in YAML under analyses: keep a short description, meta, tags, and a link to the main research page</li></ul><p>At the same time analytics is not only about SQL. A significant part of research often lives in Python notebooks. They contain sample preparation, visualizations, statistics, and experiments. These notebooks usually sit in a separate git repository, not in the dbt project. Ideally we think about the analytics knowledge base as a whole. This is already outside the scope of this course, so here it is enough to give a recommendation for the entry point. Make Confluence or Notion the single door into the knowledge base. From there link either to dbt research documentation and dbt docs or to Python notebooks in git, depending on what the analyst used. This gives us a single source of truth for SQL inside dbt and a single hub for meanings and research artifacts in the corporate wiki.</p><p>In the end the analyses/ folder becomes a structured way to keep and reuse SQL research inside a dbt project.</p><ul><li>dbt compiles clean SQL for us, so manual copy paste of queries into git disappears</li><li>dbt docs lets us search and read research code in the same place as models</li><li>the full research documentation lives in a shared hub and is linked from YAML, so the knowledge base does not fragment across tools</li></ul><p>After the sixth article our training project has not only layers for regular analytics and mechanics for lookups and history, but also a clear way to capture one-off business questions and turn useful analyses into stable marts when they are ready. In the next articles we will build on top of this and talk about tests, data quality, and automation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=27892ffb4f5d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-6-the-analyses-folder-for-one-off-research-in-dbt-27892ffb4f5d">Hands-on dbt with ClickHouse: #6 The analyses/ folder for one-off research in dbt</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #5 Seeds and snapshots for lookup tables and honest history]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/289afcd05ba0</guid>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Tue, 23 Dec 2025 08:46:21 GMT</pubDate>
            <atom:updated>2025-12-23T08:46:20.075Z</atom:updated>
            <content:encoded><![CDATA[<p>By now in this series we already have a local ClickHouse + dbt sandbox from the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">first article</a>, clean sources and staging models from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">the second article</a>, an intermediate layer with facts and dimensions from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">the third article</a>, and a clear picture of configs and properties from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">the fourth article</a>. We can take raw tables from<strong> training_raw</strong>, turn them into staging models, and then build fact tables in <strong>dbt_training</strong>.</p><p>One thing is still missing. In a real project you almost always need small lookup tables that live under version control, and you often need some form of history for tables that are constantly overwritten by the source. Seeds in dbt cover the first problem. Snapshots cover the second. This article is about both.</p><p>We will start with seeds and then switch to snapshots and historical data.</p><p>In dbt, a seed is a CSV file inside the project that is loaded into the target database as a normal table. The file lives in the repository, so it is tracked by git and goes through code review together with the rest of the project. This brings clear limits. We do not put any sensitive data into seeds.</p><p>In practice seeds work well for three types of tasks:</p><ul><li>small mappings and lookup tables where a code needs to be connected to a readable label or attribute</li><li>short internal dictionaries, such as lists of teams, employees, or statuses</li><li>exception lists such as test emails or technical IDs that you want to filter out</li></ul><p>And there are cases where a seed is a bad idea:</p><ul><li>large volumes of data that are better loaded through a proper ETL</li><li>attempts to push raw data from external systems into CSV files</li><li>any personal or sensitive data, tokens, passwords, and other secrets, because everything in the repo is visible to other people at some point</li></ul><p>By default dbt reads seeds from the seeds/ folder. Each .csv there becomes a table in the target database (in our case <strong>dbt_training</strong>) with the same name as the file.</p><p>To make this less abstract, let us use a model example. We will build a small dictionary that tells us which CS manager is responsible for which organization over time. This is a typical manual lookup that is easy to edit in a CSV file and easy to track through git.</p><p>The data can look like this: seeds/ops/managers_by_org.csv</p><pre>manager,organizations_id,start_date,end_date<br>alice_k,101,2024-01-01,2024-06-30<br>alice_k,102,2024-03-15,2024-12-31<br>bob_m,101,2024-07-01,9999-12-31<br>carol_s,103,2024-02-01,2024-09-15</pre><p>A couple of notes:</p><ul><li>the date 9999–12–31 can be treated as “until now”</li><li>the column name organizations_id is left as is as a model example, even though in the main schema we use organization_id</li></ul><p>If we run dbt seed --select managers_by_org we will see a table dbt_training.managers_by_org in DBeaver. This is enough to show the mechanics. A CSV file in the repo becomes a physical table in the warehouse. In the training project that the reader can run locally in this series, we will not include this seed to keep the structure small. It stays as a separate model example that you can play with on your own.</p><p>Just like models, seeds can be described in YAML. We can set column types, descriptions, and basic tests. The way we configure seeds follows the same pattern as for models in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">the fourth article</a>. There are three levels:</p><ul><li>per seed, in a YAML file next to it</li><li>per seeds folder, through dbt_project.yml</li><li>for all seeds in the project, also through dbt_project.yml</li></ul><p>The closer a setting is to a specific seed, the stronger it is. A local config next to the seed overrides values from dbt_project.yml.</p><p>Here is a YAML definition for managers_by_org that lives next to the CSV:</p><pre># seeds/ops/_seeds__ops.yml<br>version: 2<br><br>seeds:<br>  - name: managers_by_org<br>    description: &gt;<br>      Mapping between CSM managers and organizations over time.<br>      Model example: a small CSV loaded via dbt seed.<br>    config:<br>      # Delimiter (comma by default). Here we show a semicolon:<br>      delimiter: &quot;;&quot;<br>      # Explicit types for ClickHouse:<br>      column_types:<br>        manager: String<br>        organizations_id: Int64<br>        start_date: Date<br>        end_date: Date<br><br>    columns:<br>      - name: manager<br>        description: CSM manager login or identifier.<br>        tests: [not_null]<br><br>      - name: organizations_id<br>        description: Organization identifier.<br>        tests:<br>          - not_null<br>          - relationships:<br>              to: ref(&#39;dim_org_project&#39;)   # we built it in the third article<br>              field: organization_id<br><br>      - name: start_date<br>        description: Start date of responsibility, inclusive.<br>        tests: [not_null]<br><br>      - name: end_date<br>        description: &gt;<br>          End date of responsibility, inclusive.<br>          Value 9999-12-31 means &quot;until now&quot;.<br>        tests: [not_null]</pre><p>If we want a tab character as a delimiter, we can write:</p><pre>config:<br>  delimiter: &quot;\t&quot;</pre><p>So far we configured a single seed next to its CSV file. The same pattern as with models also works at higher levels. We can move part of the configuration to dbt_project.yml so we do not repeat it everywhere. This is useful if all seeds share the same delimiter or if you want to place them into one logical schema.</p><p>Here is an example of global settings for seeds:</p><pre>seeds:<br>  training_project:           # project name from dbt_project.yml<br>    +schema: seed_data        # all CSV files → schema seed_data (if the adapter supports schemas)<br>    +delimiter: &quot;;&quot;<br><br>    ops:                      # settings for the seeds/ops/ folder<br>      +schema: seed_ops       # CSV files from seeds/ops/ → schema seed_ops<br>      +delimiter: &quot;|&quot;</pre><p>Here:</p><ul><li>all seeds in the project use schema = seed_data and delimiter = “;” by default</li><li>seeds inside the seeds/ops/ folder override this with schema = seed_ops and delimiter = “|”</li></ul><p>The behavior is the same as for models. Project level, then folder level, then resource level, with the same “closer wins” priority.</p><p>In Postgres or Snowflake the table from seeds/ops/managers_by_org.csv will be created as seed_ops.managers_by_org or in seed_data if there is no folder override.</p><p>ClickHouse does not have schemas inside a database in the same way. At this level schema is closer to a database name. A +schema setting will not create a subschema inside <strong>dbt_training</strong>. To isolate seeds in ClickHouse you normally use one of these options:</p><ul><li>a separate database for seeds, such as dbt_seed</li><li>table name prefixes, for example seed_managers_by_org in the same <strong>dbt_training</strong> database</li></ul><p>If we set delimiter: “;”, the CSV file should look like this:</p><pre># seeds/ops/managers_by_org.csv<br>manager;organizations_id;start_date;end_date<br>alice_k;101;2024-01-01;2024-06-30<br>alice_k;102;2024-03-15;2024-12-31<br>bob_m;101;2024-07-01;9999-12-31<br>carol_s;103;2024-02-01;2024-09-15</pre><p>This YAML turns the seed into a full dbt object with descriptions, types, and tests that follows the same config rules as other models.</p><p>From the point of view of models, a seed is just a normal table in our analytic database. In SQL we refer to it with ref(...), the same way we refer to other dbt models. Raw tables still come through source(…), just as we did in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">second article</a>.</p><p>For example we can take fct_project_monthly_revenue from <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">the third article</a> and enrich it with managers:</p><pre>-- models/intermediate/prod_clickhouse/fct_project_monthly_revenue_with_mgr.sql<br>{{ config(materialized=&#39;table&#39;) }}<br><br>SELECT<br>    pmr.organization_id,<br>    pmr.organization_alias,<br>    pmr.project_id,<br>    pmr.project_title,<br>    pmr.month,<br>    pmr.project_mtr,<br>    m.manager AS csm_manager<br>FROM {{ ref(&#39;fct_project_monthly_revenue&#39;) }} AS pmr<br>LEFT JOIN {{ ref(&#39;managers_by_org&#39;) }} AS m<br>    ON m.organizations_id = pmr.organization_id<br>   AND pmr.month BETWEEN m.start_date AND m.end_date</pre><p>If we want to rebuild only models that depend on this seed, we can run:</p><pre>dbt run --select managers_by_org+</pre><p>dbt will build the correct subgraph in the DAG. The seed itself will not be touched in this command.</p><p>To keep seeds predictable, it helps to follow a small hygiene checklist.</p><ul><li><strong>Column types</strong>. In ClickHouse everything comes as a string by default. If you plan to compare dates or numbers, set column_types in YAML</li><li><strong>Updates</strong>. dbt seed always reloads the table. There is no incremental mode. Each run fully rewrites the content.</li><li><strong>CSV hygiene</strong>. Use a single encoding, usually UTF-8 without BOM. Have one header row and a consistent delimiter. Wrap text that contains the delimiter in quotes.</li><li><strong>Git</strong>. Every seed is code. It goes through review, keeps a change history, and can be rolled back.</li><li><strong>Size</strong>. If a CSV grows to tens of megabytes, there is usually a better solution through a source or a model instead of a seed.</li></ul><p>Once the basics of seeds are clear, we can move to snapshots. They keep history where the source table is overwritten every time.</p><p>In our setup snapshots solve the following problem. We do not use specialized Change Data Capture mechanisms (CDC), such as Debezium or streaming updates from transaction logs. Instead we periodically reload a table as it is. Today a transaction has status = ‘paid’. Tomorrow the same transaction_id arrives as status = ‘success’. The source keeps only the current state, but sometimes we need to know which status it had at a specific moment in time.</p><p>It is important to see how snapshots differ from the incremental models that we built in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">third article</a>. There we solved a different problem:</p><ul><li>new transactions keep arriving at the tail</li><li>old transactions sometimes change after the fact</li><li>we want to avoid recalculating the entire history on every run</li><li>at the same time we want to recalculate the live tail honestly</li></ul><p>In that pattern an incremental model knows only the current state of each row at the moment of the last run. It helps us recalculate the hot part of the data without touching the frozen past.</p><p>A snapshot solves another problem. It does not speed up the computation of the current version. It stores the history of changes instead. Every time the source changes, the snapshot adds a new version of the row with a validity interval. The result is a slowly changing dimension table where we can ask “what was true at time T”.</p><p>In practice this sits somewhere between a static table and a full CDC or streaming setup. The history is discrete and depends on how often we run dbt snapshot. If we run it once per day we will see all changes at the day level, but we will not see two changes that happened between two runs.</p><p>This works well for scenarios where a day level history is enough. For example:</p><ul><li>a dashboard for a manager that shows the current task status per day for each employee</li><li>daily subscription status history where we care about the end state of each day, not about every jump between states</li></ul><p>If we need a full timeline of every single change, like in a credit approval process that can start and finish within one day, we should look at CDC or streaming. A snapshot alone will not give this level of detail.</p><p>For each version of a row dbt maintains several technical columns:</p><ul><li>dbt_scd_id is a hash that identifies the logical row</li><li>dbt_updated_at is the moment when dbt recorded this version</li><li>dbt_valid_from is the moment when this version became valid</li><li>dbt_valid_to is the moment when this version stopped being valid, or NULL for the current version</li></ul><p>There are two main strategies to detect that a row has changed.</p><p>The first one is timestamp. We rely on a stable field that changes every time the row is updated, for example updated_at. If the value of updated_at increases, dbt treats this as a new version.</p><p>The second one is check. We list a set of columns in check_cols. If any of them changes, dbt creates a new version. Changes outside this list are ignored for history.</p><p>To show the timestamp strategy we can extend our model seed managers_by_org with an updated_at column and a matching type:</p><pre># seeds/ops/managers_by_org.csv (with &quot;;&quot;)<br>manager;organizations_id;start_date;end_date;updated_at<br>alice_k;101;2024-01-01;2024-06-30;2024-06-30 09:15:00<br>alice_k;102;2024-03-15;2024-12-31;2024-07-01 10:00:00<br>bob_m;101;2024-07-01;9999-12-31;2024-07-01 10:30:00<br>carol_s;103;2024-02-01;2024-09-15;2024-09-15 18:45:00</pre><p>And update the YAML:</p><pre># seeds/ops/_seeds__ops.yml<br>version: 2<br><br>seeds:<br>  - name: managers_by_org<br>    description: Model CSV with an update timestamp.<br>    config:<br>      delimiter: &quot;;&quot;<br>      column_types:<br>        manager: String<br>        organizations_id: Int64<br>        start_date: Date<br>        end_date: Date<br>        updated_at: DateTime</pre><p>Then we reload the seed:</p><pre>dbt seed --full-refresh --select managers_by_org</pre><p>In DBeaver we will see the new column updated_at.</p><p>Now we can set up a snapshot model. Snapshot files live in the snapshots/ folder. Inside each file the snapshot is defined like this:</p><pre>{% snapshot &lt;snapshot_name&gt; %}<br><br>{{ config(...) }}<br><br>SELECT ...<br>FROM ...<br><br>{% endsnapshot %}</pre><p>A good practice is to keep the file name and the snapshot name the same. For example a file snapshots/ops/managers_by_org_snapshot.sql with a block {% snapshot managers_by_org_snapshot %}.</p><p>Here is a snapshot with the timestamp strategy:</p><pre>-- snapshots/ops/managers_by_org_snapshot.sql<br>{% snapshot managers_by_org_snapshot %}<br><br>{{<br>  config(<br>    target_database=&#39;dbt_training&#39;,   -- where to store the historical table<br>    target_schema=&#39;snapshots&#39;,        -- in Postgres or Snowflake this is a separate schema<br>    unique_key=&#39;manager||toString(organizations_id)&#39;,<br>    strategy=&#39;timestamp&#39;,<br>    updated_at=&#39;updated_at&#39;,<br>    hard_deletes=&#39;invalidate&#39;         -- close the version when the source row disappears<br>  )<br>}}<br><br>SELECT<br>  manager,<br>  organizations_id,<br>  start_date,<br>  end_date,<br>  updated_at<br>FROM {{ ref(&#39;managers_by_org&#39;) }}<br><br>{% endsnapshot %}</pre><p>We run it with:</p><pre>dbt snapshot --select managers_by_org_snapshot</pre><p>And if needed we rebuild models that depend on this snapshot:</p><pre>dbt run --select managers_by_org_snapshot+</pre><p>In the database we will see a snapshot table. It includes the original columns plus the four SCD fields:</p><ul><li>dbt_scd_id</li><li>dbt_updated_at</li><li>dbt_valid_from</li><li>dbt_valid_to which is NULL for the current version</li></ul><p>If we change the CSV file, for example adjust end_date for one row, then reload the seed and rebuild the snapshot, we will see that:</p><ul><li>the snapshot table has more rows than the seed</li><li>the old version has a populated dbt_valid_to</li><li>the new version has dbt_valid_to IS NULL</li></ul><p>This is the base behavior of a timestamp snapshot. We keep this example out of the main training project as well. It is here to show how the mechanism works.</p><p>If there is no updated_at field, we can use the check strategy. In this case we configure a list of columns to watch, and dbt uses that list to decide when a new version is needed.</p><p>Here is a snapshot with check for transaction status:</p><pre>-- snapshots/ops/txn_status_snapshot.sql<br>{% snapshot txn_status_snapshot %}<br><br>{{<br>  config(<br>    target_database=&#39;dbt_training&#39;,<br>    target_schema=&#39;snapshots&#39;,<br>    unique_key=&#39;transaction_id&#39;,<br>    strategy=&#39;check&#39;,<br>    check_cols=[&#39;status&#39;, &#39;amount&#39;, &#39;vendor&#39;],  -- only these fields trigger a new version<br>    hard_deletes=&#39;invalidate&#39;,<br>    snapshot_meta_column_names={<br>      &quot;dbt_valid_from&quot;: &quot;dbt_effective_date_from&quot;,<br>      &quot;dbt_valid_to&quot;:   &quot;dbt_effective_date_to&quot;,<br>      &quot;dbt_scd_id&quot;:     &quot;dbt_record_scd_id&quot;,<br>      &quot;dbt_updated_at&quot;: &quot;dbt_row_updated_at&quot;<br>      # &quot;dbt_is_deleted&quot;: &quot;is_deleted&quot;<br>    }<br>  )<br>}}<br><br>SELECT<br>  transaction_id,<br>  status,<br>  amount,<br>  vendor<br>FROM {{ ref(&#39;fct_transactions&#39;) }}<br><br>{% endsnapshot %}</pre><p>Here check_cols defines which fields matter for history. Changes outside this list are ignored when dbt decides if it needs a new version.</p><p>The snapshot_meta_column_names setting lets us rename the technical columns so they look nicer in dashboards. <strong>It is important to remember that if the snapshot table already exists and we change these technical column names, the build will fail. We either need to migrate the table in the database or recreate the snapshot and lose its history.</strong></p><p>We run this snapshot in the same way:</p><pre>dbt snapshot --select txn_status_snapshot<br>dbt run --select txn_status_snapshot+</pre><p>The hard_deletes parameter controls how dbt reacts when rows disappear from the source:</p><ul><li>ignore does nothing. The historical table does not reflect the deletion</li><li>invalidate closes the current version by setting dbt_valid_to = now()</li><li>new_record writes a separate deletion record with dbt_is_deleted set to 1 (or a renamed variant). For an existing snapshot table this requires a schema change, otherwise the build will fail</li></ul><p>With new_record you will see two rows for one deleted entity. One row looks like an invalidated version. The second row marks the deletion event.</p><p>Typical queries to snapshot tables fall into two patterns.</p><p>The first pattern is to fetch the current version:</p><pre>SELECT ...<br>FROM dbt_training.snapshots.txn_status_snapshot<br>WHERE transaction_id = 123<br>  AND dbt_valid_to IS NULL</pre><p>The second pattern is to answer “what was true at time T”:</p><pre>SELECT ...<br>FROM dbt_training.snapshots.txn_status_snapshot<br>WHERE transaction_id = 123<br>  AND dbt_valid_from &lt;= toTimestamp(&#39;2025-09-01 12:00:00&#39;)<br>  AND (dbt_valid_to &gt; toTimestamp(&#39;2025-09-01 12:00:00&#39;) OR dbt_valid_to IS NULL)</pre><p>The actual time functions depend on your database. The logic is always the same. Time T must fall into the interval [dbt_valid_from, dbt_valid_to) or into the open interval that ends with NULL.</p><p>As with seeds, it helps to keep a short checklist for snapshots.</p><ul><li>Choose unique_key carefully. It must identify one logical row. Otherwise the history will be noisy</li><li>Avoid a “noisy” updated_at such as a load timestamp. If the field changes for reasons that are not business changes, prefer the check strategy</li><li>Build snapshots on a narrow SELECT with only the needed fields</li><li>In ClickHouse there are no subschemas inside a database, so target_schema may behave differently than in Postgres or Snowflake. If you want a logical separation, use a separate database or name prefixes</li><li>Describe snapshots in YAML. Add descriptions and simple tests, for example sanity checks for dates</li><li>When you rename technical columns with snapshot_meta_column_names, be ready to migrate the table or rebuild the history</li></ul><p>A small operational cheat sheet can look like this:</p><pre># 1) Decide on the strategy: timestamp (there is updated_at) or check (a list of columns)<br># 2) Optionally update the source for a demo:<br>dbt seed --full-refresh --select managers_by_org<br><br># 3) Run the snapshot:<br>dbt snapshot --select managers_by_org_snapshot<br><br># 4) Change the source and repeat to see a new version of the row<br># 5) Query history through dbt_valid_from / dbt_valid_to<br># 6) When renaming meta columns, migrate the DB table or recreate the snapshot</pre><p>In the training project that we build in this series, the model examples with managers_by_org and snapshots stay outside the minimal set. They are not required for basic revenue analytics, but they show how seeds and snapshots work on simple data.</p><p>After five articles we already have a compact but real stack:</p><ul><li>in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">the first article</a> we brought up a local ClickHouse and dbt sandbox and learned how to refresh <strong>training_raw</strong> using a safe slice</li><li>in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">the second article</a> we defined prod_clickhouse sources, built a clean staging layer, and connected freshness</li><li>in<a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3"> the third article</a> we designed the intermediate layer with dim_org_project and fact tables, learned incremental models, and saw how dbt docs works in practice</li><li>in <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">the fourth article</a> we put configs and properties in order, including priorities and the behavior of + on different levels</li><li>in this fifth article we learned how to add model lookup tables through seeds, configure and test them, and how to use snapshots to store history on top of tables that do not have CDC and still answer “what was true at time T” with the level of detail that we need.</li></ul><p>From here we can not only compute revenue and build dashboards, but also start building a more systematic data quality practice around these layers.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=289afcd05ba0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-5-seeds-and-snapshots-for-lookup-tables-and-honest-history-289afcd05ba0">Hands-on dbt with ClickHouse: #5 Seeds and snapshots for lookup tables and honest history</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #4 Configs and properties in dbt, where to put what and why it…]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/b5d0f95c5c02</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[dbt]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Tue, 16 Dec 2025 08:11:00 GMT</pubDate>
            <atom:updated>2025-12-16T08:10:59.151Z</atom:updated>
            <content:encoded><![CDATA[<h3>Hands-on dbt with ClickHouse: #4 Configs and properties in dbt, where to put what and why it matters</h3><p>By this point in the series we already have our <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">sandbox up and running</a>: ClickHouse in Docker, a training slice of data and a dbt project wired into it. We also agreed on the layer structure, described sources.yml, <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">built the first </a><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">stg_ models</a> and then <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">added an <strong>intermediate</strong> layer with </a><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">dim_ and </a><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">fct_ tables</a> that feed money dashboards.</p><p>The next natural question is not about SQL at all. It is about configuration, and two very practical questions:</p><ul><li>Where do we describe what a model means and how it behaves?</li><li>How do we avoid a situation where every model has a different set of rules that nobody remembers in three months?</li></ul><p>In dbt most of the behaviour is controlled by two big groups of settings. It helps a lot when the whole team shares the same mental model for them.</p><p>Properties answer “what this object is”, not “how to build it”. Typical examples:</p><ul><li>descriptions for models, columns and sources (description)</li><li>tests (tests). We already saw a generic not_null test in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">previous article</a> and will come back to tests in more depth later</li><li>documentation and metadata for integrations (meta, tags, exposures, sources and so on)</li></ul><p>Properties live only in YAML. You never declare them inside a SQL file.</p><p>Configs answer “how to build this, where to put it and with which options”. For example:</p><ul><li>materialization (materialized)</li><li>where to store the object in the database. Schemas, databases and prefixes depend on the adapter</li><li>engine and partitioning for ClickHouse models (engine, order_by, partition_by)</li><li>access rights (grants)</li><li>seed parameters such as delimiter, column_types, encoding</li><li>snapshot parameters such as strategy, updated_at, check_cols, hard_deletes</li></ul><p>Configs can be set in three places:</p><ol><li>dbt_project.yml for global defaults and folder level rules</li><li>YAML files next to resources, in models:, seeds:, snapshots:, sources: blocks</li><li>Inside the model itself with {{ config(…) }} as the closest level</li></ol><p>dbt also has a simple rule for priority. <strong>If the same parameter is set in several places, the closest one to the resource wins. </strong>A tiny thought experiment with meta.owner is enough to see it:</p><p>At the project level:</p><pre>models:<br>  some_project:<br>    +meta:<br>      owner: &quot;data@corp&quot;</pre><p>At the folder or layer level:</p><pre>models:<br>  some_project:<br>    staging:<br>      +meta:<br>        owner: &quot;staging@corp&quot;</pre><p>And in the model YAML:</p><pre>models:<br>  - name: stg_prod_clickhouse__organizations<br>    meta:<br>      owner: &quot;product-analytics@corp&quot;</pre><p>In dbt docs the <strong>model page</strong> will show product-analytics@corp as owner, the <strong>staging layer</strong> page will show staging@corp, and the <strong>project overview</strong> will show data@corp.</p><p>You can imagine the model as the center of the universe. The closer the config is to the model, the more weight it has. If “something did not work”, the first thing to check is whether there is a closer config that overwrote your setting.</p><p>There is one more small but important detail about configuration syntax. That is the + prefix. It usually appears in two situations. The first case is when a folder name and a config name collide. Imagine this tree:</p><pre>models/<br>  some_project/<br>    some_layer/<br>      meta/<br>        ...</pre><p>and you want to set a meta config on the some_layer folder itself, not create another subfolder. In dbt_project.yml it looks like this:</p><pre>models:<br>  some_project:<br>    some_layer:<br>      meta:         # this is the FOLDER models/some_project/some_layer/meta/<br>        +meta:      # and this is the CONFIG meta, the + is important<br>          owner: &quot;analytics@company.tld&quot;</pre><p>Without the plus sign dbt can treat meta: as a nested folder instead of a config.</p><p>The second case is lists. In this series we will treat + as a clear signal for “extend the list, do not replace it”.</p><p>A classic example is grants. At the project level:</p><pre>models:<br>  some_project:<br>    +grants:<br>      select: [&quot;alice&quot;, &quot;bob&quot;]</pre><p>and on a specific model:</p><pre>models:<br>  some_project:<br>    staging:<br>      prod_clickhouse:<br>        stg_prod_clickhouse__organizations:<br>          +grants:<br>            select: [&quot;charlie&quot;]</pre><p>If you used grants: without a + on the model, dbt would usually treat this as a new list and could drop alice and bob. With +grants we say “keep the base readers, just add charlie”.</p><p>Some settings are shared across many types of resources. Others are very specific. It is good to know which is which so you do not try to put freshness on a model, for example.</p><p>Common parameters that you see on models, sources, seeds, snapshots and tests:</p><ul><li>meta as a free metadata dictionary</li><li>grants for basic rights like select</li><li>tags for selectors, filters and DAG coloring</li></ul><p>Then the resource specific ones:</p><ul><li><strong>Models (</strong><strong>models:) </strong>materialized, alias, schema, persist_docs, grants, adapter specific options such as engine, order_by, partition_by for ClickHouse, on_schema_change, post-hook, pre-hook and others</li><li><strong>Sources (</strong><strong>sources:) </strong>freshness for SLAs, loaded_at_field, filter, quoting, sometimes external, plus meta</li><li><strong>Seeds (</strong><strong>seeds:) </strong>delimiter, column_types, quote_columns, encoding, header, schema for databases with schemas, and meta. Seeds are the way to turn a CSV file from your git repo into a table in the database. We will have a separate article about them</li><li><strong>Snapshots (</strong><strong>snapshots:) </strong>strategy (timestamp or check), updated_at or check_cols, unique_key, hard_deletes (ignore, invalidate, new_record), snapshot_meta_column_names. Snapshots let you keep the history of rows when the source table is constantly overwritten. We will also talk about them separately</li><li><strong>Tests (</strong><strong>tests:) </strong>severity, warn_if, error_if, where, limit, fail_calc for custom tests, plus meta. In this article, like in the previous one, we will only touch generic tests briefly. Detailed test types and policies deserve their own space</li></ul><p>Once you see this picture, the next step is to decide where to put configs in a live project.</p><p>The first natural level is dbt_project.yml. Here you set project wide defaults, common tags and some engine rules for whole folders or layers. A typical structure in dbt_project.yml looks like this:</p><pre>name: some_project<br>models:<br>  some_project:<br>    +meta:<br>      owner: &quot;data@corp&quot;<br>    +grants:<br>      select: [&quot;alice&quot;, &quot;bob&quot;]<br>    staging:<br>      prod_clickhouse:<br>        +tags: [&quot;stg&quot;, &quot;contracts&quot;]<br>    intermediate:<br>      +materialized: table<br>      +engine: MergeTree<br>      +order_by: [&quot;organization_id&quot;, &quot;project_id&quot;, &quot;month&quot;]<br>      +partition_by: [&quot;toYYYYMM(month)&quot;]</pre><p>Here we set:</p><ul><li>a global owner and base read access</li><li>tags for the staging/prod_clickhouse layer</li><li>default materialization and ClickHouse engine settings for all models in intermediate/</li></ul><p>The second level is YAML files next to models. These files keep properties and configs in one place. For example, the file models/staging/prod_clickhouse/_prod_clickhouse__models.yml can look like this:</p><pre>version: 2<br>models:<br>  - name: stg_prod_clickhouse__organizations<br>    description: &quot;Normalized contract over raw organizations&quot;<br>    meta:<br>      domain: &quot;organizations&quot;<br>    config:<br>      materialized: table<br>      tags: [&quot;stg&quot;, &quot;contracts&quot;]<br>      # ClickHouse specific options could also live here if you want them per model<br> ...</pre><p>In one file you see both the meaning (description and metadata) and the behaviour (materialization, tags, engine decisions). This is a good pattern when you want someone to open a single YAML file and understand “what this is” and “how it behaves in the database” in one shot.</p><p>The third level is the config inside the model. This is the closest level to the SQL and overrides everything else. A simple example:</p><pre>-- models/staging/prod_clickhouse/stg_prod_clickhouse__organizations.sql<br>{{<br>  config(<br>    materialized=&#39;table&#39;,<br>    tags=[&#39;stg&#39;, &#39;contracts&#39;]<br>  )<br>}}<br><br>SELECT<br>    id,<br>    alias,<br>    created_at,<br>    ...<br>FROM<br>    {{ source(&#39;prod_clickhouse&#39;, &#39;organizations&#39;) }}</pre><p>This approach makes sense when the config is really specific to this model. For example a special engine, partitioning or a bit of logic that you want to keep right next to the query. It is also handy while experimenting. You can start with config(…) inside the model and, if the setup proves stable, move it into YAML later.</p><p>To show how this looks in practice we can reuse a model we already know. In the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">previous article</a> we built an intermediate fact fct_project_monthly_revenue that holds MTR per project. A realistic ClickHouse config for it could look like this:</p><pre>-- models/intermediate/prod_clickhouse/fct_project_monthly_revenue.sql<br>{{<br>  config(<br>    materialized=&#39;incremental&#39;,<br>    unique_key=[&#39;project_id&#39;,&#39;month&#39;],<br>    engine=&#39;MergeTree&#39;,<br>    order_by=[&#39;organization_id&#39;, &#39;project_id&#39;, &#39;month&#39;],<br>    partition_by=[&#39;toYYYYMM(month)&#39;],<br>    on_schema_change=&#39;append_new_columns&#39;<br>  )<br>}}<br>SELECT<br>  ...</pre><p>Here:</p><ul><li>engine, order_by, partition_by directly affect query speed and storage costs in ClickHouse</li><li>on_schema_change=’append_new_columns’ is a safe default for the <strong>intermediate layer</strong>. New columns are added, existing ones are not dropped</li></ul><p>You can think of this as an extended version of the config we saw before. The idea is the same. You just give ClickHouse a few more hints on how to store the table.</p><p>For sources we already wrote a detailed sources.yml in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">second article</a>. We also introduced freshness checks there. The same idea can be expressed in a compact YAML style.</p><pre># models/staging/prod_clickhouse/_prod_clickhouse__sources.yml<br>version: 2<br>sources:<br>  - name: prod_clickhouse<br>    schema: training_raw<br>    tables:<br>      - name: transactions<br>        description: &#39;End user transactions&#39;<br>        columns:<br>          - name: id<br>          - name: created_at<br>          # ...<br><br>        config:<br>          freshness:<br>            warn_after:  {count: 6, period: hour}<br>            error_after: {count: 1, period: day}<br>            filter: &quot;created_at &gt;= now() - toIntervalDay(3) AND fake = 0&quot;<br>          loaded_at_field: created_at</pre><p>Everything related to the transactions source table columns, freshness, loaded_at_field lives in one YAML file. The filter with toIntervalDay(3) restricts the freshness window to three days and ignores test transactions. Only real ones with fake = 0 are checked.</p><p>Seeds follow the same high level logic as models. Some defaults are convenient to keep in dbt_project.yml. Some are better set next to a specific seed.</p><p>A minimal example:</p><pre># dbt_project.yml<br>seeds:<br>  some_project:<br>    +schema: seed_data<br>    +delimiter: &quot;;&quot;</pre><p>and for one particular seed:</p><pre># seeds/ops/_seeds__ops.yml<br>version: 2<br>seeds:<br>  - name: managers_by_org<br>    config:<br>      delimiter: &quot;;&quot;<br>      column_types:<br>        manager: String<br>        organizations_id: Int64<br>        start_date: Date<br>        end_date: Date</pre><p>In ClickHouse there are no schemas inside a database in the Postgres sense so schema here is effectively a database name. In production people usually either create a separate database for seeds such as dbt_seed or use table name prefixes to keep seed data isolated.</p><p>Snapshots are another type of resource where configs matter a lot. They let you track how rows change over time even when the source table is overwritten in place.</p><p>For example:</p><pre>-- snapshots/ops/txn_status_snapshot.sql<br>{% snapshot txn_status_snapshot %}<br>{{<br>  config(<br>    target_database=&#39;dbt_training&#39;,<br>    target_schema=&#39;snapshots&#39;,         -- in databases with schemas<br>    unique_key=&#39;transaction_id&#39;,<br>    strategy=&#39;check&#39;,<br>    check_cols=[&#39;status&#39;, &#39;amount&#39;, &#39;vendor&#39;],<br>    hard_deletes=&#39;invalidate&#39;,<br>    snapshot_meta_column_names={<br>      &quot;dbt_valid_from&quot;: &quot;dbt_effective_date_from&quot;,<br>      &quot;dbt_valid_to&quot;:   &quot;dbt_effective_date_to&quot;<br>    }<br>  )<br>}}<br><br>SELECT<br>  transaction_id,<br>  status,<br>  amount,<br>  vendor<br>FROM {{ ref(&#39;fct_transactions&#39;) }}<br><br>{% endsnapshot %}</pre><p>Here:</p><ul><li>strategy=’check’ is useful when you care about changes in a specific set of columns such as status, amount, vendor</li><li>hard_deletes=’invalidate’ tells dbt to mark old versions as no longer valid when a row disappears from the source</li><li>snapshot_meta_column_names lets you rename dbt internal columns to fit your internal naming. It does require a careful migration if the table already exists</li></ul><p>For tests the configuration usually lives in YAML next to the model, just like for models and seeds. A small example with generic tests on fct_transactions:</p><pre>version: 2<br>models:<br>  - name: fct_transactions<br>    columns:<br>      - name: id<br>        tests:<br>          - not_null:<br>              severity: error<br>          - unique:<br>              severity: warn</pre><p>Here severity: error turns a failure into a red stop. severity: warn is a yellow light. The pipeline can finish but the logs will contain a warning. We will talk about different test types and how to design test policies per layer in a separate article. For this one it is enough to see that configs for tests are also part of the same picture.</p><p>There are a few practical nuances that are worth fixing in your head early. Some of them we already touched in previous articles. Others will become more obvious once you see seeds, snapshots and tests in action. It is a good idea to come back to this article after you walk through the whole series and read it again with full context.</p><p>If we summarise all this in one place:</p><p><strong>First</strong>, overrides. The “resource over folder over project” rule is easy to forget. If a config does not seem to work, most of the time there is a more local setting that overwrites it.</p><p><strong>Second</strong>, + and lists. Without the plus it is very easy to accidentally replace a parent list instead of extending it. That is why it is better to treat +grants, +tags and similar patterns as explicit “extend what we had before”.</p><p><strong>Third</strong>, ClickHouse and schema. There are no schemas inside a database.<br>schema acts differently than in Postgres. For isolation in production you use different databases or table name prefixes, not a schema in the classic sense.</p><p><strong>Fourth</strong>, changing materialization. Moving a model from table to view or to incremental does not always clean up old objects in the database. Sometimes you need a manual DROP TABLE or a small multi step migration.</p><p><strong>Fifth</strong>, seeds. They live in git. That means no passwords, tokens or personal data inside CSVs. Column types are better defined explicitly. If you skip them everything will silently turn into strings.</p><p><strong>Sixth</strong>, freshness. It is not dbt build. It is a separate step that checks SLAs. The decision “should we fail on ERROR or only send an alert” is part of your pipeline policy, not a rule enforced by dbt.</p><p><strong>Finally</strong>, snapshots. You need to consciously choose between timestamp and check strategy, think through how hard_deletes should behave and remember that renaming internal columns is a real migration, not a cosmetic change.</p><p>The easiest way not to get lost here is to keep a small mental checklist.</p><ol><li>First decide what you are describing. Meaning and tests belong to properties. Behaviour and materialization belong to configs</li><li>Put the config where it makes the most sense. Project, folder or model level. Keep the priority rule in mind</li><li>If a folder name and a config key conflict, use +meta, +tags, +grants and similar forms</li><li>If you want to extend a list instead of replacing it, add + at the lower level</li><li>For ClickHouse do not forget about engine, order_by and partition_by, especially for large facts</li><li>For sources keep freshness next to the table description in sources.yml</li><li>Use seeds only for neutral dictionaries and reference data, not for sensitive content</li><li>Turn on snapshots where the source table is constantly overwritten and history is important for analytics, compliance or audit</li></ol><p>If you put it all together, it becomes much easier to reason about the project and explain it to others.</p><ul><li>Properties tell you what a resource is. Meaning, descriptions, tests and metadata</li><li>Configs tell you how it lives in the database. Materialization, engines, access rights, partitioning</li><li>When configs conflict, the closest level to the resource wins</li><li>+ helps distinguish a config from a folder with the same name and lets you extend lists instead of overwriting them</li><li>Shared parameters like meta, grants and tags appear almost everywhere. Others belong only to specific resource types</li><li>Configs are easier to maintain when they stay close to the place where they matter, but not hidden by magic. Any person on the team should be able to open a few files and understand why a model behaves the way it does</li></ul><p>In the next articles we will move from configuration to data quality and talk more seriously about tests, contracts and how to make marts safe to expose to the rest of the company.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b5d0f95c5c02" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-4-configs-and-properties-in-dbt-where-to-put-what-and-why-it-b5d0f95c5c02">Hands-on dbt with ClickHouse: #4 Configs and properties in dbt, where to put what and why it…</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #3 Intermediate layer, fact tables and the road to marts]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/d91577d27da3</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Wed, 10 Dec 2025 07:58:16 GMT</pubDate>
            <atom:updated>2025-12-10T07:58:15.468Z</atom:updated>
            <content:encoded><![CDATA[<p>In the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">first</a> <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">two</a> articles we prepared the ground.<br>We brought up a local sandbox from scratch: ClickHouse in Docker, a training slice of data and a dbt project wired into this environment. Then we agreed on data layers, described sources.yml, built the first stg_ models and connected them into <strong>dbt_training</strong> as our analytical warehouse.</p><p>Now we can finally move to what we actually want.<br>We do not just want clean staging models. We want solid fact tables and dimensions that business dashboards can lean on.</p><p>We imagine that we are a SaaS platform that helps clients accept payments through different providers. Clients integrate only with us. We handle Stripe, Paddle, Adyen and others behind the scenes.</p><p>In the end the marts/ layer should be able to provide at least:</p><ul><li>a daily breakdown by project and organization (DTR)</li><li>a monthly breakdown by project and organization (MTR)</li><li>a monthly breakdown by organization for client level KPIs</li><li>plus metadata like human readable client and project names</li></ul><p>At the same time the marts/ layer should not recompute money from raw data every time. Its job is to take ready facts from intermediate/, apply business logic on top (flags, coalesce to “unknown” for missing aliases, ect.) and give BI a flat table that is safe to show to stakeholders.</p><p>For this to work we need a normal intermediate layer. This is where facts and dimensions live and where reuse starts to be safe.</p><p>We still stick to the same three layers under models/: staging/, intermediat/, marts/. These are three sibling folders, not nested inside each other.</p><p>staging/ holds the stg_* models. This is a neat contract over the raw <strong>training_raw</strong> database. In these models we:</p><ul><li>select only the fields we actually need</li><li>normalize types</li><li>give clear column names</li><li>cut off obvious junk</li><li>fix what we consider “acceptable data”</li></ul><p>This is also the natural place for data tests and freshness checks. We will have a separate detailed article about dbt tests, how they work and how to configure them. In this article we only touch generic tests lightly. We already talked about source freshness in the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">second article</a> and will rely on that here.</p><p>intermediate/ holds fct_* and dim_* models. These are business building blocks:</p><ul><li>how much money a project brings per day</li><li>how much money an organization brings per month</li><li>which project belongs to which organization</li></ul><p>This is not ad hoc research for a one off question. This is the layer that marts and dashboards depend on.</p><p>The flow is always the same: <strong>staging</strong> → <strong>intermediate</strong> → <strong>marts</strong>.<br>In real life the work with these layers looks quite down to earth.</p><ul><li>A product manager or a CSM comes and asks:<br>“Can we see a breakdown by card type, currency, PSP, onetime vs renewal?”</li><li>We do not pull the new column into all marts right away. First we go to the staging stg_prod_clickhouse__transactions, and run the research query there. At this level we still have every low level field and no aggregations.</li><li>If the field turns out to be useful and people want to see it every day, we promote this field and logic intointermediate/.</li><li>Marts then only reuse what is already present in intermediate/. They do not connect to raw sources on their own.</li></ul><p>So the rule is simple:</p><ul><li>for research and diagnosis we work on <strong>staging</strong></li><li>what becomes a business metric is raised into the <strong>intermediate</strong> layer</li><li><strong>marts</strong> do not reinvent the logic, they only present it</li></ul><p>By the end of this article we want the structure of models/ for our domain to look roughly like this:</p><pre>models/<br>  staging/<br>    prod_clickhouse/<br>      _prod_clickhouse__sources.yml<br>      stg_prod_clickhouse__organizations.sql<br>      stg_prod_clickhouse__projects.sql<br>      stg_prod_clickhouse__transactions.sql   <br>  intermediate/<br>    prod_clickhouse/<br>      dim_org_project.sql<br>      fct_transactions.sql<br>      fct_project_daily_revenue.sql<br>      fct_org_daily_revenue.sql<br>      fct_project_monthly_revenue.sql<br>      fct_org_monthly_revenue.sql<br>      _int_prod_clickhouse__models.yml       <br>  marts/<br>    prod_clickhouse/<br>      mart_project_revenue_enriched.sql<br>      # marts that go to BI and managers</pre><p>It is important that intermediate/ is not “just SQL”.<br>Next to the models we keep YAML _int_prod_clickhouse__models.yml, where we:</p><ul><li>document models and columns</li><li>attach basic tests</li><li>set visual metadata for the DAG in dbt docs (for example node_color)</li></ul><p>The core fact table for money in our schema is fct_transactions.<br>Each row in this table is a single real transaction. No fake rows, no technical noise. All revenue aggregations depend on this model.</p><p>File models/intermediate/prod_clickhouse/fct_transactions.sql:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    ) <br>}}<br><br>SELECT<br>  id,<br>  project_id,<br>  created_at,<br>  price_usd,<br>  organization_id,<br>  status,<br>  ...<br>FROM<br>    {{ ref(&#39;stg_prod_clickhouse__transactions&#39;) }}<br>WHERE fake = 0</pre><p>There is a key shift here. In the staging layer we read from raw tables through source(‘prod_clickhouse’, ‘transactions’). In the intermediate layer we no longer talk to raw tables directly. We call the result of another dbt model through ref(‘stg_prod_clickhouse__transactions’).</p><p>Roughly speaking:</p><ul><li>source(…) means “take this physical table from an external source” (from <strong>training_raw</strong> in our case)</li><li>ref(…) means “take the result of this dbt model”</li></ul><p>Everything above staging (intermediate and later marts) should look at lower layers through ref, not through source.</p><p>Marts will follow the same pattern. They will reference dim_ and fct_ models, not raw tables or staging models.fct_transactions should not be a dump of eighty columns “just in case”. We keep only the fields that are really needed in downstream aggregates and marts:</p><ul><li>transaction identifier</li><li>project</li><li>timestamps</li><li>money</li><li>organization</li><li>status and maybe a few extra business fields.</li></ul><p>The next building block is the dim_org_project dimension. Its job is to build a clean dictionary “<em>project ↔ organization + human readable labels</em>”.</p><p>In our training slice everything is neat and simple. To make the example realistic, imagine that in production things are less clean. In many systems, organization_id on the transaction may be missing or wrong. The transaction still always knows project_id, and the project knows organization_id. If you try to aggregate money directly by organization_id from fct_transactions in such a world, you risk losing revenue because rows with missing organization_id will simply fall out of the group by.</p><p>The correct chain at the architecture level looks like this:</p><ul><li>a transaction knows project_id</li><li>a project knows organization_id</li><li>an organization has an alias that account managers actually recognize</li></ul><p>dim_org_project builds exactly this “<em>project ↔ organization</em>” dictionary. We:</p><ul><li>use it now to enrich transactions and aggregates with organizations and aliases</li><li>later will be able to join it to other dictionaries, for example “<em>manager ↔ organization</em>” from seeds (there will be a separate article about seeds) and get the list of projects that belong to a specific manager</li></ul><p>File models/intermediate/prod_clickhouse/dim_org_project.sql:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>}}<br><br>SELECT<br>    p.id AS project_id,<br>    p.title AS project_title,<br>    p.organization_id AS organization_id,<br>    o.alias AS organization_alias<br>FROM {{ ref(&#39;stg_prod_clickhouse__projects&#39;) }} AS p<br>LEFT JOIN {{ ref(&#39;stg_prod_clickhouse__organizations&#39;) }} AS o<br>    ON p.organization_id = o.id</pre><p>dim_org_project by itself does not “fix money”. It gives us a stable bridge “<em>project → organization</em>” and readable labels, which we then use in fact tables and marts.</p><p>If you preview this model through the ▶ button, you will often see typical data quality issues: projects without organizations, empty aliases, messy titles. This is a good trigger to:</p><ul><li>add quality checks on staging</li><li>use coalesce(…, ‘unknown’) in marts so that business users do not see blanks</li></ul><p>Next we need daily and monthly facts for both projects and organizations.<br>In the <strong>intermediate</strong> layer we do this with four fact tables:</p><ul><li>fct_project_daily_revenue</li><li>fct_org_daily_revenue</li><li>fct_project_monthly_revenue</li><li>fct_org_monthly_revenue</li></ul><p>The fct_project_daily_revenue answers “how much money a project brought on a given day”:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>}}<br><br>SELECT<br>    d.organization_id AS organization_id,<br>    d.organization_alias AS organization_alias,<br>    d.project_id AS project_id,<br>    d.project_title AS project_title,<br>    toDate(ft.created_at) AS day,<br>    SUM(ft.price_usd) / 100 AS project_dtr<br>FROM {{ ref(&#39;fct_transactions&#39;) }} AS ft<br>LEFT JOIN {{ ref(&#39;dim_org_project&#39;) }} AS d<br>    ON ft.project_id = d.project_id<br>GROUP BY<br>    organization_id,<br>    organization_alias,<br>    project_id,<br>    project_title,<br>    day</pre><p>The fct_org_daily_revenue aggregates by organization and day:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>}}<br><br>SELECT<br>    organization_id,<br>    organization_alias,<br>    day,<br>    SUM(project_dtr) AS org_dtr<br>FROM {{ ref(&#39;fct_project_daily_revenue&#39;) }}<br>GROUP BY<br>    organization_id,<br>    organization_alias,<br>    day</pre><p>The fct_project_monthly_revenue covers project level MTR:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>}}<br><br>SELECT<br>    d.organization_id AS organization_id,<br>    d.organization_alias AS organization_alias,<br>    d.project_id AS project_id,<br>    d.project_title AS project_title,<br>    toStartOfMonth(ft.created_at) AS month,<br>    SUM(ft.price_usd) / 100 AS project_mtr<br>FROM {{ ref(&#39;fct_transactions&#39;) }} AS ft<br>LEFT JOIN {{ ref(&#39;dim_org_project&#39;) }} AS d<br>    ON ft.project_id = d.project_id<br>GROUP BY<br>    d.organization_id,<br>    d.organization_alias,<br>    d.project_id,<br>    d.project_title,<br>    month</pre><p>And finally the fct_org_monthly_revenue gives MTR per organization:</p><pre>{{<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>}}<br><br>SELECT<br>    organization_id,<br>    organization_alias,<br>    month,<br>    SUM(project_mtr) AS org_mtr<br>FROM {{ ref(&#39;fct_project_monthly_revenue&#39;) }}<br>GROUP BY<br>    organization_id,<br>    organization_alias,<br>    month</pre><p>These four facts feed dashboards:</p><ul><li>day by day and project by project</li><li>day by day and organization by organization</li><li>month by month and project by project</li><li>month by month and organization by organization</li></ul><p>If the business wants to see metrics broken down by client size (for example MTR buckets like 10500k+, 420420k+, etc), it is better not to embed this logic into every mart directly. A cleaner way is to build one more model in the <strong>intermediate</strong> layer that labels organizations and projects into buckets by revenue and then join this model to basic facts in marts/. This way a dashboard can filter and group by client size without adding extra complexity into the core fct_* models.</p><p>To make sure that everyone on the team understands what these tables are, we describe the <strong>intermediate</strong> layer in YAML.</p><p>Filemodels/intermediate/prod_clickhouse/_int_prod_clickhouse__models.yml can look like this:</p><pre>version: 2<br><br>models:<br>  - name: dim_org_project<br>    description: &quot;Dictionary linking projects to organizations. One row per project.&quot;<br>    docs:<br>      show: true<br>    meta:<br>      node_color: &quot;#ff6666&quot;  # dimension / lookup<br>    columns:<br>      - name: project_id<br>        description: &quot;Project ID (projects.id). Primary key of the project.&quot;<br>        data_type: String<br>        tests:<br>          - not_null<br><br>      - name: project_title<br>        description: &quot;Human-readable project name. May be empty or messy in reality.&quot;<br>        data_type: String<br>        tests:<br>          - not_null<br><br>      - name: organization_id<br>        description: &quot;Owning organization (client) ID for this project. Can be NULL if project is orphaned or data is incomplete.&quot;<br>        data_type: String<br>        tests: []<br><br>      - name: organization_alias<br>        description: &quot;Client-facing alias of the organization. Can be blank. Marts will coalesce blank to &#39;unknown&#39;.&quot;<br>        data_type: String<br>        tests:<br>          - not_null<br>...</pre><p>A few things to call out:</p><ul><li>docs: show: true tells dbt that this model should be visible in the generated docs. Sometimes you hide utility models with show: false, but for core facts and dimensions it is better to keep them visible.</li><li>meta: node_color: “#ff6666” is a visual attribute for dbt docs. It is handy when you want to color dimensions and facts differently on the DAG graph.</li><li>data_type is a human readable type. It is not a strict database contract for ClickHouse. It helps people and tools that read the docs.</li><li>tests is a list of generic tests for this column. In the example we use not_null on project_id and organization_alias. We will have a separate article that goes deeper into generic, singular and unit tests, how to run them and how to set policy per layer.</li></ul><p>You describe fct_transactions and daily and monthly facts in the same way: description, types and a few key tests.</p><p>Once YAML is in place you can generate documentation. Do it in the IDE terminal where your dbt environment is active. If the environment is not active yet:</p><pre>cd /path/to/project<br>source dbt-env/bin/activate</pre><p>You should see (dbt-env) at the start of the prompt.</p><p>Now generate and serve docs:</p><pre>dbt docs generate<br>dbt docs serve</pre><p>dbt docs serve starts a small HTTP server and prints a URL in the terminal, often something like Serving docs at <a href="http://localhost:8080/">http://localhost:8080</a> . Copy this URL and open it in your browser.</p><p>In the web UI you can:</p><ul><li>click on a model and see table and column descriptions</li><li>see what tests are attached</li><li>open the DAG in the bottom right corner and follow the path <strong>staging</strong> → <strong>intermediate</strong> → <strong>marts</strong></li></ul><p>If you accidentally send dbt docs serve to the background with Ctrl+Z, it will keep the port busy. In that case:</p><pre>jobs        # show background jobs<br>fg %1       # bring the server to foreground<br>Ctrl+C      # stop it cleanly</pre><p>Incremental models are another important topic at the <strong>intermediate</strong> level.<br>So far we mostly used materialized=’table’. This means that dbt drops the table and recreates it from scratch on every build. This is fine for a tiny training sandbox. In production this is painful:</p><ul><li>it is slow and expensive</li><li>there is a window where the table is empty</li><li>charts show “2M, then 0, then 2M again”</li></ul><p>Reality of transaction data is harder:</p><ul><li>new transactions keep arriving</li><li>old transactions change after the fact refund, status pending → success, a fake flag that was updated later</li></ul><p>We need both:</p><ul><li>avoid recalculating the entire history on every run</li><li>still recalculate the “live tail” honestly, because statuses and flags move there</li></ul><p>A typical approach is to define a time window, for example 60 days.</p><ul><li>everything inside the window is recalculated from scratch on every run</li><li>everything older is treated as frozen truth and is read from the existing table</li></ul><p>Let us start with the transactional <strong>staging</strong> model:</p><pre>-- stg_prod_clickhouse__transactions:<br>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        unique_key=&#39;id&#39;,<br>        on_schema_change=&#39;append_new_columns&#39;<br>    )<br>}}<br><br>WITH fresh AS (<br>    SELECT<br>        id,<br>        project_id,<br>        created_at,<br>        currency,<br>        fake,<br>        price_usd,<br>        vendor,<br>        billing_reason,<br>        payment_method,<br>        organization_id,<br>        status,<br>        onetime,<br>        ...<br>    FROM {{ source(&#39;prod_clickhouse&#39;, &#39;transactions&#39;) }}<br>    WHERE created_at &gt;= (today() - 60)<br>)<br><br>SELECT<br>    id,<br>    project_id,<br>    created_at,<br>    currency,<br>    fake,<br>    price_usd,<br>    ...<br>FROM fresh<br><br>{% if is_incremental() %}<br>UNION ALL<br><br>SELECT<br>    id,<br>    project_id,<br>    created_at,<br>    currency,<br>    fake,<br>    price_usd,<br>    ...<br>FROM {{ this }}<br>WHERE created_at &lt; (today() - 60)<br>{% endif %}</pre><p>The important part is the structure:</p><ul><li>we still list fields explicitly</li><li>staging only keeps what we expect to use in higher layers</li></ul><p>In our training example we are lucky. The “prod” data slice is relatively clean. In real life you almost always have test and technical records. Staging is the natural place to cut them off by a flag and avoid pulling them into the warehouse at all.</p><p>On the first run the table does not exist yet and is_incremental() returns False, so the UNION ALL block is skipped. On subsequent runs the staging table already exists, the macro returns True and the fresh window is glued to the old history.</p><p>After that we can make fct_transactions incremental as well:</p><pre>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        unique_key=&#39;id&#39;,<br>        on_schema_change=&#39;append_new_columns&#39;<br>    )<br>}}<br><br>WITH fresh AS (<br>    SELECT<br>        id,<br>        project_id,<br>        created_at,<br>        price_usd,<br>        organization_id,<br>        status,<br>        ...<br>    FROM {{ ref(&#39;stg_prod_clickhouse__transactions&#39;) }}<br>    WHERE fake = 0<br>      AND created_at &gt;= (today() - 60)<br>)<br><br>SELECT<br>    id,<br>    project_id,<br>    created_at,<br>    price_usd,<br>    organization_id,<br>    status,<br>    ...<br>FROM fresh<br><br>{% if is_incremental() %}<br>UNION ALL<br><br>SELECT<br>    id,<br>    project_id,<br>    created_at,<br>    price_usd,<br>    organization_id,<br>    status,<br>    ...<br>FROM {{ this }}<br>WHERE created_at &lt; (today() - 60)<br>{% endif %}</pre><p>Same pattern, only now we work with already cleaned facts. If a transaction status changed yesterday, it falls into the 60 day window and overwrites the old value. Old, stable months stay untouched.</p><p>Aggregates can also be incremental. For fct_project_daily_revenue it can look like this:</p><pre>{{<br>    config(<br>        materialized=&#39;incremental&#39;,<br>        unique_key=[&#39;project_id&#39;,&#39;day&#39;],<br>        on_schema_change=&#39;append_new_columns&#39;<br>    )<br>}}<br><br>WITH fresh AS (<br>    SELECT<br>        d.organization_id        AS organization_id,<br>        d.organization_alias     AS organization_alias,<br>        d.project_id             AS project_id,<br>        d.project_title          AS project_title,<br>        toDate(ft.created_at)    AS day,<br>        SUM(ft.price_usd) / 100  AS project_dtr<br>    FROM {{ ref(&#39;fct_transactions&#39;) }} AS ft<br>    LEFT JOIN {{ ref(&#39;dim_org_project&#39;) }} AS d<br>        ON ft.project_id = d.project_id<br>    WHERE toDate(ft.created_at) &gt;= (today() - 60)<br>    GROUP BY<br>        organization_id,<br>        organization_alias,<br>        project_id,<br>        project_title,<br>        day<br>)<br><br>SELECT<br>    organization_id,<br>    organization_alias,<br>    project_id,<br>    project_title,<br>    day,<br>    project_dtr<br>FROM fresh<br><br>{% if is_incremental() %}<br>UNION ALL<br><br>SELECT<br>    organization_id,<br>    organization_alias,<br>    project_id,<br>    project_title,<br>    day,<br>    project_dtr<br>FROM {{ this }}<br>WHERE day &lt; (today() - 60)<br>{% endif %}</pre><p>Incremental models for the other aggregates follow the same pattern:</p><ul><li>fct_org_daily_revenue with unique_key = [‘organization_id’, ‘day’] and WHERE day &gt;= (today() — 60)</li><li>fct_project_monthly_revenue with unique_key = [‘project_id’, ‘month’] and a condition for recent months</li><li>fct_org_monthly_revenue with unique_key = [‘organization_id’, ‘month’] and a similar condition on month</li></ul><p>The internal structure is the same in each case:</p><ul><li>compute fresh days or months in a WITH fresh AS (…) block</li><li>select them in the main SELECT</li><li>and if is_incremental() is true, union old history from {{ this }} up to the boundary date or month</li></ul><p>At the same time dim_org_project stays a plain materialized=’table’ model. It is a small dictionary of current state. It changes infrequently, so it is simpler to rebuild it from scratch and always have an up to date mapping from project to organization and alias.</p><p>Almost all examples above have on_schema_change=’append_new_columns’ in config(). This parameter matters exactly for incremental models. With materialized=’table’ dbt can just drop and recreate the table with the desired schema. With incremental models the table already exists, and dbt has to decide how to reconcile schema differences.</p><p>If the transactional source adds a column, renames a field, changes a type or removes a column, dbt needs a policy. Typical on_schema_change modes:</p><ul><li>ignore : new columns are ignored. This is risky because you can believe you have a column while it never appears in the target table.</li><li>append_new_columns : new columns are added in the target table. Old columns are not touched. This is a good default for staging and intermediate facts. We keep new fields without breaking old readers.</li><li>sync_all_columns : try to synchronise the full structure. Add new columns and drop removed ones. This is aggressive and can break consumers that still rely on the old schema.</li><li>fail : treat schema change as an error and stop. This is useful for public facing marts where the contract with business is more important than a green build. Better to not update a mart at all than silently change its shape.</li></ul><p>In the training project we choose flexibility and use append_new_columns in staging and facts. We do not want to lose new fields or fall over on every small change in the production schema. In a real marts/ layer you can pick a stricter policy, but technically those models are built with the same tools as the intermediate ones.</p><p>All these models also have to be built in a sensible order. The ▶ button in IDE only compiles and runs the SQL. If a dependency is not materialised yet, ClickHouse will simply say it does not know that table.</p><p>That is why it is sometimes useful to build layers in order, by hand:</p><pre># first staging and the dictionary<br>dbt build --select stg_prod_clickhouse__transactions dim_org_project<br><br># then cleaned transactions and project level aggregates<br>dbt build --select fct_transactions fct_project_daily_revenue fct_project_monthly_revenue<br><br># finally aggregates by organization<br>dbt build --select fct_org_daily_revenue fct_org_monthly_revenue</pre><p>After this you can safely use ▶ even on marts, because their dependencies exist physically in the database. This is not a bug. dbt does not simulate data. It works with a real ClickHouse database. If a table is not created yet, preview of the upper layer cannot work.</p><p>In the<a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560"> previous article</a> we already separated two ideas: checking source freshness with dbt source freshness and building models with dbt build. We also saw that build bundles together table creation and tests. In production pipelines people usually expand this picture a bit more. We want to not only check freshness and rebuild models, but also:</p><ul><li>update data</li><li>run quality checks</li><li>and update documentation for the team</li></ul><p>A typical CI or Airflow flow looks like this:</p><ol><li><strong>Check source freshness</strong></li></ol><pre>dbt source freshness<br># or more precisely:<br># dbt source freshness --select source:prod_clickhouse.transactions</pre><p>This is a sanity check. Did data arrive at all. If freshness says that there were no new transactions for many hours, it makes little sense to compute “fresh” marts. You will only recompute yesterday.</p><p><strong>2. Update the money</strong></p><pre>dbt run<br># or more targeted:<br># dbt run --select staging intermediate<br># dbt run --select marts</pre><p>Here dbt actually creates and updates tables in ClickHouse.</p><ol><li>staging pulls in the fresh tail of transactions with incremental logic</li><li>intermediate recomputes DTR and MTR and glues the window to the stable history</li><li>marts prepare flat tables for BI</li></ol><p><strong>3. Check quality</strong></p><pre>dbt test</pre><p>This runs not_null, relationships, accepted_values and other tests.<br>The idea is:</p><ul><li>tests are a quality signal</li><li>contracts and the policy around them are the stop button</li></ul><p>If you set a very strict not_null where reality is dirty by design, you can accidentally stop revenue tables from being updated. On <strong>staging</strong> and <strong>intermediate</strong> layers it often makes sense to treat tests as diagnostics, not as a hard stop. On <strong>marts</strong> you can be stricter and say that it is better to serve yesterday’s data than a broken fresh version. We will talk about different types of tests and per layer policies in a separate article. For now it is enough to see that tests can both highlight quality degradation and stop a build if you decide so.</p><p><strong>4. Update documentation</strong></p><pre>dbt docs generate</pre><p>This rebuilds the documentation artifact: model and column descriptions and the lineage graph. Locally we view it via dbt docs serve. In production you usually publish the HTML or JSON into an internal web UI. This becomes the data wiki for your company: which tables exist, where fields come from and who depends on whom.</p><p>The dbt build command combines some of these steps into one. In real world pipelines people often split them into separate stages so they can choose different policies for “do we stop here or not”.</p><p>The key idea for the <strong>intermediate</strong> layer is this:</p><ul><li>it lets you separate “draw charts” from “keep the data contract”</li><li><strong>marts</strong> do not have to know every detail of raw transactions</li><li>analysts and data engineers get a safe place where they can add fields, fix links and clean data without breaking front facing dashboards</li></ul><p>In one of the next articles we will talk about configuration in more detail, how properties and configs differ and how to manage them at the project level.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d91577d27da3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-3-intermediate-layer-fact-tables-and-the-road-to-marts-d91577d27da3">Hands-on dbt with ClickHouse: #3 Intermediate layer, fact tables and the road to marts</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #2 Data layers, sources.yml, and your first staging model]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/5bb1722c4560</guid>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[analytics-engineering]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[dbt]]></category>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Thu, 04 Dec 2025 08:11:29 GMT</pubDate>
            <atom:updated>2025-12-04T08:18:21.346Z</atom:updated>
            <content:encoded><![CDATA[<p>This is the second article in my dbt + ClickHouse series.</p><p>In the <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">first part</a> we talked about <strong>why dbt is needed at all</strong> and built a small training setup:</p><ul><li>we defined a training scenario where we are a <strong>SaaS payment platform</strong> that connects to different payment providers (Stripe, Paddle, Adyen and others) and processes payments for clients</li><li>we agreed that we need <strong>internal dashboards</strong> to see <strong>how much money we processed, in which project and for which organization</strong></li><li>we decided that our goal is to build <strong>documented marts</strong> with data and logic tests that are safe to expose to BI</li><li>we started a local ClickHouse in Docker and created two databases: training_raw (a simulation of the application “prod” database) and dbt_training (an analytical DWH)</li><li>we loaded a small training snapshot of data and created the first dbt project that connects to this environment</li></ul><p>Now we can move from “we have ClickHouse and some data” to <strong>how we structure data layers and the dbt project,</strong> and write our first stg_ model. To talk about layers, we first need to be clear what lives where in our training setup.</p><p>Locally we have one ClickHouse instance (inside Docker) and two databases:</p><ul><li><strong>training_raw</strong> is raw data. This is a training simulation of the operational database “as is”. Tables look the same as in the real app. We do not clean them here and we do not rewrite them by hand. dbt does not write to this database.</li><li><strong>dbt_training</strong> is the analytical database. This is the place where dbt writes its models. Here we store facts, dimensions and marts.</li></ul><p>Inside dbt_training we do not have schemas like in Postgres. We use <strong>prefixes in table names</strong> to express layers:</p><ul><li><strong>stg_* (staging / silver)<br></strong>We clean raw data, select only needed fields, normalize column names and types. This is the <strong>contract layer</strong>. Everything above depends on it.</li><li><strong>int_*, </strong><strong>dim_*, </strong><strong>fct_* (intermediate)<br></strong>Here we build reusable business entities and facts:<br>int_* — helper models and “bridges” (joins of several staging models, key preparation, normalization of relationships)<br>dim_* — dimensions and reference tables (organizations, projects and so on) that many reports need<br>fct_* — facts (transactions, events and other rows that represent money or actions)</li><li><strong>mart_* (marts / gold)<br></strong>Final marts that are shaped for specific consumers and dashboards. In this training series we will build very few mart_* tables and mention them only a couple of times. In practice the mart layer is built with the same approach as the intermediate layer: SELECT on top of dim_ and fct_ plus materializations and tests. So we focus on dim_ and fct_ in the intermediate layer because they are the parts that are most reusable by different teams.</li></ul><p>Why do we do it like this? ClickHouse does not have schemas inside a database in the same way as Postgres, so we:</p><ol><li>separate raw and analytical data into different databases (training_raw and dbt_training)</li><li>use table name prefixes (stg_, int_, dim_, fct_, mart_) inside dbt_training</li></ol><p>3. In production it looks very similar:</p><ul><li>in ClickHouse you often see separate databases raw / silver / gold</li><li>in Postgres you often have one physical database and several schemas raw, staging, marts</li></ul><p>For the training project we simplify this:</p><ul><li>training_raw plays the role of the raw “prod” database</li><li>dbt_training contains staging plus intermediate plus marts</li></ul><p>This architecture is reflected in the dbt project structure. Inside the models/ folder it looks roughly like this:</p><pre>models/<br>  staging/<br>    prod_clickhouse/<br>      _prod_clickhouse__sources.yml<br>      stg_prod_clickhouse__organizations.sql<br>      stg_prod_clickhouse__projects.sql<br>      stg_prod_clickhouse__transactions.sql<br>  intermediate/<br>    &lt;domain&gt;/<br>      _&lt;domain&gt;__models.yml<br>      int_&lt;domain&gt;__….sql<br>      dim_&lt;domain&gt;__….sql<br>      fct_&lt;domain&gt;__….sql<br>  marts/<br>    &lt;domain&gt;/<br>      _&lt;domain&gt;__models.yml<br>      mart_&lt;domain&gt;__….sql</pre><p>Each subfolder contains YAML files like _prod_clickhouse__sources.yml or _revenue__models.yml. In dbt, YAML is:</p><ul><li>documentation for models and columns</li><li>a place where you declare tests</li><li>a place for configs</li></ul><p>One of the most important YAML files at the start is the one that describes your raw sources. dbt needs to know <strong>where</strong> raw tables live. In our case they are in the training_raw database. To tell dbt about this we declare a source in YAML. models/staging/prod_clickhouse/_prod_clickhouse__sources.yml Minimal example:</p><pre>version: 2<br>sources:<br>  - name: prod_clickhouse<br>    description: &#39;application data in ClickHouse used by the team&#39;<br>    schema: training_raw # in ClickHouse this is the database name<br>    tables:<br>      - name: organizations<br>        description: &#39;organizations, their id, etc.&#39;<br>        columns:<br>          - name: id<br>            description: &#39;organization identifier in our database&#39;<br>          - name: alias<br>            description: &#39;human-readable client name, usually the application name&#39;<br>        …<br>      - name: projects<br>        description: &#39;projects, their id, etc.&#39;<br>        columns:<br>          - name: id<br>            description: &#39;project identifier in our database&#39;<br>        …<br>      - name: transactions<br>        description: &#39;data about end-user transactions in client apps&#39;<br>        columns:<br>          - name: id<br>            description: &#39;transaction identifier in our database&#39;<br>          - name: project_id<br>            description: &#39;client project where this app lives&#39;<br>          - name: organization_id<br>            description: &#39;top-level client / organisation id&#39;<br>          - name: created_at<br>            description: &#39;when the transaction was created (UTC+0)&#39;<br>…</pre><p>Key points:</p><ul><li>version: 2 is the current format for dbt models and sources</li><li>sources: defines the logical name of the source (prod_clickhouse)</li><li>schema: training_raw tells dbt that these tables live physically in the training_raw database</li><li>tables: lists the tables that we will use</li><li>columns: is human-readable documentation and also the place where we will later attach data quality tests</li></ul><p>We will extend this YAML with tests and freshness later. For now the important idea is that dbt knows organizations, projects and transactions live in training_raw and belong to the prod_clickhouse source.</p><p>Now we can write the first staging model. It is <strong>not</strong> just a copy of the raw table. It is already a cleaned and normalized version that becomes a contract for everything above.</p><p>File:</p><p>models/staging/prod_clickhouse/stg_prod_clickhouse__organizations.sql</p><p>Content:</p><pre>{<br>  {<br>    config(<br>        materialized=&#39;table&#39;<br>    )<br>  }<br>}<br><br>SELECT<br>  id,<br>  alias,<br>  created_at,<br>  …<br>FROM<br>{{ source(&#39;prod_clickhouse&#39;, &#39;organizations&#39;) }}</pre><p>Here the exact fields behind … are not the main point. The important part is the approach:</p><ul><li>we <strong>explicitly</strong> list fields and do not write SELECT *</li><li>in staging we keep only what we need in the next layers</li><li>you can add extra fields in your own project if you need them</li></ul><p>We also got lucky in this training example. Our “prod” data is not noisy at all. In real life you almost always have test and technical records. The stg_ layer is a natural place to filter them out.</p><p>For example, if the table had a flag is_test, we could avoid pulling test organizations into the analytical warehouse:</p><pre>SELECT<br>  id,<br>  alias,<br>  created_at,<br>  …<br>FROM<br>{{ source(&#39;prod_clickhouse&#39;, &#39;organizations&#39;) }}<br>WHERE<br>  is_test = 0</pre><p>This kind of filter in a stg_ model saves storage and reduces the risk that test data will leak into marts and dashboards.</p><p>The line{{ config(materialized=’table’) }} tells dbt to <strong>create a physical table</strong> in the target database (dbt_training) with the result of this query.</p><p>Benefits:</p><ul><li>the table is fast to read for downstream models</li><li>it is a stable data layer</li><li>dbt can update it atomically with no downtime</li></ul><p>dbt has several basic materializations:</p><ul><li><strong>table</strong> — creates or recreates a physical table from the select</li><li><strong>view</strong> — creates a view on top of the select (cheap on disk, can be more expensive to query)</li><li><strong>incremental</strong> — keeps the table and on each run only inserts or updates new data</li><li><strong>ephemeral</strong> — does not create any object in the database, dbt inlines the SQL as a CTE into other models</li></ul><p>For ClickHouse there are extra things to think about:</p><ul><li>table engine (MergeTree, ReplacingMergeTree, AggregatingMergeTree and so on)</li><li>sorting key (order_by)</li><li>partitioning (partition_by)</li></ul><p>The dbt-clickhouse adapter lets you configure this in the config:</p><pre>{<br>  {<br>  config(<br>    materialized=&#39;table&#39;,<br>    engine=&#39;MergeTree()&#39;,<br>    order_by=[&#39;organization_id&#39;],<br>    partition_by=&quot;toYYYYMM(created_at)&quot;<br>  )<br>  }<br>}</pre><p>For a training project you can start with a simple materialized=’table’ and come back to engines and partitions later when you work with big facts and incremental models.</p><p>Now let’s go back to our simple staging model and look at one more important detail. It is also important that there is <strong>no</strong> SELECT * here. We always list fields explicitly.</p><p>Reasons:</p><ul><li>we cut off everything that we do not want upstream (PII, debug fields, internal technical columns)</li><li>we fix a <strong>schema contract</strong> so that everything above (int_, dim_, fct_, mart_) depends on a stable set of columns</li><li>we protect ourselves from surprises when a new strange field appears in the raw table</li></ul><p>In a healthy dbt culture staging is not “raw copy”. It is a <strong>normalized layer</strong> that everything else goes through.</p><p>If you use a dbt extension for VS Code or Cursor, you should see a ▶ button (“Execute dbt SQL”) on your model.</p><p>This button does three things:</p><ol><li>compiles the model (expands {{ source(…) }}, macros and Jinja into plain SQL)</li><li>runs this SQL in ClickHouse</li><li>shows the first rows of the result inside the IDE</li></ol><p>If ▶ hangs forever or fails, the most common reasons are simple:</p><ul><li>the IDE uses the system Python (for example /usr/bin/python3) where dbt-clickhouse is not installed</li><li>you need to switch the interpreter to our dbt-env</li></ul><p>Typical sequence:</p><ol><li>In the IDE terminal activate the environment with source dbt-env/bin/activate</li><li>You should see (dbt-env) at the beginning of the prompt</li><li>Check the Python path with which python</li><li>Take this path (…/dbt-env/bin/python) and select it in the IDE via Python: Select Interpreter -&gt; Enter interpreter path…</li></ol><p>After that the dbt extension will run through the environment where dbt-clickhouse is installed. Preview usually starts working.</p><p>If ▶ still does not work well with ClickHouse, that is not a blocker. You can always:</p><ul><li>build the model with dbt build</li><li>check the result in DBeaver or with a simple SELECT * from dbt_training</li></ul><p>Here is a common production horror story:</p><ul><li>prod stops sending transactions</li><li>your pipeline runs on schedule and finishes with a green status</li><li>in the morning the revenue dashboard shows zero</li><li>someone from the business comes and asks what you broke</li></ul><p>The ideal version is different:</p><ul><li>the pipeline itself tells you that the data is stale</li><li>you can debug and fix things before anyone from the business notices</li></ul><p>In dbt you have a separate mode for this: <strong>source freshness checks</strong>:</p><pre>dbt source freshness<br># or for a single table:<br>dbt source freshness --select source:prod_clickhouse.transactions</pre><p>This command <strong>does not</strong> build models, it does not generate or update tables in database. It only looks at a time field loaded_at_field and checks the “freshest” record against warn_after and error_after thresholds.</p><p>To illustrate how it can be used, we add a config block to the transactions table in _prod_clickhouse__sources.yml:</p><pre>- name: transactions<br>  description: &#39;data about end-user transactions in client funnels&#39;<br>  columns:<br>    - name: id<br>      description: &#39;transaction identifier&#39;<br>    - name: project_id<br>      description: &#39;client project&#39;<br>    - name: organization_id<br>      description: &#39;top-level client / organisation id&#39;<br>    - name: created_at<br>      description: &#39;when the transaction was created (UTC+0)&#39;<br>    # other fields can be added here<br>    config:<br>  freshness:<br>    warn_after:<br>      count: 6<br>      period: hour<br>    error_after:<br>      count: 1<br>      period: day<br>    filter: &quot;created_at &gt;= now() - toIntervalDay(3) AND fake = 0&quot;<br>    loaded_at_field: created_at</pre><p>What this means:</p><ul><li>loaded_at_field: created_at — dbt uses MAX(created_at) as the freshest event</li><li>warn_after: 6 hours — if there are no fresh transactions in production db for more than 6 hours, status becomes <strong>WARN</strong></li><li>error_after: 1 day — if there are no fresh transactions for more than 1 day, status becomes <strong>ERROR</strong></li><li>filter: “created_at &gt;= now() — toIntervalDay(3) AND fake = 0” — we only look at the last 3 days and we skip test records (fake = 0 means “real transaction”)</li></ul><p>The ClickHouse-specific trick here is now() — toIntervalDay(3). You can also use INTERVAL 3 DAY in pure ClickHouse, but in practice toIntervalDay works more reliably with dbt-clickhouse in generated freshness queries.</p><p>Now we can run dbt source freshness --select source:prod_clickhouse.transactions and see if our transactions are fresh and if we still match our SLAs.</p><p>Once you can preview a single model, the next natural question is not about one table, but about how fresh the whole source is.</p><p>We have models (stg_, int_, dim_, fct_ and so on), and need to <strong>physically</strong> put them into dbt_training. This is what dbt build does:</p><ul><li>compiles models (expands Jinja, sources and macros)</li><li>creates or updates tables in dbt_training according to the materialization</li><li>runs tests declared in YAML</li><li>deploys new versions atomically</li></ul><p>Atomic update looks roughly like this:</p><ol><li>dbt creates a temporary table with a suffix like __dbt_tmp and writes new data into it</li><li>the old table can be renamed to something like __backup</li><li>__dbt_tmp gets the final model name (for example stg_prod_clickhouse__organizations)</li><li>the old __backup is removed</li></ol><p>Thanks to this there is no window where the table is half empty or does not exist at all.</p><p>After dbt build you can connect with DBeaver and run:</p><pre>SELECT * FROM dbt_training.stg_prod_clickhouse__organizations LIMIT 10;</pre><p>If you see data, the model is live in the database.</p><p>Just like with preview, you do not have to build the whole project every time. You can build a single model:</p><p>dbt build --select stg_prod_clickhouse__projects --log-level debug</p><p>and read the log carefully to see:</p><ul><li>how the temporary table __dbt_tmp is created</li><li>how the old table is renamed to __backup</li><li>how __dbt_tmp is renamed to the final name</li><li>when the old version is removed</li></ul><p>This helps you understand what dbt does under the hood when it builds models.</p><p>It is important <strong>not to mix</strong> these two commands:</p><ul><li>dbt source freshness<br>checks how fresh the incoming data is and answers “can we trust today’s mart”. It returns statuses fresh, warn, error. On error the command exits with a non zero code, which is a perfect trigger for an alert.</li><li>dbt build<br>builds models: staging, intermediate, dims, facts and marts. It refreshes tables in dbt_training and runs tests.</li></ul><p>By default one does not automatically stop the other.</p><p>Even if freshness gives ERROR, a regular dbt build can still finish green. That is an intentional design choice. Different companies have different policies. Some block builds when freshness is in error. Others still build and just send loud alerts to Slack or monitoring.</p><p>In a typical production pipeline the flow looks like this. For now, this is a simplified view that we’ll clarify step by step.</p><p><strong>Step A</strong></p><ol><li>dbt source freshness --select source:prod_clickhouse.transactions</li><li>Check the result:</li></ol><ul><li>if it is WARN, log a soft warning like no fresh transactions for more than 6 hours</li><li>if it is ERROR, send a strong alert like no fresh transactions for more than 24 hours and maybe stop the pipeline depending on your rules</li></ul><p><strong>Step B</strong></p><ol><li>dbt build</li><li>Build stg_, int_, dim_, fct_ and maybe mart_. dbt updates tables in dbt_training atomically.</li></ol><p>After dbt build the target/ folder appears or gets updated in the root of your dbt project.</p><p>Two subfolders are most interesting:</p><ul><li><strong>target/compiled/<br></strong>Here you can find <strong>compiled SQL</strong> for every model. dbt already replaced all {{ source(…) }}, macros and Jinja. This is the exact SQL that will be sent to ClickHouse.</li><li><strong>target/run/<br></strong>Here you find the SQL and DDL that dbt actually executed (create table, insert select and so on).</li></ul><p>This folder:</p><ul><li>is generated automatically</li><li>should not be committed to git</li><li>is very useful when you want to understand what dbt is doing with your database right now</li></ul><p>There is also a practical thing to remember: if you change the materialization of a model (for example from table to view or to incremental), dbt does not always remove the old database object for you. A physical table or an old structure can stay in ClickHouse. Sometimes you need to drop it manually as part of your migration.</p><p>This is normal. Changing materialization means changing the contract for that object. dbt does not want to run aggressive DROP without you explicitly deciding to do it.</p><p>At this stage:</p><ul><li>we clearly separated the <strong>simulated prod database (</strong><strong>training_raw)</strong> and the <strong>analytical warehouse (</strong><strong>dbt_training)</strong></li><li>we agreed on layer prefixes:<br>stg_ for staging, the contract over raw tables and the natural place to cut off test and technical records<br>int_, dim_, fct_ for the intermediate layer with reusable business entities and facts<br>mart_ for final marts that you will mostly build in real projects, not in this small training series</li><li>we declared the prod_clickhouse source in sources.yml and documented the base tables</li><li>we wrote the first stg_ model for organizations and talked about materialization and why we avoid SELECT *</li><li>we showed how to filter test data in a stg_ model</li><li>we configured model preview in the IDE and fixed the Python interpreter problem</li><li>we added freshness settings for transactions to watch data freshness</li><li>we described how dbt build creates tables in ClickHouse and how it works together with dbt source freshness</li><li>we looked at the target/ folder and saw which SQL dbt really executes</li></ul><p>In the next articles we will move up the pyramid. We will build the <strong>intermediate layer</strong>, bridges between organizations and projects, and the first revenue aggregates per project and per organization. Those will be the dim_ and fct_ models that are easiest to reuse across many dashboards.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5bb1722c4560" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">Hands-on dbt with ClickHouse: #2 Data layers, sources.yml, and your first staging model</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hands-on dbt with ClickHouse: #1 Local sandbox and safe training data]]></title>
            <link>https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843?source=rss----30e90288cb0d---4</link>
            <guid isPermaLink="false">https://medium.com/p/64f897be6843</guid>
            <dc:creator><![CDATA[Georgii Baturin]]></dc:creator>
            <pubDate>Fri, 28 Nov 2025 10:09:22 GMT</pubDate>
            <atom:updated>2025-12-05T12:14:56.220Z</atom:updated>
            <content:encoded><![CDATA[<p>This is my first article on Medium.</p><p>I wanted to write something here for a long time, but I could not find a topic that felt practical, alive, and useful for people who actually work with data. Then it clicked. In almost every data or analytics job post you see the same line: <strong>“experience with dbt”</strong>.</p><p>People mention dbt in stack descriptions, talks, and blog posts. It is already used in many teams. At the same time a lot of people have no real chance to try it at work: the stack is already frozen and you cannot change it, you do not have access to prod or a different team “owns” all analytics pipelines. This series is for that situation.If you do not have dbt at work yet, you can still learn it on a <strong>local sandbox</strong> without breaking anything.</p><p>We will go all the way from a clean environment and a local database, through clean model layers, and data and logic tests, to documentation and an orchestrated pipeline.</p><p>The goal is not just “install dbt”. The goal is to walk through all the key steps you will see in a real project. But before that, we need to answer one simple question: “What is dbt and why should you care?”</p><p>dbt (data build tool) helps you:</p><ul><li>describe data transformations <strong>as code</strong> (SQL plus a bit of Jinja)</li><li>split models into clear layers with explicit dependencies</li><li>run rebuilds as separate “builds” instead of random scripts</li><li>attach <strong>tests</strong> to data (not null, unique, relationships, accepted values)</li><li>automatically <strong>document</strong> models and columns through a docs site</li></ul><p>In practice dbt turns a pile of random SQL scripts into a real project:</p><ul><li>with version control in Git</li><li>with clear structure</li><li>with data quality checks and documentation</li></ul><p>Most tutorials use PostgreSQL because dbt has a first-class adapter for it and a lot of official examples. It’s easier for tutors. In this series we go a bit more “real world” and use <strong>ClickHouse</strong> instead. There is no official dbt adapter in the core distribution for ClickHouse. That makes it a good example of how to connect dbt to a warehouse that is not fully first class, work around limits and still get usable models and tests and later plug the whole thing into an orchestrator.</p><p>You can think of this series as a hands-on mini-course on dbt with ClickHouse.</p><p>We will:</p><ul><li>spin up a local analytics environment</li><li>build model layers</li><li>add data and logic tests</li><li>document models so new people can understand what is going on</li><li>run it all under an orchestrator</li></ul><p>The series is a good fit if you are:</p><ul><li>a <strong>data analyst</strong> who writes SQL with confidence but never touched dbt</li><li>a <strong>data engineer</strong> who wants structure around marts and transforms</li><li>a <strong>developer</strong> who sometimes works with data and wants to see how modern analytics stacks are wired</li></ul><p>I do not know your exact level, so I will write as if you:</p><ul><li>already know how to write SQL</li><li>never used Docker in real life</li><li>never configured Git with SSH keys</li><li>never bootstrapped a dbt project from scratch</li></ul><p>In this article we will set up all necessary environment: start a local ClickHouse inside Docker, create a separate training database, prepare a small training copy of data in this local database that we will treat it as our “prod data source” for the analytics warehouse and finally create the first dbt project and check that it connects correctly</p><p>In future parts we will move layer by layer: staging models, intermediate models, implementing tests, documentation, orchestration and alerting.</p><p>We will go step by step, but I will focus on why each step matters in a real project, not only on what to type into the terminal.</p><p>We start by creating a working directory, a separate Python virtual environment for dbt, and installing dbt-core plus the ClickHouse adapter. I use the <strong>OS terminal</strong> (on my side this is macOS with the standard Terminal / iTerm2 and zsh / bash). If you are on Windows or Linux, commands will look slightly different, but the idea is the same.</p><p>You also need <strong>Docker Desktop</strong> installed and running. You can get it <a href="https://www.docker.com/products/docker-desktop/">here</a>.</p><p>First we need a clean sandbox for this series, so we create a separate folder and a dedicated Python environment just for dbt.</p><pre>mkdir &quot;dbt-course&quot;<br>cd &quot;dbt-course&quot;</pre><pre># create a dedicated virtual environment for dbt<br>python -m venv dbt-env</pre><pre># activate this environment<br>source dbt-env/bin/activate<br>python -m pip install -U dbt-core dbt-clickhouse</pre><pre>dbt --version</pre><p>If <em>dbt --version</em> prints the version and does not fail, the base environment is ready.</p><p>We will pull <strong>one approved sample of data</strong> into a local training database.<br>We will only read data. No writes to prod. To do that safely, we need a place to keep connection details that never land in Git or screenshots. That is the role of the .env.local file.</p><p>Create folders for Docker and scripts. Then create a .env.local file with connection settings. This file stays only on your machine.</p><pre>mkdir -p docker scripts</pre><pre>printf &quot;.env.local<br>.DS_Store<br>&quot; &gt;&gt; .gitignore<br>cat &gt; .env.local &lt;&lt; &#39;EOF&#39;<br>PROD_HOST=&lt;your host without quotes&gt;<br>PROD_PORT=&lt;your port without quotes&gt;<br>PROD_DB=&lt;your database without quotes&gt;<br>PROD_USER=&lt;your username without quotes&gt;<br>PROD_PASS=&lt;your password without quotes&gt;<br>USE_TLS=0<br>EOF</pre><pre>chmod 600 .env.local</pre><p>Important details:</p><ul><li>this should be an account with <strong>select only</strong> rights agreed with your team or admins</li><li>we added .env.local to <em>.gitignore</em> right away so it does not land in GitHub</li></ul><p>We will set up a ClickHouse instance in Docker as our <strong>training database</strong>. dbt will write models here. This will act as our <strong>analytics warehouse</strong> in the series.</p><p>I like this pattern because it gives you a database you can freely break, wipe and recreate without asking anyone from infrastructure.</p><pre>cat &gt; docker/docker-compose.yml &lt;&lt; &#39;EOF&#39;<br>services:<br>ch-training:<br>image: clickhouse/clickhouse-server:latest<br>container_name: ch-training<br>environment:<br>CLICKHOUSE_DB: dbt_training<br>CLICKHOUSE_USER: trainer<br>CLICKHOUSE_PASSWORD: trainer_pwd<br>ports:<br>- &quot;9000:9000&quot; # native protocol for dbt<br>- &quot;8123:8123&quot; # HTTP for DBeaver<br>- &quot;8443:8443&quot; # HTTPS (optional)<br>volumes:<br>- ch_data:/var/lib/clickhouse<br>- ch_logs:/var/log/clickhouse-server<br>restart: unless-stopped<br>volumes:<br>ch_data:<br>ch_logs:<br>EOF</pre><p>Now we can start the container:</p><pre>cd docker<br>docker compose up -d<br>cd ..</pre><p>and check that ClickHouse is up and responding:</p><pre>docker exec -it ch-training clickhouse-client -q &quot;SELECT &#39;OK local CH&#39;;&quot;</pre><p>We are expecting to see: OK local CH. So if you see this, the container is running.</p><p>With ClickHouse up and running, we can now decide how to split it into logical roles. We want a small but realistic simulation of two sides:</p><ul><li>a <strong>prod like source</strong> where raw operational data lives</li><li>an <strong>analytics warehouse</strong> where dbt writes cleaned and modeled tables</li></ul><p>We will use two databases for that:</p><ul><li><em>training_raw </em>for raw data pulled from the “prod” copy</li><li><em>dbt_training</em> for dbt models and marts</li></ul><p>Lets create them:</p><pre>docker exec -it ch-training clickhouse-client -q &quot;CREATE DATABASE IF NOT EXISTS training_raw;&quot;<br>docker exec -it ch-training clickhouse-client -q &quot;CREATE DATABASE IF NOT EXISTS dbt_training;&quot;</pre><p>Now it’s time to decide what data we need. Instead of working with abstract tables, I prefer to anchor everything in a simple but realistic product story.</p><p>Assume we are a <strong>SaaS payment platform</strong> that helps other companies accept payments through different gateways. Instead of integrating with Stripe, Paddle, Adyen and many others one by one, companies integrate only with us. We handle the complexity of payment providers for them. Each client (organization) can have several independent projects: a game studio can have three games, a marketplace can have several storefronts or regional versions, etc. All of these can generate transactions. We want to answer basic questions:</p><ul><li>how much money we processed over a period</li><li>what revenue each organization generates</li><li>which projects bring most of the money and which almost never see any transactions</li><li>how many active organizations and projects we have</li><li>how transactions are distributed across organizations and projects</li></ul><p>The long term goal of the series is to have <strong>documented marts</strong> with <strong>data and logic tests</strong>, <strong>automatic refresh</strong>, and dashboards on top of them. To do that we need three minimal building blocks:</p><ul><li><strong>organizations</strong> (organizations) — who the client is and which projects belong to them</li><li><strong>projects</strong> (projects) — the concrete entities under an organization (games, storefronts, products)</li><li><strong>transactions</strong> (transactions)- the payments that passed and how much money they brought</li></ul><p>In this article we create a <strong>small training copy</strong> of these three tables. We will treat it as our “production source” for the analytics DWH. Now the technical part. At this point our local ClickHouse is empty, so let us write a small script that can pull and refresh this training copy in a repeatable way.</p><p>On the local ClickHouse we temporarily turn off query logging so the prod password does not end up in <em>system.query_log</em>. Then we create three tables in training_raw and fill them.</p><pre>cat &gt; scripts/refresh_raw.sh &lt;&lt; ‘EOF’<br>#!/usr/bin/env bash<br>set -euo pipefail<br>if [ -f &quot;.env.local&quot; ]; then<br># shellcheck disable=SC2046<br>export $(grep -v &#39;^#&#39; .env.local | xargs)<br>else<br>echo &quot;.env.local not found&quot; &gt;&amp;2<br>exit 1<br>fi<br>REMOTE_FUNC=&quot;remote&quot;<br>if [ &quot;${USE_TLS:-0}&quot; = &quot;1&quot; ]; then<br>REMOTE_FUNC=&quot;remoteSecure&quot;<br>fi<br>SET_PREFIX=&quot;SET log_queries = 0;&quot;<br># make sure the database exists<br>docker exec -i ch-training clickhouse-client -q &quot;<br>${SET_PREFIX}<br>CREATE DATABASE IF NOT EXISTS training_raw;<br>&quot;<br># organizations - all rows<br>docker exec -i ch-training clickhouse-client -q &quot;<br>${SET_PREFIX}<br>CREATE OR REPLACE TABLE training_raw.organizations<br>ENGINE = MergeTree<br>ORDER BY (id)<br>AS<br>SELECT *<br>FROM ${REMOTE_FUNC}(&#39;${PROD_HOST}:${PROD_PORT}&#39;, &#39;${PROD_DB}&#39;, &#39;organizations&#39;, &#39;${PROD_USER}&#39;, &#39;${PROD_PASS}&#39;);<br>&quot;<br># projects - all rows<br>docker exec -i ch-training clickhouse-client -q &quot;<br>${SET_PREFIX}<br>CREATE OR REPLACE TABLE training_raw.projects<br>ENGINE = MergeTree<br>ORDER BY (id)<br>AS<br>SELECT *<br>FROM ${REMOTE_FUNC}(&#39;${PROD_HOST}:${PROD_PORT}&#39;, &#39;${PROD_DB}&#39;, &#39;projects&#39;, &#39;${PROD_USER}&#39;, &#39;${PROD_PASS}&#39;);<br>&quot;<br># transactions - last 1000 rows by created_at<br>docker exec -i ch-training clickhouse-client -q &quot;<br>${SET_PREFIX}<br>CREATE OR REPLACE TABLE training_raw.transactions<br>ENGINE = MergeTree<br>ORDER BY (created_at)<br>AS<br>SELECT *<br>FROM ${REMOTE_FUNC}(&#39;${PROD_HOST}:${PROD_PORT}&#39;, &#39;${PROD_DB}&#39;, &#39;transactions&#39;, &#39;${PROD_USER}&#39;, &#39;${PROD_PASS}&#39;)<br>ORDER BY created_at DESC<br>LIMIT 1000;<br>&quot;<br># small report on loaded tables<br>docker exec -it ch-training clickhouse-client -q &quot;<br>SELECT table, sum(rows) AS rows<br>FROM system.parts<br>WHERE database = &#39;training_raw&#39; AND active<br>GROUP BY table<br>ORDER BY table;<br>&quot;<br>EOF<br>chmod +x scripts/refresh_raw.sh<br>./scripts/refresh_raw.sh</pre><p>After this we will have 3 tables: training_raw.organizations, training_raw.projects and training_raw.transactions and the script will print us a short report with row counts for each table.</p><p>This is already close to how many teams refresh training or QA datasets from production: a small, boring script that you can run again and again. We can check that the data is inside the <strong>local</strong> ClickHouse instance:</p><ol><li>Open DBeaver</li><li>Database → New Connection → choose <strong>ClickHouse</strong></li><li>Fill in:</li></ol><ul><li>Host: localhost</li><li>Port: 8123</li><li>Database: default is fine</li><li>Username: trainer</li><li>Password: trainer_pwd</li><li>SSL: disabled for port 8123</li></ul><p>4. Click <strong>Test Connection</strong> then <strong>Finish</strong></p><p>In the schema browser you should see training_raw and dbt_training. Inside training_raw there should be our three tables.</p><p>If you do not use DBeaver, you can do a quick check with curl:</p><pre>curl &#39;http://localhost:8123/?query=SELECT%201&#39;<br>curl -u trainer:trainer_pwd \<br>&#39;http://localhost:8123/?query=SELECT%20currentUser()&#39;</pre><p>If everything is fine, the second command prints trainer.</p><p>Now that the data is in place, we can finally bring dbt into the picture and set up the first project.</p><p>In Terminal you should still be in the dbt-course directory where dbt-env, docker, and scripts live. Run: dbt init training_project. dbt may warn that there is no ready sample profile for ClickHouse. That is normal. After this you get a new folder training_project next to docker and scripts.</p><p>Now we will tell dbt how to connect to our local ClickHouse and where to put models. We do this in profiles.yml. On macOS and Linux it lives in ~/.dbt/profiles.yml.</p><p>Create the folder and the file:</p><pre>mkdir -p ~/.dbt<br>cat &gt; ~/.dbt/profiles.yml &lt;&lt; &#39;EOF&#39;<br>training_project:<br>target: dev<br>outputs:<br>dev:<br>type: clickhouse<br>host: localhost<br>port: 9000<br>user: trainer<br>password: trainer_pwd<br>secure: false<br>verify: false<br>threads: 4<br>schema: dbt_training<br>driver: native<br>EOF</pre><p>Now open training_project/dbt_project.yml and check:</p><pre>name: training_project<br>version: 1.0.0<br>profile: training_project<br>models:<br>training_project:<br>+materialized: table</pre><p>The important line is:</p><pre>profile: training_project</pre><p>It must match the top level key in profiles.yml (training_project:). If they do not match, dbt debug will fail and complain that it cannot find the profile.</p><p>Now we are ready to work like a normal developer and open our dbt project in Cursor or Vs Code: Use File → Open Folder… and choose the training_project folder. Do not forget to install <strong>“Power User for dbt”</strong> (or another dbt extension you like)<strong> </strong>and activate the virtual environment inside the IDE terminal and run dbt ( in VS Code: View → Terminal, in Cursor: Terminal)</p><pre># if your terminal is inside training_project right now<br>cd .. # go one level up, back to dbt-course<br>source dbt-env/bin/activate # activate the dbt-env virtual env<br>cd training_project # go back into the dbt project<br>dbt debug # check profile and connection work</pre><p>If dbt debug is green and does not shout Could not find profile named …, the project is wired correctly.</p><p>For many people this is already enough to play with dbt locally. If you want to treat this as a real project and keep it in GitHub, you can add Git on top.</p><p>Initial commit:</p><pre>git init<br>git add .<br>git commit -m &quot;dbt project init&quot;<br>git branch -M main</pre><p>Then:</p><ol><li>Go to GitHub in the browser</li><li>Click <strong>New repository</strong></li><li>Name it dbt_training</li><li>Do not add a README through the GitHub UI</li></ol><p>Connect your local repo over SSH:</p><pre>git remote add origin git@github.com:&lt;github username&gt;/dbt_training.git<br>git push -u origin main</pre><p>If you see Enumerating objects, Writing objects and a To github.com:… line, you are done.</p><p>If you see Permission denied (publickey) it means GitHub does not know your laptop yet. Generate an SSH key one time: ssh-keygen -t ed25519 -C “your_email_on_GitHub”</p><p>The tool will ask:</p><ul><li>where to save the key — just press Enter</li><li>passphrase — press Enter, then Enter again if you want no passphrase on your personal laptop</li></ul><p>You will get two files:</p><ul><li>~/.ssh/id_ed25519</li><li>~/.ssh/id_ed25519.pub</li></ul><p>Show the public key:</p><pre>cat ~/.ssh/id_ed25519.pub</pre><p>Copy the full line that starts with ssh-ed25519.</p><p>In GitHub:</p><ul><li>go to <strong>Settings</strong></li><li>open <strong>SSH and GPG keys</strong></li><li>click <strong>New SSH key</strong></li><li>set a title</li><li>choose type Authentication Key</li><li>paste the key and click <strong>Add SSH key</strong></li></ul><p>Then repeat:</p><pre>git push -u origin main</pre><p>From now on your dbt project lives in GitHub.</p><p>If all of this felt like a lot of setup, that is normal. In real projects you also spend time wiring things together before you write any serious SELECT. At this point we have:</p><ul><li>a local ClickHouse inside Docker</li><li>a small training copy of data that simulates a production database</li><li>a separate database where dbt will write models and marts</li><li>a working dbt project that connects to this setup</li></ul><p>In the next articles we will <a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-2-data-layers-sources-yml-and-your-first-staging-model-5bb1722c4560">start building the <strong>staging layer</strong></a>, then intermediate models, then marts on top of this data. After that we will add tests, documentation, and an orchestrated pipeline so the whole thing can run on its own.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=64f897be6843" width="1" height="1" alt=""><hr><p><a href="https://medium.com/hands-on-dbt-with-clickhouse/hands-on-dbt-with-clickhouse-1-local-sandbox-and-safe-training-data-64f897be6843">Hands-on dbt with ClickHouse: #1 Local sandbox and safe training data</a> was originally published in <a href="https://medium.com/hands-on-dbt-with-clickhouse">Hands-on dbt with ClickHouse</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>