DataBridge

DataBridge is a customer data access layer in Snowflake. It provides secure, near real-time access to your Filevine data, from cases and client information to notes, calendars, and more. With DataBridge, you can connect Filevine to your analytics stack, build custom dashboards, and act with greater speed and precision. DataBridge:

• provides universal, durable, and comprehensive schemas with data dictionaries, all in a SQL interface
• allows customers near-real time access to their Filevine data, with data latency of only 10–20 minutes; data is constantly refreshing

DataBridge’s views live in Filevine’s Snowflake account. Users have full query access.

• DataBridge Setup
• Using DataBridge
• Example Query

DataBridge Setup

To sign up for DataBridge, contact a Filevine account representative.

Snowflake Account

Before you set up DataBridge, you must have a Snowflow account that uses the following region:

• For US customers: AWS US West (Oregon)/AWS US West 2
• For Canadian customers: AWS Canada (Central)

Learn more about Snowflake with Snowflake’s documentation. Your account rep can help you get in contact with Snowflake to set up your account. Snowflake also offers a free trial so you can get up and running quicker.

Provide Account Information

Once you’ve signed up for DataBridge, please email the provisioning team: provisioningteam@filevine.com with the following two items:

•  your account’s Data sharing account identifier
• a screenshot confirming that you are using the correct AWS, as described above

Your Snowflake account’s Data sharing account identifier can be found in Snowflake by clicking your profile and then selecting Account > View Account Details.

You can view your AWS by clicking your avatar in the bottom left corner and then selecting Account. Take a screenshot of the AWS as shown below.

Add Listing

Once we have posted it to your account, you will be able to see DataBridge by Filevine in the Privately Shared Listings page.

Refer to Snowflake’s documentation for more information on accessing a private listing: https://other-docs.snowflake.com/en/collaboration/consumer-listings-access

Using DataBridge

Once you have access to the DataBridge by Filevine database, you can use Snowflake to query, pivot, and filter your data, create workflows, or send the data to a BI or data visualization system. 

Data Overview

As an overview, DataBridge covers the following:

• Billing Items (Time Entries, Expenses and Fees)
• Invoices
• Transactions
• Audit
• Activity Feed (Notes, Tasks and comments)
• Documents metadata
• Tags
• Persons/Contacts
• Projects
• Sections and field/field values
• Project teams
• Users

For example, the “project sections” view includes project types, project sections, project IDs, field names and types, and field values.

Note

View DataBridge views reference details. 

Example Query

This query provides a simple pivot that does a type conversion of all fields to string. It is useful if you want a procedure you can use with all sections.

with 
    filtered_data as (
        select project_id, collection_item_guid, section_name, field_name, coalesce(case when value_date is not null then value_variant else null end, value_date::variant, value_decimal::variant, value_int::variant, value_string, value_bool::variant, value_variant) as value_variant
        from vw_databridge_project_sections_data_v1
        where project_type_name = 'Criminal Defense' and section_name = 'Charges'
    ),
    piv01 as
    (
        select 
            piv.*
        from 
            filtered_data
        pivot
            (max(value_variant) for field_name in(any)) as piv
    )
select * from piv01
//If you have a section with low schema drift, you can strongly type your field set as below to ensure that data types are preserved
WITH charges_filtered AS (
    SELECT
        project_id,
        collection_item_guid,
        field_name,
        value_int,
        value_string
        -- You can include value_date/value_decimal/value_bool here
        -- if you later add typed fields that use them
    FROM vw_databridge_project_sections_data_v1
    WHERE project_type_name = 'Criminal Defense'
      AND section_name      = 'Charges'
),
charges_typed AS (
    SELECT
        project_id,
        collection_item_guid,
        -- String fields
        MAX(CASE WHEN field_name = 'Disposition'        THEN value_string END) AS disposition,
        MAX(CASE WHEN field_name = 'Status'             THEN value_string END) AS status,
        MAX(CASE WHEN field_name = 'Plain Language'     THEN value_string END) AS plain_language,
        MAX(CASE WHEN field_name = 'Charge Count'       THEN value_string END) AS charge_count,
        MAX(CASE WHEN field_name = 'Charge'             THEN value_string END) AS charge,
        MAX(CASE WHEN field_name = 'Grade'              THEN value_string END) AS grade,
        MAX(CASE WHEN field_name = 'Penal Code Section' THEN value_string END) AS penal_code_section,
        MAX(CASE WHEN field_name = 'Charge Notes'       THEN value_string END) AS charge_notes,
        -- Integer fields
        MAX(CASE WHEN field_name = 'Count Number' THEN value_int END) AS count_number,
        MAX(CASE WHEN field_name = 'Charge'       THEN value_int END) AS charge_int
    FROM charges_filtered
    GROUP BY
        project_id,
        collection_item_guid
)
SELECT *
FROM charges_typed;