02 - SQL Engineering

35 min read

SQL Engineering

Quote

“A database is only as good as the integrity constraints that protect it.”

— C.J. Date, An Introduction to Database Systems (2003)

Some Sections CREATE Database Objects

All objects are created in a demo schema or use temp tables to avoid modifying the production stoxx schema.

Safe Pattern

All persistent objects in this notebook use a dedicated demo schema (CREATE OR ALTER ... demo.object_name) and are dropped in the Cleanup section at the end. Always use a non-production schema for experimental objects, and include a cleanup block to ensure idempotent re-runs.

Load the jupysql extension and configure display settings for notebook SQL execution.

%load_ext sql
%config SqlMagic.displaycon = False
%config SqlMagic.displaylimit = 0

Connect to the local SQL Server stoxx database via ODBC.

%sql mssql+pyodbc://sa:EsgDev2026Pass1@localhost:1434/stoxx?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&MARS_Connection=yes
Connecting to 'mssql+pyodbc://sa:***@localhost:1434/stoxx?MARS_Connection=yes&TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server'

Lab-Only Credentials

The connection string above contains a plaintext password for a local lab environment. In production, credentials are stored in GCP Secret Manager and fetched at runtime — never hardcoded. See secrets-management > Access from Python.

Safe Pattern

In production, retrieve the connection string from GCP Secret Manager at runtime: secretmanager.SecretManagerServiceClient().access_secret_version(name=...). Never hardcode passwords in notebooks, scripts, or source control. Use environment variables or secret injection via Cloud Run / GKE secrets.

The demo schema isolates all objects created in this file from the production stoxx schemas. The IF NOT EXISTS guard makes this idempotent — safe to re-run.

Create the demo schema if it does not already exist (idempotent).

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'demo')
    EXEC('CREATE SCHEMA demo');

Views

SQL Server views encapsulate reusable queries as named database objects. They simplify complex query logic for consumers while centralizing maintenance — when the underlying table structure changes, only the view definition needs updating. SQL Server expands a view inline at query time: the optimizer merges the view definition with the outer query into a single execution plan, so a well-written view carries no extra cost over writing the query directly. Indexed views (created with SCHEMABINDING) pre-compute and persist the result set, trading storage for instant read access on expensive aggregations.

Cross-Engine: Views

SQL Server expands views inline — no performance penalty vs. writing the query directly. Indexed views persist pre-computed results for expensive aggregations. BigQuery supports logical views (inline) and materialized views (with a configurable refresh schedule). Firestore has no view concept — queries always run against raw document collections; reuse is achieved through query abstraction in application code.

Regular Views — Simplify Complex Queries

A view is a saved query. It doesn’t store data — it runs the query every time you SELECT from it. Use case: wrap the “latest price per stock” pattern so downstream queries are simple.

Create a view wrapping ROW_NUMBER deduplication logic

Create a view that returns the most recent OHLCV row per stock using ROW_NUMBER deduplication.

CREATE OR ALTER VIEW demo.v_latest_prices AS
SELECT symbol, date, [open], high, low, [close], volume
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY date DESC) AS rn
    FROM silver.eurostoxx50_ohlcv
) sub
WHERE rn = 1;

Once the view is created, the ROW_NUMBER deduplication logic is hidden — consumers write a simple SELECT against the view.

Query the view with a simple SELECT

Query the view — the complex dedup logic is now hidden behind a simple SELECT.

SELECT TOP 10 * FROM demo.v_latest_prices ORDER BY [close] DESC
symboldateopenhighlowclosevolume
RMS.PA2026-03-121900.01918.51894.01906.018681
RHM.DE2026-03-121536.01588.01535.01551.5158741
ASML.AS2026-03-121194.81202.21187.81190.8128223
ADYEN.AS2026-03-12920.7933.4917.3925.727887
ARGX.BR2026-03-12629.0631.6625.6626.614083
MUV2.DE2026-03-12524.4528.8523.6526.286783
MC.PA2026-03-12495.3497.4491.6494.35171997
OR.PA2026-03-12361.1362.3357.8360.882621
ALV.DE2026-03-12349.6351.6347.9348.7182426
SAF.PA2026-03-12319.3320.2314.9315.4160065

Views — Cross-Layer Dashboard View

Join multiple tables into a single business-friendly view. Dashboards query this instead of raw tables.

Create a cross-layer dashboard view

Create a cross-layer dashboard view joining gold scores with silver dimension metadata.

CREATE OR ALTER VIEW demo.v_stock_dashboard AS
SELECT
    s.composite_rank AS [rank],
    s.symbol,
    d.short_name,
    d.sector,
    d.country,
    s.current_price,
    ROUND(s.composite_score, 4) AS composite_score,
    ROUND(s.relative_value_score, 3) AS value_score,
    ROUND(s.momentum_score, 3) AS momentum_score,
    ROUND(s.index_weight * 100, 2) AS weight_pct,
    s._index,
    s.score_date
FROM gold.scores_daily s
JOIN silver.index_dim d ON s.symbol = d.symbol AND d._index = s._index AND d.is_current = 1;

Query the dashboard view for the latest rankings

Query the dashboard view for the latest Euro Stoxx 50 scores ordered by rank.

SELECT TOP 10 * FROM demo.v_stock_dashboard
WHERE _index = 'euro_stoxx_50'
  AND score_date = (SELECT MAX(score_date) FROM gold.scores_daily WHERE _index = 'euro_stoxx_50')
ORDER BY [rank]
ranksymbolshort_namesectorcountrycurrent_pricecomposite_scorevalue_scoremomentum_scoreweight_pct_indexscore_date
1BNP.PABNP PARIBAS ACT.AFinancial ServicesFrance87.440.67961.4970.461.94euro_stoxx_502026-03-12
2VOW.DEVOLKSWAGEN AGConsumer CyclicalGermany92.850.57561.028-0.3820.93euro_stoxx_502026-03-12
3DTE.DEDEUTSCHE TELEKOM AGCommunication ServicesGermany32.550.4870.2260.7063.13euro_stoxx_502026-03-12
4TTE.PATOTALENERGIESEnergyFrance69.80.39130.5851.3072.95euro_stoxx_502026-03-12
5ABI.BRAB INBEVConsumer DefensiveBelgium62.760.38520.2510.5372.43euro_stoxx_502026-03-12
6IFX.DEINFINEON TECHNOLOGIES AGTechnologyGermany40.7350.34870.0840.3021.06euro_stoxx_502026-03-12
7SAN.MCBANCO SANTANDER S.A.Financial ServicesSpain9.6170.3106-0.0370.452.78euro_stoxx_502026-03-12
8DG.PAVINCIIndustrialsFrance129.90.29280.9570.4891.43euro_stoxx_502026-03-12
9ISP.MIINTESA SANPAOLOFinancial ServicesItaly5.2040.28520.553-0.2081.8euro_stoxx_502026-03-12
10BAYN.DEBayer AGHealthcareGermany39.4750.27240.3490.6420.77euro_stoxx_502026-03-12

Stored Procedures

Stored procedures encapsulate reusable T-SQL logic as named database objects with optional input/output parameters. SQL Server compiles and caches the execution plan on first execution — subsequent calls reuse the cached plan, eliminating parse and optimization overhead. This plan caching comes with a trade-off: parameter sniffing means the optimizer builds the plan around the first set of parameter values it sees. A plan optimized for a small result set (@top_n = 5) can perform catastrophically when the same SP is called with a large result set (@top_n = 10000), because the plan was compiled with row estimates tuned to the original parameters. See the parameter sniffing callout in the section below.

Cross-Engine: Stored Procedures

SQL Server stored procedures are compiled, parameterized objects with plan caching, output parameters, and full ACID transaction support. BigQuery supports scripting procedures (CREATE PROCEDURE) introduced in 2021, but there is no plan caching — every call incurs full query compilation. Firestore delegates server-side logic to Cloud Functions, which run outside the database engine entirely.

Related pattern

The dbt-sqlserver-adapter generates parameterized queries and materialization logic similar to these stored procedures, providing a version-controlled alternative to hand-written SPs.

Stored Procedures — Basic SP with Parameters

A stored procedure is precompiled SQL that lives in the database. Use case: pipeline steps as SPs — each step has consistent parameters and error handling.

Dynamic SQL is an injection vector

EXEC('SELECT * FROM ' + @tableName) is vulnerable to SQL injection if @tableName comes from user input. Always use sp_executesql with parameterized queries for values. For dynamic object names, validate against sys.tables / sys.columns before building the string.

Safe Pattern

Use sp_executesql with typed parameters for all variable values: EXEC sp_executesql N'SELECT ... WHERE symbol = @sym', N'@sym VARCHAR(20)', @sym = @input. For dynamic object names (table/column names), always validate the input against sys.tables or sys.columns before concatenating it into SQL — never trust caller input directly.

Create a parameterized top-N stored procedure

Create a parameterized stored procedure that returns the top N stocks by composite rank for a given index.

CREATE OR ALTER PROCEDURE demo.sp_top_stocks
    @index_key NVARCHAR(50),
    @top_n INT = 10
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT TOP (@top_n)
        [rank], symbol, short_name,
        composite_score AS score,
        current_price
    FROM demo.v_stock_dashboard
    WHERE _index = @index_key
      AND score_date = (
          SELECT MAX(score_date) FROM gold.scores_daily WHERE _index = @index_key
      )
    ORDER BY [rank];
END;

Execute the stored procedure for Euro Stoxx 50

Execute the stored procedure for the Euro Stoxx 50 index, returning the top 5 stocks.

EXEC demo.sp_top_stocks @index_key = 'euro_stoxx_50', @top_n = 5
ranksymbolshort_namescorecurrent_price
1BNP.PABNP PARIBAS ACT.A0.679687.44
2VOW.DEVOLKSWAGEN AG0.575692.85
3DTE.DEDEUTSCHE TELEKOM AG0.48732.55
4TTE.PATOTALENERGIES0.391369.8
5ABI.BRAB INBEV0.385262.76

Stored Procedures — Error Handling with TRY/CATCH

Production SPs wrap logic in TRY/CATCH with explicit transactions. If anything fails, the entire operation rolls back — no partial loads. The @@TRANCOUNT > 0 guard before ROLLBACK is essential: if the error occurred outside an open transaction (e.g., in a trigger), calling ROLLBACK unconditionally would raise an additional error.

Parameter Sniffing

SQL Server sniffs parameter values on first SP execution and optimizes the plan for those specific values. A plan compiled for @top_n = 5 may perform catastrophically when called with @top_n = 10000 — the optimizer chose a nested loops join expecting 5 rows, but now processes 10,000. Plan cache invalidation (after an index rebuild or sp_recompile) resets the sniffed values.

Mitigations

Three options in order of preference: (1) OPTION (RECOMPILE) on the statement — recompiles every call using the actual parameter values, best for plans that vary dramatically by input; (2) OPTION (OPTIMIZE FOR (@param UNKNOWN)) — uses average statistics rather than the sniffed value; (3) reassign to a local variable inside the SP (DECLARE @local = @param) — prevents sniffing but may produce suboptimal plans for all inputs.

Create an SP with TRY/CATCH, transaction, and OUTPUT parameter

Create an SP with TRY/CATCH error handling, explicit transaction, and an OUTPUT parameter for row count.

CREATE OR ALTER PROCEDURE demo.sp_load_scores
    @index_key NVARCHAR(50),
    @rows_loaded INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @rows_loaded = 0;
 
    BEGIN TRY
        BEGIN TRANSACTION;
 
        SELECT @rows_loaded = COUNT(*)
        FROM gold.scores_daily
        WHERE _index = @index_key;
 
        COMMIT TRANSACTION;
        PRINT 'Load completed: ' + CAST(@rows_loaded AS VARCHAR) + ' rows';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
 
        DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @sev INT = ERROR_SEVERITY();
        RAISERROR(@msg, @sev, 1);
    END CATCH
END;

User-Defined Functions

SQL Server supports three types of user-defined functions: scalar functions (return a single value), inline table-valued functions (iTVFs, return a table via a single SELECT), and multi-statement table-valued functions (MSTVFs, build a result set row by row). iTVFs are the only type the optimizer can inline and parallelize — always prefer them. Scalar UDFs and MSTVFs force row-by-row execution and disable parallelism. SQL Server 2019 introduced scalar UDF inlining, but many patterns remain ineligible (functions with TRY/CATCH, RAND, NEWID, recursion, or side effects); verify with sys.sql_modules.is_inlineable = 1.

Scalar UDFs Kill Performance

Scalar UDFs Force Row-by-Row Execution. T-SQL scalar UDFs (non-inlineable) disable parallelism and force SQL Server to call the function once per row. A simple scalar UDF on a 10M-row table can turn a 2-second query into a 2-minute query. Always use inline table-valued functions (iTVFs) instead — the optimizer can fold them into the outer query plan. SQL Server 2019+ has “scalar UDF inlining,” but many patterns are still not eligible.

Safe Pattern

Replace scalar UDFs with inline table-valued functions (RETURNS TABLE AS RETURN (SELECT ...)). The optimizer can fold an iTVF into the outer query plan and parallelize it. If you must retain a scalar UDF, verify it qualifies for SQL Server 2019+ scalar UDF inlining by checking sys.sql_modules.is_inlineable = 1 and test with SET STATISTICS IO, TIME ON to confirm the plan is not row-by-row.

User-Defined Functions — Inline Table-Valued Function

An iTVF is like a parameterized view — the optimizer inlines it into the outer query. Always prefer iTVFs over scalar UDFs or multi-statement TVFs.

Create an inline table-valued function for price history

Create an inline table-valued function that returns OHLCV data for a given symbol and date range.

CREATE OR ALTER FUNCTION demo.fn_price_history(
    @symbol VARCHAR(20),
    @from_date DATE,
    @to_date DATE
)
RETURNS TABLE
AS RETURN (
    SELECT symbol, date, [open], high, low, [close], volume
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = @symbol AND date BETWEEN @from_date AND @to_date
);

The iTVF is called in the FROM clause exactly like a table — the optimizer inlines it into the outer query plan.

Call the iTVF from a SELECT statement

Call the iTVF for ASML March 2026 data — the optimizer inlines it into the outer query plan.

SELECT TOP 10 * FROM demo.fn_price_history('ASML.AS', '2026-03-01', '2026-03-31')
ORDER BY date DESC
symboldateopenhighlowclosevolume
ASML.AS2026-03-121194.81202.21187.81190.8128223
ASML.AS2026-03-111188.41210.81174.01198.8562904
ASML.AS2026-03-101188.41208.41172.21200.0800815
ASML.AS2026-03-091072.01147.61060.21147.6689086
ASML.AS2026-03-061186.01192.61112.81147.0857271
ASML.AS2026-03-051198.61220.01183.01186.0778081
ASML.AS2026-03-041171.01210.81167.61199.8714587
ASML.AS2026-03-031186.61187.41144.01161.8941945
ASML.AS2026-03-021192.81231.41180.01210.4871267

Indexes

Index selection is the single highest-leverage performance decision in SQL Server. The right index can turn a multi-second table scan into a sub-millisecond seek; the wrong index imposes unnecessary write overhead on every INSERT, UPDATE, and DELETE. Index design for data pipelines requires balancing read-query patterns (equality filters, range scans, analytical aggregations) against write throughput. See index-types-and-strategy for columnstore internals, fragmentation maintenance, and missing index DMV analysis.

Covering Indexes for Pipeline Queries

A covering index includes all columns needed by a query in the index leaf pages, eliminating key lookups back to the clustered index. For the fn_price_history pattern — WHERE symbol = @symbol AND date BETWEEN @from AND @to, selecting symbol, date, open, high, low, close, volume — a covering index (symbol, date) INCLUDE (open, high, low, close, volume) satisfies the entire query from the index alone. Use sys.dm_db_missing_index_details to identify queries that would benefit from a covering index.

Indexes — Types and When to Use Each

The table below summarizes SQL Server index types and their primary use cases for time-series financial data. Index selection depends on the dominant query pattern for each table.

TypeWhatWhen
ClusteredPhysical row order. One per table.PK (symbol, date) for time-series
Non-clusteredSeparate B-tree pointing to rows.Filter/sort columns (sector, _index)
CoveringIncludes extra columns in leaf.Avoids key lookups for SELECT columns
FilteredIndex only subset of rows.WHERE is_current = 1 on dims
ColumnstoreColumnar storage, batch processing.Analytical aggregations on OHLCV

The query below inspects existing indexes on the silver.eurostoxx50_ohlcv table using catalog views. STRING_AGG aggregates the key column names in ordinal order to show the composite key layout.

Inspect existing indexes on a table via catalog views

Inspect existing indexes on the OHLCV table: name, type, uniqueness, and key columns.

SELECT
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('silver.eurostoxx50_ohlcv')
GROUP BY i.name, i.type_desc, i.is_unique
ORDER BY i.type_desc
index_nametype_descis_uniquecolumns
PK__eurostox__3213E83FDF67D274CLUSTEREDTrueid
IX_silver_eurostoxx50_ohlcv_symbol_dateNONCLUSTEREDTruesymbol, date

Indexes — Design Principles for Data Pipelines

Query-pattern-first design: identify the three or four most common predicates and projections for each table before creating any index.

  1. Equality columns first in composite keys: WHERE _index = 'X' AND date >= '2026-01-01' → index on (_index, date)
  2. Include columns to avoid lookups: INCLUDE (close, volume) if you SELECT those
  3. Don’t over-index: each index slows writes. Monitor with sys.dm_db_index_usage_stats
  4. Filtered indexes for hot subsets: WHERE is_current = 1 on dimension tables

Redundancy Note

This section covers index usage patterns for query tuning. For full index internals — B-tree structure, columnstore encodings, fragmentation mechanics, and automated maintenance scripts — see index-types-and-strategy in Chapter 04.

Slowly Changing Dimensions (SCD)

The MERGE patterns used for SCD Type 2 below are a key building block for idempotent-pipeline-design, where every load can be safely re-run without duplicating or corrupting data.

Slowly Changing Dimensions — SCD Type 1 Overwrite

Simply UPDATE the row. History is lost. Use when you don’t care about old values. Example: fix a typo in a company name.

Simulate an SCD Type 1 overwrite on a temp table

Simulate an SCD Type 1 overwrite: copy 5 rows into a temp table, then UPDATE ASML’s sector in place.

SELECT TOP 5 symbol, short_name, sector, is_current
INTO #scd_demo
FROM silver.index_dim
WHERE _index = 'euro_stoxx_50' AND is_current = 1;
 
UPDATE #scd_demo SET sector = 'Information Technology' WHERE symbol = 'ASML.AS';
SELECT * FROM #scd_demo
5 rows affected.
1 rows affected.
symbolshort_namesectoris_current
ASML.ASASML HOLDINGInformation TechnologyTrue
MC.PALVMHConsumer CyclicalTrue
RMS.PAHERMES INTLConsumer CyclicalTrue
OR.PAL'OREALConsumer DefensiveTrue
SAP.DESAP SETechnologyTrue

Slowly Changing Dimensions — SCD Type 2 History Tracking

Expire the old row (is_current=0, valid_to=NOW) and insert a new row (is_current=1). This is how silver.index_dim works — it has valid_from, valid_to, is_current columns.

Query SCD Type 2 validity ranges

Query SCD Type 2 history: show valid_from/valid_to ranges for Euro Stoxx 50 dimension rows.

SELECT TOP 10
    symbol, short_name, sector,
    is_current,
    CAST(valid_from AS DATE) AS valid_from,
    CAST(valid_to AS DATE) AS valid_to
FROM silver.index_dim
WHERE _index = 'euro_stoxx_50'
ORDER BY symbol, valid_from
symbolshort_namesectoris_currentvalid_fromvalid_to
ABI.BRAB INBEVConsumer DefensiveTrue2026-03-04None
AD.ASKONINKLIJKE AHOLD DELHAIZE N.V.Consumer DefensiveTrue2026-03-04None
ADS.DEadidas AGConsumer CyclicalTrue2026-03-04None
ADYEN.ASADYENTechnologyTrue2026-03-04None
AI.PAAIR LIQUIDEBasic MaterialsTrue2026-03-04None
AIR.PAAIRBUS SEIndustrialsTrue2026-03-04None
ALV.DEAllianz SEFinancial ServicesTrue2026-03-04None
ARGX.BRARGENX SEHealthcareTrue2026-03-04None
ASML.ASASML HOLDINGTechnologyTrue2026-03-04None
BAS.DEBASF SEBasic MaterialsTrue2026-03-04None

Gap Detection & Gap Filling

Time-series data in financial pipelines frequently contains gaps: missing trading days due to market holidays, exchange closures, or ingestion failures. Detecting and classifying these gaps is a prerequisite for accurate signal computation — undetected gaps produce incorrect rolling averages, momentum scores, and drawdown calculations. SQL Server provides two primary techniques: the LAG/DATEDIFF approach (detect a gap by comparing each row’s date to the previous row’s date within the same symbol partition) and the classical islands-and-gaps pattern (use ROW_NUMBER minus the date value to assign the same group number to consecutive days, then find the spaces between groups).

Gap Detection & Gap Filling — Detect Gaps with LAG

Uses LAG() to compare each trading date to the previous date for the same symbol. A gap larger than 3 calendar days (accounting for weekends) signals a missing trading session or ingestion failure.

Detect calendar gaps with LAG and DATEDIFF

Detect time-series gaps: compare each date to the previous date using LAG and flag gaps > 3 days.

SELECT TOP 10
    symbol, date,
    LAG(date) OVER (PARTITION BY symbol ORDER BY date) AS prev_date,
    DATEDIFF(DAY, LAG(date) OVER (PARTITION BY symbol ORDER BY date), date) AS gap_days,
    CASE WHEN DATEDIFF(DAY, LAG(date) OVER (PARTITION BY symbol ORDER BY date), date) > 3
         THEN 'UNUSUAL GAP' ELSE 'normal' END AS status
FROM silver.eurostoxx50_ohlcv
WHERE symbol = 'ASML.AS' AND date >= '2025-01-01'
ORDER BY date DESC
symboldateprev_dategap_daysstatus
ASML.AS2026-03-122026-03-111normal
ASML.AS2026-03-112026-03-101normal
ASML.AS2026-03-102026-03-091normal
ASML.AS2026-03-092026-03-063normal
ASML.AS2026-03-062026-03-051normal
ASML.AS2026-03-052026-03-041normal
ASML.AS2026-03-042026-03-031normal
ASML.AS2026-03-032026-03-021normal
ASML.AS2026-03-022026-02-273normal
ASML.AS2026-02-272026-02-261normal

Deduplication Strategies

Duplicate rows in source data are one of the most common data quality issues in financial pipelines: broker feeds retry failed deliveries, ETL jobs re-run after failures, and UNION operations occasionally double-count rows. SQL Server’s ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) window function is the standard deduplication tool — assign rank 1 to the row to keep within each duplicate group, then delete or exclude the rest. The ORDER BY clause controls which duplicate survives: highest volume, latest ingestion timestamp, or most complete record.

Deduplication Strategies — ROW_NUMBER Pattern

Assigns ROW_NUMBER() within each (symbol, date) group ordered by descending volume. Rows with rn = 1 are the canonical records; rows with rn > 1 are duplicates to remove. The COUNT(*) OVER window simultaneously flags which keys have multiple rows, so you can isolate only the affected dates for inspection.

The CTE simulates a duplicate by UNION ALL-ing the same latest-date row with a slightly modified close and volume. ROW_NUMBER() partitioned by (symbol, date) and ordered by descending volume assigns rn = 1 to the row with the highest volume (the tie-breaking rule). COUNT(*) OVER counts how many copies exist per key — the outer WHERE copies > 1 isolates only the duplicated dates for inspection.

Identify duplicates with ROW_NUMBER and tie-breaking

Simulate a duplicate row and identify it using ROW_NUMBER with volume-based tie-breaking.

WITH raw_data AS (
    SELECT symbol, date, [close], volume, 'original' AS source
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS' AND date >= '2026-03-10'
    UNION ALL
    SELECT symbol, date, [close] + 0.5, volume + 999, 'duplicate'
    FROM silver.eurostoxx50_ohlcv
    WHERE symbol = 'ASML.AS' AND date = (SELECT MAX(date) FROM silver.eurostoxx50_ohlcv WHERE symbol = 'ASML.AS')
),
numbered AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY symbol, date ORDER BY volume DESC) AS rn,
           COUNT(*) OVER (PARTITION BY symbol, date) AS copies
    FROM raw_data
)
SELECT TOP 10 symbol, date, ROUND([close], 2) AS [close], volume, source, rn, copies
FROM numbered
WHERE copies > 1
ORDER BY date DESC, rn
symboldateclosevolumesourcerncopies
ASML.AS2026-03-121191.3129222duplicate12
ASML.AS2026-03-121190.8128223original22

Execution Plans & Query Optimization

SQL Server’s cost-based optimizer compiles a query into an execution plan that specifies the physical operations (seeks, scans, joins, sorts) and their estimated costs. The plan is cached and reused for subsequent identical queries. Use SET STATISTICS IO, TIME ON to measure actual logical reads and elapsed time; query sys.dm_exec_query_stats to identify the most expensive cached plans. Understanding the common anti-patterns below — non-sargable predicates, implicit type conversions, missing indexes — is the first step in pipeline performance tuning.


flowchart TD
    A[T-SQL Query Submitted] --> B[Parse & Tokenize]
    B --> C[Bind / Algebrize]
    C --> D{Plan Cache Lookup}
    D -->|Cache Hit| E[Reuse Cached Plan]
    D -->|Cache Miss| F[Optimization Phase]
    F --> G{Trivial Plan?}
    G -->|Yes - single table<br/>no joins/aggs| H[Use Trivial Plan<br/>no cost estimation]
    G -->|No| I[Cost-Based Optimization<br/>estimate rows + cost per op]
    I --> J[Select Lowest-Cost Plan<br/>cached for reuse]
    H --> K[Execute Plan]
    J --> K
    E --> K
    K --> L[Return Result Set]

    style A fill:#1a1b26,stroke:#565f89,color:#c0caf5
    style D fill:#292e42,stroke:#bb9af7,color:#c0caf5
    style F fill:#292e42,stroke:#7aa2f7,color:#c0caf5
    style I fill:#24283b,stroke:#7aa2f7,color:#c0caf5
    style K fill:#1a1b26,stroke:#9ece6a,color:#c0caf5
    style L fill:#1a1b26,stroke:#9ece6a,color:#c0caf5

Execution Plans & Query Optimization — Common Anti-Patterns

The following patterns prevent SQL Server from using indexes efficiently. Each forces a table scan where an index seek would suffice, often increasing query cost by orders of magnitude on large tables.

Anti-PatternProblemFix
WHERE YEAR(date) = 2025Function on column prevents index seekWHERE date >= '2025-01-01' AND date < '2026-01-01'
SELECT *Reads all columns, can’t use covering indexSelect only needed columns
WHERE col = NULLAlways FALSE (NULL != NULL)WHERE col IS NULL
Implicit conversionVARCHAR compared to NVARCHAR causes scanMatch data types in predicates
Missing indexTable scan on large tableAdd non-clustered index on filter columns

Both queries return the same count, but the non-sargable version (YEAR(date) = 2025) wraps the column in a function, preventing the index seek — SQL Server must evaluate YEAR() for every row. The sargable version (date >= '2025-01-01' AND date < '2026-01-01') expresses the same filter as a range predicate the index can seek directly.

Compare non-SARGable vs SARGable predicates

Compare non-SARGable (function-on-column) vs SARGable (range) predicates — same result, different plans.

SELECT
    (SELECT COUNT(*) FROM silver.eurostoxx50_ohlcv
     WHERE YEAR(date) = 2025) AS bad_function_on_column,
    (SELECT COUNT(*) FROM silver.eurostoxx50_ohlcv
     WHERE date >= '2025-01-01' AND date < '2026-01-01') AS good_sargable
bad_function_on_columngood_sargable
1269812698

Transaction Isolation Levels

SQL Server’s transaction isolation levels control how reads interact with concurrent writes — the trade-off between data consistency and blocking. The default READ COMMITTED blocks readers when a writer holds a row lock. For analytics reads in a data pipeline, SNAPSHOT isolation provides point-in-time consistency with no blocking by reading row versions stored in tempdb. Read Committed Snapshot Isolation (RCSI) extends snapshot behavior automatically to all READ COMMITTED statements database-wide — enable it with ALTER DATABASE stoxx SET READ_COMMITTED_SNAPSHOT ON — eliminating reader/writer blocking without changing any application code.

Cross-Engine: Isolation Levels

SQL Server implements all ANSI isolation levels plus SNAPSHOT (optimistic, row-versioned via tempdb) and RCSI. BigQuery uses serializable isolation for multi-statement transactions by default; single statements are always atomic and isolated. Firestore transactions are serializable and limited to 500 documents per transaction; reads outside a transaction use strong consistency by default for server-side reads, eventual consistency for mobile/web clients.

Transaction Isolation Levels — Guide for Data Engineering

Each isolation level is a commitment about which read anomalies the engine prevents. Higher levels prevent more anomalies but increase blocking — lower levels scale better but may return stale or inconsistent reads.

LevelDirty ReadsNon-RepeatablePhantomsUse Case
READ UNCOMMITTEDYesYesYesStale-tolerant dashboards, quick counts
READ COMMITTED (default)NoYesYesMost pipeline reads
REPEATABLE READNoNoYesFinancial calculations
SERIALIZABLENoNoNoCritical writes (score computation)
SNAPSHOTNoNoNoAnalytics reads (no blocking, uses tempdb)

Recommendation for pipelines: READ COMMITTED for writes, SNAPSHOT for reads.

NOLOCK Can Return Wrong Data

READ UNCOMMITTED (NOLOCK) Can Return Wrong Data. NOLOCK / READ UNCOMMITTED can read rows that are being moved by a page split, causing the same row to appear twice or not at all in the result. It can also read uncommitted data that is later rolled back. Never use NOLOCK for counts, sums, or any calculation where accuracy matters — even for “approximate” dashboards, the error can be larger than expected.

Safe Pattern

Use SNAPSHOT isolation for analytics reads instead of NOLOCK: SET TRANSACTION ISOLATION LEVEL SNAPSHOT. SNAPSHOT provides point-in-time read consistency with no blocking, using row versions from tempdb rather than dirty reads. Enable it at the database level with ALTER DATABASE stoxx SET ALLOW_SNAPSHOT_ISOLATION ON.

Bulk Loading Patterns

Bulk data loading is the performance-critical path for bronze-layer ingestion and silver-layer transforms. SQL Server provides several insertion strategies spanning orders of magnitude in throughput — from simple INSERT INTO ... SELECT to minimally-logged BULK INSERT from flat files. The choice depends on data source (query result vs. file), load size, recovery model (FULL vs. SIMPLE/BULK_LOGGED), and whether you need checkpointing for loads that exceed available transaction log space.

Bulk Loading Strategies — Insert Method Comparison

Choose an insert strategy based on data source, batch size, and recovery model. Minimal logging (requires SIMPLE or BULK_LOGGED recovery model) is needed to achieve the fastest throughput with INSERT ... WITH (TABLOCK) and BULK INSERT.

StrategySpeedWhen
INSERT INTO ... SELECTMediumSmall-medium loads from staging
INSERT ... WITH (TABLOCK)FastMinimal logging in SIMPLE/BULK_LOGGED
BULK INSERTFastestLoading from CSV files on disk
Batched inserts (TOP N loop)ControlledLarge loads with checkpoints
Drop indexes → load → rebuildFastestFull table reloads

Pipeline pattern: load to staging table → validate → MERGE to target → truncate staging.

Data Lineage & Audit Columns

The stoxx database implements audit columns on every table to support data lineage tracking: when each row was ingested, computed, and last modified. These columns enable freshness checks (is the data stale?), replay detection (has this batch already been loaded?), and pipeline debugging (which layer introduced a discrepancy?). The query below checks the latest timestamp across all four layers of the medallion architecture to confirm a successful end-to-end pipeline run.

Data Lineage & Audit — Standard Audit Columns

Every table in the stoxx database has audit columns:

ColumnTypePurpose
_ingested_atDATETIME2When the row was loaded (bronze)
_scored_atDATETIME2When the score was computed (gold)
_computed_atDATETIME2When the performance was calculated
is_filledBITWhether the row was gap-filled (silver)
is_currentBITSCD Type 2 current flag (dimension)

Check data freshness across all medallion layers

Check data freshness across all four medallion layers — the latest timestamp per layer.

SELECT 'bronze.eurostoxx50_ohlcv' AS [table], MAX(_ingested_at) AS last_update
FROM bronze.eurostoxx50_ohlcv
UNION ALL
SELECT 'silver.signals_daily', MAX(signal_date) FROM silver.signals_daily
WHERE _index = 'euro_stoxx_50'
UNION ALL
SELECT 'gold.scores_daily', MAX(score_date) FROM gold.scores_daily
WHERE _index = 'euro_stoxx_50'
UNION ALL
SELECT 'gold.index_performance', MAX(perf_date) FROM gold.index_performance
WHERE _index = 'euro_stoxx_50'
ORDER BY last_update DESC
tablelast_update
bronze.eurostoxx50_ohlcv2026-03-12 12:45:00.021478
gold.index_performance2026-03-12 00:00:00
gold.scores_daily2026-03-12 00:00:00
silver.signals_daily2026-03-12 00:00:00

Partitioning Strategies

Table partitioning divides a large table’s data into physically separate segments based on a column value range (typically a date). SQL Server’s partition elimination allows the query optimizer to skip entire partitions that cannot satisfy the WHERE clause predicate — equivalent to a physical shard filter at the storage level. Partitioning also enables instant data archival via SWITCH: moving an entire partition between tables is a metadata-only operation requiring no row movement. See partitioning-strategies for full implementation details including partition functions, schemes, sliding windows, and maintenance scripts.

Partitioning Strategies — When to Partition

Partition large tables (millions of rows) by a date column for:

  • Faster queries: partition elimination skips irrelevant months/years
  • Easier maintenance: rebuild one partition, not the whole table
  • Instant archival: SWITCH old partitions to archive table

The OHLCV tables (~65K rows each) are too small to benefit. In production with 100M+ rows, partition by year or month.

The schema below shows how a partition function and scheme would be defined — for reference only; do not run in the lab environment.

Define a yearly partition function and scheme

Define a yearly partition function and scheme for a partitioned OHLCV table (reference only — not executed in lab).

CREATE PARTITION FUNCTION pf_yearly(DATE)
    AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01');
 
CREATE PARTITION SCHEME ps_yearly
    AS PARTITION pf_yearly ALL TO ([PRIMARY]);
 
CREATE TABLE silver.ohlcv_partitioned (
    symbol VARCHAR(20), date DATE, [close] FLOAT, ...
) ON ps_yearly(date);

Cleanup

Drop all objects created in the demo schema by this notebook. Running the cleanup leaves the database in its original state and makes the notebook safe to re-run from a clean slate.

Demo object cleanup

Every view, stored procedure, and function created earlier must be dropped in reverse dependency order before the schema itself can be removed.

Drop all demo objects and the demo schema

Drop all demo objects and the demo schema to leave the database clean.

DROP VIEW IF EXISTS demo.v_latest_prices;
DROP VIEW IF EXISTS demo.v_stock_dashboard;
DROP PROCEDURE IF EXISTS demo.sp_top_stocks;
DROP PROCEDURE IF EXISTS demo.sp_load_scores;
DROP FUNCTION IF EXISTS demo.fn_price_history;
DROP SCHEMA IF EXISTS demo;
SELECT 'Demo objects cleaned up' AS status
status
Demo objects cleaned up

SQL Server SQL Engineering Warnings

The table below lists the highest-impact production pitfalls associated with the database objects and patterns covered in this note. Each entry corresponds to a warning or danger callout earlier in the page.

TopicWarning
Parameter sniffingAn SP’s cached plan is optimized for the first parameter values. A plan compiled for 5 rows can be catastrophic for 10,000 rows. Monitor with sys.dm_exec_query_stats.
Scalar UDFsNon-inlineable scalar UDFs disable parallelism and force row-by-row execution. A simple scalar UDF on 10M rows can turn a 2-second query into a 2-minute query.
MERGE concurrency bugsMicrosoft has documented multiple bugs: missing rows, duplicate key violations, incorrect results under concurrent access. Always add WITH (HOLDLOCK) on the target.
NOLOCK / READ UNCOMMITTEDCan return rows twice, skip rows, or read rolled-back data during page splits. Never use for counts, sums, or any calculation where accuracy matters.
SCD Type 1 in financial pipelinesDestroys history permanently. A sector change applied via Type 1 retroactively alters historical portfolio returns without any audit trail.
CTE re-executionA CTE referenced 3 times runs 3 times. Check the execution plan for repeated subtrees — switch to #temp if cost is significant.
Over-indexingEach index slows INSERT/UPDATE/DELETE. Monitor index usage with sys.dm_db_index_usage_stats and drop unused indexes.

SQL Server SQL Engineering Recommendations

Standing guidance for designing, writing, and operating the database objects covered above. Apply these as defaults unless a specific workload has a documented reason to deviate.

AreaRecommendation
View vs SP vs iTVFUse views for static logic, iTVFs for parameterized reads, SPs for multi-step procedural logic with transactions.
Error handlingAlways use TRY/CATCH with @@TRANCOUNT > 0 guard before ROLLBACK. Without the guard, rolling back a non-existent transaction raises an additional error.
Parameter sniffing mitigationUse OPTION (RECOMPILE) on specific statements (not the whole SP) for plans that genuinely vary by input. Use OPTIMIZE FOR UNKNOWN for stable average behavior.
Bulk loadingLoad to staging table → validate with quality checks → MERGE to target → truncate staging. Drop non-clustered indexes before large loads, rebuild after.
Isolation for analyticsEnable RCSI (ALTER DATABASE stoxx SET READ_COMMITTED_SNAPSHOT ON) to eliminate reader/writer blocking database-wide without changing application code.
Demo schema patternAlways create experimental objects in a dedicated schema (demo). Include a cleanup block at the end to ensure idempotent re-runs.
Audit columnsEvery pipeline table should have _ingested_at (bronze), _scored_at (gold), and is_filled / is_current flags for lineage tracking.

SQL Server SQL Engineering Troubleshooting

Symptoms you will encounter when a database object or query misbehaves, mapped to the most likely cause and the fix that resolves it in practice.

SymptomLikely causeFix
SP runs fast the first time, slow on subsequent callsParameter sniffing — plan cached for atypical first valuesAdd OPTION (RECOMPILE) or WITH RECOMPILE on the SP, or use sp_recompile to flush the plan.
View query is unexpectedly slowView references a CTE or subquery that is re-evaluated, or the base table lacks an index on the filter columnCheck the execution plan. Add a covering index on the most-used predicate columns.
MERGE raises duplicate key violationRace condition between MATCHED check and INSERT under concurrent accessAdd WITH (HOLDLOCK) on the target table in the MERGE statement, or switch to explicit INSERT/UPDATE in a transaction with UPDLOCK.
#temp table query slow despite small sizeMissing index on the join/filter column in the temp tableAdd CREATE INDEX ix ON #temp (key_col) after populating the temp table.
Scalar UDF causes query timeoutUDF is non-inlineable — forces row-by-row executionRewrite as an iTVF or inline the logic directly into the query. Check sys.sql_modules.is_inlineable.
Partition elimination not workingWHERE clause uses a function on the partition column, or the filter column doesn’t match the partition functionRewrite as a range predicate on the raw partition column. Verify with execution plan’s “Actual Partition Count”.

SQL Server SQL Engineering Cross-References

Related notes that extend or depend on the patterns covered here.

Graph View

Backlinks