# Bringing dbt and Analytics Engineering to Oracle Autonomous Data Warehouse — Rittman Analytics URL: https://rittmananalytics.com/blog/2023/09/07/bringing-dbt-and-analytics-engineering-to-oracle-autonomous-data-warehouse-rittman-analytics Description: Bringing dbt and Analytics Engineering to Oracle Autonomous Data Warehouse OracleAnalytics Engineering 7 Sept Written By Mark Rittman dbt (Data Build Tool) is an open source command-line tool for managing… Author: Mark Rittman Published: 2023-09-07T01:00:00+01:00 Modified: 2023-09-07T01:00:00+01:00 --- ![Bringing dbt and Analytics Engineering to Oracle Autonomous Data Warehouse — Rittman Analytics](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fdtdtemp8.wpcomstaging.com%2Fwp-content%2Fuploads%2F2023%2F09%2F1752872081516-7xugg7.jpg%3Ffit%3D2048%252C776%26ssl%3D1) # Bringing dbt and Analytics Engineering to Oracle Autonomous Data Warehouse [Oracle](https://rittmananalytics.com/blog/category/Oracle)[Analytics Engineering](https://rittmananalytics.com/blog/category/Analytics+Engineering) 7 Sept Written By [Mark Rittman](https://rittmananalytics.com/blog?author=5846da08f5e231d83261e221) [dbt (Data Build Tool)](https://www.getdbt.com/blog/what-exactly-is-dbt/) is an open source command-line tool for managing and automating data transformation workflows. It enables a standardised, modular approach to building analytics code and is typically used as part of an increasingly popular method of developing data warehouses and data platforms called [analytics engineering](https://www.getdbt.com/what-is-analytics-engineering/). The analytics engineering approach combines software engineering principles with analytics to create more maintainable, trustworthy data pipelines built on transformation logic that is modular, scalable, tested, and well-documented. With dbt, analytics code progresses from messy, ad hoc scripts to structured engineering workflows developed using a [software development lifecycle](https://www.airops.com/blog/5-reasons-dbt-sets-the-standard-for-data-transformation-tools). In this post, I’ll introduce dbt, explain its benefits for Oracle Database and Autonomous Data Warehouse developers and walk through getting started with dbt and the new [dbt-oracle adapter](https://github.com/oracle/dbt-oracle). #### What is dbt? ELT (Extract, Load, Transform) is a modern data warehousing pattern very familiar to Oracle data warehouse developers where raw data is first extracted and loaded into the target warehouse, then transformed within the warehouse itself. This is where dbt comes in — it provides the “T” for managing transformations in ELT, with the E (extract) and L (load) typically handled by services such as [Fivetran](https://www.fivetran.com/), Airbyte or Stitch. ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081516-7xugg7.jpg%3Fresize%3D2048%252C776%26ssl%3D1) Originally developed to help data analysts within the VC-backed startup world develop production-ready data transformation code for Amazon Redshift and other cloud data warehouses, dbt is now more of an ecosystem than single tool and comprises of: - An open source core ([dbt Core](https://pypi.org/project/dbt-core/)), originally developed by Fishtown Analytics (now dbt Labs) that you run from the command-line and can schedule using cron jobs or orchestrators such as [Dagster](https://docs.dagster.io/integrations/dbt) - a commercial SaaS service ([dbt Cloud](https://www.getdbt.com/product/dbt-cloud/)) that provides features for developing, testing, scheduling and investigating data models - community-provided utilities and content packages (such as our [RA Attribution](https://github.com/rittmananalytics/ra_attribution) package for multi-touch marketing attribution) along with [official](http://getdbt.slack.com/) and [related](http://locallyoptimistic.slack.com/) Slack groups Key capabilities of dbt include: - Dependency management — dbt handles model dependencies automatically - Testing — add tests to models to ensure data quality - Refactoring — easily refactor models without breaking dependencies - Documentation — auto-generate docs for all models in a project - CI/CD Integration — build, test, and deploy projects on every code change Whereas tools such as Oracle Data Integrator (ODI) and Oracle Warehouse Builder provide a visual, GUI-based mapping interface for designing data flows, dbt instead takes a code-centric approach with transformations encapsulated in modular SQL SELECT statements called “models”, either written and run using a text editor or tool such as Microsoft VS Code. ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081701-iwj2p7.jpg%3Fresize%3D2896%252C1760%26ssl%3D1) Developing transformation logic using a GUI has the benefit of opening-up this task to a wider audience, but when the business requires complex data transformations such an approach can become unwieldy. More of a concern though is the ungoverned shadow SQL or Python code that drag-and-drop tool users end-up adding to their visual transformation nodes, making it hard to track changes and ensure those transformations and embedded are tested and reliable in a scalable and automated way. ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081826-ug0skx.jpg%3Fresize%3D1400%252C906%26ssl%3D1) A key difference between ODI and dbt is therefore in its approach to modularity and reusable transformation logic. With ODI, code reuse is achieved by copying and modifying mappings across projects; dbt, however achieves this through the use of those modular, configurable SQL models that can be packaged up into shared libraries and invoked repeatedly. ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081591-znq3x.jpg%3Fresize%3D1794%252C1402%26ssl%3D1) Also, crucially, dbt models exist as simple SQL files making them straightforward to version control via Git. ODI’s visual mappings are stored within its repository, requiring use of ODI lifecycle tools. With dbt, developers can utilize the full power of Git for branching strategies, pull requests, and decentralized development. ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081585-v7r8f.jpg%3Fresize%3D1400%252C904%26ssl%3D1) dbt models also provide visibility into history and differences between versions. #### Getting Started with dbt-oracle The [dbt-oracle adapter](https://github.com/oracle/dbt-oracle) (originally created by Indicium, now supported and maintained directly by Oracle) is a python package that contains the core dbt libraries together with an adapter for connecting to on-premises and cloud Oracle databases. This, along with an install of Python 3 and the [oracledb client drivers package](https://oracle.github.io/python-oracledb/) are what’s required to install dbt on your Mac, for example. python3 -m venv dbt-oracle-venv source dbt-oracle-venv/bin/activate pip install –upgrade pip python -m pip install oracledb –upgrade python -m pip install dbt-oracle The dbt-oracle Github repo at [https://github.com/oracle/dbt-oracle](https://github.com/oracle/dbt-oracle) contains a demo dbt package that transforms data from the SH (Sales History) example schema shipped with all Oracle database versions. Looking at an example dbt model within that demo package, you can see examples of model configuration settings, references to other tables in the form of sources and some Jinja templating code that adds a further WHERE clause to the SQL statement when the model is compiled in incremental load mode. {{config(materialized='incremental', unique\_key='group\_id')}} WITH direct\_sales\_promo\_cost AS ( SELECT s.prod\_id, s.quantity\_sold, s.amount\_sold, s.time\_id, c.channel\_desc, p.promo\_name, p.promo\_cost, {{ hash\_arguments(\['s.prod\_id', 's.quantity\_sold', 's.time\_id', 'p.promo\_name'\]) }} AS group\_id FROM {{ source('sh\_database', 'sales') }} s, {{ source('sh\_database', 'promotions') }} p, {{ source('sh\_database', 'channels') }} c WHERE s.channel\_id = 3 AND s.promo\_id = p.promo\_id AND s.channel\_id = c.channel\_id {% if is\_incremental() %} AND s.time\_id \> (SELECT MAX(time\_id) FROM {{ this }}) {% endif %} ) SELECT \* FROM direct\_sales\_promo\_cost Other model definitions use [ref() Jinja functions](https://docs.getdbt.com/reference/dbt-jinja-functions/ref) instead of table names to define dependencies between tables in a transformation DAG, and use Oracle-specific configuration settings to tell dbt how specifically to materialize the database object that is the result of compiling this particular model. {{config( materialized='table', parallel=4, table\_compression\_clause='COLUMN STORE COMPRESS FOR QUERY' ) }} select c.cust\_id, c.cust\_first\_name, c.cust\_last\_name, t.country\_iso\_code, t.country\_name, t.country\_region from {{ ref('sales\_internet\_channel') }} s, {{ source('sh\_database', 'countries') }} t, {{ source('sh\_database', 'customers') }} c WHERE s.cust\_id = c.cust\_id AND c.country\_id = t.country\_id dbt Core data source connections are defined using a configuration file in YAML format stored by default at $HOME/.dbt/profiles.yml, outside of the git repository files so database credentials don’t get published with your code by accident. dbt\_test: target: dev outputs: dev: type: oracle user: MARK\_DBT pass: \*\*\*\*\* protocol: "tcps" host: adb.uk-london-1.oraclecloud.com port: 1522 service: g71ab9f09757846\_radevelopmentadw\_high.adb.oraclecloud.com schema: MARK\_DBT retry\_count: 1 retry\_delay: 5 shardingkey: - skey supershardingkey: - sskey cclass: CONNECTIVITY\_CLASS purity: self threads: 1 There are various ways that dbt can connect to an Oracle database; using thin or thick drivers from the python-oracledb Oracle client libraries package along with the choice of either TLS (Transport Layer Security) or mutual TLS (mTLS) when connecting to Oracle Autonomous Data Warehouse or Autonomous Transaction Processing. I used the thin client mode and was able to connect to Oracle Autonomous Data Warehouse Cloud without using a wallet file, using just my database username, password and host/port/service name. dbt Core packages are run from the command-line and when executed, compile your SQL and Jinja model code into SQL SELECT and MERGE statements that transforms, tests and runs utilities to transform your data within the Oracle database. (dbt-oracle-venv) markrittman@marks-imac-4 dbt\_adbs\_test\_project % dbt build 21:47:47 Running with dbt=1.5.3 21:47:47 oracle adapter: Running in thin mode 21:47:47 Registered adapter: oracle=1.5.3 21:47:47 Found 17 models, 10 tests, 1 snapshot, 1 analysis, 637 macros, 3 operations, 1 seed file, 8 sources, 2 exposures, 0 metrics, 0 groups 21:47:47 21:47:50 21:47:50 Running 1 on-run-start hook 21:47:50 1 of 1 START hook: dbt\_adbs\_test\_project.on-run-start.0 ........................ \[RUN\] 21:47:50 1 of 1 OK hook: dbt\_adbs\_test\_project.on-run-start.0 ........................... \[OK in 0.03s\] 21:47:50 21:47:50 Concurrency: 1 threads (target='dev') 21:47:50 21:47:50 1 of 28 START sql table model MARK\_DBT.countries ............................... \[RUN\] 21:47:52 1 of 28 OK created sql table model MARK\_DBT.countries .......................... \[OK in 1.30s\] 21:47:52 2 of 28 START sql view model MARK\_DBT.direct\_sales\_channel\_promo\_cost .......... \[RUN\] 21:47:53 2 of 28 OK created sql view model MARK\_DBT.direct\_sales\_channel\_promo\_cost ..... \[OK in 0.88s\] 21:47:53 3 of 28 START sql table model MARK\_DBT.promotion\_costs ......................... \[RUN\] 21:47:54 3 of 28 OK created sql table model MARK\_DBT.promotion\_costs .................... \[OK in 1.30s\] 21:47:54 4 of 28 START sql table model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel \[RUN\] 21:47:56 4 of 28 OK created sql table model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel \[OK in 2.49s\] 21:47:56 5 of 28 START sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_insert \[RUN\] 21:47:59 5 of 28 OK created sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_insert \[OK in 2.37s\] 21:47:59 6 of 28 START sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_merge \[RUN\] 21:48:00 6 of 28 OK created sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_merge \[OK in 1.56s\] 21:48:00 7 of 28 START sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_merge\_unique\_keys \[RUN\] 21:48:02 7 of 28 OK created sql incremental model MARK\_DBT.promotion\_costs\_for\_direct\_sales\_channel\_incr\_merge\_unique\_keys \[OK in 1.78s\] 21:48:02 8 of 28 START sql table model MARK\_DBT.sales\_cost .............................. \[RUN\] 21:48:04 8 of 28 OK created sql table model MARK\_DBT.sales\_cost ......................... \[OK in 1.64s\] 21:48:04 9 of 28 START sql table model MARK\_DBT.sales\_internet\_channel .................. \[RUN\] 21:48:06 9 of 28 OK created sql table model MARK\_DBT.sales\_internet\_channel ............. \[OK in 2.20s\] 21:48:06 10 of 28 START sql table model MARK\_DBT.union\_customer\_sales ................... \[RUN\] 21:48:12 10 of 28 OK created sql table model MARK\_DBT.union\_customer\_sales .............. \[OK in 6.14s\] 21:48:12 11 of 28 START sql incremental model MARK\_DBT.us\_product\_sales\_channel\_ranking . \[RUN\] 21:48:14 11 of 28 OK created sql incremental model MARK\_DBT.us\_product\_sales\_channel\_ranking \[OK in 2.20s\] 21:48:14 12 of 28 START sql incremental model MARK\_DBT.us\_product\_sales\_channel\_ranking\_append \[RUN\] 21:48:17 12 of 28 OK created sql incremental model MARK\_DBT.us\_product\_sales\_channel\_ranking\_append \[OK in 2.62s\] 21:48:17 13 of 28 START seed file MARK\_DBT.seed ......................................... \[RUN\] 21:48:18 13 of 28 OK loaded seed file MARK\_DBT.seed ..................................... \[INSERT 5 in 0.96s\] 21:48:18 14 of 28 START test dbt\_constraints\_primary\_key\_countries\_country\_id ........... \[RUN\] 21:48:19 14 of 28 PASS dbt\_constraints\_primary\_key\_countries\_country\_id ................. \[PASS in 1.50s\] 21:48:19 15 of 28 START test not\_null\_countries\_country\_id .............................. \[RUN\] 21:48:21 15 of 28 PASS not\_null\_countries\_country\_id .................................... \[PASS in 1.14s\] 21:48:21 16 of 28 START test unique\_countries\_country\_id ................................ \[RUN\] 21:48:21 16 of 28 PASS unique\_countries\_country\_id ...................................... \[PASS in 0.62s\] 21:48:21 17 of 28 START sql view model MARK\_DBT.us\_seed\_customers ....................... \[RUN\] 21:48:22 17 of 28 OK created sql view model MARK\_DBT.us\_seed\_customers .................. \[OK in 0.90s\] 21:48:22 18 of 28 START snapshot MARK\_DBT.promotion\_costs\_snapshot ...................... \[RUN\] 21:48:24 18 of 28 OK snapshotted MARK\_DBT.promotion\_costs\_snapshot ...................... \[success in 2.28s\] 21:48:24 19 of 28 START sql table model MARK\_DBT.internet\_sales\_channel\_customers ....... \[RUN\] 21:48:29 19 of 28 OK created sql table model MARK\_DBT.internet\_sales\_channel\_customers .. \[OK in 4.41s\] 21:48:29 20 of 28 START sql table model MARK\_DBT.people ................................. \[RUN\] 21:48:32 20 of 28 OK created sql table model MARK\_DBT.people ............................ \[OK in 2.86s\] 21:48:32 21 of 28 START sql table model MARK\_DBT.eu\_direct\_sales\_channels\_promo\_costs ... \[RUN\] 21:48:34 21 of 28 OK created sql table model MARK\_DBT.eu\_direct\_sales\_channels\_promo\_costs \[OK in 1.92s\] 21:48:34 22 of 28 START test accepted\_values\_people\_gender\_\_Male\_\_Female ................ \[RUN\] 21:48:35 22 of 28 PASS accepted\_values\_people\_gender\_\_Male\_\_Female ...................... \[PASS in 1.13s\] 21:48:35 23 of 28 START test dbt\_constraints\_primary\_key\_people\_id ...................... \[RUN\] 21:48:36 23 of 28 PASS dbt\_constraints\_primary\_key\_people\_id ............................ \[PASS in 0.85s\] 21:48:36 24 of 28 START test not\_null\_people\_id ......................................... \[RUN\] 21:48:36 24 of 28 PASS not\_null\_people\_id ............................................... \[PASS in 0.74s\] 21:48:36 25 of 28 START test test\_count\_employees ....................................... \[RUN\] 21:48:37 25 of 28 PASS test\_count\_employees ............................................. \[PASS in 0.96s\] 21:48:37 26 of 28 START test unique\_people\_id ........................................... \[RUN\] 21:48:38 26 of 28 PASS unique\_people\_id ................................................. \[PASS in 1.05s\] 21:48:38 27 of 28 START test dbt\_constraints\_foreign\_key\_eu\_direct\_sales\_channels\_promo\_costs\_country\_id\_\_country\_id\_\_ref\_countries\_ \[RUN\] 21:48:39 27 of 28 PASS dbt\_constraints\_foreign\_key\_eu\_direct\_sales\_channels\_promo\_costs\_country\_id\_\_country\_id\_\_ref\_countries\_ \[PASS in 0.93s\] 21:48:39 28 of 28 START test relationships\_eu\_direct\_sales\_channels\_promo\_costs\_country\_id\_\_country\_id\_\_ref\_countries\_ \[RUN\] 21:48:40 28 of 28 PASS relationships\_eu\_direct\_sales\_channels\_promo\_costs\_country\_id\_\_country\_id\_\_ref\_countries\_ \[PASS in 0.75s\] 21:48:41 21:48:41 Running 2 on-run-end hooks 21:48:41 1 of 2 START hook: dbt\_adbs\_test\_project.on-run-end.0 .......................... \[RUN\] 21:48:41 1 of 2 OK hook: dbt\_adbs\_test\_project.on-run-end.0 ............................. \[OK in 0.03s\] 21:48:41 Running dbt Constraints 21:48:45 Creating primary key: COUNTRIES\_COUNTRY\_ID\_PK 21:48:49 Creating primary key: PEOPLE\_ID\_PK 21:48:53 Creating foreign key: FK\_2419558567 referencing countries \['country\_id'\] 21:48:54 Finished dbt Constraints 21:48:54 2 of 2 START hook: dbt\_constraints.on-run-end.0 ................................ \[RUN\] 21:48:54 2 of 2 OK hook: dbt\_constraints.on-run-end.0 ................................... \[OK in 0.00s\] 21:48:54 21:48:54 21:48:54 Finished running 9 table models, 2 view models, 5 incremental models, 1 seed, 10 tests, 1 snapshot, 3 hooks in 0 hours 1 minutes and 7.14 seconds (67.14s). 21:48:54 21:48:54 Completed successfully 21:48:54 21:48:54 Done. PASS=28 WARN=0 ERROR=0 SKIP=0 TOTAL=28 dbt packages typically include descriptions for fields, schema definitions and other metadata that, together with the DAG dependency graph defined by all of the ref() and source() definitions within your models can be served-up from the command line using a built-in webserver, like this: (dbt-oracle-venv) markrittman@marks-imac-4 dbt\_adbs\_test\_project % dbt docs generate 21:54:57 Running with dbt=1.5.3 21:54:57 oracle adapter: Running in thin mode 21:54:57 Registered adapter: oracle=1.5.3 21:54:57 Found 17 models, 10 tests, 1 snapshot, 1 analysis, 637 macros, 3 operations, 1 seed file, 8 sources, 2 exposures, 0 metrics, 0 groups 21:54:57 21:55:00 Concurrency: 1 threads (target='dev') 21:55:00 21:55:03 Running dbt Constraints 21:55:03 Finished dbt Constraints 21:55:04 Building catalog 21:56:12 Catalog written to /Users/markrittman/prod/dbt-oracle/dbt\_adbs\_test\_project/target/catalog.json (dbt-oracle-venv) markrittman@marks-imac-4 dbt\_adbs\_test\_project % dbt docs serve 22:06:41 Running with dbt=1.5.3 22:06:41 oracle adapter: Running in thin mode Serving docs at 8080 To access from your browser, navigate to: http://localhost:8080 Press Ctrl+C to exit. 127.0.0.1 - - \[06/Sep/2023 23:06:41\] "GET / HTTP/1.1" 200 - 127.0.0.1 - - \[06/Sep/2023 23:06:42\] "GET /manifest.json?cb=1694038002092 HTTP/1.1" 200 - 127.0.0.1 - - \[06/Sep/2023 23:06:42\] "GET /catalog.json?cb=1694038002092 HTTP/1.1" 200 - and then visualized as a data dictionary portal and transformation dependency graph, like this: ![](https://rittmananalytics.com/api/public/og-image?src=https%3A%2F%2Fi0.wp.com%2Fnfqvbiqopvwmhnrrabjr.supabase.co%2Fstorage%2Fv1%2Fobject%2Fpublic%2Fblog-images%2Fimported-images%2F1752872081826-s3qr4.jpg%3Fresize%3D1400%252C966%26ssl%3D1) #### Interested? Find Out More! Rittman Analytics is a boutique data analytics consultancy that’s both an [Oracle Partner](https://www.rittmananalytics.com/partners/oracle) with over 25 years experience working with Oracle technology and Oracle customers, and a [long standing partner](https://partners.getdbt.com/english/directory/partner/1540716/rittman-analytics) of [dbt Labs](https://www.rittmananalytics.com/partners/dbt-labs), the software company that sponsors dbt Core development. You can read more about our work with dbt [on our blog](https://blog.rittmananalytics.com/tagged/dbt), see examples of our dbt development approach in [our Github repo](https://github.com/rittmananalytics/ra_data_warehouse) and listen to episodes of our podcast, Drill to Detail, where [we discuss dbt and the analytics engineering approach](https://www.rittmananalytics.com/drilltodetail/2018/10/2/drill-to-detail-ep59-looker-dbt-and-digital-analytics-today-with-special-guests-tristan-handy-and-stewart-bryson) with one of dbt’s original authors, Tristan Handy. If you’re looking for some help and assistance adopting dbt and the analytics engineering approach to data warehouse development, or to help build-out your analytics capabilities and data team using a modern, flexible and modular data stack, [contact us now](https://calendly.com/markrittman/initial-discovery-call-with-mark-rittman?utm_source=medium.com&utm_medium=blog&utm_campaign=customer_segmentation) to organise a 100%-free, no-obligation call — we’d love to hear from you! [dbt](https://rittmananalytics.com/blog/tag/dbt)[Oracle](https://rittmananalytics.com/blog/tag/Oracle)[Oracle Cloud](https://rittmananalytics.com/blog/tag/Oracle+Cloud) [Mark Rittman](https://rittmananalytics.com/blog?author=5846da08f5e231d83261e221) CEO of Rittman Analytics, host of the Drill to Detail Podcast, ex-product manager and twice company founder. [https://rittmananalytics.com](https://rittmananalytics.com/)