🔗 Cross-Database Queries in Microsoft Fabric¶

-- Three-part naming: database.schema.table
SELECT *
FROM database_name.schema_name.table_name;

-- Lakehouse tables always use 'dbo' schema
SELECT * FROM lh_bronze.dbo.raw_slot_telemetry;

-- Warehouse tables use 'dbo' or custom schemas
SELECT * FROM wh_gold.dbo.dim_machine;
SELECT * FROM wh_gold.analytics.fact_daily_revenue;

-- SQL Database tables
SELECT * FROM sqldb_operations.dbo.machine_configuration;

-- Join Lakehouse Bronze data with Warehouse Gold dimension
SELECT
    t.transaction_id,
    t.machine_id,
    t.wager_amount,
    t.payout_amount,
    m.machine_name,
    m.floor_location,
    m.denomination
FROM lh_bronze.dbo.raw_slot_transactions AS t
INNER JOIN wh_gold.dbo.dim_machine AS m
    ON t.machine_id = m.machine_id
WHERE t.transaction_date >= '2026-04-01';

-- Create a view in the Warehouse that references Lakehouse tables
CREATE VIEW wh_gold.dbo.vw_compliance_summary AS
SELECT
    c.filing_type,
    c.filing_date,
    c.status,
    p.player_tier,
    p.state
FROM lh_gold.dbo.gold_compliance_ctr AS c
INNER JOIN wh_gold.dbo.dim_player AS p
    ON c.player_id = p.player_id;

-- CTE combining data from three sources
WITH bronze_transactions AS (
    SELECT
        machine_id,
        SUM(wager_amount) AS total_wagers,
        COUNT(*) AS transaction_count
    FROM lh_bronze.dbo.raw_slot_transactions
    WHERE transaction_date = CAST(GETDATE() AS DATE)
    GROUP BY machine_id
),
machine_info AS (
    SELECT machine_id, machine_name, floor_location, denomination
    FROM wh_gold.dbo.dim_machine
    WHERE is_active = 1
),
maintenance_status AS (
    SELECT machine_id, last_service_date, next_service_date, status
    FROM sqldb_operations.dbo.machine_maintenance
)
SELECT
    mi.machine_name,
    mi.floor_location,
    bt.total_wagers,
    bt.transaction_count,
    ms.status AS maintenance_status,
    ms.next_service_date
FROM bronze_transactions bt
INNER JOIN machine_info mi ON bt.machine_id = mi.machine_id
LEFT JOIN maintenance_status ms ON bt.machine_id = ms.machine_id
ORDER BY bt.total_wagers DESC;

-- If Lakehouse has RLS filtering by agency_code
-- and Warehouse has RLS filtering by floor_location:
-- The cross-DB query returns only rows matching BOTH policies

SELECT l.*, w.*
FROM lh_gold.dbo.gold_agency_kpis AS l
INNER JOIN wh_gold.dbo.dim_location AS w
    ON l.location_id = w.location_id;
-- Result: Only rows where user has access in BOTH databases

-- Compliance investigation: Find all transactions for a flagged player
-- across raw data (Lakehouse), compliance records (Lakehouse Gold),
-- and machine config (SQL DB)
SELECT
    t.transaction_id,
    t.transaction_timestamp,
    t.amount,
    t.transaction_type,
    m.machine_name,
    m.floor_location,
    c.filing_type,
    c.filing_status,
    c.filed_date
FROM lh_bronze.dbo.raw_slot_transactions AS t
INNER JOIN sqldb_operations.dbo.machine_configuration AS m
    ON t.machine_id = m.machine_id
LEFT JOIN lh_gold.dbo.gold_compliance_ctr AS c
    ON t.player_id = c.player_id
    AND CAST(t.transaction_timestamp AS DATE) = c.transaction_date
WHERE t.player_id = 'P-12345'
    AND t.transaction_timestamp >= '2026-04-01'
ORDER BY t.transaction_timestamp;

-- Real-time floor status: Combine live telemetry with master data
-- and maintenance status for the floor manager dashboard
SELECT
    m.floor_location,
    m.denomination,
    COUNT(DISTINCT t.machine_id) AS active_machines,
    SUM(t.net_revenue) AS total_revenue,
    AVG(t.hold_percentage) AS avg_hold_pct,
    SUM(CASE WHEN mx.status = 'needs_service' THEN 1 ELSE 0 END) AS machines_needing_service
FROM wh_gold.dbo.fact_slot_daily AS t
INNER JOIN wh_gold.dbo.dim_machine AS m
    ON t.machine_id = m.machine_id
LEFT JOIN sqldb_operations.dbo.machine_maintenance AS mx
    ON t.machine_id = mx.machine_id
WHERE t.date_key = CAST(GETDATE() AS DATE)
GROUP BY m.floor_location, m.denomination
ORDER BY total_revenue DESC;

-- Create unified compliance view in the Warehouse
CREATE VIEW wh_gold.dbo.vw_compliance_360 AS
SELECT
    'CTR' AS filing_type,
    c.player_id,
    c.transaction_date,
    c.amount,
    c.filing_status,
    p.player_tier,
    p.risk_score
FROM lh_gold.dbo.gold_compliance_ctr AS c
INNER JOIN wh_gold.dbo.dim_player AS p ON c.player_id = p.player_id

UNION ALL

SELECT
    'SAR' AS filing_type,
    s.player_id,
    s.detection_date,
    s.total_amount,
    s.filing_status,
    p.player_tier,
    p.risk_score
FROM lh_gold.dbo.gold_compliance_sar AS s
INNER JOIN wh_gold.dbo.dim_player AS p ON s.player_id = p.player_id;

-- Cross-agency: Correlate NOAA storm events with SBA disaster loans
-- to measure federal response effectiveness
WITH storm_events AS (
    SELECT
        state,
        event_type,
        event_date,
        damage_property,
        damage_crops
    FROM lh_gold.dbo.gold_noaa_storm_events
    WHERE severity IN ('Severe', 'Extreme')
        AND event_date >= '2025-01-01'
),
disaster_loans AS (
    SELECT
        state,
        loan_type,
        approval_date,
        loan_amount,
        DATEDIFF(DAY, disaster_date, approval_date) AS days_to_approval
    FROM lh_gold.dbo.gold_sba_disaster_loans
    WHERE disaster_date >= '2025-01-01'
)
SELECT
    se.state,
    COUNT(DISTINCT se.event_date) AS storm_events,
    SUM(se.damage_property) AS total_property_damage,
    COUNT(DISTINCT dl.approval_date) AS loans_approved,
    SUM(dl.loan_amount) AS total_loan_disbursed,
    AVG(dl.days_to_approval) AS avg_days_to_approval
FROM storm_events se
LEFT JOIN disaster_loans dl
    ON se.state = dl.state
    AND dl.approval_date BETWEEN se.event_date AND DATEADD(DAY, 90, se.event_date)
GROUP BY se.state
ORDER BY total_property_damage DESC;

-- Cross-agency: Correlate EPA toxic releases with DOI land permits
SELECT
    epa.state,
    epa.facility_name,
    epa.chemical_name,
    SUM(epa.release_amount_lbs) AS total_toxic_release,
    COUNT(DISTINCT doi.permit_id) AS nearby_permits,
    STRING_AGG(DISTINCT doi.permit_type, ', ') AS permit_types
FROM lh_gold.dbo.gold_epa_tri_releases AS epa
LEFT JOIN lh_gold.dbo.gold_doi_land_permits AS doi
    ON epa.state = doi.state
    AND epa.county = doi.county
    AND doi.permit_date BETWEEN
        DATEADD(YEAR, -1, epa.reporting_year_start)
        AND epa.reporting_year_start
WHERE epa.year = 2025
    AND epa.release_medium = 'Air'
GROUP BY epa.state, epa.facility_name, epa.chemical_name
HAVING SUM(epa.release_amount_lbs) > 10000
ORDER BY total_toxic_release DESC;

-- How weather events affect crop production by state
SELECT
    c.state,
    c.commodity,
    c.yield_per_acre,
    c.production_value,
    COUNT(n.event_id) AS weather_events_in_growing_season,
    SUM(n.damage_crops) AS reported_crop_damage
FROM wh_gold.dbo.fact_usda_crop_production AS c
LEFT JOIN lh_gold.dbo.gold_noaa_storm_events AS n
    ON c.state = n.state
    AND n.event_date BETWEEN c.planting_date AND c.harvest_date
WHERE c.year = 2025
GROUP BY c.state, c.commodity, c.yield_per_acre, c.production_value
ORDER BY reported_crop_damage DESC;

-- Scheduled procedure: Materialize cross-agency summary daily
CREATE PROCEDURE wh_gold.dbo.usp_refresh_cross_agency_summary
AS
BEGIN
    TRUNCATE TABLE wh_gold.dbo.cross_agency_daily_summary;

    INSERT INTO wh_gold.dbo.cross_agency_daily_summary
    SELECT
        'USDA' AS agency,
        CAST(GETDATE() AS DATE) AS snapshot_date,
        COUNT(*) AS record_count,
        SUM(production_value) AS total_value
    FROM lh_gold.dbo.gold_usda_crop_production
    WHERE year = YEAR(GETDATE())

    UNION ALL

    SELECT 'SBA', CAST(GETDATE() AS DATE),
        COUNT(*), SUM(loan_amount)
    FROM lh_gold.dbo.gold_sba_ppp_loans
    WHERE YEAR(disbursement_date) = YEAR(GETDATE())

    UNION ALL

    SELECT 'NOAA', CAST(GETDATE() AS DATE),
        COUNT(*), SUM(damage_property)
    FROM lh_gold.dbo.gold_noaa_storm_events
    WHERE YEAR(event_date) = YEAR(GETDATE());
END;

-- List all tables accessible via cross-database queries in this workspace
SELECT
    TABLE_CATALOG AS database_name,
    TABLE_SCHEMA AS schema_name,
    TABLE_NAME AS table_name,
    TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME;