M&A Financial Business Intelligence – Power BI & SQL Analytics

dbt Staging Model — stg_sap_gl.sql (clean, rename, cast raw SAP fields):

-- models/staging/stg_sap_gl.sql
-- Cleans and normalizes raw SAP FICO GL entries from Snowflake source table

with source as (
    select * from {{ source('sap_fico', 'bseg_bkpf_joined') }}
),

renamed as (
    select
        -- Keys
        bukrs                                           as company_code,
        belnr                                           as document_number,
        gjahr                                           as fiscal_year,
        buzei                                           as line_item_number,
        hkont                                           as gl_account_number,
        kostl                                           as cost_center,
        prctr                                           as profit_center,
        vbund                                           as trading_partner,

        -- Amounts
        cast(dmbtr as numeric(18,2))                    as amount_local_currency,
        waers                                           as currency_code,
        case when shkzg = 'S' then 'DEBIT'
             when shkzg = 'H' then 'CREDIT'
        end                                             as posting_indicator,

        -- Dates
        cast(budat as date)                             as posting_date,
        cast(bldat as date)                             as document_date,
        poper                                           as posting_period,

        -- Metadata
        usnam                                           as created_by,
        cpudt                                           as created_date,
        'SAP_FICO'                                      as source_system,
        current_timestamp()                             as dbt_loaded_at

    from source
    where gjahr >= 2022  -- rolling 3-year window
      and bstat = ''     -- exclude statistical postings
)

select * from renamed

dbt Mart Model — fct_financial_statements.sql (COA mapping + GAAP adjustments):

-- models/marts/finance/fct_financial_statements.sql
-- Consolidated GL fact table: COA-mapped, GAAP-adjusted, SCD Type 2 COA rules

with gl_combined as (
    select * from {{ ref('int_sap_gl_normalized') }}
    union all
    select * from {{ ref('int_oracle_gl_normalized') }}
),

coa_mapping as (
    -- SCD Type 2: pick the active mapping rule as of the posting date
    select * from {{ ref('int_coa_scd2_mapping') }}
    where is_current = true
),

gaap_policies as (
    select * from {{ ref('seed_gaap_policy_adjustments') }}
),

mapped as (
    select
        gl.company_code,
        gl.document_number,
        gl.fiscal_year,
        gl.posting_date,
        gl.posting_period,

        -- Resolved COA hierarchy
        coalesce(coa.consolidated_account_number, gl.gl_account_number)  as consolidated_account,
        coalesce(coa.account_category, 'UNMAPPED')                       as account_category,
        coalesce(coa.financial_statement, 'UNKNOWN')                     as financial_statement,
        coalesce(coa.reporting_line, 'REVIEW')                           as reporting_line,

        gl.amount_local_currency                                         as amount_original,
        gl.posting_indicator,
        gl.trading_partner,
        gl.source_system,

        -- GAAP policy adjustments via seed file rules
        case
            when gp.adjustment_type = 'ASC_606_DEFERRED'
             and gl.gl_account_number between '400000' and '499999'
             and gl.posting_indicator = 'CREDIT'
            then gl.amount_local_currency * -1
            when gp.adjustment_type = 'DEPRECIATION_SL'
             and gl.gl_account_number between '600000' and '699999'
            then gl.amount_local_currency * 0.67
            else gl.amount_local_currency
        end                                                              as amount_gaap_adjusted,

        gp.adjustment_type,
        gp.adjustment_reason

    from gl_combined gl
    left join coa_mapping coa
        on  gl.gl_account_number = coa.source_account_number
        and gl.source_system     = coa.source_system
        and gl.posting_date between coa.valid_from and coa.valid_to
    left join gaap_policies gp
        on  gl.gl_account_number between gp.account_range_low and gp.account_range_high
        and gl.source_system = gp.source_system
),

final as (
    select
        {{ dbt_utils.generate_surrogate_key([
            'company_code', 'document_number', 'fiscal_year'
        ]) }}                                                    as transaction_key,
        *,
        current_timestamp()                                      as dbt_updated_at
    from mapped
)

select * from final

Automated Trial Balance Reconciliation (dbt/SQL in Snowflake):

• Source: Mart layer consolidated GL transactions in Snowflake (fct_financial_statements)
• Validation Rules: Debits = Credits at document level, period level, and consolidated level
• Tolerance: ±$0.01 (flags exceptions for manual review)
• Output: Trial balance report by account, cost center, profit center
• Frequency: Real-time validation on every dbt model run

Power BI Integration for M&A Reporting:

• Data Source: Snowflake (DirectQuery mode for real-time data)
• Reports: P&L statement, Balance Sheet, Cash Flow Statement, KPI scorecards
• DAX Measures: EBITDA calculation, working capital ratios, DSO, inventory turns
• Security: Row-level security (RLS) by deal team, client confidentiality enforced
• Refresh: Incremental refresh every 4 hours during active due diligence