Sqldw Authoring Cli

Update Check — ONCE PER SESSION (mandatory)

The first time this skill is used in a session, run the check-updates skill before proceeding.

- GitHub Copilot CLI / VS Code: invoke the check-updates skill.

- Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.

- Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering

2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL Endpoint Authoring — CLI Skill

Table of Contents

---

Tool Stack

Agent check — verify before first operation:

```bash

sqlcmd --version 2>/dev/null || echo "INSTALL: winget install sqlcmd OR brew install sqlcmd"

```

Authoring Scope by Item Type

---

Connection

Discover the SQL Endpoint FQDN

Per COMMON-CLI.md Discovering Connection Parameters via REST:

WS_ID="<workspaceId>"
ITEM_ID="<warehouseOrLakehouseId>"

# Warehouse
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses/$ITEM_ID" \
  --query "properties.connectionString" --output tsv

# Lakehouse SQL endpoint
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/lakehouses/$ITEM_ID" \
  --query "properties.sqlEndpointProperties.connectionString" --output tsv

Result: <uniqueId>.datawarehouse.fabric.microsoft.com

Connect with sqlcmd (Go)

# Non-interactive one-shot query
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" -G \
  -Q "SELECT TOP 10 * FROM dbo.FactSales"

# Service principal (CI/CD)
SQLCMDPASSWORD="<clientSecret>" \
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" \
  --authentication-method ActiveDirectoryServicePrincipal \
  -U "<appId>" \
  -Q "SELECT COUNT(*) FROM dbo.FactSales"

Reusable Connection Variables

# Set once at script top
FABRIC_SERVER="<endpoint>.datawarehouse.fabric.microsoft.com"
FABRIC_DB="<DatabaseName>"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

# Use throughout
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.DimProduct"
$SQLCMD -i myscript.sql

PowerShell / Windows CMD

$s = "<endpoint>.datawarehouse.fabric.microsoft.com"; $db = "<DatabaseName>"
sqlcmd -S $s -d $db -G -Q "SELECT TOP 10 * FROM dbo.FactSales"
# CMD: use set S=... and %S% / %DB% instead of $variables

---

Agentic Workflows

Schema Discovery Before Authoring

Before any write operation, discover the target schema:

# 1. List tables
$SQLCMD -Q "SELECT table_schema, table_name FROM information_schema.tables ORDER BY 1,2" -W

# 2. Check columns
$SQLCMD -Q "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name='FactSales' ORDER BY ordinal_position" -W

# 3. Sample data
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.FactSales" -W

# 4. Check constraints
$SQLCMD -Q "SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name='FactSales'" -W

# 5. Row counts
$SQLCMD -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC" -W

# 6. Programmability objects
$SQLCMD -Q "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF') ORDER BY type_desc, name" -W

Agentic Workflow

1. Discover → Run steps 1–4 to understand available tables/columns. 2. Sample → SELECT TOP 5 on relevant tables. 3. Formulate → Select pattern from SQLDW-AUTHORING-CORE.md (Table DDL through Common Authoring Patterns). 4. Execute → $SQLCMD -Q "..." or $SQLCMD -i file.sql for multi-statement. 5. Verify → Query affected table (SELECT COUNT(*), SELECT TOP 5). 6. Optionally script → Generate reusable .sh or .ps1 using references/authoring-script-templates.md.

---

Gotchas, Rules, Troubleshooting

For full authoring gotchas: SQLDW-AUTHORING-CORE.md Authoring Gotchas and Troubleshooting. For CLI-specific issues: COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Verify workspace has capacity before creating warehouse — call GET /v1/workspaces/{id} and check capacityId.
• Always `-d <DatabaseName>` — FQDN alone is insufficient.
• Always `-G` or `--authentication-method` — SQL auth not supported on Fabric.
• `az login` first — ActiveDirectoryDefault uses az session. No session → cryptic failure.
• `SET NOCOUNT ON;` in scripts — suppresses row-count messages that corrupt output.
• Use `-i file.sql` for multi-statement batches (CREATE PROCEDURE, transactions with GO separators).
• Label authoring queries with OPTION (LABEL = 'ETL_description').
• Use explicit `CAST()` in CTAS to control output types.
• Keep transactions short — long transactions increase conflict window.

AVOID

• ODBC sqlcmd (/opt/mssql-tools/bin/sqlcmd) — requires ODBC driver. Use Go version.
• Omitting `-W` in scripts — trailing spaces corrupt CSV.
• Singleton `INSERT ... VALUES` at scale — creates tiny Parquet files. Use INSERT...SELECT, CTAS, or COPY INTO.
• `DROP TABLE IF EXISTS` + `CREATE TABLE` to refresh — loses time-travel history. Use TRUNCATE TABLE + INSERT INTO.
• MERGE in production — preview, table-level conflict detection. Use DELETE + INSERT.
• ALTER COLUMN — not supported. Use CTAS workaround (Schema Evolution).
• Variables in CTAS — not allowed. Wrap in dynamic SQL: EXEC sp_executesql N'CREATE TABLE ...'.
• DML on Lakehouse/Mirrored DB SQLEP — read-only for table data. Only views/funcs/procs can be authored.
• Concurrent UPDATE/DELETE on same table — snapshot isolation conflicts at table level. Serialize writes.
• Hardcoded FQDNs — discover via REST API (Connection section).
• MARS — not supported. Remove MultipleActiveResultSets from connection strings.

PREFER

• CTAS over CREATE TABLE + INSERT — parallel, single-operation.
• `INSERT ... SELECT` over singleton INSERTs.
• `COPY INTO` for external file ingestion — highest throughput.
• DELETE + INSERT over MERGE for upserts in production.
• `TRUNCATE TABLE` over DELETE FROM without WHERE — faster, preserves history.
• `-i file.sql` over -Q "..." for anything beyond simple one-liners.
• Piped here-doc for multi-statement batches without GO requirements.
• CTAS + sp_rename for large-scale transforms instead of UPDATE.
• `sqlcmd (Go) -G` over curl+token for SQL queries.
• `-Q` (non-interactive exit) for agentic use.
• `-F vertical` for exploration of wide tables.
• Env vars (FABRIC_SERVER, FABRIC_DB) for script reuse.

TROUBLESHOOTING

Authoring CLI Quick Reference

Concise sqlcmd invocation patterns, output formatting, monitoring queries, and agent tips. For full T-SQL patterns, see SQLDW-AUTHORING-CORE.md. For full reusable scripts, see authoring-script-templates.md.

All examples assume reusable connection variables are set:

FABRIC_SERVER="<endpoint>.datawarehouse.fabric.microsoft.com"
FABRIC_DB="<WarehouseName>"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

Core Authoring via CLI

Table DDL via CLI

# CREATE TABLE
$SQLCMD -Q "
CREATE TABLE dbo.FactSales (
    SaleID bigint NOT NULL,
    ProductID int NOT NULL,
    SaleDate date NOT NULL,
    Amount decimal(19,4) NOT NULL
)"

# CTAS with explicit types (preferred for populated tables)
$SQLCMD -Q "
CREATE TABLE dbo.FactSales_2024 AS
SELECT SaleID, CAST(Amount AS decimal(19,2)) AS Amount
FROM dbo.FactSales WHERE SaleDate >= '2024-01-01'"

# ALTER TABLE — add column / DROP TABLE
$SQLCMD -Q "ALTER TABLE dbo.FactSales ADD Region varchar(50) NULL"
$SQLCMD -Q "DROP TABLE IF EXISTS dbo.StagingTable"

DML via CLI

# INSERT...SELECT (preferred for bulk)
$SQLCMD -Q "
INSERT INTO dbo.FactSales (SaleID, ProductID, SaleDate, Amount)
SELECT SaleID, ProductID, SaleDate, Amount
FROM dbo.StagingTable WHERE IsValid = 1"

# Upsert (production-safe: DELETE + INSERT) — use -i for multi-statement
$SQLCMD -i upsert.sql

# TRUNCATE (fast, preserves history — use instead of DELETE FROM)
$SQLCMD -Q "TRUNCATE TABLE dbo.StagingTable"

Data Ingestion via CLI

# Parquet from ADLS Gen2 (uses caller's Entra ID credentials)
$SQLCMD -Q "
COPY INTO dbo.FactSales
FROM 'https://storageacct.dfs.core.windows.net/container/sales/*.parquet'
WITH (FILE_TYPE = 'PARQUET')"

# CSV with options
$SQLCMD -Q "
COPY INTO dbo.FactSales
FROM 'https://storageacct.dfs.core.windows.net/container/sales/*.csv'
WITH (FILE_TYPE = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

# OPENROWSET + CTAS (transform-on-ingest)
$SQLCMD -Q "
CREATE TABLE dbo.CleanData AS
SELECT id, UPPER(country) AS Country, CAST(amount AS decimal(19,4)) AS Amount
FROM OPENROWSET(BULK 'https://storageacct.dfs.core.windows.net/container/raw/*.parquet') AS raw
WHERE amount > 0"

Advanced Authoring Patterns via CLI

Transactions via CLI

Multi-statement transactions require input files or piped here-docs (GO separators needed between batch-scoped statements).

# Simple transaction via piped input
cat <<'SQL' | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G
BEGIN TRANSACTION;
INSERT INTO dbo.FactSales SELECT * FROM dbo.StagingTable WHERE IsValid = 1;
DELETE FROM dbo.StagingTable WHERE IsValid = 1;
COMMIT TRANSACTION;
SQL

# Transaction with TRY/CATCH via input file
sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -i etl_load.sql

Schema Evolution via CLI

# Add nullable column (fast metadata op)
$SQLCMD -Q "ALTER TABLE dbo.FactSales ADD Region varchar(50) NULL"

# Drop column (April 2025+)
$SQLCMD -Q "ALTER TABLE dbo.FactSales DROP COLUMN Region"

# Change column type (CTAS workaround — ALTER COLUMN not supported)
$SQLCMD -i schema_migrate.sql

Warning: CTAS + rename loses time-travel history and security. Re-apply GRANT/DENY after swap.

Stored Procedures via CLI

# Create procedure (use -i for multi-statement with GO)
sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -i create_sp.sql

# Execute procedure
$SQLCMD -Q "EXEC dbo.sp_LoadFactSales @BatchDate = '2025-06-15'"

# Create view on Lakehouse SQLEP (read-only endpoint — views/funcs/procs allowed)
sqlcmd -S "$LAKEHOUSE_SERVER" -d "$LAKEHOUSE_DB" -G -i create_views.sql

Time Travel and Recovery via CLI

# Query data as it existed at a specific time (UTC)
$SQLCMD -Q "
SELECT * FROM dbo.FactSales
OPTION (FOR TIMESTAMP AS OF '2025-06-14T23:59:59.999')" -W

# Recover deleted data via CTAS + merge back
$SQLCMD -Q "
CREATE TABLE dbo.FactSales_Recovered AS
SELECT * FROM dbo.FactSales
OPTION (FOR TIMESTAMP AS OF '2025-06-14T23:59:59.999')"

Script Generation

sqlcmd Output Formatting Flags

Piped Input

# Pipe SQL from stdin
echo "SELECT TOP 5 * FROM dbo.FactSales" | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G

# Here-doc for multi-statement
cat <<'SQL' | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G
SET NOCOUNT ON;
SELECT COUNT(*) AS TotalRows FROM dbo.FactSales;
SELECT TOP 3 ProductID, SUM(Amount) AS Total FROM dbo.FactSales GROUP BY ProductID ORDER BY Total DESC;
SQL

Parameterized Queries (sqlcmd Variables)

sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G \
  -v StartDate="2025-01-01" EndDate="2025-06-30" \
  -Q "SET NOCOUNT ON; SELECT * FROM dbo.FactSales WHERE SaleDate BETWEEN '$(StartDate)' AND '$(EndDate)'" -W

For full reusable bash/PowerShell script templates, see authoring-script-templates.md.

Monitoring Authoring Operations

For full monitoring catalog see SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics.

# Active DML/DDL operations
$SQLCMD -Q "SELECT request_id, session_id, command, status, total_elapsed_time/1000 AS sec FROM sys.dm_exec_requests WHERE command IN ('INSERT','UPDATE','DELETE','MERGE','CREATE TABLE','COPY') ORDER BY total_elapsed_time DESC" -W

# Recent ETL queries (last 24h)
$SQLCMD -Q "SELECT TOP 20 distributed_statement_id, login_name, label, total_elapsed_time_ms FROM queryinsights.exec_requests_history WHERE start_time >= DATEADD(HOUR,-24,GETUTCDATE()) AND label LIKE 'ETL_%' ORDER BY total_elapsed_time_ms DESC" -W

# Failed writes (last 7d) — detect snapshot conflicts
$SQLCMD -Q "SELECT TOP 10 distributed_statement_id, command, start_time, status FROM queryinsights.exec_requests_history WHERE status='Failed' AND start_time >= DATEADD(DAY,-7,GETUTCDATE()) ORDER BY start_time DESC" -W

# Kill a stuck session (Admin role)
$SQLCMD -Q "KILL '<distributed_statement_id>'"

Agent Integration Notes

• GitHub Copilot CLI: Generate sqlcmd one-liners for DDL/DML or complete .sql + .sh file pairs. Ensure -G and -d in output. For COPY INTO, remind user about Storage Blob Data Reader role.
• Claude Code / Cowork: Run sqlcmd -Q "..." via bash tool directly. For multi-statement authoring (procedures, transactions): write .sql file first, then execute with -i. Always verify sqlcmd availability and az login before first use. After writes: verify success by querying affected table.
• Common agent pattern:

1. Discover schema (columns, types) 2. Formulate CTAS/DML with explicit CASTs 3. Execute via sqlcmd 4. Verify result (row count, sample) 5. Optionally generate reusable script

Authoring Script Templates

Self-contained templates for generating reusable authoring scripts.

Common prerequisites (validated in each template):

• sqlcmd (Go) installed — winget install sqlcmd / brew install sqlcmd
• az login session active
• Env vars: FABRIC_SERVER, FABRIC_DB

Bash Templates

Bash — COPY INTO Ingestion

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?Set FABRIC_SERVER env var}"
FABRIC_DB="${FABRIC_DB:?Set FABRIC_DB env var}"
STORAGE_PATH="${1:?Usage: $0 <storage_path> [table_name]}"
TABLE_NAME="${2:-dbo.StagingData}"

command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd (Go) not found. Install: winget install sqlcmd"; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

echo "Loading $STORAGE_PATH → $TABLE_NAME ..."
sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -Q "
COPY INTO $TABLE_NAME
FROM '$STORAGE_PATH'
WITH (FILE_TYPE = 'PARQUET')
OPTION (LABEL = 'ETL_COPY_INTO_$(date +%Y%m%d)')"

echo "Verifying..."
sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -Q "
SET NOCOUNT ON; SELECT COUNT(*) AS row_count FROM $TABLE_NAME" -W -h-1
echo "✓ Done"

Bash — Full ELT Pipeline (Stage → Transform → Load)

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}" ; FABRIC_DB="${FABRIC_DB:?}"
STORAGE_PATH="${1:?Usage: $0 <parquet_path>}"
BATCH_DATE=$(date +%Y-%m-%d)
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

echo "[1/3] Loading raw data from $STORAGE_PATH ..."
$SQLCMD -Q "
COPY INTO dbo.Staging_RawSales
FROM '$STORAGE_PATH'
WITH (FILE_TYPE = 'PARQUET')
OPTION (LABEL = 'ETL_Stage_$BATCH_DATE')"

echo "[2/3] Transforming and loading into FactSales ..."
$SQLCMD -Q "
INSERT INTO dbo.FactSales (SaleID, ProductID, CustomerID, SaleDate, Amount)
SELECT SaleID, ProductID, CustomerID,
       CAST(SaleTimestamp AS date) AS SaleDate,
       CAST(RawAmount AS decimal(19,4)) AS Amount
FROM dbo.Staging_RawSales
WHERE RawAmount > 0 AND SaleID IS NOT NULL
OPTION (LABEL = 'ETL_Transform_$BATCH_DATE')"

echo "[3/3] Cleaning staging ..."
$SQLCMD -Q "TRUNCATE TABLE dbo.Staging_RawSales"

echo "✓ ELT pipeline complete for $BATCH_DATE"
$SQLCMD -Q "SET NOCOUNT ON; SELECT COUNT(*) AS fact_rows FROM dbo.FactSales" -W -h-1

Bash — Incremental Upsert (DELETE + INSERT with Retry)

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}" ; FABRIC_DB="${FABRIC_DB:?}"
CUTOFF_DATE="${1:?Usage: $0 <cutoff_date YYYY-MM-DD>}"
MAX_RETRIES=3
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

# Write the upsert SQL to a temp file
TMPFILE=$(mktemp /tmp/upsert_XXXXXX.sql)
cat > "$TMPFILE" <<SQL
SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    DELETE FROM dbo.FactSales WHERE SaleDate >= '$CUTOFF_DATE';
    INSERT INTO dbo.FactSales
    SELECT * FROM SalesLakehouse.dbo.ProcessedSales WHERE SaleDate >= '$CUTOFF_DATE';
    COMMIT TRANSACTION;
    PRINT 'SUCCESS';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    PRINT 'ERROR: ' + CAST(ERROR_NUMBER() AS varchar) + ' - ' + ERROR_MESSAGE();
    THROW;
END CATCH;
SQL

ATTEMPT=1
while [ "$ATTEMPT" -le "$MAX_RETRIES" ]; do
  echo "Attempt $ATTEMPT/$MAX_RETRIES: Upserting data since $CUTOFF_DATE ..."
  if sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -i "$TMPFILE" 2>&1 | tee /dev/stderr | grep -q "SUCCESS"; then
    echo "✓ Upsert complete"
    rm -f "$TMPFILE"
    exit 0
  fi
  echo "Retrying in $((ATTEMPT * 5))s ..."
  sleep $((ATTEMPT * 5))
  ATTEMPT=$((ATTEMPT + 1))
done

rm -f "$TMPFILE"
echo "✗ Failed after $MAX_RETRIES attempts"
exit 1

Bash — Schema Migration (CTAS Workaround)

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}" ; FABRIC_DB="${FABRIC_DB:?}"
TABLE="dbo.FactSales"
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

TMPFILE=$(mktemp /tmp/migrate_XXXXXX.sql)
cat > "$TMPFILE" <<'SQL'
-- Change Amount type from decimal(19,4) to decimal(19,2)
PRINT 'Creating new table with updated schema...';
CREATE TABLE dbo.FactSales_V2 AS
SELECT SaleID, ProductID, CustomerID, SaleDate,
       CAST(Amount AS decimal(19,2)) AS Amount, Quantity
FROM dbo.FactSales;
GO
PRINT 'Dropping original...';
DROP TABLE dbo.FactSales;
GO
PRINT 'Renaming...';
EXEC sp_rename 'dbo.FactSales_V2', 'FactSales';
GO
PRINT 'Re-applying constraints...';
ALTER TABLE dbo.FactSales
ADD CONSTRAINT PK_FactSales PRIMARY KEY NONCLUSTERED (SaleID) NOT ENFORCED;
GO
PRINT 'Migration complete.';
-- WARNING: Re-apply GRANT/DENY security and verify time-travel history is reset.
SQL

echo "Migrating schema for $TABLE ..."
sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -i "$TMPFILE"
rm -f "$TMPFILE"
echo "✓ Schema migration complete. Re-apply any GRANT/DENY statements."

Bash — Data Recovery via Time Travel

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}" ; FABRIC_DB="${FABRIC_DB:?}"
TABLE="${1:?Usage: $0 <table> <utc_timestamp>}"
TIMESTAMP="${2:?Usage: $0 <table> <utc_timestamp>}"
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

echo "Recovering $TABLE as of $TIMESTAMP ..."

$SQLCMD -Q "
CREATE TABLE ${TABLE}_Recovered AS
SELECT * FROM $TABLE
OPTION (FOR TIMESTAMP AS OF '$TIMESTAMP')"

echo "Recovered rows:"
$SQLCMD -Q "SET NOCOUNT ON; SELECT COUNT(*) AS rows FROM ${TABLE}_Recovered" -W -h-1

echo "Merging back missing rows ..."
# Assumes SaleID as PK — adjust for your table
$SQLCMD -Q "
INSERT INTO $TABLE
SELECT r.* FROM ${TABLE}_Recovered r
WHERE NOT EXISTS (SELECT 1 FROM $TABLE t WHERE t.SaleID = r.SaleID)"

$SQLCMD -Q "DROP TABLE ${TABLE}_Recovered"
echo "✓ Recovery complete"

Bash — Create Stored Procedure

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}" ; FABRIC_DB="${FABRIC_DB:?}"

# Write procedure to file (GO separators required)
cat > /tmp/create_sp.sql <<'SQL'
CREATE OR ALTER PROCEDURE dbo.sp_IncrementalLoad
    @CutoffDate date
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        DELETE FROM dbo.FactSales WHERE SaleDate >= @CutoffDate;
        INSERT INTO dbo.FactSales
        SELECT * FROM SalesLakehouse.dbo.ProcessedSales WHERE SaleDate >= @CutoffDate;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO
SQL

sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G -i /tmp/create_sp.sql
echo "✓ Procedure created. Execute: EXEC dbo.sp_IncrementalLoad @CutoffDate = '2025-01-01'"

PowerShell Templates

PowerShell — COPY INTO Ingestion

#Requires -Version 5.1
param(
    [Parameter(Mandatory)][string]$Server,
    [Parameter(Mandatory)][string]$Database,
    [Parameter(Mandatory)][string]$StoragePath,
    [string]$TableName = "dbo.StagingData"
)

if (-not (Get-Command sqlcmd -ErrorAction SilentlyContinue)) {
    Write-Error "sqlcmd (Go) not found. Install: winget install sqlcmd"; exit 1
}
$null = az account show 2>$null
if ($LASTEXITCODE -ne 0) { Write-Error "Not logged in. Run: az login"; exit 1 }

$query = @"
COPY INTO $TableName
FROM '$StoragePath'
WITH (FILE_TYPE = 'PARQUET')
OPTION (LABEL = 'ETL_COPY_$(Get-Date -Format yyyyMMdd)')
"@

Write-Host "Loading $StoragePath → $TableName ..."
sqlcmd -S $Server -d $Database -G -Q $query
Write-Host "Verifying..."
sqlcmd -S $Server -d $Database -G -Q "SET NOCOUNT ON; SELECT COUNT(*) AS rows FROM $TableName" -W -h-1
Write-Host "Done"

PowerShell — Incremental Upsert with Retry

#Requires -Version 5.1
param(
    [Parameter(Mandatory)][string]$Server,
    [Parameter(Mandatory)][string]$Database,
    [Parameter(Mandatory)][string]$CutoffDate,
    [int]$MaxRetries = 3
)

if (-not (Get-Command sqlcmd -ErrorAction SilentlyContinue)) {
    Write-Error "sqlcmd not found."; exit 1
}

$sql = @"
SET NOCOUNT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    DELETE FROM dbo.FactSales WHERE SaleDate >= '$CutoffDate';
    INSERT INTO dbo.FactSales SELECT * FROM SalesLakehouse.dbo.ProcessedSales WHERE SaleDate >= '$CutoffDate';
    COMMIT TRANSACTION;
    PRINT 'SUCCESS';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
"@

$tmpFile = [System.IO.Path]::GetTempFileName() + ".sql"
$sql | Out-File -FilePath $tmpFile -Encoding UTF8

for ($i = 1; $i -le $MaxRetries; $i++) {
    Write-Host "Attempt $i/$MaxRetries ..."
    $output = sqlcmd -S $Server -d $Database -G -i $tmpFile 2>&1
    if ($output -match "SUCCESS") {
        Remove-Item $tmpFile -Force
        Write-Host "Upsert complete"; exit 0
    }
    Write-Host "Retrying in $($i * 5)s ..."
    Start-Sleep -Seconds ($i * 5)
}

Remove-Item $tmpFile -Force
Write-Error "Failed after $MaxRetries attempts"; exit 1