Compass Data Warehouse
You set up a compass data warehouse by creating storage datasets and assigning user roles. You then link your data sources so teams can run SQL queries on clean tables. Most teams start by giving broad read and write roles to speed up early work. Over time, these wide permissions create security risks when many people query live production data. How a compass data warehouse manages roles and data A compass data warehouse pulls raw records from many apps into one central place. Data engineers
Data Warehouse: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
You set up a compass data warehouse by creating storage datasets and assigning user roles. You then link your data sources so teams can run SQL queries on clean tables. Most teams start by giving broad read and write roles to speed up early work. Over time, these wide permissions create security risks when many people query live production data.
How a compass data warehouse manages roles and data
A compass data warehouse pulls raw records from many apps into one central place. Data engineers build pipelines that clean and sort these records into structured tables. Analysts and apps then log in to read those tables and build reports.
To control who sees what, admins set up role-based rules inside the warehouse. You create a role for analysts and another role for data engineers. Each role gets specific rights to read or write to certain schemas and tables. When a user logs in, the warehouse checks their identity and loads their assigned role.
Teams also set up service accounts for reporting tools and automated scripts. These accounts use stored keys or tokens to log in and run scheduled jobs. By grouping users and apps into roles, you avoid managing rules for each person. This native setup forms the base layer of warehouse security.
What native warehouse roles solve and what they miss
Native warehouse roles do a great job of deciding who can log in. They also stop unauthorized users from opening restricted schemas or dropping whole tables. If an analyst only has read rights, they cannot delete records by mistake.
Still, table-level roles leave a big gap during daily operations. Once a user has read rights on a table, they can run any query on it. The native setup checks who logs in, not what individual statements do in flight. An analyst querying a customer table receives every column, including emails, phone numbers, and card numbers.
Data Warehouse: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Static roles also stay active all the time, even when no one needs them. Engineers who need temporary write rights often keep those rights for months. Shared service keys create another blind spot for audit logs. When five people share one database login, the audit log shows the shared role, not the person. To close these gaps, teams need controls that check each query and rewrite sensitive fields in results.
How to tighten daily query operations
Start by splitting broad roles into smaller, task-based roles for each team. Give regular users read rights only on summary tables that hold no personal data. Keep raw tables locked down so only pipeline jobs can write to them.
Next, review how your engineers run one-off fixes on live warehouse tables. Instead of granting permanent write roles, require approval before someone runs an update statement. Also, make sure your results mask sensitive fields like national IDs and emails during everyday checks. This keeps data safe while letting teams fix bugs and build reports fast.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev runs beside those services as an open-source proxy, a sidecar for your Postgres or API. It inspects requests coming in and results going back on the wire.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run. On responses, hoop.dev data masking rewrites matched sensitive values in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
What is the main purpose of a compass data warehouse?
It stores data from many sources in one central system for reporting and analysis. Teams use it to run fast SQL queries without blocking down live customer apps.
Why are table-level roles not enough for data privacy?
Table roles grant all-or-nothing rights to an entire table once a user logs in. Without field masking, any user with read rights will pull raw emails and card numbers in query results.
How should teams handle engineer access to production tables?
Remove permanent write roles from human accounts and link every login to an individual user identity. Require peer approval for write queries and apply masking rules to rewrite sensitive fields in results.