Questions tagged [etl]

'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.

Filter by
Sorted by
Tagged with
1
vote
2answers
125 views

Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?

For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data? I had expected that the total time for ETL when ...
1
vote
0answers
21 views

History data in CUBE

Not sure if this is the right place to ask. I am working on an ETL procedure that gathers the data about medication consumption and I have something like this: Dim_Patient, the dimension for all the ...
0
votes
2answers
42 views

How to “normalize” external data before insertion into “Data Warehouse” like database?

I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse. There are MILLIONS of email records from an email platform that wasn't too ...
1
vote
1answer
34 views

Is “ETL vs ELT” just “tables vs views”?

TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse? So forgive me the broadness of the subject ...
0
votes
1answer
32 views

Oracle - Exchange partitions between two List partitioned tables

Need some help here. I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by ...
1
vote
1answer
211 views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
0
votes
0answers
45 views

How to ETL SQL Server resultset to mongo DB

Let's say I have multiple columns on sql server that I want to join and save the resultset into a single mongodb collection for faster lookup and calculations ( I'm talking about over 15 tables and ...
0
votes
1answer
33 views

Truncate and insert: Heap and build index or directly into Clustered table

In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place. I know that I can ...
1
vote
0answers
19 views

ODI remove auto lock when object / datastore is opened

I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time. Now I want to remove the automatically lock from GUI but I couldn't seem to find it. I ...
-1
votes
1answer
47 views

Non-procedural way of implementing ELT dependency logic

I have two tables with identical schema, sales_primary and sales_secondary: productID trxnWeekStartDate dollarAmt 123 2021-02-07 100 For a particular week, these two tables can both have a ...
1
vote
1answer
72 views

RCSI & ETL processing compatibility

The Situation: I have a write-heavy, read-light ETL workload with the basic process of: get chunk of raw data (generally 1000-5000 records) from an import landing table. pass the chunk of data from ...
2
votes
2answers
108 views

What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?

I am doing some test with clustered columnstore tables - both partitioned and not. In my research, I've seen flavors of this pattern: -- step 1: make new table SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
2
votes
1answer
38 views

What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?

The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts. Are there any ...
1
vote
1answer
41 views

MYSQL Huge Data migration (5TB) from old server to new server

We have an old MYSQL server contains huge data ~5TB and wanted to migrate to the new server in order to minimize the costs and get rid of very old hardware. My one and only idea in my mind is using ...
0
votes
2answers
68 views

SQL join on col1 if present else use col2

There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup. What I want is either join on T1.col1 = T2.col1 or T1....
0
votes
0answers
81 views

Select Changed Rows in MySQL and Postgres without Timestamp column

I have the following scenario: I have 2 source systems, one running Postgres, the other running MySQL. An ETL process (using Azure DataFactory and Azure Integration runtimes) runs the Extract for a ...
0
votes
1answer
81 views

Snowflake/S3 Pipeline: ETL architecture Questions

I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data ...
1
vote
0answers
118 views

How to modify the block size of my S3 parquet files that are being queried in Athena?

I read that adjusting the block size of the parquet files being queried with Athena can affect and possibly improve the performance of the queries. My parquet files for my database are currently ...
0
votes
0answers
70 views

Avoid transaction when reading data through a linked server

This is SQL Server related. Is there other techniques that I can use for this? (Read data from a procedure in another server.) I'm trying to re-use a stored procedure from another sever instance ...
0
votes
1answer
237 views

Agent Job: Execute T-SQL on different server?

While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link). We were moving our SSIS packages and agent jobs on ETL ...
1
vote
1answer
136 views

Batch - Update vs Insert then Delete [closed]

There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 ...
0
votes
1answer
52 views

connect two sql server and import to CSV file (ETL)

I have two servers, both has a sql server . Let's say srv1 has a sql-srv1 and srv2 with a sql-srv2 , i want to get some informations within sql-srv2 but making requests from sql-srv1. my project is ...
2
votes
1answer
23 views

Azure SSIS Deploying in SQL Azure

Does deploying SSIS really required an azure data lake house when deploying on Azure database?. I am currently working on an SSIS project that its database is currently deployed on Azure and I am ...
1
vote
0answers
37 views

Tool to help breaking multitenant into a single tenant [closed]

Oracle 11.2.0.4 I've been tasked with breaking out a single customer from our multitenant system into a separate system. And the data will have to be continuously updated. This is a large system ...
1
vote
2answers
156 views

Strategies/Resources for implementing data lineage in an ETL process?

I'm struggling to articulate my need here. I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack. I'd ...
1
vote
0answers
113 views

Database - limit of reading a table with a lot of concurrent batch read and writes

We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and ...
1
vote
1answer
50 views

Data Pipeline load bulk tables in one time

I am using AWS Data Pipeline for copying my RDS MySQL Database to Redshift. I need to create separate pipeline for each table and each pipeline create new EC2 instance. (Process takes time). Problem ...
1
vote
0answers
7 views

Date getting truncated on PDI 7.1 for salesforce DB - Need workaround

We are working on transformation which inserts date into a salesforce DB and are seeing issues. Below are the issues: A transformation working in PDI CE 6.1 do not work in CE 7.1 or CE 8.3. Error : ...
0
votes
1answer
137 views

TIMESTAMP OR DateUpdate

I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, ...
1
vote
1answer
293 views

Does loading data to temp table improve performance?

When I write stored procedure I use views that have calculation inside them and so if I have to reuse the view multiple times in my procedure, I load it to a temp table and then use the temp table ...
1
vote
0answers
74 views

SSRS managed shared subscriptions data from from database

While running an ETL process we need to pause the shared managed SSRS subscriptions and then enable them and run them accordingly when our ETL has progressed. In order to do that we take a backup of ...
0
votes
2answers
2k views

SSIS Slow Data Transfer between Oracle and SQL Server 2016

I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS. It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
0
votes
2answers
2k views

Specifying Order of Executing Stored Procedures

Issue: The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error: Msg 2627, Level 14, State 1, Procedure ...
1
vote
0answers
252 views

Parallel insert and delete into staging tables

I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
0
votes
1answer
56 views

Transactional Replication data during rebuild

To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/...
0
votes
1answer
725 views

SSIS data flow gets stuck reading table with no errors

When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
0
votes
3answers
98 views

Window Function to get pairs of records and compute the time difference

I'm working with an event dataset as the one in the example below: Event_Type Event_Timestamp Is_Active A 2010-10-01 00:00:00 1 B 2010-10-01 00:00:01 1 A ...
1
vote
1answer
80 views

Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?

I am building an Azure SQL DB database and to do this I will have to regularly (weekly-monthly) pull data from various other servers. I am designing SSIS packages to do this and whilst most of the ...
2
votes
2answers
5k views

Linked server returned message “Query timeout expired.”

We have two separate SQL Servers. On one server we have a data warehouse (DWH), on the other we have sales information database. Now on the DWH server there is an ETL job that collects the ...
2
votes
1answer
228 views

SSIS comparison load between new, updated and deleted records and populate a staging database

So far, My control flow looks like: Execute SQL Task - This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be ...
0
votes
1answer
140 views

What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015? [closed]

I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to ...
3
votes
3answers
1k views

How to handle schema changes in source systems when running an ETL?

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.
3
votes
0answers
37 views

Migrating Access Saved Import Specifications to SSIS Package

We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
1
vote
2answers
599 views

Can Transactional Replication and triggers be used for near-realtime ETL [closed]

We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers? How we currently ETL We use sql ...
2
votes
1answer
622 views

ETL, Production, Development — Should these be different servers? Instances?

You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly. My predecessor left quite the mess regardless but I'm trying ...
0
votes
0answers
69 views

How to Represent a VARCHAR Column with XML Data in a Fact Table?

I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
31
votes
4answers
2k views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
1
vote
1answer
563 views

When to Disable and Recreate Indexes in Data Warehouse ETL?

We are moving data from Staging Database into Kimball Data Warehouse DB. I am hearing different ideas regarding when to disable and rebuild indexes. Stage tables are truncated every day for each new ...
-2
votes
1answer
382 views

Datawarehouse Staging Tables: Null or Not Null

We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process. We are inquiring about staging tables, if they should have null/not null constraints. ...
3
votes
2answers
265 views

Structured .TXT Data To A Proper DBMS [closed]

I'm presented with multiple .txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each .txt file has different ...

1
2 3 4 5