2022 Developer Survey is open! Take survey.

Questions tagged [etl]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 views

Postgres improvements in conversion functions for bulk load

I regularly import data from httparchive.org. The data is a MySQL CSV export and I use pgloader, which handles the quirks of this export (\N for NULL), etc. I also need to do some additional ...
user avatar
0 votes
1 answer
66 views

Bigint vs varchar datatype. Should I match source system? [closed]

Unfortunately, I think this is an opinion question. But I'd like to know if there is a best practice. Our datawarehouse uses a bigint as a primary key for an address table. The existing records all ...
user avatar
0 votes
0 answers
13 views

SSIS Deployment The locale identifier (LCID) 14348 is not supported by SQL Server

When trying to deploy a SSIS package on my local machine I get the following error message The locale identifier (LCID) 14348 is not supported by SQL Server And ideas ?
user avatar
0 votes
0 answers
12 views

ODI scheduled load plans

I recently inherited on a data warehouse which uses ODI to integrate data. The mechanism is using scheduled ODI load plans. The problem is that in the ODI environment we have thousands of load plans, ...
user avatar
  • 101
1 vote
1 answer
46 views

Bulk Updates on Normalised data present across several PostgreSQL tables

We have several websites from where we download csv files - say 100 such websites. We don't have a control over their schedules, so we download files from them every few hours. Each such file can be ...
user avatar
  • 13
0 votes
1 answer
23 views

Extracting multiple similar tables in different schemas

I am attempting to extract tables from multiple schemas in the same database. The tables are similar enough that they are easy to identify and work with using meta data queries like this one: SELECT ...
user avatar
0 votes
2 answers
55 views

From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?

I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form. I'm investigating a ...
user avatar
0 votes
0 answers
27 views

Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?

Assume A, B, C etc are attribute values in the current rowset Current function logic is similar to: Case A = 'Y' then Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales' Case B in (99,98,102) OR D ...
user avatar
0 votes
0 answers
7 views

SSIS - Attunity 5.0 Oracle destination running for hours yet row count shows data has arrived so why does the dest task look in a hung state?

Using SSDT v14 A simple package that extracts data from a SQL Server view and sends via Attunity to Oracle takes about 5 hours to run, of which 4 hours seems to be the Oracle destination task showing ...
user avatar
1 vote
1 answer
37 views

SSIS: Can't save "parallel load" option in Oracle destination with Attunity driver, how to solve this?

I am working on an existing SSIS package that has proven to run successfully, however, due to data volume issues, I would like to try enabling the "parallel load" option by selecting the ...
user avatar
0 votes
2 answers
42 views

Selecting MySQL row during Batch Update

I currently have a database with 500K rows that could grow to 3 million in the future. The structure of the table looks like the following: -id -price -sku -version (denoted by a Date Time string ...
user avatar
  • 103
0 votes
1 answer
77 views

How to export data from AWS RDS MySQL database and import to an other AWS RDS MySQL database instance

I am looking for the best way to export data from one MySQL DB instance of RDS an import into a different one automatically. Any ideas?
user avatar
3 votes
1 answer
678 views

What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?

I've been tasked to extract images saved in SQL Server (as varbinary) to flat files via t-sql. I haven't done ETL work for over a decade and I can't recall any other method of doing this via t-sql ...
user avatar
0 votes
0 answers
32 views

ETL with Oracle: how to do an isolated run without overwriting previous results

Objective I'm refactoring ETL. ETL is imlemented as a chain of queries submitted to Oracle one-by-one. While executing, ETL creates/overwrites about 20 output tables. For sake of testing (including ...
user avatar
  • 387
3 votes
3 answers
138 views

ADF patterns to minimise batch run elapsed time

We use Azure Data Factory (ADF) to pull a number of source tables from an on-prem SQL Server DB into Azure Data Lake (DL). We've made this data-driven using the Lookup-ForEach pattern. There is one ...
user avatar
1 vote
3 answers
152 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 ...
user avatar
  • 443
1 vote
0 answers
24 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 ...
user avatar
0 votes
2 answers
99 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 ...
user avatar
  • 1,493
1 vote
1 answer
175 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 ...
user avatar
  • 207
0 votes
1 answer
84 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 ...
user avatar
1 vote
2 answers
587 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 ...
user avatar
  • 443
0 votes
0 answers
97 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 ...
user avatar
  • 1
0 votes
1 answer
119 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 ...
user avatar
  • 587
1 vote
1 answer
131 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 ...
user avatar
  • 115
-1 votes
1 answer
52 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 ...
user avatar
  • 313
1 vote
1 answer
79 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 ...
user avatar
2 votes
2 answers
275 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] ...
user avatar
2 votes
1 answer
53 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 ...
user avatar
1 vote
1 answer
83 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 ...
user avatar
  • 11
0 votes
2 answers
171 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....
user avatar
  • 101
0 votes
1 answer
129 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 ...
user avatar
  • 101
1 vote
0 answers
164 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 ...
user avatar
  • 24.3k
0 votes
0 answers
212 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 ...
user avatar
  • 101
0 votes
1 answer
521 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 ...
user avatar
1 vote
1 answer
237 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 ...
user avatar
0 votes
1 answer
76 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 ...
user avatar
2 votes
1 answer
31 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 ...
user avatar
  • 29
1 vote
0 answers
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 ...
user avatar
  • 1,529
1 vote
2 answers
184 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 ...
user avatar
1 vote
0 answers
134 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 ...
user avatar
1 vote
1 answer
81 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 ...
user avatar
1 vote
0 answers
9 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 : ...
user avatar
0 votes
1 answer
164 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, ...
user avatar
1 vote
1 answer
473 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 ...
user avatar
1 vote
0 answers
105 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 ...
user avatar
1 vote
3 answers
3k 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 ...
user avatar
0 votes
2 answers
3k 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 ...
user avatar
  • 103
1 vote
1 answer
370 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 ...
user avatar
0 votes
1 answer
89 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/...
user avatar
1 vote
1 answer
907 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 ...
user avatar
  • 21

1
2 3 4 5