Welcome!
Welcome to the auto-generated documentation for your dbt project!
Navigation
You can use the Project and Database navigation tabs on the left side of the window to explore the models
in your project.
Project Tab
The Project tab mirrors the directory structure of your dbt project. In this tab, you can see all of the
models defined in your dbt project, as well as models imported from dbt packages.
Database Tab
The Database tab also exposes your models, but in a format that looks more like a database explorer. This view
shows relations (tables and views) grouped into database schemas. Note that ephemeral models are not shown
in this interface, as they do not exist in the database.
Graph Exploration
You can click the blue icon on the bottom-right corner of the page to view the lineage graph of your models.
On model pages, you'll see the immediate parents and children of the model you're exploring. By clicking the Expand
button at the top-right of this lineage pane, you'll be able to see all of the models that are used to build,
or are built from, the model you're exploring.
Once expanded, you'll be able to use the --select and --exclude model selection syntax to filter the
models in the graph. For more information on model selection, check out the dbt docs.
Note that you can also right-click on models to interactively filter and explore the graph.
More information
- What is dbt?
- Read the dbt viewpoint
- Installation
- Join the dbt Community for questions and discussion
561 search results
sat_order model
{%- set yaml_metadata -%}
source_model: stg_order
src_pk: HKEY
src_hashdiff: HASHDIFF
src_payload:
- ID
- USER_ID
- ORDER_DATE
- STATUS
src_ldts: LOAD_DATETIME
src_source: RECORD_SOURCE
{%- endset -%}
{% set metadata_dict = fromyaml(yaml_metadata) %}
{{ dbtvault.sat(src_pk=metadata_dict["src_pk"],
src_hashdiff=metadata_dict["src_hashdiff"],
src_payload=metadata_dict["src_payload"],
src_ldts=metadata_dict["src_ldts"],
src_source=metadata_dict["src_source"],
source_model=metadata_dict["source_model"]) }}
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_jaffle_shop,
sample_jaffle_shop
it_sample_not_dv model
integration test for NON DV MODEL
columns:
NOT_DV_HKEY,
NOT_DV_HKEY
Show -2 more
select 1 as not_dv_hkey union all
select 2 as not_dv_hkey union all
select 3 as not_dv_hkey union all
select 4 as not_dv_hkey union all
select 1 as not_dv_hkey
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_custom,
sample_custom
test_get_datavault_type model
select 'default' as test_case,
'{{ dq_vault.get_datavault_type() | escape }}' as actual,
'{{
"case
when
/* type: hub */
(
lower(node__refs) like concat('%','hub','%')
)
then 'hub'
when
/* type: sat */
(
lower(node__refs) like concat('%','sat','%') or
lower(node__refs) like concat('%','satellite','%')
)
then 'sat'
when
/* type: link */
(
lower(node__refs) like concat('%','link','%') or
lower(node__refs) like concat('%','tlink','%')or
lower(node__refs) like concat('%','t_link','%')or
lower(node__refs) like concat('%','lnk','%')or
lower(node__refs) like concat('%','tlnk','%')or
lower(node__refs) like concat('%','t_lnk','%')
)
then 'link'
when
/* type: pit */
(
lower(node__refs) like concat('%','pit','%')
)
then 'pit'
when
/* type: bridge */
(
lower(node__refs) like concat('%','bridge','%')
)
then 'bridge'
when
/* type: xts */
(
lower(node__refs) like concat('%','xts','%')
)
then 'xts'
else 'unknown'
end" | escape }}' as expected
union all
select 'key_column="my_column"' as test_case,
'{{ dq_vault.get_datavault_type(key_column="my_column") | escape }}' as actual,
'{{
"case
when
/* type: hub */
(
lower(my_column) like concat('%','hub','%')
)
then 'hub'
when
/* type: sat */
(
lower(my_column) like concat('%','sat','%') or
lower(my_column) like concat('%','satellite','%')
)
then 'sat'
when
/* type: link */
(
lower(my_column) like concat('%','link','%') or
lower(my_column) like concat('%','tlink','%')or
lower(my_column) like concat('%','t_link','%')or
lower(my_column) like concat('%','lnk','%')or
lower(my_column) like concat('%','tlnk','%')or
lower(my_column) like concat('%','t_lnk','%')
)
then 'link'
when
/* type: pit */
(
lower(my_column) like concat('%','pit','%')
)
then 'pit'
when
/* type: bridge */
(
lower(my_column) like concat('%','bridge','%')
)
then 'bridge'
when
/* type: xts */
(
lower(my_column) like concat('%','xts','%')
)
then 'xts'
else 'unknown'
end" | escape }}' as expected
stg_customer model
{%- set yaml_metadata -%}
source_model: 'raw_customers'
hashed_columns:
HKEY:
- ID
HASHDIFF:
is_hashdiff: true
columns:
- ID
- FIRST_NAME
- LAST_NAME
{%- endset -%}
{% set metadata_dict = fromyaml(yaml_metadata) %}
{% set source_model = metadata_dict['source_model'] %}
{% set derived_columns = metadata_dict['derived_columns'] %}
{% set hashed_columns = metadata_dict['hashed_columns'] %}
with stage as (
{{ dbtvault.stage(include_source_columns=true,
source_model=source_model,
hashed_columns=hashed_columns,
derived_columns=derived_columns) }}
)
select *
,{{ dbt_utils.current_timestamp() }} as load_datetime
,'jaffle_shop' as record_source
from stage
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_jaffle_shop,
sample_jaffle_shop
link_order_customer model
columns:
HKEY,
HKEY
columns:
CUSTOMER_HKEY,
CUSTOMER_HKEY
columns:
LOAD_DATETIME,
LOAD_DATETIME
Show 1 more
{%- set yaml_metadata -%}
source_model: stg_order
src_pk: HKEY
src_fk: CUSTOMER_HKEY
src_ldts: LOAD_DATETIME
src_source: RECORD_SOURCE
{%- endset -%}
{% set metadata_dict = fromyaml(yaml_metadata) %}
{{ dbtvault.link(src_pk=metadata_dict["src_pk"],
src_fk=metadata_dict["src_fk"],
src_ldts=metadata_dict["src_ldts"],
src_source=metadata_dict["src_source"],
source_model=metadata_dict["source_model"]) }}
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_jaffle_shop,
sample_jaffle_shop
metric_test_status_of_each_run model
columns:
DATE_DAY,
DATE_DAY
columns:
RUN_ID,
RUN_ID
columns:
RUN_EST_EXECUTED_AT,
RUN_EST_EXECUTED_AT
Show 4 more
select *
from {{ metrics.calculate(
metric('test_status_of_each_run'),
grain='day',
dimensions=['run_id', 'run_est_executed_at', 'datavault_type', 'datavault_test_type', 'test_status'],
where="test_status_of_each_run != 0"
) }}
fact_dv_tests model
fact_dv_tests
columns:
TEST_ID,
TEST_ID
columns:
TEST_NAME,
TEST_NAME
columns:
DATAVAULT_TYPE,
DATAVAULT_TYPE
Show 23 more
select
dim_dv_tests.* exclude(valid_from, valid_to, valid_flag),
dim_dv_test_executions.* exclude(test_id),
min(test_executed_at) over (partition by run_id) as run_est_executed_at
from {{ ref('dim_dv_tests') }}
inner join {{ ref('dim_dv_test_executions') }}
on dim_dv_tests.test_id = dim_dv_test_executions.test_id
and dim_dv_test_executions.test_executed_at >= dim_dv_tests.valid_from
and dim_dv_test_executions.test_executed_at < dim_dv_tests.valid_to
metric_average_test_execution_time model
columns:
DATE_DAY,
DATE_DAY
columns:
TEST_ID,
TEST_ID
columns:
AVERAGE_TEST_EXECUTION_TIME,
AVERAGE_TEST_EXECUTION_TIME
Show 0 more
select *
from {{ metrics.calculate(
metric('average_test_execution_time'),
grain='day',
dimensions=['test_id']
) }}
stg_test model
Columnized test result based on raw json data
columns:
PROJECT_NAME,
PROJECT_NAME
columns:
INVOCATION_ID,
INVOCATION_ID
columns:
STATUS,
STATUS
Show 64 more
with source as (
select *
,parse_json(content_json) as content
from {{ source('run_result_log', 'raw_test') }}
),
casting as (
select cast(content:project_name as {{ type_string() }}) as project_name
,cast(content:invocation_id as {{ type_string() }}) as invocation_id
,cast(content:status as {{ type_string() }}) as status
,cast(content:timing__compile__started_at as {{ type_timestamp() }}) as timing__compile__started_at
,cast(content:timing__compile__completed_at as {{ type_timestamp() }}) as timing__compile__completed_at
,cast(content:timing__execute__started_at as {{ type_timestamp() }}) as timing__execute__started_at
,cast(content:timing__execute__completed_at as {{ type_timestamp() }}) as timing__execute__completed_at
,cast(content:thread_id as {{ type_string() }}) as thread_id
,cast(content:execution_time as {{ type_numeric() }}) as execution_time
,cast(content:adapter_response as {{ type_string() }}) as adapter_response
,cast(content:message as {{ type_string() }}) as message
,cast(content:failures as {{ type_int() }}) as failures
,cast(content:node__raw_sql as {{ type_string() }}) as node__raw_sql
,cast(content:node__test_metadata__name as {{ type_string() }}) as node__test_metadata__name
,cast(content:node__test_metadata__kwargs as {{ type_string() }}) as node__test_metadata__kwargs
,cast(content:node__test_metadata__namespace as {{ type_string() }}) as node__test_metadata__namespace
,cast(content:node__compiled as {{ type_string() }}) as node__compiled
,cast(content:node__database as {{ type_string() }}) as node__database
,cast(content:node__schema as {{ type_string() }}) as node__schema
,cast(content:node__fqn as {{ type_string() }}) as node__fqn
,cast(content:node__unique_id as {{ type_string() }}) as node__unique_id
,cast(content:node__package_name as {{ type_string() }}) as node__package_name
,cast(content:node__root_path as {{ type_string() }}) as node__root_path
,cast(content:node__path as {{ type_string() }}) as node__path
,cast(content:node__original_file_path as {{ type_string() }}) as node__original_file_path
,cast(content:node__name as {{ type_string() }}) as node__name
,cast(content:node__resource_type as {{ type_string() }}) as node__resource_type
,cast(content:node__config__enabled as {{ type_string() }}) as node__config__enabled
,cast(content:node__config__alias as {{ type_string() }}) as node__config__alias
,cast(content:node__config__schema as {{ type_string() }}) as node__config__schema
,cast(content:node__config__database as {{ type_string() }}) as node__config__database
,cast(content:node__config__tags as {{ type_string() }}) as node__config__tags
,cast(content:node__config__meta as {{ type_string() }}) as node__config__meta
,cast(content:node__config__materialized as {{ type_string() }}) as node__config__materialized
,upper(cast(content:node__config__severity as {{ type_string() }})) as node__config__severity
,cast(content:node__config__store_failures as {{ type_string() }}) as node__config__store_failures
,cast(content:node__config__where as {{ type_string() }}) as node__config__where
,cast(content:node__config__limit as {{ type_string() }}) as node__config__limit
,cast(content:node__config__fail_calc as {{ type_string() }}) as node__config__fail_calc
,cast(content:node__config__warn_if as {{ type_string() }}) as node__config__warn_if
,cast(content:node__config__error_if as {{ type_string() }}) as node__config__error_if
,cast(content:node__tags as {{ type_string() }}) as node__tags
-- ,cast(content:node__refs as {{ type_string() }}) as node__refs
,content:node__refs as node__refs -- 2 level array contain ref models
,cast(content:node__sources as {{ type_string() }}) as node__sources
,cast(content:node__metrics as {{ type_string() }}) as node__metrics
,cast(content:node__depends_on__macros as {{ type_string() }}) as node__depends_on__macros
,cast(content:node__depends_on__nodes as {{ type_string() }}) as node__depends_on__nodes
,cast(content:node__description as {{ type_string() }}) as node__description
,cast(content:node__columns as {{ type_string() }}) as node__columns
,cast(content:node__meta as {{ type_string() }}) as node__meta
,cast(content:node__docs__show as {{ type_string() }}) as node__docs__show
,cast(content:node__patch_path as {{ type_string() }}) as node__patch_path
,cast(content:node__compiled_path as {{ type_string() }}) as node__compiled_path
,cast(content:node__build_path as {{ type_string() }}) as node__build_path
,cast(content:node__deferred as {{ type_string() }}) as node__deferred
,cast(content:node__unrendered_config as {{ type_string() }}) as node__unrendered_config
,cast(content:node__created_at as {{ type_timestamp() }}) as node__created_at
,cast(content:node__config_call_dict as {{ type_string() }}) as node__config_call_dict
,cast(content:node__extra_ctes_injected as {{ type_string() }}) as node__extra_ctes_injected
,cast(content:node__extra_ctes as {{ type_string() }}) as node__extra_ctes
,cast(content:node__relation_name as {{ type_string() }}) as node__relation_name
,cast(content:node__column_name as {{ type_string() }}) as node__column_name
,cast(content:node__file_key_name as {{ type_string() }}) as node__file_key_name
,created_timestamp
from source
),
final as (
select *
,row_number() over (
partition by node__unique_id, {{ date_trunc("day", "created_timestamp") }}
order by created_timestamp desc
) as seq
,{{ dq_vault.get_test_type() }} as datavault_test_type
,{{ dq_vault.get_datavault_type() }} as datavault_type
from casting
)
select *
from final
fact_dv_tests_unnest_models model
fact_dv_tests_unnest_models
columns:
TEST_ID,
TEST_ID
columns:
TEST_NAME,
TEST_NAME
columns:
DATAVAULT_TYPE,
DATAVAULT_TYPE
Show 24 more
select
f.*,
f2.value::string as test_ref_model
from {{ ref('fact_dv_tests') }} f,
lateral flatten(input => f.test_ref_models) f1,
lateral flatten(input => f1.value) f2 -- unnesting ref models array
stg_order model
{%- set yaml_metadata -%}
source_model: 'raw_orders'
hashed_columns:
HKEY:
- ID
CUSTOMER_HKEY:
- USER_ID
HASHDIFF:
is_hashdiff: true
columns:
- USER_ID
- ORDER_DATE
- STATUS
{%- endset -%}
{% set metadata_dict = fromyaml(yaml_metadata) %}
{% set source_model = metadata_dict['source_model'] %}
{% set derived_columns = metadata_dict['derived_columns'] %}
{% set hashed_columns = metadata_dict['hashed_columns'] %}
with stage as (
{{ dbtvault.stage(include_source_columns=true,
source_model=source_model,
hashed_columns=hashed_columns,
derived_columns=derived_columns) }}
)
select *
,{{ dbt_utils.current_timestamp() }} as load_datetime
,'jaffle_shop' as record_source
from stage
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_jaffle_shop,
sample_jaffle_shop
raw_customers seed
dim_dv_tests model
tests metadata
columns:
TEST_ID,
TEST_ID
columns:
TEST_NAME,
TEST_NAME
columns:
DATAVAULT_TYPE,
DATAVAULT_TYPE
Show 18 more
with
tests_metadata as (
select
-- PKs
test_executed_at,
test_id,
--
test_name,
datavault_type,
datavault_test_type,
project_name,
test_database,
test_schema,
test_column_name,
test_severity,
test_config_tags,
test_tags,
test_ref_models, -- arrays of ref models
test_sources,
test_metrics,
test_description,
test_meta,
cast(md5_binary(concat_ws('||',
ifnull(nullif(upper(trim(cast(test_id as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_name as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(datavault_type as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(datavault_test_type as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(project_name as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_database as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_schema as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_column_name as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_severity as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_config_tags as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_tags as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_ref_models as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_sources as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_metrics as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_description as varchar))), ''), '^^'),
ifnull(nullif(upper(trim(cast(test_meta as varchar))), ''), '^^')
)) as binary(16)) as test_hashdiff
from {{ ref('stg_test_dv') }}
),
calculate_preceeding_test_run_time as (
select
*,
lag(test_hashdiff) ignore nulls over (
partition by test_id order by test_executed_at) as preceeding_test_hashdiff
from tests_metadata
),
calculate_starting_timestamp_for_each_test_hashdiff as (
select
*
from calculate_preceeding_test_run_time
where true
-- get the starting test_executed_at for each period of preceeding_test_hashdiff of a test_id
and (test_hashdiff != preceeding_test_hashdiff or preceeding_test_hashdiff is null)
),
source as (
select
* exclude (test_executed_at),
test_executed_at as valid_from,
coalesce (lead (test_executed_at) over (partition by test_id order by test_executed_at), to_timestamp('9999-12-31')) as valid_to
from calculate_starting_timestamp_for_each_test_hashdiff
)
select
*,
case when valid_to = to_timestamp('9999-12-31') then 'Y' else 'N' end as valid_flag
from source
dim_dv_test_executions model
info on test executions
select distinct
-- PKs
run_id,
test_id,
--
test_status,
test_message,
failures_count,
test_executed_at,
test_completed_at,
test_execution_time
from {{ ref('stg_test_dv') }}
stg_test_dv model
columns:
PROJECT_NAME,
PROJECT_NAME
columns:
RUN_ID,
RUN_ID
columns:
TEST_STATUS,
TEST_STATUS
Show 21 more
select
project_name,
invocation_id as run_id,
status as test_status,
{# timing__compile__started_at, #}
{# timing__compile__completed_at, #}
timing__execute__started_at as test_executed_at,
timing__execute__completed_at as test_completed_at,
{# thread_id, #}
execution_time as test_execution_time,
{# adapter_response, #}
message as test_message,
failures as failures_count,
{# node__raw_sql, #}
{# node__test_metadata__name, #}
{# node__test_metadata__kwargs, #}
{# node__test_metadata__namespace, #}
{# node__compiled, #}
node__database as test_database,
node__schema as test_schema,
{# node__fqn, #}
node__unique_id as test_id,
{# node__package_name, #}
{# node__root_path, #}
{# node__path, #}
{# node__original_file_path, #}
node__name as test_name,
{# node__resource_type, #}
{# node__config__enabled, #}
{# node__config__alias, #}
{# node__config__schema, #}
{# node__config__database, #}
node__config__tags as test_config_tags,
{# node__config__meta, #}
{# node__config__materialized, #}
node__config__severity as test_severity,
{# node__config__store_failures, #}
{# node__config__where, #}
{# node__config__limit, #}
{# node__config__fail_calc, #}
{# node__config__warn_if, #}
{# node__config__error_if, #}
node__tags as test_tags,
node__refs as test_ref_models,
node__sources as test_sources,
node__metrics as test_metrics,
{# node__depends_on__macros, #}
{# node__depends_on__nodes, #}
node__description as test_description,
{# node__columns, #}
node__meta as test_meta,
{# node__docs__show, #}
{# node__patch_path, #}
{# node__compiled_path, #}
{# node__build_path, #}
{# node__deferred, #}
{# node__unrendered_config, #}
{# node__created_at as test_created_at, #}
{# node__config_call_dict, #}
{# node__extra_ctes_injected, #}
{# node__extra_ctes, #}
{# node__relation_name, #}
node__column_name as test_column_name,
{# node__file_key_name, #}
{# created_timestamp, #}
seq,
datavault_test_type,
datavault_type
from {{ ref('stg_test') }}
where {{ where_select_dv_models() }}
test_get_raw_test model
{%- set raw_test_relation -%}
{{- var('dq_vault__raw_db', target.database) -}}.
{{- var('dq_vault__raw_schema', target.schema) -}}.
{{- 'raw_test' -}}
{%- endset %}
select 'default' as test_case,
'{{ dq_vault.get_raw_test() }}' as actual,
'{{ raw_test_relation }}' as expected, 1 as exact
union all
select 'create_sql=true' as test_case,
'{{ dq_vault.get_raw_test(create_sql=true) }}' as actual,
'create table if not exists {{ raw_test_relation }}' as expected, 0 as exact
union all
select 'create_sql=true, full_refresh=true' as test_case,
'{{ dq_vault.get_raw_test(create_sql=true, full_refresh=true) }}' as actual,
'create or replace table {{ raw_test_relation }}' as expected, 0 as exact
dbt_metrics_default_calendar model
columns:
DATE_DAY,
DATE_DAY
columns:
DATE_WEEK,
DATE_WEEK
columns:
DATE_MONTH,
DATE_MONTH
Show 2 more
{{ config(materialized='table') }}
with days as (
{{ metrics.metric_date_spine(
datepart="day",
start_date="cast('1990-01-01' as date)",
end_date="cast('2030-01-01' as date)"
)
}}
),
final as (
select
cast(date_day as date) as date_day,
cast({{ date_trunc('week', 'date_day') }} as date) as date_week,
cast({{ date_trunc('month', 'date_day') }} as date) as date_month,
cast({{ date_trunc('quarter', 'date_day') }} as date) as date_quarter,
cast({{ date_trunc('year', 'date_day') }} as date) as date_year
from days
)
select * from final
metric_no_of_test_of_each_model model
columns:
DATE_DAY,
DATE_DAY
columns:
TEST_REF_MODEL,
TEST_REF_MODEL
columns:
DATAVAULT_TYPE,
DATAVAULT_TYPE
Show 1 more
select *
from {{ metrics.calculate(
metric('no_of_test_of_each_model'),
grain='day',
dimensions=['test_ref_model', 'datavault_type']
) }}
hub_customer model
{%- set yaml_metadata -%}
source_model:
- stg_customer
src_pk: HKEY
src_nk: ID
src_ldts: LOAD_DATETIME
src_source: RECORD_SOURCE
{%- endset -%}
{% set metadata_dict = fromyaml(yaml_metadata) %}
{{ dbtvault.hub(src_pk=metadata_dict["src_pk"],
src_nk=metadata_dict["src_nk"],
src_ldts=metadata_dict["src_ldts"],
src_source=metadata_dict["src_source"],
source_model=metadata_dict["source_model"]) }}
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_jaffle_shop,
sample_jaffle_shop
it_sample_hub model
integration test for DV HUB
columns:
HUB_HKEY,
HUB_HKEY
Show -2 more
select 1 as hub_hkey union all
select null as hub_hkey union all
select 3 as hub_hkey union all
select 4 as hub_hkey union all
select 1 as hub_hkey
tags:
it_sample_datavault,
it_sample_datavault
tags:
sample_custom,
sample_custom