Data Warehouse Query Guide

Overview & Architecture

This documentation outlines the process of writing SQL queries against the Data Warehouse Gold Layer.

• Architecture: We use the Medallion Architecture (Bronze, Silver, Gold). Analytical queries and reporting must only target the Gold layer.
• Data Model: The Gold layer uses a Star Schema approach centered around specific Fact tables and Dimension (Dim) tables.
• Scope: While the operational database is limited to current data, the Data Warehouse allows access to both current states and full historical data.

Writing your query

Step 1: Choosing Your Base Fact

To write a query, you must first determine the primary subject of your data. You have three main Fact tables available:

1. fact_dossier_current: Use this if your query is centered on Dossiers and you only need the latest status.
2. fact_party_current: Use this if your query is centered on Parties (Clients) and you only need the latest status.
3. fact_party_history: Use this if you need to analyze the changes in Party data over time.

note

Migration Tip: If you are migrating a standard query from the operational database (which typically shows "live" data), you will almost always start with a _current fact.

note

Data Completeness: If you start with fact_dossier_current, you will retrieve all dossiers. However, if you subsequently join party data, you will only see party info for parties that have a linked dossier. So choosing the right starting fact based on which entity and data you want to see in your report is important.

Step 2: Joining Dimensions (Standard T-Keys)

Facts in the Gold layer do not contain raw text data (like names or statuses); they contain Technical Keys (t_key). You must join Dimension tables to the Fact to read the actual data.

• Rule: Always join a Dimension to a Fact using the specific t_key column available in the Fact table.
• Join Type:
  • Use INNER JOIN if you only want records where that dimension exists.
  • Use LEFT JOIN if you want to keep all fact records even if the dimension data is missing.

Use this approach as much as possible:

• Joins are simple: Fact.t_key_X = Dim.t_key
• Performance is optimized
• Available for all modules on party and dossier, e.g.:
  • Dossier -> documents
  • Dossier -> bank accounts
  • Party -> Identity
  • ...

Example: Basic Dossier Query

SELECT
    dd.dossier_number,
    dd.dossier_status,
    ddd.document_type,
    ddd.document_status
FROM
    data_warehouse_gold.fact_dossier_current fdc
INNER JOIN
    data_warehouse_gold.dim_dossier dd
ON
    dd.t_key = fdc.t_key_dossier
INNER JOIN
    data_warehouse_gold.dim_dossier_document ddd
ON
    ddd.t_key = fdc.t_key_dossier_document

Step 3: Bridging Entities (Joining Multiple Facts)

A single Fact table contains keys for its specific domain (e.g., a dossier with the related data, such as documents, bank accounts, etc.). However, you often need data from another associated dossier or party.

For example, you want a report with the dossier status and the documents of the client of that dossier. In the relational database, you would join tables as follows: dossier -> party -> document. In the data warehouse, you might be tempted to do: fact_dossier_current -> dim_dossier -> dim_party -> dim_party_document. While this is possible, you need extensive domain knowledge to know which keys to use for joining, and it is not optimized for performance.

The correct solution is to do: fact_dossier_current -> dim_dossier -> fact_party_current -> dim_party_document. This reduces the join complexity, since joining from the facts to the dimensions is the same as in Step 2.

The remaining complexity is to understand how to bridge from one fact to another (fact_dossier_current to fact_party_current in this example).

Since fact_dossier_current does not contain t_key_party, you cannot join dim_party directly. You must "bridge" to the Party Fact first.

The Bridging Logic:

1. Join the primary Dimension (e.g., dim_dossier) to your starting Fact.
2. The Dimension contains the ID (e.g., id_party) needed to bridge to the other domain.
3. Join the secondary Fact (e.g., fact_party_current) using that ID.
4. Join the secondary Dimensions (e.g., dim_party) to the secondary Fact using T-Keys.

Example: Adding Client Data to a Dossier Query

FROM
    data_warehouse_gold.fact_dossier_current fdc
-- 1. Get Dossier Details
INNER JOIN
    data_warehouse_gold.dim_dossier dd
ON
    dd.t_key = fdc.t_key_dossier

-- 2. Bridge to Party Fact using the ID found in dim_dossier
LEFT JOIN
    data_warehouse_gold.fact_party_current fpc
ON
    dd.id_party = fpc.id_party

-- 3. Now you can get Party Details using T-Keys from the Party Fact
LEFT JOIN
    data_warehouse_gold.dim_party dp
ON
    dp.t_key = fpc.t_key_party

note

This logic applies to various relationships, such as related parties, UBOs, insured persons, related dossiers, ...

Step 4: Handling Historical Data (The Exception)

When using History Facts (e.g., fact_party_history), a simple ID join is insufficient because one ID may have multiple rows representing different time periods.

Rule: When joining a history fact to another entity, you must join on the ID AND the Timestamp Validity to ensure you are matching the correct version of the history to the specific point in time of your main record.

Example: Joining History Data

LEFT JOIN
    data_warehouse_gold.fact_party_history fph
ON
    fph.id_party = party.id_party
    AND fph.revision_valid_from <= party.party_revision_valid_from
    AND (
       fph.revision_valid_to > party.party_revision_valid_from
       OR fph.revision_valid_to IS NULL
    )

Step 5: Using Pivot Dimensions (Simplifying Attributes)

In the operational database, retrieving multiple due diligence checks (e.g., Risk Rating, PEP check, Google check) often requires joining the same table multiple times for each parameter (see Comparison: Old vs. New for an example).

In the Gold layer, we provide Pivot Dimensions to simplify this. These dimensions flatten the data, allowing you to select multiple attributes as simple columns from a single join.

Available Pivot Dims:

• dim_dossier_due_diligence_check_pivot
• dim_party_due_diligence_check_pivot
• dim_rba_parameter_result_pivot

warning

Pivots are Current-Only Pivot dimensions always contain only the current data. They never contain history. Even if you are working with history facts, you cannot retrieve historical states of these checks via the Pivot dimensions.

Comparison: Old vs. New

❌ The Hard Way (Operational Style): Requires complex multiple joins.

-- Operational DB approach
LEFT OUTER JOIN due_diligence_check ddc_CHECK_A
    ON (ddc_CHECK_A.id = ... AND ddc_CHECK_A.parameter = 'SPECIFIC_PARAMETER_A')
LEFT OUTER JOIN due_diligence_check ddc_CHECK_B
    ON (ddc_CHECK_B.id = ... AND ddc_CHECK_B.parameter = 'SPECIFIC_PARAMETER_B')

✅ The Gold Layer Way (Pivot Style): One join, direct column selection.

-- Gold Layer approach
SELECT
    pivot_dim.manual_system_result_specific_parameter_a AS check_a_result,
    pivot_dim.manual_system_result_specific_parameter_b AS check_b_result
FROM
    data_warehouse_gold.fact_dossier_current fdc
LEFT JOIN
    data_warehouse_gold.dim_dossier_due_diligence_check_pivot pivot_dim
ON
    pivot_dim.t_key = fdc.t_key_dossier_due_diligence_check_pivot

Testing your query

Superset contains an SQL-lab functionality that allows users to test and validate their queries before configuring them in Harmoney.

Configuring your query

It is possible to directly configure and manage these reports via the admin UI, without assistance from the product/custom team.