Lineage Graph

All selected

• Select All
• Models
• Seeds
• Snapshots
• Sources
• Tests
• Analyses
• Exposures
• Metrics
• Semantic_models

Data Direct

Access Canopy data through direct database connection

DataDirect is the recommended method for analytical consumption of your data, meaning it is best suited for reports and business decisions as opposed to using the data for operational business processes. It is a read-only PostgreSQL database connection that allows you to query your data in bulk in near real-time.

📘 Who would benefit the most from DataDirect?

• Data analysts and engineers
• Credit risk and Business Intelligence teams

Common Use Cases

Loan Tapes

Export a snapshot of your customer base and loans. By connecting DataDirect to your data warehouse, automate reports to your capital provider. Relevant data from Canopy include all customers, associated products, outstanding balances and deadlines etc.

Loan Portfolio Dashboards

Overview your portfolio performance at a glance. By connecting DataDirect to your BI solution, see a high-level summary of your loan portfolio. This could include the total number of active loans, the total loan amount outstanding, and any important performance indicators such as delinquency rates or default rates.

Ad-hoc SQL Queries

Discover new business insights. By accessing DataDirect with your database app of choice (e.g. Postico), execute any SQL query to uncover new patterns in your data. The data can help you answer questions such as:

1. Which customers are paying in full each month?
2. What strategies produce the highest repayment rates?
3. Where is my highest cost of servicing coming from?

How to navigate

You can use the Database navigation tabs on the left side of the window to explore the tables.

The Database tab also exposes 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.

cardsmodel

A table of all Cards and related information in the system.

columns:card_id,card_idcolumns:external_card_id,external_card_idcolumns:account_id,account_idShow 9 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("cards_table") }}',
post_hook = [
'{{ drop_fdw("cards_table") }}',
'{{ create_pk(this, "card_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
cards_table
)
SELECT
*
FROM
source_data

adjustment_amountsmodel

A table of adjustment amounts associated to loans in the system. Only available for some customers.

columns:line_item_id,line_item_idcolumns:external_line_item_id,external_line_item_idcolumns:account_id,account_idShow 2 more

{{ config(
materialized = 'table',
pre_hook = [
'{{ create_fdw("adjustment_amounts_table") }}'
],
post_hook = [
'{{ drop_fdw("adjustment_amounts_table") }}',
'{{ create_pk(this, "line_item_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
adjustment_amounts_table
)
SELECT
*
FROM
source_data

delinquency_reportmodel

A table of delinquency report for all accounts.

columns:account_id,account_idcolumns:external_account_id,external_account_idcolumns:organization_id,organization_idShow 7 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("delinquency_report_table") }}',
post_hook = [
'{{ drop_fdw("delinquency_report_table") }}',
'{{ create_pk(this, "account_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
delinquency_report_table
)
SELECT
*
FROM
source_data

statement_line_itemsmodel

A table of statement line items in the system.

columns:line_item_id,line_item_idcolumns:external_line_item_id,external_line_item_idcolumns:statement_id,statement_idShow 22 more

{{ config(
materialized = 'table',
pre_hook = [
'{{ create_fdw("statement_line_items_table") }}',
],
post_hook = [
'{{ drop_fdw("statement_line_items_table") }}',
'{{ create_pk(this, "line_item_id, statement_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
statement_line_items_table
)

SELECT
*
FROM
source_data

transactionsmodel

A table of all transactions that have taken place within the system.

columns:account_id,account_idcolumns:transaction_id,transaction_idcolumns:external_transaction_id,external_transaction_idShow 23 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("transactions_table") }}',
post_hook = [
'{{ drop_fdw("transactions_table") }}',
'{{ create_pk(this, "transaction_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
transactions_table
)
SELECT
*
FROM
source_data

card_transactionsmodel

A table of all transactions related to cards in the system.

columns:card_transaction_id,card_transaction_idcolumns:external_card_transaction_id,external_card_transaction_idcolumns:card_id,card_idShow 4 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("card_transactions_table") }}',
post_hook = [
'{{ drop_fdw("card_transactions_table") }}',
'{{ create_pk(this, "card_transaction_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
card_transactions_table
)
SELECT
*
FROM
source_data

accountsmodel

A table of all accounts and related information in the system.

columns:account_id,account_idcolumns:external_account_id,external_account_idcolumns:organization_id,organization_idShow 133 more

{{ config(
materialized = 'table',
pre_hook = [
'{{ create_fdw("accounts_table") }}',
],
post_hook = [
'{{ drop_fdw("accounts_table") }}',
'{{ create_pk(this, "account_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
accounts_table
)
SELECT
*
FROM
source_data

line_item_splits_retro_tracesmodel

A table to store the additional split types used during reversal and re-pouring for Nacha.

columns:trace_split_id,trace_split_idcolumns:external_trace_split_id,external_trace_split_idcolumns:account_id,account_idShow 9 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("line_item_splits_retro_traces_table") }}',
post_hook = [
'{{ drop_fdw("line_item_splits_retro_traces_table") }}',
'{{ create_pk(this, "trace_split_id") }}'
],
) }}

WITH source_data AS (
SELECT 
*
FROM line_item_splits_retro_traces_table
)
SELECT *
FROM source_data

statementsmodel

A table of all current and prior statement in the system.

columns:statement_id,statement_idcolumns:external_statement_id,external_statement_idcolumns:effective_at,effective_atShow 71 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("statements_table") }}',
post_hook = [
'{{ drop_fdw("statements_table") }}',
'{{ create_pk(this, "statement_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
statements_table
)
SELECT
*
FROM
source_data

line_itemsmodel

Line item table to store all type of events information performed on the accounts in the system.

columns:line_item_id,line_item_idcolumns:external_line_item_id,external_line_item_idcolumns:product_id,product_idShow 24 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("line_items_table") }}',
post_hook = [
'{{ drop_fdw("line_items_table") }}',
'{{ create_pk(this, "line_item_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
line_items_table
)
SELECT
*
FROM
source_data

stabilization_reportmodel

A table of cure, stabilization, and delinquency bucket reduction payment amounts for past due accounts.

columns:account_id,account_idcolumns:external_account_id,external_account_idcolumns:organization_id,organization_idShow 12 more

{{ config(
materialized = 'table',
pre_hook = [
'{{ create_fdw("stabilization_report_table") }}'
],
post_hook = [
'{{ drop_fdw("stabilization_report_table") }}',
'{{ create_pk(this, "account_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
stabilization_report_table
)

SELECT
*
FROM
source_data

balance_splitsmodel

A table of all balance splits that have taken place within the system.

columns:account_id,account_idcolumns:balance_split_id,balance_split_idcolumns:external_balance_split_id,external_balance_split_idShow 13 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("balance_splits_table") }}',
post_hook = [
'{{ drop_fdw("balance_splits_table") }}',
'{{ create_pk(this, "balance_split_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
balance_splits_table
)
SELECT
*
FROM
source_data

nacha_entriesmodel

A table of nacha entries in the system.

columns:nacha_entry_id,nacha_entry_idcolumns:external_nacha_entry_id,external_nacha_entry_idcolumns:subject_account_id,subject_account_idShow 6 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_fdw("nacha_entries_table") }}',
post_hook = [
'{{ drop_fdw("nacha_entries_table") }}',
'{{ create_pk(this, "nacha_entry_id") }}'
],
) }}

WITH source_data AS (

SELECT
*
FROM
nacha_entries_table
)
SELECT
*
FROM
source_data

account_receivables_aging_reportmodel

The Accounts Receivable Aging Report provides a comprehensive view of outstanding loan receivables. This report allows lenders to track the status of their receivables, and assess the effectiveness of their credit policies and collection efforts.

columns:Date,Datecolumns:Customer ID,Customer IDcolumns:External Account ID,External Account IDShow 7 more

{{ config(
materialized = 'table',
pre_hook = [
'{{ create_fdw_reporting("account_receivables_aging_report_table") }}'
],
post_hook = [
'{{ drop_fdw("account_receivables_aging_report_table") }}',
],
) }}

WITH source_data AS (

SELECT
*,
CASE
WHEN "Total Days Past Due" >= 1 AND "Total Days Past Due" <= 30 THEN "Total Amount Past Due"
ELSE 0::numeric(16, 2)
END AS "1-30",
CASE
WHEN "Total Days Past Due" >= 31 AND "Total Days Past Due" <= 60 THEN "Total Amount Past Due"
ELSE 0::numeric(16, 2)
END AS "31-60",
CASE
WHEN "Total Days Past Due" >= 61 AND "Total Days Past Due" <= 90 THEN "Total Amount Past Due"
ELSE 0::numeric(16, 2)
END AS "61-90",
CASE
WHEN "Total Days Past Due" >= 91 AND "Total Days Past Due" <= 120 THEN "Total Amount Past Due"
ELSE 0::numeric(16, 2)
END AS "91-120",
CASE
WHEN "Total Days Past Due" >= 121 THEN "Total Amount Past Due"
ELSE 0::numeric(16, 2)
END AS "121+"
FROM
account_receivables_aging_report_table
)

SELECT
*
FROM
source_data

system_applied_tagsmodel

A table to store system applied tags with the number of accounts that have that tag.

columns:tag_id,tag_idcolumns:label,labelcolumns:description,descriptionShow 4 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("system_applied_tags") }}',
post_hook = [
'{{ drop_public_fdw("system_applied_tags") }}',
'{{ create_pk(this, "tag_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.system_applied_tags
)
SELECT
*
FROM
source_data

organizations_accounts_logmodel

A table to store when associations between organizations and accounts were added or removed.

columns:log_id,log_idcolumns:organization_id,organization_idcolumns:account_id,account_idShow 3 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("organizations_accounts_log") }}',
post_hook = [
'{{ drop_public_fdw("organizations_accounts_log") }}',
'{{ create_pk(this, "log_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.organizations_accounts_log
)
SELECT
*
FROM
source_data

organizations_accountsmodel

A table of all associations between organizations and accounts in the system.

columns:organization_id,organization_idcolumns:account_id,account_idcolumns:is_originating_organization,is_originating_organizationShow 0 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("organizations_accounts") }}',
post_hook = [
'{{ drop_public_fdw("organizations_accounts") }}',
'{{ create_composite_pk(this, ["organization_id", "account_id"]) }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.organizations_accounts
)
SELECT
*
FROM
source_data

organizationsmodel

A table of all organizations in the system.

columns:organization_id,organization_idcolumns:name,namecolumns:webhook_url,webhook_urlShow 1 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("organizations") }}',
post_hook = [
'{{ drop_public_fdw("organizations") }}',
'{{ create_pk(this, "organization_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.organizations
)
SELECT
*
FROM
source_data

accounts_system_applied_tags_logmodel

A table to store when system applied tags were added or removed from accounts.

columns:log_id,log_idcolumns:account_id,account_idcolumns:tag_id,tag_idShow 2 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("accounts_system_applied_tags_log") }}',
post_hook = [
'{{ drop_public_fdw("accounts_system_applied_tags_log") }}',
'{{ create_pk(this, "log_id") }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.accounts_system_applied_tags_log
)
SELECT
*
FROM
source_data

accounts_system_applied_tagsmodel

A join table between accounts and system applied tags.

columns:account_id,account_idcolumns:tag_id,tag_idShow -1 more

{{ config(
materialized = 'table',
pre_hook = '{{ create_public_fdw("accounts_system_applied_tags") }}',
post_hook = [
'{{ drop_public_fdw("accounts_system_applied_tags") }}',
'{{ create_composite_pk(this, ["account_id", "tag_id"]) }}'
],
) }}

WITH source_data AS (
SELECT
*
FROM
public.accounts_system_applied_tags
)
SELECT
*
FROM
source_data