Finance analytics platform

Monthly actuals with historical context and explicit sign rules.

Built a ClickHouse and dbt demonstration that joins synthetic ledger entries to historical account and cost-center records, then calculates signed monthly actuals.

Inspect the artifacts View repository Engineering decisions

My role: I designed the data model, wrote the dbt transformations, and worked through local data and build defects.
Result & status: Implemented a local path from synthetic ledger files to a monthly actuals mart, with readable SQL, source definitions, and data-quality test definitions available for review.

This is an independent demonstration with synthetic data. Airflow orchestration and full integration CI remain future work.

Public evidence

Inspect the work.

Each link opens the source file or working product described below.

Transformation

Monthly actuals SQL

Inspect the effective-date joins, grouping columns, and signed-amount calculation that define the mart.

dbt schema

Staging data-quality tests

Defines uniqueness, required-field, and accepted-value checks for ledger and historical reference records.

Development record

Local validation and CI decisions

Records duplicate-load and debit/credit defects, followed by the decision to start CI with parsing only.

Build configuration

CI configuration

Shows the actual dbt parse job, making the current automation boundary visible.

The problem

How should monthly reporting retain the account and cost-center context that applied when an entry was posted?

Historical joins

Posting dates select effective account and cost-center versions.

Declared grain

Fiscal year and period × cost-center version × account version.

Metric definition

Signed actuals use the entry flag and the account’s natural sign.

Approach

Preserve historical reporting context.

I separated CSV inputs, raw ClickHouse tables, dbt staging models, and a monthly mart. Account and cost-center records use surrogate keys and effective dates, so the SQL can resolve a ledger entry to the dimension version valid on its posting date.

Engineering

Make the grain and sign calculation inspectable.

The mart uses left joins on business keys and inclusive effective-date ranges. It groups by fiscal year, fiscal period, and account and cost-center versions, retaining their labels. A CASE expression assigns the amount sign from the debit/credit flag and account natural sign before aggregation. Keeping dimension versions in the grouping preserves changes that occur within a period.

Validation

Separate data tests from CI parsing.

The checked-in dbt schemas define unique and non-null entry and dimension keys, accepted debit/credit and current-record flags, and required mart fields. The project log records tests exposing duplicate loads and blank debit/credit values during local development. GitLab CI parses the dbt project; it does not run a database integration test.

Tools & methods

SQL · dbt · ClickHouse · Docker Compose · Dimensional modeling