05 - SQL Server Schema Layering

69 min read

SQL Server Schema Layering


flowchart TD
    A["Starting a SQL Server data platform"] --> B{"Do the layers share the same<br/>backup, security, and compute boundary?"}
    B --> Y1([YES])
    B --> N1([NO])
    Y1 --> C["Use one database with<br/>bronze / silver / gold schemas"]
    N1 --> D{"Is the split driven by true operational isolation,<br/>not by team preference alone?"}
    D --> Y2([YES])
    D --> N2([NO])
    Y2 --> E["Use separate databases per layer<br/>or per regulated boundary"]
    N2 --> F["Stay in one database and keep<br/>schema boundaries explicit"]
    C --> G{"Do multiple domain teams own data products<br/>independently?"}
    E --> G
    F --> G
    G --> Y3([YES])
    G --> N3([NO])
    Y3 --> H["Add domain-oriented schemas or naming<br/>without hiding the layer boundary"]
    N3 --> I["Keep the model simple and avoid schema proliferation"]

    classDef yesNode fill:#1f3b2d,stroke:#73d13d,stroke-width:2px,color:#c0caf5,font-weight:bold;
    classDef noNode fill:#4a1f24,stroke:#db4b4b,stroke-width:2px,color:#c0caf5,font-weight:bold;
    class Y1,Y2,Y3 yesNode;
    class N1,N2,N3 noNode;

Key Concepts

Every later section in this note assumes the reader already understands five terms: the medallion architecture, the dbo schema, the schema as a namespace boundary, the schema as a permission scope, and schema ownership via AUTHORIZATION. This section defines each one precisely so the rest of the note can build on them without ambiguity.

Medallion architecture — bronze, silver, gold

The medallion architecture is a three-layer convention for organizing a data platform by data maturity, not by physical storage location. It originated in the lakehouse world (Databricks, Delta Lake) and has become the standard vocabulary for warehouse and lakehouse designs alike.

LayerData maturityTypical contentsTypical consumersSource of truth?
bronzeRaw, as-landedIngested source records, usually append-only, with metadata such as _ingested_at, _source_file, _batch_idPipeline jobs onlyNo — re-fetchable from upstream
silverCleaned, deduplicated, conformedBusiness-ready facts and dimensions, validated types, joined reference data, SCD2 historyPipeline jobs and analytical workloadsUsually yes
goldPublished, consumption-readyAggregates, metrics, denormalized reporting tables, or Kimball-style star schemasDashboards, BI tools, downstream productsNo — rebuildable from silver

The critical insight is that each layer describes a different contract with its readers, not a different physical location. bronze is a re-fetchable staging surface; silver is the operationally valuable historized truth; gold is the disposable, rebuildable presentation layer. Schemas named after layers make that contract visible in every fully qualified table name.

The dbo schema

dbo (short for database owner) is the default schema SQL Server creates in every database and assigns to every new user that does not have an explicit default schema. When a user issues CREATE TABLE foo without a schema qualifier, SQL Server places foo in dbo unless the user’s default schema has been changed.

This default behavior is the root cause of dbo sprawl: over time, every ad-hoc table, every demo, every temporary support object, and every legacy import accumulates in dbo because no one had to think about where it should go. Once that has happened, schema-level security becomes impossible (there is no layer boundary to grant against), discoverability collapses (there is no semantic clue in the fully qualified name), and cleanup becomes risky (no one knows which dbo tables are still in use).

Schema-per-layer is the structural defense against dbo sprawl: if bronze, silver, and gold exist from day one and production objects are placed in them from day one, dbo never becomes a dumping ground.

The schema as a namespace boundary

A SQL Server schema is first and foremost a namespace: a container that gives objects a fully qualified two-part name (schema.object). Two tables with the same name can coexist in different schemas (bronze.ohlcv and silver.ohlcv) and are unambiguously different objects.

The schema is not a physical container — it does not imply a separate file group, backup unit, or security boundary unless the operator chooses to create one. It is purely a logical grouping that shows up in system catalog views (sys.schemas, sys.tables.schema_id) and in every reference to the object.

The schema as a permission scope

SQL Server allows permissions to be granted, denied, or revoked at four class levels: the server, the database, the schema, and the individual object. The schema-level class is identified in sys.database_permissions as class = 3 with class_desc = 'SCHEMA'. When a permission is granted at the schema level, it automatically applies to every object inside that schema, including objects that do not yet exist.

The scope qualifier for schema-level grants is SCHEMA::schema_name:

  • GRANT SELECT ON SCHEMA::gold TO dashboard_reader grants SELECT on every current and future table in gold.
  • DENY SELECT ON SCHEMA::silver TO dashboard_reader denies SELECT on every current and future table in silver, and that DENY takes precedence over any GRANT the principal might inherit from another role.

The :: scope qualifier is required for schema-level grants — without it, SQL Server interprets the statement as an object-level grant and fails if no object with that name exists.

Schema ownership via AUTHORIZATION

Every schema has exactly one owner, recorded in sys.schemas.principal_id (which joins to sys.database_principals.principal_id). The owner is set either implicitly (the default is the creator) or explicitly with CREATE SCHEMA name AUTHORIZATION owner_name. Ownership can be transferred later with ALTER AUTHORIZATION ON SCHEMA::name TO new_owner.

Schema ownership has three operational consequences:

  • The schema owner retains CONTROL permission on every object in the schema, regardless of who created the object.
  • Objects created inside the schema have NULL in sys.objects.principal_id, which means they inherit the schema owner for ownership-chain purposes.
  • Ownership chaining (where permissions on a view or stored procedure implicitly cover the tables it touches) only works cleanly when all objects share the same owner — which is almost always the case when the schema owner owns everything inside it.

In practice: when a schema boundary is aligned to a team or a function, set the owner to match. When a schema is just a layer name in a single-team database, the default dbo owner is correct.

Live Baseline

The current stoxx database already shows why schema layering matters. It has a real bronze / silver / gold backbone, but it also has a large dbo surface carrying demos, support tables, and mixed-purpose objects. This section captures two audit queries against the live instance: one aggregate view of every relevant schema, and one representative sample of the tables that sit in each layer.

Current schema footprint in stoxx

The first audit establishes a baseline: how many tables live in each of the four relevant schemas, how much data they hold, how many are demo-shaped, and whether any schema-level permissions are in place.

Inspect the schema footprint

At the very start of any schema-design review, before deciding whether to keep, split, or consolidate existing schemas. It is typically triggered by A new engineer joining the platform, a pre-migration audit, or a governance review triggered by growth of dbo. Read-only T-SQL query against sys.schemas, sys.tables, sys.partitions, and sys.database_permissions. Requires VIEW DEFINITION on the database or membership in a role that implies it. No state change. Produce a single-row-per-schema summary that exposes the layer structure, the workload scale in each layer, the governance risk (demo tables in dbo), and the current schema-level security posture in one output.

The query touches four catalog views. Before running it, make sure the column semantics are clear: every field in the SELECT list either comes from a catalog view directly or is a computed aggregate derived from one.

FieldSourceType / UnitMeaning
schema_namesys.schemas.namesysnameLogical name of the schema, unique within the database.
schema_ownerUSER_NAME(sys.schemas.principal_id)nvarchar(128)Database principal that owns the schema. Resolved via USER_NAME() so the output is a readable name, not a raw id.
table_countCOUNT(DISTINCT sys.tables.object_id)integerDistinct user tables belonging to this schema. DISTINCT is required because the LEFT JOIN to sys.partitions multiplies rows for partitioned tables.
total_rowsSUM(sys.partitions.rows) filtered on index_id IN (0,1)bigintSum of row counts across the heap (index_id = 0) or clustered index (index_id = 1) of each table. COALESCE(...,0) replaces NULL with 0 for empty schemas.
demo_table_countSUM(CASE WHEN sys.tables.name LIKE 'demo[_]%' THEN 1 ELSE 0 END)integerNumber of tables whose name begins with the literal prefix demo_. The [_] escape prevents the _ wildcard from matching arbitrary characters.
explicit_schema_permission_rowsSUM(CASE WHEN sys.database_permissions.class = 3 THEN 1 ELSE 0 END)integerNumber of rows in sys.database_permissions attached to this schema as a securable. class = 3 is the schema class.
sys.partitions.index_idsys.partitionsintRow-count source selector: 0 = heap, 1 = clustered index, >1 = non-clustered indexes. Using IN (0,1) avoids double-counting rows across non-clustered index copies.
sys.database_permissions.classsys.database_permissionstinyintPermission class identifier: 0 = Database, 1 = Object/Column, 3 = Schema, 4 = Database Principal, and so on. Full enumeration in sys.database_permissions.
sys.database_permissions.major_idsys.database_permissionsintID of the securable. When class = 3, major_id is the schema_id.
SELECT
    s.name AS schema_name,
    USER_NAME(s.principal_id) AS schema_owner,
    COUNT(DISTINCT t.object_id) AS table_count,
    COALESCE(SUM(CASE WHEN p.index_id IN (0,1) THEN p.rows END), 0) AS total_rows,
    SUM(CASE WHEN t.name LIKE 'demo[_]%' THEN 1 ELSE 0 END) AS demo_table_count,
    SUM(CASE WHEN dp.class = 3 THEN 1 ELSE 0 END) AS explicit_schema_permission_rows
FROM sys.schemas AS s
LEFT JOIN sys.tables AS t
  ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions AS p
  ON t.object_id = p.object_id
LEFT JOIN sys.database_permissions AS dp
  ON dp.class = 3
 AND dp.major_id = s.schema_id
WHERE s.name IN ('dbo','bronze','silver','gold')
GROUP BY s.name, s.principal_id
ORDER BY CASE s.name
    WHEN 'bronze' THEN 1
    WHEN 'silver' THEN 2
    WHEN 'gold' THEN 3
    WHEN 'dbo' THEN 4
    ELSE 5
END;
schema_nameschema_ownertable_counttotal_rowsdemo_table_countexplicit_schema_permission_rows
bronzedbo123030700
silverdbo722410200
golddbo3616200
dbodbo191703099140

The live layout is a mostly-correct layered design with one clear weakness: bronze, silver, and gold exist and already carry the real medallion flow, but dbo is still the largest schema by row count and contains 14 demo tables. In a production warehouse, that is a governance smell — dbo should not become the place where unrelated operational, demo, and fallback objects accumulate indefinitely. The explicit_schema_permission_rows = 0 column shows that even though the layer boundary exists structurally, it is not yet being used as a security boundary.

ColumnObserved valueWatchMeaningImplication
schema_name present for bronze, silver, gold3 rowsHealthyAll three medallion layers are structurally present.Good foundation — no CREATE SCHEMA work required before layer-based security can be adopted.
schema_ownerdbo for every schemaHealthy in a single-team sandbox; noteworthy in a multi-team platformEvery schema is owned by the default dbo user.Acceptable here because the platform is single-admin. In a multi-team platform, ownership should reflect which team controls the layer.
table_count in bronze/silver/gold12 / 7 / 3HealthyNarrowing from bronze to gold shows the expected distillation shape of a medallion flow.Layer intent is visible in the population.
table_count in dbo19Warningdbo holds more tables than any single layer schema.Most of these are demo and legacy. New production objects must stop landing here.
total_rows in dbo1,703,099Warningdbo carries more rows than all three layer schemas combined.Row volume is concentrated outside the layer model — harder to reason about backup priorities, access control, and lineage.
demo_table_count in dbo14Warning in production, expected in a labDemos mixed with production-shaped objects.Fine for this sandbox. For promotion to production, demos should move to an isolated demo or lab schema, not stay in dbo.
explicit_schema_permission_rows0 across all four schemasWarning in a production warehouseNo schema-level permissions are in effect.The database is ready for schema-based security but is not using it yet. See Cross-Schema Security for the repair pattern.

dbo carrying the largest row count is a governance smell. In a production data platform, the layer schemas should dominate row counts because the business data lives there. When dbo carries more rows than bronze + silver + gold combined, it means the default schema has become a fallback dumping ground — and future grants, audits, and cleanup tasks will all suffer. Treat dbo as a system-owned namespace only. Put every production-facing object in the layer schema that matches its maturity. Keep demos in an explicit demo schema so they can be dropped in one command without touching production-shaped tables. The full security argument for this rule is covered in Cross-Schema Security below.

Representative table layout by schema

The previous query aggregated by schema. The next query drops to the table level so the layer semantics are visible row by row: what does a typical bronze table look like, what does silver look like, what does gold look like.

Inspect representative tables per layer

Immediately after the schema footprint audit, when the aggregate numbers have raised a question about what actually lives in each layer. It is typically triggered by A discussion about whether a table is in the wrong layer, or a cleanup review of dbo. Read-only T-SQL query. Uses a CTE plus TOP (20) to keep the sample short enough to read without losing the cross-layer comparison. No state change. Show that the live row counts align with the intended medallion semantics: small raw landings in bronze, larger cleaned history in silver, and compact presentation tables in gold.

FieldSourceType / UnitMeaning
schema_namesys.schemas.namesysnameSchema containing the table.
table_namesys.tables.namesysnameName of the user table, unqualified.
row_countSUM(sys.partitions.rows) filtered on index_id IN (0,1)bigintTotal rows across the heap or clustered index of the table. Non-clustered index rows are excluded by the index_id IN (0,1) filter to avoid double-counting.
CTE row_countsIntermediate result—Per-table row counts, computed once so the outer SELECT TOP (20) ... ORDER BY can stably break ties.
WITH row_counts AS (
    SELECT
        s.name AS schema_name,
        t.name AS table_name,
        SUM(p.rows) AS row_count
    FROM sys.tables AS t
    JOIN sys.schemas AS s
      ON t.schema_id = s.schema_id
    JOIN sys.partitions AS p
      ON t.object_id = p.object_id
     AND p.index_id IN (0,1)
    WHERE s.name IN ('bronze','silver','gold','dbo')
    GROUP BY s.name, t.name
)
SELECT TOP (20)
    schema_name,
    table_name,
    row_count
FROM row_counts
ORDER BY CASE schema_name
    WHEN 'bronze' THEN 1
    WHEN 'silver' THEN 2
    WHEN 'gold' THEN 3
    WHEN 'dbo' THEN 4
    ELSE 5
END,
row_count DESC,
table_name;
schema_nametable_namerow_count
bronzetrading_calendar29335
bronzedim_country212
bronzeindex_dim169
bronzesignals_daily169
bronzesignals_quarterly169
bronzeeurostoxx50_ohlcv50
bronzestoxxasia50_ohlcv50
bronzestoxxusa50_ohlcv50
bronzepulse40
bronzepulse_tickers40
bronzeoil20_ohlcv19
bronzedim_index4
silvereurostoxx50_ohlcv67155
silverstoxxusa50_ohlcv66000
silverstoxxasia50_ohlcv64875
silveroil20_ohlcv25080
silversignals_daily635
silversignals_quarterly188
silverindex_dim169
goldindex_performance5351

The live row counts align with the intended layer semantics. bronze is small and source-shaped — trading_calendar at 29,335 rows dominates, every OHLCV landing holds only 50 rows because it is a latest-snapshot surface, and reference tables are tiny. silver carries the cleaned OHLCV history with tens of thousands of rows per index, because it is the historized source of truth. gold appears at the bottom with a single 5,351-row index_performance table because presentation tables are compact by design. That is exactly the pattern schema-per-layer is supposed to make obvious: the fully qualified name encodes the maturity level, and the row-count distribution confirms it.

ColumnObserved rangeWatchMeaningImplication
schema_name = 'bronze', per-table row counts4 to 29,335HealthyRaw landings plus small reference data. trading_calendar is the largest by design (calendar across exchanges over a multi-year span).Layer intent honored.
schema_name = 'silver', per-table row counts169 to 67,155HealthyLarge cleaned fact tables dominate. Reference tables (index_dim at 169) are carried through for joins.Layer intent honored — historized facts are where the row volume lives.
schema_name = 'gold', per-table row counts5,351 (single table in the sample)HealthyCompact, presentation-shaped.Layer intent honored — downstream consumers do not have to scan the fact table history to get to the answer.
dbo rows outside the top 20Not shown here — but the footprint query above reported 1,703,099 totalWarning in productionBulk dbo rows live in demo_idxmaint_* tables seeded from the 04-Databases/01-SQL-Server/03-Query-Writing-and-Optimization/ chapter.Disposable by design, but should not sit next to production-shaped objects long-term.

Schema-per-Layer (Default Recommendation)

For a single SQL Server database serving one pipeline system, schema-per-layer should be the default until a real operational boundary forces something else. The pattern is cheap to adopt, cheap to evolve into more elaborate variants later, and structurally prevents dbo sprawl.

Create the medallion schemas

The three DDL statements below each create one layer schema if it does not already exist. They are written idempotently so they can be replayed on a database that already has some of the schemas present — a common situation when adopting the pattern on a brown-field system.

Create the bronze schema idempotently

Initial platform bootstrap, or when adopting schema-per-layer on an existing database that still has production objects in dbo. It is typically triggered by A decision to stop creating new objects in dbo and start placing them in layer schemas. T-SQL DDL against the current database. Requires CREATE SCHEMA permission. State-changing but idempotent: safe to re-run because the existence check guards the CREATE. Guarantee that a schema named bronze exists before any downstream DDL references bronze.<table>.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'bronze')
    EXEC('CREATE SCHEMA bronze');
GO
(no result set)

Create the silver schema idempotently

Immediately after bronze is in place, as part of the same bootstrap batch. It is typically triggered by same as bronze — a decision to adopt schema-per-layer. T-SQL DDL, idempotent, same permission requirements as the bronze step. Guarantee that a schema named silver exists for cleaned, deduplicated, conformed tables.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'silver')
    EXEC('CREATE SCHEMA silver');
GO
(no result set)

Create the gold schema idempotently

After bronze and silver are in place. It is typically triggered by same bootstrap — or, on an older database, the moment someone decides to build a published reporting surface. T-SQL DDL, idempotent. Guarantee that a schema named gold exists for published analytical tables.

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'gold')
    EXEC('CREATE SCHEMA gold');
GO
(no result set)

Verify the three layer schemas exist

Immediately after the three idempotent creates, to confirm the bootstrap was effective. It is typically triggered by completion of any schema bootstrap or restore. Read-only query against sys.schemas. No state change. Produce a three-row verification capture that proves the layer skeleton is in place and shows each schema’s id and owner.

FieldSourceType / UnitMeaning
schema_namesys.schemas.namesysnameLogical schema name.
schema_idsys.schemas.schema_idintInternal id. Useful to prove a schema was newly created versus carried over, because new schemas get higher ids.
owner_nameUSER_NAME(sys.schemas.principal_id)nvarchar(128)Database principal that owns the schema. dbo for platform-level schemas unless otherwise specified.
SELECT
    s.name AS schema_name,
    s.schema_id,
    USER_NAME(s.principal_id) AS owner_name
FROM sys.schemas AS s
WHERE s.name IN ('bronze','silver','gold')
ORDER BY CASE s.name WHEN 'bronze' THEN 1 WHEN 'silver' THEN 2 WHEN 'gold' THEN 3 END;
schema_nameschema_idowner_name
bronze5dbo
silver6dbo
gold7dbo

All three layer schemas exist in the live stoxx database, owned by dbo, with contiguous ids 5, 6, 7. The contiguous ids are the signature of a single bootstrap pass — the three schemas were created in order with no user-defined schemas created in between. dbo ownership is appropriate here because this is a single-admin sandbox. In a multi-team platform, the owners would typically be a role such as etl_admin created specifically to own the layer schemas.

Why schema-per-layer is still the best default:

  • Query intent is obvious in every reference: silver.signals_daily tells the reader more than dbo.signals_daily.
  • Schema-level grants are simple and durable. A single GRANT SELECT ON SCHEMA::gold TO dashboard_reader covers every current and future table in gold, with no per-table maintenance.
  • Layer-wide review, retention, and cleanup become possible without parsing table-name prefixes.
  • Moving later from schema-per-layer to a more elaborate variant (for example, adding domain schemas on top) is cheap. Cleaning up years of accumulated dbo sprawl is not.

Separate Databases per Layer

Use separate databases only when the split is driven by a real operational boundary: different recovery model, different admin domain, different compliance perimeter, or different restore lifecycle. Splitting a single platform across multiple databases is a significant complexity cost — it has to be justified by a concrete operational win that a single database cannot deliver.

Database-per-layer is not a tidiness pattern

Do not split layers into separate databases just because the names look tidy or because it matches a lakehouse folder layout. Cross-database querying, deployment, testing, and ownership become more complex immediately: three-part names everywhere, harder constraint and trigger modeling, more involved backup coordination, and extra security boundaries that have to be re-configured every time a new user joins.

Split when the operational boundary is real

Use separate databases when you genuinely need different backup-chain behavior, different restore isolation, a distinct compliance perimeter, or tenant/security boundaries that a single database cannot express cleanly. In those cases, the extra complexity is paid for by the operational win.

Recovery-model split across layer databases

The most concrete and common reason to split layers across databases is recovery model. SQL Server supports three recovery models — SIMPLE, BULK_LOGGED, and FULL — and each has different log-chain semantics. Because recovery model is set at the database level, applying different policies to different layers requires different databases.

Recovery modelLog-chain behaviorPoint-in-time recoveryTypical use
SIMPLELog truncates automatically at each checkpoint; no log backups are needed or possible.NoRe-loadable data, dev sandboxes, disposable staging.
BULK_LOGGEDLog is minimally logged for bulk operations; log backups are still required.Partially — point-in-time recovery works except during bulk-logged intervals.Large-scale loads with periodic heavy bulk operations.
FULLEvery change is fully logged; log backups are required to keep the log from growing unbounded.YesHistorized, operationally valuable data that must be recoverable to any point in time.

Set different recovery models per layer database

Reach for this material when during platform bootstrap of a database-per-layer design, after the layer databases have been created. It usually becomes relevant when A decision to split layers across databases because the layers have materially different restore obligations. T-SQL DDL against master or the instance level. Requires ALTER permission on each target database. State-changing. Changing recovery model affects log-chain behavior immediately; changes from SIMPLE to FULL require a subsequent full backup before point-in-time recovery becomes available. Not executed against the live stoxx sandbox because this instance runs a single database for teaching purposes — changing its recovery model would break the Docker image’s log-chain expectations. The example below is presented as a reference pattern.

SettingWhat it controlsPossible valuesProduction guidance
RECOVERYLog-chain behavior for the databaseSIMPLE, BULK_LOGGED, FULLChoose by restore obligation, not by aesthetics. SIMPLE for re-loadable data, FULL for historized truth, BULK_LOGGED only for databases with a predictable heavy-bulk window and a tolerance for partial point-in-time recovery.
ALTER DATABASE bronze_db SET RECOVERY SIMPLE;
ALTER DATABASE silver_db SET RECOVERY FULL;
ALTER DATABASE gold_db   SET RECOVERY SIMPLE;

Choose this when:

  • bronze is re-fetchable and does not need point-in-time recovery — a full re-fetch from upstream is the accepted recovery path.
  • silver is historized and operationally valuable enough to justify FULL plus a log-backup chain.
  • gold is fully rebuildable from silver and does not justify its own log-backup chain.

Do not choose it when:

  • All layers live on the same host, same team, same restore playbook — the layers have the same restore obligations and the split buys nothing.
  • The split exists only to imitate a lakehouse folder pattern — folder-shaped layouts do not map onto SQL Server’s database boundary cleanly.
  • The team is not prepared to manage cross-database deployment, security, and referential integrity explicitly.

Schema-per-Domain

Domain schemas are useful when teams genuinely own their data products end to end — they are responsible for both the ingestion and the published surface, and their deployment lifecycle is independent of other teams. Domain schemas are not a substitute for layer boundaries; they are an ownership overlay that must still express the maturity level of the data inside.

Data-mesh literature (Dehghani and others) pushes this further and argues that domain ownership should be the primary axis: the people closest to the data own both its operational and analytical forms, and share it through contracts. In a SQL Server platform that is not a pure mesh, the pragmatic compromise is to keep layer schemas as the primary axis in a single-team system and add domain schemas only when organizational ownership actually diverges.

Create domain-owned schemas

The DDL below creates three domain schemas, each with an explicit owner via the AUTHORIZATION clause. In a real platform, the owner is usually a database role rather than an individual user, so that team membership changes do not require re-owning every schema.

Create a finance-owned schema

When a finance team is about to take ownership of its own data products and wants a namespace that reflects that ownership. It is typically triggered by A reorganization that gives a team end-to-end responsibility for an area of data. T-SQL DDL. Requires CREATE SCHEMA permission and IMPERSONATE permission on the owner principal (or membership in the target role, if the owner is a role). State-changing. Establish a schema whose owner is the team responsible for the data that lives in it, so ownership, permissions, and object lifetimes align with the team boundary.

SettingWhat it controlsAccepted valuesProduction guidance
AUTHORIZATION owner_namePrincipal that owns the new schemaDatabase user, database role, or application rolePrefer a database role so team membership can change without re-owning the schema. On single-admin sandboxes, falling back to dbo is acceptable but provides no ownership signal.
CREATE SCHEMA demo_finance AUTHORIZATION dbo;
(no result set)

Create an operations-owned schema

Immediately after the finance schema, as part of the same bootstrap pass. It is typically triggered by same domain-ownership adoption. T-SQL DDL, same permission requirements. State-changing. Establish a second domain schema so the pattern is verifiable with multiple owners.

CREATE SCHEMA demo_operations AUTHORIZATION dbo;
(no result set)

Create a research-owned schema

Immediately after the operations schema. It is typically triggered by same domain-ownership adoption. T-SQL DDL, same permission requirements. State-changing. Establish a third domain schema so the verification query below returns a meaningful multi-row result.

CREATE SCHEMA demo_research AUTHORIZATION dbo;
(no result set)

Verify the domain schemas and their owners

Immediately after the three domain creates, to confirm the bootstrap was effective and the ownership is what was intended. It is typically triggered by any time a domain-ownership boundary is introduced or changed. Read-only query against sys.schemas. No state change. Show that each expected schema exists and is owned by the expected principal — the single place where ownership intent becomes verifiable live state.

FieldSourceType / UnitMeaning
schema_namesys.schemas.namesysnameDomain schema name.
schema_idsys.schemas.schema_idintInternal id. High ids confirm this is a freshly created schema rather than a system schema.
owner_nameUSER_NAME(sys.schemas.principal_id)nvarchar(128)Resolved owner name. Should match the AUTHORIZATION principal from the CREATE SCHEMA statement.
SELECT
    s.name AS schema_name,
    s.schema_id,
    USER_NAME(s.principal_id) AS owner_name
FROM sys.schemas AS s
WHERE s.name IN ('demo_finance','demo_operations','demo_research')
ORDER BY s.name;
schema_nameschema_idowner_name
demo_finance9dbo
demo_operations10dbo
demo_research11dbo

All three domain schemas were created successfully and received contiguous schema ids 9, 10, 11 (the next ids above the bronze/silver/gold block at 5/6/7). Each one reports dbo as the owner because the sandbox does not have dedicated domain-owner roles; in a real platform the owner_name column would carry the team role name and would be the primary value this query exists to verify.

Clean up the demo domain schemas

Immediately after the verification capture, so the sandbox does not accumulate demonstration-only schemas. It is typically triggered by end of a demo or documentation run. T-SQL DDL. Requires CONTROL on each schema (inherited from dbo ownership). State-changing — the schemas must be empty, or DROP SCHEMA fails. Return the live instance to the pre-demo state so subsequent audits against sys.schemas are not polluted.

DROP SCHEMA demo_research;
DROP SCHEMA demo_operations;
DROP SCHEMA demo_finance;
(no result set)

Keep the layer meaning visible

Domain schemas succeed only when the layer semantics stay visible. If a domain schema hides which data is raw, which is cleaned, and which is published, consumers lose the maturity signal that made schema-per-layer valuable in the first place.

Two acceptable patterns, one anti-pattern

When adopting domain schemas, keep the maturity signal in either the object name or the schema name:

  • Layer prefix on object names. finance.bronze_trades, finance.silver_trades, finance.gold_positions. Compact; one schema per team; layer is visible in the first component of every table name.
  • Domain-and-layer schema names. finance_bronze, finance_silver, finance_gold. Verbose but unambiguous; layer is visible in the schema name itself. Preferred when ownership clarity matters more than compact names.

The anti-pattern is a domain schema where the layer is implicit: finance.trades with no indication of whether this is raw, cleaned, or published. Readers have to open the definition (or read a doc) to find out.

Schema-per-Source for Staging

Schema-per-source is a staging-only pattern. It is useful when multiple upstream systems land with different refresh schedules, data quality quirks, file formats, or column naming conventions. Each source gets its own isolated landing namespace so source-specific mess does not pollute the bronze layer.

The pattern layers on top of schema-per-layer — it does not replace it. The downstream contract is still that every source-scoped staging table normalizes into a common bronze.<table> surface before it is consumed by the rest of the pipeline.

Create source-scoped staging schemas

The DDL below creates three staging schemas, one per upstream source. Each schema is a dedicated landing namespace where the ingestion pipeline can write source-specific column shapes without a naming conflict.

Create a staging schema for the yfinance source

During the initial configuration of a new upstream source, before any ingestion job writes landing data. It is typically triggered by A new upstream feed is about to be onboarded. T-SQL DDL. Requires CREATE SCHEMA permission. State-changing. Isolate the yfinance upstream’s landing tables from every other source, so column renames, schema drift, and ingestion quirks from yfinance never collide with other upstreams.

CREATE SCHEMA demo_stg_yfinance;
(no result set)

Create a staging schema for the bloomberg source

During onboarding of a second upstream source. It is typically triggered by A new upstream feed is about to be onboarded. T-SQL DDL, same permission requirements. State-changing. Second staging schema to demonstrate multi-source isolation.

CREATE SCHEMA demo_stg_bloomberg;
(no result set)

Create a staging schema for manual uploads

When a platform needs to accept occasional manual uploads (CSV drops, one-off spreadsheets, vendor deliveries) that are not tied to any automated feed. It is typically triggered by recognition that manual uploads are a recurring need and deserve their own boundary. T-SQL DDL, same permission requirements. State-changing. Capture manual uploads in a bounded namespace that can be cleaned up or audited separately from automated landings.

CREATE SCHEMA demo_stg_manual;
(no result set)

Verify the source-scoped staging schemas

Immediately after the three staging schemas are created. It is typically triggered by completion of source-schema bootstrap. Read-only query against sys.schemas. No state change. Confirm that each source schema exists and is owned by the expected principal, as a single verification capture.

FieldSourceType / UnitMeaning
schema_namesys.schemas.namesysnameSource staging schema name.
schema_idsys.schemas.schema_idintInternal id.
owner_nameUSER_NAME(sys.schemas.principal_id)nvarchar(128)Resolved owner. For staging schemas, the owner is usually an ingestion service account.
SELECT
    s.name AS schema_name,
    s.schema_id,
    USER_NAME(s.principal_id) AS owner_name
FROM sys.schemas AS s
WHERE s.name LIKE 'demo_stg[_]%'
ORDER BY s.name;
schema_nameschema_idowner_name
demo_stg_bloomberg10dbo
demo_stg_manual11dbo
demo_stg_yfinance9dbo

All three staging schemas exist with contiguous ids 9, 10, 11. They reused the same id range the previous demo domain schemas had occupied because those had been dropped between the two demos — schema_id values are reclaimed when a schema is dropped and reassigned to the next CREATE SCHEMA. The alphabetical sort places demo_stg_bloomberg first even though it has id 10, because ordering is on schema_name, not on schema_id.

Clean up the demo staging schemas

Immediately after the verification capture, so the sandbox does not retain demonstration schemas. It is typically triggered by end of the staging-schema demo. T-SQL DDL. Requires CONTROL on each schema. State-changing — DROP SCHEMA fails if the schema contains any object. Return the live instance to the pre-demo state.

DROP SCHEMA demo_stg_manual;
DROP SCHEMA demo_stg_bloomberg;
DROP SCHEMA demo_stg_yfinance;
(no result set)

When schema-per-source is layered on top of schema-per-layer, the complete pipeline namespace becomes:

StageSchemaContentsConsumers
Source landingstg_yfinance, stg_bloomberg, stg_manualUpstream-shaped landing tables, one per sourceIngestion pipelines only
Normalized rawbronze.*Common-shape persisted raw tables, metadata-augmentedDownstream cleaning jobs
Cleanedsilver.*Deduplicated, conformed, historized business-ready tablesAnalytics jobs, semantic models
Publishedgold.*Aggregates, metrics, reporting tablesDashboards, BI tools, downstream products

The key constraint is that stg_* is a pipeline-internal boundary. It exists so ingestion can move quickly without polluting the rest of the database, and it is never exposed to end users — BI tools and dashboards always read from gold, occasionally silver, and never from stg_*.

Control, Audit, And Contract Schemas

Layer schemas explain data maturity, but they do not solve every architectural boundary. Production platforms also need a place for pipeline control state, quality events, and stable consumer contracts. Those objects should not be scattered through dbo, and they should not be mixed into bronze, silver, or gold when they serve a different operational purpose — they describe the pipeline itself, not the business data flowing through it.

Readiness check for control-plane schemas

The first step before adopting a control, audit, or contract schema is to find out which of them already exist. A single query against SCHEMA_ID collapses the check into one readable row.

Check whether control-plane schemas exist

Before planning any control, audit, or contract schema adoption, or during a platform maturity audit. It is typically triggered by any initiative that wants to add watermark tables, run ledgers, or stable contract views to the platform. Read-only query that calls SCHEMA_ID() once per candidate schema. Uses CASE ... IS NULL to produce a 0/1 readiness flag for each. No state change. Produce a one-row readiness checklist for five common supporting schemas, so the reader knows at a glance which of them are already in place and which would be new adoptions.

FieldSourceType / UnitMeaning
meta_schema_existsSCHEMA_ID('meta')bit-shaped int0 if meta is absent, 1 if present. Intended to hold watermarks, run ledgers, dependency state, and schema contracts.
control_schema_existsSCHEMA_ID('control')bit-shaped int0/1 presence flag for a schema named control. Alternative name for the same control-plane role as meta.
audit_schema_existsSCHEMA_ID('audit')bit-shaped int0/1 presence flag for a schema named audit. Intended for retained operational evidence and quality events.
history_schema_existsSCHEMA_ID('history')bit-shaped int0/1 presence flag for a schema named history. Intended for explicit temporal or CDC-facing history surfaces.
contract_schema_existsSCHEMA_ID('contract')bit-shaped int0/1 presence flag for a schema named contract. Intended for stable consumer-facing views or synonyms.
SELECT
    CASE WHEN SCHEMA_ID('meta') IS NULL THEN 0 ELSE 1 END AS meta_schema_exists,
    CASE WHEN SCHEMA_ID('control') IS NULL THEN 0 ELSE 1 END AS control_schema_exists,
    CASE WHEN SCHEMA_ID('audit') IS NULL THEN 0 ELSE 1 END AS audit_schema_exists,
    CASE WHEN SCHEMA_ID('history') IS NULL THEN 0 ELSE 1 END AS history_schema_exists,
    CASE WHEN SCHEMA_ID('contract') IS NULL THEN 0 ELSE 1 END AS contract_schema_exists;
meta_schema_existscontrol_schema_existsaudit_schema_existshistory_schema_existscontract_schema_exists
00000

stoxx currently has none of these supporting schemas, which keeps the model simple but also means there is no explicit home yet for control tables, quality events, or stable consumer-facing abstractions. On a single-admin sandbox that is acceptable — the cost of adopting a control-plane schema is not yet paid off by a matching operational benefit. If the platform grows beyond a single-admin sandbox, this absence becomes an architectural decision rather than a neutral default, and the next two subsections describe when each of these schemas starts to pay for itself.

Add a meta or control schema for pipeline state

The control plane of a data platform is not the same thing as the data layers. A meta or control schema is where the platform stores state about the pipeline itself rather than business data flowing through the pipeline. Typical contents cluster around four object families, all of which share the property that they describe the pipeline, not the data.

Object familyTypical contentsWhy it belongs outside bronze / silver / gold
WatermarksLast successful processed date, LSN, or rowversion per pipeline stepIt describes pipeline progress, not business data
Run ledgerRun ids, start/end time, row counts, status, error summaryIt is operational history for the ETL system
Schema contractsExpected source columns, allowed type changes, approval stateIt governs writes rather than serving analytics directly
Quality eventsFailed checks, offending keys, reconciliation resultsIt is back-room operational evidence, not end-user gold data

Create a control-plane schema

When the platform starts to need durable, queryable state about its own pipeline — typically the first time the operator needs to answer “did the last run succeed?” without reading logs. It is typically triggered by adoption of a run-ledger, watermark, or schema-contract table; or a governance requirement that the pipeline’s operational state be queryable like any other dataset. T-SQL DDL. Requires CREATE SCHEMA permission. State-changing. Establish a dedicated namespace for control-plane tables so they do not mix with either business data (bronze/silver/gold) or ad-hoc objects (dbo).

CREATE SCHEMA demo_meta;
(no result set)

Verify the control-plane schema exists

Immediately after the control-plane schema is created. It is typically triggered by completion of the control-plane bootstrap. Read-only query against sys.schemas. No state change. Confirm the schema exists with the expected name, id, and owner before any table is placed in it.

SELECT
    s.name AS schema_name,
    s.schema_id,
    USER_NAME(s.principal_id) AS owner_name
FROM sys.schemas AS s
WHERE s.name = 'demo_meta';
schema_nameschema_idowner_name
demo_meta9dbo

The schema was created successfully with schema_id = 9, matching the pattern of every fresh schema in this note (the schema_id sequence reuses ids released by previous DROP SCHEMA statements). Ownership is dbo because no dedicated control-plane role exists on the sandbox; in a real platform the owner should be a role such as platform_ops or etl_admin so control-plane objects inherit the right permission profile.

Clean up the control-plane demo schema

After the verification capture, to leave the sandbox in its pre-demo state. It is typically triggered by end of the control-plane demo. T-SQL DDL. Requires CONTROL on the schema. State-changing. Drop the demo schema so subsequent audits do not see it.

Drop the demo control-plane schema.

DROP SCHEMA demo_meta;
(no result set)

Separate audit or history surfaces from the core medallion flow

An audit or history schema is useful when the platform must expose retained operational evidence or explicit history surfaces that are not the same as the medallion layers. The boundary is about consumer expectation: when a reader queries gold.<table>, they expect published, current-state, business-shaped data; they do not expect to have to navigate around legal audit artifacts or raw CDC-facing helpers.

Use an audit or history schema when:

  • Quality and reconciliation events need retention beyond the run ledger’s operational window.
  • Temporal or CDC history needs to be exposed in a curated form, distinct from the live table it was captured from.
  • Legal or operational audit tables exist that should not sit beside the published gold model.

Avoid it when:

  • The only reason is aesthetic symmetry with another platform or folder layout.
  • The history is already handled correctly inside a table’s own design — a system-versioned temporal table in silver or a well-scoped SCD2 dimension does not need a separate history schema.

Expose stable contract views when physical tables keep evolving

A contract schema is often the cleanest answer when consumer-facing names must stay stable while the underlying physical tables continue to evolve. Instead of exposing the physical gold.index_performance table directly, the platform exposes contract.index_performance as a thin view over whatever physical table currently holds the data. When the physical table is refactored, renamed, or replaced, the view is updated in place and no downstream consumer is affected.

Good fits:

  • Views that preserve a public column contract while silver or gold tables are refactored.
  • Synonyms or narrow views that hide source-system churn from downstream tools.
  • Semantic serving surfaces that should not expose internal helper columns such as _batch_id or _ingested_at.

This is not a replacement for gold. The pattern is:

  • gold stores the published physical model and is the object contract views read from.
  • contract exposes the stable consumer-facing abstraction when that extra decoupling is justified.

The Proxy pattern from Data Engineering Design Patterns makes this concrete: contract.devices is a view over gold_internal.devices_20260101, and when the physical table is rebuilt as gold_internal.devices_20260401, the view’s definition is updated to point at the new physical table. Consumers always read contract.devices; the DBA swaps the underlying table as part of a routine refactor.

Naming and Metadata Rules

Schema design fails when the naming inside the schema is inconsistent. The schema boundary gives the reader a maturity signal; the table and column names have to carry the rest of the meaning, and every inconsistency in naming accumulates into a slow tax on everyone who has to read the model later.

Recommended defaults:

  • Keep table names business-oriented, not tool-oriented. Table names outlive the tool that built them.
  • Use schema names to express the layer instead of prefixes like raw_ or stg_ on every table. If the schema already says bronze, the table name should not also say raw_.
  • Keep metadata columns explicit and consistent across every landing table: _ingested_at, _source_file, _batch_id, _index. Fix the spelling once and never deviate.
  • Prefer predictable index names: PK_<table> for primary keys, UX_<table>_<cols> for unique indexes, IX_<table>_<cols> for non-unique indexes.

Never name schemas after tools (airflow, dbt, spark, fivetran, airbyte) — tool names change on a timescale of years and the schema becomes meaningless the moment the tool is replaced. Name schemas after the data boundary they represent: bronze, silver, gold, stg_yfinance, finance, audit, contract. Every one of those names still makes sense if the underlying orchestrator is swapped out.

Reserved words in table design

Financial OHLCV models routinely use column names that collide with T-SQL reserved words: open, close, and date are the most common offenders. SQL Server can handle them, but the quoting discipline has to be consistent — every reference to the column, in every query and every DDL statement, must either consistently bracket the name or consistently quote it.

Define a layer table with bracketed reserved-word columns

When defining a new bronze-layer table whose upstream feed uses column names that collide with T-SQL reserved words. It is typically triggered by A first load from a financial data source (yfinance, Bloomberg, Refinitiv) whose canonical column names include date, open, high, low, close. T-SQL DDL against the bronze schema. Requires CREATE TABLE permission on the schema. State-changing. Runs inside the bronze schema to preserve the layer semantics — the schema name carries the layer, the table name stays business-oriented, and the column names stay faithful to upstream. Create a table that preserves the familiar OHLCV column names (so downstream joins and the upstream feed stay readable) while remaining syntactically valid in every query that touches the table.

ColumnData typeNullabilityDefaultReason
symbolvarchar(20)NOT NULL—Business identifier, no quoting needed.
[date]dateNOT NULL—Bracketed because date is a T-SQL function and reserved-like identifier. The bracket form is the safest portable quoting.
[open]floatNULL—Bracketed because OPEN is a T-SQL reserved word (used by cursors).
highfloatNULL—Not reserved. Kept unbracketed for readability.
lowfloatNULL—Not reserved. Kept unbracketed for readability.
[close]floatNULL—Bracketed because CLOSE is a T-SQL reserved word (used by cursors).
volumebigintNULL—Not reserved. Kept unbracketed.
_ingested_atdatetime2NOT NULLSYSUTCDATETIME()Standard ingestion metadata. UTC timestamp of the landing.
CREATE TABLE bronze.demo_ohlcv_reserved
(
    symbol       varchar(20) NOT NULL,
    [date]       date        NOT NULL,
    [open]       float       NULL,
    high         float       NULL,
    low          float       NULL,
    [close]      float       NULL,
    volume       bigint      NULL,
    _ingested_at datetime2   NOT NULL DEFAULT SYSUTCDATETIME()
);
(no result set)

Verify the table’s column definitions

Immediately after the CREATE TABLE, to confirm every column landed with the intended type, nullability, and default. It is typically triggered by any new table creation where nullability or defaults matter — which in practice means most tables. Read-only query against sys.columns joined to sys.default_constraints via parent_object_id/parent_column_id. Uses OBJECT_ID() to resolve the table name once and TYPE_NAME() to resolve the type id to a readable name. No state change. Produce a single-table column reference that proves the reserved-word handling, the nullability, and the ingestion default all survived the DDL.

FieldSourceType / UnitMeaning
column_namesys.columns.namesysnameColumn name as stored in the catalog — without brackets. SQL Server stores the unbracketed identifier; brackets are a query-time quoting choice.
data_typeTYPE_NAME(sys.columns.user_type_id)sysnameResolved SQL Server type name.
max_lengthsys.columns.max_lengthsmallintStorage length in bytes. For varchar, nvarchar, and char, it is the byte length — nvarchar(N) columns report 2*N.
is_nullablesys.columns.is_nullablebit1 if the column allows NULLs, 0 otherwise.
default_definitionOBJECT_DEFINITION(sys.default_constraints.object_id)nvarchar(max)Text of the default constraint bound to the column, or NULL if no default is defined.
SELECT
    c.name AS column_name,
    TYPE_NAME(c.user_type_id) AS data_type,
    c.max_length,
    c.is_nullable,
    OBJECT_DEFINITION(dc.object_id) AS default_definition
FROM sys.columns AS c
LEFT JOIN sys.default_constraints AS dc
  ON dc.parent_object_id = c.object_id
 AND dc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID('bronze.demo_ohlcv_reserved')
ORDER BY c.column_id;
column_namedata_typemax_lengthis_nullabledefault_definition
symbolvarchar20FalseNULL
datedate3FalseNULL
openfloat8TrueNULL
highfloat8TrueNULL
lowfloat8TrueNULL
closefloat8TrueNULL
volumebigint8TrueNULL
_ingested_atdatetime28False(sysutcdatetime())

Two things to notice in the output. First, the column_name values are stored without brackets — date, open, close, not [date], [open], [close]. SQL Server resolves the reserved-word collision only at query-time, when the parser reads the statement. The catalog simply stores the identifier. Second, the default_definition column shows (sysutcdatetime()) for _ingested_at, confirming that the DEFAULT SYSUTCDATETIME() clause was bound as a named default constraint. Every other column has NULL in default_definition because no default was specified.

ColumnObserved valueWatchMeaningImplication
data_type for [date]dateHealthyThe bracketed column name resolved to the date type, not the scalar function.Bracketing was effective — no parser collision.
max_length for varchar(20)20HealthyByte length for a single-byte character encoding.Matches the declaration. If this were nvarchar(20), the value would be 40.
is_nullable for symbol, date, _ingested_atFalseHealthyThe three columns declared NOT NULL enforce non-null.Nullability survived the DDL.
default_definition for _ingested_at(sysutcdatetime())HealthyDefault constraint is bound and the function name matches the declaration.Ingestion jobs can INSERT without specifying _ingested_at and still get a UTC timestamp.
default_definition for every other columnNULLHealthyNo defaults on business columns.Correct by design — business columns should not have silent fallbacks.

Drop the reserved-word demo table

Immediately after the verification capture. It is typically triggered by end of the reserved-word demo. T-SQL DDL. Requires ALTER on the schema or CONTROL on the object. State-changing. Remove the demo table so it does not appear in later audit captures of bronze.

Drop the reserved-word demo table.

DROP TABLE bronze.demo_ohlcv_reserved;
(no result set)

Cross-Schema Security

The live stoxx database is structurally ready for schema-level security — bronze, silver, and gold already exist as explicit layer boundaries — but no schema-level grants or denies are in place. This section shows the baseline check, the live adoption pattern with captured results, and the cleanup.

The central insight is that schemas are SQL Server’s coarsest object-level permission scope. A grant on SCHEMA::gold covers every current and future object in gold with a single statement. That durability is the whole point of schema-per-layer as a security boundary.

Baseline: inspect current schema-level permissions

Before adopting schema-level security, the first step is to check what is already in place. This is the same query used later to capture live grants — run it before any changes are applied to establish the baseline, and again after, to prove the effect.

Inspect explicit schema-level permissions

Before introducing any schema-level grants, to confirm the baseline is empty (or to document what is already in effect). Run again after every change. It is typically triggered by A security audit, or the moment before or after any GRANT, DENY, or REVOKE at the schema class. Read-only query against sys.database_permissions joined to sys.schemas on major_id. Filters on class = 3 so only schema-class permissions are returned. No state change. Produce one row per schema-level permission currently in force, resolving the grantee to a readable name so the output can be audited without cross-referencing ids.

FieldSourceType / UnitMeaning
class_descsys.database_permissions.class_descnvarchar(60)Textual description of the permission class. SCHEMA for this query because of the WHERE class = 3 filter.
schema_namesys.schemas.namesysnameThe schema the permission is attached to, resolved via the join on major_id.
permission_namesys.database_permissions.permission_namenvarchar(128)The action the permission covers: SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, VIEW DEFINITION, ALTER, CONTROL, and others.
state_descsys.database_permissions.state_descnvarchar(60)One of GRANT, GRANT_WITH_GRANT_OPTION, DENY, REVOKE. Describes whether the row is a grant, a denial, or the narrow column-exception case explained below.
grantee_nameUSER_NAME(sys.database_permissions.grantee_principal_id)nvarchar(128)Resolved name of the principal receiving the permission: a database user, a database role, or an application role.
sys.database_permissions.classsys.database_permissionstinyintClass enum: 3 = SCHEMA. Other values in the same view map to database, object/column, principal, assembly, and so on.
sys.database_permissions.major_idsys.database_permissionsintId of the securable; when class = 3, it is the schema_id.
SELECT
    dp.class_desc,
    s.name AS schema_name,
    dp.permission_name,
    dp.state_desc,
    USER_NAME(dp.grantee_principal_id) AS grantee_name
FROM sys.database_permissions AS dp
JOIN sys.schemas AS s
  ON dp.major_id = s.schema_id
WHERE dp.class = 3
ORDER BY grantee_name, s.name, dp.permission_name;

On the live stoxx baseline this query returns zero rows. No schema-level grants or denies are in force before the demo pattern below is applied. That confirms the explicit_schema_permission_rows = 0 finding from the Live Baseline query and gives the next subsection a clean starting point.

The recommended pattern is one database role per service type or reader group, granted at the schema level. Roles own the permission model; principals (users, logins) are added to and removed from roles as staff comes and goes; tables are granted permissions automatically as they are created inside the schema. No per-table, per-user maintenance.

This subsection applies the pattern to the live stoxx instance using demo roles (demo_etl_writer, demo_dashboard_reader) so the baseline query from above can capture real rows. Every step is cleaned up at the end.

Create the ETL writer role

During adoption of schema-level security, before any grants are issued. It is typically triggered by the first pipeline service account that needs broad write access across the bronze and silver layers. T-SQL DDL. Requires CREATE ROLE permission on the database. State-changing. Establish a named container for the ETL service’s permissions so membership can change without re-granting every schema.

CREATE ROLE demo_etl_writer;
(no result set)

Create the dashboard reader role

Immediately after the writer role, as part of the same bootstrap. It is typically triggered by the first reporting account that needs read-only access to the gold layer. T-SQL DDL. Requires CREATE ROLE permission. State-changing. Establish a named container for dashboard and BI reader permissions.

CREATE ROLE demo_dashboard_reader;
(no result set)

Grant bronze layer write access to the ETL role

After both roles exist. It is typically triggered by giving the ETL pipeline the ability to land and correct raw data. T-SQL DDL. Requires either GRANT OPTION on each permission at the schema class, or membership in db_securityadmin/db_owner. State-changing. Cover every current and future table in bronze with full DML rights for the ETL role, in a single statement.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::bronze TO demo_etl_writer;
(no result set)

Grant silver layer write access to the ETL role

After the bronze grant. It is typically triggered by same ETL adoption. T-SQL DDL, same permissions as the bronze grant. State-changing. Extend the same full DML coverage to silver so the ETL pipeline can land and transform cleaned rows.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::silver TO demo_etl_writer;
(no result set)

Grant gold read access to the dashboard role

After the ETL role is fully granted. It is typically triggered by the first reader joining the platform. T-SQL DDL. State-changing. Give the dashboard role read-only access to every current and future table in gold, the published layer.

GRANT SELECT ON SCHEMA::gold TO demo_dashboard_reader;
(no result set)

Deny bronze read access to the dashboard role

Immediately after the gold grant, so there is never a window where the dashboard role can read upstream layers. It is typically triggered by the hard separation rule between published data and upstream raw data. T-SQL DDL. State-changing. DENY takes precedence over any GRANT — this makes it an enforcement mechanism, not a preference. Make it impossible for the dashboard role to read from bronze, even if a future grant accidentally includes it through another role.

DENY is coarse and hard to reverse

DENY beats GRANT in every ownership-chain evaluation. That is exactly what makes it useful as a hard separation rule, and it is also what makes it easy to paint yourself into a corner. Once demo_dashboard_reader is denied SELECT on SCHEMA::bronze, the only ways to restore access are REVOKE (remove the deny) or removing the principal from the role — neither of which is obvious to someone debugging a permission surprise months later. Use DENY only when you mean it as a security boundary, not as a permission preference.

Pair every DENY with a comment explaining the boundary it enforces

When you add a DENY to a production platform, document its intent inline (in a migration script) and in the platform’s permission docs. DENY SELECT ON SCHEMA::bronze TO dashboard_reader -- dashboard readers must never see raw upstream is much easier to reason about a year later than the bare statement.

DENY SELECT ON SCHEMA::bronze TO demo_dashboard_reader;
(no result set)

Deny silver read access to the dashboard role

Immediately after the bronze DENY. It is typically triggered by same hard-separation rule applied to the cleaned layer. T-SQL DDL. State-changing. Extend the bronze DENY to silver so the dashboard role cannot read the cleaned layer either.

DENY SELECT ON SCHEMA::silver TO demo_dashboard_reader;
(no result set)

Re-run the security-surface query against the live grants

Immediately after all grants and denies are in place. It is typically triggered by verification of the adoption batch. Same read-only query as the baseline above, now expected to return 11 rows (4 grants for the writer on bronze × 4 permission types + 4 grants for the writer on silver × 4 permission types + 1 grant for the reader on gold + 2 denies for the reader on bronze and silver). Prove the grants took effect and present the live schema-level security surface for interpretation.

Inspect the schema-level permissions now in force on the live instance.

SELECT
    dp.class_desc,
    s.name AS schema_name,
    dp.permission_name,
    dp.state_desc,
    USER_NAME(dp.grantee_principal_id) AS grantee_name
FROM sys.database_permissions AS dp
JOIN sys.schemas AS s
  ON dp.major_id = s.schema_id
WHERE dp.class = 3
ORDER BY grantee_name, s.name, dp.permission_name;
class_descschema_namepermission_namestate_descgrantee_name
SCHEMAbronzeSELECTDENYdemo_dashboard_reader
SCHEMAgoldSELECTGRANTdemo_dashboard_reader
SCHEMAsilverSELECTDENYdemo_dashboard_reader
SCHEMAbronzeDELETEGRANTdemo_etl_writer
SCHEMAbronzeINSERTGRANTdemo_etl_writer
SCHEMAbronzeSELECTGRANTdemo_etl_writer
SCHEMAbronzeUPDATEGRANTdemo_etl_writer
SCHEMAsilverDELETEGRANTdemo_etl_writer
SCHEMAsilverINSERTGRANTdemo_etl_writer
SCHEMAsilverSELECTGRANTdemo_etl_writer
SCHEMAsilverUPDATEGRANTdemo_etl_writer

The 11 rows confirm the complete schema-level security surface for the two demo roles. The demo_etl_writer rows spell out every permission individually because GRANT SELECT, INSERT, UPDATE, DELETE is expanded into four rows by SQL Server — one row per permission per schema. The demo_dashboard_reader rows show a single GRANT on gold and two DENY rows on bronze and silver, which is exactly the published-consumer pattern the platform is trying to enforce.

ColumnValueWatchMeaningImplication
class_descSCHEMAExpectedEvery row in the output is at the schema class.Confirms the WHERE class = 3 filter worked — no accidental object-level or database-level rows leaking in.
permission_name for demo_etl_writerSELECT, INSERT, UPDATE, DELETEExpectedFour individual rows per granted schema.GRANT on multiple permissions is normalized to one row each in the catalog view.
state_desc = 'GRANT'9 rowsHealthyStandard grants across both roles.Expected majority state for a well-adopted role model.
state_desc = 'DENY'2 rowsIssued explicitly via DENY SELECT ON SCHEMA::bronze/silver TO demo_dashboard_readerThe dashboard role is hard-blocked from bronze and silver.These rows are the core of the separation contract. Any attempt to read from those schemas will fail regardless of other role memberships. Verify with the schema-permissions query above; an accidental DENY would appear as an unexpected row that was not part of the bootstrap batch.
state_desc = 'GRANT_WITH_GRANT_OPTION'0 rowsHealthyNo role has been granted the ability to re-grant these permissions to others.Appropriate — re-granting schema permissions should be a deliberate decision, not a side effect of the default pattern.
state_desc = 'REVOKE'0 rowsHealthyNo column-exception revokes in effect.Simple, unambiguous security surface.

dbo is not a security boundary

If business tables, support tables, and demos all live in dbo, there is no way to grant or deny access to just one of those categories. Permission decisions have to drop to the individual object level, which is brittle, repetitive, and impossible to audit at a glance.

Keep dbo nearly empty in production-facing warehouses

Treat dbo as a system-owned namespace. Utility objects only, or ideally nothing user-facing at all. Every production-facing object should live in a layer schema or a domain schema so permissions can be expressed once at the schema boundary.

Clean up the demo security objects

After capturing the live security surface, every demo object created in this section must be cleaned up. The cleanup is a sequence of four steps:

  1. Revoke all grants and denies — REVOKE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::<schema> FROM <role> for each schema/role pair.
  2. Drop the ETL role — DROP ROLE [demo_etl_writer].
  3. Drop the dashboard role — DROP ROLE [demo_dashboard_reader].
  4. Re-run the baseline permission query — confirm the sys.database_permissions surface returns zero rows for the demo schemas.

Revoke all demo grants and denies

Immediately after the security-surface capture. It is typically triggered by end of the security demo. T-SQL DDL. REVOKE removes both GRANT and DENY rows. Requires the same permissions as the original grants. State-changing. Remove every row in sys.database_permissions that the demo added.

Revoke every demo grant and deny on bronze, silver, and gold.

REVOKE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::bronze FROM demo_etl_writer;
REVOKE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::silver FROM demo_etl_writer;
REVOKE SELECT ON SCHEMA::gold FROM demo_dashboard_reader;
REVOKE SELECT ON SCHEMA::bronze FROM demo_dashboard_reader;
REVOKE SELECT ON SCHEMA::silver FROM demo_dashboard_reader;
(no result set)

Drop the demo ETL writer role

After every grant held by the role has been revoked. It is typically triggered by end of the demo. T-SQL DDL. DROP ROLE fails if the role still owns objects or has members. State-changing. Remove the role so it no longer appears in sys.database_principals.

Drop the demo ETL writer role.

DROP ROLE demo_etl_writer;
(no result set)

Drop the demo dashboard reader role

Immediately after dropping the ETL role. It is typically triggered by same demo cleanup. T-SQL DDL. State-changing. Remove the second demo role.

Drop the demo dashboard reader role.

DROP ROLE demo_dashboard_reader;
(no result set)

Verify the security surface is empty again

After both roles are dropped. It is typically triggered by final cleanup check. Read-only aggregate query against sys.database_permissions. No state change. Prove that the demo introduced no permanent change to the live schema-level security surface.

Count remaining schema-level permissions after cleanup.

SELECT COUNT(*) AS schema_perm_rows_remaining
FROM sys.database_permissions
WHERE class = 3;
schema_perm_rows_remaining
0

The count is 0, which confirms the demo left no permanent grants, denies, or revokes on the live instance. The baseline established at the start of this section is fully restored.

Decision Guide

The five scenarios below cover the vast majority of schema-design decisions on a SQL Server pipeline system. When more than one row applies, work top to bottom — simpler patterns first, more complex ones only when the simpler pattern cannot express the operational boundary.

ScenarioBest patternWhy
Single SQL Server database, one data platform teamSchema-per-layerSimplest, clearest, best security-to-complexity ratio.
Different recovery, restore, or compliance requirements per layerSeparate databases per layerRecovery policy and compliance perimeters are real operational boundaries that schemas cannot express.
Independent domain teams own end-to-end data productsDomain schemas plus visible layer namingOwnership matters, but layer semantics must stay visible — either via prefixed table names or domain-and-layer schema names.
Many upstream sources with different quirks, schedules, or formatsSource-scoped staging (stg_*) plus unified medallion schemasIsolates ingestion noise without polluting the serving model.
Early-stage project with uncertain scopeStart with schema-per-layerEasiest to evolve; every more elaborate variant is cheaper to adopt on top of schema-per-layer than to refactor into from dbo sprawl.

Anti-Patterns

The anti-patterns below are each individually tempting in the short term and each individually painful in the medium term. The common thread is that they sacrifice the schema boundary as a durable organizing principle for a short-term convenience.

Anti-patternWhy it hurts
Everything in dboNo meaningful security or semantic boundary. Grants cannot be expressed once; every object has to be audited individually.
Schemas named after tools (airflow, dbt, spark, fivetran)Schema meaning changes when the tooling changes. Tool names outlive neither the data nor the team.
Prefixes instead of schemas (raw_*, stg_* on every table)Harder permissions model, weaker discoverability, and no way to grant at the prefix boundary.
Domain-only schemas with no visible layer semanticsReaders cannot tell raw from curated data without opening the table definition.
Table-level grants to individual usersPermission maintenance becomes brittle and repetitive. Every new table and every staff change requires a new round of grants.
DENY used as a permission preference rather than a boundaryDENY takes precedence over every inherited grant. Using it casually creates permission surprises that are hard to debug later.

Current Recommendation for stoxx

The live stoxx database already has the right structural backbone — bronze, silver, and gold exist, they carry the majority of the real medallion flow, and the schema-layering audit at the top of this note confirmed the row distribution matches the intended layer semantics. The remaining work is about using that structure — applying schema-level grants, enforcing the no-new-dbo-objects rule, and adopting schema-aligned roles — not creating it.

  • Keep bronze, silver, and gold as the primary production schemas. They are already in place with contiguous ids 5, 6, 7 and do not need any bootstrap work.
  • Stop letting dbo grow as a mixed-purpose default landing area. Every new production-facing object should land in a layer schema from day one; any migration of existing dbo objects into layer schemas should be treated as a planned refactor, not an opportunistic cleanup.
  • Move long-lived production-facing dbo objects into the right layer schema using ALTER SCHEMA <target> TRANSFER dbo.<object>, one object at a time, with a verification capture against sys.tables.schema_id after each move.
  • Introduce a single meta or control schema when the platform needs durable run state, watermarks, or schema-governance tables. Do not split it into many micro-schemas up front.
  • Reserve audit, history, or contract schemas for clear non-layer purposes, not for aesthetic symmetry. Every one of them should be justified by a concrete operational win.
  • Keep demos disposable and clearly separated from production-facing objects. The demo_* prefix used throughout this note is the working pattern — every demo object was created, verified, and dropped in the same session so nothing accumulated.
  • Start using schema-level roles (etl_writer, dashboard_reader, and similar) if this environment becomes more than a single-admin sandbox. The security pattern in Cross-Schema Security is the adoption recipe: roles first, schema-level grants second, DENY only where a hard separation boundary must be enforced.

SQL Server Schema Layering References

Microsoft Learn — schema DDL and catalog views

  • CREATE SCHEMA (Transact-SQL) — authoritative syntax for CREATE SCHEMA, the AUTHORIZATION clause, and inline GRANT / DENY / REVOKE / CREATE TABLE / CREATE VIEW inside a single schema bootstrap statement. Also documents the batch-ordering rule that forces the EXEC('CREATE SCHEMA ...') idempotent wrapper pattern used in this note.
  • ALTER SCHEMA (Transact-SQL) — transfers objects between schemas without dropping and recreating them. The tool of choice when moving a production object out of dbo into its correct layer schema.
  • ALTER AUTHORIZATION (Transact-SQL) — changes the owner of a schema (or any other securable class). Used when schema ownership has to be re-aligned to a new role or team structure.
  • Create a database schema — high-level task-based guidance combining SSMS and T-SQL, including the combined CREATE SCHEMA ... CREATE TABLE ... GRANT form.
  • sys.schemas (Transact-SQL) — the catalog view underlying every schema verification query in this note. Documents the name, schema_id, and principal_id columns and clarifies that principal_id is the id of the principal that owns the schema.
  • SCHEMA_ID (Transact-SQL) — the function used in the control-plane readiness check. Documents the NULL-on-missing return semantics that the CASE WHEN SCHEMA_ID(...) IS NULL readiness flag depends on.

Microsoft Learn — permissions and security

  • GRANT Schema Permissions (Transact-SQL) — canonical reference for the ON SCHEMA::schema_name scope qualifier used in every schema-level grant in this note. Lists the full set of permissions that can be granted on a schema securable.
  • GRANT (Transact-SQL) — the top-level GRANT statement, including the WITH GRANT OPTION semantics that feed state_desc = GRANT_WITH_GRANT_OPTION in sys.database_permissions.
  • DENY (Transact-SQL) — the precedence rules that make DENY a hard boundary: DENY overrides any inherited GRANT, which is what makes the dashboard-reader pattern in this note safe.
  • REVOKE (Transact-SQL) — removes a GRANT or DENY row. Key to understand because REVOKE is not the same as DENY — a revoked permission can still be inherited from another role, while a denied permission cannot.
  • sys.database_permissions (Transact-SQL) — documents the class enum (3 = SCHEMA), the state_desc values, and the REVOKE / column-exception edge case. This is the catalog view underlying every audit query in the Cross-Schema Security section.
  • Permissions (Database Engine) — the permission inheritance matrix showing which object-level permissions are implied by a schema-level grant, and the new granular permissions added in SQL Server 2022 (principle of least privilege improvements).
  • Grant a Permission to a Principal — task-based guidance that explicitly recommends granting at the schema level rather than the object level: “configure similar securables to be owned by a schema, then grant permissions to the schema”. This is the authoritative phrasing of the main recommendation in this note.

ChromaDB supporting context

  • Building Medallion Architectures.pdf — the most SQL Server-native source in the knowledge base. It implements a three-layer medallion flow directly on Azure SQL (AdventureWorks) and explicitly treats schema management as a cross-layer concern rather than an afterthought. The closest match to the single-database, schema-per-layer pattern this note defaults to.
  • Delta Lake Up And Running Modern Data Lakehouse Architectures with Delta Lake.pdf — best compact summary of the bronze/silver/gold contract with each layer’s intended maturity, transformations, and business value. The source behind the Key Concepts medallion table in this note.
  • Pro SQL Server 2022 Administration, Third Edition A Guide for the Modern DBA.pdf — native SQL Server source for schema-as-namespace and role-based GRANT patterns. Specifically warns against treating DENY as a routine permission choice because of the management complexity it creates in a nested-role model — the warning echoed in the DENY callout in the security section.
  • Data Management at Scale Modern Data Architecture with Data Mesh and Data Fabric - 2nd Edition.pdf — defines data contracts as provider-consumer agreements with schema validation and observability statistics, and recommends a central contract repository. The conceptual foundation for the contract schema pattern in the Control, Audit, And Contract Schemas section.
  • Data Mesh Delivering Data-Driven Value at Scale.epub — primary source for the domain-ownership principle and its structural tension with layer-first schema design. Motivates the Schema-per-Domain recommendation that the layer semantics must stay visible even when domain ownership becomes the organizing axis.
  • Data Engineering Design Patterns - Recipes for Solving the Most Common Data Engineering Problems, 3rd Early Release.epub — source of the Proxy pattern (stable view over a rotating physical table) referenced at the end of the Control, Audit, And Contract Schemas section. Also documents SQL Server DDL event triggers as a mechanism for enforcing schema compatibility at the engine level.
  • Data Modeling with Snowflake.pdf — Snowflake-native but transferable to SQL Server: recommends keeping the staging/silver-equivalent schema off-limits to end users and reporting sources. Supports the rule in Schema-per-Source for Staging that stg_* is a pipeline-internal boundary.

Graph View

Backlinks