FGAC Design Trade-offs: When to Use What

The Central Tension: Choosing an access control strategy means balancing precision against performance, centralization against flexibility, and security against developer productivity. There is no universally correct answer. The right choice depends on your read to write ratio, user scale, data sensitivity, and organizational structure. Performance versus Precision: Very granular row level filters can significantly increase planning and execution time. A query that normally scans 200 gigabytes and finishes in 5 seconds might degrade to 20 seconds p99 if the engine cannot efficiently push down user specific predicates. This happens when predicates reference complex functions or external lookups that break index usage. Some teams deliberately accept slightly broader access with coarser filters in exchange for predictable performance. For example, filtering by department instead of individual user IDs reduces permission table size from millions to hundreds of rows. The trade off is that some users see data from colleagues in the same department that they do not strictly need. Centralization versus Flexibility: Central policy engines like Unity Catalog or Lake Formation give you one place to manage rules and generate audit logs. But they can be slower to evolve and might not capture application specific logic. If your recommendation system needs to filter based on complex user preference graphs, encoding that in a central policy language is painful. In contrast, embedding access control in application code gives teams more control and faster iteration. But it risks inconsistent policies where the data warehouse enforces one set of rules, the search index enforces different rules, and the mobile API has yet another implementation. When you explain this trade off in an interview, emphasize who owns policies and how you prevent divergence. Security versus Productivity: Strict fine grained controls on development and staging data reduce risk of leaking production information, but they slow down debugging and experimentation. Some systems adopt secure by default where policies apply everywhere. This forces developers to work with anonymized data, which surfaces data quality issues earlier but makes reproducing production bugs harder. Others allow more open access in non production environments but require more rigor in data anonymization pipelines. The key question is whether your threat model includes insider threats from developers with environment access. When to Choose Pre Filtered Views: Despite the data duplication, pre filtered materialized views make sense when access patterns are stable and user populations are discrete. If you have exactly three business units that never overlap in their data needs, maintaining three filtered copies is simpler than dynamic row level security. Views are easier to reason about, perform better, and avoid policy evaluation overhead entirely. Choose built in FGAC when user attributes change frequently, access patterns are unpredictable, or you have thousands of distinct permission combinations that would explode into unmanageable view counts.