Replace hardcoded schema references with {{ ref() }} and fix override_ref dimensional prefix mapping

[quazi-h]

Generated by AI

Description/Context

The local DuckDB development workflow (--target dev_local) was introduced to allow engineers to build
and test dbt models locally without incurring cloud costs or requiring Trino credentials. However, two
related issues currently prevent dimensional-layer-dependent models from being testable locally,
requiring fallback to the Trino dev_production target for any model that references dimensional tables.

Issue 1: Hardcoded schema paths in learner_engagement_report.sql

models/reporting/learner_engagement_report.sql references dimensional and intermediate tables using
hardcoded Trino catalog paths (e.g. ol_warehouse_production_dimensional.tfact_video_events) instead
of {{ ref() }} calls. DuckDB has no way to resolve these paths, causing the model to always fail
locally:

Catalog Error: Table with name tfact_video_events does not exist!

This is the only model in the codebase currently using hardcoded paths.

Issue 2: override_ref macro missing dimensional model prefix mappings

Even for models that correctly use {{ ref() }}, the override_ref macro in macros/override_ref.sql
will not correctly route dimensional model refs to their registered Glue views. The current
prefix-to-database mapping checks for dim__ (double underscore), but dimensional models use the
prefixes dim_, tfact_, and afact_ (single underscore). Since none of these match, the Glue view
fallback never fires and the macro falls through to a local relation that doesn't exist.

Impact

Together these two issues mean any reporting model that depends on dimensional tables cannot be built
or tested locally. Engineers must use the Trino dev_production target for these models, which is
slower, requires credentials, and incurs cloud costs — exactly what the local dev workflow was designed
to avoid.

---

Plan/Design

Fix for Issue 1: Replace all hardcoded ol_warehouse_production_dimensional.* and
ol_warehouse_production_intermediate.* references in learner_engagement_report.sql with
{{ ref('model_name') }} calls, consistent with the rest of the codebase.

Fix for Issue 2: Update the prefix-to-database mapping in macros/override_ref.sql to include
the correct dimensional model prefixes:

{% elif model_name.startswith('dim_') 
    or model_name.startswith('tfact_') 
    or model_name.startswith('afact_') %}
  {% set glue_database = 'ol_warehouse_production_dimensional' %}