DataDirect
Access Canopy data through direct database connection
Introduction
After Canopyβs computation engine has run, the resulting output data can be accessed in a number of different ways.
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
DataDirect vs API Requests
DataDirect was designed to solve a few key points over the API approach:
API | DataDirect |
---|---|
Our APIs and CanopyOS are best suited for operational purposes. | DataDirect was built with analytical and reporting purposes in mind. |
E.g. when you need to know real-time information about your customer, account, individual charge/loan/payment etc. | E.g. when you need to analyze and aggregate information across your portfolio. |
DataDirect solves the problem of having to make multiple API calls and having to store the data off somewhere in order to query it.
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:
- Which customers are paying in full each month?
- What strategies produce the highest repayment rates?
- Where is my highest cost of servicing coming from?
How does it fit within Canopy's Platform?
For clarity, here are a few additional methods that Canopy provides for accessing your output data, their individual use cases and read/write rights:
DataDirect | API Calls | Web Hooks | CanopyOS | Borrower Portal |
---|---|---|---|---|
Bulk consumption of your data. | Real-time access to SOR. | Notifications of data state change. | Web interface (UI) access to SOR. | White-labeled web interface (UI) for your borrowers. |
Ideal for reporting and data analytics. | Ideal for operational consumption. | Ideal for creating event-based triggers. | Ideal for non-technical users internally | Ideal for driving borrower self-service (repayments, balance checking) |
Read-only | Read and Write | Read-only | Read and Write | Read and Write |
Checking data freshness
DataDirect data is typically refreshed every hour. To identify the time of the last refresh and the state it represents, you can check for the most recent related record in the refresh_history
:
select
table_name,
last_updated_at
from data_direct.refresh_history;
Changing Timezone Configuration in Database Clients
In order to ensure that your database interactions reflect the appropriate timezone, it's essential to configure your SQL client application to use the desired timezone. By default, our databases are configured to use Coordinated Universal Time (UTC). However, depending on your location or specific requirements, you may need to adjust the timezone settings in your SQL client.
Changing the timezone configuration in your SQL client ensures that timestamps and datetime values are displayed and interpreted correctly according to your local timezone.
Below are general steps for changing the timezone configuration in some commonly used SQL client applications:
- PostgreSQL's psql Command Line Tool:
When connecting to your PostgreSQL database using psql, you can specify the timezone using the TZ environment variable. For example:
psql -h your_database_host -U your_username -d your_database_name -c 'SET TIME ZONE your_desired_timezone;'
Replace your_desired_timezone with the timezone you want to use, such as 'America/New_York'.
- GUI-based SQL Clients (e.g., pgAdmin, DBeaver):
In graphical SQL clients, timezone configuration settings are typically found in the connection properties or preferences menu. Look for options related to timezone, date/time format, or session settings, and adjust them according to your requirements.
DBeaver: In Window -> Preferences. Go to User Interface and change the Client Timezone. Restart dbeaver.
Updated 2 months ago