CSV, staging, ETL and data quality

Validate raw data before production.

UnderstandStudy an examplePractise

Understand the concept

Staging separates raw external records before validation and transformation. Define CSV encoding, delimiters, quoted values, date formats and NULL representation. BULK INSERT depends on the SQL Server service’s file access, not a browser upload; these examples therefore stage safe VALUES.

Use TRY_CONVERT to isolate conversion failures into a quarantine list. Reruns should not duplicate data: design business keys, batch identifiers and idempotency. Incremental watermarks can miss late arrivals; assess lookback windows, change feeds and reconciliation. A completed command is not enough: reconcile row counts, totals and rejected records.

WHEN TO USE IT

Turn external data into reliable records.

COMMON PITFALL

Silently converting invalid input to zero.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT TRY_CONVERT(decimal(10,2), N'120.50') AS Amount, TRY_CONVERT(decimal(10,2), N'wrong') AS Invalid;

An invalid value becomes NULL for detection.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Select valid positive amounts from Stage.

Show the solution and explanation

Worked solution

SELECT Code, TRY_CONVERT(decimal(10,2), RawAmount) AS Amount FROM #Stage WHERE TRY_CONVERT(decimal(10,2), RawAmount) > 0;

Run the intermediate example first. A and C qualify; B should be reported as rejected, not silently lost.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.