Guides

Note how a table actor\_summary\_snapshot has been created in the snapshots db (determined by the target\_schema parameter).

4. Sampling this data you will see how dbt has included the columns dbt\_valid\_from and dbt\_valid\_to. The latter has values set to null. Subsequent runs will update this.

   ```
   SELECT id, name, num_movies, dbt_valid_from, dbt_valid_to FROM snapshots.actor_summary_snapshot ORDER BY num_movies DESC LIMIT 5;
   ```

   ```
   +------+----------+------------+----------+-------------------+------------+
   |id    |first_name|last_name   |num_movies|dbt_valid_from     |dbt_valid_to|
   +------+----------+------------+----------+-------------------+------------+
   |845467|Danny     |DeBito      |920       |2022-05-25 19:33:32|NULL        |
   |845466|Clicky    |McClickHouse|910       |2022-05-25 19:32:34|NULL        |
   |45332 |Mel       |Blanc       |909       |2022-05-25 19:31:47|NULL        |
   |621468|Bess      |Flowers     |672       |2022-05-25 19:31:47|NULL        |
   |283127|Tom       |London      |549       |2022-05-25 19:31:47|NULL        |
   +------+----------+------------+----------+-------------------+------------+
   ```
5. Make our favorite actor Clicky McClickHouse appear in another 10 films.

   ```
   INSERT INTO imdb.roles
   SELECT now() as created_at, 845466 as actor_id, rand(number) % 412320 as movie_id, 'Himself' as role
   FROM system.numbers
   LIMIT 10;
   ```
6. Re-run the dbt run command from the `imdb` directory. This will update the incremental model. Once this is complete, run the dbt snapshot to capture the changes.

   ```
   clickhouse-user@clickhouse:~/imdb$ dbt run
   13:46:14  Running with dbt=1.1.0
   13:46:14  Found 1 model, 0 tests, 1 snapshot, 0 analyses, 181 macros, 0 operations, 0 seed files, 3 sources, 0 exposures, 0 metrics
   13:46:14
   13:46:15  Concurrency: 1 threads (target='dev')
   13:46:15
   13:46:15  1 of 1 START incremental model imdb_dbt.actor_summary....................... [RUN]
   13:46:18  1 of 1 OK created incremental model imdb_dbt.actor_summary.................. [OK in 2.76s]
   13:46:18
   13:46:18  Finished running 1 incremental model in 3.73s.
   13:46:18
   13:46:18  Completed successfully
   13:46:18
   13:46:18  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

   clickhouse-user@clickhouse:~/imdb$ dbt snapshot
   13:46:26  Running with dbt=1.1.0
   13:46:26  Found 1 model, 0 tests, 1 snapshot, 0 analyses, 181 macros, 0 operations, 0 seed files, 3 sources, 0 exposures, 0 metrics
   13:46:26
   13:46:27  Concurrency: 1 threads (target='dev')
   13:46:27
   13:46:27  1 of 1 START snapshot snapshots.actor_summary_snapshot...................... [RUN]
   13:46:31  1 of 1 OK snapshotted snapshots.actor_summary_snapshot...................... [OK in 4.05s]
   13:46:31
   13:46:31  Finished running 1 snapshot in 5.02s.
   13:46:31
   13:46:31  Completed successfully
   13:46:31
   13:46:31  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
   ```
7. If we now query our snapshot, notice we have 2 rows for Clicky McClickHouse. Our previous entry now has a dbt\_valid\_to value. Our new value is recorded with the same value in the dbt\_valid\_from column, and a dbt\_valid\_to value of null. If we did have new rows, these would also be appended to the snapshot.

   ```
   SELECT id, name, num_movies, dbt_valid_from, dbt_valid_to FROM snapshots.actor_summary_snapshot ORDER BY num_movies DESC LIMIT 5;
   ```

   ```
   +------+----------+------------+----------+-------------------+-------------------+
   |id    |first_name|last_name   |num_movies|dbt_valid_from     |dbt_valid_to       |
   +------+----------+------------+----------+-------------------+-------------------+
   |845467|Danny     |DeBito      |920       |2022-05-25 19:33:32|NULL               |
   |845466|Clicky    |McClickHouse|920       |2022-05-25 19:34:37|NULL               |
   |845466|Clicky    |McClickHouse|910       |2022-05-25 19:32:34|2022-05-25 19:34:37|
   |45332 |Mel       |Blanc       |909       |2022-05-25 19:31:47|NULL               |
   |621468|Bess      |Flowers     |672       |2022-05-25 19:31:47|NULL               |
   +------+----------+------------+----------+-------------------+-------------------+
   ```

For further details on dbt snapshots see [here](https://docs.getdbt.com/docs/building-a-dbt-project/snapshots).

## Using seeds[​](#using-seeds "Direct link to Using seeds")

dbt provides the ability to load data from CSV files. This capability isn't suited to loading large exports of a database and is more designed for small files typically used for code tables and [dictionaries](/docs/dictionary), e.g. mapping country codes to country names. For a simple example, we generate and then upload a list of genre codes using the seed functionality.

1. We generate a list of genre codes from our existing dataset. From the dbt directory, use the `clickhouse-client` to create a file `seeds/genre_codes.csv`:

   ```
   clickhouse-user@clickhouse:~/imdb$ clickhouse-client --password <password> --query
   "SELECT genre, ucase(substring(genre, 1, 3)) as code FROM imdb.genres GROUP BY genre
   LIMIT 100 FORMAT CSVWithNames" > seeds/genre_codes.csv
   ```
2. Execute the `dbt seed` command. This will create a new table `genre_codes` in our database `imdb_dbt` (as defined by our schema configuration) with the rows from our csv file.

   ```
   clickhouse-user@clickhouse:~/imdb$ dbt seed
   17:03:23  Running with dbt=1.1.0
   17:03:23  Found 1 model, 0 tests, 1 snapshot, 0 analyses, 181 macros, 0 operations, 1 seed file, 6 sources, 0 exposures, 0 metrics
   17:03:23
   17:03:24  Concurrency: 1 threads (target='dev')
   17:03:24
   17:03:24  1 of 1 START seed file imdb_dbt.genre_codes..................................... [RUN]
   17:03:24  1 of 1 OK loaded seed file imdb_dbt.genre_codes................................. [INSERT 21 in 0.65s]
   17:03:24
   17:03:24  Finished running 1 seed in 1.62s.
   17:03:24
   17:03:24  Completed successfully
   17:03:24
   17:03:24  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
   ```
3. Confirm these have been loaded:

   ```
   SELECT * FROM imdb_dbt.genre_codes LIMIT 10;
   ```

   ```
   +-------+----+
   |genre  |code|
   +-------+----+
   |Drama  |DRA |
   |Romance|ROM |
   |Short  |SHO |
   |Mystery|MYS |
   |Adult  |ADU |
   |Family |FAM |

   |Action |ACT |
   |Sci-Fi |SCI |
   |Horror |HOR |
   |War    |WAR |
   +-------+----+=
   ```

## Further Information[​](#further-information "Direct link to Further Information")

The previous guides only touch the surface of dbt functionality. Users are recommended to read the excellent [dbt documentation](https://docs.getdbt.com/docs/introduction).

- [Setup](#setup)
  - [Prepare ClickHouse](#prepare-clickhouse)- [Connecting to ClickHouse](#connecting-to-clickhouse)- [Creating a simple view materialization](#creating-a-simple-view-materialization)- [Creating a table materialization](#creating-a-table-materialization)- [Creating an Incremental Materialization](#creating-an-incremental-materialization)
          - [Internals](#internals)- [Append Strategy (inserts-only mode)](#append-strategy-inserts-only-mode)- [Delete and insert mode (experimental)](#deleteinsert-mode-experimental)- [insert\_overwrite mode (experimental)](#insert_overwrite-mode-experimental)- [Creating a snapshot](#creating-a-snapshot)- [Using seeds](#using-seeds)- [Further Information](#further-information)

Was this page helpful?