Alternative solutions for Synapse analytics limitations

Hello @rajendarerabathini-1405 and welcome to Microsoft Q&A.

Since these features are not availably in Synapse, the solution is to build them into your queries. I have examples for your first two points, but not yet for triggers.

1. The default values must be constants, functions are not supported as column defaults.
   Write the default into the insert statement. Suppose we have staging table and production table. In the process of inserting the staging table into the production table, we can coalesce the date column with the getdate function, effectively provide a default. create table withnull(
   val integer ,
   defdate DATE default null
   ) insert into withnull (val) VALUES (3) create table production(
   val integer ,
   defdate DATE not NULL
   ) insert into withnull (val) VALUES (3) INSERT INTO production (val, defdate)
   SELECT
   val
   ,COALESCE(defdate,getdate())
   FROM withnull SELECT * from production
2. There are no foreign key constraints in Synapse
   Write checks into your DML. Here I demonstrate how to only insert fact rows which contain valid id's create table fact_fruit_staging (
   vendorid INT,
   apples INT default 0,
   bananas INT default 0
   ) create table fact_fruit_imports_production (
   vendorid INT NOT NULL,
   apples INT,
   bananas INT
   ) create table dim_vendors_production (
   vendorid INT NOT NULL,
   name varchar(30) NOT NULL
   ) INSERT INTO dim_vendors_production (vendorid, name) VALUES ('1','Satish');
   INSERT INTO dim_vendors_production (vendorid, name) VALUES (2,'Jacques');
   INSERT INTO dim_vendors_production (vendorid, name) VALUES (3,'Sarah'); INSERT INTO fact_fruit_staging (vendorid, apples, bananas) VALUES (null,23,34);
   INSERT INTO fact_fruit_staging (vendorid, apples, bananas) VALUES (2,12,6);
   INSERT INTO fact_fruit_staging (vendorid, apples, bananas) VALUES (99,2,22);
   INSERT INTO fact_fruit_staging (vendorid, apples, bananas) VALUES (3,2,40); INSERT INTO fact_fruit_imports_production (vendorid, apples, bananas)
   SELECT stag.vendorid, stag.apples, stag.bananas
   FROM fact_fruit_staging as stag, dim_vendors_production
   WHERE stag.vendorid IN (SELECT vendorid FROM dim_vendors_production); select * from fact_fruit_imports_production
   3. In a similar vein , extra actions need to be added to your queries, maybe.