multi-catalog — one session, three catalogsnot a dbt project

1 attach a read-only warehouse + a local catalog (OneLake tokens are automatic)

# a Warehouse is just a Lakehouse that is locked to writes — attach it read-only.
# OneLake tokens are acquired automatically (Fabric notebook / env / az login):
conn.attach(warehouse_path, name="warehouse", schema="mart", read_only=True)
conn.attach(local_path,     name="local")   # a plain local folder, writable

conn.catalog.listCatalogs()

three catalogs bound to one session; 'lakehouse' is writable, 'warehouse' is read-only

2 list EVERY table across all catalogs — the three-part catalog.schema.table name

# one session sees all three catalogs; every table has a three-part name:
conn.sql("""SELECT table_catalog, table_schema, table_name
            FROM information_schema.tables
            WHERE table_schema NOT IN ('information_schema','pg_catalog','main')
            ORDER BY 1, 2, 3""")

every table the session can see, three-part named

6 tables across 3 catalogs — the warehouse and lakehouse expose the SAME mart tables; only the warehouse is read-only

3 the three ways to name a table: catalog.schema.table → schema.table → table

# 3-part — fully qualified, works from anywhere (cross-catalog):
conn.sql("SELECT * FROM warehouse.mart.fct_summary LIMIT 5")
# 2-part — schema.table, in the current catalog:
conn.catalog.setCurrentCatalog("warehouse"); conn.sql("SELECT * FROM mart.fct_summary LIMIT 5")
# 1-part — bare table, in the current catalog + database:
conn.catalog.setCurrentDatabase("mart");      conn.sql("SELECT * FROM fct_summary LIMIT 5")

the same warehouse table, three naming conventions — identical on a bounded probe

all three names resolve to the same table (probed on a 100k-row sample, not the full fact)

4 a Warehouse is locked to writes — the read-only fence refuses a write into it

conn.sql("SELECT 1 AS x").write.mode("overwrite").saveAsTable("warehouse.mart.nope")
#  -> PermissionError: catalog 'warehouse' is read-only

the same write, two catalogs

the warehouse refuses writes (locked); the lakehouse accepts them

5 write a carbon-factor lookup into the LOCAL catalog (local.dbo.fuel_factors)

lookup = conn.sql("SELECT * FROM (VALUES …) t(fuel, co2_kg_per_mwh)")
lookup.write.mode("overwrite").saveAsTable("local.dbo.fuel_factors")

15 fuel factors written to the local catalog

6 JOIN all three → write 'multicatalog_demo.mart_generation_by_state' back to the lakehouse

# facts: WAREHOUSE (read-only) · DUID dim: LAKEHOUSE · carbon factor: LOCAL
mart = conn.sql(f"""
    WITH facts AS (   -- a bounded sample, not the whole 100M+-row fact
        SELECT DUID, mw, price FROM warehouse.mart.fct_summary LIMIT 200000
    )
    SELECT d.State,
           round(sum(f.mw), 0)                              AS total_mw,
           round(avg(f.price), 2)                           AS avg_price,
           round(sum(f.mw * coalesce(lf.co2_kg_per_mwh,0))/1000.0, 1) AS est_tonnes_co2
    FROM facts f
    JOIN mart.dim_duid d                ON d.DUID = f.DUID
    LEFT JOIN local.dbo.fuel_factors lf ON lower(lf.fuel) = lower(d.FuelSourceDescriptor)
    GROUP BY d.State""")
mart.write.mode("overwrite").saveAsTable("multicatalog_demo.mart_generation_by_state")

multicatalog_demo.mart_generation_by_state — warehouse ⋈ lakehouse ⋈ local (200,000-row fact sample)

mart written back to the lakehouse (abfss://…/Tables/multicatalog_demo/mart_generation_by_state) — joined a 200,000-row fact sample(warehouse) + dim(lakehouse) + factors(local)