Execution plan for staging In-Memory tables estimate row count =1 We are using In-Memory tables (durability schema only) as temporary staging tables for part of the ETL process.
the nature of these staging tables is empty most of the time.
The ETL process executed per organization asynchronicity.

Problem:
The execution plan is generated with estimate row number = 1 (probably the table was empty for first execution)

Manually update statistics will not fix the issue since:
the table may be empty or the no record for an organization that ETL will execute first.

I add OPTION recompile to query but still it not stable

I'm looking for correct pattern working with in-memory tables as the Staging table

MS SQL 2017 CU 13
Standard edition

In-Memory table schema:

CREATE TABLE [dbo].[process_aggregation_close] ( [organization_id] [bigint] NOT NULL, ...... INDEX [ix_process_aggregation_memory_computer_id_process_id_test] NONCLUSTERED ( [organization_id] ASC, [computer_id] ASC, [process_id] ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )


Destination Clustered Column Store (disk base)

CREATE TABLE [dbo].[process_aggregation_5m]( [organization_id] [bigint] NOT NULL, ....... ) ON [ps_process_aggregation_close]([partkey]) GO CREATE CLUSTERED COLUMNSTORE INDEX [IX_process_aggregation_5m] ON [dbo].[process_aggregation_5m] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [ps_process_aggregation_close]([partkey])


Thank you
Yuri
Are you using memory-optimized tables, or memory-optimized table variables? Memory-optimized table variables suffer from the same issues as "regular" table variables: the cardinality is assumed to be very low. 

The other potential issue with using memory-optimized tables as a destination for staging ETL, is that all writes to memory-optimized tables are serial. So you will have to "roll your own" parallelism.