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: a prospect the sales team has validated as ready for direct outreach and a proposal, having passed clear qualification criteria.View full definition → 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 lakehouselakehouseA hybrid architecture combining the flexibility of a data lake with the analytical capabilities of a data warehouse, on a single storage layer.View full definition → (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) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.View full definition → (Extract, Transform, LoadExtract, Transform, LoadETL (Extract, Transform, Load) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.View full definition →) jobs pull all tables from PMS, DMS metadata, and billing into a central schemaschemaA schema is the formal blueprint that defines how data is structured, named, typed, and related within a database, file, or message.View full definition →, then a BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition → tool (Power BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition →, 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition → 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition → alongside the business data, not as an afterthought applied at the dashboard.

Layered lakehouselakehouseA hybrid architecture combining the flexibility of a data lake with the analytical capabilities of a data warehouse, on a single storage layer.View full definition → 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 BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition → 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition → 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition →; 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 maps how data moves and transforms across systems, from origin to consumption, showing where it came from, what changed it, and where it goes.View full definition → mapmapUsing software to automate repetitive marketing tasks and campaigns, enabling personalisation at scale across channels like email, web, and social.View full definition → 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 BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.View full definition → 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) is a data integration process that pulls data from sources, reshapes it into a consistent format, and writes it into a target system.View full definition → 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition → 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.