← All work

Regulatory reporting model on Snowflake

Analytics Engineer, 2degrees · 2024 – present
This work involves a sensitive dataset. The detail below is deliberately general. The transferable skills are real; the confidential specifics stay out.
dbtSnowflakeSQL (window functions, arrays)IcebergIncremental / micro-batch

Some source data arrives in pieces. A single real-world event can be spread across many fragmented records that have to be stitched back together, in the right order, before anyone can report on it. For a high-sensitivity regulatory dataset, that reconstruction also has to be complete and auditable, because the output has to stand up to scrutiny.

I built the dbt model that assembles those fragments into complete activity records. The hard parts were the ordering logic and the edge cases: window functions to sequence events, array handling for grouped data, and layered CASE logic to resolve the messy in-between states correctly. It runs incrementally in micro-batches on Iceberg tables so it stays fast and affordable at volume.

What made it hard

  • Reconstructing complete records from fragmented, out-of-order source data.
  • Getting the edge cases right, where “close enough” is not good enough for a regulatory output.
  • Keeping it performant and cheap to run as the data grew, using incremental and micro-batch patterns.