Building a warehouse or lakehouse without breaking the ethical wall

A managing partner asks for a single dashboard showing realization rates, matter profitability, and staffing utilization across the whole firm. Simple request. Except three of the underlying matters are subject to an ethical wall (an internal information barrier that screens specific lawyers from a matter because of a conflict of interest), and one client's outside counsel guidelines explicitly forbid that client's billing data from being visible to anyone outside the engagement team. If the dashboard's SQLSQLSales Qualified Lead: ein Prospect, den das Sales-Team als bereit für direkte Ansprache und ein Angebot validiert hat, weil er klare Qualifizierungskriterien erfüllt.Vollständige Definition ansehen → query joins tables without respecting those barriers, the partner just saw something they legally cannot see. That is not a bug ticket. It is a malpractice and conflicts problem wearing a data architecture costume.

This lesson is about building the reporting layer so that never happens, structurally, not by hoping analysts remember the rules.

The source systems: what actually holds the data

Before any warehouse or lakehouselakehouseEine hybride Architektur, die die Flexibilität eines Data Lake mit den analytischen Fähigkeiten eines Data Warehouse auf einer einzigen Storage-Schicht verbindet.Vollständige Definition ansehen → (a centralized repository for structured and semi-structured data, used for analytics) design makes sense, you need to know what feeds it.

• PMS (Practice Management System): the system of record for matters, clients, timekeepers, and matter status. Examples: Elite 3E, Aderant, Intapp. This is where matter-level access rules usually originate.
• DMS (Document Management System): stores work product, iManage and NetDocuments dominate this space in large firms. DMS security models are often the most granular in the firm, down to individual document ACLs (access control lists).
• Time capture: increasingly its own layer (Intapp Time, Bellefield) feeding the PMS, capturing billable hours at the point of entry, sometimes via passive AI capture from calendar and email metadata.
• Conflicts and AML systems: intake tools (Intapp Conflicts, Elite Conflicts Manager) that screen new matters against existing clients, adverse parties, and beneficial ownership data. AML here means Anti-Money Laundering, relevant because firms handling client funds face KYC (Know Your Customer) obligations in many jurisdictions.
• Billing: e-billing platforms (Elite, Aderant, or client-mandated portals like Legal Tracker, Brightflag) that handle invoice submission, often against client-specific rate and format rules (LEDES formats, a standard electronic billing exchange format for legal invoices).

Each of these systems has its own access model. The warehouse's job is to combine their data without inheriting a security hole.

Why the "just extract everything" pattern fails

The naive pattern: nightly ETLETLETL (Extract, Transform, Load) ist ein Datenintegrationsprozess, der Daten aus Quellsystemen extrahiert, in ein konsistentes Format bringt und in ein Zielsystem schreibt.Vollständige Definition ansehen → (Extract, Transform, LoadExtract, Transform, LoadETL (Extract, Transform, Load) ist ein Datenintegrationsprozess, der Daten aus Quellsystemen extrahiert, in ein konsistentes Format bringt und in ein Zielsystem schreibt.Vollständige Definition ansehen →) jobs pull all tables from PMS, DMS metadata, and billing into a central schemaschemaEin Schema ist der formale Bauplan, der festlegt, wie Daten in einer Datenbank, einer Datei oder einer Nachricht strukturiert, benannt, typisiert und miteinander verknüpft sind.Vollständige Definition ansehen →, then a BIBITechnologien und Prozesse, die Rohdaten über Reporting, Dashboards und Analysen in verwertbare Insights überführen, damit Teams auf Basis von Fakten statt Intuition entscheiden.Vollständige Definition ansehen → tool (Power BIBITechnologien und Prozesse, die Rohdaten über Reporting, Dashboards und Analysen in verwertbare Insights überführen, damit Teams auf Basis von Fakten statt Intuition entscheiden.Vollständige Definition ansehen →, Tableau) sits on top with row-level security bolted on at the dashboard layer.

Problems:

1. Security drifts from the source of truth. The PMS knows, in real time, that Matter 48213 just went on ethical wall. If the warehouse's access rules are maintained separately, there's a lag, or worse, someone forgets to update them.
2. Joins can leak by inference. Even if you mask the client name, joining timekeeper hours to matter financials can let someone deduce which lawyers are staffed on a walled-off matter just from utilization patterns.
3. Document content is not the same risk as document metadata. DMS analytics often only need metadata (who touched what, when) but a careless extract pulls full text into a searchable index, which is a privilege (attorney-client privilege, the legal protection over confidential communications between lawyer and client) disaster if that index isn't equally walled.

A pattern that respects the wall: entitlements as a first-class pipelinepipelineAlle aktiven Verkaufschancen über die Phasen des Vertriebsprozesses hinweg, zusammen mit ihrem gesamten potenziellen Wert und ihrer Abschlusswahrscheinlichkeit.Vollständige Definition ansehen → object

The fix is architectural, not procedural. Treat matter-level entitlements (the record of who is allowed to see what) as data that flows through the pipelinepipelineAlle aktiven Verkaufschancen über die Phasen des Vertriebsprozesses hinweg, zusammen mit ihrem gesamten potenziellen Wert und ihrer Abschlusswahrscheinlichkeit.Vollständige Definition ansehen → alongside the business data, not as an afterthought applied at the dashboard.

Layered lakehouselakehouseEine hybride Architektur, die die Flexibilität eines Data Lake mit den analytischen Fähigkeiten eines Data Warehouse auf einer einzigen Storage-Schicht verbindet.Vollständige Definition ansehen → pattern:

• Bronze/raw layer: raw extracts from PMS, DMS metadata, time, billing. Land as-is, no filtering yet. This layer is locked down to the data engineering team only.
• Silver layer: cleaned, conformed data, joined to a canonical matter_id. Critically, this layer also ingests the entitlement table from the PMS or conflicts system: a row per matter, per user or group, with an access flag (full access, walled, view-financial-only, etc.), refreshed on the same cadence as the source (ideally near real time via change data capture, not nightly batch).
• Gold layer: business-ready aggregates (realization rate, matter profitability) but every gold table carries matter_id and is never queried directly by end users. Instead, all access goes through a semantic layer or a view that joins gold data to the entitlement table and filters by the querying user's identity.

This means row-level security (RLS) is enforced at the data layer, not the BIBITechnologien und Prozesse, die Rohdaten über Reporting, Dashboards und Analysen in verwertbare Insights überführen, damit Teams auf Basis von Fakten statt Intuition entscheiden.Vollständige Definition ansehen → tool. If someone connects Excel directly to the warehouse via ODBC, bypassing the dashboard entirely, they still can't see the walled matter.

-- Simplified illustration of entitlement-filtered view
CREATE VIEW gold.matter_financials_secured AS
SELECT f.*
FROM gold.matter_financials f
JOIN security.entitlements e
  ON f.matter_id = e.matter_id
WHERE e.user_id = CURRENT_USER()
  AND e.access_level IN ('FULL', 'FINANCIAL_ONLY');

Every downstream dashboard queries this view, never the base table. Snowflake, Databricks, and Microsoft Fabric all support this pattern natively (Snowflake calls it row access policies, Databricks uses dynamic views with current_user()).

Two failure modes to design against specifically

Aggregation leakage. A dashboard showing "average realization rate by practice group" seems safe, until a practice group has only one matter that quarter, and that matter is walled. The average *is* the walled matter's number. Mitigation: enforce a minimum group size (a common threshold is n≥5 matters or clients) before an aggregate is displayed, a technique borrowed from statistical disclosure control used by agencies like the U.S. Census Bureau.

Stale entitlements. Conflicts and ethical walls change the day a lateral hire joins from a firm that represented the adverse party. If your entitlement table refreshes nightly and the wall goes up at 10am, the dashboard is wrong for hours. This is why entitlement data deserves the same real-time pipelinepipelineAlle aktiven Verkaufschancen über die Phasen des Vertriebsprozesses hinweg, zusammen mit ihrem gesamten potenziellen Wert und ihrer Abschlusswahrscheinlichkeit.Vollständige Definition ansehen → investment as financial data, not less.

Governance layer: who owns the entitlement table

Technology alone doesn't solve this. The entitlement table needs an owner, typically the Office of General Counsel (OGC) or risk/conflicts team, not IT. IT builds the pipelinepipelineAlle aktiven Verkaufschancen über die Phasen des Vertriebsprozesses hinweg, zusammen mit ihrem gesamten potenziellen Wert und ihrer Abschlusswahrscheinlichkeit.Vollständige Definition ansehen →; risk defines and certifies the rules. This mirrors how banks separate data engineering from a compliance-owned entitlements function under frameworks like SOC 2 (Service Organization Control 2, an audit standard for data security controls) that many firms now pursue to satisfy client outside counsel guidelines.

Practically, this means:

• A documented data lineagedata lineageData Lineage zeigt, wie Daten sich durch Systeme bewegen und dabei transformiert werden, von der Quelle bis zur Nutzung: woher sie kommen, was sie verändert hat und wohin sie gehen.Vollständige Definition ansehen → mapmapEinsatz von Software, um wiederkehrende Marketingaufgaben und Kampagnen zu automatisieren und Personalisierung in großem Maßstab über Kanäle wie E-Mail, Web und Social zu ermöglichen.Vollständige Definition ansehen → showing every place matter data lands (a requirement increasingly demanded by sophisticated corporate clients during vendor security reviews).
• Periodic access recertification: someone in risk reviews, quarterly, who has access to walled matters and why.
• Audit logging on the gold layer views themselves, so if a leak is ever alleged, the firm can show exactly who queried what, when.

🎬 [VIDEO: "Row-Level Security in Data Warehouses Explained" — youtube.com — a practical walkthrough of implementing row-level and column-level security patterns in modern cloud data warehouses, applicable directly to the entitlement-view pattern above]

Key Takeaways

• Matter-level access control must be enforced at the data layer (views, row access policies), not just in the BIBITechnologien und Prozesse, die Rohdaten über Reporting, Dashboards und Analysen in verwertbare Insights überführen, damit Teams auf Basis von Fakten statt Intuition entscheiden.Vollständige Definition ansehen → tool, or any direct database connection becomes a leak vector.
• Treat entitlements (who can see which matter) as a real-time data pipelinedata pipelineETL (Extract, Transform, Load) ist ein Datenintegrationsprozess, der Daten aus Quellsystemen extrahiert, in ein konsistentes Format bringt und in ein Zielsystem schreibt.Vollständige Definition ansehen → object sourced from the PMS or conflicts system, not a static list maintained separately.
• Watch for aggregation leakage: small-group averages and counts can reveal walled-matter data even when individual rows are hidden; enforce minimum group sizes.
• Ownership of the entitlement rules belongs to OGC/risk/conflicts teams; IT builds and maintains the pipelinepipelineAlle aktiven Verkaufschancen über die Phasen des Vertriebsprozesses hinweg, zusammen mit ihrem gesamten potenziellen Wert und ihrer Abschlusswahrscheinlichkeit.Vollständige Definition ansehen → that executes those rules.
• Bronze/silver/gold layering lets you separate raw sensitive extracts from governed, access-filtered business views, which is also the pattern that satisfies client security audits and SOC 2 reviews.