dbt and BigQuery in Practice; A Use-Case of Transforming Stock Data

Introduction

Modeling

data pipeline
Source Stock Data

Introducing dbt and BigQuery

Python: 3.9.11
dbt-core: 1.1.0
dbt-bigquery: 1.1.0
dbt init
name: 'invest_analytics'
version: '1.0.0'
config-version: 2
~~~models:
invest_analytics:
invest_analytics_dev:
+materialized: view
warehouse-date:
warehouse-stock:
warehouse-portfolio:
mart-portfolio-value:
+materialized: table
mart-portfolio-ratio:
+materialized: table
invest_analytics:outputs:dev:
dataset: invest_analytics_dev
job_execution_timeout_seconds: 300
job_retries: 1
keyfile: ../service_account.json
location: asia-northeast1
method: service-account
priority: interactive
project: invest-analytics-347211
threads: 1
type: bigquery
target: dev
select
Date
, cast(close_voo as integer) as close_voo
, cast(close_btcusd as integer) as close_btcusd
, cast(close_bnd as integer) as close_bnd
, cast((close_voo * pf.num_voo + close_btcusd * pf.num_btcusd + c close_bnd * pf.num_bnd) as integer) as close_total
from
{{ ref('warehouse-stock') }} as st
left outer join {{ ref('warehouse-portfolio') }} as pf
on 1 = 1
order by
Date
select
max(case ticker when 'VOO' then num_of_hold else null end) as num_voo
, max(case ticker when 'BTC-USD' then num_of_hold else null end) as num_btcusd
, max(case ticker when 'BND' then num_of_hold else null end) as num_bnd
from
{{ source('invest_analytics_dev', 'source-portfolio') }}
version: 2
sources:
- name: invest_analytics_dev
tables:
- name: source-voo
- name: source-btcusd
- name: source-bnd
- name: source-portfolio
models:
- name: mart-portfolio-ratio
description: ''
columns:
- name: ticker
description: ''
tests:
- unique
- not_null
- accepted_values:
values: ['voo', 'btcusd', 'bnd']
- name: close_percent
description: ''
tests:
- unique
- not_null
dbt run — full-refresh — profiles-dir .
12:11:02  Running with dbt=1.1.0
12:11:02 Unable to do partial parsing because a project config has changed
12:11:03 Found 5 models, 17 tests, 0 snapshots, 0 analyses, 191 macros, 0 operations, 0 seed files, 4 sources, 0 exposures, 0 metrics
12:11:04 Concurrency: 1 threads (target='dev')
12:11:04 1 of 5 START view model invest_analytics_dev.warehouse-date .................... [RUN]
12:11:06 1 of 5 OK created view model invest_analytics_dev.warehouse-date ............... [OK in 1.61s]
~~~12:11:11 5 of 5 START table model invest_analytics_dev.mart-portfolio-ratio ............. [RUN]
12:11:14 5 of 5 OK created table model invest_analytics_dev.mart-portfolio-ratio ........ [CREATE TABLE (3.0 rows, 62.7 KB processed) in 3.27s]
12:11:14 Finished running 3 view models, 2 table models in 11.36s.
12:11:14 Completed successfully
12:11:14 Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5
bigquery console
dbt test — profiles-dir .
10:04:03  Running with dbt=1.1.0
10:04:03 Found 5 models, 17 tests, 0 snapshots, 0 analyses, 191 macros, 0 operations, 0 seed files, 4 sources, 0 exposures, 0 metrics
10:04:04 Concurrency: 1 threads (target='dev')
10:04:04 1 of 17 START test accepted_values_mart-portfolio-ratio_ticker__voo__btcusd__bnd [RUN]
10:04:06 1 of 17 PASS accepted_values_mart-portfolio-ratio_ticker__voo__btcusd__bnd ..... [PASS in 2.16s]
~~~10:04:31 17 of 17 START test unique_warehouse-stock_Date ................................ [RUN]
10:04:32 17 of 17 PASS unique_warehouse-stock_Date ...................................... [PASS in 1.33s]
10:04:32 Finished running 17 tests in 28.88s.
10:04:32 Completed successfully
10:04:32 Done. PASS=17 WARN=0 ERROR=0 SKIP=0 TOTAL=17
dbt docs generate — profiles-dir .
dbt docs serve — profiles-dir .
table definition
lineage

Conclusion

--

--

--

Data Scientist in Japan. https://www.linkedin.com/in/ryo-koyajima/

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How do you write “before” and “after” in CSS?

Crossing boundaries: Reflections by a former academic developer

Launch Date Delay Notice

Streaming Vitess at Bolt

8 Best Linux Courses for Developers, Cloud Engineers, and DevOps in 2022

8 Best Linux Courses for Programmers and DevOps Engineers

Top common Memory leaks using C#

Take Ownership of Your Work: Adopt InnerSource

Step by Step build a Kotlin Multiplatform Library (SDK) — Part 2

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ryo Koyajima / 小矢島 諒

Ryo Koyajima / 小矢島 諒

Data Scientist in Japan. https://www.linkedin.com/in/ryo-koyajima/

More from Medium

Using the Split Function in BigQuery

Raw Google Analytics 4 (GA4) Data in BigQuery (BQ)

Google Analytics and BigQuery: setting up your project

How to use Google Spreadsheet as a database using Python